1 / 77

May 13 th , 2009 •2:45PM to 3:45PM DB2 for z/OS

Session: V07 Bind and Rebind Analysis. Mike Bell HLS Technologies. May 13 th , 2009 •2:45PM to 3:45PM DB2 for z/OS. What is an Access Path?. For each SQL statement DB2 makes a choice about how to process that SQL. For static SQL, the access path is stored in a skeleton cursor table.

mircea
Télécharger la présentation

May 13 th , 2009 •2:45PM to 3:45PM DB2 for z/OS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Session: V07 Bind and Rebind Analysis Mike BellHLS Technologies May 13th, 2009 •2:45PM to 3:45PM DB2 for z/OS

  2. What is an Access Path? For each SQL statement DB2 makes a choice about how to process that SQL. For static SQL, the access path is stored in a skeleton cursor table. These are stored in directory tables DSNDB01.SCT02 and DSNDB01.SPT01 For dynamic SQL the access path is kept in the EDM pool and can be saved in DYNAMIC SQL CACHE.

  3. Nobody complains about performance? Jobs (or transactions) meet their performance goals. Each SQL statement processes the data required in the most cost efficient method. DB2 chooses the access path with the lowest estimated cost. That cost is recorded in PROCMS and PROCSU in DSN_STATEMNT_TABLE for static SQL. What is a Good Access Path?

  4. Anything that generates a midnight call. Anything that generates a “come see me” note on your voice mail. The standard answer: Run RUNSTATS REBIND Repeat until solved What is a Bad Access Path?

  5. Static SQL requires a BIND or REBIND. Dynamic SQL – it depends: Dynamic SQL cache active? Does it match an entry in the cache? Has the cache been flushed (RUNSTATS)? The access path DB2 chooses is dependent on the statistics in the catalog. Both RUNSTATS and RTS. How do you get a Bad Access Path?

  6. Examine the data in PLAN_TABLE. Examine the data in DSN_STATEMNT_TABLE. EXPLAIN(YES) Check SMF records for CPU used per SQL statement. Wait for a phone call. How do you know the Access Path is Bad?

  7. REBIND COLLID(collection.*.(*)) This will attempt to rebind every version of every package. It will include in that list packages that are no longer valid if they exist in the collection. Procedure to get your Packages Rebound on current DB2 Version

  8. SELECT DISTINCT ' REBIND PACKAGE(' CONCAT RTRIM(COLLID) CONCAT ').*.(*))' FROM SYSIBM.SYSPACKAGE ; REBIND PACKAGE(ADBL).*.(*)) Aid and Abet

  9. SELECT DISTINCT ' REBIND PACKAGE(‘ CONCAT RTRIM(COLLID) CONCAT ').' CONCAT RTRIM(NAME) CONCAT '.(' CONCAT RTRIM(VERSION) CONCAT '))' FROM SYSIBM.SYSPACKAGE A WHERE CREATOR <> 'SYSIBM' AND VALID = 'Y' AND VERSION > '' AND CONTOKEN = (SELECT MAX(CONTOKEN) FROM SYSIBM.SYSPACKAGE B WHERE A.LOCATION = B.LOCATION AND A.COLLID = B.COLLID AND A.NAME = B.NAME) REBIND PACKAGE(ADBL).ADB27AC.(1)) Another Aid and Abet

  10. SELECT 'COMPARE PACKAGE ' CONCAT RTRIM(COLLID) CONCAT '.' CONCAT RTRIM(NAME) CONCAT ' IN P390H.PLAN_TABLE TO PREVIOUS' FROM SYSIBM.SYSPACKAGE A WHERE CREATOR = 'P390H' AND VALID = 'Y' AND OPERATIVE = 'Y' AND COLLID = 'TESTCOLLX' AND CONTOKEN = (SELECT MAX(CONTOKEN) FROM SYSIBM.SYSPACKAGE B WHERE A.LOCATION = B.LOCATION AND A.COLLID = B.COLLID AND A.NAME = B.NAME) ORDER BY 1 Now Check to See What Changed

  11. COMPARE PACKAGE TESTCOLLX.TEST01 IN P390H.PLAN_TABLE TO PREVIOUS COMPARE PACKAGE TESTCOLLX.TEST02 IN P390H.PLAN_TABLE TO PREVIOUS COMPARE PACKAGE TESTCOLLX.TEST03 IN P390H.PLAN_TABLE TO PREVIOUS Results of SPUFI

  12. COMPARE PACKAGE TESTCOLLX.TEST01 IN P390H.PLAN_TABLE TO PREVIOUS 1 ACCESS PATH FOR COLLID - TESTCOLLX .TEST01 SQL ID- OLD PLAN_TABLE - RUN DATE 2009/05/09 PCK212I TIMESTAMP USED FOR PREVIOUS COMMAND = 2009050715502529 VERSION = 2005-04-11-18.20.42.913225 BIND_TIME = 2009-05-07-15.50.25.290000 TIMESTAMP USED FOR CURRENT = 2009050909150119 VERSION = 2005-04-11-18.20.42.913225 BIND_TIME = 2009-05-09-09.15.01.190000 PCK206I COMPARE COMPLETE FOR PROGRAM = TEST01 VERSION = 2005-04-11-18.20.42.913225 BIND-TIME = 2009-05-09-09.15.01.190000 PREVIOUS VERSION = 2005-04-11-18.20.42.913225 PREVIOUS BIND-TIME = 2009-05-07-15.50.25.290000 PCK203I STATEMENTS WITH SAME ACCESS PATH 1 STATEMENTS WITH DIFFERENT ACCESS PATH 0 FOR PROGRAM TEST01 PCK204I STATEMENTS WITH MATCHING EXPLAIN 1 STATEMENTS WITHOUT MATCHING EXPLAIN 0 FOR PROGRAM TEST01 IS ‘HOPING TO GOODNESS’ A VALID MIGRATION STRATEGY?

  13. What Is a Reasonable Method for Processing Rebinds? • Identify the old binds. • Identify packages that don’t have current access path information. • Identify packages that don’t have cost information (DSN-STATEMNT_TABLE). • Use PATH CHECK to analyze the access path data for those packages. • Use PATH CHECK to create current access path data for old packages. • Use PATH CHECK to identify safe REBIND’s. • Use PATH CHECK to create HINT’s to make REBIND safe. • Use PATH CHECK to manage ongoing REBIND activity.

  14. Path Check • Path Check 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: • EXPLAIN DBRM • TEST DBRM • COMPARE • REPORT

  15. Read a DBRM. Identify SQL that can be processed by EXPLAIN. REPORT on the access path selected. EXPLAIN DBRM

  16. Read a DBRM. Identify SQL that can be process by EXPLAIN. COMPARE the potential access path to an existing access path. TEST DBRM

  17. COMPARE two sets of access path data from PLAN_TABLE and DSN_STATEMNT_TABLE. Identify differences. COMPARE

  18. Display the access path data from PLAN_TABLE and DSN_STATEMNT_TABLE. REPORT

  19. How old are your Packages? DB2 version 8 was the first release to require REBIND for old packages (pre DB2 V2.3) Does NOT require that you recompile but you do have to BIND or REBIND. OLD DBRM’s are still supported but old packages are not. What information is available – if the last bind was several years ago, the access path has a high probability of change.

  20. How old are you Packages? SELECT BINDYEAR ,COUNT(*) AS COUNT FROM TABLE (SELECT COLLID, NAME, YEAR(BINDTIME) AS BINDYEAR FROM SYSIBM.SYSPACKAGE T1) AS SYSPACKAGE GROUP BY BINDYEAR ;

  21. BINDYEAR COUNT ---------+---------+------ 2004 1 2006 448 2007 243 2008 97 2009 367 SPUFI Results

  22. What does it mean? • This is a fairly normal development Version 8 subsystem. • Majority of the BIND’s were done after the subsystem was initially started. • Not a subsystem that was migrated from an older version. • Notice the lack of activity when Version 9 became available. • What if there were BIND’s that were 10 years old?

  23. Old Bind’s are now hazardous to your System • 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 .

  24. Depends on how you want to structure the work. Start with the oldest packages. Start with packages that are missing data in PLAN_TABLE or DSN_STATEMNT_TABLE. Work forward. What next?

  25. Check for data in Plan_Table SELECT SUBSTR(COLLID,1,18) AS COLLID , SUBSTR(NAME,1,8) AS NAME FROM SYSIBM.SYSPACKAGE A WHERE CREATOR = 'P390H' AND VALID = 'Y' AND OPERATIVE = 'Y' AND VERSION = '' AND NOT EXISTS (SELECT 1 FROM P390H.PLAN_TABLE B WHERE A.COLLID = B.COLLID AND A.NAME = B.PROGNAME AND B.VERSION = '' );

  26. COLLID NAME ---------+---------+--------- DSNTWR81 DSNTWR DSNUT810 DSNUGSQL DSNUTIL DSNUGSQL DSNJAR DSNX9INJ DSNJAR DSNX9RPJ SPUFI Results

  27. Check for data in Plan_Table with Version SELECT SUBSTR(COLLID,1,18) AS COLLID , SUBSTR(NAME,1,8) AS NAME FROM SYSIBM.SYSPACKAGE A WHERE CREATOR = 'P390H' AND VALID = 'Y' AND OPERATIVE = 'Y' AND VERSION > '' AND NOT EXISTS (SELECT 1 FROM P390H.PLAN_TABLE B WHERE A.COLLID = B.COLLID AND A.NAME = B.PROGNAME AND A.VERSION = B.VERSION AND A.BINDTIME = B.BIND_TIME );

  28. COLLID NAME ---------+---------+---------+ DSNWZP DSNWZP ADBL ADB27AC ADBL ADBASW DSNACC DSNACCDL DSNACC DSNACCDS DSNACC DSNACCJF DSNACC DSNACCUC DSNACC DSNACCDF SPUFI Results

  29. EXPLAIN DBRM dbrmname TO PACKAGE testcoll.* in creator.PLAN_TABLE. Read the DBRM. Take explainable SQL and run EXPLAIN. Save the results in the target name. Use Path Check to Identify Current Access Path

  30. SELECT DISTINCT 'EXPLAIN DBRM ' CONCAT RTRIM(NAME) CONCAT ' TO PACKAGE ' CONCAT RTRIM(COLLID) CONCAT ‘.* IN P390I.PLAN_TABLE ‘ FROM SYSIBM.SYSPACKAGE A WHERE CREATOR = 'P390H' AND VALID = 'Y' AND OPERATIVE = 'Y‘ AND NOT EXISTS (SELECT 1 FROM P390H.PLAN_TABLE B WHERE A.COLLID = B.COLLID AND A.NAME = B.PROGNAME AND A.VERSION = B.VERSION ); Use SPUFI to generate Path Check Commands

  31. EXPLAIN DBRM ADB27AC TO PACKAGE ADBL.* IN P390I.PLAN_TABLE EXPLAIN DBRM ADB27SP TO PACKAGE ADBL.* IN P390I.PLAN_TABLE EXPLAIN DBRM ADB2CHK TO PACKAGE ADBL.* IN P390I.PLAN_TABLE Generated Commands

  32. //STEP1 EXEC PGM=PATHCHK //STEPLIB DD DSN=P390H.PDS.LOADX,DISP=SHR // DD DSN=DSN810.SDSNLOAD,DISP=SHR //SYSPRINT DD SYSOUT=* //SYSEXPLN DD SYSOUT=* //SYSCHG DD SYSOUT=* //DBRMIN DD DSN=P390H.DB2V8.DBRMLIB1,DISP=SHR // DD DSN=P390H.DB2V8.DBRMLIB,DISP=SHR //SYSIN DD * Example Run

  33. Input OPTIONS SQLERROR CONNECT TO DB8G SET CURRENT SQLID = 'P390H' EXPLAIN DBRM TDBRM2 TO PACKAGE TESTEXP.* IN P390I.PLAN_TABLE

  34. SYSPRINT PCK031I EXECUTING LICENSED PATH CHECK V3R3 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 PCK225I DSN_STATEMNT_TABLE ESTIMATED COST - EST SVC UNITS 209 CKP228I DSN_STATEMNT_TABLE ESTIMATED COST - EST CPU 108

  35. ACCESS PATH FOR COLLID - TESTEXP .TDBRM2 SQL ID- OLD PLAN_TABLE - RUN DATE 2009/05/04 DBRM TDBRM2 IS VERSION A000A DBRM TDBRM2 HAS A CONNECT TOKEN OF 0C1F0F0F00C14040 DBRM TDBRM2 WAS PRECOMPILED WITH LANGUAGE IBMCOB STATEMENT 294 THIS IS THE NEW SQL STATEMENT. STATEMENT# 294 DECLARE SELECT-1 CURSOR FOR SELECT NAME , TBNAME , COLNO , C OLTYPE , LENGTH FROM SYSCOLUMNS WHERE TBNAME = : H STEP 1 ACCESSES TABLE P390H .SYSCOLUMNS USING A TABLESPACE SCAN. THE ACCESS WILL USE SEQUENTIAL PREFETCH THE TIMESTAMP FOR THIS EXPLAIN IS 2009050409431656 THE TABLE HAS -1 ROWS OF 969 BYTES. DB2 ESTIMATES THIS QUERY WILL USE 108 MILLISECONDS OF CPU TIME DB2 ESTIMATES THIS QUERY WILL USE 209 SERVICE UNITS DB2 WAS FORCED TO USE ESTIMATES BECAUSE OF TABLE CARDINALITY SYSEXPLN

  36. SYSCHG ****************************************************** PCK218I PROGRAM TEST01 HAS A NEW SQL STATEMENT QUERYNO 333 COLLECTION TESTCOLLX VERSION 2005-04-11-18.20.42.913225

  37. Three basic reports. SYSPRINT has data only. SYSEXPLN has SQL text, readable access method, and minimal stats for table and indexes. SYSCHG just has access path changes The data is saved in the PLAN_TABLE specified using the names specified. Summary

  38. Use Path Check to Identify Changes TEST DBRM dbrmname AS PACKAGE testcoll.* in creator PLAN_TABLE. Read the DBRM. Take explainable SQL and run EXPLAIN. Compare the results with an existing access path.

  39. SELECT DISTINCT 'TEST DBRM ' CONCAT RTRIM(NAME) CONCAT ' AS PACKAGE ' CONCAT RTRIM(COLLID) CONCAT '.* IN P390H.PLAN_TABLE ' FROM SYSIBM.SYSPACKAGE A WHERE CREATOR = 'P390H' AND VALID = 'Y' AND OPERATIVE = 'Y' AND COLLID = 'TESTCOLLX' How shall I build the Commands?

  40. SELECT 'TEST DBRM ' CONCAT RTRIM(NAME) CONCAT ' AS PACKAGE ' CONCAT RTRIM(COLLID) CONCAT '.* IN P390H.PLAN_TABLE ' FROM SYSIBM.SYSPACKAGE A WHERE CREATOR = 'P390H' AND VALID = 'Y' AND OPERATIVE = 'Y' AND COLLID = 'TESTCOLLX' AND CONTOKEN = (SELECT MAX(CONTOKEN) FROM SYSIBM.SYSPACKAGE B WHERE A.LOCATION = B.LOCATION AND A.COLLID = B.COLLID AND A.NAME = B.NAME) ORDER BY 1 Alternate Version

  41. TEST DBRM TEST01 AS PACKAGE TESTCOLLX.* IN P390H.PLAN_TABLE One of the Generated Commands

  42. //STEP1 EXEC PGM=PATHCHK //STEPLIB DD DSN=P390H.PDS.LOADX,DISP=SHR // DD DSN=DSN810.SDSNLOAD,DISP=SHR //SYSPRINT DD SYSOUT=* //SYSEXPLN DD SYSOUT=* //SYSCHG DD SYSOUT=* //DBRMIN DD DSN=P390H.DB2V8.DBRMLIB2,DISP=SHR //SYSIN DD * JCL to Process

  43. CONNECT TO DB8G SET CATALOG QUALIFIER = 'SYSIBM' SET CURRENT SQLID = 'PUBLIC' TEST DBRM TEST01 AS PACKAGE TESTCOLLX.* IN P390H.PLAN_TABLE Commands

  44. IN QRYNO M CREATOR TNAME TBNO AC MC CREATOR ACCESSNAME IO SORTUJOG LK PF FN QBNO PLNO MXSQ MJN PG JN OP * 327 0 P390H SYSCOLUMNS 1 I 2 P390H DSNDCX01 N NNNNNNNN IS L 1 1 0 PCK220I DATA CHANGE FOR COLUMN PREFETCH WAS PCK221I DATA CHANGE FOR COLUMN PREFETCH IS NOW L PCK224I DSN_STATEMNT_TABLE ESTIMATED COST CHANGE - OLD EST SVC UNITS 1 CKP227I DSN_STATEMNT_TABLE ESTIMATED COST CHANGE - OLD EST CPU 1 PCK223I DSN_STATEMNT_TABLE ESTIMATED COST CHANGE - NEW EST SVC UNITS 146 CKP226I DSN_STATEMNT_TABLE ESTIMATED COST CHANGE - NEW EST CPU 76 PCK206I TEST COMPLETE FOR PROGRAM = TEST01 VERSION = 2005-04-11-18.34.53.754785 PREVIOUS VERSION = 2005-04-11-18.34.53.754785 PCK203I STATEMENTS WITH SAME ACCESS PATH 1 STATEMENTS WITH DIFFERENT ACCESS PATH 1 FOR PROGRAM TEST01 PCK204I STATEMENTS WITH MATCHING EXPLAIN 2 STATEMENTS WITHOUT MATCHING EXPLAIN 0 FOR PROGRAM TEST01 Results(Not Good)

  45. ACCESS PATH FOR COLLID - TESTCOLLX .TEST01 SQL ID- OLD PLAN_TABLE - PLAN_TABLE RUN DATE 2009/05/06 PCK206I TEST COMPLETE FOR PROGRAM = TEST01 VERSION = 2006-04-24-22.45.29.330423 PREVIOUS VERSION = 2005-04-11-18.34.53.754785 PCK203I STATEMENTS WITH SAME ACCESS PATH 2 STATEMENTS WITH DIFFERENT ACCESS PATH 0 FOR PROGRAM TEST01 PCK204I STATEMENTS WITH MATCHING EXPLAIN 2 STATEMENTS WITHOUT MATCHING EXPLAIN 0 FOR PROGRAM TEST01 Results(Better)

  46. New DDNAME //RBINDOUT REBIND PACKAGE (TESTCOLLX.TEST01.(2006-04-24-22.45.29.330423))- OWNER (PUBLIC)- EXPLAIN(YES) Generated by PATHCHECK because the access path has not changed Trigger is the RBINDOUT ddname Starting Goal is no Access Path Changes

  47. Process REBIND even though there are Access Path changes but depend on the estimated cost in DSN_STATEMNT_TABLE to select acceptable changes. OPTIONS CPUPCT nn Can specify nn = 0 or some other percentage. Next Step

  48. //STEP1 EXEC PGM=PATHCHK //STEPLIB DD DSN=P390H.PDS.LOADX,DISP=SHR // DD DSN=DSN810.SDSNLOAD,DISP=SHR //SYSPRINT DD SYSOUT=* //SYSEXPLN DD SYSOUT=* //SYSCHG DD SYSOUT=* //RBINDOUT DD SYSOUT=* //PBINDOUT DD SYSOUT=* //DBRMIN DD DSN=P390H.DB2V8.DBRMLIB2,DISP=SHR //SYSIN DD * A New DDNAME

  49. 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 OPTIONS CPUPCT nn

  50. OPTIONS CPUPCT 10 CONNECT TO DB8G SET CURRENT SQLID = 'PUBLIC' TEST DBRM TEST01 AS PACKAGE TESTCOLL8.* IN P390H.PLAN_TABLE TEST DBRM TEST02 AS PACKAGE TESTCOLL8.* IN P390H.PLAN_TABLE TEST DBRM TEST03 AS PACKAGE TESTCOLL8.* IN P390H.PLAN_TABLE TEST DBRM TEST04 AS PACKAGE TESTCOLL8.* IN P390H.PLAN_TABLE TEST DBRM TEST05 AS PACKAGE TESTCOLL8.* IN P390H.PLAN_TABLE Sample Input

More Related