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

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

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

DB2 slideshows

Posted April 19, 2018

5 New Features for Db2 12 for z/OS

By Lockwood Lyon

  • Previous
    5 New Features for Db2 12 for z/OS

    5 New Features for Db2 12 for z/OS

  • Previous
    SQL Pagination Syntax

    SQL Pagination Syntax

    In the past, on-line applications provided search capabilities to users by permitting the entry of key subsets or wildcards on the screen. For example, one could request a search for customers with a last name of “Smith*”, where the asterisk indicated any combination of zero or more letters. The results would include names like Smith, Smithe and Smithson. Typically, the application would present these results in alphabetical order. More complex searches might involve a search on a combination of last name and first name. The user might then select a function that initiated a “page down” process, where the following rows from the same query would be retrieved for display. This required application logic to remember the last row retrieved in order to continue executing the original query.

    In Figure 1 (“Old Method”) we show the SQL required to retrieve table rows based on last name and first name, where the results must appear in alpha order. In the application this query would be placed in a cursor and rows fetched from the result to populate a results screen. The coding of the query, containing multiple columns, host variables and boolean logic could become quite complex, especially for queries on multi-column keys.

    Db2 v12 provides two alternative syntaxes for doing the same thing. The first simplifies the old method by allowing specification of key combinations in parentheses; the second method uses the new OFFSET keyword and FETCH FIRST syntax to specify how many result rows to retrieve, thus greatly simplifying application logic.

  • Previous
    Increased partition and table sizes

    Increased Partition and Table Sizes

    In prior Db2 versions the maximum size of a dataset for a partitioned tablespace was 256 gigabytes, as specified with the DSSIZE parameter. This parameter was defined at the tablespace level, and held for all partitions. With a 4K page size this limited the maximum number of partitions to 64, giving a maximum total tablespace size of 16 terabytes. While it was possible to alter the DSSIZE parameter (from a minimum up to 256 GB), alterations required a reorganization of the entire tablespace, during which time the table was unavailable. In addition, should a single partition become full of active rows, changing the number of partitions or adding partitions also required a major reorg.

    In Db2 v12 there is a new universal tablespace structure that allows the DBA to specify DSSIZE at the partition level. Further, the maximum partition size is increased to 1 terabyte. This change, along with allowing for more than 64 partitions, increases the maximum tablespace size to 4 petabytes. (1 petabyte = 1,000 terabytes). As a result, it is now possible for a single table to contain 256 trillion rows.

    Db2 v12 also includes new DSSIZE changes to support larger indexes.

  • Previous
    Index Fast Traverse Blocks

    Index Fast Traverse Blocks

    Some SQL queries analyzed by the Db2 optimizer result in access paths that require multiple searches through an index tree structure. Beginning with the index root page, Db2 must determine what non-leaf pages must be read into memory and, depending upon the results, if additional pages are required. These may be non-leaf pages at a lower level in the index tree, or leaf pages that contain key information and pointers to rows in the table. Each retrieval of a non-leaf page typically requires in an I/O, and multiple retrievals of pages can cause I/O waits.

    Db2 v12 delivers a new feature called Index Fast Traversal. First, Db2 analyzes whether a query can take advantage of this new traversal method, i.e., whether the new method will be cost effective. If so, Db2 assembles index page information and stores it in a new memory area called index control that is outside the normal buffer pools reserved for data and index I/Os. Db2 then stores the data in a proprietary structure in that area, and allows the query to continue.

    New queries that can benefit from this index information are automatically directed to the new area.

    This new feature is currently restricted to unique indexes whose key lengths are no greater than 64 bytes. Initial measurements show a 20 percent reduction in CPU when index fast traversal is used.

  • Previous
    Heavy INSERT Workloads

    Heavy INSERT Workloads

    One of the most difficult performance tuning situations for the DBA is that of heavy application workloads that do SQL inserts into a single table. There are several potential issues to contend with, including “hot spots” in the table where newly-inserted rows having similar keys should be added, system logs filling with new changed data for recovery purposes, CPU and I/O used to search for empty space in the table and index, and others.

    In the past, DBAs have attempted to alleviate some of these bottlenecks through creative database design techniques. One method is to physically partition the table and randomly assign new rows to partitions, thus avoiding heavy changes to a single place in the table. Another method is to define the tablespace with frequent free space areas, initially empty of rows, so that newly-inserted rows can be added there.

    Db2 v12 provides a new method to alleviate some of the constraints of a heavy insert workload. This is a new algorithm for determining where new rows are to be inserted, and is called insert algorithm 2. Db2 can use this new insert algorithm to speed up non-clustered inserts where the constraint is multiple concurrent threads doing inserts associated with delays finding available space for the new rows. While not a panacea for every situation, this new algorithm does provide constraint relief in many cases.

    Estimates made by IBM indicate that a table with a single index can experience more than five million inserts per second using the new algorithm.

  • Previous
    Dynamic SQL Plan Stability

    Dynamic SQL Plan Stability

    Many applications construct SQL statements based upon user requests, rather than pre-code all possible statements in the application. Pre-coding SQL statements, or static SQL, is much easier to tune for a variety of factors:

    • Statements are already known before rollout to production, as they are stored in the Db2 catalog and directory during bind;
    • DBAs can query the catalog and directory to determine what objects are being accessed and their associated access paths;
    • If any access paths are problematic the DBA can initiate performance tuning such as adding or changing indexes, or suggesting alternative SQL syntax to developers.

    On the other hand, dynamic SQL by its very nature has been difficult to tune. Since statements are constructed immediately prior to execution, DBAs may have little or no idea what to expect from the application. While Db2 stores dynamic SQL statements and access path information in a cache, the limited size of the cache usually causes statement information to be deleted soon after storage. Problems can be exacerbated when DBAs make infrastructure changes such as adding indexes, changing memory configurations or upgrading software.

    Db2 12 introduces a new feature called dynamic SQL plan stability. Dynamic SQL information previously stored in the dynamic statement cache is now stored permanently in the Db2 catalog. If a statement executes a second time, access path information can now be retrieved from the catalog, avoiding the CPU cost of invoking the Db2 optimizer.

IBM’s Db2 Version 12 for z/OS became generally available to customers in October, 2016. After many successful customer installs, IBM announced in September of 2017 that version 10 would no longer be in service. In addition, the end-of-service date for version 11 is set at September, 2020. IBM mainframe Db2 customers, therefore, have about two years to plan on migrating or upgrading their current Db2 version 11 systems.

Version 12 has many new and improved features that increase availability, scalability and performance, and address the future of mobile and cloud computing. In addition, v12 introduces a new paradigm of maintenance that seeks to speed up delivery of new features and function to customers without them having to wait for a new version of the software.

In this article, we review some of the highlights of the latest version.

See all articles by Lockwood Lyon

Comment and Contribute


(Maximum characters: 1200). You have characters left.