zOs/WK/OPTHIDO
$#:
$*( import / export statement level opthints
fun = i --> import optHints
1) unload from $rz/$dbSys:$cre.dsn_userQuery + .plan_table
2) load replace into rz4/dp4g:$cre.dsn_userQuery und .plan_table
3) insert into oa1p.tqz080OptHintQuery + tqz081OptHintPlan
mit $tst und $desc
fun = e --> export optHints and bind Query
1) unload from rz4/dpp4g:oa1p.tqz080OptHintQuery+ .tqz081OptHintPlan
where explain_time = $eTst
2) load replace into $rz/$dbSys:$cre.dsn_userQuery + .plan_table
3) $rz/$dbSys BIND QUERY EXPLAININPUTSCHEMA('$cre')
fun = h --> import history
1) und 2) wie fun = i
dann manuell mit optHihiT tqz08* inserten
$*)
rz=RZY
dbSys=DE0G
fun = e
desc = ybpSuch langläufer - 2. joinSeq
eTst = 2014-09-05-11.24.16
user =- userId()
cre = $user
pre = $user.TMPOPTHK.$rz.$dbSys
job = ${user}K
tst =- f('%t s')
$#@
if $fun == 'i' | $fun == 'h' then $@[
$@jc
$@iUnload
$>$rz/intRdr
$<> $** wait 5 seconds and hope job has allocated unloads
$** that remobe job has already allocated unloads
call sleep 5
$<>
$@jc
$@%[copy $rz RZ4$]
$@load
$>intRdr
$]
else if $fun == 'e' then $@=[
$** $= pre =- $pre'.'f('%tSM', $eTst)
$@jc
$@eUnload
$@%[sub }c $]
//* copy must be in separate job
//* otherwise jes3 complains about non existing datasets
//* which have by dynamically create by Unload
$@%[copy RZ4 $rz $]
$@%[sub }l $rz $]
//* load and bind query in remote RZ
$@load
}l
}c
$>intRdr
$]
else $@[
call err 'bad fun='$fun
$]
$;
$proc $@=/jc/
//$job JOB (CP00,KE50),'DB2 REO',
// MSGCLASS=T,TIME=1440,
// NOTIFY=&SYSUID,REGION=0M,SCHENV=DB2
//*MAINCLASS=LOG
$/jc/
$proc $@=/sub/
$@ parse arg , dlm subRz
$= dlm =- dlm
$= subRz =- subRz
//************************************* submit job to $subRz
//SUB EXEC PGM=IEBGENER submit job to $subRz
//SYSPRINT DD SYSOUT=*
$@[ if $subRz == '' then $@=[
//SYSUT2 DD SYSOUT=(,INTRDR)
$] else $@=[
//SYSUT2 DD SUBSYS=(CSM,'SYSTEM=$subRz,SYSOUT=(A,INTRDR)')
$]
$]
//* achtung, dlm höchstens 2 Zeichen lang
//SYSUT1 DD *,DLM='$dlm'
$@jc
$/sub/
$proc $@=/util/
$@ parse arg , stp1 dbS1
$= stp1 =- stp1
$= dbS1 =- dbS1
//************************************* db2Utility $stp1 fun=$fun
//$stp1 EXEC PGM=DSNUTILB,TIME=1440,
// PARM=($dbS1,'$job.$stp1'),
// REGION=0M
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSTEMPL DD DSN=$dbS1.DBAA.LISTDEF(TEMPL),DISP=SHR
//SYSIN DD *
TEMPLATE QU DSN($pre.QUERY.UNL)
DATACLAS (NULL30) MGMTCLAS(COM#E005)
SPACE (10,120) CYL
TEMPLATE QP DSN($pre.QUERY.PUN)
DATACLAS (NULL12) MGMTCLAS(COM#E005)
SPACE (1,10) TRK
TEMPLATE PU DSN($pre.PLAN.UNL)
DATACLAS (NULL30) MGMTCLAS(COM#E005)
SPACE (10,120) CYL
TEMPLATE PP DSN($pre.PLAN.PUN)
DATACLAS (NULL12) MGMTCLAS(COM#E005)
SPACE (1,10) TRK
$/util/
$proc $@=/del/
//************************************* delete datasets
//DEL EXEC PGM=IEFBR14,TIME=1440
//QP DD DISP=(MOD,DELETE),
// DSN=$pre.QUERY.PUN
//QU DD DISP=(MOD,DELETE),
// DSN=$pre.QUERY.UNL
//PP DD DISP=(MOD,DELETE),
// DSN=$pre.PLAN.PUN
//PU DD DISP=(MOD,DELETE),
// DSN=$pre.PLAN.UNL
$/del/
$proc $@/copy/
$@ parse arg , fRz tRz
$= fRz =- fRz
$= tRz =- tRz
if fRz = 'RZ4' then
$= fCsm =- ''
else
$= fCsm =- ",SUBSYS=(CSM,'SYSTEM="fRz"')"
if tRz = 'RZ4' then
$= tCsm =- ''
else
$= tCsm =- ",SUBSYS=(CSM,'SYSTEM="tRz"')"
$@ if fRz <> tRz then $@=[
//************************************* delete in $tRz
//CODEL EXEC PGM=IEFBR14,TIME=1440
//QU DD DISP=(MOD,DELETE)$tCsm,
// DSN=$pre.QUERY.UNL
//PU DD DISP=(MOD,DELETE)$tCsm,
// DSN=$pre.PLAN.UNL
//* vbs dataset fails with csm ||||||
//************************************* copy from $fRz to $tRz
//COQUE EXEC PGM=CSMUTIL,PARM='CSM,COPYREPLACE'
//SYSPRINT DD SYSOUT=*
//SYSUT1 DD DSN=$pre.QUERY.UNL,
// DISP=SHR$fCsm
//SYSUT2 DD DSN=$pre.QUERY.UNL,
// DISP=(NEW,CATLG)$tCsm,
// MGMTCLAS=COM#E005,DATACLAS=NULL30,
//* csm fails on vbs except csmUtil
// DCB=(RECFM=VBS,DSORG=PS,BLKSIZE=27998,LRECL=X),
// SPACE=(CYL,(1,50))
//************************************* copy from $fRz to $tRz
//COPLA EXEC PGM=CSMUTIL,PARM='CSM,COPYREPLACE'
//SYSPRINT DD SYSOUT=*
//SYSUT1 DD DSN=$pre.PLAN.UNL,
// DISP=SHR$fCsm
//SYSUT2 DD DSN=$pre.PLAN.UNL,
// DISP=(NEW,CATLG)$tCsm,
// MGMTCLAS=COM#E005,DATACLAS=NULL30,
// DCB=(RECFM=VB,DSORG=PS,BLKSIZE=27998,LRECL=7775),
// SPACE=(CYL,(1,50))
$]
$/copy/
$proc $@=/iUnload/
$@del
$@%[util UNL $dbSys$]
UNLOAD DATA
PUNCHDDN QP UNLDDN QU SPANNED YES
SHRLEVEL REFERENCE
FROM TABLE $cre.DSN_USERQUERY_TABLE
$@%[queryCols 0$]
UNLOAD DATA
PUNCHDDN PP UNLDDN PU SPANNED YES
SHRLEVEL REFERENCE
FROM TABLE $cre.PLAN_TABLE
$/iUnload/
$proc $@=/load/
$@[
if $fun == 'e' then
$@%[util LOAD $dbSys $]
else
$@%[util LOAD DP4G $]
$]
-- delete target
EXEC SQL
delete from $cre.plan_table
ENDEXEC
EXEC SQL
delete from $cre.dsn_userQuery_table
ENDEXEC
-- load plan_table
LOAD DATA LOG YES
WORKDDN(TSYUTS,TSOUTS)
UNICODE CCSID(00367,01208,01200)
INDDN PU
RESUME YES SHRLEVEL CHANGE
INTO TABLE
$cre.PLAN_TABLE
$@planCols
-- load userQuery
LOAD DATA LOG YES
WORKDDN(TSYUTS,TSOUTS)
RESUME YES SHRLEVEL CHANGE
INDDN QU
FORMAT SPANNED YES
UNICODE CCSID(00367,01208,01200)
INTO TABLE
$cre.DSN_USERQUERY_TABLE
IGNOREFIELDS YES
$@%[queryCols 1$]
$@[
if $fun == 'i' then $@=[
-- set timestamp
EXEC SQL
update $cre.plan_table
set explain_time = '$tst'
ENDEXEC
EXEC SQL
-- insert into optHintPlan
insert into oa1p.tqz081OptHintPlan
select * from $cre.plan_table
ENDEXEC
EXEC SQL
-- insert into optHintQuery
insert into oa1p.tqz080OptHintQuery
( EXPLAIN_TIME
, DESC
, QUERYNO
, SCHEMA
, HINT_SCOPE
, QUERY_TEXT
, QUERYID
, USERFILTER
, OTHER_OPTIONS
, COLLECTION
, PACKAGE
, VERSION
, REOPT
, STARJOIN
, MAX_PAR_DEGREE
, DEF_CURR_DEGREE
, SJTABLES
, OTHER_PARMS
)
select
'$tst' EXPLAIN_TIME
,'$desc'
desc
, QUERYNO
, SCHEMA
, HINT_SCOPE
, QUERY_TEXT
, QUERYID
, USERFILTER
, OTHER_OPTIONS
, COLLECTION
, PACKAGE
, VERSION
, REOPT
, STARJOIN
, MAX_PAR_DEGREE
, DEF_CURR_DEGREE
, SJTABLES
, OTHER_PARMS
from $cre.dsn_userQuery_table
ENDEXEC
$] else if $fun == 'e' then $@=[
//************************************* bind query into $dbSys
//BINDQUER EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYS($dbSys)
BIND QUERY EXPLAININPUTSCHEMA('$cre')
$]
$]
$/load/
$proc $@=/eUnload/
$@del
$@%[util UNLOAD DP4G$]
UNLOAD DATA
PUNCHDDN QP UNLDDN QU SPANNED YES
SHRLEVEL REFERENCE
FROM TABLE OA1P.TQZ080OPTHINTQUERY
WHEN (EXPLAIN_TIME = '$eTst')
$@%[queryCols 0$]
UNLOAD DATA
PUNCHDDN PP UNLDDN PU SPANNED YES
SHRLEVEL REFERENCE
FROM TABLE OA1P.TQZ081OPTHINTPLAN
WHEN (EXPLAIN_TIME = '$eTst')
$/eUnload/
$proc $@=/planCols/
( "QUERYNO"
POSITION( 00003:00006) INTEGER
, "QBLOCKNO"
POSITION( 00007:00008) SMALLINT
, "APPLNAME"
POSITION( 00009) VARCHAR MIXED
, "PROGNAME"
POSITION( *) VARCHAR MIXED
, "PLANNO"
POSITION( *) SMALLINT
, "METHOD"
POSITION( *) SMALLINT
, "CREATOR"
POSITION( *) VARCHAR MIXED
, "TNAME"
POSITION( *) VARCHAR MIXED
, "TABNO"
POSITION( *) SMALLINT
, "ACCESSTYPE"
POSITION( *) CHAR MIXED(002)
, "MATCHCOLS"
POSITION( *) SMALLINT
, "ACCESSCREATOR"
POSITION( *) VARCHAR MIXED
, "ACCESSNAME"
POSITION( *) VARCHAR MIXED
, "INDEXONLY"
POSITION( *) CHAR MIXED(001)
, "SORTN_UNIQ"
POSITION( *) CHAR MIXED(001)
, "SORTN_JOIN"
POSITION( *) CHAR MIXED(001)
, "SORTN_ORDERBY"
POSITION( *) CHAR MIXED(001)
, "SORTN_GROUPBY"
POSITION( *) CHAR MIXED(001)
, "SORTC_UNIQ"
POSITION( *) CHAR MIXED(001)
, "SORTC_JOIN"
POSITION( *) CHAR MIXED(001)
, "SORTC_ORDERBY"
POSITION( *) CHAR MIXED(001)
, "SORTC_GROUPBY"
POSITION( *) CHAR MIXED(001)
, "TSLOCKMODE"
POSITION( *) CHAR MIXED(003)
, "TIMESTAMP"
POSITION( *) CHAR MIXED(016)
, "REMARKS"
POSITION( *) VARCHAR MIXED
, "PREFETCH"
POSITION( *) CHAR MIXED(001)
, "COLUMN_FN_EVAL"
POSITION( *) CHAR MIXED(001)
, "MIXOPSEQ"
POSITION( *) SMALLINT
, "VERSION"
POSITION( *) VARCHAR MIXED
, "COLLID"
POSITION( *) VARCHAR MIXED
, "DSN_NULL_IND_00031" POSITION( *) CHAR(1)
, "ACCESS_DEGREE"
POSITION( *) SMALLINT
NULLIF(DSN_NULL_IND_00031)=X'FF'
, "DSN_NULL_IND_00032" POSITION( *) CHAR(1)
, "ACCESS_PGROUP_ID"
POSITION( *) SMALLINT
NULLIF(DSN_NULL_IND_00032)=X'FF'
, "DSN_NULL_IND_00033" POSITION( *) CHAR(1)
, "JOIN_DEGREE"
POSITION( *) SMALLINT
NULLIF(DSN_NULL_IND_00033)=X'FF'
, "DSN_NULL_IND_00034" POSITION( *) CHAR(1)
, "JOIN_PGROUP_ID"
POSITION( *) SMALLINT
NULLIF(DSN_NULL_IND_00034)=X'FF'
, "DSN_NULL_IND_00035" POSITION( *) CHAR(1)
, "SORTC_PGROUP_ID"
POSITION( *) SMALLINT
NULLIF(DSN_NULL_IND_00035)=X'FF'
, "DSN_NULL_IND_00036" POSITION( *) CHAR(1)
, "SORTN_PGROUP_ID"
POSITION( *) SMALLINT
NULLIF(DSN_NULL_IND_00036)=X'FF'
, "DSN_NULL_IND_00037" POSITION( *) CHAR(1)
, "PARALLELISM_MODE"
POSITION( *) CHAR MIXED(001)
NULLIF(DSN_NULL_IND_00037)=X'FF'
, "DSN_NULL_IND_00038" POSITION( *) CHAR(1)
, "MERGE_JOIN_COLS"
POSITION( *) SMALLINT
NULLIF(DSN_NULL_IND_00038)=X'FF'
, "DSN_NULL_IND_00039" POSITION( *) CHAR(1)
, "CORRELATION_NAME"
POSITION( *) VARCHAR MIXED
NULLIF(DSN_NULL_IND_00039)=X'FF'
, "PAGE_RANGE"
POSITION( *) CHAR MIXED(001)
, "JOIN_TYPE"
POSITION( *) CHAR MIXED(001)
, "GROUP_MEMBER"
POSITION( *) VARCHAR MIXED
, "IBM_SERVICE_DATA"
POSITION( *) VARCHAR
, "WHEN_OPTIMIZE"
POSITION( *) CHAR MIXED(001)
, "QBLOCK_TYPE"
POSITION( *) CHAR MIXED(006)
, "BIND_TIME"
POSITION( *) TIMESTAMP EXTERNAL(026)
, "OPTHINT"
POSITION( *) VARCHAR MIXED
, "HINT_USED"
POSITION( *) VARCHAR MIXED
, "PRIMARY_ACCESSTYPE"
POSITION( *) CHAR MIXED(001)
, "PARENT_QBLOCKNO"
POSITION( *) SMALLINT
, "DSN_NULL_IND_00051" POSITION( *) CHAR(1)
, "TABLE_TYPE"
POSITION( *) CHAR MIXED(001)
NULLIF(DSN_NULL_IND_00051)=X'FF'
, "TABLE_ENCODE"
POSITION( *) CHAR MIXED(001)
, "TABLE_SCCSID"
POSITION( *) SMALLINT
, "TABLE_MCCSID"
POSITION( *) SMALLINT
, "TABLE_DCCSID"
POSITION( *) SMALLINT
, "ROUTINE_ID"
POSITION( *) INTEGER
, "CTEREF"
POSITION( *) SMALLINT
, "DSN_NULL_IND_00058" POSITION( *) CHAR(1)
, "STMTTOKEN"
POSITION( *) VARCHAR MIXED
NULLIF(DSN_NULL_IND_00058)=X'FF'
, "PARENT_PLANNO"
POSITION( *) SMALLINT
, "BIND_EXPLAIN_ONLY"
POSITION( *) CHAR MIXED(001)
, "SECTNOI"
POSITION( *) INTEGER
, "EXPLAIN_TIME"
POSITION( *) TIMESTAMP EXTERNAL(026)
, "MERGC"
POSITION( *) CHAR MIXED(001)
, "MERGN"
POSITION( *) CHAR MIXED(001)
)
$/planCols/
$proc $@=/queryCols/
$= isLoad =- arg(2)
( "QUERYNO"
POSITION( 00003) INTEGER
, "SCHEMA"
POSITION(7) VARCHAR MIXED
, "HINT_SCOPE"
POSITION( *) SMALLINT
$@[ if $isLoad then
$$ , "DSN_ROWID"
else
$$ , "QUERY_ROWID"
$]
POSITION( *) ROWID
, "QUERYID"
POSITION( *) BIGINT
, "USERFILTER"
POSITION( *) CHAR MIXED(008)
, "OTHER_OPTIONS"
POSITION( *) CHAR MIXED(128)
, "COLLECTION"
POSITION( *) VARCHAR MIXED
, "PACKAGE"
POSITION( *) VARCHAR MIXED
, "VERSION"
POSITION( *) VARCHAR MIXED
, "REOPT"
POSITION( *) CHAR MIXED(001)
, "STARJOIN"
POSITION( *) CHAR MIXED(001)
, "MAX_PAR_DEGREE"
POSITION( *) INTEGER
, "DEF_CURR_DEGREE"
POSITION( *) CHAR MIXED(003)
, "SJTABLES"
POSITION( *) INTEGER
, "OTHER_PARMS"
POSITION( *) VARCHAR MIXED $*(
, "DSN_NULL_IND_00017" POSITION( *) CHAR(1)
, "QUERY_TEXT"
POSITION( *) CLOB MIXED
NULLIF(DSN_NULL_IND_00017)=X'FF' $*)
, "QUERY_TEXT"
POSITION( *) CLOB MIXED
)
$/queryCols/