zOs/JCL/DB2SQL

//A540769D  JOB (CP00,KE50),                                            00010001
//         MSGCLASS=T,TIME=1440,
//         NOTIFY=A540769
//*MAIN CLASS=LOG,SYSTEM=S12
//S01      EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99                       00020001
//SYSTSIN  DD *
    DSN SYSTEM(DBAF)
   RUN PROGRAM(DSNTEP2) PARMS('ALIGN(LHS)') PLAN(DSNTEP2)
//SYSTSPRT DD SYSOUT=*
//*SYSPRINT DD DISP=SHR,DSN=DSN.NAKTST.LIST(COUNTNEU)
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTERM  DD DUMMY
//*SYSIN    DD DISP=SHR,DSN=DSN.NAKTST.JCL(COUNTNEU)
//*SYSIN    DD DISP=SHR,DSN=A540769.WK.SQL(EXPLAIN5)
//SYSIN    DD * DISP=SHR,DSN=A540769.WK.SQL(TTT)
set current sqlid = 'A540769';
delete from plan_table;
explain plan set queryno = 2 for
    SELECT * FROM oA1A.VVV_IDS_STD842
    WHERE INFOCUSTYPE IN (1,2,3)
    AND EVENTFUNCTIONTYPE IN (6,7,10,11,15,20,32,33,36,37,38)
;
explain plan set queryno = 3 for
    SELECT * FROM oA1A.VVV_IDS_STD843
    WHERE INFOCUSTYPE IN (1,2,3)
    AND EVENTFUNCTIONTYPE IN (6,7,10,11,15,20,32,33,36,37,38)
;
explain plan set queryno = 33 for
 SELECT   SYMBOLCSFI,EVENTFUNCTIONTYPE,EFFECTIVEDATE,CFAMOUNTTYPE,
CUMCOUPONNUMBER,
          HELDAMOUNT,UNDINSTR_CSFI,SYMBOLCH,SYMBOLISIN,PAYRATE,
PAYAMOUNT,
 PAYINSTRUMENT_CSFI,PAYINSTRUMENT_I2,PAYINSTRUMENT_CSCURR,
PAYDIRECTIONTYPE,PRORATAPAYRATE,HELDUNITTYPE,
HASMULTIUNDERLYERS,AMOUNTSTATUSTYPE,DESCRIPTION_E,DESCRIPTION_F,
DESCRIPTION_G,DESCRIPTION_I,DESCRIPTION_N,UNDINSTR_CH
FROM oA1A.VVV_XTREQSCANKEY
INNER JOIN oA1A.VVV_IDS_STD843
ON oA1A.VVV_IDS_STD843.INSTRUMENTID = oA1A.VVV_XTREQSCANKEY.TEMP_DBKEY
AND oA1A.VVV_IDS_STD843.INFOCUSTYPE IN (1,2,3)
AND oA1A.VVV_IDS_STD843.EVENTFUNCTIONTYPE
 IN (6,7,10,11,15,20,32, 33,36,37,38)
WHERE OA1A.VVV_XTREQSCANKEY.PARTITION_NUMBER =         2
WITH UR
;
explain plan set queryno = 34 for
 SELECT   SYMBOLCSFI,EVENTFUNCTIONTYPE,EFFECTIVEDATE,CFAMOUNTTYPE,
CUMCOUPONNUMBER,
          HELDAMOUNT,UNDINSTR_CSFI,SYMBOLCH,SYMBOLISIN,PAYRATE,
PAYAMOUNT,
 PAYINSTRUMENT_CSFI,PAYINSTRUMENT_I2,PAYINSTRUMENT_CSCURR,
PAYDIRECTIONTYPE,PRORATAPAYRATE,HELDUNITTYPE,
HASMULTIUNDERLYERS,AMOUNTSTATUSTYPE,DESCRIPTION_E,DESCRIPTION_F,
DESCRIPTION_G,DESCRIPTION_I,DESCRIPTION_N,UNDINSTR_CH
FROM oA1A.VVV_XTREQSCANKEY
INNER JOIN oA1A.VVV_IDS_STD843
ON oA1A.VVV_IDS_STD843.INSTRUMENTID = oA1A.VVV_XTREQSCANKEY.TEMP_DBKEY
AND oA1A.VVV_IDS_STD843.INFOCUSTYPE+0 IN (1,2,3)
AND oA1A.VVV_IDS_STD843.EVENTFUNCTIONTYPE
 IN (6,7,10,11,15,20,32, 33,36,37,38)
WHERE OA1A.VVV_XTREQSCANKEY.PARTITION_NUMBER =         2
WITH UR
;
explain plan set queryno = 35 for
 SELECT   count(*), avg(payamount), min(SYMBOLCSFI)
FROM oA1A.VVV_XTREQSCANKEY
INNER JOIN oA1A.VVV_IDS_STD843
ON oA1A.VVV_IDS_STD843.INSTRUMENTID = oA1A.VVV_XTREQSCANKEY.TEMP_DBKEY
AND oA1A.VVV_IDS_STD843.INFOCUSTYPE+0 IN (1,2,3)
AND oA1A.VVV_IDS_STD843.EVENTFUNCTIONTYPE
 IN (6,7,10,11,15,20,32, 33,36,37,38)
WHERE OA1A.VVV_XTREQSCANKEY.PARTITION_NUMBER =         2
WITH UR
;
explain plan set queryno = 4 for
    SELECT * FROM oA1A.VVV_IDS_STD844
    WHERE INFOCUSTYPE IN (1,2,3)
    AND EVENTFUNCTIONTYPE IN (6,7,10,11,15,20,32,33,36,37,38)
;
explain plan set queryno = 6 for
 SELECT COUNT(*) FROM vdps2.VTINSTRUMENTEVENT
 WHERE INFOCUSTYPE IN (1,2,3)
 AND EVENTFUNCTIONTYPE IN (6,7,10,11,15,20,32,33,36,37,38)
;
select * from  plan_view2
    order by applname, progname, queryNo, qblockno, planno
;
ROLLBACK
;
 select current timestamp from sysibm.sysdummy1;
 SELECT *
FROM oA1A.VVV_XTREQSCANKEY
INNER JOIN oA1A.VVV_IDS_STD843
ON oA1A.VVV_IDS_STD843.INSTRUMENTID = oA1A.VVV_XTREQSCANKEY.TEMP_DBKEY
AND oA1A.VVV_IDS_STD843.INFOCUSTYPE IN (1,2,3)
AND oA1A.VVV_IDS_STD843.EVENTFUNCTIONTYPE
 IN (6,7,10,11,15,20,32, 33,36,37,38)
WHERE OA1A.VVV_XTREQSCANKEY.PARTITION_NUMBER =         2
WITH UR
;
 select current timestamp from sysibm.sysdummy1;