IDENTIFICATION DIVISION. PROGRAM-ID BSY58101. AUTHOR. ED KRISIEWICZ. DATE-WRITTEN. MAY 2007. ENVIRONMENT DIVISION. INPUT-OUTPUT SECTION. FILE-CONTROL. SELECT DISKOA-FILE ASSIGN TO DISKOA. SELECT DISKOB-FILE ASSIGN TO DISKOB. DATA DIVISION. FILE SECTION. FD DISKOA-FILE LABEL RECORDS ARE STANDARD 00150007 RECORDING MODE IS F 00160007 BLOCK CONTAINS 0 RECORDS. 00170007 01 DISKOA-REC PIC X(80). 00180007 FD DISKOB-FILE LABEL RECORDS ARE STANDARD 00150007 RECORDING MODE IS F 00160007 BLOCK CONTAINS 0 RECORDS. 00170007 01 DISKOB-REC PIC X(80). 00180007 WORKING-STORAGE SECTION. *** SQL INCLUDE STATEMENTS: EXEC SQL INCLUDE SQLCA END-EXEC. EXEC SQL DECLARE TOGRP7.PLAN_TABLE TABLE ( QUERYNO INTEGER NOT NULL, QBLOCKNO SMALLINT NOT NULL, APPLNAME CHAR(8) NOT NULL, ... ... PRIMARY_ACCESSTYPE CHAR(1) NOT NULL ) END-EXEC. EXEC SQL DECLARE TOGRP8.PLAN_TABLE TABLE ( QUERYNO INTEGER NOT NULL, QBLOCKNO SMALLINT NOT NULL, APPLNAME CHAR(8) NOT NULL, PROGNAME VARCHAR(128) NOT NULL, ... ... STMTTOKEN VARCHAR(240) ) END-EXEC. *** CAF WORKING STORAGE COPY COBCAFWS. COPY COBCAFLS. *** DSNTIAR WORKING STORAGE COPY COBIARWS. 01 FATAL-RET PIC S9(8) USAGE COMP VALUE +12 . 01 COMMA-CHAR PIC X VALUE ',' . 01 PARM-LOCATION PIC X(16). 01 PARM-SSID PIC X(4). 01 PARM-PLAN PIC X(8). 01 PARM-V7-COLLID PIC X(8). 01 PARM-PROC-DATE PIC X(26). 01 WS-VARIABLES. 05 WS-DOA-LINE-CT PIC S9(4) USAGE COMP VALUE 100. 05 WS-DOA-PAGE-CT PIC S9(4) USAGE COMP VALUE 0. 05 WS-FETCH-COUNT PIC S9(4) USAGE COMP VALUE 0. 05 WS-PREV-PR-QNO PIC S9(9). 05 WS-QNO-COLLID PIC X(18). 05 WS-QNO-PROGRAM PIC X(8). 05 WS-V7-COLLID PIC X(8). 05 WS-QNO-QUERYNO PIC S9(9) USAGE COMP. 05 WS-CREATE-DT PIC X(10). 05 WS-PREV-DT PIC X(10). 05 WS-ERROR-TYPE PIC X(4). 05 WS-PREV-MAX2-QNO PIC S9(9) USAGE COMP. 05 WS-PREV-MAX-QNO PIC S9(9) USAGE COMP. 05 WS-MAX2-QNO PIC S9(9) USAGE COMP. 05 WS-MAX-QNO PIC S9(9) USAGE COMP. 05 WS-MAX2-REL-QNO PIC 999999. 05 WS-MAX-REL-QNO PIC 999999. 05 WS-CURR-IX PIC 999. 05 WS-CURR-CTR PIC 999. 05 WS-PREV-CTR PIC 999. 05 WS-PREV-IX PIC 999. 05 WS-COMP-QRY-IND PIC 9. 05 WS-COMP-PLAN-IND PIC 9. 05 WS-CURR-PREV-TABLE OCCURS 999 TIMES. 10 WS-CURR-QNO PIC S9(9) USAGE COMP. 10 WS-PREV-QNO PIC S9(9) USAGE COMP. 05 WS-CP-QUERYNO PIC S9(9) USAGE COMP. 05 WS-CP-QBLOCKNO PIC S9(4) USAGE COMP. 05 WS-CP-PROGNAME PIC X(8). 05 WS-CP-PLANNO PIC S9(4) USAGE COMP. 05 WS-CP-METHOD PIC S9(4) USAGE COMP. 05 WS-CP-CREATOR PIC X(8). 05 WS-CP-TNAME PIC X(18). 05 WS-CP-TABNO PIC S9(4) USAGE COMP. 05 WS-CP-ACCESSTYPE PIC X(2). 05 WS-CP-MATCHCOLS PIC S9(4) USAGE COMP. 05 WS-CP-ACCESSCREATOR PIC X(8). 05 WS-CP-ACCESSNAME PIC X(18). 05 WS-CP-INDEXONLY PIC X(1). 05 WS-CP-SORTN-UNIQ PIC X(1). 05 WS-CP-SORTN-JOIN PIC X(1). 05 WS-CP-SORTN-ORDERBY PIC X(1). 05 WS-CP-SORTN-GROUPBY PIC X(1). 05 WS-CP-SORTC-UNIQ PIC X(1). 05 WS-CP-SORTC-JOIN PIC X(1). 05 WS-CP-SORTC-ORDERBY PIC X(1). 05 WS-CP-SORTC-GROUPBY PIC X(1). 05 WS-CP-TIMESTAMP PIC X(16). 05 WS-CP-PREFETCH PIC X(1). 05 WS-CP-COLUMN-FN-EVAL PIC X(1). 05 WS-CP-MIXOPSEQ PIC S9(4) USAGE COMP. 05 WS-CP-COLLID PIC X(18). 05 WS-CP-JOIN-TYPE PIC X(1). 05 WS-CP-WHEN-OPTIMIZE PIC X(1). 05 WS-CP-OPTHINT PIC X(8). 05 WS-CP-HINT-USED PIC X(8). EK0722 05 WS-CP-ROWCOUNT PIC S9(9) USAGE COMP. 05 WS-PP-QUERYNO PIC S9(9) USAGE COMP. 05 WS-PP-QBLOCKNO PIC S9(4) USAGE COMP. 05 WS-PP-PROGNAME PIC X(8). 05 WS-PP-PLANNO PIC S9(4) USAGE COMP. 05 WS-PP-METHOD PIC S9(4) USAGE COMP. 05 WS-PP-CREATOR PIC X(8). 05 WS-PP-TNAME PIC X(18). 05 WS-PP-TABNO PIC S9(4) USAGE COMP. 05 WS-PP-ACCESSTYPE PIC X(2). 05 WS-PP-MATCHCOLS PIC S9(4) USAGE COMP. 05 WS-PP-ACCESSCREATOR PIC X(8). 05 WS-PP-ACCESSNAME PIC X(18). 05 WS-PP-INDEXONLY PIC X(1). 05 WS-PP-SORTN-UNIQ PIC X(1). 05 WS-PP-SORTN-JOIN PIC X(1). 05 WS-PP-SORTN-ORDERBY PIC X(1). 05 WS-PP-SORTN-GROUPBY PIC X(1). 05 WS-PP-SORTC-UNIQ PIC X(1). 05 WS-PP-SORTC-JOIN PIC X(1). 05 WS-PP-SORTC-ORDERBY PIC X(1). 05 WS-PP-SORTC-GROUPBY PIC X(1). 05 WS-PP-TIMESTAMP PIC X(16). 05 WS-PP-PREFETCH PIC X(1). 05 WS-PP-COLUMN-FN-EVAL PIC X(1). 05 WS-PP-MIXOPSEQ PIC S9(4) USAGE COMP. 05 WS-PP-COLLID PIC X(18). 05 WS-PP-JOIN-TYPE PIC X(1). 05 WS-PP-WHEN-OPTIMIZE PIC X(1). 05 WS-PP-OPTHINT PIC X(8). 05 WS-PP-HINT-USED PIC X(8). EK0722 05 WS-PP-ROWCOUNT PIC S9(9) USAGE COMP. 05 WS-PREV-REP-DATE PIC X(10) VALUE SPACES. 05 WS-PR1-TIT1. 10 FILLER PIC X(40) VALUE ' '. 10 FILLER PIC X(40) VALUE ' NO. NO. '. 05 WS-PR1-TIT2. 10 FILLER PIC X(40) VALUE 'COLLID PROGRAM V8 BIND TIMESTAMP '. 10 FILLER PIC X(40) VALUE ' V7 BIND TIMESTAMP SQL DIF '. 05 WS-PR1-TIT3. 10 FILLER PIC X(40) VALUE '------- -------- -----------------------'. 10 FILLER PIC X(40) VALUE '--- -------------------------- ---- --- '. 05 WS-INDB2-TIME PIC S9(9)V9(6) USAGE COMP-3. 05 WS-INDB2-CPU PIC S9(9)V9(6) USAGE COMP-3. 05 WS-SQL-CALLS PIC S9(9)V9(6) USAGE COMP-3. 05 PR2-AVG-PFPAGES PIC ZZZZZ9.9. 05 PR2-COLLID PIC X(5). 05 PR2-AVG-GP PIC ZZZZZ9.9. 05 WS-WORK-FIELD PIC S9(9)V9(6) USAGE COMP-3. 05 WS-NUM-SQLS PIC S9(9) USAGE COMP. 05 WS-NUM-SQLS-DIFF PIC S9(9) USAGE COMP. 05 WS-PROC-TIMESTAMP PIC X(26). 05 WS-MAX-BIND-TIME PIC X(26). 05 WS-MAX2-BIND-TIME PIC X(26). 05 WS-PROC-TIMESTAMP-HOLD. 10 WS-PROC-DATE-HOLD PIC X(10). 10 FILLER PIC X(16) VALUE '-00.00.00.000000'. 05 WS-SYSPACK-VARS. 10 WS-SP-COLLID PIC X(18). 10 WS-SP-NAME PIC X(8). 01 WS-DISKOA-DET. 05 PR-COLLID PIC X(7). 05 FILLER PIC X(1) VALUE SPACES. 05 PR-NAME PIC X(8). 05 FILLER PIC X(1) VALUE SPACES. 05 PR-V8-BIND PIC X(26). 05 FILLER PIC X(1) VALUE SPACES. 05 PR-V7-BIND PIC X(26). 05 FILLER PIC X(1) VALUE SPACES. 05 PR-NUM-SQLS PIC ZZZ9. 05 FILLER PIC X(1) VALUE SPACES. 05 PR-NUM-SQLS-DIFF PIC ZZ9. 01 WS-SPACE-LINE. 05 FILLER PIC X(80) VALUE SPACES. 01 WS-PREV-LINE. 05 FILLER PIC X(80) VALUE ' PREVIOUS:'. 01 WS-CURR-LINE. 05 FILLER PIC X(80) VALUE ' CURRENT:'. 01 WS-DATE-LINE. 05 FILLER PIC X(13) VALUE ' BIND DATE : '. 05 WS-PR-REP-DATE PIC X(10). 01 WS-DOB-H1-LINE. 05 PR-DOB-H1-NAME PIC X(8). 05 FILLER PIC X(1) VALUE ' '. 05 PR-DOB-H1-COLLID PIC X(8). 05 FILLER PIC X(16) VALUE ' BIND DATE: '. 05 PR-DOB-H1-MAX-BIND PIC X(16). 05 FILLER PIC X(15) VALUE ' PREVIOUS: '. 05 PR-DOB-H1-MAX2-BIND PIC X(16). 01 WS-DOB-DET-LINE-1. 05 FILLER PIC X(40) VALUE ' M I S'. 05 FILLER PIC X(40) VALUE 'ORTS P C M J O '. 01 WS-DOB-DET-LINE-2. 05 FILLER PIC X(40) VALUE 'QUERY T TABLE AC MT INDEX X NN'. 05 FILLER PIC X(40) VALUE 'NN CCCC R F I N P OPT TABLE INDEX '. 01 WS-DOB-DET-LINE-3. 05 FILLER PIC X(40) VALUE ' NO D NAME TY CL NAME O UJ'. 05 FILLER PIC X(40) VALUE 'OG UJOG F E X T T HINT CREATR CREATR'. 01 WS-DOB-DET-LINE-4. 05 FILLER PIC X(40) VALUE '----- - ------------ -- -- -------- - --'. 05 FILLER PIC X(40) VALUE '-- ---- - - - - - -------- ------ ------'. 01 WS-DOB-PR-DET. 05 WS-PR-DOB-QUERYNO PIC ZZZZ9. 05 FILLER PIC X VALUE SPACE. 05 WS-PR-DOB-METHOD PIC 9. 05 FILLER PIC X VALUE SPACE. 05 WS-PR-DOB-TNAME PIC X(12). 05 FILLER PIC X VALUE SPACE. 05 WS-PR-DOB-ACCESSTYPE PIC X(2). 05 WS-PR-DOB-MATCHCOLS PIC ZZ9. 05 FILLER PIC X VALUE SPACE. 05 WS-PR-DOB-ACCESSNAME PIC X(8). 05 FILLER PIC X VALUE SPACE. 05 WS-PR-DOB-INDEXONLY PIC X. 05 FILLER PIC X VALUE SPACE. 05 WS-PR-DOB-SORTN-UNIQ PIC X. 05 WS-PR-DOB-SORTN-JOIN PIC X. 05 WS-PR-DOB-SORTN-ORDERBY PIC X. 05 WS-PR-DOB-SORTN-GROUPBY PIC X. 05 FILLER PIC X VALUE SPACE. 05 WS-PR-DOB-SORTC-UNIQ PIC X. 05 WS-PR-DOB-SORTC-JOIN PIC X. 05 WS-PR-DOB-SORTC-ORDERBY PIC X. 05 WS-PR-DOB-SORTC-GROUPBY PIC X. 05 FILLER PIC X VALUE SPACE. 05 WS-PR-DOB-PREFETCH PIC X. 05 FILLER PIC X VALUE SPACE. 05 WS-PR-DOB-COLUMN-FN-EVAL PIC X. 05 FILLER PIC X VALUE SPACE. 05 WS-PR-DOB-MIXOPSEQ PIC 9. 05 FILLER PIC X VALUE SPACE. 05 WS-PR-DOB-JOIN-TYPE PIC X. 05 FILLER PIC X VALUE SPACE. 05 WS-PR-DOB-WHEN-OPTIMIZE PIC X. 05 FILLER PIC X VALUE SPACE. 05 WS-PR-DOB-OPTHINT PIC X(8). 05 FILLER PIC X VALUE SPACE. 05 WS-PR-DOB-CREATOR PIC X(6). 05 FILLER PIC X VALUE SPACE. 05 WS-PR-DOB-ACCESSCREATOR PIC X(6). EXEC SQL DECLARE SYSPACK_CSR CURSOR WITH HOLD FOR SELECT SUBSTR(COLLID,1,18), SUBSTR(PROGNAME,1,8), MAX(BIND_TIME) FROM TOGRP8.PLAN_TABLE WHERE COLLID NOT IN ('ADHOCP', 'DBATURN', 'DBACHECK', 'DB2TURN') GROUP BY COLLID, PROGNAME HAVING MAX(BIND_TIME) >= :WS-PROC-TIMESTAMP END-EXEC. EXEC SQL DECLARE QNO CURSOR FOR SELECT COLLID, PROGNAME, QUERYNO FROM SESSION.PACKQNOC ORDER BY COLLID, PROGNAME, QUERYNO END-EXEC. EXEC SQL DECLARE CURR_PLAN CURSOR FOR SELECT P.QUERYNO, QBLOCKNO, SUBSTR(P.PROGNAME,1,8), PLANNO, METHOD, SUBSTR(CREATOR,1,8), SUBSTR(TNAME,1,18), TABNO, ACCESSTYPE, MATCHCOLS, SUBSTR(ACCESSCREATOR,1,8), SUBSTR(ACCESSNAME,1,18), INDEXONLY, SORTN_UNIQ, SORTN_JOIN, SORTN_ORDERBY, SORTN_GROUPBY, SORTC_UNIQ, SORTC_JOIN, SORTC_ORDERBY, SORTC_GROUPBY, TIMESTAMP, PREFETCH, COLUMN_FN_EVAL, MIXOPSEQ, P.COLLID, JOIN_TYPE, WHEN_OPTIMIZE, SUBSTR(OPTHINT,1,8), SUBSTR(HINT_USED,1,8) FROM TOGRP8.PLAN_TABLE P WHERE P.COLLID = :WS-SP-COLLID AND P.PROGNAME = :WS-SP-NAME AND P.BIND_TIME = :WS-MAX-BIND-TIME AND P.QUERYNO = :WS-QNO-QUERYNO ORDER BY P.QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ DESC END-EXEC. EXEC SQL DECLARE PREV_PLAN CURSOR FOR SELECT P.QUERYNO, QBLOCKNO, P.PROGNAME, PLANNO, METHOD, CREATOR, TNAME, TABNO, ACCESSTYPE, MATCHCOLS, ACCESSCREATOR, ACCESSNAME, INDEXONLY, SORTN_UNIQ, SORTN_JOIN, SORTN_ORDERBY, SORTN_GROUPBY, SORTC_UNIQ, SORTC_JOIN, SORTC_ORDERBY, SORTC_GROUPBY, TIMESTAMP, PREFETCH, COLUMN_FN_EVAL, MIXOPSEQ, P.COLLID, JOIN_TYPE, WHEN_OPTIMIZE, OPTHINT, HINT_USED FROM TOGRP7.PLAN_TABLE P WHERE P.COLLID = :WS-V7-COLLID AND P.PROGNAME = :WS-SP-NAME AND P.BIND_TIME = :WS-MAX2-BIND-TIME AND P.QUERYNO = :WS-QNO-QUERYNO ORDER BY P.QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ DESC END-EXEC. LINKAGE SECTION. 01 PARM-AREA. 05 PARM-LENGTH PIC S9(04) COMP. 05 PARM-DATA PIC X(100). PROCEDURE DIVISION USING PARM-AREA. DISPLAY '***** STARTING BSY58101 '. PERFORM 1000-PGM-INIT. EXEC SQL DECLARE GLOBAL TEMPORARY TABLE SESSION.PACKQNOC ( COLLID CHAR(18) NOT NULL, PROGNAME CHAR(8) NOT NULL, QUERYNO INTEGER NOT NULL, ROW_COUNT INTEGER NOT NULL ) END-EXEC. DISPLAY 'AFTER GTT CREATE. SQLCODE: ' SQLCODE. PERFORM 2000-OPEN-SYSPACK-CSR. PERFORM 2001-FETCH-SYSPACK-CSR. PERFORM 2005-PROCESS-SYSPACK-CSR UNTIL SQLCODE IS NOT EQUAL TO 0. PERFORM 2010-CLOSE-SYSPACK-CSR. PERFORM 9000-END-OF-JOB. 1000-PGM-INIT. OPEN OUTPUT DISKOA-FILE. 00860007 OPEN OUTPUT DISKOB-FILE. 00860007 INSPECT PARM-DATA REPLACING ALL LOW-VALUES BY SPACE. MOVE SPACES TO PARM-LOCATION PARM-SSID PARM-PLAN PARM-V7-COLLID PARM-PROC-DATE. UNSTRING PARM-DATA DELIMITED BY ALL SPACE OR COMMA-CHAR INTO PARM-LOCATION PARM-SSID PARM-PLAN PARM-V7-COLLID PARM-PROC-DATE. PERFORM OPEN-DB THRU OPEN-DB-EXIT. EXEC SQL CONNECT TO :PARM-LOCATION END-EXEC. MOVE PARM-V7-COLLID TO WS-V7-COLLID. IF PARM-PROC-DATE IS EQUAL TO SPACES THEN EXEC SQL SELECT CURRENT_TIMESTAMP - 2 YEARS INTO :WS-PROC-TIMESTAMP FROM SYSIBM.SYSDUMMY1 END-EXEC ELSE MOVE PARM-PROC-DATE TO WS-PROC-TIMESTAMP END-IF. WRITE DISKOA-REC FROM WS-SPACE-LINE. WRITE DISKOB-REC FROM WS-SPACE-LINE. WRITE DISKOA-REC FROM WS-PR1-TIT1. WRITE DISKOA-REC FROM WS-PR1-TIT2. WRITE DISKOA-REC FROM WS-PR1-TIT3. WRITE DISKOA-REC FROM WS-SPACE-LINE. 9000-END-OF-JOB. EXEC SQL DROP TABLE SESSION.PACKQNOC END-EXEC. IF SQLCODE IS NOT EQUAL TO ZERO THEN DISPLAY 'DROP SESSION.PACKQNOC SQLCODE: ' SQLCODE END-IF. EXEC SQL DROP TABLE SESSION.PACKQNOP END-EXEC. IF SQLCODE IS NOT EQUAL TO ZERO THEN DISPLAY 'DROP SESSION.PACKQNOP SQLCODE: ' SQLCODE END-IF. EXEC SQL COMMIT WORK END-EXEC. PERFORM CLOSE-DB THRU CLOSE-DB-EXIT. DISPLAY ' END OF BSY58101 '. CLOSE DISKOA-FILE. 00860007 CLOSE DISKOB-FILE. 00860007 STOP RUN. EJECT *** ESTABLISH DB2 CONNECTION. OPEN-DB. DISPLAY '*---------------------------------------' '---------------------------------------*'. DISPLAY '* ' ' *'. DISPLAY '* CONNECTING TO SUBSYSTEM ' PARM-SSID ' *'. MOVE PARM-SSID TO DBCAF-SSID. COPY COBCAFCO. IF DBCAF-RETURN-CODE NOT EQUAL ZERO THEN IF DBCAF-RETURN-CODE NOT EQUAL 200 THEN GO TO CAF-ERROR. *** NOW ESTABLISH THE THREAD, USING THE CONNECTION JUST *** CREATED, ALONG WITH THE SPECIFIED PLAN NAME. DISPLAY '* OPENING THREAD WITH SUBSYSTEM ' PARM-SSID ' USING PLAN ' PARM-PLAN ' *'. DISPLAY '* ' ' *'. DISPLAY '*---------------------------------------' '---------------------------------------*'. DISPLAY SPACE. MOVE PARM-PLAN TO DBCAF-PLAN-NAME. COPY COBCAFOP. IF DBCAF-RETURN-CODE NOT EQUAL ZERO GO TO CAF-ERROR. OPEN-DB-EXIT. EXIT. EJECT 2000-OPEN-SYSPACK-CSR. EXEC SQL OPEN SYSPACK_CSR END-EXEC. IF SQLCODE IS NOT EQUAL TO ZERO THEN DISPLAY 'OPEN SYSPACK SQLCODE: ' SQLCODE END-IF. 2001-FETCH-SYSPACK-CSR. EXEC SQL FETCH SYSPACK_CSR INTO :WS-SP-COLLID, :WS-SP-NAME, :WS-SP-BINDTIME END-EXEC. IF SQLCODE IS NOT EQUAL TO ZERO THEN DISPLAY 'FETCH SYSPACK SQLCODE: ' SQLCODE END-IF. 2005-PROCESS-SYSPACK-CSR. MOVE SPACES TO WS-DISKOA-DET. MOVE WS-SP-COLLID TO PR-COLLID. MOVE WS-SP-NAME TO PR-NAME. MOVE 0 TO WS-NUM-SQLS. MOVE 0 TO WS-NUM-SQLS-DIFF. PERFORM 2100-PROCESS-PACKAGE. MOVE WS-NUM-SQLS TO PR-NUM-SQLS. MOVE WS-NUM-SQLS-DIFF TO PR-NUM-SQLS-DIFF. WRITE DISKOA-REC FROM WS-DISKOA-DET. PERFORM 2001-FETCH-SYSPACK-CSR. 2010-CLOSE-SYSPACK-CSR. EXEC SQL CLOSE SYSPACK_CSR END-EXEC. IF SQLCODE IS NOT EQUAL TO ZERO THEN DISPLAY ' CLOSE SYSPACK, SQLCODE: ' SQLCODE END-IF. 2100-PROCESS-PACKAGE. EXEC SQL DELETE FROM SESSION.PACKQNOC END-EXEC. IF SQLCODE IS NOT EQUAL TO ZERO THEN DISPLAY 'SQLCODE AFTER DELETE FROM SESSIOC: ' SQLCODE END-IF. EXEC SQL COMMIT END-EXEC. IF SQLCODE IS NOT EQUAL TO ZERO THEN DISPLAY 'SQLCODE AFTER 2100-COMMIT: ' SQLCODE END-IF. EXEC SQL SELECT MAX(BIND_TIME) INTO :WS-MAX-BIND-TIME FROM TOGRP8.PLAN_TABLE WHERE PROGNAME = :WS-SP-NAME AND COLLID = :WS-SP-COLLID END-EXEC. IF SQLCODE IS NOT EQUAL TO ZERO THEN MOVE '0001-01-01-00.00.00.000000' TO WS-MAX-BIND-TIME ELSE MOVE WS-MAX-BIND-TIME TO PR-V8-BIND EXEC SQL INSERT INTO SESSION.PACKQNOC SELECT COLLID, PROGNAME, QUERYNO, COUNT(*) FROM TOGRP8.PLAN_TABLE WHERE PROGNAME = :WS-SP-NAME AND COLLID = :WS-SP-COLLID AND BIND_TIME = :WS-MAX-BIND-TIME GROUP BY COLLID, PROGNAME, QUERYNO END-EXEC DISPLAY 'SQLCODE, INSERT GTT, DBACHECK: ' SQLCODE IF SQLCODE = 0 THEN MOVE SQLERRD(3) TO WS-NUM-SQLS DISPLAY 'NUM SQLS: ' WS-NUM-SQLS END-IF END-IF. EXEC SQL SELECT MAX(BIND_TIME) INTO :WS-MAX2-BIND-TIME FROM TOGRP7.PLAN_TABLE WHERE PROGNAME = :WS-SP-NAME AND COLLID = :WS-V7-COLLID END-EXEC. IF SQLCODE IS NOT EQUAL TO ZERO THEN DISPLAY 'BAD MAX2 ' SQLCODE ELSE MOVE WS-MAX2-BIND-TIME TO PR-V7-BIND END-IF. EXEC SQL OPEN QNO END-EXEC. IF SQLCODE IS NOT EQUAL TO ZERO THEN DISPLAY 'BAD OPEN, QNO SQLCODE: ' SQLCODE. EXEC SQL FETCH QNO INTO :WS-QNO-COLLID, :WS-QNO-PROGRAM, :WS-QNO-QUERYNO END-EXEC. IF SQLCODE IS NOT EQUAL TO ZERO THEN DISPLAY 'BAD FETCH, QNO SQLCODE: '. PERFORM 2102-COMP-QUERY UNTIL SQLCODE = +100. EXEC SQL CLOSE QNO END-EXEC. IF SQLCODE IS NOT EQUAL TO ZERO THEN DISPLAY 'BAD OPEN, QNO SQLCODE: '. 2102-COMP-QUERY. EXEC SQL OPEN CURR_PLAN END-EXEC. IF SQLCODE IS NOT EQUAL TO ZERO THEN DISPLAY 'BAD OPEN, CURR PLAN. SQLCODE: '. EXEC SQL OPEN PREV_PLAN END-EXEC. IF SQLCODE IS NOT EQUAL TO ZERO THEN DISPLAY 'BAD OPEN, PREV PLAN. SQLCODE:' SQLCODE. MOVE 0 TO WS-MAX2-REL-QNO. MOVE 0 TO WS-MAX-REL-QNO. MOVE 0 TO WS-COMP-QRY-IND. PERFORM 2201-FETCH-CURR-PLAN. PERFORM 2202-FETCH-PREV-PLAN. PERFORM 2220-CHECK-FOR-DIFF UNTIL WS-MAX2-REL-QNO = 999999 AND WS-MAX-REL-QNO = 999999. EXEC SQL CLOSE CURR_PLAN END-EXEC. IF SQLCODE IS NOT EQUAL TO ZERO THEN DISPLAY 'BAD CLSE, CURR PLAN. SQLCODE: '. EXEC SQL CLOSE PREV_PLAN END-EXEC. IF SQLCODE IS NOT EQUAL TO ZERO THEN DISPLAY 'BAD CLSE, PREV PLAN. SQLCODE: '. IF WS-COMP-QRY-IND IS NOT EQUAL TO 0 PERFORM 4000-ACCESS-PATH-UNEQUAL END-IF. EXEC SQL FETCH QNO INTO :WS-QNO-COLLID, :WS-QNO-PROGRAM, :WS-QNO-QUERYNO END-EXEC. IF SQLCODE IS NOT EQUAL TO ZERO AND SQLCODE IS NOT EQUAL TO +100 THEN MOVE +100 TO SQLCODE END-IF. 2201-FETCH-CURR-PLAN. EXEC SQL FETCH CURR_PLAN INTO :WS-CP-QUERYNO, :WS-CP-QBLOCKNO, :WS-CP-PROGNAME, :WS-CP-PLANNO, :WS-CP-METHOD, :WS-CP-CREATOR, :WS-CP-TNAME, :WS-CP-TABNO, :WS-CP-ACCESSTYPE, :WS-CP-MATCHCOLS, :WS-CP-ACCESSCREATOR, :WS-CP-ACCESSNAME , :WS-CP-INDEXONLY , :WS-CP-SORTN-UNIQ , :WS-CP-SORTN-JOIN , :WS-CP-SORTN-ORDERBY , :WS-CP-SORTN-GROUPBY, :WS-CP-SORTC-UNIQ , :WS-CP-SORTC-JOIN , :WS-CP-SORTC-ORDERBY , :WS-CP-SORTC-GROUPBY, :WS-CP-TIMESTAMP , :WS-CP-PREFETCH , :WS-CP-COLUMN-FN-EVAL , :WS-CP-MIXOPSEQ , :WS-CP-COLLID , :WS-CP-JOIN-TYPE , :WS-CP-WHEN-OPTIMIZE , :WS-CP-OPTHINT , :WS-CP-HINT-USED END-EXEC. IF SQLCODE IS EQUAL TO ZERO THEN ADD 1 TO WS-MAX-REL-QNO ELSE IF SQLCODE IS EQUAL TO +100 THEN MOVE 999999 TO WS-MAX-REL-QNO ELSE DISPLAY 'FETCH CP ERROR, SQLCODE: ' SQLCODE END-IF END-IF. 2202-FETCH-PREV-PLAN. EXEC SQL FETCH PREV_PLAN INTO :WS-PP-QUERYNO, :WS-PP-QBLOCKNO, :WS-PP-PROGNAME, :WS-PP-PLANNO, :WS-PP-METHOD, :WS-PP-CREATOR, :WS-PP-TNAME, :WS-PP-TABNO, :WS-PP-ACCESSTYPE, :WS-PP-MATCHCOLS, :WS-PP-ACCESSCREATOR, :WS-PP-ACCESSNAME , :WS-PP-INDEXONLY , :WS-PP-SORTN-UNIQ , :WS-PP-SORTN-JOIN , :WS-PP-SORTN-ORDERBY , :WS-PP-SORTN-GROUPBY, :WS-PP-SORTC-UNIQ , :WS-PP-SORTC-JOIN , :WS-PP-SORTC-ORDERBY , :WS-PP-SORTC-GROUPBY, :WS-PP-TIMESTAMP , :WS-PP-PREFETCH , :WS-PP-COLUMN-FN-EVAL , :WS-PP-MIXOPSEQ , :WS-PP-COLLID , :WS-PP-JOIN-TYPE , :WS-PP-WHEN-OPTIMIZE , :WS-PP-OPTHINT , :WS-PP-HINT-USED END-EXEC. IF SQLCODE IS EQUAL TO ZERO THEN ADD 1 TO WS-MAX2-REL-QNO ELSE IF SQLCODE IS EQUAL TO +100 THEN MOVE 999999 TO WS-MAX2-REL-QNO ELSE DISPLAY 'FETCH PP ERROR, SQLCODE: ' SQLCODE END-IF END-IF. 2220-CHECK-FOR-DIFF. IF WS-MAX-REL-QNO IS NOT EQUAL TO WS-MAX2-REL-QNO THEN MOVE 1 TO WS-COMP-QRY-IND END-IF. IF WS-CP-QBLOCKNO IS NOT EQUAL TO WS-PP-QBLOCKNO OR WS-CP-PLANNO IS NOT EQUAL TO WS-PP-PLANNO OR WS-CP-METHOD IS NOT EQUAL TO WS-PP-METHOD OR WS-CP-TNAME IS NOT EQUAL TO WS-PP-TNAME OR WS-CP-TABNO IS NOT EQUAL TO WS-PP-TABNO OR WS-CP-ACCESSTYPE IS NOT EQUAL TO WS-PP-ACCESSTYPE OR WS-CP-MATCHCOLS IS NOT EQUAL TO WS-PP-MATCHCOLS OR WS-CP-ACCESSCREATOR IS NOT EQUAL TO WS-PP-ACCESSCREATOR OR WS-CP-ACCESSNAME IS NOT EQUAL TO WS-PP-ACCESSNAME OR WS-CP-INDEXONLY IS NOT EQUAL TO WS-PP-INDEXONLY OR WS-CP-SORTN-UNIQ IS NOT EQUAL TO WS-PP-SORTN-UNIQ OR WS-CP-SORTN-JOIN IS NOT EQUAL TO WS-PP-SORTN-JOIN OR WS-CP-SORTN-ORDERBY IS NOT EQUAL TO WS-PP-SORTN-ORDERBY OR WS-CP-SORTN-GROUPBY IS NOT EQUAL TO WS-PP-SORTN-GROUPBY OR WS-CP-SORTC-UNIQ IS NOT EQUAL TO WS-PP-SORTC-UNIQ OR WS-CP-SORTC-JOIN IS NOT EQUAL TO WS-PP-SORTC-JOIN OR WS-CP-SORTC-ORDERBY IS NOT EQUAL TO WS-PP-SORTC-ORDERBY OR WS-CP-SORTC-GROUPBY IS NOT EQUAL TO WS-PP-SORTC-GROUPBY OR WS-CP-PREFETCH IS NOT EQUAL TO WS-PP-PREFETCH OR WS-CP-COLUMN-FN-EVAL IS NOT EQUAL TO WS-PP-COLUMN-FN-EVAL OR WS-CP-MIXOPSEQ IS NOT EQUAL TO WS-PP-MIXOPSEQ OR WS-CP-JOIN-TYPE IS NOT EQUAL TO WS-PP-JOIN-TYPE OR WS-CP-WHEN-OPTIMIZE IS NOT EQUAL TO WS-PP-WHEN-OPTIMIZE OR WS-CP-OPTHINT IS NOT EQUAL TO WS-PP-OPTHINT OR WS-CP-HINT-USED IS NOT EQUAL TO WS-PP-HINT-USED THEN MOVE 1 TO WS-COMP-QRY-IND END-IF. IF WS-COMP-QRY-IND IS EQUAL TO 1 THEN MOVE 999999 TO WS-MAX-REL-QNO MOVE 999999 TO WS-MAX2-REL-QNO ADD 1 TO WS-NUM-SQLS-DIFF ELSE PERFORM 2201-FETCH-CURR-PLAN PERFORM 2202-FETCH-PREV-PLAN END-IF. 4000-ACCESS-PATH-UNEQUAL. WRITE DISKOB-REC FROM WS-SPACE-LINE. WRITE DISKOB-REC FROM WS-SPACE-LINE. WRITE DISKOB-REC FROM WS-SPACE-LINE. WRITE DISKOB-REC FROM WS-SPACE-LINE. WRITE DISKOB-REC FROM WS-SPACE-LINE. WRITE DISKOB-REC FROM WS-SPACE-LINE. MOVE WS-SP-NAME TO PR-DOB-H1-NAME. MOVE WS-V7-COLLID TO PR-DOB-H1-COLLID. MOVE WS-MAX-BIND-TIME TO PR-DOB-H1-MAX-BIND. MOVE WS-MAX2-BIND-TIME TO PR-DOB-H1-MAX2-BIND. WRITE DISKOB-REC FROM WS-DOB-H1-LINE. WRITE DISKOB-REC FROM WS-SPACE-LINE. WRITE DISKOB-REC FROM WS-SPACE-LINE. WRITE DISKOB-REC FROM WS-DOB-DET-LINE-1. WRITE DISKOB-REC FROM WS-DOB-DET-LINE-2. WRITE DISKOB-REC FROM WS-DOB-DET-LINE-3. WRITE DISKOB-REC FROM WS-DOB-DET-LINE-4. WRITE DISKOB-REC FROM WS-SPACE-LINE. WRITE DISKOB-REC FROM WS-PREV-LINE. EXEC SQL OPEN PREV_PLAN END-EXEC. IF SQLCODE IS NOT EQUAL TO ZERO THEN DISPLAY 'BAD OPEN, PREV PLAN. SQLCODE: '. PERFORM 2202-FETCH-PREV-PLAN. PERFORM 4001-PRINT-PREV UNTIL SQLCODE IS EQUAL TO +100. EXEC SQL CLOSE PREV_PLAN END-EXEC. IF SQLCODE IS NOT EQUAL TO ZERO THEN DISPLAY 'BAD CLSE, PREV PLAN. SQLCODE: '. WRITE DISKOB-REC FROM WS-SPACE-LINE. WRITE DISKOB-REC FROM WS-SPACE-LINE. WRITE DISKOB-REC FROM WS-CURR-LINE. EXEC SQL OPEN CURR_PLAN END-EXEC. IF SQLCODE IS NOT EQUAL TO ZERO THEN DISPLAY 'BAD OPEN, CURR PLAN. SQLCODE: '. PERFORM 2201-FETCH-CURR-PLAN. PERFORM 4002-PRINT-CURR UNTIL SQLCODE IS EQUAL TO +100. EXEC SQL CLOSE CURR_PLAN END-EXEC. IF SQLCODE IS NOT EQUAL TO ZERO THEN DISPLAY 'BAD CLSE, CURR PLAN. SQLCODE: '. 4001-PRINT-PREV. MOVE WS-PP-QUERYNO TO WS-PR-DOB-QUERYNO MOVE WS-PP-METHOD TO WS-PR-DOB-METHOD MOVE WS-PP-TNAME TO WS-PR-DOB-TNAME MOVE WS-PP-ACCESSTYPE TO WS-PR-DOB-ACCESSTYPE MOVE WS-PP-MATCHCOLS TO WS-PR-DOB-MATCHCOLS MOVE WS-PP-ACCESSNAME TO WS-PR-DOB-ACCESSNAME MOVE WS-PP-INDEXONLY TO WS-PR-DOB-INDEXONLY MOVE WS-PP-SORTN-UNIQ TO WS-PR-DOB-SORTN-UNIQ MOVE WS-PP-SORTN-JOIN TO WS-PR-DOB-SORTN-JOIN MOVE WS-PP-SORTN-ORDERBY TO WS-PR-DOB-SORTN-ORDERBY MOVE WS-PP-SORTN-GROUPBY TO WS-PR-DOB-SORTN-GROUPBY MOVE WS-PP-SORTC-UNIQ TO WS-PR-DOB-SORTC-UNIQ MOVE WS-PP-SORTC-JOIN TO WS-PR-DOB-SORTC-JOIN MOVE WS-PP-SORTC-ORDERBY TO WS-PR-DOB-SORTC-ORDERBY MOVE WS-PP-SORTC-GROUPBY TO WS-PR-DOB-SORTC-GROUPBY MOVE WS-PP-PREFETCH TO WS-PR-DOB-PREFETCH MOVE WS-PP-COLUMN-FN-EVAL TO WS-PR-DOB-COLUMN-FN-EVAL MOVE WS-PP-MIXOPSEQ TO WS-PR-DOB-MIXOPSEQ MOVE WS-PP-JOIN-TYPE TO WS-PR-DOB-JOIN-TYPE MOVE WS-PP-WHEN-OPTIMIZE TO WS-PR-DOB-WHEN-OPTIMIZE MOVE WS-PP-OPTHINT TO WS-PR-DOB-OPTHINT MOVE WS-PP-CREATOR TO WS-PR-DOB-CREATOR MOVE WS-PP-ACCESSCREATOR TO WS-PR-DOB-ACCESSCREATOR WRITE DISKOB-REC FROM WS-DOB-PR-DET PERFORM 2202-FETCH-PREV-PLAN. 4002-PRINT-CURR. MOVE WS-CP-QUERYNO TO WS-PR-DOB-QUERYNO MOVE WS-CP-METHOD TO WS-PR-DOB-METHOD MOVE WS-CP-TNAME TO WS-PR-DOB-TNAME MOVE WS-CP-ACCESSTYPE TO WS-PR-DOB-ACCESSTYPE MOVE WS-CP-MATCHCOLS TO WS-PR-DOB-MATCHCOLS MOVE WS-CP-ACCESSNAME TO WS-PR-DOB-ACCESSNAME MOVE WS-CP-INDEXONLY TO WS-PR-DOB-INDEXONLY MOVE WS-CP-SORTN-UNIQ TO WS-PR-DOB-SORTN-UNIQ MOVE WS-CP-SORTN-JOIN TO WS-PR-DOB-SORTN-JOIN MOVE WS-CP-SORTN-ORDERBY TO WS-PR-DOB-SORTN-ORDERBY MOVE WS-CP-SORTN-GROUPBY TO WS-PR-DOB-SORTN-GROUPBY MOVE WS-CP-SORTC-UNIQ TO WS-PR-DOB-SORTC-UNIQ MOVE WS-CP-SORTC-JOIN TO WS-PR-DOB-SORTC-JOIN MOVE WS-CP-SORTC-ORDERBY TO WS-PR-DOB-SORTC-ORDERBY MOVE WS-CP-SORTC-GROUPBY TO WS-PR-DOB-SORTC-GROUPBY MOVE WS-CP-PREFETCH TO WS-PR-DOB-PREFETCH MOVE WS-CP-COLUMN-FN-EVAL TO WS-PR-DOB-COLUMN-FN-EVAL MOVE WS-CP-MIXOPSEQ TO WS-PR-DOB-MIXOPSEQ MOVE WS-CP-JOIN-TYPE TO WS-PR-DOB-JOIN-TYPE MOVE WS-CP-WHEN-OPTIMIZE TO WS-PR-DOB-WHEN-OPTIMIZE MOVE WS-CP-OPTHINT TO WS-PR-DOB-OPTHINT MOVE WS-CP-CREATOR TO WS-PR-DOB-CREATOR MOVE WS-CP-ACCESSCREATOR TO WS-PR-DOB-ACCESSCREATOR WRITE DISKOB-REC FROM WS-DOB-PR-DET PERFORM 2201-FETCH-CURR-PLAN. *** TERMINATE DB2 CONNECTION. CLOSE-DB. MOVE DBCAF-TERM-SYNC TO DBCAF-TERM-OPTION. DISPLAY '*---------------------------------------' '---------------------------------------*'. DISPLAY '* ' ' *'. DISPLAY '* TERMINATING ' PARM-SSID ' THREAD USING "' DBCAF-TERM-OPTION '"' ' *'. COPY COBCAFCL. IF DBCAF-RETURN-CODE NOT EQUAL ZERO GO TO CAF-ERROR. *** DISCONNECT FROM THE LOCAL DB2. UPON COMPLETION OF THIS *** REQUEST, WE ARE COMPLETELY FREE FROM DB2. DISPLAY '* DISCONNECTING FROM ' PARM-SSID ' ' ' *'. DISPLAY '* ' ' *'. DISPLAY '*---------------------------------------' '---------------------------------------*'. COPY COBCAFDI. IF DBCAF-RETURN-CODE NOT EQUAL ZERO GO TO CAF-ERROR. CLOSE-DB-EXIT. EXIT. EJECT *** HANDLE ALL SQL ERRORS HERE DBERROR. COPY COBIARCA. DISPLAY SPACE. DISPLAY '*---------------------------------------' '---------------------------------------*'. DISPLAY '* ' ' *'. DISPLAY '* PROCESSING TERMINATED DUE TO ABOV' 'E ERRORS. *'. DISPLAY '* ' ' *'. DISPLAY '*---------------------------------------' '---------------------------------------*'. MOVE FATAL-RET TO RETURN-CODE. STOP RUN. DBERROR-EXIT. EXIT. EJECT *** HANDLE ALL CAF ERRORS HERE CAF-ERROR. DISPLAY SPACE. DISPLAY '*---------------------------------------' '---------------------------------------*'. DISPLAY '* ' ' *'. DISPLAY '* ERROR IN CALL ATTACHMENT FACILITY,' ' DIAGNOSTICS FOLLOW: *'. DISPLAY '* ' ' *'. DISPLAY '*---------------------------------------' '---------------------------------------*'. COPY COBCAFIN. *** ON OPEN ERRORS, WE CAN GET A MORE SPECIFIC MESSAGE VIA *** THE 'DSNTIAR' INTERFACE. IF DBCAF-FUNCTION = DBCAF-OPEN DISPLAY SPACE PERFORM DBERROR THRU DBERROR-EXIT END-IF. MOVE FATAL-RET TO RETURN-CODE. STOP RUN. CAF-ERROR-EXIT. EXIT.