zOs/WK/PLANTBV9
$#@ $** create plan tables and views 5.12.11
$** 1) Parameter eingeben
$** 2) wsh
$** 3) job kontrollieren und sub
$>.fEdit() $@[
$=subsys=DBAF $**** db2 subsystem *********************
$=cr=A540769 $**** creator *********************
$=fun = d $**** funktion *********************
$*( c= create plan tables and views
d=drop Tablespace and recreate everything
u=update tables for v9.1 and recreate views
v=drop/recreate views only $*)
$=job=Y4PLANTB $***** jobName *********************
$=ts=- strip(left($cr, 7))
$=db=DB2PLAN
$=suf=- ''
$=tsH= ${ts}H
$=tsL= ${ts}L
if 0 then $@[ $**** Spezial Name für Daten Migration
$=suf= _New
$=tsH= ${ts}Q
$**$=tsL= ${ts}O
$]
if $subsys == 'DBAF' then
$=path= OA1A
else if $subsys == 'DBTF' then
$=path= OA1T
else
$=path= OA1P
$=cmnViews =- $cr = 'CMNBATCH'
$=doReo = 0
$=defer=- if(pos($fun, 'cd') <= 0, YES, NO)
$*( *** history *******************************************************
3. 2.12 allow v10 views (but do not change v9 to v10 yet|)
****************************************************************** $*)
if pos($fun, 'uv') > 0 then
call sqlConnect $subsys
$@=[
//$job JOB (CP00,KE50),
// MSGCLASS=T,TIME=1440,
// NOTIFY=&SYSUID
//*MAIN CLASS=LOG0 ,SYSTEM=S12
//*
//* db2SubSys = $subsys
//* tableSpaces = $db.$ts*
//* creator = $cr
//*
$]
if $fun == 'c' then
$$ //* create plan tables only
else if $fun == 'd' then $@[
$$ //* drop recreate plan tables and views
$$ //* all data lost ||||||||||||||||||||||||||||||||||||
$] else if $fun == 'u' then
$$ //* update plantables for v9.1 and recreate views
else if $fun == 'v' then
$$ //* drop recreate plan views only
else
call err 'bad fun='$fun
if $suf \== '' then
$$ //* suf=$suf ||||||||||||||||||||||||||||||||||||||||||
if $cmnViews then
$$ //* with changeman views
$@=[
//*
//DDL EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99
//SYSTSIN DD *
DSN SYSTEM($subsys)
RUN PROGRAM(DSNTEP2) PARMS('ALIGN(LHS)') PLAN(DSNTEP2)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTERM DD DUMMY
//SYSIN DD *
set current path = '$path';
set current sqlid = 'S100447';
$]
$$ --#SET MAXERRORS 99
if $fun == 'd' then $@=[
DROP TABLESPACE ${db}.${ts} ;
DROP TABLESPACE ${db}.${ts}X;
DROP TABLESPACE ${db}.$tsL;
COMMIT;
-----------------------------------------------------------------------
--DROP THE TABLESPACES FOR OTHER V9 EXPLAIN TABLES.
DROP TABLESPACE ${db}.${ts}G;
DROP TABLESPACE ${db}.$tsH;
DROP TABLESPACE ${db}.${ts}I;
DROP TABLESPACE ${db}.${ts}Y;
DROP TABLESPACE ${db}.${ts}J;
COMMIT;
$] else if $fun \== 'c' then $@=[
drop VIEW $cr.PLAN_VIEW5 ;
DROP VIEW $cr.PLAN_ViewPred ;
DROP VIEW $cr.PLAN_VIEW1 ;
DROP VIEW $cr.PLAN_VIEW0 ;
DROP VIEW $cr.PLAN_VIEW ;
-- changeman views
drop VIEW $cr.PLAN_VIEW9 ;
drop VIEW $cr.PLAN_VIEW7 ;
drop VIEW $cr.PLAN_VIEW6_CMN ;
drop VIEW $cr.PLAN_VIEW6 ;
DROP VIEW $cr.PLAN_VIEW3 ;
DROP VIEW $cr.PLAN_Filt ; --??? old name should not exist
$]
$$ --#SET MAXERRORS 0
if pos($fun, 'cd') > 0 then $@=[
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V9 SAMPLE PLAN_TABLE,
--DSN_FUNCTION_TABLE, DSN_STATEMNT_TABLE, AND DSN_STATEMENT_CACHE_TABLE
CREATE TABLESPACE ${ts}X
IN ${db}
USING STOGROUP GSMS PRIQTY -1 SECQTY -1 ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP8K0
SEGSIZE 16
COMPRESS YES CLOSE YES
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE LOB TABLE SPACE FOR THE SAMPLE DSN_STATEMENT_CACHE_TABLE
CREATE LOB TABLESPACE $tsL
IN ${db}
BUFFERPOOL BP32K
USING STOGROUP GSMS PRIQTY -1 SECQTY -1 ERASE NO
CLOSE YES;
-----------------------------------------------------------------------
--CREATE THE TABLESPACES FOR THE OTHER V9 SAMPLE EXPLAIN TABLES
CREATE TABLESPACE ${ts}G
IN ${db}
BUFFERPOOL BP16K0
CCSID UNICODE
USING STOGROUP GSMS PRIQTY -1 SECQTY -1 ERASE NO
COMPRESS YES CLOSE YES;
CREATE TABLESPACE $tsH
IN ${db}
BUFFERPOOL BP8K0
CCSID UNICODE
USING STOGROUP GSMS PRIQTY -1 SECQTY -1 ERASE NO
COMPRESS YES CLOSE YES;
CREATE TABLESPACE ${ts}I
IN ${db}
BUFFERPOOL BP8K0
CCSID UNICODE
USING STOGROUP GSMS PRIQTY -1 SECQTY -1 ERASE NO
COMPRESS YES CLOSE YES;
CREATE LOB TABLESPACE ${ts}Y
IN ${db}
BUFFERPOOL BP8K0
USING STOGROUP GSMS PRIQTY -1 SECQTY -1 ERASE NO
CLOSE YES;
CREATE TABLESPACE ${ts}J
IN ${db}
BUFFERPOOL BP0
CCSID UNICODE
USING STOGROUP GSMS PRIQTY -1 SECQTY -1 ERASE NO
COMPRESS YES CLOSE YES;
$]
$@proc tb $@[
parse arg ., var tb
if pos($fun, 'cd') > 0 then
$=$-{var}=- ''
else
$=$-{var}=- catTbLastCol($cr, tb)
$]
$@proc tbCol $@[
parse arg ., var tb col
call sqlPreAllCl 1, "select count(*) from sysibm.sysColumns" ,
"where tbCreator = '"$cr"' and tbName = '"tb"'" ,
"and name = '"col"'", st, ':cn'
$=$-{var}=- cn
$]
$@proc ix $@[
parse arg ., vKy vDr ix
if pos($fun, 'cd') > 0 then
$=$-{vKy}=- ''
else
$=$-{vKy}=- catIxKeys($cr, ix)
if ${$-{vKy}} == '' then
$=$-{vDr}=- ''
else
$=$-{vDr}=- 'drop index' $cr'.'ix';'
$]
$@proc ixAtt $@=[
NOT PADDED
USING STOGROUP GSMS PRIQTY -1 SECQTY -1 ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
$-{if(pos('c', arg(2)) < 1, 'NOT')} CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE NO
COPY NO
DEFER $defer
DEFINE YES
PIECESIZE 2 G;
$]
$@tb={plan PLAN_TABLE}
if $plan == '64 MERGN' then $@=[ $** already v10 ok
$] else if $plan == '59 PARENT_PLANNO' then $@=[ $** already v9 ok
$] else if $fun == 'u' & $plan == '58 STMTTOKEN' then $@=[
$=doReo=1
-- update explain tables from v8 to v9
ALTER TABLE $cr.PLAN_TABLE
ALTER "APPLNAME" SET DATA TYPE VARCHAR(24)
ALTER "VERSION" SET DATA TYPE VARCHAR(122)
ALTER "GROUP_MEMBER" SET DATA TYPE VARCHAR(24);
ALTER TABLE $cr.PLAN_TABLE
ADD "PARENT_PLANNO" SMALLINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_FUNCTION_TABLE
ALTER "APPLNAME" SET DATA TYPE VARCHAR(24)
ALTER "GROUP_MEMBER" SET DATA TYPE VARCHAR(24);
ALTER TABLE $cr.DSN_STRUCT_TABLE
ALTER "APPLNAME" SET DATA TYPE VARCHAR(24);
ALTER TABLE $cr.DSN_STRUCT_TABLE
ADD "GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_PGROUP_TABLE
ALTER "VERSION" SET DATA TYPE VARCHAR(122);
ALTER TABLE $cr.DSN_PGROUP_TABLE
ALTER "PLANNAME" SET DATA TYPE VARCHAR(24);
ALTER TABLE $cr.DSN_PGROUP_TABLE
ADD "GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_PGROUP_TABLE
ADD "HOST_REASON" SMALLINT;
ALTER TABLE $cr.DSN_PGROUP_TABLE
ADD "PARA_TYPE" CHAR(4);
ALTER TABLE $cr.DSN_PGROUP_TABLE
ADD "PART_INNER" CHAR(1);
ALTER TABLE $cr.DSN_PGROUP_TABLE
ADD "GRNU_KEYRNG" CHAR(1);
ALTER TABLE $cr.DSN_PGROUP_TABLE
ADD "OPEN_KEYRNG" CHAR(1);
ALTER TABLE $cr.DSN_PTASK_TABLE
ALTER "APPLNAME" SET DATA TYPE VARCHAR(24);
ALTER TABLE $cr.DSN_PTASK_TABLE
ADD "GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ALTER "APPLNAME" SET DATA TYPE VARCHAR(24);
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "MAX_COMPOSITES" INTEGER NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "MAX_STOR" INTEGER NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "MAX_CPU" INTEGER NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "MAX_ELAP" INTEGER NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "TBL_JOINED_THRESH" INTEGER NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "STOR_USED" INTEGER NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "CPU_USED" INTEGER NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "ELAPSED" INTEGER NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "MIN_CARD_KEEP" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "MAX_CARD_KEEP" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "MIN_COST_KEEP" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "MAX_COST_KEEP" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "MIN_VALUE_KEEP" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "MIN_VALUE_CARD_KEEP" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "MIN_VALUE_COST_KEEP" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "MAX_VALUE_KEEP" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "MAX_VALUE_CARD_KEEP" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "MAX_VALUE_COST_KEEP" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "MIN_CARD_CLIP" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "MAX_CARD_CLIP" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "MIN_COST_CLIP" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "MAX_COST_CLIP" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "MIN_VALUE_CLIP" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "MIN_VALUE_CARD_CLIP" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "MIN_VALUE_COST_CLIP" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "MAX_VALUE_CLIP" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "MAX_VALUE_CARD_CLIP" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "MAX_VALUE_COST_CLIP" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "PSEQIOCOST" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "PSEQCPUCOST" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "PSEQCOST" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "PADJIOCOST" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "PADJCPUCOST" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "PADJCOST" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_SORT_TABLE
ALTER "APPLNAME" SET DATA TYPE VARCHAR(24);
ALTER TABLE $cr.DSN_SORT_TABLE
ADD "GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_SORTKEY_TABLE
ALTER "APPLNAME" SET DATA TYPE VARCHAR(24);
ALTER TABLE $cr.DSN_SORTKEY_TABLE
ADD "GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_PGRANGE_TABLE
ADD "GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DEFAULT;
$] else if $plan == '' & fun \== '' then $@=[
-----------------------------------------------------------------------
--CREATE THE V9 SAMPLE PLAN_TABLE.
CREATE TABLE $cr.PLAN_TABLE$suf
( "QUERYNO" INTEGER NOT NULL,
"QBLOCKNO" SMALLINT NOT NULL,
"APPLNAME" VARCHAR(24) NOT NULL,
"PROGNAME" VARCHAR(128) NOT NULL,
"PLANNO" SMALLINT NOT NULL,
"METHOD" SMALLINT NOT NULL,
"CREATOR" VARCHAR(128) NOT NULL,
"TNAME" VARCHAR(128) NOT NULL,
"TABNO" SMALLINT NOT NULL,
"ACCESSTYPE" CHAR(2) NOT NULL,
"MATCHCOLS" SMALLINT NOT NULL,
"ACCESSCREATOR" VARCHAR(128) NOT NULL,
"ACCESSNAME" VARCHAR(128) NOT NULL,
"INDEXONLY" CHAR(1) NOT NULL,
"SORTN_UNIQ" CHAR(1) NOT NULL,
"SORTN_JOIN" CHAR(1) NOT NULL,
"SORTN_ORDERBY" CHAR(1) NOT NULL,
"SORTN_GROUPBY" CHAR(1) NOT NULL,
"SORTC_UNIQ" CHAR(1) NOT NULL,
"SORTC_JOIN" CHAR(1) NOT NULL,
"SORTC_ORDERBY" CHAR(1) NOT NULL,
"SORTC_GROUPBY" CHAR(1) NOT NULL,
"TSLOCKMODE" CHAR(3) NOT NULL,
"TIMESTAMP" CHAR(16) NOT NULL,
"REMARKS" VARCHAR(762) NOT NULL,
"PREFETCH" CHAR(1) NOT NULL WITH DEFAULT,
"COLUMN_FN_EVAL" CHAR(1) NOT NULL WITH DEFAULT,
"MIXOPSEQ" SMALLINT NOT NULL WITH DEFAULT,
"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT,
"COLLID" VARCHAR(128) NOT NULL WITH DEFAULT,
"ACCESS_DEGREE" SMALLINT,
"ACCESS_PGROUP_ID" SMALLINT,
"JOIN_DEGREE" SMALLINT,
"JOIN_PGROUP_ID" SMALLINT,
"SORTC_PGROUP_ID" SMALLINT,
"SORTN_PGROUP_ID" SMALLINT,
"PARALLELISM_MODE" CHAR(1),
"MERGE_JOIN_COLS" SMALLINT,
"CORRELATION_NAME" VARCHAR(128),
"PAGE_RANGE" CHAR(1) NOT NULL WITH DEFAULT,
"JOIN_TYPE" CHAR(1) NOT NULL WITH DEFAULT,
"GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DEFAULT,
"IBM_SERVICE_DATA" VARCHAR(254) FOR BIT DATA
NOT NULL WITH DEFAULT,
"WHEN_OPTIMIZE" CHAR(1) NOT NULL WITH DEFAULT,
"QBLOCK_TYPE" CHAR(6) NOT NULL WITH DEFAULT,
"BIND_TIME" TIMESTAMP NOT NULL WITH DEFAULT,
"OPTHINT" VARCHAR(128) NOT NULL WITH DEFAULT,
"HINT_USED" VARCHAR(128) NOT NULL WITH DEFAULT,
"PRIMARY_ACCESSTYPE" CHAR(1) NOT NULL WITH DEFAULT,
"PARENT_QBLOCKNO" SMALLINT NOT NULL WITH DEFAULT,
"TABLE_TYPE" CHAR(1),
"TABLE_ENCODE" CHAR(1) NOT NULL WITH DEFAULT,
"TABLE_SCCSID" SMALLINT NOT NULL WITH DEFAULT,
"TABLE_MCCSID" SMALLINT NOT NULL WITH DEFAULT,
"TABLE_DCCSID" SMALLINT NOT NULL WITH DEFAULT,
"ROUTINE_ID" INTEGER NOT NULL WITH DEFAULT,
"CTEREF" SMALLINT NOT NULL WITH DEFAULT,
"STMTTOKEN" VARCHAR(240),
"PARENT_PLANNO" SMALLINT NOT NULL WITH DEFAULT
)
IN ${db}.${ts}X
CCSID UNICODE;
$] else call err 'cannot update plan_Table'$suf 'from' $plan
$@ix={keys drop PLAN_TABLE_idx1$suf}
if $keys == 'QUERYNO<BIND_TIME<' then $@[
$] else if $fun == 'v' then $@[
say 'warning fun=v but index PLAN_TABLE_idx1'$suf':' $keys
$] else $@=[
$=doReo=- $doReo | ($defer == 'YES')
$drop
CREATE INDEX $cr.PLAN_TABLE_idx1$suf
ON $cr.PLAN_Table$suf
( "QUERYNO"
,"BIND_TIME"
)
$@ixAtt={c}
$]
$@ix={keys drop PLAN_TABLE_HINT_IX$suf}
if $keys == 'QUERYNO<APPLNAME<PROGNAME<VERSION<COLLID<OPTHINT<' ,
then $@[
$] else if $fun == 'v' then $@[
say 'warning fun=v but index PLAN_TABLE_HINT_IX'$suf':' $keys
$] else $@=[
$=doReo=- $doReo | ($defer == 'YES')
$drop
CREATE INDEX $cr.PLAN_TABLE_HINT_ix$suf
ON $cr.PLAN_Table$suf
("QUERYNO" ASC,
APPLNAME ASC,
PROGNAME ASC,
VERSION ASC,
"COLLID" ASC,
OPTHINT ASC)
$@ixAtt={}
$]
$@ix={keys drop PLAN_TABLE_PROG_IX$suf}
if $keys == 'PROGNAME<COLLID<VERSION<BIND_TIME<QUERYNO<' ,
then $@[
$] else if $fun == 'v' then $@[
say 'warning fun=v but index PLAN_TABLE_PROG_IX'$suf':' $keys
$] else $@=[
$=doReo=- $doReo | ($defer == 'YES')
$drop
CREATE INDEX $cr.PLAN_TABLE_PROG_ix$suf
ON $cr.PLAN_Table$suf
(PROGNAME ASC,
COLLID ASC,
VERSION ASC,
BIND_TIME ASC,
QUERYNO ASC)
$@ixAtt={}
$]
if pos($fun, 'cd') > 0 then $@=[
-----------------------------------------------------------------------
--CREATE THE V9 SAMPLE DSN_FUNCTION_TABLE.
CREATE TABLE $cr.DSN_FUNCTION_Table$suf
( "QUERYNO" INTEGER NOT NULL WITH DEFAULT,
"QBLOCKNO" INTEGER NOT NULL WITH DEFAULT,
"APPLNAME" VARCHAR(24) NOT NULL WITH DEFAULT,
"PROGNAME" VARCHAR(128) NOT NULL WITH DEFAULT,
"COLLID" VARCHAR(128) NOT NULL WITH DEFAULT,
"GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DEFAULT,
"EXPLAIN_TIME" TIMESTAMP NOT NULL WITH DEFAULT,
"SCHEMA_NAME" VARCHAR(128) NOT NULL WITH DEFAULT,
"FUNCTION_NAME" VARCHAR(128) NOT NULL WITH DEFAULT,
"SPEC_FUNC_NAME" VARCHAR(128) NOT NULL WITH DEFAULT,
"FUNCTION_TYPE" CHAR(2) NOT NULL WITH DEFAULT,
"VIEW_CREATOR" VARCHAR(128) NOT NULL WITH DEFAULT,
"VIEW_NAME" VARCHAR(128) NOT NULL WITH DEFAULT,
"PATH" VARCHAR(2048) NOT NULL WITH DEFAULT,
"FUNCTION_TEXT" VARCHAR(1500) NOT NULL WITH DEFAULT
)
IN ${db}.${ts}X
CCSID UNICODE;
CREATE INDEX $cr.FUNC_EXPidx1$suf
ON $cr.DSN_FUNCTION_Table$suf
( "QUERYNO"
,"EXPLAIN_TIME"
);
-----------------------------------------------------------------------
--CREATE THE V9 SAMPLE DSN_STATEMNT_TABLE.
CREATE TABLE $cr.DSN_STATEMNT_Table$suf
( "QUERYNO" INTEGER NOT NULL WITH DEFAULT,
"APPLNAME" VARCHAR(24) NOT NULL WITH DEFAULT,
"PROGNAME" VARCHAR(128) NOT NULL WITH DEFAULT,
"COLLID" VARCHAR(128) NOT NULL WITH DEFAULT,
"GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DEFAULT,
"EXPLAIN_TIME" TIMESTAMP NOT NULL WITH DEFAULT,
"STMT_TYPE" CHAR(6) NOT NULL WITH DEFAULT,
"COST_CATEGORY" CHAR(1) NOT NULL WITH DEFAULT,
"PROCMS" INTEGER NOT NULL WITH DEFAULT,
"PROCSU" INTEGER NOT NULL WITH DEFAULT,
"REASON" VARCHAR(254) NOT NULL WITH DEFAULT,
"STMT_ENCODE" CHAR(1) NOT NULL WITH DEFAULT,
"TOTAL_COST" FLOAT NOT NULL WITH DEFAULT
)
IN ${db}.${ts}X
CCSID UNICODE;
CREATE INDEX $cr.DSN_STATEMNT_TABLE_idx1$suf
ON $cr.DSN_STATEMNT_Table$suf
( "QUERYNO"
,"EXPLAIN_TIME"
);
$]
$@tb={stCa DSN_STATEMENT_CACHE_TABLE}
if $stCa == '?4 MERGN' then $@=[ $** already v10 ok
$] else if $stCa == '48 BIND_RA_TOT' then $@=[ $** already v9 ok
$] else if pos($fun, 'cd') > 0 | $fun == 'u' & $stCa == '' then $@=[
-----------------------------------------------------------------------
--CREATE THE V9 SAMPLE DSN_STATEMENT_CACHE_Table$suf
CREATE TABLE $cr.DSN_STATEMENT_CACHE_Table$suf
( "STMT_ID" INTEGER NOT NULL,
"STMT_TOKEN" VARCHAR(240) ,
"COLLID" VARCHAR(128) NOT NULL,
"PROGRAM_NAME" VARCHAR(128) NOT NULL,
"INV_DROPALT" CHAR(1) NOT NULL,
"INV_REVOKE" CHAR(1) NOT NULL,
"INV_LRU" CHAR(1) NOT NULL,
"INV_RUNSTATS" CHAR(1) NOT NULL,
"CACHED_TS" TIMESTAMP NOT NULL,
"USERS" INTEGER NOT NULL,
"COPIES" INTEGER NOT NULL,
"LINES" INTEGER NOT NULL,
"PRIMAUTH" VARCHAR(128) NOT NULL,
"CURSQLID" VARCHAR(128) NOT NULL,
"BIND_QUALIFIER" VARCHAR(128) NOT NULL,
"BIND_ISO" CHAR(2) NOT NULL,
"BIND_CDATA" CHAR(1) NOT NULL,
"BIND_DYNRL" CHAR(1) NOT NULL,
"BIND_DEGRE" CHAR(1) NOT NULL,
"BIND_SQLRL" CHAR(1) NOT NULL,
"BIND_CHOLD" CHAR(1) NOT NULL,
"STAT_TS" TIMESTAMP NOT NULL,
"STAT_EXEC" INTEGER NOT NULL,
"STAT_GPAG" INTEGER NOT NULL,
"STAT_SYNR" INTEGER NOT NULL,
"STAT_WRIT" INTEGER NOT NULL,
"STAT_EROW" INTEGER NOT NULL,
"STAT_PROW" INTEGER NOT NULL,
"STAT_SORT" INTEGER NOT NULL,
"STAT_INDX" INTEGER NOT NULL,
"STAT_RSCN" INTEGER NOT NULL,
"STAT_PGRP" INTEGER NOT NULL,
"STAT_ELAP" FLOAT NOT NULL,
"STAT_CPU" FLOAT NOT NULL,
"STAT_SUS_SYNIO" FLOAT NOT NULL,
"STAT_SUS_LOCK" FLOAT NOT NULL,
"STAT_SUS_SWIT" FLOAT NOT NULL,
"STAT_SUS_GLCK" FLOAT NOT NULL,
"STAT_SUS_OTHR" FLOAT NOT NULL,
"STAT_SUS_OTHW" FLOAT NOT NULL,
"STAT_RIDLIMT" INTEGER NOT NULL,
"STAT_RIDSTOR" INTEGER NOT NULL,
"EXPLAIN_TS" TIMESTAMP NOT NULL,
"SCHEMA" VARCHAR(128) NOT NULL,
"STMT_TEXT" CLOB(2M) NOT NULL,
"STMT_ROWID" ROWID NOT NULL GENERATED ALWAYS,
"BIND_RO_TYPE" CHAR(1) NOT NULL WITH DEFAULT,
"BIND_RA_TOT" INTEGER NOT NULL WITH DEFAULT
)
IN ${db}.${ts}X
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE AUXILIARY TABLE FOR THE SAMPLE DSN_STATEMENT_CACHE_Table$suf
CREATE AUX TABLE $cr.DSN_STATEMENT_CACHE_AUX$suf
IN ${db}.$tsL
STORES $cr.DSN_STATEMENT_CACHE_Table$suf
COLUMN STMT_TEXT;
-----------------------------------------------------------------------
--CREATE AN INDEX ON THE V9 SAMPLE PLAN_Table$suf
-----------------------------------------------------------------------
--CREATE INDEXES ON THE V9 SAMPLE DSN_STATEMENT_CACHE_Table$suf
CREATE INDEX $cr.DSN_STATEMENT_CACHE_idx1$suf
ON $cr.DSN_STATEMENT_CACHE_Table$suf
( "STMT_ID" ASC );
CREATE INDEX $cr.DSN_STATEMENT_CACHE_idx2$suf
ON $cr.DSN_STATEMENT_CACHE_Table$suf
( "STMT_TOKEN" ASC )
CLUSTER;
CREATE INDEX $cr.DSN_STATEMENT_CACHE_idx3$suf
ON $cr.DSN_STATEMENT_CACHE_Table$suf
( "EXPLAIN_TS" DESC );
CREATE INDEX $cr.DSN_STATEMENT_CACHE_AUXInx$suf
ON $cr.DSN_STATEMENT_CACHE_AUX$suf;
$] else $@[
call err 'cannot update statement_cache from' $stCa
$]
if pos($fun, 'cd') > 0 then $@=[
-----------------------------------------------------------------------
--CREATE THE V9 SAMPLE DSN_STRUCT_Table$suf AND ITS INDEX
CREATE TABLE $cr.DSN_STRUCT_Table$suf
( "QUERYNO" INTEGER NOT NULL
,"QBLOCKNO" SMALLINT NOT NULL
,"APPLNAME" VARCHAR(24) NOT NULL
,"PROGNAME" VARCHAR(128) NOT NULL
,"PARENT" SMALLINT NOT NULL
,"TIMES" FLOAT NOT NULL
,"ROWCOUNT" INTEGER NOT NULL
,"ATOPEN" CHAR(1) NOT NULL
,"CONTEXT" CHAR(10) NOT NULL
,"ORDERNO" SMALLINT NOT NULL
,"DOATOPEN_PARENT" SMALLINT NOT NULL
,"QBLOCK_TYPE" CHAR(6) NOT NULL WITH DEFAULT
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"QUERY_STAGE" CHAR(8) NOT NULL
,"GROUP_MEMBER" VARCHAR(24) NOT NULL
)
IN ${db}.${ts}X
VOLATILE
CCSID UNICODE;
CREATE INDEX $cr.DSN_STRUCT_TABLE_idx1$suf
ON $cr.DSN_STRUCT_Table$suf
( "QUERYNO"
,"EXPLAIN_TIME"
);
-----------------------------------------------------------------------
--CREATE THE V9 SAMPLE DSN_PGROUP_Table$suf AND ITS INDEX
CREATE TABLE $cr.DSN_PGROUP_Table$suf
( "QUERYNO" INTEGER NOT NULL
,"QBLOCKNO" SMALLINT NOT NULL
,"PLANNAME" VARCHAR(24) NOT NULL
,"COLLID" VARCHAR(128) NOT NULL
,"PROGNAME" VARCHAR(128) NOT NULL
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"VERSION" VARCHAR(122) NOT NULL
,"GROUPID" SMALLINT NOT NULL
,"FIRSTPLAN" SMALLINT NOT NULL
,"LASTPLAN" SMALLINT NOT NULL
,"CPUCOST" REAL NOT NULL
,"IOCOST" REAL NOT NULL
,"BESTTIME" REAL NOT NULL
,"DEGREE" SMALLINT NOT NULL
,"MODE" CHAR(1) NOT NULL
,"REASON" SMALLINT NOT NULL
,"LOCALCPU" SMALLINT NOT NULL
,"TOTALCPU" SMALLINT NOT NULL
,"FIRSTBASE" SMALLINT
,"LARGETS" CHAR(1)
,"PARTKIND" CHAR(1)
,"GROUPTYPE" CHAR(3)
,"ORDER" CHAR(1)
,"STYLE" CHAR(4)
,"RANGEKIND" CHAR(1)
,"NKEYCOLS" SMALLINT
,"LOWBOUND" VARCHAR(40)
,"HIGHBOUND" VARCHAR(40)
,"LOWKEY" VARCHAR(40)
,"HIGHKEY" VARCHAR(40)
,"FIRSTPAGE" CHAR(4)
,"LASTPAGE" CHAR(4)
,"GROUP_MEMBER" VARCHAR(24) NOT NULL
,"HOST_REASON" SMALLINT
,"PARA_TYPE" CHAR(4)
,"PART_INNER" CHAR(1)
,"GRNU_KEYRNG" CHAR(1)
,"OPEN_KEYRNG" CHAR(1)
)
IN ${db}.${ts}G
VOLATILE
CCSID UNICODE;
CREATE INDEX $cr.DSN_PGROUP_TABLE_idx1$suf
ON $cr.DSN_PGROUP_Table$suf
( "QUERYNO"
,"EXPLAIN_TIME"
);
-----------------------------------------------------------------------
--CREATE THE V9 SAMPLE DSN_PTASK_Table$suf AND ITS INDEX
CREATE TABLE $cr.DSN_PTASK_Table$suf
( "QUERYNO" INTEGER NOT NULL
,"QBLOCKNO" SMALLINT NOT NULL
,"PGDNO" SMALLINT NOT NULL
,"APPLNAME" VARCHAR(24) NOT NULL
,"PROGNAME" VARCHAR(128) NOT NULL
,"LPTNO" SMALLINT NOT NULL
,"KEYCOLID" SMALLINT
,"DPSI" CHAR(1) NOT NULL
,"LPTLOKEY" VARCHAR(40)
,"LPTHIKEY" VARCHAR(40)
,"LPTLOPAG" CHAR(4)
,"LPTHIPAG" CHAR(4)
,"LPTLOPG" CHAR(4)
,"LPTHIPG" CHAR(4)
,"LPTLOPT" SMALLINT
,"LPTHIPT" SMALLINT
,"KEYCOLDT" SMALLINT
,"KEYCOLPREC" SMALLINT
,"KEYCOLSCAL" SMALLINT
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"GROUP_MEMBER" VARCHAR(24) NOT NULL
)
IN ${db}.$tsH
VOLATILE
CCSID UNICODE;
CREATE INDEX $cr.DSN_PTASK_TABLE_idx1$suf
ON $cr.DSN_PTASK_Table$suf
( "QUERYNO"
,"EXPLAIN_TIME"
);
-----------------------------------------------------------------------
--CREATE THE V9 SAMPLE DSN_DETCOST_Table$suf AND ITS INDEX
CREATE TABLE $cr.DSN_DETCOST_Table$suf
( "QUERYNO" INTEGER NOT NULL
,"QBLOCKNO" SMALLINT NOT NULL
,"APPLNAME" VARCHAR(24) NOT NULL
,"PROGNAME" VARCHAR(128) NOT NULL
,"PLANNO" SMALLINT NOT NULL
,"OPENIO" FLOAT(4) NOT NULL
,"OPENCPU" FLOAT(4) NOT NULL
,"OPENCOST" FLOAT(4) NOT NULL
,"DMIO" FLOAT(4) NOT NULL
,"DMCPU" FLOAT(4) NOT NULL
,"DMTOT" FLOAT(4) NOT NULL
,"SUBQIO" FLOAT(4) NOT NULL
,"SUBQCPU" FLOAT(4) NOT NULL
,"SUBQCOST" FLOAT(4) NOT NULL
,"BASEIO" FLOAT(4) NOT NULL
,"BASECPU" FLOAT(4) NOT NULL
,"BASETOT" FLOAT(4) NOT NULL
,"ONECOMPROWS" FLOAT(4) NOT NULL
,"IMLEAF" FLOAT(4) NOT NULL
,"IMIO" FLOAT(4) NOT NULL
,"IMPREFH" CHAR(2) NOT NULL
,"IMMPRED" INTEGER NOT NULL
,"IMFF" FLOAT(4) NOT NULL
,"IMSRPRED" INTEGER NOT NULL
,"IMFFADJ" FLOAT(4) NOT NULL
,"IMSCANCST" FLOAT(4) NOT NULL
,"IMROWCST" FLOAT(4) NOT NULL
,"IMPAGECST" FLOAT(4) NOT NULL
,"IMRIDSORT" FLOAT(4) NOT NULL
,"IMMERGCST" FLOAT(4) NOT NULL
,"IMCPU" FLOAT(4) NOT NULL
,"IMTOT" FLOAT(4) NOT NULL
,"IMSEQNO" SMALLINT NOT NULL
,"DMPREFH" CHAR(2) NOT NULL
,"DMCLUDIO" FLOAT(4) NOT NULL
,"DMNCLUDIO" FLOAT(4) NOT NULL
,"DMPREDS" INTEGER NOT NULL
,"DMSROWS" FLOAT(4) NOT NULL
,"DMSCANCST" FLOAT(4) NOT NULL
,"DMCOLS" SMALLINT NOT NULL
,"DMROWS" FLOAT(4) NOT NULL
,"RDSROWCST" FLOAT(4) NOT NULL
,"DMPAGECST" FLOAT(4) NOT NULL
,"DMDATAIO" FLOAT(4) NOT NULL
,"DMDATACPU" FLOAT(4) NOT NULL
,"DMDATATOT" FLOAT(4) NOT NULL
,"RDSROW" FLOAT(4) NOT NULL
,"SNCOLS" SMALLINT NOT NULL
,"SNROWS" FLOAT(4) NOT NULL
,"SNRECSZ" INTEGER NOT NULL
,"SNPAGES" FLOAT(4) NOT NULL
,"SNRUNS" FLOAT(4) NOT NULL
,"SNMERGES" FLOAT(4) NOT NULL
,"SNIOCOST" FLOAT(4) NOT NULL
,"SNCPUCOST" FLOAT(4) NOT NULL
,"SNCOST" FLOAT(4) NOT NULL
,"SNSCANIO" FLOAT(4) NOT NULL
,"SNSCANCPU" FLOAT(4) NOT NULL
,"SNSCANCOST" FLOAT(4) NOT NULL
,"SCCOLS" SMALLINT NOT NULL
,"SCROWS" FLOAT(4) NOT NULL
,"SCRECSZ" INTEGER NOT NULL
,"SCPAGES" FLOAT(4) NOT NULL
,"SCRUNS" FLOAT(4) NOT NULL
,"SCMERGES" FLOAT(4) NOT NULL
,"SCIOCOST" FLOAT(4) NOT NULL
,"SCCPUCOST" FLOAT(4) NOT NULL
,"SCCOST" FLOAT(4) NOT NULL
,"SCSCANIO" FLOAT(4) NOT NULL
,"SCSCANCPU" FLOAT(4) NOT NULL
,"SCSCANCOST" FLOAT(4) NOT NULL
,"COMPCARD" FLOAT(4) NOT NULL
,"COMPIOCOST" FLOAT(4) NOT NULL
,"COMPCPUCOST" FLOAT(4) NOT NULL
,"COMPCOST" FLOAT(4) NOT NULL
,"JOINCOLS" SMALLINT NOT NULL
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"COSTBLK" INTEGER NOT NULL
,"COSTSTOR" INTEGER NOT NULL
,"MPBLK" INTEGER NOT NULL
,"MPSTOR" INTEGER NOT NULL
,"COMPOSITES" INTEGER NOT NULL
,"CLIPPED" INTEGER NOT NULL
,"PARTITION" INTEGER NOT NULL
,"TABREF" VARCHAR(64) NOT NULL
,"MAX_COMPOSITES" INTEGER NOT NULL
,"MAX_STOR" INTEGER NOT NULL
,"MAX_CPU" INTEGER NOT NULL
,"MAX_ELAP" INTEGER NOT NULL
,"TBL_JOINED_THRESH" INTEGER NOT NULL
,"STOR_USED" INTEGER NOT NULL
,"CPU_USED" INTEGER NOT NULL
,"ELAPSED" INTEGER NOT NULL
,"MIN_CARD_KEEP" FLOAT(4) NOT NULL
,"MAX_CARD_KEEP" FLOAT(4) NOT NULL
,"MIN_COST_KEEP" FLOAT(4) NOT NULL
,"MAX_COST_KEEP" FLOAT(4) NOT NULL
,"MIN_VALUE_KEEP" FLOAT(4) NOT NULL
,"MIN_VALUE_CARD_KEEP" FLOAT(4) NOT NULL
,"MIN_VALUE_COST_KEEP" FLOAT(4) NOT NULL
,"MAX_VALUE_KEEP" FLOAT(4) NOT NULL
,"MAX_VALUE_CARD_KEEP" FLOAT(4) NOT NULL
,"MAX_VALUE_COST_KEEP" FLOAT(4) NOT NULL
,"MIN_CARD_CLIP" FLOAT(4) NOT NULL
,"MAX_CARD_CLIP" FLOAT(4) NOT NULL
,"MIN_COST_CLIP" FLOAT(4) NOT NULL
,"MAX_COST_CLIP" FLOAT(4) NOT NULL
,"MIN_VALUE_CLIP" FLOAT(4) NOT NULL
,"MIN_VALUE_CARD_CLIP" FLOAT(4) NOT NULL
,"MIN_VALUE_COST_CLIP" FLOAT(4) NOT NULL
,"MAX_VALUE_CLIP" FLOAT(4) NOT NULL
,"MAX_VALUE_CARD_CLIP" FLOAT(4) NOT NULL
,"MAX_VALUE_COST_CLIP" FLOAT(4) NOT NULL
,"GROUP_MEMBER" VARCHAR(24) NOT NULL
,"PSEQIOCOST" FLOAT(4) NOT NULL
,"PSEQCPUCOST" FLOAT(4) NOT NULL
,"PSEQCOST" FLOAT(4) NOT NULL
,"PADJIOCOST" FLOAT(4) NOT NULL
,"PADJCPUCOST" FLOAT(4) NOT NULL
,"PADJCOST" FLOAT(4) NOT NULL
)
IN ${db}.${ts}X
VOLATILE
CCSID UNICODE;
CREATE INDEX $cr.DSN_DETCOST_TABLE_idx1$suf
ON $cr.DSN_DETCOST_Table$suf
( "QUERYNO"
,"EXPLAIN_TIME"
);
-----------------------------------------------------------------------
--CREATE THE V9 SAMPLE DSN_SORT_Table$suf AND ITS INDEX
CREATE TABLE $cr.DSN_SORT_Table$suf
( "QUERYNO" INTEGER NOT NULL
,"QBLOCKNO" SMALLINT NOT NULL
,"PLANNO" SMALLINT NOT NULL
,"APPLNAME" VARCHAR(24) NOT NULL
,"PROGNAME" VARCHAR(128) NOT NULL
,"COLLID" VARCHAR(128) NOT NULL WITH DEFAULT
,"SORTC" CHAR(5) NOT NULL WITH DEFAULT
,"SORTN" CHAR(5) NOT NULL WITH DEFAULT
,"SORTNO" SMALLINT NOT NULL
,"KEYSIZE" SMALLINT NOT NULL
,"ORDERCLASS" INTEGER NOT NULL
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"GROUP_MEMBER" VARCHAR(24) NOT NULL
)
IN ${db}.${ts}X
VOLATILE
CCSID UNICODE;
CREATE INDEX $cr.DSN_SORT_TABLE_idx1$suf
ON $cr.DSN_SORT_Table$suf
( "QUERYNO"
,"EXPLAIN_TIME"
);
-----------------------------------------------------------------------
--CREATE THE V9 SAMPLE DSN_SORTKEY_Table$suf AND ITS INDEX
CREATE TABLE $cr.DSN_SORTKEY_Table$suf
( "QUERYNO" INTEGER NOT NULL
,"QBLOCKNO" SMALLINT NOT NULL
,"PLANNO" SMALLINT NOT NULL
,"APPLNAME" VARCHAR(24) NOT NULL
,"PROGNAME" VARCHAR(128) NOT NULL
,"COLLID" VARCHAR(128) NOT NULL WITH DEFAULT
,"SORTNO" SMALLINT NOT NULL
,"ORDERNO" SMALLINT NOT NULL
,"EXPTYPE" CHAR(3) NOT NULL
,"TEXT" VARCHAR(128) NOT NULL
,"TABNO" SMALLINT NOT NULL
,"COLNO" SMALLINT NOT NULL
,"DATATYPE" CHAR(18) NOT NULL
,"LENGTH" INTEGER NOT NULL
,"CCSID" INTEGER NOT NULL
,"ORDERCLASS" INTEGER NOT NULL
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"GROUP_MEMBER" VARCHAR(24) NOT NULL
)
IN ${db}.${ts}X
VOLATILE
CCSID UNICODE;
CREATE INDEX $cr.DSN_SORTKEY_TABLE_idx1$suf
ON $cr.DSN_SORTKEY_Table$suf
( "QUERYNO"
,"EXPLAIN_TIME"
);
-----------------------------------------------------------------------
--CREATE THE V9 SAMPLE DSN_PGRANGE_Table$suf AND ITS INDEX
CREATE TABLE $cr.DSN_PGRANGE_Table$suf
( "QUERYNO" INTEGER NOT NULL
,"QBLOCKNO" SMALLINT NOT NULL
,"TABNO" SMALLINT NOT NULL
,"RANGE" SMALLINT NOT NULL
,"FIRSTPART" SMALLINT NOT NULL
,"LASTPART" SMALLINT NOT NULL
,"NUMPARTS" SMALLINT NOT NULL
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"GROUP_MEMBER" VARCHAR(24) NOT NULL
)
IN ${db}.${ts}X
VOLATILE
CCSID UNICODE;
CREATE INDEX $cr.DSN_PGRANGE_TABLE_idx1$suf
ON $cr.DSN_PGRANGE_Table$suf
( "QUERYNO"
,"EXPLAIN_TIME"
);
-----------------------------------------------------------------------
--CREATE THE V9 SAMPLE DSN_QUERY_Table$suf AND ITS INDEXES
CREATE TABLE $cr.DSN_QUERY_Table$suf
( "QUERYNO" INTEGER NOT NULL
,"TYPE" CHAR(8) NOT NULL
,"QUERY_STAGE" CHAR(8) NOT NULL
,"SEQNO" INTEGER NOT NULL
,"NODE_DATA" CLOB(2M) NOT NULL
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"QUERY_ROWID" ROWID NOT NULL
GENERATED BY DEFAULT
,"GROUP_MEMBER" VARCHAR(24) NOT NULL
,"HASHKEY" INTEGER NOT NULL
,"HAS_PRED" CHAR(1) NOT NULL
)
IN ${db}.${ts}I
VOLATILE
CCSID UNICODE;
CREATE INDEX $cr.DSN_QUERY_TABLE_idx1$suf
ON $cr.DSN_QUERY_Table$suf
( "QUERYNO"
,"EXPLAIN_TIME"
);
CREATE INDEX $cr.DSN_QUERY_TABLE_idx2$suf
ON $cr.DSN_QUERY_Table$suf
( "QUERYNO"
,"TYPE"
,"QUERY_STAGE"
,"EXPLAIN_TIME"
,"SEQNO"
);
CREATE UNIQUE INDEX $cr.DSN_QUERY_TABLE_idx3$suf
ON $cr.DSN_QUERY_Table$suf
( "QUERY_ROWID"
);
-----------------------------------------------------------------------
--CREATE THE AUXILIARY TABLE FOR THE V9 SAMPLE DSN_QUERY_Table$suf
--AND ITS INDEX
CREATE AUX TABLE $cr.DSN_QUERY_AUX$suf
IN ${db}.${ts}Y
STORES $cr.DSN_QUERY_Table$suf
COLUMN "NODE_DATA";
CREATE INDEX $cr.DSN_QUERY_AUXInx$suf
ON $cr.DSN_QUERY_AUX$suf;
-----------------------------------------------------------------------
--CREATE THE V9 SAMPLE DSN_VIRTUAL_INDEXES TABLE AND ITS INDEX
CREATE TABLE $cr.DSN_VIRTUAL_INDEXES$suf
( "TBCREATOR" VARCHAR(128) NOT NULL
,"TBNAME" VARCHAR(128) NOT NULL
,"IXCREATOR" VARCHAR(128) NOT NULL
,"IXNAME" VARCHAR(128) NOT NULL
,"ENABLE" CHAR(1) NOT NULL
CHECK("ENABLE" IN('Y','N'))
,"MODE" CHAR(1) NOT NULL
CHECK("MODE" IN('C','D'))
,"UNIQUERULE" CHAR(1) NOT NULL
CHECK("UNIQUERULE" IN('D','U'))
,"COLCOUNT" SMALLINT NOT NULL
CHECK("COLCOUNT" > 0)
,"CLUSTERING" CHAR(1) NOT NULL
CHECK("CLUSTERING" IN('Y','N'))
,"NLEAF" INTEGER NOT NULL
CHECK("NLEAF" >= -1)
,"NLEVELS" SMALLINT NOT NULL
CHECK("NLEVELS" >= -1)
,"INDEXTYPE" CHAR(1) NOT NULL WITH DEFAULT
CHECK("INDEXTYPE" IN('D','2'))
,"PGSIZE" SMALLINT NOT NULL
CHECK("PGSIZE" IN(4, 8, 16, 32))
,"FIRSTKEYCARDF" FLOAT NOT NULL WITH DEFAULT -1
CHECK("FIRSTKEYCARDF" = -1
OR "FIRSTKEYCARDF" >= 0)
,"FULLKEYCARDF" FLOAT NOT NULL WITH DEFAULT -1
CHECK("FULLKEYCARDF" = -1
OR "FULLKEYCARDF" >= 0)
,"CLUSTERRATIOF" FLOAT NOT NULL WITH DEFAULT -1
CHECK("CLUSTERRATIOF" = -1
OR "CLUSTERRATIOF" >= 0)
,"PADDED" CHAR(1) NOT NULL WITH DEFAULT
CHECK("PADDED" IN(' ','Y','N'))
,"COLNO1" SMALLINT
CHECK("COLNO1" IS NULL
OR "COLNO1" > 0)
,"ORDERING1" CHAR(1)
CHECK("ORDERING1" IS NULL
OR "ORDERING1" IN('A','D'))
,"COLNO2" SMALLINT
CHECK("COLNO2" IS NULL
OR "COLNO2" > 0)
,"ORDERING2" CHAR(1)
CHECK("ORDERING2" IS NULL
OR "ORDERING2" IN('A','D'))
,"COLNO3" SMALLINT
CHECK("COLNO3" IS NULL
OR "COLNO3" > 0)
,"ORDERING3" CHAR(1)
CHECK("ORDERING3" IS NULL
OR "ORDERING3" IN('A','D'))
,"COLNO4" SMALLINT
CHECK("COLNO4" IS NULL
OR "COLNO4" > 0)
,"ORDERING4" CHAR(1)
CHECK("ORDERING4" IS NULL
OR "ORDERING4" IN('A','D'))
,"COLNO5" SMALLINT
CHECK("COLNO5" IS NULL
OR "COLNO5" > 0)
,"ORDERING5" CHAR(1)
CHECK("ORDERING5" IS NULL
OR "ORDERING5" IN('A','D'))
,"COLNO6" SMALLINT
CHECK("COLNO6" IS NULL
OR "COLNO6" > 0)
,"ORDERING6" CHAR(1)
CHECK("ORDERING6" IS NULL
OR "ORDERING6" IN('A','D'))
,"COLNO7" SMALLINT
CHECK("COLNO7" IS NULL
OR "COLNO7" > 0)
,"ORDERING7" CHAR(1)
CHECK("ORDERING7" IS NULL
OR "ORDERING7" IN('A','D'))
,"COLNO8" SMALLINT
CHECK("COLNO8" IS NULL
OR "COLNO8" > 0)
,"ORDERING8" CHAR(1)
CHECK("ORDERING8" IS NULL
OR "ORDERING8" IN('A','D'))
,"COLNO9" SMALLINT
CHECK("COLNO9" IS NULL
OR "COLNO9" > 0)
,"ORDERING9" CHAR(1)
CHECK("ORDERING9" IS NULL
OR "ORDERING9" IN('A','D'))
,"COLNO10" SMALLINT
CHECK("COLNO10" IS NULL
OR "COLNO10" > 0)
,"ORDERING10" CHAR(1)
CHECK("ORDERING10" IS NULL
OR "ORDERING10" IN('A','D'))
,"COLNO11" SMALLINT
CHECK("COLNO11" IS NULL
OR "COLNO11" > 0)
,"ORDERING11" CHAR(1)
CHECK("ORDERING11" IS NULL
OR "ORDERING11" IN('A','D'))
,"COLNO12" SMALLINT
CHECK("COLNO12" IS NULL
OR "COLNO12" > 0)
,"ORDERING12" CHAR(1)
CHECK("ORDERING12" IS NULL
OR "ORDERING12" IN('A','D'))
,"COLNO13" SMALLINT
CHECK("COLNO13" IS NULL
OR "COLNO13" > 0)
,"ORDERING13" CHAR(1)
CHECK("ORDERING13" IS NULL
OR "ORDERING13" IN('A','D'))
,"COLNO14" SMALLINT
CHECK("COLNO14" IS NULL
OR "COLNO14" > 0)
,"ORDERING14" CHAR(1)
CHECK("ORDERING14" IS NULL
OR "ORDERING14" IN('A','D'))
,"COLNO15" SMALLINT
CHECK("COLNO15" IS NULL
OR "COLNO15" > 0)
,"ORDERING15" CHAR(1)
CHECK("ORDERING15" IS NULL
OR "ORDERING15" IN('A','D'))
,"COLNO16" SMALLINT
CHECK("COLNO16" IS NULL
OR "COLNO16" > 0)
,"ORDERING16" CHAR(1)
CHECK("ORDERING16" IS NULL
OR "ORDERING16" IN('A','D'))
,"COLNO17" SMALLINT
CHECK("COLNO17" IS NULL
OR "COLNO17" > 0)
,"ORDERING17" CHAR(1)
CHECK("ORDERING17" IS NULL
OR "ORDERING17" IN('A','D'))
,"COLNO18" SMALLINT
CHECK("COLNO18" IS NULL
OR "COLNO18" > 0)
,"ORDERING18" CHAR(1)
CHECK("ORDERING18" IS NULL
OR "ORDERING18" IN('A','D'))
,"COLNO19" SMALLINT
CHECK("COLNO19" IS NULL
OR "COLNO19" > 0)
,"ORDERING19" CHAR(1)
CHECK("ORDERING19" IS NULL
OR "ORDERING19" IN('A','D'))
,"COLNO20" SMALLINT
CHECK("COLNO20" IS NULL
OR "COLNO20" > 0)
,"ORDERING20" CHAR(1)
CHECK("ORDERING20" IS NULL
OR "ORDERING20" IN('A','D'))
,"COLNO21" SMALLINT
CHECK("COLNO21" IS NULL
OR "COLNO21" > 0)
,"ORDERING21" CHAR(1)
CHECK("ORDERING21" IS NULL
OR "ORDERING21" IN('A','D'))
,"COLNO22" SMALLINT
CHECK("COLNO22" IS NULL
OR "COLNO22" > 0)
,"ORDERING22" CHAR(1)
CHECK("ORDERING22" IS NULL
OR "ORDERING22" IN('A','D'))
,"COLNO23" SMALLINT
CHECK("COLNO23" IS NULL
OR "COLNO23" > 0)
,"ORDERING23" CHAR(1)
CHECK("ORDERING23" IS NULL
OR "ORDERING23" IN('A','D'))
,"COLNO24" SMALLINT
CHECK("COLNO24" IS NULL
OR "COLNO24" > 0)
,"ORDERING24" CHAR(1)
CHECK("ORDERING24" IS NULL
OR "ORDERING24" IN('A','D'))
,"COLNO25" SMALLINT
CHECK("COLNO25" IS NULL
OR "COLNO25" > 0)
,"ORDERING25" CHAR(1)
CHECK("ORDERING25" IS NULL
OR "ORDERING25" IN('A','D'))
,"COLNO26" SMALLINT
CHECK("COLNO26" IS NULL
OR "COLNO26" > 0)
,"ORDERING26" CHAR(1)
CHECK("ORDERING26" IS NULL
OR "ORDERING26" IN('A','D'))
,"COLNO27" SMALLINT
CHECK("COLNO27" IS NULL
OR "COLNO27" > 0)
,"ORDERING27" CHAR(1)
CHECK("ORDERING27" IS NULL
OR "ORDERING27" IN('A','D'))
,"COLNO28" SMALLINT
CHECK("COLNO28" IS NULL
OR "COLNO28" > 0)
,"ORDERING28" CHAR(1)
CHECK("ORDERING28" IS NULL
OR "ORDERING28" IN('A','D'))
,"COLNO29" SMALLINT
CHECK("COLNO29" IS NULL
OR "COLNO29" > 0)
,"ORDERING29" CHAR(1)
CHECK("ORDERING29" IS NULL
OR "ORDERING29" IN('A','D'))
,"COLNO30" SMALLINT
CHECK("COLNO30" IS NULL
OR "COLNO30" > 0)
,"ORDERING30" CHAR(1)
CHECK("ORDERING30" IS NULL
OR "ORDERING30" IN('A','D'))
,"COLNO31" SMALLINT
CHECK("COLNO31" IS NULL
OR "COLNO31" > 0)
,"ORDERING31" CHAR(1)
CHECK("ORDERING31" IS NULL
OR "ORDERING31" IN('A','D'))
,"COLNO32" SMALLINT
CHECK("COLNO32" IS NULL
OR "COLNO32" > 0)
,"ORDERING32" CHAR(1)
CHECK("ORDERING32" IS NULL
OR "ORDERING32" IN('A','D'))
,"COLNO33" SMALLINT
CHECK("COLNO33" IS NULL
OR "COLNO33" > 0)
,"ORDERING33" CHAR(1)
CHECK("ORDERING33" IS NULL
OR "ORDERING33" IN('A','D'))
,"COLNO34" SMALLINT
CHECK("COLNO34" IS NULL
OR "COLNO34" > 0)
,"ORDERING34" CHAR(1)
CHECK("ORDERING34" IS NULL
OR "ORDERING34" IN('A','D'))
,"COLNO35" SMALLINT
CHECK("COLNO35" IS NULL
OR "COLNO35" > 0)
,"ORDERING35" CHAR(1)
CHECK("ORDERING35" IS NULL
OR "ORDERING35" IN('A','D'))
,"COLNO36" SMALLINT
CHECK("COLNO36" IS NULL
OR "COLNO36" > 0)
,"ORDERING36" CHAR(1)
CHECK("ORDERING36" IS NULL
OR "ORDERING36" IN('A','D'))
,"COLNO37" SMALLINT
CHECK("COLNO37" IS NULL
OR "COLNO37" > 0)
,"ORDERING37" CHAR(1)
CHECK("ORDERING37" IS NULL
OR "ORDERING37" IN('A','D'))
,"COLNO38" SMALLINT
CHECK("COLNO38" IS NULL
OR "COLNO38" > 0)
,"ORDERING38" CHAR(1)
CHECK("ORDERING38" IS NULL
OR "ORDERING38" IN('A','D'))
,"COLNO39" SMALLINT
CHECK("COLNO39" IS NULL
OR "COLNO39" > 0)
,"ORDERING39" CHAR(1)
CHECK("ORDERING39" IS NULL
OR "ORDERING39" IN('A','D'))
,"COLNO40" SMALLINT
CHECK("COLNO40" IS NULL
OR "COLNO40" > 0)
,"ORDERING40" CHAR(1)
CHECK("ORDERING40" IS NULL
OR "ORDERING40" IN('A','D'))
,"COLNO41" SMALLINT
CHECK("COLNO41" IS NULL
OR "COLNO41" > 0)
,"ORDERING41" CHAR(1)
CHECK("ORDERING41" IS NULL
OR "ORDERING41" IN('A','D'))
,"COLNO42" SMALLINT
CHECK("COLNO42" IS NULL
OR "COLNO42" > 0)
,"ORDERING42" CHAR(1)
CHECK("ORDERING42" IS NULL
OR "ORDERING42" IN('A','D'))
,"COLNO43" SMALLINT
CHECK("COLNO43" IS NULL
OR "COLNO43" > 0)
,"ORDERING43" CHAR(1)
CHECK("ORDERING43" IS NULL
OR "ORDERING43" IN('A','D'))
,"COLNO44" SMALLINT
CHECK("COLNO44" IS NULL
OR "COLNO44" > 0)
,"ORDERING44" CHAR(1)
CHECK("ORDERING44" IS NULL
OR "ORDERING44" IN('A','D'))
,"COLNO45" SMALLINT
CHECK("COLNO45" IS NULL
OR "COLNO45" > 0)
,"ORDERING45" CHAR(1)
CHECK("ORDERING45" IS NULL
OR "ORDERING45" IN('A','D'))
,"COLNO46" SMALLINT
CHECK("COLNO46" IS NULL
OR "COLNO46" > 0)
,"ORDERING46" CHAR(1)
CHECK("ORDERING46" IS NULL
OR "ORDERING46" IN('A','D'))
,"COLNO47" SMALLINT
CHECK("COLNO47" IS NULL
OR "COLNO47" > 0)
,"ORDERING47" CHAR(1)
CHECK("ORDERING47" IS NULL
OR "ORDERING47" IN('A','D'))
,"COLNO48" SMALLINT
CHECK("COLNO48" IS NULL
OR "COLNO48" > 0)
,"ORDERING48" CHAR(1)
CHECK("ORDERING48" IS NULL
OR "ORDERING48" IN('A','D'))
,"COLNO49" SMALLINT
CHECK("COLNO49" IS NULL
OR "COLNO49" > 0)
,"ORDERING49" CHAR(1)
CHECK("ORDERING49" IS NULL
OR "ORDERING49" IN('A','D'))
,"COLNO50" SMALLINT
CHECK("COLNO50" IS NULL
OR "COLNO50" > 0)
,"ORDERING50" CHAR(1)
CHECK("ORDERING50" IS NULL
OR "ORDERING50" IN('A','D'))
,"COLNO51" SMALLINT
CHECK("COLNO51" IS NULL
OR "COLNO51" > 0)
,"ORDERING51" CHAR(1)
CHECK("ORDERING51" IS NULL
OR "ORDERING51" IN('A','D'))
,"COLNO52" SMALLINT
CHECK("COLNO52" IS NULL
OR "COLNO52" > 0)
,"ORDERING52" CHAR(1)
CHECK("ORDERING52" IS NULL
OR "ORDERING52" IN('A','D'))
,"COLNO53" SMALLINT
CHECK("COLNO53" IS NULL
OR "COLNO53" > 0)
,"ORDERING53" CHAR(1)
CHECK("ORDERING53" IS NULL
OR "ORDERING53" IN('A','D'))
,"COLNO54" SMALLINT
CHECK("COLNO54" IS NULL
OR "COLNO54" > 0)
,"ORDERING54" CHAR(1)
CHECK("ORDERING54" IS NULL
OR "ORDERING54" IN('A','D'))
,"COLNO55" SMALLINT
CHECK("COLNO55" IS NULL
OR "COLNO55" > 0)
,"ORDERING55" CHAR(1)
CHECK("ORDERING55" IS NULL
OR "ORDERING55" IN('A','D'))
,"COLNO56" SMALLINT
CHECK("COLNO56" IS NULL
OR "COLNO56" > 0)
,"ORDERING56" CHAR(1)
CHECK("ORDERING56" IS NULL
OR "ORDERING56" IN('A','D'))
,"COLNO57" SMALLINT
CHECK("COLNO57" IS NULL
OR "COLNO57" > 0)
,"ORDERING57" CHAR(1)
CHECK("ORDERING57" IS NULL
OR "ORDERING57" IN('A','D'))
,"COLNO58" SMALLINT
CHECK("COLNO58" IS NULL
OR "COLNO58" > 0)
,"ORDERING58" CHAR(1)
CHECK("ORDERING58" IS NULL
OR "ORDERING58" IN('A','D'))
,"COLNO59" SMALLINT
CHECK("COLNO59" IS NULL
OR "COLNO59" > 0)
,"ORDERING59" CHAR(1)
CHECK("ORDERING59" IS NULL
OR "ORDERING59" IN('A','D'))
,"COLNO60" SMALLINT
CHECK("COLNO60" IS NULL
OR "COLNO60" > 0)
,"ORDERING60" CHAR(1)
CHECK("ORDERING60" IS NULL
OR "ORDERING60" IN('A','D'))
,"COLNO61" SMALLINT
CHECK("COLNO61" IS NULL
OR "COLNO61" > 0)
,"ORDERING61" CHAR(1)
CHECK("ORDERING61" IS NULL
OR "ORDERING61" IN('A','D'))
,"COLNO62" SMALLINT
CHECK("COLNO62" IS NULL
OR "COLNO62" > 0)
,"ORDERING62" CHAR(1)
CHECK("ORDERING62" IS NULL
OR "ORDERING62" IN('A','D'))
,"COLNO63" SMALLINT
CHECK("COLNO63" IS NULL
OR "COLNO63" > 0)
,"ORDERING63" CHAR(1)
CHECK("ORDERING63" IS NULL
OR "ORDERING63" IN('A','D'))
,"COLNO64" SMALLINT
CHECK("COLNO64" IS NULL
OR "COLNO64" > 0)
,"ORDERING64" CHAR(1)
CHECK("ORDERING64" IS NULL
OR "ORDERING64" IN('A','D'))
)
IN ${db}.${ts}J
CCSID UNICODE;
CREATE INDEX $cr.DSN_VIRTUAL_INDEXES_idx1$suf
ON $cr.DSN_VIRTUAL_INDEXES$suf
( "TBCREATOR"
,"TBNAME"
);
commit;
$]
$@tb={filt DSN_FILTER_TABLE}
if $filt == '17 PUSHDOWN' then $@=[ $** already v10 ok
$] else if $filt == '14 GROUP_MEMBER' then $@=[ $** already v9 ok
$] else if $filt == '11 EXPLAIN_TIME' then $@=[
$=doReo=1
ALTER TABLE $cr.DSN_FILTER_Table$suf
ALTER "APPLNAME" SET DATA TYPE VARCHAR(24);
ALTER TABLE $cr.DSN_FILTER_Table$suf
ADD "MIXOPSEQNO" SMALLINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_FILTER_Table$suf
ADD "REEVAL" CHAR(1) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_FILTER_Table$suf
ADD "GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DEFAULT;
$] else if $filt == '' & $fun \== 'v' then $@=[
-----------------------------------------------------------------------
--CREATE THE V9 SAMPLE DSN_FILTER_Table$suf AND ITS INDEX
CREATE TABLE $cr.DSN_FILTER_Table$suf
( "QUERYNO" INTEGER NOT NULL
,"QBLOCKNO" SMALLINT NOT NULL
,"PLANNO" SMALLINT NOT NULL
,"APPLNAME" VARCHAR(24) NOT NULL
,"PROGNAME" VARCHAR(128) NOT NULL
,"COLLID" VARCHAR(128) NOT NULL WITH DEFAULT
,"ORDERNO" INTEGER NOT NULL
,"PREDNO" INTEGER NOT NULL
,"STAGE" CHAR(9) NOT NULL
,"ORDERCLASS" INTEGER NOT NULL
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"MIXOPSEQNO" SMALLINT NOT NULL
,"REEVAL" CHAR(1) NOT NULL
,"GROUP_MEMBER" VARCHAR(24) NOT NULL
)
IN ${db}.${ts}X
VOLATILE
CCSID UNICODE;
CREATE INDEX $cr.DSN_FILTER_TABLE_idx1$suf
ON $cr.DSN_FILTER_Table$suf
( "QUERYNO"
,"EXPLAIN_TIME"
);
$] else call err 'cannot update DSN_FILTER_TABLE'$suf 'from' $filt
$@tb={pred DSN_PREDICAT_TABLE}
if $pred == '41 VERSION' then $@=[ $** already v10 ok
$] else if $pred == '36 GROUP_MEMBER' then $@=[ $** already v9 ok
$] else if $pred == '34 LITERALS' & $fun == 'u' then $@=[
$=doReo=1
ALTER TABLE $cr.DSN_PREDICAT_Table$suf
ALTER "PROGNAME" SET DATA TYPE VARCHAR(128);
ALTER TABLE $cr.DSN_PREDICAT_Table$suf
ALTER "LEFT_HAND_SIDE" SET DATA TYPE VARCHAR(128);
ALTER TABLE $cr.DSN_PREDICAT_Table$suf
ADD "CLAUSE" CHAR(8) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_PREDICAT_Table$suf
ALTER "APPLNAME" SET DATA TYPE VARCHAR(24);
ALTER TABLE $cr.DSN_PREDICAT_Table$suf
ADD "GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DEFAULT;
$] else if $pred == '' & $fun \== 'v' then $@=[
-----------------------------------------------------------------------
--CREATE THE V9 SAMPLE DSN_PREDICAT_Table$suf AND ITS INDEX
CREATE TABLE $cr.DSN_PREDICAT_Table$suf
( "QUERYNO" INTEGER NOT NULL
,"QBLOCKNO" SMALLINT NOT NULL
,"APPLNAME" VARCHAR(24) NOT NULL
,"PROGNAME" VARCHAR(128) NOT NULL
,"PREDNO" INTEGER NOT NULL
,"TYPE" CHAR(8) NOT NULL
,"LEFT_HAND_SIDE" VARCHAR(128) NOT NULL
,"LEFT_HAND_PNO" INTEGER NOT NULL
,"LHS_TABNO" SMALLINT NOT NULL
,"LHS_QBNO" SMALLINT NOT NULL
,"RIGHT_HAND_SIDE" VARCHAR(128) NOT NULL
,"RIGHT_HAND_PNO" INTEGER NOT NULL
,"RHS_TABNO" SMALLINT NOT NULL
,"RHS_QBNO" SMALLINT NOT NULL
,"FILTER_FACTOR" FLOAT NOT NULL
,"BOOLEAN_TERM" CHAR(1) NOT NULL
,"SEARCHARG" CHAR(1) NOT NULL
,"JOIN" CHAR(1) NOT NULL
,"AFTER_JOIN" CHAR(1) NOT NULL
,"ADDED_PRED" CHAR(1) NOT NULL
,"REDUNDANT_PRED" CHAR(1) NOT NULL
,"DIRECT_ACCESS" CHAR(1) NOT NULL
,"KEYFIELD" CHAR(1) NOT NULL
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"CATEGORY" SMALLINT NOT NULL
,"CATEGORY_B" SMALLINT NOT NULL
,"TEXT" VARCHAR(2000) NOT NULL
,"PRED_ENCODE" CHAR(1) NOT NULL WITH DEFAULT
,"PRED_CCSID" SMALLINT NOT NULL WITH DEFAULT
,"PRED_MCCSID" SMALLINT NOT NULL WITH DEFAULT
,"MARKER" CHAR(1) NOT NULL WITH DEFAULT
,"PARENT_PNO" INTEGER NOT NULL
,"NEGATION" CHAR(1) NOT NULL
,"LITERALS" VARCHAR(128) NOT NULL
,"CLAUSE" CHAR(8) NOT NULL
,"GROUP_MEMBER" VARCHAR(24) NOT NULL
)
IN ${db}.${ts}X
VOLATILE
CCSID UNICODE;
CREATE INDEX $cr.DSN_PREDICAT_TABLE_idx1$suf
ON $cr.DSN_PREDICAT_Table$suf
( "QUERYNO"
,"EXPLAIN_TIME"
);
$] else call err 'cannot update DSN_PREDICAT_Table'$suf 'from' $pred
if $pred \== '' then $@[
$@tbCol={ccss DSN_PREDICAT_Table$suf PRED_CCSSID}
if $ccss \= 1 then $@=[
$] else if $fun \== 'v' then $@=[
$=doReo=1
ALTER TABLE $cr.DSN_PREDICAT_Table$suf
rename column PRED_CCSSID to PRED_CCSID;
ALTER TABLE $cr.DSN_PREDICAT_Table$suf
rename column PRED_MCCSSID to PRED_MCCSID;
$] else call err 'cannot update PRED_CCSSID'
$]
$@tb={viewref DSN_VIEWREF_TABLE}
if $viewref == '12 SECTNOI' then $@=[ $** already v10 ok
$] else if $viewref == '11 GROUP_MEMBER' then $@=[ $** already v9 ok
$] else if $viewref == '10 EXPLAIN_TIME' & $fun == 'u' then $@=[
$=doReo=1
--alter DSN_VIEWREF_Table$suf from v8
ALTER TABLE $cr.DSN_VIEWREF_Table$suf
ALTER "APPLNAME" SET DATA TYPE VARCHAR(24);
ALTER TABLE $cr.DSN_VIEWREF_Table$suf
ALTER "VERSION" SET DATA TYPE VARCHAR(122);
ALTER TABLE $cr.DSN_VIEWREF_Table$suf
ADD "GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DEFAULT;
$] else if $viewref == '' & fun \== 'v' then $@=[
-----------------------------------------------------------------------
--CREATE THE V9 SAMPLE DSN_VIEWREF_Table$suf AND ITS INDEX
CREATE TABLE $cr.DSN_VIEWREF_Table$suf
( "QUERYNO" INTEGER NOT NULL WITH DEFAULT
,"APPLNAME" VARCHAR(24) NOT NULL WITH DEFAULT
,"PROGNAME" VARCHAR(128) NOT NULL WITH DEFAULT
,"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
,"COLLID" VARCHAR(128) NOT NULL WITH DEFAULT
,"CREATOR" VARCHAR(128) NOT NULL WITH DEFAULT
,"NAME" VARCHAR(128) NOT NULL WITH DEFAULT
,"TYPE" CHAR(1) NOT NULL WITH DEFAULT
,"MQTUSE" SMALLINT NOT NULL WITH DEFAULT
,"EXPLAIN_TIME" TIMESTAMP NOT NULL WITH DEFAULT
,"GROUP_MEMBER" VARCHAR(24) NOT NULL
)
IN ${db}.${ts}X
VOLATILE
CCSID UNICODE;
CREATE INDEX $cr.DSN_VIEWREF_TABLE_idx1$suf
ON $cr.DSN_VIEWREF_Table$suf
( "QUERYNO"
,"EXPLAIN_TIME"
);
$] else call err 'cannot update' $cr'.DSN_VIEWREF_TABLE from' $viewref
if pos($fun, 'cd') > 0 then $@=[
-----------------------------------------------------------------------
-- fileAid plan table
CREATE TABLE $cr.F2PLAN_TABLE$suf
(TIMESTAMP CHAR(16) FOR SBCS DATA NOT NULL
WITH DEFAULT,
STMTNO INTEGER NOT NULL WITH DEFAULT,
SEQNO INTEGER NOT NULL WITH DEFAULT,
TEXT VARCHAR(254) FOR SBCS DATA NOT NULL
WITH DEFAULT,
REMARKS CHAR(64) FOR SBCS DATA NOT NULL
WITH DEFAULT)
IN ${db}.${ts}X
AUDIT NONE
DATA CAPTURE NONE
NOT VOLATILE
APPEND NO ;
$]
if $suf == '' then $@=[
--- versionen, compiles und explains eines packages -------------------
alter function $path.fosFmtE7(r real) deterministic ;
alter function $path.fosFmtE8(r float) deterministic ;
alter function $path.fosFmtE7(r float) deterministic ;
alter function $path.fosFmtE8(r real) deterministic ;
alter function $path.fosFmtE7(r real) deterministic ;
alter function $path.fosFmtE(v varchar(30), decP int) deterministic;
alter function $path.fosFmtE(ma real,ex int,decP int) deterministic;
alter function $path.fosFmtL0(n int, l int) deterministic ;
alter function $path.fosFmtL0(n varchar(11), l int) deterministic ;
--- versionen, compiles und explains eines packages -------------------
-- does not work if plan_table ebcdic ||
create view $cr.plan_view0 as
with e as (
select p.collid
, p.name prog
, p.version
, count(e.collid) expCnt
, e.bind_Time
, case when e.bind_time is null then 'p'
when e.bind_time > min(p.bindTime) then '>'
when e.bind_time = min(p.bindTime) then '='
else '<' end c
, min(p.pcTimeStamp) pcTimestamp
, sum(case when hint_Used = '' then 0 else 1 end) hiUse
, sum(case when e.optHint = '' then 0 else 1 end) optHi
, max(max(e.optHint, hint_Used)) hint
from sysIbm.sysPackage p
left join $cr.plan_table e
on e.collid = p.collid and e.progName = p.name
and e.version = p.version
where p.location = ''
group by p.collid, p.name, p.version, e.bind_Time
union all select collid
, progName prog
, version
, count(e.collid) expCnt
, e.bind_Time
, 'e' c
, case when 1=0 then current timestamp else null end pcTimestamp
, sum(case when hint_Used = '' then 0 else 1 end) hiUse
, sum(case when e.optHint = '' then 0 else 1 end) optHi
, max(max(e.optHint, hint_Used)) hint
from CMNBATCH.plan_table e
where not exists (select 1 from sysibm.sysPackage p
where e.collid = p.collid and e.progName = p.name
and e.version = p.version and p.location = '' )
group by e.collid, e.progName, e.version, e.bind_Time
)
select substr(collid, 1, 6) "collid"
, substr(prog, 1, 8) "prog"
, substr(version, 1, 12) "version"
, smallint(expCnt) "expCnt"
, bind_time, c, pcTimestamp
, smallint(hiUse) "hiUse"
, smallint(optHi) "optHi"
, substr(hint, 1, 8) "hint"
, collid, prog, version
, coalesce(pcTimestamp, bind_time) pcBi
from e
;
CREATE VIEW $cr.PLAN_VIEW1 AS
select substr(right(' ' || strip(char(queryNo)) , 6)
|| right(' ' || strip(char(qBlockNo)) , 2)
|| right(' ' || strip(char(planNo)) , 2)
|| right(' ' || strip(char(mixOpSeq)), 1)
,1 ,11) "Queryn B PM",
QBLOCK_TYPE AS "TYPE",
CASE WHEN PRIMARY_ACCESSTYPE = 'D' then 'dirRow'
WHEN ACCESSTYPE = 'I ' THEN 'ixScan'
WHEN ACCESSTYPE = 'I1' THEN 'ixOne '
WHEN ACCESSTYPE = 'R ' THEN 'tsScan'
WHEN ACCESSTYPE = 'RW' THEN 'woScan'
WHEN ACCESSTYPE = 'N ' THEN 'ixSPin'
WHEN ACCESSTYPE = 'M ' THEN 'ixMult'
WHEN ACCESSTYPE = 'MX' THEN 'ixMSca'
WHEN ACCESSTYPE = 'MI' THEN 'ixMInt'
WHEN ACCESSTYPE = 'MU' THEN 'ixMUni'
WHEN ACCESSTYPE = 'T ' THEN 'ixSPRS'
WHEN ACCESSTYPE = 'V ' THEN 'insBuf'
WHEN ACCESSTYPE = ' ' THEN ' '
ELSE '??' || accessType
END AS ACCESS,
CASE WHEN ACCESSTYPE = 'R ' THEN ' '
when PRIMARY_ACCESSTYPE = 'D' THEN ' '
ELSE SUBSTR(ACCESSNAME, 1, 12) END AS "INDEX",
SUBSTR(TNAME, 1, 12) AS "TABLE",
CASE WHEN TABLE_TYPE IS NULL THEN ' '
ELSE TABLE_TYPE END AS TTYP,
CASE WHEN METHOD = 3 THEN ' '
WHEN ACCESSTYPE = 'R ' THEN ' '
ELSE SUBSTR(DIGITS(MATCHCOLS), 5, 1) || ' ' || indexOnly
END AS MC_O,
CASE METHOD WHEN 0 THEN ' '
WHEN 1 THEN 'NLJOIN'
when 2 THEN 'SMJOIN'
WHEN 3 THEN 'SORT '
WHEN 4 THEN 'HYJOIN'
ELSE NULL END AS METHOD,
CASE JOIN_TYPE WHEN 'F' THEN 'full'
WHEN 'L' THEN 'left'
WHEN 'S' THEN 'star'
ELSE ' ' END AS "joiT",
SORTN_UNIQ CONCAT SORTN_JOIN CONCAT SORTN_ORDERBY CONCAT
SORTN_GROUPBY AS UJOG,
SORTC_UNIQ CONCAT SORTC_JOIN CONCAT SORTC_ORDERBY CONCAT
SORTC_GROUPBY AS UJOC,
TSLOCKMODE AS LCK,
SUBSTR(CORRELATION_NAME, 1, 4) AS CORR,
CASE PARALLELISM_MODE WHEN 'C' THEN 'CPU'
WHEN 'I' THEN 'I-O'
WHEN 'X' THEN 'SYSPLEX'
ELSE NULL END AS PARAL,
STRIP(DIGITS(ACCESS_DEGREE), LEADING, '0') || ' '
|| STRIP(DIGITS(JOIN_DEGREE), LEADING, '0') AS AJ_DEG,
STRIP(DIGITS(ACCESS_PGROUP_ID), LEADING, '0')|| ' '
|| STRIP(DIGITS(JOIN_PGROUP_ID), LEADING, '0') PG_DEG,
STRIP(DIGITS(MERGE_JOIN_COLS), LEADING, '0') AS MC,
PREFETCH AS PRE,
page_range as pgRa,
substr(opthint, 1, 10) optHint,
substr(hint_used, 1, 10) hint_used,
-- full length names
TNAME, ACCESSNAME, accessType,
-- package identifikation
collid, progName, applName, version, bind_time,
-- query node identificaten
queryno, qBlockNo, planno, mixopSeq, timestamp,
PARENT_QBLOCKNO
FROM $cr.PLAN_TABLE A
;
------------------------------------------------------------------------
CREATE VIEW $cr.PLAN_VIEW2 AS
SELECT CASE WHEN METHOD = 'SORT ' THEN ' '
ELSE substr(right(' '
|| strip(CHAR(S.PROCMS)),9), 1, 9) END AS MSEC,
a.*,
S.COST_CATEGORY,
S.PROCMS, S.PROCSU, S.REASON
FROM $cr.PLAN_VIEW1 A
LEFT OUTER JOIN
$cr.DSN_STATEMNT_TABLE S
ON S."COLLID" = A."COLLID"
AND S.APPLNAME = A.APPLNAME
AND S.PROGNAME = A.PROGNAME
AND S.EXPLAIN_TIME = A.BIND_TIME
AND S.QUERYNO = A.QUERYNO
;
------------------------------------------------------------------------
CREATE VIEW $cr.PLAN_VIEW2Det AS
SELECT substr(fosFmtE7(dmRows), 1, 7) dmRows,
substr(fosFmtE7(rdsRow), 1, 7) rdsRow,
substr(fosFmtE7(snRows), 1, 7) snRows,
substr(fosFmtE7(compCost), 1, 7) compCost,
substr(fosFmtE7(openCost), 1, 7) openCost,
a.*
FROM $cr.PLAN_VIEW1 A
LEFT OUTER JOIN
$cr.DSN_DetCost_TABLE d
on --d.APPLNAME = A.APPLNAME ??? ist manchmal x'0000
d.PROGNAME = A.PROGNAME
AND d.EXPLAIN_TIME = A.BIND_TIME
AND d.QueryNO = A.QueryNO
AND d.QBlockNO = A.QBlockNO
AND d.PlanNo = A.PlanNo
;
CREATE VIEW $cr.plan_ViewPred as
select a."Queryn B PM" "Queryn_B_PM"
, smallint(p.PREDNO) predno
, f.stage
-- , substr(fosFmte7(p.filter_factor), 1, 7) ff
-- optimizer otherwise choose bad path|||
, real(p.FILTER_FACTOR) FILTER_FACTOR
, p.type prTy
, p.boolean_term || ' ' || p.negation "BoN"
, p.text
, p.LEFT_HAND_SIDE
, p.LEFT_HAND_PNO
, p.LHS_TABNO
, p.LHS_QBNO
, p.RIGHT_HAND_SIDE
, p.RIGHT_HAND_PNO
, p.RHS_TABNO
, p.RHS_QBNO
, p.BOOLEAN_TERM
, p.SEARCHARG
, p.JOIN
, p.AFTER_JOIN
, p.ADDED_PRED
, p.REDUNDANT_PRED
, p.DIRECT_ACCESS
, p.KEYFIELD
, p.CATEGORY
, p.CATEGORY_B
, p.PRED_ENCODE
, p.PRED_CCSID
, p.PRED_MCCSID
, p.MARKER
, p.PARENT_PNO
, p.NEGATION
, p.LITERALS
, p.CLAUSE
, f.reEval
, f.ORDERNO
, a.*
from $cr.plan_view1 a
left join $cr.dsn_filter_Table f
on f.applName = a.applName
and f.collid = a.collid
and f.progName = a.progName
and f.explain_time = a.bind_time
and f.QUERYNO = a.QUERYNO
and f.QBLOCKNO = a.QBLOCKNO
and f.PLANNO = a.PLANNO
and f.mixOpSeqNo = a.mixOpSeq
and a.accessType not in ('MX', 'MI', 'MU')
left join $cr.dsn_predicat_table p
on --p.applName = a.applName ??? ist manchmal x'0000
p.progName = a.progName
and p.explain_time = a.bind_time
and p.queryNo = a.queryNo
and p.qBlockNo = a.qBlockNo
and p.predNo = f.predNo
;
-- sortierung --------------------------------------
-- order by applName, collid, progName, bind_time,
-- queryNo, qBlockNo, planno, mixOpSeq,
-- stage, predNo
;
------------------------------------------------------------------------
-- use of view1 instead of view2 directly
-- because otherwise optimizer makes TS Scan ||||
CREATE VIEW $cr.PLAN_VIEW5 AS
with l as
( select collid, progName, max(r.bind_time) bind_time
FROM $cr.PLAN_TABLE r
group by collid, progName
)
select CASE WHEN METHOD = 'SORT ' THEN ' '
ELSE substr(right(' '
|| strip(CHAR(S.PROCMS)),9), 1, 9) END AS MSEC
, a.*
, S.COST_CATEGORY,
S.PROCMS, S.PROCSU, S.REASON
FROM l
join $cr.PLAN_VIEW1 a
on A.collid = l.collid
AND A.PROGNAME = l.PROGNAME
AND A.bind_time = l.bind_time
LEFT OUTER JOIN
$cr.DSN_STATEMNT_TABLE S
ON S."COLLID" = A."COLLID"
AND S.APPLNAME = A.APPLNAME
AND S.PROGNAME = A.PROGNAME
AND S.EXPLAIN_TIME = A.BIND_TIME
AND S.QUERYNO = A.QUERYNO
;
commit;
------------ Ende user Views ----- cmnViews = $cmnViews----------------
$]
if $cmnViews & $suf == '' then $@=[
------------ diese views braucht es nur für changeman -----------------
------------------------------------------------------------------------
CREATE VIEW $cr.PLAN_VIEW3
( QN,QB,AP,PG, PN,ME,CR,TN,TO, AT,JT,MC,AN,IO, SP,SU,SJ, SO, SG,ZP,ZU,
ZJ, ZO,ZG,TL,TS,PR,EV, CO,VR,MO, AD,AI, JD,JI, PA,MJ,CN, PF,GM,WO,QT,
BT,RM,SD, OH,HU,PAC )
AS SELECT QUERYNO, QBLOCKNO, APPLNAME, PROGNAME, PLANNO, METHOD,
CREATOR, TNAME, TABNO, ACCESSTYPE, JOIN_TYPE, MATCHCOLS, ACCESSNAME,
INDEXONLY, SORTN_PGROUP_ID, SORTN_UNIQ, SORTN_JOIN, SORTN_ORDERBY,
SORTN_GROUPBY, SORTC_PGROUP_ID, SORTC_UNIQ, SORTC_JOIN, SORTC_ORDERBY,
SORTC_GROUPBY, TSLOCKMODE, TIMESTAMP, PREFETCH, COLUMN_FN_EVAL, COLLID,
VERSION, MIXOPSEQ, ACCESS_DEGREE, ACCESS_PGROUP_ID, JOIN_DEGREE,
JOIN_PGROUP_ID, PARALLELISM_MODE, MERGE_JOIN_COLS, CORRELATION_NAME,
PAGE_RANGE, GROUP_MEMBER, WHEN_OPTIMIZE, QBLOCK_TYPE, BIND_TIME,
REMARKS, IBM_SERVICE_DATA, OPTHINT, HINT_USED, PRIMARY_ACCESSTYPE
FROM $cr.PLAN_TABLE
;
------------------------------------------------------------------------
CREATE VIEW $cr.PLAN_VIEW6 AS
SELECT SUBSTR(A.PROGNAME, 1, 8) AS PROGNAME,
SUBSTR(DIGITS(A."QUERYNO"), 5) AS STMT,
CASE WHEN A.METHOD = 3
THEN ' '
-- ELSE SUBSTR(CHAR(S.PROCSU), 1, 7) END AS SUNITS,
ELSE '1 '
END AS SUNITS,
CASE WHEN A.PRIMARY_ACCESSTYPE = 'D' THEN 'DIR.ROW'
WHEN A.ACCESSTYPE = 'I ' THEN 'IX-SCAN'
WHEN A.ACCESSTYPE = 'I1' THEN 'IX-ONEF'
WHEN A.ACCESSTYPE = 'M ' THEN 'MULT-IX'
WHEN A.ACCESSTYPE = 'MX' THEN 'IX-SC.X'
WHEN A.ACCESSTYPE = 'MI' THEN 'IX-SC.I'
WHEN A.ACCESSTYPE = 'MU' THEN 'IX-SC.U'
WHEN A.ACCESSTYPE = 'N ' THEN 'IX-INLI'
WHEN A.ACCESSTYPE = 'R ' THEN 'TS-SCAN'
WHEN A.ACCESSTYPE = 'RW' THEN 'WF-SCAN'
WHEN A.ACCESSTYPE = 'T ' THEN 'IX-SPRS'
WHEN A.ACCESSTYPE = 'V ' THEN 'BUFFERS'
WHEN A.ACCESSTYPE = ' ' THEN ' '
ELSE ' ' END AS ACCESS,
CASE WHEN A.PRIMARY_ACCESSTYPE = 'D' THEN ' '
WHEN A.ACCESSTYPE = 'R ' THEN ' '
ELSE SUBSTR(A.ACCESSNAME, 1, 12) END AS "INDEX",
CASE WHEN A.TNAME = ' ' THEN ' '
ELSE SUBSTR(A.TNAME, 1, 12) END AS "TABLE",
SUBSTR(A.CORRELATION_NAME, 1, 5) AS CORR,
CASE WHEN A.METHOD = 3 THEN ' '
WHEN A.ACCESSTYPE = 'R ' THEN ' '
WHEN A.QBLOCK_TYPE = 'INSERT' THEN ' '
ELSE SUBSTR(DIGITS(A.MATCHCOLS), 5, 1) END AS MC,
CASE WHEN A.INDEXONLY = 'Y' THEN 'XO'
ELSE ' ' END AS XO,
CASE A.METHOD
WHEN 0 THEN '0 '
WHEN 1 THEN 'NLJOIN'
WHEN 2 THEN 'SMJOIN'
WHEN 3 THEN 'SORT '
WHEN 4 THEN 'HYJOIN'
ELSE CHAR(A.METHOD) END AS METHOD,
CASE A.JOIN_TYPE
WHEN 'F' THEN 'FULL '
WHEN 'L' THEN 'LEFT '
WHEN 'S' THEN 'STAR '
ELSE ' ' END AS "JOIN",
A.SORTN_UNIQ CONCAT A.SORTN_JOIN CONCAT A.SORTN_ORDERBY
CONCAT A.SORTN_GROUPBY AS UJOG,
A.SORTC_UNIQ CONCAT A.SORTC_JOIN CONCAT A.SORTC_ORDERBY
CONCAT A.SORTC_GROUPBY AS UJOC,
A.QBLOCK_TYPE AS QBTYPE,
CASE WHEN A.TABLE_TYPE IS NULL THEN ' '
WHEN A.TABLE_TYPE = 'B' THEN 'BUFFER'
WHEN A.TABLE_TYPE = 'C' THEN 'CTE '
WHEN A.TABLE_TYPE = 'F' THEN 'TBLFNC'
WHEN A.TABLE_TYPE = 'M' THEN 'MQT '
WHEN A.TABLE_TYPE = 'Q' THEN 'VMQT '
WHEN A.TABLE_TYPE = 'R' THEN 'RC#CTE'
WHEN A.TABLE_TYPE = 'T' THEN 'TABLE/'
WHEN A.TABLE_TYPE = 'W' THEN 'WRKFIL'
ELSE A.TABLE_TYPE END AS TTYP,
A.TSLOCKMODE AS LCK,
CASE A.PARALLELISM_MODE
WHEN 'C' THEN 'CPU '
WHEN 'I' THEN 'I-O '
WHEN 'X' THEN 'PLEX'
ELSE ' ' END AS PARAL,
STRIP(DIGITS(A.ACCESS_DEGREE), LEADING, '0') CONCAT ' '
CONCAT STRIP(DIGITS(A.JOIN_DEGREE), LEADING, '0')
AS AJ_DEG,
STRIP(DIGITS(A.ACCESS_PGROUP_ID), LEADING, '0') CONCAT ' '
CONCAT STRIP(DIGITS(A.JOIN_PGROUP_ID), LEADING, '0')
AS PG_DEG,
STRIP(DIGITS(A.MERGE_JOIN_COLS), LEADING, '0') AS MJC,
CASE A.PREFETCH
WHEN 'S' THEN 'SEQ '
WHEN 'L' THEN 'LIST'
WHEN 'D' THEN 'DYN '
ELSE ' ' END AS PREFETCH,
STRIP(DIGITS(A."QUERYNO"), LEADING, '0') AS QNO,
SUBSTR(DIGITS(A.QBLOCKNO), 4, 2) CONCAT ' '
CONCAT SUBSTR(DIGITS(A.PLANNO), 4, 2)
AS BL_PL,
A.PARENT_QBLOCKNO,
A.QBLOCKNO, A.PLANNO, A.TNAME, A.ACCESSNAME, A.OPTHINT,
A.HINT_USED, A.APPLNAME, A."COLLID", A.VERSION,
A.TIMESTAMP, A.BIND_TIME, A."QUERYNO", A.MIXOPSEQ, A.TABNO,
A.CORRELATION_NAME, A.COLUMN_FN_EVAL, A.SORTC_PGROUP_ID,
A.SORTN_PGROUP_ID, A.PAGE_RANGE, A.WHEN_OPTIMIZE,
A.TABLE_ENCODE, A.TABLE_SCCSID, A.ROUTINE_ID, A.CTEREF,
A.STMTTOKEN,
-- S.COST_CATEGORY, S.PROCMS, S.PROCSU, S.REASON
'A' AS COST_CATEGORY, 1 AS PROCMS, 1 AS PROCSU, ' ' AS REASON
FROM $cr.PLAN_TABLE A
-- JOIN
-- (SELECT B.PROGNAME AS BPROGNAME,
-- B.COLLID AS BCOLLID,
-- MAX(B.BIND_TIME) BBIND_TIME
-- FROM $cr.PLAN_TABLE B
-- GROUP BY B.PROGNAME, B.COLLID) AS N1
-- ON A.PROGNAME = N1.BPROGNAME
-- AND A.BIND_TIME = N1.BBIND_TIME
-- AND A.COLLID = N1.BCOLLID
-- LEFT OUTER JOIN $cr.DSN_STATEMNT_TABLE S
-- ON S."COLLID" = A."COLLID"
-- AND S.APPLNAME = A.APPLNAME
-- AND S.PROGNAME = A.PROGNAME
-- AND S."QUERYNO" = A."QUERYNO"
-- AND S.EXPLAIN_TIME = A.BIND_TIME
;
------------------------------------------------------------------------
CREATE VIEW $cr.PLAN_VIEW6_CMN AS
SELECT SUBSTR(A.PROGNAME, 1, 8) AS PROGNAME,
SUBSTR(DIGITS(A."QUERYNO"), 5) AS STMT,
CASE WHEN A.METHOD = 3
THEN ' '
-- ELSE SUBSTR(CHAR(S.PROCSU), 1, 7) END AS SUNITS,
ELSE '1 '
END AS SUNITS,
CASE WHEN A.PRIMARY_ACCESSTYPE = 'D' THEN 'DIR.ROW'
WHEN A.ACCESSTYPE = 'I ' THEN 'IX-SCAN'
WHEN A.ACCESSTYPE = 'I1' THEN 'IX-ONEF'
WHEN A.ACCESSTYPE = 'M ' THEN 'MULT-IX'
WHEN A.ACCESSTYPE = 'MX' THEN 'IX-SC.X'
WHEN A.ACCESSTYPE = 'MI' THEN 'IX-SC.I'
WHEN A.ACCESSTYPE = 'MU' THEN 'IX-SC.U'
WHEN A.ACCESSTYPE = 'N ' THEN 'IX-INLI'
WHEN A.ACCESSTYPE = 'R ' THEN 'TS-SCAN'
WHEN A.ACCESSTYPE = 'RW' THEN 'WF-SCAN'
WHEN A.ACCESSTYPE = 'T ' THEN 'IX-SPRS'
WHEN A.ACCESSTYPE = 'V ' THEN 'BUFFERS'
WHEN A.ACCESSTYPE = ' ' THEN ' '
ELSE ' ' END AS ACCESS,
CASE WHEN A.PRIMARY_ACCESSTYPE = 'D' THEN ' '
WHEN A.ACCESSTYPE = 'R ' THEN ' '
ELSE SUBSTR(A.ACCESSNAME, 1, 12) END AS "INDEX",
CASE WHEN A.TNAME = ' ' THEN ' '
ELSE SUBSTR(A.TNAME, 1, 12) END AS "TABLE",
SUBSTR(A.CORRELATION_NAME, 1, 5) AS CORR,
CASE WHEN A.METHOD = 3 THEN ' '
WHEN A.ACCESSTYPE = 'R ' THEN ' '
WHEN A.QBLOCK_TYPE = 'INSERT' THEN ' '
ELSE SUBSTR(DIGITS(A.MATCHCOLS), 5, 1) END AS MC,
CASE WHEN A.INDEXONLY = 'Y' THEN 'XO'
ELSE ' ' END AS XO,
CASE A.METHOD
WHEN 0 THEN '0 '
WHEN 1 THEN 'NLJOIN'
WHEN 2 THEN 'SMJOIN'
WHEN 3 THEN 'SORT '
WHEN 4 THEN 'HYJOIN'
ELSE CHAR(A.METHOD) END AS METHOD,
CASE A.JOIN_TYPE
WHEN 'F' THEN 'FULL '
WHEN 'L' THEN 'LEFT '
WHEN 'S' THEN 'STAR '
ELSE ' ' END AS "JOIN",
A.SORTN_UNIQ CONCAT A.SORTN_JOIN CONCAT A.SORTN_ORDERBY
CONCAT A.SORTN_GROUPBY AS UJOG,
A.SORTC_UNIQ CONCAT A.SORTC_JOIN CONCAT A.SORTC_ORDERBY
CONCAT A.SORTC_GROUPBY AS UJOC,
A.QBLOCK_TYPE AS QBTYPE,
CASE WHEN A.TABLE_TYPE IS NULL THEN ' '
WHEN A.TABLE_TYPE = 'B' THEN 'BUFFER'
WHEN A.TABLE_TYPE = 'C' THEN 'CTE '
WHEN A.TABLE_TYPE = 'F' THEN 'TBLFNC'
WHEN A.TABLE_TYPE = 'M' THEN 'MQT '
WHEN A.TABLE_TYPE = 'Q' THEN 'VMQT '
WHEN A.TABLE_TYPE = 'R' THEN 'RC#CTE'
WHEN A.TABLE_TYPE = 'T' THEN 'TABLE/'
WHEN A.TABLE_TYPE = 'W' THEN 'WRKFIL'
ELSE A.TABLE_TYPE END AS TTYP,
A.TSLOCKMODE AS LCK,
CASE A.PARALLELISM_MODE
WHEN 'C' THEN 'CPU '
WHEN 'I' THEN 'I-O '
WHEN 'X' THEN 'PLEX'
ELSE ' ' END AS PARAL,
STRIP(DIGITS(A.ACCESS_DEGREE), LEADING, '0') CONCAT ' '
CONCAT STRIP(DIGITS(A.JOIN_DEGREE), LEADING, '0')
AS AJ_DEG,
STRIP(DIGITS(A.ACCESS_PGROUP_ID), LEADING, '0') CONCAT ' '
CONCAT STRIP(DIGITS(A.JOIN_PGROUP_ID), LEADING, '0')
AS PG_DEG,
STRIP(DIGITS(A.MERGE_JOIN_COLS), LEADING, '0') AS MJC,
CASE A.PREFETCH
WHEN 'S' THEN 'SEQ '
WHEN 'L' THEN 'LIST'
WHEN 'D' THEN 'DYN '
ELSE ' ' END AS PREFETCH,
STRIP(DIGITS(A."QUERYNO"), LEADING, '0') AS QNO,
SUBSTR(DIGITS(A.QBLOCKNO), 4, 2) CONCAT ' '
CONCAT SUBSTR(DIGITS(A.PLANNO), 4, 2)
AS BL_PL,
A.PARENT_QBLOCKNO,
A.QBLOCKNO, A.PLANNO, A.TNAME, A.ACCESSNAME, A.OPTHINT,
A.HINT_USED, A.APPLNAME, A."COLLID", A.VERSION,
A.TIMESTAMP, A.BIND_TIME, A."QUERYNO", A.MIXOPSEQ, A.TABNO,
A.CORRELATION_NAME, A.COLUMN_FN_EVAL, A.SORTC_PGROUP_ID,
A.SORTN_PGROUP_ID, A.PAGE_RANGE, A.WHEN_OPTIMIZE,
A.TABLE_ENCODE, A.TABLE_SCCSID, A.ROUTINE_ID, A.CTEREF,
A.STMTTOKEN,
-- S.COST_CATEGORY, S.PROCMS, S.PROCSU, S.REASON
'A' AS COST_CATEGORY, 1 AS PROCMS, 1 AS PROCSU, ' ' AS REASON
FROM $cr.PLAN_TABLE A
-- JOIN
-- (SELECT B.PROGNAME AS BPROGNAME,
-- B.COLLID AS BCOLLID,
-- MAX(B.BIND_TIME) BBIND_TIME
-- FROM $cr.PLAN_TABLE B
-- GROUP BY B.PROGNAME, B.COLLID) AS N1
-- ON A.PROGNAME = N1.BPROGNAME
-- AND A.BIND_TIME = N1.BBIND_TIME
-- AND A.COLLID = N1.BCOLLID
-- LEFT OUTER JOIN $cr.DSN_STATEMNT_TABLE S
-- ON S."COLLID" = A."COLLID"
-- AND S.APPLNAME = A.APPLNAME
-- AND S.PROGNAME = A.PROGNAME
-- AND S."QUERYNO" = A."QUERYNO"
-- AND S.EXPLAIN_TIME = A.BIND_TIME
;
------------------------------------------------------------------------
CREATE VIEW $cr.PLAN_VIEW7 AS
SELECT (
SUBSTR(DIGITS(A."QUERYNO"),5) || ' ' || -- STMT
QBLOCK_TYPE || ' ' || -- TYPE
CASE WHEN METHOD = 3 THEN ' '
ELSE CHAR(S.PROCMS) END || -- MSEC
CASE WHEN PRIMARY_ACCESSTYPE = 'D' THEN 'DIR.ROW'
WHEN ACCESSTYPE = 'I ' THEN 'IX-SCAN'
WHEN ACCESSTYPE = 'I1' THEN 'IX-ONE '
WHEN ACCESSTYPE = 'R ' THEN 'TS-SCAN'
WHEN ACCESSTYPE = 'N ' THEN 'IX-ACC '
WHEN ACCESSTYPE = 'M ' THEN 'IX-SC.M'
WHEN ACCESSTYPE = 'MX' THEN 'IX-SC.X'
WHEN ACCESSTYPE = 'MI' THEN 'IX-SC.I'
WHEN ACCESSTYPE = 'MU' THEN 'IX-SC.U'
WHEN ACCESSTYPE = 'T' THEN 'IX-SPRS'
WHEN ACCESSTYPE = ' ' THEN ' '
ELSE NULL END || ' ' || -- ACCESS
CASE WHEN ACCESSTYPE = 'R' THEN ' '
WHEN PRIMARY_ACCESSTYPE = 'D' THEN ' '
ELSE SUBSTR(ACCESSNAME,1,12) END || ' ' ||
-- "INDEX"
SUBSTR(TNAME,1,18) || ' ' || -- TABLE
CASE WHEN TABLE_TYPE IS NULL THEN ' '
ELSE TABLE_TYPE END || ' ' || -- TTYP
CASE WHEN METHOD = 3 THEN ' '
WHEN ACCESSTYPE = 'R ' THEN ' '
ELSE SUBSTR(DIGITS(MATCHCOLS), 4, 2)
END || ' ' || -- MC_O
CASE WHEN INDEXONLY='Y' THEN 'Y '
ELSE ' '
END || ' ' || -- IXO
CASE WHEN OPTHINT<>' ' THEN '*'
ELSE ' '
END || -- HINT
CASE WHEN HINT_USED<>' ' THEN '*'
ELSE ' '
END -- HINT_USED
) AS TEXT,
CASE METHOD
WHEN 0 THEN ' '
WHEN 1 THEN 'NLJOIN'
WHEN 2 THEN 'SMJOIN'
WHEN 3 THEN 'SORT '
WHEN 4 THEN 'HYJOIN'
ELSE NULL
END AS METHOD,
CASE JOIN_TYPE WHEN 'F' THEN 'FULL ' WHEN 'L' THEN 'LEFT '
WHEN 'S' THEN 'STAR ' ELSE ' ' END AS "JOIN",
SORTN_UNIQ CONCAT SORTN_JOIN CONCAT SORTN_ORDERBY CONCAT
SORTN_GROUPBY AS UJOG,
SORTC_UNIQ CONCAT SORTC_JOIN CONCAT SORTC_ORDERBY CONCAT
SORTC_GROUPBY AS UJOC, TSLOCKMODE AS LCK,
SUBSTR(CORRELATION_NAME, 1, 4) AS CORR,
CASE PARALLELISM_MODE
WHEN 'C' THEN 'CPU'
WHEN 'I' THEN 'I-O'
WHEN 'X' THEN 'SYSPLEX'
ELSE NULL
END AS PARAL,
STRIP(DIGITS(ACCESS_DEGREE), LEADING, '0') CONCAT ' '
CONCAT STRIP(DIGITS(JOIN_DEGREE), LEADING, '0') AS AJ_DEG,
STRIP(DIGITS(ACCESS_PGROUP_ID), LEADING, '0') CONCAT ' '
CONCAT STRIP(DIGITS(JOIN_PGROUP_ID), LEADING, '0') AS
PG_DEG,
STRIP(DIGITS(MERGE_JOIN_COLS), LEADING, '0') AS MC,
PREFETCH AS PRE,
STRIP(DIGITS(A."QUERYNO"), LEADING, '0') AS QNO,
SUBSTR(DIGITS(QBLOCKNO), 4, 2) CONCAT ' ' CONCAT SUBSTR(
DIGITS(PLANNO), 4, 2) AS BL_PL,
A.PARENT_QBLOCKNO,
A.QBLOCKNO,
A.PLANNO,
A.TNAME,
A.ACCESSNAME,
A.OPTHINT,
A.HINT_USED,
A.APPLNAME,
A."COLLID",
A.PROGNAME,
A.VERSION,
A.BIND_TIME,
A.TIMESTAMP,
A."QUERYNO",
A.MIXOPSEQ,
S.COST_CATEGORY,
S.PROCMS,
S.PROCSU,
S.REASON
FROM $cr.PLAN_TABLE A
JOIN $cr.DSN_STATEMNT_TABLE S
ON S."COLLID" = A."COLLID"
AND S.APPLNAME = A.APPLNAME
AND S.PROGNAME = A.PROGNAME
AND S."QUERYNO" = A."QUERYNO"
AND S.EXPLAIN_TIME = A.BIND_TIME
;
------------------------------------------------------------------------
CREATE VIEW $cr.PLAN_VIEW9 AS
SELECT A.PROGNAME, A.VERSION, S.PROCMS
FROM $cr.PLAN_TABLE A
LEFT OUTER JOIN $cr.DSN_STATEMNT_TABLE S
ON S."COLLID" = A."COLLID"
AND S.APPLNAME = A.APPLNAME
AND S.PROGNAME = A.PROGNAME
AND S.QUERYNO = A.QUERYNO
AND S.EXPLAIN_TIME = A.BIND_TIME
;
------------------------------------------------------------------------
commit
;
$]
if $doReo then $@=[
// IF DDL.RC LE 4 THEN
//REO EXEC PGM=DSNUTILB,TIME=1440,
// PARM=($subsys,'$job.REORG'),
// REGION=0M
//DSSPRINT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSTEMPL DD DISP=SHR,DSN=$subsys.DBAA.LISTDEF(TEMPL)
//SYSIN DD *
LISTDEF LSTBASE INCLUDE TABLESPACE $db.$ts* BASE
REORG TABLESPACE LIST LSTBASE
LOG NO
SORTDATA
COPYDDN(TCOPYD)
SHRLEVEL CHANGE
MAPPINGTABLE S100447.MAPTAB03
MAXRO 300
DRAIN ALL
DELAY 1500
TIMEOUT TERM
UNLDDN TSRECD
UNLOAD CONTINUE
PUNCHDDN TPUNCH
DISCARDDN TDISCA
SORTKEYS
SORTDEVT DISK
STATISTICS
INDEX ALL KEYCARD
REPORT NO UPDATE ALL
LISTDEF LSTLOB INCLUDE TABLESPACE $db.$ts* LOB
REORG TABLESPACE LIST LSTLOB
SHRLEVEL REFERENCE
COPYDDN(TCOPYD)
UNLOAD CONTINUE
// ENDIF
$]
$]
$#out 20120319 13:48:18
$#out 20120319 13:41:52
$#out 20120319 13:40:39
$#out 20120319 13:39:12
$#out 20120319 13:34:25
$#out 20120319 13:33:03
$#out 20120319 13:29:20
$#out 20120210 17:38:13
$#out 20120210 16:59:00
$#out 20120210 16:24:47
$#out 20120210 16:24:24