DB2 11 for z/OS SQL performance improvements help make your applications go faster. DB2 11 also helps address some of the latest coding problems provided by the latest Java and other development coding. The following two items highlight some of the improvements that come straight out of the box in DB2 11 for z/OS and others that are enhanced in DB2 11 to improve your SQL performance and overall application efficiency.
DB2 Processing Stages
First, it is good to review the five different stages that DB2 uses during its processing of the SQL WHERE clause predicates. The SQL predicates are processed through the DB2 optimizer in different evaluation stages. These different optimizer stages evaluate all the predicates, how they match up against the database columns, the table’s indexes, and the columns in the various indexes.
- The first stage evaluates the index-able predicates. The DB2 optimizer evaluates and applies the WHERE clause criteria against the matching index key columns when the index is accessed.
- All indexed columns with DB2 Stage 1 predicates that were not picked up in the first index matching process are applied against the index.
- Within all this and all subsequent DB2 optimization stages the equal WHERE clause predicates are applied first, including IN predicates and BETWEEN predicates that contain the same value twice.
- Next are DB2 WHERE clause range predicates and column-name IS NOT NULL tests.
- Then all other DB2 WHERE clause predicate types are evaluated in the order that they appear in the query.
- Any DB2 Stage 1 WHERE clause predicate referring to the partitioning range column definitions are applied to limit the partitions accessed.
- As the data pages are referenced, any other DB2 Stage 1 predicates are applied to the data.
- The remaining DB2 Stage 2 predicates are applied to the returned data rows.
All of these DB2 optimizer evaluation stages and other valuable information is detailed in the various extended DB2 EXPLAIN output tables. In addition to the DB2 access plan query block sequence in the PLAN_TABLE, the DSN_FILTER_TABLE indicates the various stages when predicates are applied within the DB2 access path execution of retrieving the data. All of this information helps you understand the DB2 SQL performance improvements from the different DB2 SQL WHERE clause predicates.
DB2 11 for z/OS Improvements
To take care of more filtering and improve DB2 11 SQL performance, the DB2 11 optimizer adds several additional SQL WHERE clause phrases to its Stage 1 access path evaluation. Adding these to Stage 1 DB2 SQL performance processing filters the data earlier in the overall processing and helps limit the data that might be needed in later DB2 stage processing for the ultimate result set. These following DB2 SQL WHERE clause predicates are now evaluated in Stage 1 of the DB2 11 SQL performance processing.
- value BETWEEN COL1 AND COL2
- value BETWEEN column-expression AND column-expression
- SUBSTR(COLX, 1, n) = value
- DATE(timestamp-column) = value
- YEAR(date-column) = value
- CASE WHEN THEN ELSE END = value
All of these commonly used DB2 SQL predicates will help match on indexes and filter data earlier in the DB2 processing. These DB2 SQL performance improvements can make a huge performance impact on your applications cutting overall elapsed time and CPU time by filtering the data in Stage 1 processing.
New within DB2 11 SQL performance are new APPLICATION COMPATIBILITY/APPLCOMPAT parameters. The APPLCOMPAT subsystem parameter, new BIND and REBIND APPLCOMPAT options, and SET APPLICATION COMPATIBILITY SQL statement allows the DB2 system administrator and the DB2 SQL performance to be set to a compatibility of DB2 10 or DB2 11. This option allows flexibility to manage at the DB2 subsystem, package level, within a JDBC data source connection and within the application when new DB2 SQL performance and functionality will be leveraged.
- DB2 subsystem parameter – APPLCOMPAT
- BIND/REBIND package or REBIND TRIGGER PACKAGE parameter – APPLCOMPAT
- EXEC SQL SET :CS = CURRENT APPLICATION COMPATIBILITY; Where the value of CS is either V10R1 or V11R1.
Through these new APPLICATION COMPATIBILITY/APPLCOMPAT parameters the DB2 system can be migrated to DB2 11 through to New Function Mode (NFM) and the SQL functionality can have the characteristics of DB2 V10. This can allow the flexibility to allow some applications to move forward and leverage the new DB2 11 SQL performance improvements right away. During older application testing, applications can use the new parameter to emulate DB2 10 and gradually expose their applications to the new DB2 11 capabilities.
Remembering these three DB2 SQL performance improvement items your applications can be improved with the new DB2 11 SQL capabilities and provide better performance straight out of the box.
Dave Beulke is a system strategist, application architect, and performance expert specializing in Big Data, data warehouses, and high performance internet business solutions. He is an IBM Gold Consultant, Information Champion, President of DAMA-NCR, former President of International DB2 User Group, and frequent speaker at national and international conferences. His architectures, designs, and performance tuning techniques help organization better leverage their information assets, saving millions in processing costs.