zOs/WK/PLANTB
$#@ $*(******** create plan tables and views db2v10 ********** 5. 2.14
1) Parameter eingeben
2) wsh
3) job kontrollieren und sub
6.11.14 neue Version F2Plan_table für FileAid mit fun=u
6. 6.14 achtung bind_time --> explain_time
braucht update und drop/create auf plan_table|
v10 plan_table 64 Kolonnen und Unicode
v9 plan_table 59 Kolonnen
v8 plan_table 58 Kolonnen
*********************** v7 plan_table 51 Kolonnen ******************$*)
$=subsys=DBOL $** db2 subsystem
$=cr=S103931 $** creator
$=fun = u $** funktion
$*( c= create plan tables and views
u=update tables for v10.1 and recreate views
must already be UniCode ||||
r=drop Tablespaces and recreate everything
d=drop Tablespaces only
v=drop/recreate views only $*)
$=job=Y4PLANTB $** jobName
$=ts=- strip(left($cr, 7))
$=db=DB2PLAN
$=queryT=0 $** 1: mit userQuery tb, 0: ohne
$=queryV=0 $** 1: mit VirtualIndexes und dynStmtCache, 0: ohne
if $subsys == 'DBAF' then
$=path= OA1A
else if $subsys == 'DBTF' then
$=path= OA1T
else
$=path= OA1P
$=cmnViews =- $cr = 'CMNBATCH'
$=doReo = 0
$=suf=- '' $** Parameter für Spezialfälle
$=tsH= ${ts}H
$=tsL= ${ts}L
$=tsX= ${ts}X
if 0 then $@[ $** Spezial Namen für Daten Migration
$=suf= _New
$=tsH= ${ts}Q
$**$=tsL= ${ts}O
$]
$*( *** history *******************************************************
29. 3.12 neu fun=d: drop Only, r= drop and recreate
21. 3.12 drop only necessary TS and Views ==> JobCondCode <=4
21. 3.12 create v10 views or migrate to v10
3. 2.12 allow v10 views (but do not change v9 to v10 yet|)
****************************************************************** $*)
$=isCre=- pos($fun, 'cr') > 0 $** create everything
$=isUpd=- pos($fun, 'crud') > 0 $** update allowed
$=defer=- if($isCre, NO, YES)
if pos($fun, 'druv') > 0 then
call sqlConnect $subsys
$;
$>. fEdit() $@/fEdit/
$@=[
//$job JOB (CP00,KE50),
// MSGCLASS=T,TIME=1440,SCHENV=DB2,
// 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 == 'r' then $@=[
//* drop recreate plan tables and views
//* all data lost ||||||||||||||||||||||||||||||||||||
$] else if $fun == 'd' then $@=[
//* drop tablespaces, tables and views
//* all data lost ||||||||||||||||||||||||||||||||||||
$] else if $fun == 'u' then $@=[
//* update plantables for v10.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
$@%[tb quIn DSN_QUERYINFO_TABLE$suf $]
$@%[tb stCa DSN_STATEMENT_CACHE_TABLE$suf $]
$@%[tb usQu DSN_USERQUERY_TABLE$suf $]
$@%[tb viIx DSN_VIRTUAL_INDEXES$suf $]
if $fun == 'd' | $fun == 'r' then $@[
$@dropTS
$] else $@[
if $fun == 'u' & \ $queryV then $@[
if $stCa \== '' then $@=[
drop table $cr.DSN_STATEMENT_CACHE_TABLE$suf;
drop tablespace ${db}.$tsL;
$]
if $viIx \== '' then $@=[
drop table $cr.DSN_VIRTUAL_INDEXES$suf;
$]
$]
if $fun == 'u' & \ $queryT & $usQu \== '' then $@=[
drop table $cr.DSN_USERQUERY_TABLE$suf;
drop tableSpace ${db}.${ts}V;
$]
if $fun \== 'c' then
$@dropViews
$]
$$ --#SET MAXERRORS 0
if $fun \== 'd' then $@/createOrUpdate/
if $isCre then $@=[
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V10 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;
$@[
if $queryT then $@=[
-----------------------------------------------------------------------
--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 V10 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;
$@[
if $queryT then $@=[
-- ts fuer *query* tables
CREATE LOB TABLESPACE ${ts}V
IN $db
BUFFERPOOL BP32K;
$]
$]
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 BP2
CCSID UNICODE
USING STOGROUP GSMS PRIQTY -1 SECQTY -1 ERASE NO
COMPRESS YES CLOSE YES;
$]
if $isCre | ($fun == 'u' & $quIn == '') then $@=[
CREATE LOB TABLESPACE ${ts}Q
IN $db
BUFFERPOOL BP32K;
CREATE LOB TABLESPACE ${ts}R
IN $db
BUFFERPOOL BP32K;
$]
$@%[tb plan PLAN_TABLE$]
if $plan == '64 MERGN' then $@=[ $** already v10 ok
$] else if \ $isUpd then $@[
call err 'fun' $fun 'cannot update plan_table from:' $plan
$] else if $plan == '59 PARENT_PLANNO' then $@=[ $** v9 update
$=doReo=1
ALTER TABLE $cr.PLAN_TABLE
ADD "BIND_EXPLAIN_ONLY" CHAR(1) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.PLAN_TABLE
ADD "SECTNOI" INTEGER NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.PLAN_TABLE
ADD "EXPLAIN_TIME" TIMESTAMP NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.PLAN_TABLE
ADD "MERGC" CHAR(1) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.PLAN_TABLE
ADD "MERGN" CHAR(1) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_FUNCTION_TABLE
ADD "FUNC_VERSION" VARCHAR(122) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_FUNCTION_TABLE
ADD "SECURE" CHAR(1) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_FUNCTION_TABLE
ADD "SECTNOI" INTEGER NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_FUNCTION_TABLE
ADD "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_STATEMNT_TABLE
ADD "SECTNOI" INTEGER NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_STATEMNT_TABLE
ADD "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_STRUCT_TABLE
ADD "ORIGIN" CHAR(1) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STRUCT_TABLE
ADD "SECTNOI" INTEGER NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STRUCT_TABLE
ADD "COLLID" VARCHAR(128) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STRUCT_TABLE
ADD "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_PGROUP_TABLE
ALTER "LOWBOUND" SET DATA TYPE VARCHAR(40) FOR BIT DATA ;
ALTER TABLE $cr.DSN_PGROUP_TABLE
ALTER "HIGHBOUND" SET DATA TYPE VARCHAR(40) FOR BIT DATA ;
ALTER TABLE $cr.DSN_PGROUP_TABLE
ALTER "LOWKEY" SET DATA TYPE VARCHAR(40) FOR BIT DATA ;
ALTER TABLE $cr.DSN_PGROUP_TABLE
ALTER "HIGHKEY" SET DATA TYPE VARCHAR(40) FOR BIT DATA ;
ALTER TABLE $cr.DSN_PGROUP_TABLE
ALTER "FIRSTPAGE" SET DATA TYPE CHAR(4) FOR BIT DATA ;
ALTER TABLE $cr.DSN_PGROUP_TABLE
ALTER "LASTPAGE" SET DATA TYPE CHAR(4) FOR BIT DATA ;
ALTER TABLE $cr.DSN_PGROUP_TABLE
ADD "APPLNAME" VARCHAR(24) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_PGROUP_TABLE
ADD "SECTNOI" INTEGER NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_PGROUP_TABLE
ADD "STRAW_MODEL" CHAR(1) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_PTASK_TABLE
ALTER "LPTLOKEY" SET DATA TYPE VARCHAR(40) FOR BIT DATA ;
ALTER TABLE $cr.DSN_PTASK_TABLE
ALTER "LPTHIKEY" SET DATA TYPE VARCHAR(40) FOR BIT DATA ;
ALTER TABLE $cr.DSN_PTASK_TABLE
ALTER "LPTLOPAG" SET DATA TYPE CHAR(4) FOR BIT DATA ;
ALTER TABLE $cr.DSN_PTASK_TABLE
ALTER "LPTHIPAG" SET DATA TYPE CHAR(4) FOR BIT DATA ;
ALTER TABLE $cr.DSN_PTASK_TABLE
ALTER "LPTLOPG" SET DATA TYPE CHAR(4) FOR BIT DATA ;
ALTER TABLE $cr.DSN_PTASK_TABLE
ALTER "LPTHIPG" SET DATA TYPE CHAR(4) FOR BIT DATA ;
ALTER TABLE $cr.DSN_PTASK_TABLE
ADD "SECTNOI" INTEGER NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_PTASK_TABLE
ADD "COLLID" VARCHAR(128) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_PTASK_TABLE
ADD "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ALTER "TABREF" SET DATA TYPE VARCHAR(64) FOR BIT DATA;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "UNCERTAINTY" FLOAT(4) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "UNCERTAINTY_1T" FLOAT(4) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "SECTNOI" INTEGER NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "COLLID" VARCHAR(128) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "IMNP" FLOAT(4) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "DMNP" FLOAT(4) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "IMJC" FLOAT(4) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "IMFC" FLOAT(4) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "IMJBC" FLOAT(4) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "IMJFC" FLOAT(4) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "CRED" INTEGER NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_SORT_TABLE
ADD "SECTNOI" INTEGER NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_SORT_TABLE
ADD "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_SORTKEY_TABLE
ADD "SECTNOI" INTEGER NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_SORTKEY_TABLE
ADD "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_PGRANGE_TABLE
ADD "SECTNOI" INTEGER NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_PGRANGE_TABLE
ADD "APPLNAME" VARCHAR(24) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_PGRANGE_TABLE
ADD "PROGNAME" VARCHAR(128) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_PGRANGE_TABLE
ADD "COLLID" VARCHAR(128) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_PGRANGE_TABLE
ADD "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_QUERY_TABLE
ADD "SECTNOI" INTEGER NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_QUERY_TABLE
ADD "APPLNAME" VARCHAR(24) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_QUERY_TABLE
ADD "PROGNAME" VARCHAR(128) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_QUERY_TABLE
ADD "COLLID" VARCHAR(128) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_QUERY_TABLE
ADD "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT ;
$] else if $plan == '' then $@=[
-----------------------------------------------------------------------
--CREATE THE V10 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,
"BIND_EXPLAIN_ONLY" CHAR(1) NOT NULL WITH DEFAULT,
"SECTNOI" INTEGER NOT NULL WITH DEFAULT,
"EXPLAIN_TIME" TIMESTAMP NOT NULL WITH DEFAULT,
"MERGC" CHAR(1) NOT NULL WITH DEFAULT,
"MERGN" CHAR(1) NOT NULL WITH DEFAULT
)
IN ${db}.${ts}X
VOLATILE
DATA CAPTURE CHANGES
CCSID UNICODE;
$] else call err 'cannot update plan_Table'$suf 'from' $plan
if $isCre then $@=/creTbFun/
-----------------------------------------------------------------------
--CREATE THE V10 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,
"FUNC_VERSION" VARCHAR(122) NOT NULL WITH DEFAULT,
"SECURE" CHAR(1) NOT NULL WITH DEFAULT,
"SECTNOI" INTEGER NOT NULL WITH DEFAULT,
"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
)
IN ${db}.${ts}X
VOLATILE
DATA CAPTURE CHANGES
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE V10 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,
"SECTNOI" INTEGER NOT NULL WITH DEFAULT,
"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
)
IN ${db}.$tsX
VOLATILE
DATA CAPTURE CHANGES
CCSID UNICODE;
$/creTbFun/
if $queryV then $@/stmtCache/
if $stCa == '67 STAT_SUS_LOG' then $@=[ $** already v10 ok
$] else if \ $isUpd then $@[
call err 'fun' $fun 'cannot update DSN_STATEMENT_CACHE_TA'$suf
$] else if $stCa == '49 GROUP_MEMBER' $*+
| $stCa == '48 BIND_RA_TOT' then $@=[ $** v8 oder v9
$=doReo=1
$@[
if $stCa == '48 BIND_RA_TOT' then $@=[ $** v8
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DEFAULT;
$] $]
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_EXECB" BIGINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_GPAGB" BIGINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_SYNRB" BIGINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_WRITB" BIGINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_EROWB" BIGINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_PROWB" BIGINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_SORTB" BIGINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_INDXB" BIGINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_RSCNB" BIGINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_PGRPB" BIGINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_RIDLIMTB" BIGINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_RIDSTORB" BIGINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "LITERAL_REPL" CHAR(1) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_SUS_LATCH" FLOAT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_SUS_PLATCH" FLOAT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_SUS_DRAIN" FLOAT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_SUS_CLAIM" FLOAT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_SUS_LOG" FLOAT NOT NULL WITH DEFAULT;
$] else if $stCa == '' then $@=[
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_STATEMENT_CACHE_TABLE
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,
"GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DEFAULT,
"STAT_EXECB" BIGINT NOT NULL WITH DEFAULT,
"STAT_GPAGB" BIGINT NOT NULL WITH DEFAULT,
"STAT_SYNRB" BIGINT NOT NULL WITH DEFAULT,
"STAT_WRITB" BIGINT NOT NULL WITH DEFAULT,
"STAT_EROWB" BIGINT NOT NULL WITH DEFAULT,
"STAT_PROWB" BIGINT NOT NULL WITH DEFAULT,
"STAT_SORTB" BIGINT NOT NULL WITH DEFAULT,
"STAT_INDXB" BIGINT NOT NULL WITH DEFAULT,
"STAT_RSCNB" BIGINT NOT NULL WITH DEFAULT,
"STAT_PGRPB" BIGINT NOT NULL WITH DEFAULT,
"STAT_RIDLIMTB" BIGINT NOT NULL WITH DEFAULT,
"STAT_RIDSTORB" BIGINT NOT NULL WITH DEFAULT,
"LITERAL_REPL" CHAR(1) NOT NULL WITH DEFAULT,
"STAT_SUS_LATCH" FLOAT NOT NULL WITH DEFAULT,
"STAT_SUS_PLATCH" FLOAT NOT NULL WITH DEFAULT,
"STAT_SUS_DRAIN" FLOAT NOT NULL WITH DEFAULT,
"STAT_SUS_CLAIM" FLOAT NOT NULL WITH DEFAULT,
"STAT_SUS_LOG" FLOAT NOT NULL WITH DEFAULT
)
IN ${db}.$tsX
VOLATILE
DATA CAPTURE CHANGES
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE AUXILIARY TABLE FOR THE SAMPLE DSN_STATEMENT_CACHE_TABLE
CREATE AUX TABLE $cr.DSN_STATEMENT_CACHE_AUX$suf
IN ${db}.$tsL
STORES $cr.DSN_STATEMENT_CACHE_TABLE$suf
COLUMN STMT_TEXT;
$] else call err 'cannot update DSN_STATEMENT_CACHE_TA'$suf $*+
'from' $stCa
$/stmtCache/
$@%[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 AN INDEX ON THE V10 SAMPLE PLAN_TABLE
--IT IS RECOMMENDED TO CREATE THIS INDEX IF
--USING USER-LEVEL AND/OR INSTANCE-LEVEL
--OPTIMIZATION HINTS
CREATE INDEX $cr.PLAN_TABLE_HINT_IX$suf
ON $cr.PLAN_TABLE$suf
( "QUERYNO",
"APPLNAME",
"PROGNAME",
"VERSION",
"COLLID",
"OPTHINT"
) -- no other fields allowed ||||
$@ixAtt
$]
$@%[ix keys drop PLAN_TABLE_idx1$suf$]
if $keys == 'QUERYNO<EXPLAIN_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"
,explain_time
)
$@%[ixAtt c$]
$]
$@%[ix keys drop PLAN_TABLE_PROG_IX$suf$]
if $keys == 'PROGNAME<COLLID<VERSION<EXPLAIN_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,
explain_time ASC,
QUERYNO ASC)
$@ixAtt
$]
if $isCre then $@=[
CREATE INDEX $cr.FUNC_EXPidx1$suf
ON $cr.DSN_FUNCTION_Table$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt
CREATE INDEX $cr.DSN_STATEMNT_TABLE_idx1$suf
ON $cr.DSN_STATEMNT_Table$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt
$]
if $isUpd & $queryV & $stCa == '' then $@=/creIxStCa/
-----------------------------------------------------------------------
--CREATE INDEXES ON THE V10 SAMPLE DSN_STATEMENT_CACHE_TABLE
CREATE INDEX $cr.DSN_STATEMENT_CACHE_IDX1$suf
ON $cr.DSN_STATEMENT_CACHE_TABLE$suf
( "STMT_ID" ASC )
$@ixAtt
CREATE INDEX $cr.DSN_STATEMENT_CACHE_IDX2$suf
ON $cr.DSN_STATEMENT_CACHE_TABLE$suf
( "STMT_TOKEN" ASC )
$@%[ixAtt c $]
CREATE INDEX $cr.DSN_STATEMENT_CACHE_IDX3$suf
ON $cr.DSN_STATEMENT_CACHE_TABLE$suf
( "EXPLAIN_TS" DESC )
$@ixAtt
CREATE INDEX $cr.DSN_STATEMENT_CACHE_AUXINX$suf
ON $cr.DSN_STATEMENT_CACHE_AUX$suf
$@%[ixAtt l $]
$/creIxStCa/
$@%[tb pred DSN_PREDICAT_TABLE$]
if $pred == '41 VERSION' then $@=[ $** already v10 ok
$] else if \ $isUpd then $@[
call err 'fun' $fun 'cannot update DSN_PREDICAT_Table'$suf $pred
$] else if $pred == '36 GROUP_MEMBER' then $@=[ $** V9
$=doReo=1
ALTER TABLE $cr.DSN_PREDICAT_Table$suf
ADD "ORIGIN" CHAR(1) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_PREDICAT_Table$suf
ADD "UNCERTAINTY" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_PREDICAT_Table$suf
ADD "SECTNOI" INTEGER NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_PREDICAT_Table$suf
ADD "COLLID" VARCHAR(128) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_PREDICAT_Table$suf
ADD "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT;
$] else if $pred == '' then $@=/creTbPred/
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_PREDICAT_TABLE 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
,"ORIGIN" CHAR(1) NOT NULL WITH DEFAULT
,"UNCERTAINTY" FLOAT(4) NOT NULL WITH DEFAULT
,"SECTNOI" INTEGER NOT NULL WITH DEFAULT
,"COLLID" VARCHAR(128) NOT NULL WITH DEFAULT
,"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
)
IN ${db}.$tsX
VOLATILE
DATA CAPTURE CHANGES
CCSID UNICODE;
CREATE INDEX $cr.DSN_PREDICAT_TABLE_IDX1$suf
ON $cr.DSN_PREDICAT_TABLE$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt
$/creTbPred/
else call err 'cannot update DSN_PREDICAT_Table'$suf 'from' $pred
if $isCre then $@=/creTbStru/
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_STRUCT_TABLE 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
,"ORIGIN" CHAR(1) NOT NULL WITH DEFAULT
,"SECTNOI" INTEGER NOT NULL WITH DEFAULT
,"COLLID" VARCHAR(128) NOT NULL WITH DEFAULT
,"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
)
IN ${db}.$tsX
VOLATILE
DATA CAPTURE CHANGES
CCSID UNICODE;
CREATE INDEX $cr.DSN_STRUCT_TABLE_IDX1$suf
ON $cr.DSN_STRUCT_TABLE$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_PGROUP_TABLE 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) FOR BIT DATA
,"HIGHBOUND" VARCHAR(40) FOR BIT DATA
,"LOWKEY" VARCHAR(40) FOR BIT DATA
,"HIGHKEY" VARCHAR(40) FOR BIT DATA
,"FIRSTPAGE" CHAR(4) FOR BIT DATA
,"LASTPAGE" CHAR(4) FOR BIT DATA
,"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)
,"APPLNAME" VARCHAR(24) NOT NULL WITH DEFAULT
,"SECTNOI" INTEGER NOT NULL WITH DEFAULT
,"STRAW_MODEL" CHAR(1) NOT NULL WITH DEFAULT
)
IN ${db}.${ts}G
VOLATILE
DATA CAPTURE CHANGES
CCSID UNICODE;
CREATE INDEX $cr.DSN_PGROUP_TABLE_IDX1$suf
ON $cr.DSN_PGROUP_TABLE$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_PTASK_TABLE 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) FOR BIT DATA
,"LPTHIKEY" VARCHAR(40) FOR BIT DATA
,"LPTLOPAG" CHAR(4) FOR BIT DATA
,"LPTHIPAG" CHAR(4) FOR BIT DATA
,"LPTLOPG" CHAR(4) FOR BIT DATA
,"LPTHIPG" CHAR(4) FOR BIT DATA
,"LPTLOPT" SMALLINT
,"LPTHIPT" SMALLINT
,"KEYCOLDT" SMALLINT
,"KEYCOLPREC" SMALLINT
,"KEYCOLSCAL" SMALLINT
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"GROUP_MEMBER" VARCHAR(24) NOT NULL
,"SECTNOI" INTEGER NOT NULL WITH DEFAULT
,"COLLID" VARCHAR(128) NOT NULL WITH DEFAULT
,"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
)
IN ${db}.$tsH
VOLATILE
DATA CAPTURE CHANGES
CCSID UNICODE;
CREATE INDEX $cr.DSN_PTASK_TABLE_IDX1$suf
ON $cr.DSN_PTASK_TABLE$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt
$/creTbStru/
$@%[tb filt DSN_FILTER_TABLE$]
if $filt == '17 PUSHDOWN' then $@=[ $** already v10 ok
$] else if \ $isUpd then $@[
call err 'fun' $fun 'cannot update DSN_Filter_Table from' $filt
$] else if $filt == '14 GROUP_MEMBER' then $@=[
$=doReo=1
ALTER TABLE $cr.DSN_FILTER_TABLE
add "SECTNOI" INTEGER NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_FILTER_TABLE
add "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_FILTER_TABLE
add "PUSHDOWN" CHAR(1) NOT NULL WITH DEFAULT;
$] else if $filt == '' then $@=/creTbFilt/
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_FILTER_TABLE 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
,"SECTNOI" INTEGER NOT NULL WITH DEFAULT
,"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
,"PUSHDOWN" CHAR(1) NOT NULL WITH DEFAULT
)
IN ${db}.$tsX
VOLATILE
DATA CAPTURE CHANGES
CCSID UNICODE;
CREATE INDEX $cr.DSN_FILTER_TABLE_IDX1$suf
ON $cr.DSN_FILTER_TABLE$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt
$/creTbFilt/
else call err 'cannot update DSN_FilterAT_Table'$suf 'from' $filt
if $isCre then $@=/creTbDetCo/
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_DETCOST_TABLE 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 FOR BIT DATA
,"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
,"UNCERTAINTY" FLOAT(4) NOT NULL WITH DEFAULT
,"UNCERTAINTY_1T" FLOAT(4) NOT NULL WITH DEFAULT
,"SECTNOI" INTEGER NOT NULL WITH DEFAULT
,"COLLID" VARCHAR(128) NOT NULL WITH DEFAULT
,"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
,"IMNP" FLOAT(4) NOT NULL WITH DEFAULT
,"DMNP" FLOAT(4) NOT NULL WITH DEFAULT
,"IMJC" FLOAT(4) NOT NULL WITH DEFAULT
,"IMFC" FLOAT(4) NOT NULL WITH DEFAULT
,"IMJBC" FLOAT(4) NOT NULL WITH DEFAULT
,"IMJFC" FLOAT(4) NOT NULL WITH DEFAULT
,"CRED" INTEGER NOT NULL WITH DEFAULT
)
IN ${db}.$tsX
VOLATILE
DATA CAPTURE CHANGES
CCSID UNICODE;
CREATE INDEX $cr.DSN_DETCOST_TABLE_IDX1$suf
ON $cr.DSN_DETCOST_TABLE$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_SORT_TABLE 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
,"SECTNOI" INTEGER NOT NULL WITH DEFAULT
,"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
)
IN ${db}.$tsX
VOLATILE
DATA CAPTURE CHANGES
CCSID UNICODE;
CREATE INDEX $cr.DSN_SORT_TABLE_IDX1$suf
ON $cr.DSN_SORT_TABLE$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_SORTKEY_TABLE 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
,"SECTNOI" INTEGER NOT NULL WITH DEFAULT
,"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
)
IN ${db}.$tsX
VOLATILE
DATA CAPTURE CHANGES
CCSID UNICODE;
CREATE INDEX $cr.DSN_SORTKEY_TABLE_IDX1$suf
ON $cr.DSN_SORTKEY_TABLE$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_PGRANGE_TABLE 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
,"SECTNOI" 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
,"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
)
IN ${db}.$tsX
VOLATILE
DATA CAPTURE CHANGES
CCSID UNICODE;
CREATE INDEX $cr.DSN_PGRANGE_TABLE_IDX1$suf
ON $cr.DSN_PGRANGE_TABLE$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt
$/creTbDetCo/
$@%[tb viRe DSN_VIEWREF_TABLE$]
if $viRe == '12 SECTNOI' then $@=[ $** already v10 ok
$] else if \ $isUpd then $@[
call err 'fun' $fun 'cannot update DSN_VIEWREF_TABLE' $viRe
$] else if $viRe == '11 GROUP_MEMBER' then $@=[
$=doReo=1
alter TABLE $cr.DSN_VIEWREF_TABLE$suf
add "SECTNOI" INTEGER NOT NULL WITH DEFAULT;
$] else if $viRe == '' then $@=/creTbViRe/
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_VIEWREF_TABLE 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
,"SECTNOI" INTEGER NOT NULL WITH DEFAULT
)
IN ${db}.$tsX
VOLATILE
DATA CAPTURE CHANGES
CCSID UNICODE;
CREATE INDEX $cr.DSN_VIEWREF_TABLE_IDX1$suf
ON $cr.DSN_VIEWREF_TABLE$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt
$/creTbViRe/ else call err 'cannot update DSN_VIEWREF_TABLE from' $viRe
if $isCre then $@=/creTbQu/
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_QUERY_TABLE 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
,"SECTNOI" 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
,"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
)
IN ${db}.${ts}I
VOLATILE
DATA CAPTURE CHANGES
CCSID UNICODE;
CREATE INDEX $cr.DSN_QUERY_TABLE_IDX1$suf
ON $cr.DSN_QUERY_TABLE$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt
CREATE INDEX $cr.DSN_QUERY_TABLE_IDX2$suf
ON $cr.DSN_QUERY_TABLE$suf
( "QUERYNO"
,"TYPE"
,"QUERY_STAGE"
,"EXPLAIN_TIME"
,"SEQNO"
)
$@ixAtt
CREATE UNIQUE INDEX $cr.DSN_QUERY_TABLE_IDX3$suf
ON $cr.DSN_QUERY_TABLE$suf
( "QUERY_ROWID"
)
$@ixAtt
-----------------------------------------------------------------------
--CREATE THE AUXILIARY TABLE FOR THE V10 SAMPLE DSN_QUERY_TABLE
--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
$@%[ixAtt l $]
$/creTbQu/
if $isCre & $queryT then $@=/creTbUsQu/
-- DESCRIPTIVE NAME = CREATE THE DB2 V10 SAMPLE DSN_USERQUERY_TABLE
CREATE table $cr.DSN_USERQUERY_TABLE$suf
( "QUERYNO" INTEGER NOT NULL PRIMARY KEY
, "SCHEMA" VARCHAR(128) NOT NULL DEFAULT ' '
, "HINT_SCOPE" SMALLINT NOT NULL DEFAULT 0
, "QUERY_TEXT" CLOB(2M) NOT NULL
, "QUERY_ROWID" ROWID NOT NULL GENERATED ALWAYS
, "QUERYID" BIGINT NOT NULL DEFAULT 0
, "USERFILTER" CHAR(8) NOT NULL DEFAULT ' '
, "OTHER_OPTIONS" CHAR(128) NOT NULL DEFAULT ' '
, "COLLECTION" VARCHAR(128) NOT NULL DEFAULT ' '
, "PACKAGE" VARCHAR(128) NOT NULL DEFAULT ' '
, "VERSION" VARCHAR(128) NOT NULL DEFAULT ' '
, "REOPT" CHAR(1) NOT NULL DEFAULT ' '
, "STARJOIN" CHAR(1) NOT NULL DEFAULT ' '
, "MAX_PAR_DEGREE" INTEGER NOT NULL DEFAULT -1
, "DEF_CURR_DEGREE" CHAR(3) NOT NULL DEFAULT ' '
, "SJTABLES" INTEGER NOT NULL DEFAULT -1
, "OTHER_PARMS" VARCHAR(128) NOT NULL DEFAULT ' '
)
IN ${db}.${ts}X
DATA CAPTURE CHANGES
CCSID UNICODE;
CREATE AUX TABLE $cr.DSN_USERQUERY_TABLE_AUX$suf
IN ${db}.${ts}V
STORES $cr.DSN_USERQUERY_TABLE$suf
COLUMN QUERY_TEXT;
CREATE UNIQUE INDEX $cr.DSN_USERQUERY_TABLE${suf}_IX1
ON $cr.DSN_USERQUERY_TABLE$suf
( "QUERYNO" ASC
);
CREATE INDEX $cr.DSN_USERQUERY_TABLE${suf}_AUX_IX
ON $cr.DSN_USERQUERY_TABLE${suf}_AUX;
$/creTbUsQu/
if $isCre | ($fun == 'u' & $quIn == '') then $@=/creTbQuIn/
--CREATE THE V10 SAMPLE DSN_QUERYINFO_TABLE AND ITS AUXILIARY TABLES
--AND INDEXES
CREATE TABLE $cr.DSN_QUERYINFO_TABLE$suf
( "QUERYNO" INTEGER NOT NULL WITH DEFAULT
, "QBLOCKNO" SMALLINT NOT NULL WITH DEFAULT
, "QINAME1" VARCHAR(128) NOT NULL WITH DEFAULT
, "QINAME2" VARCHAR(128) 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
, "GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DEFAULT
, "SECTNOI" INTEGER NOT NULL WITH DEFAULT
, "SEQNO" INTEGER NOT NULL WITH DEFAULT
, "EXPLAIN_TIME" TIMESTAMP NOT NULL WITH DEFAULT
, "TYPE" CHAR(8) NOT NULL WITH DEFAULT
, "REASON_CODE" SMALLINT NOT NULL WITH DEFAULT
, "QI_DATA" CLOB(2M) NOT NULL WITH DEFAULT
, "SERVICE_INFO" BLOB(2M) NOT NULL WITH DEFAULT
, "QB_INFO_ROWID" ROWID NOT NULL
GENERATED ALWAYS
)
IN ${db}.$tsX
DATA CAPTURE CHANGES
CCSID UNICODE;
CREATE AUX TABLE $cr.DSN_QUERYINFO_AUX$suf
IN $db.${ts}Q
STORES $cr.DSN_QUERYINFO_TABLE$suf
COLUMN "QI_DATA";
CREATE INDEX $cr.DSN_QUERYINFO_AUXINX$suf
ON $cr.DSN_QUERYINFO_AUX$suf;
CREATE AUX TABLE $cr.DSN_QUERYINFO_AUX2$suf
IN $db.${ts}R
STORES $cr.DSN_QUERYINFO_TABLE$suf
COLUMN "SERVICE_INFO";
CREATE INDEX $cr.DSN_QUERYINFO_AUXINX2$suf
ON $cr.DSN_QUERYINFO_AUX2$suf;
$/creTbQuIn/
if $isCre & $queryV then $@=/creTbViIx/
-----------------------------------------------------------------------
--CREATE THE V10 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
VOLATILE
DATA CAPTURE CHANGES
CCSID UNICODE;
CREATE INDEX $cr.DSN_VIRTUAL_INDEXES_IDX1$suf
ON $cr.DSN_VIRTUAL_INDEXES$suf
( "TBCREATOR"
,"TBNAME"
)
$@ixAtt
$/creTbViIx/
$@%[tb coDi DSN_COLDIST_TABLE$]
if $coDi == '20 HIGHVALUE' then $@=[ $** already v10 ok
$] else if \ $isUpd then $@[
call err 'fun' $fun 'cannot update DSN_COLDIST_TABLE' $coDi
$] else if $coDi == '' then $@=/creTbCoDi/
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_COLDIST_TABLE
CREATE TABLE $cr.DSN_COLDIST_TABLE$suf
( "QUERYNO" INTEGER NOT NULL
,"APPLNAME" VARCHAR(128) NOT NULL
,"PROGNAME" VARCHAR(128) NOT NULL
,"COLLID" VARCHAR(128) NOT NULL
,"GROUP_MEMBER" VARCHAR(128) NOT NULL
,"SECTNOI" INTEGER NOT NULL
,"VERSION" VARCHAR(122) NOT NULL
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"SCHEMA" VARCHAR(128) NOT NULL
,"TBNAME" VARCHAR(128) NOT NULL
,"NAME" VARCHAR(128) NOT NULL
,"COLVALUE" VARCHAR(2000) NOT NULL FOR BIT DATA
,"TYPE" CHAR(1) NOT NULL
,"CARDF" FLOAT NOT NULL
,"COLGROUPCOLNO" VARCHAR(254) NOT NULL FOR BIT DATA
,"NUMCOLUMNS" SMALLINT NOT NULL
,"FREQUENCYF" FLOAT NOT NULL
,"QUANTILENO" SMALLINT NOT NULL
,"LOWVALUE" VARCHAR(2000) NOT NULL FOR BIT DATA
,"HIGHVALUE" VARCHAR(2000) NOT NULL FOR BIT DATA
)
IN ${db}.$tsX
VOLATILE
DATA CAPTURE CHANGES
CCSID UNICODE;
$/creTbCoDi/ else call err 'cannot update DSN_COLDIST_TABLE from' $coDi
$@%[tb kyDi DSN_KEYTGTDIST_TABLE$]
if $kyDi == '20 HIGHVALUE' then $@=[ $** already v10 ok
$] else if \ $isUpd then $@[
call err 'fun' $fun 'cannot update DSN_KEYTGTDIST_TABLE' $kyDi
$] else if $kyDi == '' then $@=/creTbKey/
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_KEYTGTDIST_TABLE
CREATE TABLE $cr.DSN_KEYTGTDIST_TABLE$suf
( "QUERYNO" INTEGER NOT NULL
,"APPLNAME" VARCHAR(128) NOT NULL
,"PROGNAME" VARCHAR(128) NOT NULL
,"COLLID" VARCHAR(128) NOT NULL
,"GROUP_MEMBER" VARCHAR(128) NOT NULL
,"SECTNOI" INTEGER NOT NULL
,"VERSION" VARCHAR(122) NOT NULL
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"IXSCHEMA" VARCHAR(128) NOT NULL
,"IXNAME" VARCHAR(128) NOT NULL
,"KEYSEQ" VARCHAR(128) NOT NULL
,"KEYVALUE" VARCHAR(2000) NOT NULL FOR BIT DATA
,"TYPE" CHAR(1) NOT NULL
,"CARDF" FLOAT NOT NULL
,"KEYGROUPKEYNO" VARCHAR(254) NOT NULL FOR BIT DATA
,"NUMKEYS" SMALLINT NOT NULL
,"FREQUENCYF" FLOAT NOT NULL
,"QUANTILENO" SMALLINT NOT NULL
,"LOWVALUE" VARCHAR(2000) NOT NULL FOR BIT DATA
,"HIGHVALUE" VARCHAR(2000) NOT NULL FOR BIT DATA
)
IN ${db}.$tsX
VOLATILE
DATA CAPTURE CHANGES
CCSID UNICODE;
$/creTbKey/
else call err 'cannot update DSN_KEYTGTDIST_TABLE from' $kyDi
$** fileAid plan table ****************
$= f2pNN = 6 IDNO
$= mkF2P = $isCre
if $isUpd then $@[
$@%[tb f2p F2PLAN_TABLE$suf $]
$= mkF2P =- $f2p <> 6 IDNO
if $mkF2P & $f2p <> '' then
$$ drop table $cr.F2PLAN_TABLE$suf;
$]
if $mkF2P 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(3800) FOR SBCS DATA NOT NULL
WITH DEFAULT,
REMARKS CHAR(64) FOR Bit DATA NOT NULL
WITH DEFAULT,
idNo Integer not null with default
)
IN ${db}.$tsX
AUDIT NONE
NOT VOLATILE
DATA CAPTURE CHANGES
APPEND NO ;
$]
if $suf == '' then $@=/views/
--- 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.explain_time
, case when e.explain_time is null then 'p'
when e.explain_time > min(p.bindTime) then '>'
when e.explain_time = min(p.bindTime) then '='
else '<' end c
, min(p.pcTimeStamp) pcTimestamp
, min(p.lastUsed) lastUsed
, min(p.conToken) conToken
, sum(case when e.hint_Used = '' or e.hint_Used is null
then 0 else 1 end) hiUse
, sum(case when e.optHint = '' or e.optHint is null
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.explain_time
union all select collid
, progName prog
, version
, count(e.collid) expCnt
, e.explain_time
, 'e' c
, cast(null as timestamp) pcTimestamp
, cast(null as date) lastUsed
, cast(null as char(8)) conToken
, 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 $cr.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.explain_time
)
select substr(collid, 1, 6) "collid"
, substr(prog, 1, 8) "prog"
, substr(version, 1, 21) "version"
, smallint(expCnt) "expCnt"
, explain_time, c, pcTimestamp, lastUsed
, smallint(hiUse) "hiUse"
, smallint(optHi) "optHi"
, substr(hint, 1, 8) "hint"
, hex(conToken) conToken, collid, prog, version
, coalesce(pcTimestamp, explain_time) pcEx
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 = 'E ' THEN 'dirRow'
WHEN ACCESSTYPE = 'H ' THEN 'hash'
WHEN ACCESSTYPE = 'I ' THEN 'ixScan'
WHEN ACCESSTYPE = 'IN' THEN 'ixScIn'
WHEN ACCESSTYPE = 'I1' THEN 'ixOne '
WHEN ACCESSTYPE = 'N ' THEN 'ixScIn'
WHEN ACCESSTYPE = 'NR' THEN 'raLiAc'
WHEN ACCESSTYPE = 'M ' THEN 'ixMult'
WHEN ACCESSTYPE = 'MX' THEN 'ixMSca'
WHEN ACCESSTYPE = 'MI' THEN 'ixMInt'
WHEN ACCESSTYPE = 'MU' THEN 'ixMUni'
WHEN ACCESSTYPE = 'R ' THEN 'tsScan'
WHEN ACCESSTYPE = 'RW' THEN 'woScan'
WHEN ACCESSTYPE = 'T ' THEN 'ixSPRS'
WHEN ACCESSTYPE = 'V ' THEN 'insBuf'
WHEN ACCESSTYPE = ' ' THEN ' '
ELSE '??' || primary_accessType || '?' || 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",
substr(CASE WHEN A.TABLE_TYPE IS NULL THEN ' '
WHEN A.TABLE_TYPE = 'B' THEN 'buf'
WHEN A.TABLE_TYPE = 'C' THEN 'cte'
WHEN A.TABLE_TYPE = 'F' THEN 'fun'
WHEN A.TABLE_TYPE = 'I' THEN 'inL'
WHEN A.TABLE_TYPE = 'M' THEN 'mqt'
WHEN A.TABLE_TYPE = 'Q' THEN 'vqt'
WHEN A.TABLE_TYPE = 'R' THEN 'rte'
WHEN A.TABLE_TYPE = 'S' THEN 'sub'
WHEN A.TABLE_TYPE = 'T' THEN 'tb '
WHEN A.TABLE_TYPE = 'W' THEN 'wrk'
ELSE A.TABLE_TYPE || '??' END
|| right(' ' || tabNo, 3)
|| ' ' || coalesce(CORRELATION_NAME, '')
, 1, 10) as "tTy no cor",
substr(CASE WHEN METHOD = 3 THEN ''
WHEN ACCESSTYPE = 'R ' THEN ''
ELSE right(' ' || MATCHCOLS, 2) || indexOnly
END, 1, 3) AS "mcO",
substr(
CASE JOIN_TYPE WHEN 'F' THEN 'full '
WHEN 'L' THEN 'left '
WHEN 'S' THEN 'star '
ELSE '' END
|| CASE METHOD WHEN 0 THEN ''
WHEN 1 THEN 'nlJoin'
when 2 THEN 'smJoin'
WHEN 3 THEN 'sort '
WHEN 4 THEN 'hyJoin'
ELSE NULL END, 1, 8) AS "join/met",
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,
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, method,
-- package identifikation
collid, progName, applName, version, explain_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 = 3 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.explain_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.explain_time
AND d.QueryNO = A.QueryNO
AND d.QBlockNO = A.QBlockNO
AND d.PlanNo = A.PlanNo
; $*(
CREATE VIEW $cr.plan_ViewPred as old old
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.explain_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.explain_time
and p.queryNo = a.queryNo
and p.qBlockNo = a.qBlockNo
and p.predNo = f.predNo
;
-- sortierung --------------------------------------
-- order by applName, collid, progName, explain_time,
-- queryNo, qBlockNo, planno, mixOpSeq,
-- stage, predNo
$*)
CREATE VIEW $cr.plan_ViewPred as
select substr(right(' ' || strip(char(p.queryNo)) , 6)
|| right(' ' || strip(char(p.qBlockNo)) , 2)
|| right(' ' || strip(value(char(f.planNo), '')) , 2)
|| right(' ' || strip(value(char(f.mixOpSeqNo),'')),1)
,1 ,11) "Queryn B PM"
, smallint(p.PREDNO) prediN
, smallint(f.orderNO) orderN
, f.stage
, substr(fosFmte7(p.filter_factor), 1, 7) ff
, 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
, p.ORIGIN
, p.UNCERTAINTY
, p.QUERYNO, p.QBLOCKNO, p.APPLNAME, p.PROGNAME, p.group_member
, p.SECTNOI, p.COLLID, p.VERSION, p.explain_time, p.predNo
, f.ORDERCLASS
, f.MIXOPSEQNO
, f.REEVAL
, f.PUSHDOWN
, f.planNo
, f.orderNO
from $cr.dsn_predicat_table p
left join $cr.dsn_filter_Table f
on f.progName = p.progName
and f.applName = p.applName -- ??? ist manchmal x'0000
and f.collid = p.collid
and f.version = p.version
and f.group_member = p.group_member
and f.sectNoi = p.sectNoi
and f.explain_time = p.explain_time
and f.QUERYNO = p.QUERYNO
and f.QBLOCKNO = p.QBLOCKNO
and f.predNo = p.predNo
;
-- sortierung --------------------------------------
-- order by applName, collid, progName, explain_time,
-- queryNo, qBlockNo, predNo, orderNo, mixOpSeqNo
------------------------------------------------------------------------
-- 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.explain_time) explain_time
FROM $cr.PLAN_TABLE r
group by collid, progName
)
select CASE WHEN METHOD = 3 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.explain_time = l.explain_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.explain_time
AND S.QUERYNO = A.QUERYNO
;
commit;
------------ Ende user Views ----- cmnViews = $cmnViews----------------
$/views/
if $cmnViews & $suf == '' then $@=/cmnViews/
------------ 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, explain_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.explain_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.explain_time) Bexplain_time
-- FROM $cr.PLAN_TABLE B
-- GROUP BY B.PROGNAME, B.COLLID) AS N1
-- ON A.PROGNAME = N1.BPROGNAME
-- AND A.explain_time = N1.Bexplain_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.explain_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.explain_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.explain_time) Bexplain_time
-- FROM $cr.PLAN_TABLE B
-- GROUP BY B.PROGNAME, B.COLLID) AS N1
-- ON A.PROGNAME = N1.BPROGNAME
-- AND A.explain_time = N1.Bexplain_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.explain_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.explain_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.explain_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.explain_time
;
------------------------------------------------------------------------
commit
;
$/cmnViews/
if $doReo then $@=/reo/
// 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
$/reo/
$/createOrUpdate/
$/fEdit/
$;
$@proc tb $@/tb/
parse arg ., var tb
if $isCre then
$={$-[var$]}=- ''
else
$={$-[var$]}=- catTbLastCol($cr, tb)
$/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 $isCre then
$={$-[vKy$]}=- ''
else
$={$-[vKy$]}=- catIxKeys($cr, ix)
if ${$-[vKy$]} == '' then
$={$-[vDr$]}=- ''
else
$={$-[vDr$]}=- 'drop index' $cr'.'ix';commit;'
$]
$@proc ixAtt $@=[
USING STOGROUP GSMS PRIQTY -1 SECQTY -1 ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
$@[ if pos('l', arg(2)) < 1 then $@=[
NOT PADDED
$-[if(pos('c', arg(2)) < 1, NOT) CLUSTER$]
DEFER $defer
$] $]
COMPRESS NO
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G;
$]
$@proc dropViews $@/dropViews/
$<=[ select (select strip(d.bcreator) || '.' || strip(d.bname)
from sysibm.sysViewDep d
where bType = 'V'
and d.dcreator = v.creator and dName = v.name
and d.bcreator = v.creator and bName like 'PLAN%'
fetch first row only
) dep, v.creator cr, v.name na
from sysibm.sysTables v
where type = 'V' and name like 'PLAN%'
and creator = '$cr'
$]
call sqlSel
$| $@forWith v $@[
if $DEP == m.sqlNull then
$$ drop view $CR.$NA;
else
$$ $' --' view $CR.$NA depends on $DEP
$]
$/dropViews/
$@proc dropTS $@/dropTS/
$<=[ WITH pt AS
( select *
from sysibm.sysTables o
where type in ('T', 'X') and creator = '$cr'
and name in ('COST_TABLE'
,'DSN_COLDIST_TABLE'
,'DSN_DETCOST_TABLE'
,'DSN_FILTER_TABLE'
,'DSN_FUNCTION_TABLE'
,'DSN_KEYTGTDIST_TABLE'
,'DSN_PGRANGE_TABLE'
,'DSN_PGROUP_TABLE'
,'DSN_PREDICAT_TABLE'
,'DSN_PTASK_TABLE'
,'DSN_QUERY_AUX'
,'DSN_QUERY_TABLE'
,'DSN_QUERYINFO_TABLE'
,'DSN_QUERYINFO_AUX'
,'DSN_QUERYINFO_AUX2'
,'DSN_USERQUERY_TABLE'
,'DSN_USERQUERY_TABLE_AUX'
,'DSN_SORTKEY_TABLE'
,'DSN_SORT_TABLE'
,'DSN_STATEMENT_CACHE_AUX'
,'DSN_STATEMENT_CACHE_TABLE'
,'DSN_STATEMNT_TABLE'
,'DSN_STRUCT_TABLE'
,'DSN_VIEWREF_TABLE'
,'DSN_VIRTUAL_INDEXES'
,'EEEAUTH'
,'EEEDBRM'
,'EEEHINT'
,'EEEPATH'
,'EEEPLAN'
,'EEEWORK'
,'OBJECT_DATA'
,'OBJECT_DIRECTORY'
,'F2PLAN_TABLE'
,'PLAN_TABLE'
,'PREDICATE_TABLE'
,'STRUCTURE_TABLE'
)
)
, ts as
( select dbName db, name ts, type tsTy, nTables nTb
from sysibm.sysTableSpace s
where (dbName = '$db' and name like '$ts%')
union select dbName db, name ts, type tsTy, nTables nTb
from sysibm.sysTableSpace s
where (dbName, name) in (select pt.dbName, pt.tsName from pt)
)
select ts.*
, (select strip(o.creator) || '.' || strip(o.name)
from sysibm.sysTables o
where o.dbName = ts.db and o.tsName = ts.ts
and o.type not in ('V', 'A')
and not exists (select 1
from pt
where pt.creator = o.creator and pt.name = o.name)
fetch first 1 row only
) oth
from ts
order by translate(tsTy, 'ZZ', 'OP') || ts
$]
call sqlSel
$| $@forWith v $@[
if $OTH == m.sqlNull then
$$ drop tablespace $DB.$TS; -- type $TSTY, $NTB tables
else
call err 'cannot drop ts' $DB'.'$TS 'contains tb' $OTH
$]
$;
$$ COMMIT;
$/dropTS/