New Features

This article describes the new enhancements in Version 20.0 of
CA Plan Analyzer® for DB2 for z/OS
.
capadb220
The following list summarizes the new features and enhancements that have been delivered to date in Version 20.0 for
CA Plan Analyzer® for DB2 for z/OS
:
To ensure availability of all features and fixes, install all available maintenance. To download and receive maintenance automatically, use CA SMP/E Receive Order. To view a list of available solutions and fixes, select the link for PTFs since GA on the CA Database Management Solutions for DB2 for z/OS Maintenance Grid (login required).
For a list of enhancements for all the CA Database Management Solutions for DB2 for z/OS, click here. To request product enhancements, contact a product manager or post your request in the "Ideation" section of the Db2 Tools Community.
DBM 20 IR 8 is the last incremental update for Version 20.0. New features and fixes are no longer bundled in incremental PTFs. Instead, we have transitioned to a continuous delivery release model that provides individual PTFs for new product features and product fixes. For more information, see Transition to Continuous Delivery.
Compare Summary Reporting in Explain Strategies (LU00440)
CA Plan Analyzer® for DB2 for z/OS
has a new Compare History function and Compare Summary report that enhances your ability to identify changes to access paths and SQL costs in your Db2 subsystems.
When you use the new Compare History function in your explain strategies, the compare results are collected and saved. You can use the Explain Strategy Services to generate a Compare Summary report from the saved comparison data. This report helps you identify statement access paths with performance degradation, and access paths with improved performance that can benefit from a rebind. You can filter the report by various compare categories (Degraded, Improved, Changed, Unchanged, Non-Paired, or Error). You can also use line commands to perform a deeper analysis on individual statements. These features enable you to quickly exclude irrelevant data and focus on only those critical statements that impact your systems.
This new functionality is enabled or displayed in the following locations:
  • A new Explain Strategy Compare History table (PTPA_ES_COMPHIST_#) has been created to store the compare history records.
  • In the PPA member of
    hlq
    .CDBAPARM, the COMPARE parameter has a new "H" value that stores the results of your explain version comparisons in the Explain Strategy Compare History table.
  • In the explain profiles and in the source-level and global explain options for explain strategies, the Compare Report field has a new “H” option to select the Compare History function.
  • The enhanced explain REPORT card supports a new COMPARE/HISTORY value to select the Compare History function.
  • The Explain Strategy Services panel has a new “CH” column that indicates which strategy versions have compare history records. A value of “Y” indicates that compare history records exist. A blank value indicates that these records do not exist.
  • On the Explain Strategy Services panel, the explain strategy versions support a new “S” line command that displays the new Compare Summary report.
For more information about these new features, see Identify Access Path and SQL Cost Changes (Compare Summary).
Db2 12 Built-In Scalar Functions and Alternate Built-In Function Names (SO12650)
CA Plan Analyzer now supports several new scalar functions that were introduced in function level V12R1M505. This product also supports the alternate names that were introduced for several preexisting built-in functions in function level V12R1M506. SQL that contains the following syntax can now be processed successfully:
  • Built-in scalar functions:
    • DECRYPT_DATAKEY_BIGINT
    • DECRYPT_DATAKEY_BIT
    • DECRYPT_DATAKEY_CLOB
    • DECRYPT_DATAKEY_DBCLOB
    • DECRYPT_DATAKEY_DECIMAL
    • DECRYPT_DATAKEY_INTEGER
    • DECRYPT_DATAKEY_VARCHAR
    • DECRYPT_DATAKEY_VARGRAPHIC
    • ENCRYPT_DATAKEY
  • Alternate names for preexisting built-in functions:
    Preexisting Function Name
    Alternate Name
    COVARIANCE or COVAR
    COVAR_POP
    CHARACTER_LENGTH
    CHAR_LENGTH
    CLOB
    TO_CLOB
    HASH_MD5, HASH_SHA1, or HASH_SHA256
    HASH
    LEFT
    STRLEFT
    POSSTR
    STRPOS
    POWER
    POW
    RAND
    RANDOM
    RIGHT
    STRRIGHT
    TIMESTAMP_FORMAT or TO_DATE
    TO_TIMESTAMP
Db2 11 PROGAUTH Support in Plan Reports (SO12367)
CA Plan Analyzer now supports the PROGAUTH column in the SYSIBM.SYSPLAN table. The Plan List, Plan Detail, Plan/Package Utilities, and Identify Problem Plan/Packages reports have been updated. The values in this column indicate whether Db2 performs program authorization checking to determine whether it can execute a plan.
Db2 11 APPLCOMPAT Support in Future Explains (SO12267)
A new enhanced explain card, APPLCOMPAT, lets you specify application compatibility behavior for catalog or non-catalog SQL during a future explain. You can use this card to enable the use of SQL functionality that is provided within a specific Db2 version. You can also use this card to prevent the use of incompatible SQL functionality.
If the APPLCOMPAT card is not specified, the following default values are used:
  • Catalog SQL uses the package APPLCOMPAT bind value.
  • Non-catalog SQL uses the APPLCOMPAT setting that was assigned to the CA Plan Analyzer explain application packages during product installation.
Db2 12 Explain Report Support for New DSN_STATEMNT_TABLE Columns (SO12067)
Db2 12 DSN_STATEMNT_TABLE columns APCOMPARE_STATUS, APREUSE_STATUS, APREUSE_COPYID, and APREUSE_VERSION are now supported.
  • The short Access Path reports now supports the new columns. These columns are populated by a BIND or REBIND parameter for APCOMPARE or APREUSE. Also, a new access path filter, APREUSE Fail, has been added to the explain options. This filter lets you eliminate SQL statements that do not meet the criteria of unsuccessful access path reuse (because of a failed operation or because no match was found).
  • The Query Explain Database Explain Summary report includes a new APREUS FAIL column that reports on CURRENT explains where access path reuse is not successful. A new APREUS FAIL filter in the Identify Problem SQL for Explain Problem report options eliminates SQL statements that do not meet the criteria of unsuccessful access path reuse.
  • The new Db2 12 columns have been added to the
    CA Plan Analyzer® for DB2 for z/OS
    historical database table PTAN_STMT. You can display these fields using the INFO line command in the Historical Database Reports.
  • The Explain Type field now provides more granularity on the origins of CURRENT explains. Instead of displaying CURRENT, the Explain Type field now shows a value of AUTO-BIND, BIND, or REBIND in the following reports: Explain SQL Detail Report, Compare Header Report, Historical Database Reports (where it appears as ET instead of Explain Type), and Historical Database Reports (where it is displayed by using the INFO line command).
Db2 11 and Db2 12 Db2 Analytics Accelerator Bind Options (SO11807)
CA Plan Analyzer now supports new IBM Db2 Analytics Accelerator for z/OS bind options.
This support is provided through the following BIND and REBIND parameters:
  • ACCELERATIONWAITFORDATA
    Specifies the maximum time that IBM Db2 Analytics Accelerator for z/OS delays a static SQL query while waiting for the replication of committed Db2 data changes that occurred before the query began.
    This new functionality is enabled through the following reports and commands:
    • Package BIND Parameters report
    • Package REBIND Parameters report
    • Bind packages: B, BO, R, RO, BINDPACK, COPYPACK, and REBINDPACK
    • Native SQL procedure packages: ROA and DPLY
    • SQL scalar function packages: ROA and DPLY
  • ACCELERATOR
    Specifies the preferred target accelerator to use for queries that are accelerated in the IBM DB2 Analytics Accelerator for z/OS.
    This new functionality is enabled through the following reports and commands:
    • Package BIND Parameters report
    • Package REBIND Parameters report
    • Bind packages: B, BO, R, RO, BINDPACK, COPYPACK, and REBINDPACK
    • Native SQL procedure packages: ROA and DPLY
    • SQL scalar function packages: ROA and DPLY
Db2 12 REBIND Phase-in Package Copy Support (SO11129)
The Db2 12 rebind phase-in function is now supported. Rebind phase-in allows Db2 to rebind and execute the same package concurrently. During the rebind process, a new package copy is created. After the rebind finishes, new threads can use the new package copy immediately. Existing threads continue to use the phased-out copy that was in use before the rebind.
The new package copies are support in the following reports: Package List, Package Detail, Package Utilities, Identify Problem Packages, Package Dependency, Package Bind Parameters, Package Rebind Parameters, and Object Reporting.
Support for rebind phase-in begins in function level V12R1M505.
Db2 12 Advanced Trigger Packages and CONCENTRATESTMT Support (SO10712)
Db2 12 advanced trigger packages are now supported. You can rebind these packages, modify certain rebind options when rebinding them, switch between advanced trigger package copies, free package copies, drop trigger versions, and drop triggers. This support is provided through the following commands:
  • Line commands R, RO, ROA, F, FO, and FA
  • Primary commands REBINDPACK and FREEPACK
The F and FREEPACK commands replace the DTR and DROPTRIG commands, respectively.
The following Db2 12 BIND and REBIND parameter is also supported:
  • CONCENTRATESTMT
    Specifies whether to treat dynamic SQL with specific literal constants as a separate unique statement in the dynamic cache, or allow it to be shared with an existing statement in the cache.
    This new functionality is enabled through the following reports and commands:
    • Package BIND Parameters report
    • Package REBIND Parameters report
    • Bind packages: B, BO, R, RO, BINDPACK, COPYPACK, and REBINDPACK
    • Advanced trigger packages: R, RO, ROA, and REBINDPACK
    • Native SQL procedure packages: R, RO, ROA, and REBINDPACK
    • SQL scalar function packages: R, RO, ROA, and REBINDPACK
Db2 12 SYSPACKAGE Column Support (SO12067)
CA Plan Analyzer now supports the APREUSE_NO_FL, APREUSE_NO_TS, CONC_STMT, FUNCTION_LVL, and ORIGIN columns in the SYSPACKAGE table.
These columns have been added to the following reports:
  • Package List
  • Package Detail
  • Package Utilities
  • Identify Problem Package
Db2 12 SQL DML Syntax Support
The following Db2 12 SQL DML syntax support is now provided. SQL that contains this syntax can now be processed successfully:
  • BETWEEN temporal period clause in searched DELETE and UPDATE statements
  • FETCH NEXT in SUBSELECT, FULLSELECT, and SELECT INTO
  • FETCH in searched DELETE statements
  • LIMIT as an alternative to FETCH or FETCH OFFSET
  • LISTAGG built-in function (introduced in function level V12R1M501)
  • OFFSET in SUBSELECT, FULLSELECT, and SELECT INTO statements
  • Global variables:
    • CATALOG_LEVEL
    • DEFAULT_SQLLEVEL
    • MERGE USING
      table-reference
      as an alternate way of specifying source data
    • PRODUCTID_EXT
    • TEMPORAL_LOGICAL_TRANSACTION_TIME
    • TEMPORAL_LOGICAL_TRANSACTIONS
    • REPLICATION_OVERRIDE (introduced in function level V12R1M503)
  • Obfuscated statement text in CREATE TRIGGER statements
  • Operands in row-value expressions for basic predicates:
    • < (less than)
    • > (greater than)
    • <= (less than or equal to)
    • >= (greater than or equal to)
Db2 12 EXPLAIN Table Support
The short and long access path reports now support new Db2 12 values in the following PLAN_TABLE columns:
  • PRIMARY_ACCESSTYPE
  • COLUMN_FN_EVAL
  • QBLOCK_TYPE
In addition, the explain table auto-creation function and the Create Explain/Input Tables option now support the new Db2 12 FUNCTION_LEVEL column in the DSN_STATEMNT_TABLE.
Db2 12 Support
IBM Db2 12 for z/OS function levels V12R1M100, V12R1M500, and V12R1M
nnn
are now supported.
Version 20.0 of
CA Plan Analyzer® for DB2 for z/OS
supports the IBM Db2 12 Continuous Delivery Model, which introduces new features in the maintenance stream for faster adoption. Customers who are moving to Db2 12 should upgrade to this version. In Version 19.0,
only
V12R1M100 for migration from Db2 11 and new function level V12R1M500 are supported for Db2 12.
Db2 11 SYSPACKAGE Column Support
Db2 11 SYSPACKAGE columns ARCHIVESENSITIVE, BUSTIMESENSITIVE, DESCSTAT, and SYSTIMESENSITIVE have been added to the following reports:
  • Package List
  • Package Detail
  • Package Utilities
  • Identify Problem Package
Db2 11 Syntax Support
The following Db2 11 syntax support is provided:
  • Special register support:
    • CURRENT ACCELERATOR
    • CURRENT QUERY ACCELERATION WAITFORDATA
  • Correlation clause is now optional for nested table expression, XMLTABLE expression, and collection-derived table.
  • Built-in scalar functions SOAPHTTPNC and SOAPHTTPNV
SQL that contains this syntax can now be processed successfully.
Db2® Spatial Extender Support
SQL that contains the predicate selectivity clause in search conditions is now supported.
SQL that contains this syntax can now be processed successfully.