Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

DB2 slideshows

Posted May 10, 2018

5 More Features of IBM Db2 12 for z/OS

By Lockwood Lyon

  • Previous
    5 More Features of IBM Db2 12 for z/OS

    5 More Features of IBM Db2 12 for z/OS

  • Previous
    Remote applications can now directly invoke the Db2 LOAD utility while avoiding costly data transfers

    Remote Applications Can Now Directly Invoke the Db2 LOAD Utility While Avoiding Costly Data Transfers

    Distributed Relational Database Architecture (DRDA) Fast Load

    In the past, remote systems and applications owning data that needed to be loaded into Db2 had to go through a cumbersome process. The data was first transferred from the remote system to the z/OS system, usually using file transfer protocol (FTP) or some other means of transporting data across the network. After that, the process invoked the Db2 LOAD utility to load the data into the table and its associated indexes.  This required setting up the transfer processes and defining storage on z/OS to accept the data.

    Db2 v12 includes a facility called DRDA Fast Load. This allows a process on a remote server to invoke DRDA Fast Load and send its data directly, followed by  automatically invoking the Db2 LOAD utility. This method of streaming data from a remote system directly into Db2 bypasses the intermediate file transfer step as well as consolidating multiple processes into a single process.

  • Previous
    Expanded Data Compression and Encryption Options

    Expanded Data Compression and Encryption Options

    New Hardware-based Compression and Encryption Options

    Db2 v12 was designed to synergize with new IBM z14 hardware. These new hardware options include accelerated cryptographic processing on specialized CPUs. This feature can be used by Db2 during data storage for both data encryption and compression. If a tablespace is configured with COMPRESS YES and Db2 is executing on a z14 machine with this feature enabled, data compression is handled by the hardware transparently prior to storage on disk. Retrieval and the required decompression is handled similarly.

    This permits Db2 12 to bypass software-based data compression, thus speeding up data storage and retrieval for compressed data. The same is true for encrypting table data.

    Future maintenance upgrades for Db2 12 will include another high-performance feature, order-preserving compression for Db2 indexes. This compression will allow for compressing index data without disturbing the sort order of index keys.

  • Previous
    High-speed Insert for the IBM Db2 Analytics Accelerator (IDAA)

    High-speed Insert for the IBM DB2 Analytics Accelerator (IDAA)

    Multi-row Insert for Accelerator-only Tables

    With the advent of big data applications and business analytics, several vendors came out with hybrid hardware/software solutions that stored business data in massively parallel disk storage units. This was coupled with software that could take an SQL query, split it into hundreds of sub-queries, execute each of these on a single disk file and merge the results. These appliances provided crazy fast response times to many analytical queries. IBM’s solution was called the IBM Db2 Analytics Accelerator, or IDAA.

    Customers noticed that some categories of data were accessed exclusively by analytical queries against the appliance. In other words, there was no operational need to maintain a copy of the data in a native Db2 table. Thus, were born “accelerator-only” tables, created and maintained in the appliance. While many analytical tables are relatively static, for some of these tables data changed frequently.

    Recent software upgrades to Db2 version 12 along with the latest version of the IDAA now allows for executing multi-row SQL insert statements against accelerator-only tables.  This feature provides a high-speed way to get up-to-date analytical data into the appliance without potential outages caused by a LOAD utility.

  • Previous
    Use of Special Processor for Reload Phase of LOAD Utility

    Use of Special Processor for Reload Phase of LOAD Utility

    zIIP Enabled Reload Phase of LOAD and REORG

    The IBM z Integrated Information Processor (zIIP) is a special processor chip that can be attached to most z hardware installations. It works asynchronously with the general processor units (GPUs) on the mainframe. The primary way for IBM to measure software licensing charges is through CPU usage. One typical method is to measure a rolling 4-hour average CPU used over the licensing period (typically a month) and use that figure as an estimate of CPU usage.

    Cycles used by the zIIP processor are not included in this measurement; hence, enabling software packages to use zIIP rather than GPU cycles reduces CPU usage and licensing costs.

    The latest maintenance update to Db2 v12 has software changes that designate the reload phases of the LOAD and REORG utilities as zIIP-eligible.

    The LOAD utility has multiple phases. The first phase is the Load phase, where data is input and loaded into the table base. During this phase key information about new index entries is saved in a separate index keys file. The second phase is the Reload phase, where indexes keys are inserted into the index base. It is this second that can now use the zIIP processor for sorting and I/Os associated with loading the index.

    Measurements by IBM indicate that in the best cases almost 90% of CPU usage by the LOAD utility can be offloaded to the zIIP processor.

  • Previous
    Replicating Data to the IDAA Permits Concurrent Transaction Processing and Analytics in Near Real-time

    Replicating Data to the IDAA Permits Concurrent Transaction Processing and Analytics in Near Real-time

    Zero-latency HTAP

    As big data applications matured one thorny performance issue remained: the data in the appliance was huge, and required time-consuming data load processes. For some customers it became like a large data warehouse, providing analytics against day-old data. Other customers attempted to load the appliance as soon as operational data was available. Still, there was a time delay while loading applications executed. Also, at times several loads were required to several tables to maintain data consistency. For example, one couldn’t load new orders into the Order table until the corresponding customers were loaded into the Customer table.

    The latest versions of IBM’s Db2 version 12 and IDAA can now be configured to allow concurrent transactional and analytical processing. IBM’s new patented concurrent replication protocol is a “just-in-time” method of propagating operational Db2 table changes to the IDAA. The result is that transaction data in the operational application systems is up-to-date for business analytics requests executed against data in the appliance.

    The result is a hybrid of both transactional and analytical processing.

    With these new features (and more) of their flagship relational database management system, IBM has redefined the infrastructure of enterprise information technology to better align with digital business, cloud computing and mobile applications. New access path management methods such as dynamic SQL plan stability allow for more efficient capacity planning while reducing the risks of rogue SQL statements running for extended periods while locking data and consuming large amounts of CPU time. Finally, synergy with advances in IBM z14 hardware such as taking advantage of the hardware acceleration of cryptographic processing and data compression means that Db2 on IBM Z will continue to provide industry-leading scalability, availability and performance.

As noted in our previous article "5 New Features for Db2 12 for z/OS", IBM's Db2 Version 12 for z/OS has many new features that advance the availability, scalability and performance of transactional and analytical applications. In this slideshow we focus on performance and security measures, particularly for mobile and cloud applications. Advances in cryptographic hardware and query accelerator technologies facilitate rapid development of customer-facing applications and embedding big data queries in operational systems.

See all articles by Lockwood Lyon