Explain Table Maintenance mit ADMIN_EXPLAIN_MAINT
Mit dem DB2 11 for z/OS Enhancement APAR PI05200 steht künftig eine sehr komfortable Möglichkeit zur Verfügung, Objekte, die während des EXPLAIN-Prozesses benötigt werden, anzulegen oder zu komplettieren bzw. zu aktuaklisieren. Der Anwender muss sich nur noch den gewünschten EXPLAIN-Table-Umfang aussuchen.
Die Rede ist von ADMIN_EXPLAIN_MAINT, der IBM DB2 supplied Stored Procedure für die EXPLAIN-Table-Maintenance.
Die Ausführung der Stored Procedure ist leider nicht ganz einfach, zumindest nicht für den „z/OS-Dinosaurier“. Ich habe deswegen den Aufruf von ADMIN_EXPLAIN_MAINT in eine kleine REXX-Prozedur gepackt, um sehr komfortabel z.B. über z/OS-Job die gewünschten Aktionen durchführen zu können.
Wenn die REXX Prozedur erstmal in einer konkatinierten TSO/ISPF Bibliothek steht, kann sie mit „TSO XMPEXADM“ aufgerufen werden und zeigt dann ihre möglichen Argumente an, wovon nur einige wenige „must“, die meisten „optional“ sind.
###############################################################################
# XMPEXADM V1.0 - Maintain or Create DB2 EXPLAIN Tables 14/12/30 20:00:00 #
###############################################################################
DB2 Subsystem not specified.
XMPEXADM: Syntax error.
[TSO] XMPEXADM :
SSID .......: DB2 Subsystem ID
MODE .......: RUN|PREVIEW
ACTION .....: STANDARDIZE|..._AND_CREATE|DROP|...
STANDARDIZE Upgrade all existing EXPLAIN tables
to the format for the current DB2 version.
STANDARDIZE_AND_CREATE Upgrade all existing
EXPLAIN tables to the format for the current
DB2 version, and create any missing tables of
those that are specified by the table-set
input parameter.
CREATE Create a new set of EXPLAIN tables in
the specified schema. Only tables that are
specified by the table-set input parameter
are created.
CREATE_ALIAS Create a new set of aliases only.
The new aliases are qualified by the value
that is specified for the schema-alias
input parameter.
DROP Drop all exisiting EXPLAIN tables and the
associated table space.
DROP_AND_CREATE Drop all existing EXPLAIN
tables, and the associated table space, and
create a replacement set of the tables that
are specified by the table-set input
parameter.
MGALIAS ....: manage aliases YES|NO
TABLESET ...: ALL|ACCEL|DIANGOSTICS|ALL_ACCEPT()
AUTHID .....: (must) authorization ID (CURRENT SQLID)
SCHEMA .....: (must) schema|qualifier (CURRENT SQLID)
SCHEMAALIAS : schema name for aliases
DBNAME .....: database
STGDBNAME ..: storage group tablespaces
STGIXNAME ..: storage group indexspaces
BP04NAME ...: BP 4K pages to be used
BP08NAME ...: BP 8K pages to be used
BP16NAME ...: BP 16K pages to be used
BP32NAME ...: BP 32K pages to be used
BPIXNAME ...: BP for IX pages to be used
BP04LOB ....: BP 4K pages to be used for LOB's
1 BP08LOB ....: BP 8K pages to be used for LOB's
BP16LOB ....: BP 16K pages to be used for LOB's
BP32LOB ....: BP 32K pages to be used for LOB's
Examples:
TSO XMPEXADM DB2P,PREVIEW,STANDARDIZE_AND_CREATE,,ALL,USER1,USER1
TSO XMPEXADM DB2P,RUN,CREATE,,ALL,ADMUSR1,USER2,DBUSR2
Sehr viel komfortabler wird der Aufruf aus einem z/OS Batch Job heraus:
//EXADM EXEC PGM=IKJEFT1A //***************************************************************** //* EXPLAIN TABLE MAINTENANCE //***************************************************************** //STEPLIB INCLUDE MEMBER=&SSID.#SL // DD DSN=YOURID.PGM.LOAD,DISP=SHR //SYSEXEC DD DSN=YOURID.REXX.LIB,DISP=SHR //SYSTSPRT DD SYSOUT=* //SYSPRINT DD SYSOUT=* //SYSTSIN DD * XMPEXADM DB2P,PREVIEW,STANDARDIZE_AND_CREATE,,ALL,ADMUSR1,DEMOID,,+ DEMODB /*
Der Output würde dann wie folgt aussehen:
XMPEXADM DB2P,PREVIEW,STANDARDIZE_AND_CREATE,,ALL,ADMUSR1,DEMOID,,DEMODB
###############################################################################
# XMPEXADM V1.0 - Maintain or Create DB2 EXPLAIN Tables 15/03/04 17:40:52 #
###############################################################################
XMPEXADM ...: Following values have been set for ADMIN_EXPLAIN_MAINT
DB2 SSID ...: DB2P
Mode .......: PREVIEW
Action .....: STANDARDIZE_AND_CREATE
Manage alias:
Table Set ..: ALL
Authid .....: ADMUSR1
Schema .....: DEMOID
Schema Alias:
Database ...: DEMODB
Storage Grp :
StGrp Index :
BP 4K ......:
BP 8K ......:
BP 16K .....:
BP 32K .....:
BP IX pages :
BP 4K LOB ..:
BP 8K LOB ..:
BP 16K LOB .:
BP 32K LOB .:
Summary of ADMIN_EXPLAIN_MAINT examinations:
--------------------------------------------
Created....: Database: 1 Tablespace: 0
Table: 23 IX: 21 Alias: 0
Examined...: Table: 0
Altered....: Table: 0
Dropped....: Tablespace: 0 Table: 0
Unexpected.: Table: 0
Not Stand..: Table: 0
Not UNICODE: Table: 0
Statements created by ADMIN_EXPLAIN_MAINT:
------------------------------------------
--- Row 1
CREATE DATABASE DEMODB CCSID UNICODE
--- Row 2
CREATE TABLE DEMOID.PLAN_TABLE ("QUERYNO" INTEGER NOT NULL, "QBLOCKNO" SMALLINT NOT NULL, "APPLNAME" VARCHAR(24) NOT NULL, "PROGNAM
E" 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) N
OT NULL, "SORTN_ORDERBY" CHAR(1) NOT NULL, "SORTN_GROUPBY" CHAR(1) NOT NULL, "SORTC_UNIQ" CHAR(1) NOT NULL, "SORTC_JOIN" CHAR(1) NO
T 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" SMALL
INT, "SORTN_PGROUP_ID" SMALLINT, "PARALLELISM_MODE" CHAR(1), "MERGE_JOIN_COLS" SMALLINT, "CORRELATION_NAME" VARCHAR(128), "PAGE_RAN
GE" CHAR(1) NOT NULL WITH DEFAULT, "JOIN_TYPE" CHAR(1) NOT NULL WITH DEFAULT, "GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DEFAULT, "IB
1M_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" VA
RCHAR(128) NOT NULL WITH DEFAULT, "PRIMARY_ACCESSTYPE" CHAR(1) NOT NULL WITH DEFAULT, "PARENT_QBLOCKNO" SMALLINT NOT NULL WITH DEFA
ULT, "TABLE_TYPE" CHAR(1), "TABLE_ENCODE" CHAR(1) NOT NULL WITH DEFAULT, "TABLE_SCCSID" SMALLINT NOT NULL WITH DEFAULT, "TABLE_MCCS
ID" SMALLINT NOT NULL WITH DEFAULT, "TABLE_DCCSID" SMALLINT NOT NULL WITH DEFAULT, "ROUTINE_ID" INTEGER NOT NULL WITH DEFAULT, "CTE
REF" 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" CHA
R(1) NOT NULL WITH DEFAULT, "MERGN" CHAR(1) NOT NULL WITH DEFAULT, "SCAN_DIRECTION" CHAR(1), "EXPANSION_REASON" CHAR(2) NOT NULL WI
TH DEFAULT) IN DATABASE DEMODB CCSID UNICODE
--- Row 3
CREATE INDEX DEMOID.PLAN_TABLE_HINT_IX ON DEMOID.PLAN_TABLE ("QUERYNO", "APPLNAME", "PROGNAME", "VERSION", "COLLID", "OPTHINT") CLO
SE NO
--- Row 4
CREATE TABLE DEMOID.DSN_FUNCTION_TABLE ("QUERYNO" INTEGER NOT NULL WITH DEFAULT, "QBLOCKNO" INTEGER NOT NULL WITH DEFAULT, "APPLNAM
E" 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 NUL
L 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 DEFA
ULT, "PATH" VARCHAR(2048) NOT NULL WITH DEFAULT, "FUNCTION_TEXT" VARCHAR(1500) NOT NULL WITH DEFAULT, "FUNC_VERSION" VARCHAR(122) N
OT NULL WITH DEFAULT, "SECURE" CHAR(1) NOT NULL WITH DEFAULT, "SECTNOI" INTEGER NOT NULL WITH DEFAULT, "VERSION" VARCHAR(122) NOT N
ULL WITH DEFAULT, "EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT) IN DATABASE DEMODB CCSID UNICODE
--- Row 5
CREATE TABLE DEMOID.DSN_STATEMNT_TABLE ("QUERYNO" INTEGER NOT NULL WITH DEFAULT, "APPLNAME" VARCHAR(24) NOT NULL WITH DEFAULT, "PRO
GNAME" VARCHAR(128) NOT NULL WITH DEFAULT, "COLLID" VARCHAR(128) NOT NULL WITH DEFAULT, "GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DE
FAULT, "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 D
EFAULT, "STMT_ENCODE" CHAR(1) NOT NULL WITH DEFAULT, "TOTAL_COST" FLOAT NOT NULL WITH DEFAULT, "SECTNOI" INTEGER NOT NULL WITH DEFA
ULT, "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT, "EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT) IN DATABASE DEMODB CCSID UNICO
DE
--- Row 6
CREATE TABLE DEMOID.DSN_STATEMENT_CACHE_TABLE ("STMT_ID" INTEGER NOT NULL, "STMT_TOKEN" VARCHAR(240), "COLLID" VARCHAR(128) NOT NUL
L, "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" INTE
GER 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 NUL
L, "STAT_SYNR" INTEGER NOT NULL, "STAT_WRIT" INTEGER NOT NULL, "STAT_EROW" INTEGER NOT NULL, "STAT_PROW" INTEGER NOT NULL, "STAT_SO
RT" INTEGER NOT NULL, "STAT_INDX" INTEGER NOT NULL, "STAT_RSCN" INTEGER NOT NULL, "STAT_PGRP" INTEGER NOT NULL, "STAT_ELAP" FLOAT N
OT 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, "S
TMT_ROWID" ROWID NOT NULL GENERATED ALWAYS, "BIND_RO_TYPE" CHAR(1) NOT NULL WITH DEFAULT, "BIND_RA_TOT" INTEGER NOT NULL WITH DEFAU
LT, "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 DE
FAULT, "STAT_PROWB" BIGINT NOT NULL WITH DEFAULT, "STAT_SORTB" BIGINT NOT NULL WITH DEFAULT, "STAT_INDXB" BIGINT NOT NULL WITH DEFA
ULT, "STAT_RSCNB" BIGINT NOT NULL WITH DEFAULT, "STAT_PGRPB" BIGINT NOT NULL WITH DEFAULT, "STAT_RIDLIMTB" BIGINT NOT NULL WITH DEF
AULT, "STAT_RIDSTORB" BIGINT NOT NULL WITH DEFAULT, "LITERAL_REPL" CHAR(1) NOT NULL WITH DEFAULT, "STAT_SUS_LATCH" FLOAT NOT NULL W
ITH DEFAULT, "STAT_SUS_PLATCH" FLOAT NOT NULL WITH DEFAULT, "STAT_SUS_DRAIN" FLOAT NOT NULL WITH DEFAULT, "STAT_SUS_CLAIM" FLOAT NO
T NULL WITH DEFAULT, "STAT_SUS_LOG" FLOAT NOT NULL WITH DEFAULT, "EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT) IN DATABASE DEMO
DB CCSID UNICODE
--- Row 7
CREATE INDEX DEMOID.DSN_STATEMENT_CACHE_IDX1 ON DEMOID.DSN_STATEMENT_CACHE_TABLE ("STMT_ID" ASC)
--- Row 8
CREATE INDEX DEMOID.DSN_STATEMENT_CACHE_IDX2 ON DEMOID.DSN_STATEMENT_CACHE_TABLE ("STMT_TOKEN" ASC) CLUSTER
--- Row 9
CREATE INDEX DEMOID.DSN_STATEMENT_CACHE_IDX3 ON DEMOID.DSN_STATEMENT_CACHE_TABLE ("EXPLAIN_TS" DESC)
1--- Row 10
CREATE TABLE DEMOID.DSN_PREDICAT_TABLE ("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, "BO
OLEAN_TERM" CHAR(1) NOT NULL, "SEARCHARG" CHAR(1) NOT NULL, "JOIN" CHAR(1) NOT NULL, "AFTER_JOIN" CHAR(1) NOT NULL, "ADDED_PRED" CH
AR(1) NOT NULL, "REDUNDANT_PRED" CHAR(1) NOT NULL, "DIRECT_ACCESS" CHAR(1) NOT NULL, "KEYFIELD" CHAR(1) NOT NULL, "EXPLAIN_TIME" TI
MESTAMP 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" CHA
R(8) NOT NULL, "GROUP_MEMBER" VARCHAR(24) NOT NULL, "ORIGIN" CHAR(1) NOT NULL WITH DEFAULT, "UNCERTAINTY" FLOAT(4) NOT NULL WITH DE
FAULT, "SECTNOI" INTEGER NOT NULL WITH DEFAULT, "COLLID" VARCHAR(128) NOT NULL WITH DEFAULT, "VERSION" VARCHAR(122) NOT NULL WITH D
EFAULT, "EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT) IN DATABASE DEMODB VOLATILE CCSID UNICODE
--- Row 11
CREATE INDEX DEMOID.DSN_PREDICAT_TABLE_IDX1 ON DEMOID.DSN_PREDICAT_TABLE ("QUERYNO", "EXPLAIN_TIME")
--- Row 12
CREATE INDEX DEMOID.DSN_PREDICAT_TABLE_IDX2 ON DEMOID.DSN_PREDICAT_TABLE ("QUERYNO", "PREDNO")
--- Row 13
CREATE TABLE DEMOID.DSN_STRUCT_TABLE ("QUERYNO" INTEGER NOT NULL, "QBLOCKNO" SMALLINT NOT NULL, "APPLNAME" VARCHAR(24) NOT NULL, "P
ROGNAME" VARCHAR(128) NOT NULL, "PARENT" SMALLINT NOT NULL, "TIMES" FLOAT NOT NULL, "ROWCOUNT" INTEGER NOT NULL, "ATOPEN" CHAR(1) N
OT NULL, "CONTEXT" CHAR(10) NOT NULL, "ORDERNO" SMALLINT NOT NULL, "DOATOPEN_PARENT" SMALLINT NOT NULL, "QBLOCK_TYPE" CHAR(6) NOT N
ULL 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" VARC
HAR(122) NOT NULL WITH DEFAULT, "EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT) IN DATABASE DEMODB VOLATILE CCSID UNICODE
--- Row 14
CREATE INDEX DEMOID.DSN_STRUCT_TABLE_IDX1 ON DEMOID.DSN_STRUCT_TABLE ("QUERYNO", "EXPLAIN_TIME")
--- Row 15
CREATE TABLE DEMOID.DSN_PGROUP_TABLE ("QUERYNO" INTEGER NOT NULL, "QBLOCKNO" SMALLINT NOT NULL, "PLANNAME" VARCHAR(24) NOT NULL, "C
OLLID" 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" FLOAT(4) NOT NULL, "IOCOST" FL
OAT(4) NOT NULL, "BESTTIME" FLOAT(4) NOT NULL, "DEGREE" SMALLINT NOT NULL, "MODE" CHAR(1) NOT NULL, "REASON" SMALLINT NOT NULL, "LO
CALCPU" SMALLINT NOT NULL, "TOTALCPU" SMALLINT NOT NULL, "FIRSTBASE" SMALLINT, "LARGETS" CHAR(1), "PARTKIND" CHAR(1), "GROUPTYPE" C
HAR(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" C
HAR(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, "EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT) IN DATABASE DEMODB VOLATIL
E CCSID UNICODE
--- Row 16
CREATE INDEX DEMOID.DSN_PGROUP_TABLE_IDX1 ON DEMOID.DSN_PGROUP_TABLE ("QUERYNO", "EXPLAIN_TIME")
--- Row 17
CREATE TABLE DEMOID.DSN_PTASK_TABLE ("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 B
IT DATA, "LPTLOPG" CHAR(4) FOR BIT DATA, "LPTHIPG" CHAR(4) FOR BIT DATA, "LPTLOPT" SMALLINT, "LPTHIPT" SMALLINT, "KEYCOLDT" SMALLIN
T, "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, "EXPANSIO
N_REASON" CHAR(2) NOT NULL WITH DEFAULT) IN DATABASE DEMODB VOLATILE CCSID UNICODE
--- Row 18
CREATE INDEX DEMOID.DSN_PTASK_TABLE_IDX1 ON DEMOID.DSN_PTASK_TABLE ("QUERYNO", "EXPLAIN_TIME")
--- Row 19
CREATE TABLE DEMOID.DSN_FILTER_TABLE ("QUERYNO" INTEGER NOT NULL, "QBLOCKNO" SMALLINT NOT NULL, "PLANNO" SMALLINT NOT NULL, "APPLNA
ME" 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, "MIXOPSEQN
O" SMALLINT NOT NULL, "REEVAL" CHAR(1) NOT NULL, "GROUP_MEMBER" VARCHAR(24) NOT NULL, "SECTNOI" INTEGER NOT NULL WITH DEFAULT, "VER
SION" VARCHAR(122) NOT NULL WITH DEFAULT, "PUSHDOWN" CHAR(1) NOT NULL WITH DEFAULT, "EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAUL
1T) IN DATABASE DEMODB VOLATILE CCSID UNICODE
--- Row 20
CREATE INDEX DEMOID.DSN_FILTER_TABLE_IDX1 ON DEMOID.DSN_FILTER_TABLE ("QUERYNO", "EXPLAIN_TIME")
--- Row 21
CREATE TABLE DEMOID.DSN_DETCOST_TABLE ("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" FL
OAT(4) NOT NULL, "DMIO" FLOAT(4) NOT NULL, "DMCPU" FLOAT(4) NOT NULL, "DMTOT" FLOAT(4) NOT NULL, "SUBQIO" FLOAT(4) NOT NULL, "SUBQC
PU" FLOAT(4) NOT NULL, "SUBQCOST" FLOAT(4) NOT NULL, "BASEIO" FLOAT(4) NOT NULL, "BASECPU" FLOAT(4) NOT NULL, "BASETOT" FLOAT(4) NO
T 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 NU
LL, "IMROWCST" FLOAT(4) NOT NULL, "IMPAGECST" FLOAT(4) NOT NULL, "IMRIDSORT" FLOAT(4) NOT NULL, "IMMERGCST" FLOAT(4) NOT NULL, "IMC
PU" 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, "DMCOL
S" 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, "S
NROWS" 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, "S
NSCANCPU" FLOAT(4) NOT NULL, "SNSCANCOST" FLOAT(4) NOT NULL, "SCCOLS" SMALLINT NOT NULL, "SCROWS" FLOAT(4) NOT NULL, "SCRECSZ" INTE
GER 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" FLOA
T(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" I
NTEGER NOT NULL, "TABREF" VARCHAR(64) NOT NULL FOR BIT DATA, "MAX_COMPOSITES" INTEGER NOT NULL, "MAX_STOR" INTEGER NOT NULL, "MAX_C
PU" INTEGER NOT NULL, "MAX_ELAP" INTEGER NOT NULL, "TBL_JOINED_THRESH" INTEGER NOT NULL, "STOR_USED" INTEGER NOT NULL, "CPU_USED" I
NTEGER 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_CO
ST_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_C
LIP" 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) N
OT NULL WITH DEFAULT, "UNCERTAINTY_1T" FLOAT(4) NOT NULL WITH DEFAULT, "SECTNOI" INTEGER NOT NULL WITH DEFAULT, "COLLID" VARCHAR(12
8) 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 DEF
AULT, "IMJFC" FLOAT(4) NOT NULL WITH DEFAULT, "CRED" INTEGER NOT NULL WITH DEFAULT, "IXSCAN_SKIP_DUPS" CHAR(1) NOT NULL WITH DEFAUL
T 'N', "IXSCAN_SKIP_SCREEN" CHAR(1) NOT NULL WITH DEFAULT 'N', "EARLY_OUT" CHAR(1) NOT NULL WITH DEFAULT ' ', "EXPANSION_REASON" CH
AR(2) NOT NULL WITH DEFAULT) IN DATABASE DEMODB VOLATILE CCSID UNICODE
--- Row 22
CREATE INDEX DEMOID.DSN_DETCOST_TABLE_IDX1 ON DEMOID.DSN_DETCOST_TABLE ("QUERYNO", "EXPLAIN_TIME")
--- Row 23
CREATE TABLE DEMOID.DSN_SORT_TABLE ("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 WIT
H 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" V
ARCHAR(122) NOT NULL WITH DEFAULT, "EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT) IN DATABASE DEMODB VOLATILE CCSID UNICODE
--- Row 24
CREATE INDEX DEMOID.DSN_SORT_TABLE_IDX1 ON DEMOID.DSN_SORT_TABLE ("QUERYNO", "EXPLAIN_TIME")
--- Row 25
CREATE TABLE DEMOID.DSN_SORTKEY_TABLE ("QUERYNO" INTEGER NOT NULL, "QBLOCKNO" SMALLINT NOT NULL, "PLANNO" SMALLINT NOT NULL, "APPLN
AME" VARCHAR(24) NOT NULL, "PROGNAME" VARCHAR(128) NOT NULL, "COLLID" VARCHAR(128) NOT NULL WITH DEFAULT, "SORTNO" SMALLINT NOT NUL
L, "ORDERNO" SMALLINT NOT NULL, "EXPTYPE" CHAR(3) NOT NULL, "TEXT" VARCHAR(128) NOT NULL, "TABNO" SMALLINT NOT NULL, "COLNO" SMALLI
NT NOT NULL, "DATATYPE" CHAR(18) NOT NULL, "LENGTH" INTEGER NOT NULL, "CCSID" INTEGER NOT NULL, "ORDERCLASS" INTEGER NOT NULL, "EXP
LAIN_TIME" TIMESTAMP NOT NULL, "GROUP_MEMBER" VARCHAR(24) NOT NULL, "SECTNOI" INTEGER NOT NULL WITH DEFAULT, "VERSION" VARCHAR(122)
NOT NULL WITH DEFAULT, "EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT) IN DATABASE DEMODB VOLATILE CCSID UNICODE
1--- Row 26
CREATE INDEX DEMOID.DSN_SORTKEY_TABLE_IDX1 ON DEMOID.DSN_SORTKEY_TABLE ("QUERYNO", "EXPLAIN_TIME")
--- Row 27
CREATE TABLE DEMOID.DSN_PGRANGE_TABLE ("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" TIMES
TAMP NOT NULL, "GROUP_MEMBER" VARCHAR(24) NOT NULL, "SECTNOI" INTEGER NOT NULL WITH DEFAULT, "APPLNAME" VARCHAR(24) NOT NULL WITH D
EFAULT, "PROGNAME" VARCHAR(128) NOT NULL WITH DEFAULT, "COLLID" VARCHAR(128) NOT NULL WITH DEFAULT, "VERSION" VARCHAR(122) NOT NULL
WITH DEFAULT, "EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT) IN DATABASE DEMODB VOLATILE CCSID UNICODE
--- Row 28
CREATE INDEX DEMOID.DSN_PGRANGE_TABLE_IDX1 ON DEMOID.DSN_PGRANGE_TABLE ("QUERYNO", "EXPLAIN_TIME")
--- Row 29
CREATE TABLE DEMOID.DSN_VIEWREF_TABLE ("QUERYNO" INTEGER NOT NULL WITH DEFAULT, "APPLNAME" VARCHAR(24) NOT NULL WITH DEFAULT, "PROG
NAME" 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, "M
QTUSE" SMALLINT NOT NULL WITH DEFAULT, "EXPLAIN_TIME" TIMESTAMP NOT NULL WITH DEFAULT, "GROUP_MEMBER" VARCHAR(24) NOT NULL, "SECTNO
I" INTEGER NOT NULL WITH DEFAULT, "EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT) IN DATABASE DEMODB VOLATILE CCSID UNICODE
--- Row 30
CREATE INDEX DEMOID.DSN_VIEWREF_TABLE_IDX1 ON DEMOID.DSN_VIEWREF_TABLE ("QUERYNO", "EXPLAIN_TIME")
--- Row 31
CREATE TABLE DEMOID.DSN_QUERY_TABLE ("QUERYNO" INTEGER NOT NULL, "TYPE" CHAR(8) NOT NULL, "QUERY_STAGE" CHAR(8) NOT NULL, "SEQNO" I
NTEGER NOT NULL, "NODE_DATA" CLOB(2M) NOT NULL, "EXPLAIN_TIME" TIMESTAMP NOT NULL, "QUERY_ROWID" ROWID NOT NULL GENERATED BY DEFAUL
T, "GROUP_MEMBER" VARCHAR(24) NOT NULL, "HASHKEY" INTEGER NOT NULL, "HAS_PRED" CHAR(1) NOT NULL, "SECTNOI" INTEGER NOT NULL WITH DE
FAULT, "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, "EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT) IN DATABASE DEMODB VO
LATILE CCSID UNICODE
--- Row 32
CREATE INDEX DEMOID.DSN_QUERY_TABLE_IDX1 ON DEMOID.DSN_QUERY_TABLE ("QUERYNO", "EXPLAIN_TIME")
--- Row 33
CREATE INDEX DEMOID.DSN_QUERY_TABLE_IDX2 ON DEMOID.DSN_QUERY_TABLE ("QUERYNO", "TYPE", "QUERY_STAGE", "EXPLAIN_TIME", "SEQNO")
--- Row 34
CREATE UNIQUE INDEX DEMOID.DSN_QUERY_TABLE_IDX3 ON DEMOID.DSN_QUERY_TABLE ("QUERY_ROWID")
--- Row 35
CREATE TABLE DEMOID.DSN_QUERYINFO_TABLE ("QUERYNO" INTEGER NOT NULL WITH DEFAULT, "QBLOCKNO" SMALLINT NOT NULL WITH DEFAULT, "QINAM
E1" 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 D
EFAULT, "GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DEFAULT, "SECTNOI" INTEGER NOT NULL WITH DEFAULT, "SEQNO" INTEGER NOT NULL WITH DE
FAULT, "EXPLAIN_TIME" TIMESTAMP NOT NULL WITH DEFAULT, "TYPE" CHAR(8) NOT NULL WITH DEFAULT, "REASON_CODE" SMALLINT NOT NULL WITH D
EFAULT, "QI_DATA" CLOB(2M) NOT NULL WITH DEFAULT, "SERVICE_INFO" BLOB(2M) NOT NULL WITH DEFAULT, "QB_INFO_ROWID" ROWID NOT NULL GEN
ERATED ALWAYS, "EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT) IN DATABASE DEMODB CCSID UNICODE
--- Row 36
CREATE TABLE DEMOID.DSN_VIRTUAL_INDEXES ("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" SMAL
LINT 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 "FIRSTKEYC
ARDF" >= 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 "O
RDERING1" IN ('A','D')), "COLNO2" SMALLINT CHECK("COLNO2" IS NULL OR "COLNO2" > 0), "ORDERING2" CHAR(1) CHECK("ORDERING2" IS NULL O
R "ORDERING2" IN ('A','D')), "COLNO3" SMALLINT CHECK("COLNO3" IS NULL OR "COLNO3" > 0), "ORDERING3" CHAR(1) CHECK("ORDERING3" IS NU
LL OR "ORDERING3" IN ('A','D')), "COLNO4" SMALLINT CHECK("COLNO4" IS NULL OR "COLNO4" > 0), "ORDERING4" CHAR(1) CHECK("ORDERING4" I
S NULL OR "ORDERING4" IN ('A','D')), "COLNO5" SMALLINT CHECK("COLNO5" IS NULL OR "COLNO5" > 0), "ORDERING5" CHAR(1) CHECK("ORDERING
5" IS NULL OR "ORDERING5" IN ('A','D')), "COLNO6" SMALLINT CHECK("COLNO6" IS NULL OR "COLNO6" > 0), "ORDERING6" CHAR(1) CHECK("ORDE
RING6" 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) CHE
1CK("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), "O
RDERING11" 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 O
R "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" SMAL
LINT CHECK("COLNO16" IS NULL OR "COLNO16" > 0), "ORDERING16" CHAR(1) CHECK("ORDERING16" IS NULL OR "ORDERING16" IN ('A','D')), "COL
NO17" 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 "O
RDERING19" 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("ORDER
ING21" IS NULL OR "ORDERING21" IN ('A','D')), "COLNO22" SMALLINT CHECK("COLNO22" IS NULL OR "COLNO22" > 0), "ORDERING22" CHAR(1) CH
ECK("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), "OR
DERING24" 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" SMALL
INT CHECK("COLNO29" IS NULL OR "COLNO29" > 0), "ORDERING29" CHAR(1) CHECK("ORDERING29" IS NULL OR "ORDERING29" IN ('A','D')), "COLN
O30" 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 "OR
DERING32" IN ('A','D')), "COLNO33" SMALLINT CHECK("COLNO33" IS NULL OR "COLNO33" > 0), "ORDERING33" CHAR(1) CHECK("ORDERING33" IS N
ULL OR "ORDERING33" IN ('A','D')), "COLNO34" SMALLINT CHECK("COLNO34" IS NULL OR "COLNO34" > 0), "ORDERING34" CHAR(1) CHECK("ORDERI
NG34" IS NULL OR "ORDERING34" IN ('A','D')), "COLNO35" SMALLINT CHECK("COLNO35" IS NULL OR "COLNO35" > 0), "ORDERING35" CHAR(1) CHE
CK("ORDERING35" IS NULL OR "ORDERING35" IN ('A','D')), "COLNO36" SMALLINT CHECK("COLNO36" IS NULL OR "COLNO36" > 0), "ORDERING36" C
HAR(1) CHECK("ORDERING36" IS NULL OR "ORDERING36" IN ('A','D')), "COLNO37" SMALLINT CHECK("COLNO37" IS NULL OR "COLNO37" > 0), "ORD
ERING37" 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" I
S 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" SMALLI
NT CHECK("COLNO42" IS NULL OR "COLNO42" > 0), "ORDERING42" CHAR(1) CHECK("ORDERING42" IS NULL OR "ORDERING42" IN ('A','D')), "COLNO
43" 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" I
N ('A','D')), "COLNO45" SMALLINT CHECK("COLNO45" IS NULL OR "COLNO45" > 0), "ORDERING45" CHAR(1) CHECK("ORDERING45" IS NULL OR "ORD
ERING45" IN ('A','D')), "COLNO46" SMALLINT CHECK("COLNO46" IS NULL OR "COLNO46" > 0), "ORDERING46" CHAR(1) CHECK("ORDERING46" IS NU
LL OR "ORDERING46" IN ('A','D')), "COLNO47" SMALLINT CHECK("COLNO47" IS NULL OR "COLNO47" > 0), "ORDERING47" CHAR(1) CHECK("ORDERIN
G47" IS NULL OR "ORDERING47" IN ('A','D')), "COLNO48" SMALLINT CHECK("COLNO48" IS NULL OR "COLNO48" > 0), "ORDERING48" CHAR(1) CHEC
K("ORDERING48" IS NULL OR "ORDERING48" IN ('A','D')), "COLNO49" SMALLINT CHECK("COLNO49" IS NULL OR "COLNO49" > 0), "ORDERING49" CH
AR(1) CHECK("ORDERING49" IS NULL OR "ORDERING49" IN ('A','D')), "COLNO50" SMALLINT CHECK("COLNO50" IS NULL OR "COLNO50" > 0), "ORDE
RING50" 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("C
OLNO54" IS NULL OR "COLNO54" > 0), "ORDERING54" CHAR(1) CHECK("ORDERING54" IS NULL OR "ORDERING54" IN ('A','D')), "COLNO55" SMALLIN
T CHECK("COLNO55" IS NULL OR "COLNO55" > 0), "ORDERING55" CHAR(1) CHECK("ORDERING55" IS NULL OR "ORDERING55" IN ('A','D')), "COLNO5
6" 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 "ORDE
RING58" IN ('A','D')), "COLNO59" SMALLINT CHECK("COLNO59" IS NULL OR "COLNO59" > 0), "ORDERING59" CHAR(1) CHECK("ORDERING59" IS NUL
L OR "ORDERING59" IN ('A','D')), "COLNO60" SMALLINT CHECK("COLNO60" IS NULL OR "COLNO60" > 0), "ORDERING60" CHAR(1) CHECK("ORDERING
160" 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" CHA
R(1) CHECK("ORDERING62" IS NULL OR "ORDERING62" IN ('A','D')), "COLNO63" SMALLINT CHECK("COLNO63" IS NULL OR "COLNO63" > 0), "ORDER
ING63" 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')), "KEYTARGET_COUNT" SMALLINT NOT NULL WITH DEFAULT
CHECK("KEYTARGET_COUNT" >= 0), "UNIQUE_COUNT" SMALLINT NOT NULL WITH DEFAULT CHECK("UNIQUE_COUNT" >= 0), "IX_EXTENSION_TYPE" CHAR(
1) NOT NULL WITH DEFAULT CHECK("IX_EXTENSION_TYPE" IN (' ','S','V')), "DATAREPEATFACTORF" FLOAT NOT NULL WITH DEFAULT -1 CHECK("DAT
AREPEATFACTORF" = -1 OR "DATAREPEATFACTORF" >= 1), "SPARSE" CHAR(1) NOT NULL WITH DEFAULT 'N' CHECK("SPARSE" IN ('N','Y','X'))) IN
DATABASE DEMODB CCSID UNICODE
--- Row 37
CREATE INDEX DEMOID.DSN_VIRTUAL_INDEXES_IDX1 ON DEMOID.DSN_VIRTUAL_INDEXES ("TBCREATOR", "TBNAME")
--- Row 38
CREATE TABLE DEMOID.DSN_COLDIST_TABLE ("QUERYNO" INTEGER NOT NULL, "APPLNAME" VARCHAR(128) NOT NULL, "PROGNAME" VARCHAR(128) NOT NU
LL, "COLLID" VARCHAR(128) NOT NULL, "GROUP_MEMBER" VARCHAR(128) NOT NULL, "SECTNOI" INTEGER NOT NULL, "VERSION" VARCHAR(122) NOT NU
LL, "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, "EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT) IN
DATABASE DEMODB CCSID UNICODE
--- Row 39
CREATE TABLE DEMOID.DSN_KEYTGTDIST_TABLE ("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) N
OT NULL, "KEYVALUE" VARCHAR(2000) NOT NULL FOR BIT DATA, "TYPE" CHAR(1) NOT NULL, "CARDF" FLOAT NOT NULL, "KEYGROUPKEYNO" VARCHAR(2
54) NOT NULL FOR BIT DATA, "NUMKEYS" SMALLINT NOT NULL, "FREQUENCYF" FLOAT NOT NULL, "QUANTILENO" SMALLINT NOT NULL, "LOWVALUE" VAR
CHAR(2000) NOT NULL FOR BIT DATA, "HIGHVALUE" VARCHAR(2000) NOT NULL FOR BIT DATA, "EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT
) IN DATABASE DEMODB CCSID UNICODE
--- Row 40
CREATE TABLE DEMOID.DSN_PREDICATE_SELECTIVITY ("QUERYNO" INTEGER NOT NULL, "QBLOCKNO" SMALLINT NOT NULL, "APPLNAME" VARCHAR(24) NOT
NULL, "PROGNAME" VARCHAR(128) NOT NULL, "SECTNOI" INTEGER NOT NULL WITH DEFAULT, "COLLID" VARCHAR(128) NOT NULL WITH DEFAULT, "VER
SION" VARCHAR(122) NOT NULL WITH DEFAULT, "PREDNO" INTEGER NOT NULL, "INSTANCE" SMALLINT NOT NULL, "SELECTIVITY" FLOAT NOT NULL, "W
EIGHT" FLOAT(4) NOT NULL, "ASSUMPTION" VARCHAR(128) NOT NULL, "INSERT_TIME" TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPD
ATE AS ROW CHANGE TIMESTAMP, "EXPLAIN_TIME" TIMESTAMP, "REMARKS" VARCHAR(762), "EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT) IN
DATABASE DEMODB CCSID UNICODE
--- Row 41
CREATE INDEX DEMOID.DSN_PREDSEL_IX1 ON DEMOID.DSN_PREDICATE_SELECTIVITY ("QUERYNO")
--- Row 42
CREATE TABLE DEMOID.DSN_STAT_FEEDBACK ("QUERYNO" INTEGER NOT NULL, "APPLNAME" VARCHAR(24) NOT NULL, "PROGNAME" VARCHAR(128) NOT NUL
L, "COLLID" VARCHAR(128) NOT NULL, "GROUP_MEMBER" VARCHAR(24) NOT NULL, "EXPLAIN_TIME" TIMESTAMP NOT NULL, "SECTNOI" INTEGER NOT NU
LL WITH DEFAULT, "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT, "TBCREATOR" VARCHAR(128) NOT NULL, "TBNAME" VARCHAR(128) NOT NULL, "
IXCREATOR" VARCHAR(128) NOT NULL, "IXNAME" VARCHAR(128) NOT NULL, "COLNAME" VARCHAR(128) NOT NULL, "NUMCOLUMNS" SMALLINT NOT NULL,
"COLGROUPCOLNO" VARCHAR(254) NOT NULL FOR BIT DATA, "TYPE" CHAR(1) NOT NULL, "DBNAME" VARCHAR(24) NOT NULL, "TSNAME" VARCHAR(24) NO
T NULL, "REASON" CHAR(8) NOT NULL, "REMARKS" VARCHAR(254) NOT NULL) IN DATABASE DEMODB CCSID UNICODE
--- Row 43
CREATE TABLE DEMOID.DSN_VIRTUAL_KEYTARGETS ("ENABLE" CHAR(1) NOT NULL CHECK("ENABLE" IN ('Y','N')), "IXNAME" VARCHAR(128) NOT NULL,
"IXSCHEMA" VARCHAR(128) NOT NULL, "KEYSEQ" SMALLINT NOT NULL WITH DEFAULT CHECK("KEYSEQ" >= 0), "COLNO" SMALLINT NOT NULL WITH DEF
AULT CHECK("COLNO" >= 0), "ORDERING" CHAR(1) NOT NULL CHECK("ORDERING" IN ('A')), "TYPESCHEMA" VARCHAR(128) NOT NULL, "TYPENAME" VA
RCHAR(128) NOT NULL, "LENGTH" SMALLINT NOT NULL CHECK("LENGTH" > 0), "LENGTH2" INTEGER NOT NULL CHECK("LENGTH2" >= 0), "SCALE" SMAL
LINT NOT NULL WITH DEFAULT CHECK("SCALE" >= 0), "NULLS" CHAR(1) NOT NULL WITH DEFAULT 'N' CHECK("NULLS" IN ('Y','N')), "CCSID" INTE
GER NOT NULL CHECK("CCSID" >= 0), "SUBTYPE" CHAR(1) NOT NULL CHECK("SUBTYPE" IN ('B','M','S',' ')), "DERIVED_FROM" VARCHAR(4000) NO
T NULL, "CARDF" FLOAT NOT NULL WITH DEFAULT -1 CHECK("CARDF" >= 0 OR "CARDF" = -1 OR "CARDF" = -2)) IN DATABASE DEMODB CCSID UNICOD
E
--- Row 44
CREATE TABLE DEMOID.DSN_USERQUERY_TABLE ("QUERYNO" INTEGER NOT NULL PRIMARY KEY, "SCHEMA" VARCHAR(128) NOT NULL WITH DEFAULT ' ', "
HINT_SCOPE" SMALLINT NOT NULL WITH DEFAULT 0, "QUERY_TEXT" CLOB(2M) NOT NULL, "QUERY_ROWID" ROWID NOT NULL GENERATED ALWAYS, "QUERY
ID" BIGINT NOT NULL WITH DEFAULT 0, "USERFILTER" CHAR(8) NOT NULL WITH DEFAULT ' ', "OTHER_OPTIONS" CHAR(128) NOT NULL WITH DEFAULT
1 ' ', "COLLECTION" VARCHAR(128) NOT NULL WITH DEFAULT ' ', "PACKAGE" VARCHAR(128) NOT NULL WITH DEFAULT ' ', "VERSION" VARCHAR(128)
NOT NULL WITH DEFAULT ' ', "REOPT" CHAR(1) NOT NULL WITH DEFAULT ' ', "STARJOIN" CHAR(1) NOT NULL WITH DEFAULT ' ', "MAX_PAR_DEGRE
E" INTEGER NOT NULL WITH DEFAULT -1, "DEF_CURR_DEGREE" CHAR(3) NOT NULL WITH DEFAULT ' ', "SJTABLES" INTEGER NOT NULL WITH DEFAULT
-1, "OTHER_PARMS" VARCHAR(128) NOT NULL WITH DEFAULT ' ', "SELECTVTY_OVERRIDE" CHAR(1) NOT NULL WITH DEFAULT 'N', "ACCESSPATH_HINT"
CHAR(1) NOT NULL WITH DEFAULT ' ', "OPTION_OVERRIDE" CHAR(1) NOT NULL WITH DEFAULT ' ') IN DATABASE DEMODB CCSID UNICODE
--- Row 45
CREATE UNIQUE INDEX DEMOID.DSN_USERQUERY_TABLE_IX1 ON DEMOID.DSN_USERQUERY_TABLE ("QUERYNO" ASC)
*** end ***
Aber hier nun endlich der REXX code der Prozedure XMPEXADM:
/*REXX****************************************************************/
/* XMPEXADM ..: Framework to maintain or create EXPLAIN tables in */
/* DB2 11 z/OS databases using ADMIN_EXPLAIN_MAINT. */
/*-------------------------------------------------------------------*/
/* CALLED BY..: IKJEFT01 - TSO Batch oder Online */
/* ARGUMENTS..: SSID - (must) Local/Home Subsystem */
/* MODE - RUN|PREVIEW */
/* ACTION - STANDARDIZE|..._AND_CREATE|DROP|... */
/* MGALIAS - manage aliases YES|NO */
/* TABLESET - ALL|ACCEL|DIANGOSTICS|ALL_ACCEPT() */
/* AUTHID - (must) authorization ID (CURRENT SQLID) */
/* SCHEMA - (must) schema|qaulifier (CURRENT SCHEMA)*/
/* SCHEMAALIAS- schema name for aliases */
/* DBNAME - database */
/* STGDBNAME - storage group tablespaces */
/* STGIXNAME - storage group indexespaces */
/* BP04NAME - BP 4K pages to be used */
/* BP08NAME - BP 8K pages to be used */
/* BP16NAME - BP 16K pages to be used */
/* BP32NAME - BP 32K pages to be used */
/* BPIXNAME - BP for IX pages to be used */
/* BP04LOB - BP 4K pages to be used for LOB's */
/* BP08LOB - BP 8K pages to be used for LOB's */
/* BP16LOB - BP 16K pages to be used for LOB's */
/* BP32LOB - BP 32K pages to be used for LOB's */
/* CALLS......: DSNREXX M - DB2/REXX Interface */
/* XMPABND M - Abend U0001 (Non-ISPF) */
/*-------------------------------------------------------------------*/
/* COMMENTS...: Verify that SDSNLOAD ist part of LINKLIST or already */
/* allocated to TSO session. If not, you need to */
/* dynamically allocate. (See STEPLIBX in code below!) */
/* Example of batch execution JCL: */
/* //EXADM EXEC PGM=IKJEFT1A */
/* //STEPLIB DD DSN=SDSNLOAD,DISP=SHR */
/* //SYSEXEC DD DSN=your.SYSEXEC,DISP=SHR */
/* //SYSTSPRT DD SYSOUT=* */
/* //SYSPRINT DD SYSOUT=* */
/* //SYSTSIN DD * */
/* XMPEXADM ssid [options, some must be passed] */
/* */
/*-------------------------------------------------------------------*/
/* FUNCTION...: Calls ADMIN_EXPLAIN_MAINT store procedure to */
/* maintain or create EXPLAIN tables. */
/*-------------------------------------------------------------------*/
/* HISTORY....: 29.12.14/G.Ruban - V1R0 initial edition */
/* nn.nn.2015 N.Nomen Vn.n - future amendments */
/*********************************************************************/
x = MSG("OFF")
/*********************************************************************/
/* process arguments passed to procedure */
/*********************************************************************/
parse upper arg arglist
ver = "1.0"
Numeric Digits 12
if wordpos("DEBUG",arglist)>0
then do
debug = "DEBUG"
arglist = delword(arglist,wordpos("DEBUG",arglist))
TRACE ?I
SQLTRACE = "TRACE"
end
/*********************************************************************/
/* Some environment information and initialzations */
/*********************************************************************/
PARSE SOURCE,
source_host_env,
source_call_type,
source_uc_exec_name,
source_hostdd,
source_hostdsn,
source_nf_exec_name,
source_initial_cmd_env,
source_asid_name
jobnamestep = Get_Jobname()
sysvar_sysuid = SYSVAR("SYSUID")
sysvar_sysenv = SYSVAR("SYSENV")
/*********************************************************************/
/* Header */
/*********************************************************************/
"CLRSCRN"
say copies("#",79)
say "# XMPEXADM V"ver" - Maintain or Create DB2 EXPLAIN Tables",
" "date("O")" "time()" #";
say copies("#",79)
say ""
/*********************************************************************/
/* Check required environment */
/*********************************************************************/
if (source_host_env ^= "TSO") then do
Say "Wrong host environment" source_host_env
signal error
end
/*********************************************************************/
/* split and check arguments */
/*********************************************************************/
parse var arglist ssid "," mode "," action "," mgalias ",",
tableset "," authid "," schema "," schemaalias ",",
dbname "," stgdbname "," stgixname "," bp04name ",",
bp08name "," bp16name "," bp32name "," bpixname ",",
bp04lob "," bp08lob "," bp16lob "," bp32lob
if ssid = ""
then do
say "DB2 Subsystem not specified."
signal syntax
end
if authid = ""
then do
say "XMPEXADM ...: Following values have been set for ADMIN_EXPLAIN_MAINT"
say "DB2 SSID ...:" ssid
say "Mode .......:" mode
say "Action .....:" action
say "Manage alias:" mgalias
say "Table Set ..:" tableset
say "Auth ID not specified."
signal syntax
end
if schema = ""
then do
say "XMPEXADM ...: Following values have been set for ADMIN_EXPLAIN_MAINT"
say "DB2 SSID ...:" ssid
say "Mode .......:" mode
say "Action .....:" action
say "Manage alias:" mgalias
say "Table Set ..:" tableset
say "Authid .....:" authid
say "EXPLAIN Table Schema not specified."
signal syntax
end
say "XMPEXADM ...: Following values have been set for ADMIN_EXPLAIN_MAINT"
say "DB2 SSID ...:" ssid
say "Mode .......:" mode
say "Action .....:" action
say "Manage alias:" mgalias
say "Table Set ..:" tableset
say "Authid .....:" authid
say "Schema .....:" schema
say "Schema Alias:" schemaalias
say "Database ...:" dbname
say "Storage Grp :" stgdbname
say "StGrp Index :" stgixname
say "BP 4K ......:" bp04name
say "BP 8K ......:" bp08name
say "BP 16K .....:" bp16name
say "BP 32K .....:" bp32name
say "BP IX pages :" bpixname
say "BP 4K LOB ..:" bp04lob
say "BP 8K LOB ..:" bp08lob
say "BP 16K LOB .:" bp16lob
say "BP 32K LOB .:" bp32lob
say ""
/*********************************************************************/
/* If DB2 Load Library is not part of z/OS LINKLIST: */
/* Allocate SDSNLOAD using CA TSOPLUS (or any other software tool */
/* which adds the DB2 load libray dynamically to the STEPLIB). */
/*********************************************************************/
address TSO
"FREE FI(STEPLIBX) DA('IZS.DSN."ssid".SDSNLOAD')"
"ALLOC FI(STEPLIBX) DA('IZS.DSN."ssid".SDSNLOAD') SHR REUSE"
if rc <> 0
then do
say "DB2 SDSNLOAD library for SSID="ssid" is not available!"
say "Check z/OS LINKLIST or allocate to STEPLIB in advance!"
signal error
end
ADDRESS TSO "SUBCOM DSNREXX"
if rc <> 0
then do
cc = RXSUBCOM("ADD","DSNREXX","DSNREXX")
if cc
then do
say "DB2 SDSNLOAD library not available!"
say "Check z/OS LINKLIST or allocate to STEPLIB in advance!"
signal error
end
end
/*********************************************************************/
/* Set indicators and other initial values */
/*********************************************************************/
if mode = "" then ind_mode = -1; else ind_mode = 0;
if action = "" then ind_action = -1; else ind_action = 0;
if mgalias = "" then ind_mgalias = -1; else ind_mgalias = 0;
if tableset = "" then ind_tableset = -1; else ind_tableset = 0;
if authid = "" then ind_authid = -1; else ind_authid = 0;
if schema = "" then ind_schema = -1; else ind_schema = 0;
if schemaalias = "" then ind_schemaalias = -1; else ind_schemaalias = 0;
if dbname = "" then ind_dbname = -1; else ind_dbname = 0;
if stgdbname = "" then ind_stgdbname = -1; else ind_stgdbname = 0;
if stgixname = "" then ind_stgixname = -1; else ind_stgixname = 0;
if bp04name = "" then ind_bp04name = -1; else ind_bp04name = 0;
if bp08name = "" then ind_bp08name = -1; else ind_bp08name = 0;
if bp16name = "" then ind_bp16name = -1; else ind_bp16name = 0;
if bp32name = "" then ind_bp32name = -1; else ind_bp32name = 0;
if bpixname = "" then ind_bpixname = -1; else ind_bpixname = 0;
if bp04lob = "" then ind_bp04lob = -1; else ind_bp04lob = 0;
if bp08lob = "" then ind_bp08lob = -1; else ind_bp08lob = 0;
if bp16lob = "" then ind_bp16lob = -1; else ind_bp16lob = 0;
if bp32lob = "" then ind_bp32lob = -1; else ind_bp32lob = 0;
prc = 0; ind_prc = 0;
pmsg = COPIES(",",2048); ind_pmsg = 0;
resultset3 = 0; /* Result set 3 not returned */
/*********************************************************************/
/* Connect to DB2 subsystem and clear results */
/*********************************************************************/
ADDRESS DSNREXX "CONNECT" ssid
call Code 0
ADDRESS DSNREXX "EXECSQL DELETE FROM SYSIBM.EXPLAIN_MAINT_SUMMARY"
call Code 0
ADDRESS DSNREXX "EXECSQL DELETE FROM SYSIBM.EXPLAIN_MAINT_SQL"
call Code 0
ADDRESS DSNREXX "EXECSQL DELETE FROM SYSIBM.EXPLAIN_MAINT_TB_NOT_UPGRADED"
/*********************************************************************/
/* call DB2 stored procedure */
/*********************************************************************/
sqlstmt = "CALL SYSPROC.ADMIN_EXPLAIN_MAINT",
"(:mode:ind_mode, ",
" :action:ind_action, ",
" :mgalias:ind_mgalias, ",
" :tableset:ind_tableset, ",
" :authid:ind_authid, ",
" :schema:ind_schema, ",
" :schemaalias:ind_schemaalias, ",
" :dbname:ind_dbname, ",
" :stgdbname:ind_stgdbname, ",
" :stgixname:ind_stgixname, ",
" :bp04name:ind_bp04name, ",
" :bp08name:ind_bp08name, ",
" :bp16name:ind_bp16name, ",
" :bp32name:ind_bp32name, ",
" :bpixname:ind_bpixname, ",
" :bp04lob:ind_bp04lob, ",
" :bp08lob:ind_bp08lob, ",
" :bp16lob:ind_bp16lob, ",
" :bp32lob:ind_bp32lob, ",
" :prc:ind_prc, ",
" :pmsg:ind_pmsg)";
address DSNREXX "EXECSQL" sqlstmt
if prc > 8
then do
say copies("-",79)
say "ADMIN_EXPLAIN_MAINT terminated with severe errors:"
say "Return Code .......: "prc
say "Error Message .....: "pmsg
say copies("-",79)
signal error
end
if prc = 4
then do
say copies("-",79)
say "ADMIN_EXPLAIN_MAINT:"
say "One or more existing EXPLAIN tables are not in Unicode. The"
say "table formats are updated to the format for the current DB2"
say "version. However, the tables are not converted to Unicode."
end
if prc = 8
then do
say copies("-",79)
say "ADMIN_EXPLAIN_MAINT:"
say "The format of one or more existing EXPLAIN tables that were"
say "included in the table-set input parameter cannot be updated"
say "to the format for the current DB2 version because the"
say "existing format does not match any known format, or the"
say "tables cannot be altered."
end
call Code 0 466 /* +466 = more rows to fetch */
/*********************************************************************/
/* Continue only if result set returned (SQLCODE=+466) */
/*********************************************************************/
if sqlcode = 0
then do
say copies("-",79)
say "ADMIN_EXPLAIN_MAINT has not detected any discrepancy"
say "or missing EXPLAIN tables. No result has been returned."
say copies("-",79)
exit 0
end
/*********************************************************************/
/* Combine 3 locators with the stored procedure */
/*********************************************************************/
LOC1 = d2x(0)
LOC2 = d2x(0)
LOC3 = d2x(0)
sqlstmt = "ASSOCIATE LOCATORS (:LOC1, :LOC2 , :LOC3)",
"WITH PROCEDURE SYSPROC.ADMIN_EXPLAIN_MAINT"
address DSNREXX "EXECSQL" sqlstmt;
call code 0
/*********************************************************************/
/* Init variables used with locator/cursor c1 */
/*********************************************************************/
db_created = ""
ts_created = ""
tb_created = ""
aux_created = ""
ix_created = ""
alias_created = ""
tb_examined = ""
tb_altered = ""
ts_dropped = ""
tb_dropped = ""
tb_unexpected_format = ""
tb_not_standardized = ""
tb_not_unicode = ""
/*********************************************************************/
/* Allocate and process cursor-driven LOC1/C1 summary result set */
/*********************************************************************/
say ""
say "Summary of ADMIN_EXPLAIN_MAINT examinations:"
say "--------------------------------------------"
sqlstmt = "ALLOCATE C101 CURSOR FOR RESULT SET :LOC1"
address DSNREXX "EXECSQL" sqlstmt;
do while sqlcode = 0
call code 0
sqlstmt = "FETCH C101 INTO :db_created, :ts_created, ",
" :tb_created, :aux_created, ",
" :ix_created, :alias_created, ",
" :tb_examined, :tb_altered, ",
" :ts_dropped, :tb_dropped, ",
" :tb_unexpected_format, ",
" :tb_not_standardized, ",
" :tb_not_unicode";
Address DSNREXX "EXECSQL" sqlstmt;
if sqlcode = 0
then do
say "Created....: Database: "db_created" Tablespace: "ts_created
say " Table: "tb_created" IX: "ix_created,
"Alias: "alias_created
say "Examined...: Table: "tb_examined
say "Altered....: Table: "tb_altered
say "Dropped....: Tablespace: "ts_dropped" Table: "tb_dropped
say "Unexpected.: Table: "tb_unexpected_format
say "Not Stand..: Table: "tb_not_standardized
say "Not UNICODE: Table: "tb_not_unicode
end
end
call code 0 100
sqlstmt = "CLOSE C101"
address DSNREXX "EXECSQL "sqlstmt;
call code 0 100
/*********************************************************************/
/* Allocate and process cursor-driven LOC2/C102 statement result set */
/*********************************************************************/
sqlstmt = "ALLOCATE C102 CURSOR FOR RESULT SET :LOC2"
address DSNREXX "EXECSQL" sqlstmt;
if sqlcode = 0 /* result set 2 returned */
then do
say ""
say "Statements created by ADMIN_EXPLAIN_MAINT:"
say "------------------------------------------"
do while sqlcode = 0
call code 0
sqlstmt = "FETCH C102 INTO :rownum2, :sqlout";
Address DSNREXX "EXECSQL" sqlstmt;
if sqlcode = 0
then do
say "--- Row "rownum2
say sqlout
end
end
call code 0 100
sqlstmt = "CLOSE C102"
address DSNREXX "EXECSQL "sqlstmt;
call code 0 100
/****************************************************************/
/* Allocate and process cursor-driven LOC3/C103 proc. results */
/****************************************************************/
sqlstmt = "ALLOCATE C103 CURSOR FOR RESULT SET :LOC3"
address DSNREXX "EXECSQL" sqlstmt;
if sqlcode = 0 /* result set 3 returned */
then do
say ""
say "Exceptions while processing generated statements:"
say "-------------------------------------------------"
do while sqlcode = 0
call code 0
sqlstmt = "FETCH C103 INTO :rownum3, :schemaname, :tbname,",
" :reason";
Address DSNREXX "EXECSQL" sqlstmt;
say "--- Row "rownum3":",
say "Schema: "schemaname" Table:"tbname
say "Reason:"reason
end
call code 0 100
sqlstmt = "CLOSE C103"
address DSNREXX "EXECSQL "sqlstmt;
call code 0 100
end
end
else do
say ""
say "ADMIN_EXPLAIN_MAINT did not detect any discrepancies."
end
say "*** end ***"
/*********************************************************************/
/* Disconnect vom Subsystem */
/*********************************************************************/
Disconnect:
ADDRESS DSNREXX "DISCONNECT"
cc = RXSUBCOM("DELETE","DSNREXX","DSNREXX")
return
/*********************************************************************/
/* Error processing and termination */
/*********************************************************************/
SYNTAX:
say "XMPEXADM: Syntax error."
say " [TSO] XMPEXADM :"
say " SSID .......: DB2 Subsystem ID"
say " MODE .......: RUN|PREVIEW "
say " ACTION .....: STANDARDIZE|..._AND_CREATE|DROP|... "
say " STANDARDIZE Upgrade all existing EXPLAIN tables "
say " to the format for the current DB2 version. "
say " STANDARDIZE_AND_CREATE Upgrade all existing "
say " EXPLAIN tables to the format for the current "
say " DB2 version, and create any missing tables of"
say " those that are specified by the table-set"
say " input parameter. "
say " CREATE Create a new set of EXPLAIN tables in "
say " the specified schema. Only tables that are "
say " specified by the table-set input parameter"
say " are created."
say " CREATE_ALIAS Create a new set of aliases only."
say " The new aliases are qualified by the value"
say " that is specified for the schema-alias"
say " input parameter."
say " DROP Drop all exisiting EXPLAIN tables and the"
say " associated table space."
say " DROP_AND_CREATE Drop all existing EXPLAIN"
say " tables, and the associated table space, and"
say " create a replacement set of the tables that"
say " are specified by the table-set input "
say " parameter."
say " MGALIAS ....: manage aliases YES|NO "
say " TABLESET ...: ALL|ACCEL|DIANGOSTICS|ALL_ACCEPT() "
say " AUTHID .....: (must) authorization ID (CURRENT SQLID)"
say " SCHEMA .....: (must) schema|qaulifier (CURRENT (SQLID)"
say " SCHEMAALIAS : schema name for aliases"
say " DBNAME .....: database "
say " STGDBNAME ..: storage group tablespaces "
say " STGIXNAME ..: storage group indexespaces "
say " BP04NAME ...: BP 4K pages to be used "
say " BP08NAME ...: BP 8K pages to be used "
say " BP16NAME ...: BP 16K pages to be used "
say " BP32NAME ...: BP 32K pages to be used "
say " BPIXNAME ...: BP for IX pages to be used "
say " BP04LOB ....: BP 4K pages to be used for LOB's"
say " BP08LOB ....: BP 8K pages to be used for LOB's "
say " BP16LOB ....: BP 16K pages to be used for LOB's "
say " BP32LOB ....: BP 32K pages to be used for LOB's "
say "Example:"
say "TSO XMPEXADM DB2P,PREVIEW,STANDARDIZE_AND_CREATE,,ALL,USER1,USER1"
say "TSO XMPEXADM DB2P,RUN,CREATE,,ALL,ADMUSER1,USER3,DBUSER3"
ERROR:
say ""
If Sysvar(SYSISPF) = "NOT ACTIVE"
then do
say "REXX procedure XMPEXADM ends with U0001."
address ATTACH "XMPABND"
exit 0
end
say "REXX procedure XMPEXADM ends with RC=20."
exit 20
/*********************************************************************/
/* Check SQLCODE's allowed */
/*********************************************************************/
Code:
arg codes
allowed = "NO"
do ci=1 to words(codes)
if sqlcode = word(codes,ci)
then allowed = "YES"
end
if allowed = "NO"
then do
say "CONNECT: SQL Error with SQLCODE="sqlcode
call TIAR sqlcode
say " "sqlstmt
call Disconnect
signal error
end
return
/*********************************************************************/
/* process line output, hide if value contains no information */
/*********************************************************************/
Line: parse arg lval ltext
lval = trunc(lval)
if lval > 0
then say right(lval,20)" "ltext
return
/*********************************************************************/
/* Retrieve some internal information from z/OS control blocks */
/*********************************************************************/
GET_JOBNAME: Procedure
/* all fields are part of general use prog'ing interface */
PSATOLD = '21C'X
TCB = C2D(STORAGE(C2X(PSATOLD),4))
TIOT = C2D(STORAGE(D2X(TCB+12),4))
TIOCNJOB = STRIP(STORAGE(D2X(TIOT),8))
TIOCSTEP = STRIP(STORAGE(D2X(TIOT+8),8))
Return TIOCNJOB TIOCSTEP
/*********************************************************************/
/* Display SQL Commmunication area using DSNTIAR */
/*********************************************************************/
TIAR: arg sqlcode
sqlc = d2x(sqlcode,8)
sqlc = x2c(sqlc)
sqlca = 'sqlca '
sqlca = sqlca !! x2c(00000088)
sqlca = sqlca !! sqlc
sqlca = sqlca !! x2c(0000)
sqlca = sqlca !! copies(' ',78)
sqlca = sqlca !! copies(x2c(00),24)
sqlca = sqlca !! copies(' ',16)
tiar_msg = x2c(0190)copies(' ',400)
text_len = x2c(00000050) ;
/***************************************************************/
/* Extract Message from Return Area */
/***************************************************************/
ADDRESS ATTCHPGM 'DSNTIAR SQLCA TIAR_MSG TEXT_LEN'
say substr(tiar_msg,4,400);
/***************************************************************/
/* Extract Message from Return Area */
/***************************************************************/
len = c2d(substr(rtrnarea,5,2))
/***************************************************************/
/* Extract Message from Return Area */
/***************************************************************/
say " SQLCODE="sqlcode
say " SQLERRMC="sqlerrmc
say " SQLWARN5="sqlwarn.5
say " SQLERRP ="sqlerrp
say " SQLSTATE="sqlstate
return
IBM DB2 Dokumentation zur Stored Procedure ADMIN_EXPLAIN_MAINT gibt’s hier!
Comments
Comments are closed.