IBM DB2 - The Ultimate Database for Cloud, Analytics & Mobile
Abstract: As of September 30th, of 2018, IBM Db2 Path Checker will be at “end of service”. This post covers topics related to migration from IBM Db2 Path Checker to IBM Db2 SQL Performance Analyzer.
For existing Path Checker customers, contact your IBM representative for information on the transition from Path Checker to SQL Performance Analyzer.
To ease the migration path for Path Checker users, IBM Db2 SQL Performance Analyzer R510 has functions that will help you perform many of the same analysis tasks you formerly performed using Path Checker.
The TEST and COMPARE functions have been migrated from IBM Db2 Path Checker to SQL PA R510, and since they are new to SQL PA, they are being actively enhanced and improved by the SQL PA development team.
Among other benefits, of key interest to Path Checker users might be that SQL PA is also able to explain packages to produce access paths, plus provides a “What If?” feature that allows iterative modeling of SQL Statement changes.
As part of migrating to SQL PA, you might be looking for some of your favorite features that may or may not have been ported from Path Checker. As you might expect, the functionality may not look exactly as it did in Path Checker. For example, the “TO PREVIOUS” option of COMPARE from Path Checker can be found on the COMPARE panel of SQL PA as the “Compare to previous?” field. If “Y” is entered for that field, SQL PA will automatically compare the most current version of the package to the immediately previous instance of the same package version in the same set of explain tables just like the “TO PREVIOUS” option in Path Checker.
As another example, if you used the Path Checker-provided capability to name a PLAN_TABLE virtually anything, there is a better way to do this. IBM Db2 does not write access paths to these explain tables having non-standard names. Likewise, IBM Db2 cannot retrieve access path hints or overrides from these explain tables having non-standard names. SQL PA can access these non-standard explain table names using the IBM Db2 ALIAS capability. As an example, if a customer created and populated a table having a format like PLAN_TABLE but named 'PRIOR_PLAN_TABLE', the access path information can be accessed by SQL PA via an ALIAS using standard names and a new SCHEMA by issuing these commands:
"CREATE ALIAS SYSDBA2.PLAN_TABLE FOR SYSDBA.PRIOR_PLAN_TABLE;"
"CREATE ALIAS SYSDBA2.DSN_STATEMNT_TABLE
With an ALIAS in place, you can refer to any appropriately formatted table from SQL PA, and COMPARE, TEST, and EXPLAIN will work with no further customization. On the COMPARE or TEST panel, enter the new SCHEMA as the SQLID to select the non-standard table names. To set the schema for EXPLAIN, the SETPLAN option controls whether the owning SQLID of the PLAN_TABLE can be set. If you are using generic plan tables (SETPLAN value of “GENERIC” or “NOTALLOW”), selecting an aliased table is not allowed. Otherwise, if SETPLAN is set to "ALLOW", you can use option 3.2 to set the SQLID to your new schema. You may then use option 1.2 (EXPLAIN) to populate the aliased explain tables from a DBRM.
Note that both the PLAN_TABLE and the DSN_STATEMNT_TABLE must exist for TEST and COMPARE functionality. Path Checker would use a DSN_STATEMNT_TABLE if it was available, but currently SQL PA requires both a PLAN_TABLE and a DSN_STATEMNT_TABLE so that cost information may be compared. If you do not have a DSN_STATEMNT_TABLE that matches your PLAN_TABLE, just create an empty DSN_STATEMNT_TABLE. The empty table will impair cost comparison, but will not prevent access path comparison.
As TEST and COMPARE are new functionality in SQL PA, the development team is strongly considering any and all enhancements coming from Path Checker users that are migrating to SQL PA. If you feel that SQL PA could be improved by migrating additional Path Checker capabilities, open an enhancement request by navigating to "https://www.ibm.com/developerworks/rfe/", selecting “Analytics Platform" as the brand, and selecting “DB2 SQL Performance Analyzer for z/OS" as the product. Click on "Submit" and follow the prompts. Your input is welcome.
Questions regarding the TEST and COMPARE features should be directed to the author. Other questions regarding IBM’s Db2 performance tooling can be directed to your IBM representative.