IBM Software Group

1. IBM Software Group Performance Prediction for DB2 Upgrades to DB2 z/OS V9. New England DB2 User's group September 17, 2009 Hussaina Husain Certified Consulting I/T Specialist IBM East Region zSeries DB2 Tools Technical Sales

2. Some of the Challenges . . . • Version 8 started the requirement to REBIND. • Required rebind for packages from version 2.3 or older. • Uses an internal flag from the skeleton cursor table. • Version 9 continues with rebind required for version 3.1 . • New optimizer delivered with V8 and DB2 9 • Rebind highly recommended

3. More reasons to Rebind for DB2 9 migration • In CM mode to take advantage: • DISTINCT and GROUP By enhancements to obtain a sort avoidance • Dynamic prefetch functionality • Global query optimization functionality • Enhanced page-range screening functionality • Generalized sparse indexes and in-memory data caching • Dynamic index ANDing for a star join query • To exploit 64-bit addressing for CT and PT • Some plans and packages become invalidated during the enabling-new-function and New Function mode

4. Causes of DB2 Access Path Change • BINDS: • Application Maintenance and Development • Test to Production Migrations • REBINDS: • Updated Catalog Statistics • New Release/Version of DB2 • DB2 Optimizer Maintenance • Table Structure and Index Changes • System Changes (ZPARMS settings) • New Access Paths can be favorable or not

5. Planning for the release. • Understanding how applications currently execute will help isolate the true new version introduced problems via access path regression analysis • Identify access path changes prior to mass rebind • Analyze and tune any undesirable access path changes prior to rebinds • Establish a full complete performance baseline • Average and total resource consumption on system, thread and SQL level • Workload profile

6. Things To Check Before Migration • Is the data in PLAN_TABLE complete? • Is the data in DSN_STATEMNT_TABLE complete? • How many versions are there in SYSPACKAGE and SKPT? • How many plans have DBRM’s instead of packages? • How many plans or packages have not been rebound on the current release? • How many programs have not been precompiled on the current release? • Host variable : • New reserved words • What happens if the source is no longer available or the application (DBRM) is no longer available • Check for QUERYNO clauses in SQL

7. Leveraging the full tools migration solution • Assure that Plan_Tables contain data for packages and Plans while running DB2 V8 • Use EXPLAIN(YES) on BIND/REBINDS • Use DB2 Path CheckerExplaincommands (CKPEXPxx Programs to Generate commands) • Copy plan_table into a “backup” plan_table • Migrate to DB2 V9 Conversion Mode • UseDB2 Path CheckerTEST commands to check for Changes • Feed statements into DB2 SQL Performance Analyzerfor analysis & tuning • Adjust/generate statistics and retry TEST into secondary plan table • Execute REBIND EXPLAIN(YES) if comfortable with result • Run DB2 Path CheckerCOMPARE command to assure expected path chosen • Repeat steps as needed before issuing Rebinds in subsequent migration modes (Enabling, and New Function Modes) • Run DB2 Bind Manager “Catalog Cleanup” to find packages without load libraries

8. Integration of Bind Manager, Path Checker & SQL PA

9. DB2 Bind Manager Safely by-passes the DB2 bind process for code changes that do not alter existing SQL structures in an application • Automatically eliminates unnecessary binds, processing only the necessary DB2 binds • Includes a “Catalog cleanup” feature that compares bound packages in a DB2 subsystem to one or more load libraries and generates FREE commands to remove obsolete or unreferenced packages from the catalog • Maintains DB2 system integrity using the DBRM Checker feature • Helps determine whether a bind is required by comparing the consistency tokens for a plan in a DBRMLIB with the corresponding tokens in the DB2 catalog tables • Insures that the correct DBRMLIB is being accessed during the compile process because DB2 Bind Manager does not connect to DB2 to check DBRM validity • Lets you (re)build DBRMs from the catalog Helps save CPU – Every time programmers do a bind, there is a cost associated with it.If programmers can avoid doing binds, they can save CPU which equates to an overall lower total cost of ownership.

10. DB2 Path CHECKER • Path CHECKER has been available for 10 years. • Is a tool to compare access paths for SQL. • Has a number of features that make analyzing BIND and REBIND activity easier. • Commands available are: • Report Access Paths after Bind (REPORT) • Report Access Paths without a Bind (EXPLAIN) • Optionally populate the Plan Table • Compare Access Path after Binds (COMPARE) • Compare Access Path without Bind (TEST) • Make Hint to preserve old Access Path

11. PATH CHECKER Review • It is designed to test and compare the access data for large numbers of programs • It can directly compare • V8 to V9 plan table even though a column has been added in V9 • V7 toV8 plan table even though the column lengths are different and some columns have changed to VARCHAR • It will accept and process 3 part names if the DDF connections are available but performance is affected by normal DDF constraints

12. DB2 Path Checker Process DBRMLIB Or DB2 Catalog BACKUP PLAN PLAN TABLE TABLE DBRM CHECKER EXPLAIN CHANGES REPORT* History table ANLOUT* REPORT*

13. Use Path CHECKER to Identify Access Path Changes without Bind/rebind TEST DBRM: TEST DBRM PROGNAME AS PACKAGE COLLID.* IN OWNERID.PLAN_TABLE {START WITH pgm2} {BEFORE tmstp} {FROM CATALOG} Read a DBRM or DB2 Catalog Identify SQL that can be process by EXPLAIN. COMPARE the potential access path to an existing access path.

14. Path CHECKER to Identify Current Access Path and to Create missing plan Table entries EXPLAIN DBRM dbrmname TO PACKAGE testcoll.* in creator.PLAN_TABLE. Read the DBRM or DB2 catalog Take explainable SQL and run EXPLAIN. Save the results in the target table (establishes a baseline). Populate both PLAN_TABLE and DSN_STATEMNT_TABLE Report on rebind issues without killing the existing package OPTIONS SQLERROR

15. Now Check to See What Changed after Rebind/Bind COMPARE (PLAN|PACKAGE) qualifier.pgm1 IN tablename1 TO qualifier2.* IN tablename2 {START WITH pgm2} {BEFORE tmstp | PREVIOUS | PREVIOUS version} COMPARE two sets of access path data from PLAN_TABLE and DSN_STATEMNT_TABLE. Identify differences.

16. Path Checker Options Options REPORTCHG | REPORTALL changes only or all MATCHSQL1| MATCHSEQUENCE | MATCHQUERYNO (NO)MATCHCREATOR (NO)SQLERROR (NO)CATALOGSQL report SQL statement from catalog (NO)REPORTCOSTGT report if path or estimated costs (PROCSU in DSN_STATEMNT_TABLE) changes HISTORY TABLE creator.HIST_TABLE CCSID-target CCSID for conversion CREATE TABLES to create PLAN_TABLE and DSN_STATEMNT_TABLE if the tables are not predefined

17. Path Checker Output Outputs Default Summary Report - 1 liner (SYSPRINT) Detailed Report - optional (SYSEXPLN DD) Summary of Access Path Changes Report (SYSCHG) Changed SQL statements for SQLPA - (ANLOUT) Change Summary is inserted into HIST_TABLE Rebind statements from TEST (RBINDOUT) for no access path changes or cost is within CPUPCT specified boundary Rebind statements from TEST (PBINDOUT) for access path changes or cost exceeds CPUPCT specified boundary

18. Path Checker History table • Can keep a history table of the results of COMPARE and TEST processing • Captures the old and new PROCSU from DSN_STATEMNT_TABLE so you can identify large swings in estimated cost

19. Path Checker- SYSPRINT CKP031I EXECUTING LICENSED PATH CHECKER V3R1 DEBUG LISTSQL OPTIONS SQLERROR CONNECT TO DB8G SET CURRENT SQLID = 'P390H' EXPLAIN DBRM TDBRM2 TO PACKAGE TESTEXP.* IN P390I.PLAN_TABLE ACCESS PATH FOR COLLID - TESTEXP .TDBRM2 SQL ID- OLD PLAN_TABLE - RUN DATE 2009/05/04 IN QRYNO M CREATOR TNAME TBNO AC MC CREATOR ACCESSNAME IO SORTUJOG LK PF FN QBNO PLNO MXSQ MJN PG JN OP 294 0 P390H SYSCOLUMNS 1 R 0 N NNNNNNNN IS S 1 1 0 CKP225I DSN_STATEMNT_TABLE ESTIMATED COST - EST SVC UNITS 209 CKP228I DSN_STATEMNT_TABLE ESTIMATED COST - EST CPU 108

20. Path Checker- SYSEXPLN

21. Path Checker- SYSCHG

22. Path Checker- SYSPRINT -Results (Not Good) IN QRYNO M CREATOR TNAME TBNO AC MC CREATOR ACCESSNAME IO * 327 0 P390H SYSCOLUMNS 1 I 2 P390H DSNDCX01 CKP220I DATA CHANGE FOR COLUMN PREFETCH WAS CKP221I DATA CHANGE FOR COLUMN PREFETCH IS NOW L CKP224I DSN_STATEMNT_TABLE ESTIMATED COST CHANGE - OLD EST SVC UNITS 1 CKP227I DSN_STATEMNT_TABLE ESTIMATED COST CHANGE - OLD EST CPU 1 CKP223I DSN_STATEMNT_TABLE ESTIMATED COST CHANGE - NEW EST SVC UNITS 146 CKP226I DSN_STATEMNT_TABLE ESTIMATED COST CHANGE - NEW EST CPU 76 CKP206I TEST COMPLETE FOR PROGRAM = TEST01 VERSION = 2005-04-11-18.34.53.754785 PREVIOUS VERSION = 2005-04-11-18.34.53.754785 CKP203I STATEMENTS WITH SAME ACCESS PATH 1 STATEMENTS WITH DIFFERENT ACCESS PATH 1 FOR PROGRAM TEST01 CKP204I STATEMENTS WITH MATCHING EXPLAIN 2 STATEMENTS WITHOUT MATCHING EXPLAIN 0 FOR PROGRAM TEST01

23. Path Checker- Sample SYSPRINT IN QRYNO M CREATOR TNAME TBNO AC MC CREATOR ACCESSNAME IO SORTUJOG LK PF FN QBNO PLNO MXSQ MJN PG JN OP * 327 0 PUBLIC SYSCOLUMNS 1 I 2 PUBLIC DSNDCX01 N NNNNNNNN IS 1 1 0 CKP220I DATA CHANGE FOR COLUMN MATCHCOLS WAS 0 CKP220I DATA CHANGE FOR COLUMN PREFETCH WAS S CKP221I DATA CHANGE FOR COLUMN MATCHCOLS IS NOW 2 CKP221I DATA CHANGE FOR COLUMN PREFETCH IS NOW CKP224I DSN_STATEMNT_TABLE ESTIMATED COST CHANGE - OLD EST SVC UNITS 38 CKP227I DSN_STATEMNT_TABLE ESTIMATED COST CHANGE - OLD EST CPU 20 CKP223I DSN_STATEMNT_TABLE ESTIMATED COST CHANGE - NEW EST SVC UNITS 1 CKP226I DSN_STATEMNT_TABLE ESTIMATED COST CHANGE - NEW EST CPU 1 CKP206I TEST COMPLETE FOR PROGRAM = TEST01 VERSION = 2006-04-24-22.45.29.330423 PREVIOUS VERSION = 2007-11-09-13.47.05.581274 CKP203I STATEMENTS WITH SAME ACCESS PATH 1 STATEMENTS WITH DIFFERENT ACCESS PATH 1 FOR PROGRAM TEST01 CKP204I STATEMENTS WITH MATCHING EXPLAIN 2 STATEMENTS WITHOUT MATCHING EXPLAIN 0 FOR PROGRAM TEST01 CKP219I STATEMENTS OVER CPUPCT 0

24. Path Checker OPTIONS REPORTCOSTGT PATH CHECKER compares the cost changes even if the Access Path stays the same. To show those cost changes: OPTIONS REPORTCOSTGT Will show Access Paths that did not change but the estimated cost in DSN_STATEMNT_TABLE changed.

25. Path Checker OPTIONS CPUPCT nn RBINDOUT will have REBIND command for TEST command with no access path changes TEST command where the estimated PROCMS is less than the old PROCMS * (1+CPUPCT) for every changed ACCESS PATH PBINDOUT will have REBIND commands for hazardous REBIND’s Every TEST command will generate a REBIND

26. Path Checker- Another Option OPTIONS CPUPCT2 for when you only want to create REBIND packages control card that will have Access Path changes. Ignore packages that do not have access path changes. If the estimated cost for every SQL statement is less than the percentage specified, put the REBIND in RBINDOUT. If the estimated cost for one SQL statement is greater than the percentage specified, put the REBIND in PBINDOUT. This is a safe method to keep your Access Paths current without automatic Rebind.

27. More Features of Path Checker What if DBRM’S are Missing Usually safe assumption the SQL in SYSSTMT or SYSPACKSTMT is valid. Optional clause for EXPLAIN and TEST commands ‘FROM CATALOG’. Supports use of Shadow Catalog Makes Existing Access Path a Hint Constructs Path Checker commands from Bind/Rebind control cards

28. Reasonable Method for Processing Binds/Rebinds • Use PATH CHECKER to analyze the access path data • Use PATH CHECKER to create current access path data for old packages • Use SQL Performance Analyzer to get the access path details, cost and tuning advice • Use PATH CHECKER to identify safe REBIND’s • Use PATH CHECKER to manage ongoing Bind/REBIND activity • Helps determine access path changes so you can make adjustments before putting the application into production - thereby avoiding performance issues which could impact the bottom line

29. SQL Performance Analyzer To predict cost and performance of SQL queries without executing them • Evaluates SQL cost and performance • Gives recommendations for tuning the SQL for maximum performance • Provides access path information…which road it is going to take to get the data • Used with DB2 Path Checker, the tool passes the statements with different access paths to get the cost and more detailed explain material • Reduces the escalating costs of database queries Improves the quality of SQL code which results in better performance which in turn allows a higher transaction rate and more throughput, which results in better profitability.

30. What else can SQL PA do? • Enhanced EXPLAIN report: • Catalog Statistics • Access Path Information • RI Relationships • Key ADVICE on each SQL statement: • Warnings and Alerts • Guidelines and Recommendations • Performance Notes and Good News • What If Analysis • SQLPA teaches users how to write better SQL

31. And more… from SQLPA • Provides a detailed execution “forecast” report: • Breaks down SQL by time spent in DB2 components - Wait Times and Bottlenecks, Path Lengths and I/O Types • One line summary for each SQL statement evaluated • Recap of costs • Quick eye catcher for problem queries • Acts as a GOVERNOR for Static or Dynamic SQL: - in QMF, as a preemptive Exit - in DRDA and IMS or CICS, via a Stored Procedure call

32. Where does SQL PA run? • In BATCH, as normal z/OS job • In TSO, under ISPF interface • From SPUFI, TSO/ISPF edit session • In QMF, as a Governor Intercept • In anyDB2 application, via a Stored Procedure call

33. How much flexibility does SQLPA have? • Users can vary the test catalog statistics, via Updates and Inserts and the RUNSTATS utility or importstatistics from another DB2 subsystem • Users can emulateproduction volumes in the test catalog, and fine tune applications while still in early design and development stages • Users can also vary the Parameters: • User Parms describe the Application and DB2 scenarios • Installation Parms describe the Configuration & Environment

34. SQL PA processing task

35. Process SQL in a PLAN using existing plan records If a plan or package was bound with EXPLAIN (YES) option, choice is given to show the plan generated at bind or issue a dynamic explain

36. Perform What-If on a single SQL statement(slide 1 of 2) Use the line command W to perform the what-if.

37. DB2 SQL PERFORMANCE ANALYZER Architecture DB2 Catalog SQL/PA ISPF Edit/Browse Cost Summary Report QM/Path Checker ANLOUT DBRM(s) / Seq. File / PDS Plan/package QMF saved queries SQL/PA Batch Job Query Limit Report SQL/PA ANLPGMxx ANL4QMFxx- single SQL statements Governor exit QMF Enhanced Explain Report Generic or Private Plan Tables SQL/PA SUBSYSTEM PARMS SQL/PA PARMS ANLCNTL ANLPARM Application Stored Procedure Call (Local/Remote) Detailed Trace Report uses CAF or WLM

38. Horizontal & vertical integration from OMPE perspective … OMEGAMON XE for Mainframe Networks / for zNetview OMEGAMON XE for CICS OMEGAMON XE for IMS OMEGAMON XE for z/OS …. Tivoli OMEGAMON Monitors DB2 OMEGAMON XE DB2 SQL PA for DB2 PE Query Monitor Visual Explain OSC Control Center Optimization Expert DB2 Path Checker Information Management Tools => DWL or additionally using OMEGAMON DE (Dashboard Edition)

39. Tivoli Omegamon XE for DB2 Performance Expert (OMPE) can assist with real time and batch system monitoring OMEGAMON • Cross-zSeries monitoring • Consistent “look and feel” • Web browser, 3270 VTAM • Integrated “dashboard” • Online monitoring DB2 PM/PE/BPA • In depth Reporting • DB2 Monitoring • Performance DB • Expert analysis • Buffer Pool Analysis • DB2 Connect Monitoring The expertise of two leaders in the industry have come together!

40. OMEGAMON XE For DB2 PM/PE Identify, quantify excessive resource usage on a system, plan and package basis Object Analysis • I/O & getpage analysis • Correlate activity by object & applications Lock Conflicts Near-Term Historical • Near-term history online Historical Analysis • Batch reporting • XE Historical analysis • DB2Plex Monitoring View • View CF structures • Global lock analysis Automation capabilities Real Time Thread Analysis • Thread performance (elapsed, CPU, getpage info) • Thread Detail (lock detail, SQL detail, plan & package level • Triggers, Procedures, & UDFs Real Time – DB2 subsystem • Virtual Pool & EDM Pool analysis • Pool performance • Pool snapshot detail • Locking & Logging Application Trace Facility • Detailed performance tracing Choice Of Interfaces (TEP XE, PE, 3270 Classic & CUA)

41. OMEGAMON XE For DB2 PE Provides: • World Class Batch Reporting – In-depth problem analysis • Performance Warehouse – (PEclient GUI) • Expert analysis (ROT and SQL Performance queries) • DB2 Connect Monitoring (Classic, TEP, PE GUI) • More granular snapshot history via the PE GUI for online ad-hoc problem analysis • Buffer Pool Analysis

42. OMPE Reporting for trend analysis Application A Dist.Appl A DB2 DB2 Connect Dist.Appl B Application B IFI OPx Dist.Appl C Application C DB2 event trace processing • Post processing analysis (Report / Trace) • Statistics • Accounting • Deep dive with performance traces • SQL Activities • Locking Activities • I/O Activities • Audit • Record Trace • Explain • Exception Reports • Trend analysis SMF GTF Batch Historical (Collector &) Reporter DB2 Load PWH / PDB Reports

43. Broad Flexibility in Trace Collection, Reporting, saving to DBs DB2 event trace processing DB2 Trace collection Processing / Reporting Performance DB archiving SMF GTF Job - Scheduler Historical Reporting Programs REPORT TRACE O P - B u f f e r BatchCRD Seq PDB DB2 Load Utility VSAM SAVE ISPF CRD Seq PWH CRD & SQL Act. Seq FILE PWH VSAM PE SERVER Seq Near-Term History VSAM PWH Client VSAM ATF

44. Expert Analysis using Rule-of-Thumb and Expert Queries to detect performance issues Predefined expert rules • Database table column wizard • Point and drop support Analyze using • Single rule • Cluster of rules • Zoom-in

45. OMPE-PWH-Analysis Support Performance Queries

46. OMPE-PWH-Analysis Support – Performance Queries

47. OMPE-Buffer Pool Analysis • Collects buffer pool data • as summary or detailed data • continuously or in sampling mode • in Online and Batch • Generates various reports and displays results in multiple formats for BP and GBP (including graphical end-user interface) • Provides expert knowledge and recommendations • Recommends object placements, BP size & thresholds • Generates ALTER statements for the recommendation • Provides simulation for planned changes • Long-term analysis to detect trends, hourly, daily, and weekly peaks, repetitive performance pattern, unbalanced resource usage • Makes it easy to tune your buffer pools

48. More Control of your System and Application Performance PE Client ISPF Online Monitor Classic Interface Tivoli Enterprise Portal

49. Tivoli Enterprise Portal (XE Web browser Interface) Easy to use Browser controls Plug and Play components Personalized Views View Zoom Splitter controls Intelligent Linking Persistent customized workspaces

50. OMPE-Situation Analysis What are the details? What is the problem? Any expert advice? Any Predefined Actions?