DSNTESC
THE FOLLOWING SQL STATEMENTS DEMONSTRATE HOW TO CREATE EXPLAIN TABLES AND RELATED OBJECTS.
-----------------------------------------------------------------------
-- NAME = DSNTESC
--
-- DESCRIPTIVE NAME = SAMPLE EXPLAIN TABLES
--
-- LICENSED MATERIALS - PROPERTY OF IBM
-- 5698-DB2
-- COPYRIGHT IBM CORP 1982, 2022
--
-- STATUS = VERSION 13
--
-- FUNCTION = THE FOLLOWING SQL STATEMENTS DEMONSTRATE HOW TO:
-- (1) CREATE THESE SAMPLE V13 EXPLAIN TABLES:
-- * PLAN_TABLE
-- * DSN_FUNCTION_TABLE
-- * DSN_STATEMNT_TABLE
-- * DSN_STATEMENT_CACHE_TABLE
-- * DSN_STRUCT_TABLE
-- * DSN_PREDICAT_TABLE
-- * DSN_DETCOST_TABLE,
-- * DSN_SORT_TABLE
-- * DSN_SORTKEY_TABLE
-- * DSN_FILTER_TABLE,
-- * DSN_PGRANGE_TABLE
-- * DSN_PGROUP_TABLE
-- * DSN_PTASK_TABLE,
-- * DSN_VIEWREF_TABLE
-- * DSN_QUERY_TABLE
-- * DSN_QUERYINFO_TABLE
-- * DSN_VIRTUAL_INDEXES
-- * DSN_COLDIST_TABLE
-- * DSN_KEYTGTDIST_TABLE
-- * DSN_STAT_FEEDBACK
-- * DSN_PREDICATE_SELECTIVITY
-- * DSN_VIRTUAL_KEYTARGETS
-- (2) CREATE AN INDEX FOR OPTIMIZATION HINTS ON THE SAMPLE
-- PLAN_TABLE AND INDEXES ON THE SAMPLE DSN_STATEMENT_CACHE_TABLE
-- (3) MIGRATE THE PREVIOUS RELEASE SAMPLE EXPLAIN TABLES TO V13
-- (4) DROP THE PREVIOUS RELEASE SAMPLE TABLES AFTER THEY HAVE BEEN
-- MIGRATED TO V13
--
-- NOTES:
-- SOME TABLE SPACES CREATED BY THIS MEMBER SPECIFY NON-DEFAULT
-- BUFFER POOLS, BP8K1 AND BP16K1. VERIFY THAT THESE BUFFER POOLS
-- ARE ENABLED BEFORE YOU PROCESS THIS DDL.
--
--
-- CHANGE LOG:
-- 02/01/2012 UPDATE MIGRATION STATEMENTS 156515 D156515
-- 11/07/2012 ADD SET CURRENT SQLID DN1651_INST1 / DN1651
-- 07/30/2012 ADD DSN_STAT_FEEDBACK N11059R6_INST1 / N11059R6
-- 12/13/2012 UPDATE COPYRIGHT 164788
-- 12/05/2012 UPDATE FOR V11 165085
-- 02/04/2014 CREATE PARTITION-BY-GROWTH TABLE SPACES PI05200
-- 05/20/2015 ADD BLOCK_FETCH COL TO DSN_DETCOST_TABLE S23543
-- 10/31/2014 ADD COLUMNS 179574 N4269 S18574 PI28660
-- TO DSN_STATEMENT_CACHE_TABLE:
-- 'ACCELERATED',
-- 'STAT_ACC_ELAP', 'STAT_ACC_CPU',
-- 'STAT_ACC_ROW', 'STAT_ACC_BYTE',
-- 'STAT_ACC_1ROW', 'STAT_ACC_DB2',
-- 'STAT_ACC_EXEC', 'STAT_ACC_WAIT',
-- 'ACCEL_OFFLOAD_ELIGIBLE', AND 'ACCELERATOR_NAME'
-- 11/12/2015 UPDATE FOR DB2 12 EXPLAIN TABLES 181358
-- 11/18/2015 FIX SELECTED MIGRATION STATEMENTS 181686
-- 11/19/2015 ADD COLUMNS S25894
-- TO DSN_STATEMENT_CACHE_TABLE:
-- 'STAT_SUS_CHILDLLOCK', 'STAT_SUS_OTHERLLOCK'
-- 'STAT_SUS_PAGESETPLOCK', 'STAT_SUS_PAGEPLOCK'
-- 'STAT_SUS_OTHERPLOCK', 'STAT_SUS_PIPE'
-- 'STAT_SUS_PQSYNC'
-- 04/11/2016 ADD V12 COLUMN S27418_AE /S27418
-- TO DSN_STATEMNT_TABLE:
-- 'FUNCTION_LVL'
-- 09/12/2018 ADD COLUMNS PH00574
-- TO DSN_STATEMENT_CACHE_TABLE:
-- 'STAT_ACC_TWDP', 'STAT_ACC_NWDP'
-- 01/28/2022 UPDATE FOR DB2 13 E6837-S15746
-- 09/01/2022 ADD COLUMN PH48053
-- 'AP_PLANID'
-- AND INDEX
-- '<EXPLAIN_TB_NAME>_APID_IX'
-- TO THE FOLLOWING EXPLAIN TABLES:
-- PLAN_TABLE
-- DSN_FUNCTION_TABLE
-- DSN_PREDICAT_TABLE
-- DSN_STRUCT_TABLE
-- DSN_PGROUP_TABLE
-- DSN_PTASK_TABLE
-- DSN_FILTER_TABLE
-- DSN_DETCOST_TABLE
-- DSN_SORT_TABLE
-- DSN_SORTKEY_TABLE
-- DSN_PGRANGE_TABLE
-- DSN_VIEWREF_TABLE
-- DSN_QUERYINFO_TABLE
-- DSN_COLDIST_TABLE
-- DSN_KEYTGTDIST_TABLE
-- DSN_STAT_FEEDBACK
--
-- ADD COLUMNS
-- 'STMT_HASHID2', 'STMT_HASH2VER',
-- 'AP_PLANID', 'AP_PLANHASH',
-- 'AP_PLANHASHVER', 'AP_SERVICE_DATA',
-- 'CONNECTION_TYPE', 'CLIENT_USERID',
-- 'CLIENT_APPLNAME', 'CLIENT_WRKSTNNAME'
-- AND INDEX
-- 'DSN_STATEMNT_ML_IX1'
-- TO DSN_STATEMNT_TABLE
--
-- ADD COLUMNS
-- 'STMT_HASHID2', 'STMT_HASH2VER',
-- 'AP_PLANID', 'AP_PLANHASH',
-- 'AP_PLANHASHVER',
-- 'CONNECTION_TYPE', 'CLIENT_USERID',
-- 'CLIENT_APPLNAME', 'CLIENT_WRKSTNNAME'
-- AND INDEX
-- 'DSN_STATEMENT_CACHE_ML_IX1'
-- TO DSN_STATEMENT_CACHE_TABLE
--
-----------------------------------------------------------------------
-- SET THE CURRENT AUTHORIZATION ID TO PERFORM THE WORK
-----------------------------------------------------------------------
SET CURRENT SQLID = 'SYSADM';
--
-----------------------------------------------------------------------
--DROP THE SAMPLE DATABASE FOR THE V13 SAMPLE EXPLAIN TABLES
--IF CLEANUP IS REQUIRED.
--DROP DATABASE DSN8D!!Z;
-----------------------------------------------------------------------
--DROP THE SAMPLE TABLESPACES FOR THE V13 SAMPLE EXPLAIN TABLES
--IF CLEANUP IS REQUIRED AND DATABASE DSN8D!!Z IS NOT DROPPED
--
--IF THE PROGRAM YOU ARE RUNNING TO EXECUTE THE DROP TABLESPACE
--STATEMENTS WAS BOUND WITH AN APPLCOMPAT VALUE GREATER THAN
--OR EQUAL TO V12R1M506, DO NOT UNCOMMENT THE DROP TABLESPACE
--DSN8D!!Z.DSN8L!!* STATEMENTS, AS THE LOB TABLE SPACES ARE
--IMPLICITY DROPPED WHEN THEIR BASE TABLE SPACE IS DROPPED.
--IF YOU UNCOMMENT THESE STATEMENTS, DB2 WILL RETURN
--SQLCODE -204.
--DROP TABLESPACE DSN8D!!Z.DSN8S!!A;
--DROP TABLESPACE DSN8D!!Z.DSN8S!!B;
--DROP TABLESPACE DSN8D!!Z.DSN8S!!C;
--DROP TABLESPACE DSN8D!!Z.DSN8S!!D;
--DROP TABLESPACE DSN8D!!Z.DSN8L!!D;
--DROP TABLESPACE DSN8D!!Z.DSN8S!!E;
--DROP TABLESPACE DSN8D!!Z.DSN8S!!F;
--DROP TABLESPACE DSN8D!!Z.DSN8S!!G;
--DROP TABLESPACE DSN8D!!Z.DSN8S!!H;
--DROP TABLESPACE DSN8D!!Z.DSN8S!!K;
--DROP TABLESPACE DSN8D!!Z.DSN8S!!L;
--DROP TABLESPACE DSN8D!!Z.DSN8S!!M;
--DROP TABLESPACE DSN8D!!Z.DSN8S!!N;
--DROP TABLESPACE DSN8D!!Z.DSN8S!!O;
--DROP TABLESPACE DSN8D!!Z.DSN8S!!P;
--DROP TABLESPACE DSN8D!!Z.DSN8S!!I;
--DROP TABLESPACE DSN8D!!Z.DSN8L!!I;
--DROP TABLESPACE DSN8D!!Z.DSN8S!!Q;
--DROP TABLESPACE DSN8D!!Z.DSN8L!!Q;
--DROP TABLESPACE DSN8D!!Z.DSN8L!!R;
--DROP TABLESPACE DSN8D!!Z.DSN8S!!J;
--DROP TABLESPACE DSN8D!!Z.DSN8S!!S;
--DROP TABLESPACE DSN8D!!Z.DSN8S!!T;
--DROP TABLESPACE DSN8D!!Z.DSN8S!!U;
--DROP TABLESPACE DSN8D!!Z.DSN8S!!W;
--DROP TABLESPACE DSN8D!!Z.DSN8S!!X;
--COMMIT;
-----------------------------------------------------------------------
--CREATE THE SAMPLE DATABASE FOR THE V13 EXPLAIN TABLES
CREATE DATABASE DSN8D!!Z
STOGROUP DSN8G!!0
BUFFERPOOL BP0
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V13 SAMPLE PLAN_TABLE
CREATE TABLESPACE DSN8S!!A
IN DSN8D!!Z
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP8K0
SEGSIZE 16
CLOSE NO
MAXPARTITIONS 256
DSSIZE 4G
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V13 SAMPLE DSN_FUNCTION_TABLE
CREATE TABLESPACE DSN8S!!B
IN DSN8D!!Z
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP8K0
SEGSIZE 16
CLOSE NO
MAXPARTITIONS 256
DSSIZE 4G
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V13 SAMPLE DSN_STATEMNT_TABLE
CREATE TABLESPACE DSN8S!!C
IN DSN8D!!Z
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP8K0
SEGSIZE 16
CLOSE NO
MAXPARTITIONS 256
DSSIZE 4G
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V13 SAMPLE
--DSN_STATEMENT_CACHE_TABLE
CREATE TABLESPACE DSN8S!!D
IN DSN8D!!Z
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP8K0
SEGSIZE 16
CLOSE NO
MAXPARTITIONS 256
DSSIZE 4G
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE LOB TABLE SPACE FOR THE SAMPLE DSN_STATEMENT_CACHE_TABLE
CREATE LOB TABLESPACE DSN8L!!D
IN DSN8D!!Z
BUFFERPOOL BP32K;
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V13 SAMPLE DSN_PREDICAT_TABLE
CREATE TABLESPACE DSN8S!!E
IN DSN8D!!Z
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP8K0
SEGSIZE 16
CLOSE NO
MAXPARTITIONS 256
DSSIZE 4G
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V13 SAMPLE DSN_STRUCT_TABLE
CREATE TABLESPACE DSN8S!!F
IN DSN8D!!Z
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP8K0
SEGSIZE 16
CLOSE NO
MAXPARTITIONS 256
DSSIZE 4G
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V13 SAMPLE DSN_PGROUP_TABLE
CREATE TABLESPACE DSN8S!!G
IN DSN8D!!Z
BUFFERPOOL BP16K1
CCSID UNICODE
MAXPARTITIONS 256
DSSIZE 4G
USING STOGROUP DSN8G!!0;
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V13 SAMPLE DSN_PTASK_TABLE
CREATE TABLESPACE DSN8S!!H
IN DSN8D!!Z
BUFFERPOOL BP8K1
CCSID UNICODE
MAXPARTITIONS 256
DSSIZE 4G
USING STOGROUP DSN8G!!0;
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V13 SAMPLE DSN_FILTER_TABLE
CREATE TABLESPACE DSN8S!!K
IN DSN8D!!Z
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP8K0
SEGSIZE 16
CLOSE NO
MAXPARTITIONS 256
DSSIZE 4G
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V13 SAMPLE DSN_DETCOST_TABLE
CREATE TABLESPACE DSN8S!!L
IN DSN8D!!Z
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP8K0
SEGSIZE 16
CLOSE NO
MAXPARTITIONS 256
DSSIZE 4G
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V13 SAMPLE DSN_SORT_TABLE
CREATE TABLESPACE DSN8S!!M
IN DSN8D!!Z
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP8K0
SEGSIZE 16
CLOSE NO
MAXPARTITIONS 256
DSSIZE 4G
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V13 SAMPLE DSN_SORTKEY_TABLE
CREATE TABLESPACE DSN8S!!N
IN DSN8D!!Z
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP8K0
SEGSIZE 16
CLOSE NO
MAXPARTITIONS 256
DSSIZE 4G
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V13 SAMPLE DSN_PGRANGE_TABLE
CREATE TABLESPACE DSN8S!!O
IN DSN8D!!Z
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP8K0
SEGSIZE 16
CLOSE NO
MAXPARTITIONS 256
DSSIZE 4G
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V13 SAMPLE DSN_VIEWREF_TABLE
CREATE TABLESPACE DSN8S!!P
IN DSN8D!!Z
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP8K0
SEGSIZE 16
CLOSE NO
MAXPARTITIONS 256
DSSIZE 4G
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V13 SAMPLE DSN_QUERY_TABLE
CREATE TABLESPACE DSN8S!!I
IN DSN8D!!Z
BUFFERPOOL BP8K1
CCSID UNICODE
MAXPARTITIONS 256
DSSIZE 4G
USING STOGROUP DSN8G!!0;
-----------------------------------------------------------------------
--CREATE THE LOB TABLE SPACE FOR THE SAMPLE DSN_QUERY_TABLE
CREATE LOB TABLESPACE DSN8L!!I
IN DSN8D!!Z
BUFFERPOOL BP8K1
USING STOGROUP DSN8G!!0;
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V13 SAMPLE DSN_QUERYINFO_TABLE
CREATE TABLESPACE DSN8S!!Q
IN DSN8D!!Z
BUFFERPOOL BP0
CCSID UNICODE
USING STOGROUP DSN8G!!0
PRIQTY 30
SEGSIZE 16
MAXPARTITIONS 256
DSSIZE 4G
CLOSE NO;
-----------------------------------------------------------------------
--CREATE THE LOB TABLE SPACE FOR THE SAMPLE DSN_QUERYINFO_TABLE
CREATE LOB TABLESPACE DSN8L!!Q
IN DSN8D!!Z
BUFFERPOOL BP8K0
USING STOGROUP DSN8G!!0;
-----------------------------------------------------------------------
--CREATE THE LOB TABLE SPACE FOR THE SAMPLE DSN_QUERYINFO_TABLE
CREATE LOB TABLESPACE DSN8L!!R
IN DSN8D!!Z
BUFFERPOOL BP8K0
USING STOGROUP DSN8G!!0;
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V13 SAMPLE DSN_VIRTUAL_INDEXES
CREATE TABLESPACE DSN8S!!J
IN DSN8D!!Z
BUFFERPOOL BP0
CCSID UNICODE
MAXPARTITIONS 256
DSSIZE 4G
USING STOGROUP DSN8G!!0;
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V13 SAMPLE DSN_COLDIST_TABLE
CREATE TABLESPACE DSN8S!!S
IN DSN8D!!Z
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP8K0
SEGSIZE 16
CLOSE NO
MAXPARTITIONS 256
DSSIZE 4G
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V13 SAMPLE DSN_KEYTGTDIST_TABLE
CREATE TABLESPACE DSN8S!!T
IN DSN8D!!Z
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP8K0
SEGSIZE 16
CLOSE NO
MAXPARTITIONS 256
DSSIZE 4G
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V13 SAMPLE
--DSN_PREDICATE_SELECTIVITY
CREATE TABLESPACE DSN8S!!U
IN DSN8D!!Z
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP8K0
SEGSIZE 16
CLOSE NO
MAXPARTITIONS 256
DSSIZE 4G
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V13 SAMPLE DSN_STAT_FEEDBACK
CREATE TABLESPACE DSN8S!!W
IN DSN8D!!Z
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP8K0
SEGSIZE 16
CLOSE NO
MAXPARTITIONS 256
DSSIZE 4G
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V13 SAMPLE DSN_VIRTUAL_KEYTARGETS
CREATE TABLESPACE DSN8S!!X
IN DSN8D!!Z
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP8K0
SEGSIZE 16
CLOSE NO
MAXPARTITIONS 256
DSSIZE 4G
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE V13 SAMPLE PLAN_TABLE AND ITS INDEX
CREATE TABLE DSN8!!0.PLAN_TABLE
( "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,
"SCAN_DIRECTION" CHAR(1),
"EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT,
"PER_STMT_ID" BIGINT NOT NULL WITH DEFAULT,
"AP_PLANID" CHAR(16) FOR BIT DATA
)
IN DSN8D!!Z.DSN8S!!A
CCSID UNICODE;
CREATE INDEX DSN8!!0.PLAN_TABLE_HINT_IX
ON DSN8!!0.PLAN_TABLE
( "QUERYNO",
"APPLNAME",
"PROGNAME",
"VERSION",
"COLLID",
"OPTHINT" )
USING STOGROUP DSN8G!!0
ERASE NO
BUFFERPOOL BP0
CLOSE NO;
CREATE INDEX DSN8!!0.PLAN_TABLE_APID_IX
ON DSN8!!0.PLAN_TABLE
( "AP_PLANID"
);
-----------------------------------------------------------------------
--CREATE THE V13 SAMPLE DSN_FUNCTION_TABLE.
CREATE TABLE DSN8!!0.DSN_FUNCTION_TABLE
( "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,
"EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT,
"PER_STMT_ID" BIGINT NOT NULL WITH DEFAULT,
"AP_PLANID" CHAR(16) FOR BIT DATA
)
IN DSN8D!!Z.DSN8S!!B
CCSID UNICODE;
CREATE INDEX DSN8!!0.DSN_FUNCTION_TABLE_APID_IX
ON DSN8!!0.DSN_FUNCTION_TABLE
( "AP_PLANID"
);
-----------------------------------------------------------------------
--CREATE THE V13 SAMPLE DSN_STATEMNT_TABLE.
CREATE TABLE DSN8!!0.DSN_STATEMNT_TABLE
( "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,
"EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT,
"APCOMPARE_STATUS" CHAR(1) NOT NULL WITH DEFAULT,
"APREUSE_STATUS" CHAR(1) NOT NULL WITH DEFAULT,
"APREUSE_VERSION" VARCHAR(122) NOT NULL WITH DEFAULT,
"APREUSE_COPYID" INTEGER NOT NULL WITH DEFAULT -1,
"EXPLAIN_TYPE" CHAR(1) NOT NULL WITH DEFAULT,
"PER_STMT_ID" BIGINT NOT NULL WITH DEFAULT,
"QUERY_HASH" CHAR(16) FOR BIT DATA
NOT NULL WITH DEFAULT,
"FUNCTION_LVL" VARCHAR(10) NOT NULL WITH DEFAULT,
"STMT_HASHID2" CHAR(8) FOR BIT DATA,
"STMT_HASH2VER" INTEGER,
"AP_PLANID" CHAR(16) FOR BIT DATA,
"AP_PLANHASH" CHAR(8) FOR BIT DATA,
"AP_PLANHASHVER" INTEGER,
"AP_SERVICE_DATA" VARCHAR(512) FOR BIT DATA,
"CONNECTION_TYPE" CHAR(8),
"CLIENT_USERID" VARCHAR(128),
"CLIENT_APPLNAME" VARCHAR(255),
"CLIENT_WRKSTNNAME" VARCHAR(255)
)
IN DSN8D!!Z.DSN8S!!C
CCSID UNICODE;
CREATE INDEX DSN8!!0.DSN_STATEMNT_ML_IX1
ON DSN8!!0.DSN_STATEMNT_TABLE
( "STMT_HASHID2"
,"AP_PLANID"
);
-----------------------------------------------------------------------
--CREATE THE V13 SAMPLE DSN_STATEMENT_CACHE_TABLE AND ITS INDEXES
CREATE TABLE DSN8!!0.DSN_STATEMENT_CACHE_TABLE
( "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,
"EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT,
"ACCELERATED" CHAR(10) NOT NULL WITH DEFAULT 'NO'
CHECK("ACCELERATED"
IN('ACCEL_ONLY', 'NO',
'NEVER', 'YES')),
"STAT_ACC_ELAP" BIGINT NOT NULL WITH DEFAULT,
"STAT_ACC_CPU" BIGINT NOT NULL WITH DEFAULT,
"STAT_ACC_ROW" BIGINT NOT NULL WITH DEFAULT,
"STAT_ACC_BYTE" BIGINT NOT NULL WITH DEFAULT,
"STAT_ACC_1ROW" BIGINT NOT NULL WITH DEFAULT,
"STAT_ACC_DB2" BIGINT NOT NULL WITH DEFAULT,
"STAT_ACC_EXEC" BIGINT NOT NULL WITH DEFAULT,
"STAT_ACC_WAIT" BIGINT NOT NULL WITH DEFAULT,
"ACCEL_OFFLOAD_ELIGIBLE" CHAR(1) NOT NULL WITH DEFAULT 'N',
"ACCELERATOR_NAME" VARCHAR(128) WITH DEFAULT NULL,
"PER_STMT_ID" BIGINT NOT NULL WITH DEFAULT,
"STBLGRP" VARCHAR(128) NOT NULL WITH DEFAULT,
"QUERY_HASH" CHAR(16) FOR BIT DATA
NOT NULL WITH DEFAULT,
"QUERY_HASH_VERSION" INTEGER NOT NULL WITH DEFAULT -1,
"STABILIZED" CHAR(1) NOT NULL WITH DEFAULT,
"APPLCOMPAT" VARCHAR(10) NOT NULL WITH DEFAULT,
"CNO" BIGINT NOT NULL WITH DEFAULT,
"STAT_SUS_CHILDLLOCK" FLOAT NOT NULL WITH DEFAULT,
"STAT_SUS_OTHERLLOCK" FLOAT NOT NULL WITH DEFAULT,
"STAT_SUS_PAGESETPLOCK" FLOAT NOT NULL WITH DEFAULT,
"STAT_SUS_PAGEPLOCK" FLOAT NOT NULL WITH DEFAULT,
"STAT_SUS_OTHERPLOCK" FLOAT NOT NULL WITH DEFAULT,
"STAT_SUS_PIPE" FLOAT NOT NULL WITH DEFAULT,
"STAT_SUS_PQSYNC" FLOAT NOT NULL WITH DEFAULT,
"STAT_ACC_TWDP" BIGINT NOT NULL WITH DEFAULT,
"STAT_ACC_NWDP" BIGINT NOT NULL WITH DEFAULT,
"STMT_HASHID2" CHAR(8) FOR BIT DATA,
"STMT_HASH2VER" INTEGER,
"AP_PLANID" CHAR(16) FOR BIT DATA,
"AP_PLANHASH" CHAR(8) FOR BIT DATA,
"AP_PLANHASHVER" INTEGER,
"CONNECTION_TYPE" CHAR(8),
"CLIENT_USERID" VARCHAR(128),
"CLIENT_APPLNAME" VARCHAR(255),
"CLIENT_WRKSTNNAME" VARCHAR(255),
"STAT_ZIIP_CPU" FLOAT
)
IN DSN8D!!Z.DSN8S!!D
CCSID UNICODE;
CREATE AUX TABLE DSN8!!0.DSN_STATEMENT_CACHE_AUX
IN DSN8D!!Z.DSN8L!!D
STORES DSN8!!0.DSN_STATEMENT_CACHE_TABLE
COLUMN STMT_TEXT
PART 1;
CREATE INDEX DSN8!!0.DSN_STATEMENT_CACHE_IDX1
ON DSN8!!0.DSN_STATEMENT_CACHE_TABLE
( "STMT_ID" ASC );
CREATE INDEX DSN8!!0.DSN_STATEMENT_CACHE_IDX2
ON DSN8!!0.DSN_STATEMENT_CACHE_TABLE
( "STMT_TOKEN" ASC )
CLUSTER;
CREATE INDEX DSN8!!0.DSN_STATEMENT_CACHE_IDX3
ON DSN8!!0.DSN_STATEMENT_CACHE_TABLE
( "EXPLAIN_TS" DESC );
CREATE INDEX DSN8!!0.DSN_STATEMENT_CACHE_AUXINX
ON DSN8!!0.DSN_STATEMENT_CACHE_AUX;
CREATE INDEX DSN8!!0.DSN_STATEMENT_CACHE_ML_IX1
ON DSN8!!0.DSN_STATEMENT_CACHE_TABLE
( "STMT_HASHID2"
,"AP_PLANID"
);
-----------------------------------------------------------------------
--CREATE THE V13 SAMPLE DSN_PREDICAT_TABLE AND ITS INDEX
CREATE TABLE DSN8!!0.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
,"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
,"EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT
,"PER_STMT_ID" BIGINT NOT NULL WITH DEFAULT
,"AP_PLANID" CHAR(16) FOR BIT DATA
)
IN DSN8D!!Z.DSN8S!!E
VOLATILE
CCSID UNICODE;
CREATE INDEX DSN8!!0.DSN_PREDICAT_TABLE_IDX1
ON DSN8!!0.DSN_PREDICAT_TABLE
( "QUERYNO"
,"EXPLAIN_TIME"
);
CREATE INDEX DSN8!!0.DSN_PREDICAT_TABLE_IDX2
ON DSN8!!0.DSN_PREDICAT_TABLE
( "QUERYNO"
, "PREDNO"
);
CREATE INDEX DSN8!!0.DSN_PREDICAT_TABLE_APID_IX
ON DSN8!!0.DSN_PREDICAT_TABLE
( "AP_PLANID"
);
-----------------------------------------------------------------------
--CREATE THE V13 SAMPLE DSN_STRUCT_TABLE AND ITS INDEX
CREATE TABLE DSN8!!0.DSN_STRUCT_TABLE
( "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
,"EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT
,"PER_STMT_ID" BIGINT NOT NULL WITH DEFAULT
,"AP_PLANID" CHAR(16) FOR BIT DATA
)
IN DSN8D!!Z.DSN8S!!F
VOLATILE
CCSID UNICODE;
CREATE INDEX DSN8!!0.DSN_STRUCT_TABLE_IDX1
ON DSN8!!0.DSN_STRUCT_TABLE
( "QUERYNO"
,"EXPLAIN_TIME"
);
CREATE INDEX DSN8!!0.DSN_STRUCT_TABLE_APID_IX
ON DSN8!!0.DSN_STRUCT_TABLE
( "AP_PLANID"
);
-----------------------------------------------------------------------
--CREATE THE V13 SAMPLE DSN_PGROUP_TABLE AND ITS INDEX
CREATE TABLE DSN8!!0.DSN_PGROUP_TABLE
( "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
,"EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT
,"PER_STMT_ID" BIGINT NOT NULL WITH DEFAULT
,"AP_PLANID" CHAR(16) FOR BIT DATA
)
IN DSN8D!!Z.DSN8S!!G
VOLATILE
CCSID UNICODE;
CREATE INDEX DSN8!!0.DSN_PGROUP_TABLE_IDX1
ON DSN8!!0.DSN_PGROUP_TABLE
( "QUERYNO"
,"EXPLAIN_TIME"
);
CREATE INDEX DSN8!!0.DSN_PGROUP_TABLE_APID_IX
ON DSN8!!0.DSN_PGROUP_TABLE
( "AP_PLANID"
);
-----------------------------------------------------------------------
--CREATE THE V13 SAMPLE DSN_PTASK_TABLE AND ITS INDEX
CREATE TABLE DSN8!!0.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 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
,"EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT
,"PER_STMT_ID" BIGINT NOT NULL WITH DEFAULT
,"AP_PLANID" CHAR(16) FOR BIT DATA
)
IN DSN8D!!Z.DSN8S!!H
VOLATILE
CCSID UNICODE;
CREATE INDEX DSN8!!0.DSN_PTASK_TABLE_IDX1
ON DSN8!!0.DSN_PTASK_TABLE
( "QUERYNO"
,"EXPLAIN_TIME"
);
CREATE INDEX DSN8!!0.DSN_PTASK_TABLE_APID_IX
ON DSN8!!0.DSN_PTASK_TABLE
( "AP_PLANID"
);
-----------------------------------------------------------------------
--CREATE THE V13 SAMPLE DSN_FILTER_TABLE AND ITS INDEX
CREATE TABLE DSN8!!0.DSN_FILTER_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
,"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
,"EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT
,"PER_STMT_ID" BIGINT NOT NULL WITH DEFAULT
,"AP_PLANID" CHAR(16) FOR BIT DATA
)
IN DSN8D!!Z.DSN8S!!K
VOLATILE
CCSID UNICODE;
CREATE INDEX DSN8!!0.DSN_FILTER_TABLE_IDX1
ON DSN8!!0.DSN_FILTER_TABLE
( "QUERYNO"
,"EXPLAIN_TIME"
);
CREATE INDEX DSN8!!0.DSN_FILTER_TABLE_APID_IX
ON DSN8!!0.DSN_FILTER_TABLE
( "AP_PLANID"
);
-----------------------------------------------------------------------
--CREATE THE V13 SAMPLE DSN_DETCOST_TABLE AND ITS INDEX
CREATE TABLE DSN8!!0.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" 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
,"IXSCAN_SKIP_DUPS" CHAR(1) NOT NULL
WITH DEFAULT 'N'
,"IXSCAN_SKIP_SCREEN" CHAR(1) NOT NULL
WITH DEFAULT 'N'
,"EARLY_OUT" CHAR(1) NOT NULL
WITH DEFAULT ' '
,"EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT
,"BLOCK_FETCH" CHAR(1) NOT NULL
WITH DEFAULT 'N'
,"PER_STMT_ID" BIGINT NOT NULL WITH DEFAULT
,"AP_PLANID" CHAR(16) FOR BIT DATA
)
IN DSN8D!!Z.DSN8S!!L
VOLATILE
CCSID UNICODE;
CREATE INDEX DSN8!!0.DSN_DETCOST_TABLE_IDX1
ON DSN8!!0.DSN_DETCOST_TABLE
( "QUERYNO"
,"EXPLAIN_TIME"
);
CREATE INDEX DSN8!!0.DSN_DETCOST_TABLE_APID_IX
ON DSN8!!0.DSN_DETCOST_TABLE
( "AP_PLANID"
);
-----------------------------------------------------------------------
--CREATE THE V13 SAMPLE DSN_SORT_TABLE AND ITS INDEX
CREATE TABLE DSN8!!0.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 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
,"EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT
,"PER_STMT_ID" BIGINT NOT NULL WITH DEFAULT
,"AP_PLANID" CHAR(16) FOR BIT DATA
)
IN DSN8D!!Z.DSN8S!!M
VOLATILE
CCSID UNICODE;
CREATE INDEX DSN8!!0.DSN_SORT_TABLE_IDX1
ON DSN8!!0.DSN_SORT_TABLE
( "QUERYNO"
,"EXPLAIN_TIME"
);
CREATE INDEX DSN8!!0.DSN_SORT_TABLE_APID_IX
ON DSN8!!0.DSN_SORT_TABLE
( "AP_PLANID"
);
-----------------------------------------------------------------------
--CREATE THE V13 SAMPLE DSN_SORTKEY_TABLE AND ITS INDEX
CREATE TABLE DSN8!!0.DSN_SORTKEY_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
,"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
,"EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT
,"PER_STMT_ID" BIGINT NOT NULL WITH DEFAULT
,"AP_PLANID" CHAR(16) FOR BIT DATA
)
IN DSN8D!!Z.DSN8S!!N
VOLATILE
CCSID UNICODE;
CREATE INDEX DSN8!!0.DSN_SORTKEY_TABLE_IDX1
ON DSN8!!0.DSN_SORTKEY_TABLE
( "QUERYNO"
,"EXPLAIN_TIME"
);
CREATE INDEX DSN8!!0.DSN_SORTKEY_TABLE_APID_IX
ON DSN8!!0.DSN_SORTKEY_TABLE
( "AP_PLANID"
);
-----------------------------------------------------------------------
--CREATE THE V13 SAMPLE DSN_PGRANGE_TABLE AND ITS INDEX
CREATE TABLE DSN8!!0.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" 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
,"EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT
,"PER_STMT_ID" BIGINT NOT NULL WITH DEFAULT
,"AP_PLANID" CHAR(16) FOR BIT DATA
)
IN DSN8D!!Z.DSN8S!!O
VOLATILE
CCSID UNICODE;
CREATE INDEX DSN8!!0.DSN_PGRANGE_TABLE_IDX1
ON DSN8!!0.DSN_PGRANGE_TABLE
( "QUERYNO"
,"EXPLAIN_TIME"
);
CREATE INDEX DSN8!!0.DSN_PGRANGE_TABLE_APID_IX
ON DSN8!!0.DSN_PGRANGE_TABLE
( "AP_PLANID"
);
-----------------------------------------------------------------------
--CREATE THE V13 SAMPLE DSN_VIEWREF_TABLE AND ITS INDEX
CREATE TABLE DSN8!!0.DSN_VIEWREF_TABLE
( "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
,"EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT
,"AP_PLANID" CHAR(16) FOR BIT DATA
)
IN DSN8D!!Z.DSN8S!!P
VOLATILE
CCSID UNICODE;
CREATE INDEX DSN8!!0.DSN_VIEWREF_TABLE_IDX1
ON DSN8!!0.DSN_VIEWREF_TABLE
( "QUERYNO"
,"EXPLAIN_TIME"
);
CREATE INDEX DSN8!!0.DSN_VIEWREF_TABLE_APID_IX
ON DSN8!!0.DSN_VIEWREF_TABLE
( "AP_PLANID"
);
-----------------------------------------------------------------------
--CREATE THE V13 SAMPLE DSN_QUERY_TABLE AND ITS INDEXES
CREATE TABLE DSN8!!0.DSN_QUERY_TABLE
( "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
,"EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT
)
IN DSN8D!!Z.DSN8S!!I
VOLATILE
CCSID UNICODE;
CREATE INDEX DSN8!!0.DSN_QUERY_TABLE_IDX1
ON DSN8!!0.DSN_QUERY_TABLE
( "QUERYNO"
,"EXPLAIN_TIME"
);
CREATE INDEX DSN8!!0.DSN_QUERY_TABLE_IDX2
ON DSN8!!0.DSN_QUERY_TABLE
( "QUERYNO"
,"TYPE"
,"QUERY_STAGE"
,"EXPLAIN_TIME"
,"SEQNO"
);
CREATE UNIQUE INDEX DSN8!!0.DSN_QUERY_TABLE_IDX3
ON DSN8!!0.DSN_QUERY_TABLE
( "QUERY_ROWID"
);
-----------------------------------------------------------------------
--CREATE THE AUXILIARY TABLE FOR THE V13 SAMPLE DSN_QUERY_TABLE
--AND ITS INDEX
CREATE AUX TABLE DSN8!!0.DSN_QUERY_AUX
IN DSN8D!!Z.DSN8L!!I
STORES DSN8!!0.DSN_QUERY_TABLE
COLUMN "NODE_DATA"
PART 1;
CREATE INDEX DSN8!!0.DSN_QUERY_AUXINX
ON DSN8!!0.DSN_QUERY_AUX;
----------------------------------------------------------------------
--CREATE THE V13 SAMPLE DSN_QUERYINFO_TABLE AND ITS AUXILIARY TABLES
--AND INDEXES
CREATE TABLE DSN8!!0.DSN_QUERYINFO_TABLE
( "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
, "EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT
, "AP_PLANID" CHAR(16) FOR BIT DATA
)
IN DSN8D!!Z.DSN8S!!Q
CCSID UNICODE;
CREATE AUX TABLE DSN8!!0.DSN_QUERYINFO_AUX
IN DSN8D!!Z.DSN8L!!Q
STORES DSN8!!0.DSN_QUERYINFO_TABLE
COLUMN "QI_DATA"
PART 1;
CREATE INDEX DSN8!!0.DSN_QUERYINFO_AUXINX
ON DSN8!!0.DSN_QUERYINFO_AUX;
CREATE AUX TABLE DSN8!!0.DSN_QUERYINFO_AUX2
IN DSN8D!!Z.DSN8L!!R
STORES DSN8!!0.DSN_QUERYINFO_TABLE
COLUMN "SERVICE_INFO"
PART 1;
CREATE INDEX DSN8!!0.DSN_QUERYINFO_AUXINX2
ON DSN8!!0.DSN_QUERYINFO_AUX2;
CREATE INDEX DSN8!!0.DSN_QUERYINFO_TABLE_APID_IX
ON DSN8!!0.DSN_QUERYINFO_TABLE
( "AP_PLANID"
);
-----------------------------------------------------------------------
--CREATE THE V13 SAMPLE DSN_VIRTUAL_INDEXES TABLE AND ITS INDEX
CREATE TABLE DSN8!!0.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" 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'))
,"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("DATAREPEATFACTORF" = -1
OR "DATAREPEATFACTORF" >= 1)
,"SPARSE" CHAR(1) NOT NULL
WITH DEFAULT 'N'
CHECK("SPARSE" IN ('N','Y','X'))
)
IN DSN8D!!Z.DSN8S!!J
CCSID UNICODE;
CREATE INDEX DSN8!!0.DSN_VIRTUAL_INDEXES_IDX1
ON DSN8!!0.DSN_VIRTUAL_INDEXES
( "TBCREATOR"
,"TBNAME"
);
-----------------------------------------------------------------------
--CREATE THE V13 SAMPLE DSN_COLDIST_TABLE
CREATE TABLE DSN8!!0.DSN_COLDIST_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
,"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
,"PER_STMT_ID" BIGINT NOT NULL WITH DEFAULT
,"AP_PLANID" CHAR(16) FOR BIT DATA
)
IN DSN8D!!Z.DSN8S!!S
CCSID UNICODE;
CREATE INDEX DSN8!!0.DSN_COLDIST_TABLE_APID_IX
ON DSN8!!0.DSN_COLDIST_TABLE
( "AP_PLANID"
);
-----------------------------------------------------------------------
--CREATE THE V13 SAMPLE DSN_KEYTGTDIST_TABLE
CREATE TABLE DSN8!!0.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) 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
,"EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT
,"PER_STMT_ID" BIGINT NOT NULL WITH DEFAULT
,"AP_PLANID" CHAR(16) FOR BIT DATA
)
IN DSN8D!!Z.DSN8S!!T
CCSID UNICODE;
CREATE INDEX DSN8!!0.DSN_KEYTGTDIST_TABLE_APID_IX
ON DSN8!!0.DSN_KEYTGTDIST_TABLE
( "AP_PLANID"
);
-----------------------------------------------------------------------
--CREATE THE V13 SAMPLE DSN_PREDICATE_SELECTIVITY TABLE
CREATE TABLE DSN8!!0.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
, "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
, "PREDNO" INTEGER NOT NULL
, "INSTANCE" SMALLINT NOT NULL
, "SELECTIVITY" FLOAT NOT NULL
, "WEIGHT" FLOAT(4) NOT NULL
, "ASSUMPTION" VARCHAR(128) NOT NULL
, "INSERT_TIME" TIMESTAMP NOT NULL
GENERATED ALWAYS
FOR EACH ROW ON UPDATE
AS ROW CHANGE TIMESTAMP
, "EXPLAIN_TIME" TIMESTAMP
, "REMARKS" VARCHAR(762)
, "EXPANSION_REASON" CHAR(2) NOT NULL WITH DEFAULT
, "PER_STMT_ID" BIGINT NOT NULL WITH DEFAULT
)
IN DSN8D!!Z.DSN8S!!U
CCSID UNICODE;
CREATE INDEX DSN8!!0.DSN_PREDSEL_IX1
ON DSN8!!0.DSN_PREDICATE_SELECTIVITY
( "QUERYNO"
);
-----------------------------------------------------------------------
--CREATE THE V13 SAMPLE DSN_STAT_FEEDBACK TABLE
CREATE TABLE DSN8!!0.DSN_STAT_FEEDBACK
( "QUERYNO" INTEGER NOT NULL
, "APPLNAME" VARCHAR(24) NOT NULL
, "PROGNAME" VARCHAR(128) NOT NULL
, "COLLID" VARCHAR(128) NOT NULL
, "GROUP_MEMBER" VARCHAR(24) NOT NULL
, "EXPLAIN_TIME" TIMESTAMP NOT NULL
, "SECTNOI" INTEGER NOT NULL 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) NOT NULL
, "REASON" CHAR(8) NOT NULL
, "REMARKS" VARCHAR(254) NOT NULL
, "AP_PLANID" CHAR(16) FOR BIT DATA
)
IN DSN8D!!Z.DSN8S!!W
CCSID UNICODE;
CREATE INDEX DSN8!!0.DSN_STAT_FEEDBACK_APID_IX
ON DSN8!!0.DSN_STAT_FEEDBACK
( "AP_PLANID"
);
-----------------------------------------------------------------------
--CREATE THE V13 SAMPLE DSN_VIRTUAL_KEYTARGETS TABLE
CREATE TABLE DSN8!!0.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 DEFAULT
CHECK("COLNO" >= 0)
, "ORDERING" CHAR(1) NOT NULL
CHECK("ORDERING" IN ('A'))
, "TYPESCHEMA" VARCHAR(128) NOT NULL
, "TYPENAME" VARCHAR(128) NOT NULL
, "LENGTH" SMALLINT NOT NULL
CHECK("LENGTH" > 0)
, "LENGTH2" INTEGER NOT NULL
CHECK("LENGTH2" >= 0)
, "SCALE" SMALLINT NOT NULL WITH DEFAULT
CHECK("SCALE" >= 0)
, "NULLS" CHAR(1) NOT NULL
WITH DEFAULT 'N'
CHECK("NULLS" IN ('Y','N'))
, "CCSID" INTEGER NOT NULL
CHECK("CCSID" >= 0)
, "SUBTYPE" CHAR(1) NOT NULL
CHECK("SUBTYPE"
IN ('B','M','S',' '))
, "DERIVED_FROM" VARCHAR(4000) NOT NULL
, "CARDF" FLOAT NOT NULL
WITH DEFAULT -1
CHECK("CARDF" >= 0
OR "CARDF" = -1
OR "CARDF" = -2)
)
IN DSN8D!!Z.DSN8S!!X
CCSID UNICODE;
-----------------------------------------------------------------------
--MIGRATE A V12 (68-COLUMN) PLAN_TABLE TO V13
INSERT INTO DSN8!!0.PLAN_TABLE
( "QUERYNO","QBLOCKNO","APPLNAME","PROGNAME","PLANNO",
"METHOD","CREATOR","TNAME","TABNO","ACCESSTYPE",
"MATCHCOLS","ACCESSCREATOR","ACCESSNAME","INDEXONLY",
"SORTN_UNIQ","SORTN_JOIN","SORTN_ORDERBY","SORTN_GROUPBY",
"SORTC_UNIQ","SORTC_JOIN","SORTC_ORDERBY","SORTC_GROUPBY",
"TSLOCKMODE","TIMESTAMP","REMARKS","PREFETCH",
"COLUMN_FN_EVAL","MIXOPSEQ","VERSION","COLLID"
,"ACCESS_DEGREE","ACCESS_PGROUP_ID","JOIN_DEGREE",
"JOIN_PGROUP_ID"
,"SORTC_PGROUP_ID","SORTN_PGROUP_ID","PARALLELISM_MODE",
"MERGE_JOIN_COLS","CORRELATION_NAME","PAGE_RANGE",
"JOIN_TYPE","GROUP_MEMBER","IBM_SERVICE_DATA"
,"WHEN_OPTIMIZE","QBLOCK_TYPE","BIND_TIME"
,"OPTHINT","HINT_USED","PRIMARY_ACCESSTYPE"
,"PARENT_QBLOCKNO","TABLE_TYPE"
,"TABLE_ENCODE","TABLE_SCCSID","TABLE_MCCSID"
,"TABLE_DCCSID","ROUTINE_ID","CTEREF","STMTTOKEN"
,"PARENT_PLANNO"
,"BIND_EXPLAIN_ONLY"
,"SECTNOI"
,"EXPLAIN_TIME"
,"MERGC"
,"MERGN"
,"SCAN_DIRECTION"
,"EXPANSION_REASON"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 67-COLUMN TABLE
,"AP_PLANID"
)
SELECT "QUERYNO","QBLOCKNO","APPLNAME","PROGNAME","PLANNO",
"METHOD","CREATOR","TNAME","TABNO","ACCESSTYPE",
"MATCHCOLS","ACCESSCREATOR","ACCESSNAME","INDEXONLY",
"SORTN_UNIQ","SORTN_JOIN","SORTN_ORDERBY","SORTN_GROUPBY",
"SORTC_UNIQ","SORTC_JOIN","SORTC_ORDERBY","SORTC_GROUPBY",
"TSLOCKMODE","TIMESTAMP","REMARKS","PREFETCH",
"COLUMN_FN_EVAL","MIXOPSEQ","VERSION","COLLID"
,"ACCESS_DEGREE","ACCESS_PGROUP_ID","JOIN_DEGREE",
"JOIN_PGROUP_ID"
,"SORTC_PGROUP_ID","SORTN_PGROUP_ID","PARALLELISM_MODE",
"MERGE_JOIN_COLS","CORRELATION_NAME","PAGE_RANGE",
"JOIN_TYPE","GROUP_MEMBER","IBM_SERVICE_DATA"
,"WHEN_OPTIMIZE","QBLOCK_TYPE","BIND_TIME"
,"OPTHINT","HINT_USED","PRIMARY_ACCESSTYPE"
,"PARENT_QBLOCKNO","TABLE_TYPE"
,"TABLE_ENCODE","TABLE_SCCSID","TABLE_MCCSID"
,"TABLE_DCCSID","ROUTINE_ID","CTEREF","STMTTOKEN"
,"PARENT_PLANNO"
,"BIND_EXPLAIN_ONLY"
,"SECTNOI"
,"EXPLAIN_TIME"
,"MERGC"
,"MERGN"
,"SCAN_DIRECTION"
,"EXPANSION_REASON"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 67-COLUMN TABLE
,"AP_PLANID"
FROM DSN8@@0.PLAN_TABLE;
-----------------------------------------------------------------------
--MIGRATE A V12 (22-COLUMN) DSN_FUNCTION_TABLE TO V13
INSERT INTO DSN8!!0.DSN_FUNCTION_TABLE
( "QUERYNO"
,"QBLOCKNO"
,"APPLNAME"
,"PROGNAME"
,"COLLID"
,"GROUP_MEMBER"
,"EXPLAIN_TIME"
,"SCHEMA_NAME"
,"FUNCTION_NAME"
,"SPEC_FUNC_NAME"
,"FUNCTION_TYPE"
,"VIEW_CREATOR"
,"VIEW_NAME"
,"PATH"
,"FUNCTION_TEXT"
,"FUNC_VERSION"
,"SECURE"
,"SECTNOI"
,"VERSION"
,"EXPANSION_REASON"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 21-COLUMN TABLE
,"AP_PLANID"
)
SELECT "QUERYNO"
,"QBLOCKNO"
,"APPLNAME"
,"PROGNAME"
,"COLLID"
,"GROUP_MEMBER"
,"EXPLAIN_TIME"
,"SCHEMA_NAME"
,"FUNCTION_NAME"
,"SPEC_FUNC_NAME"
,"FUNCTION_TYPE"
,"VIEW_CREATOR"
,"VIEW_NAME"
,"PATH"
,"FUNCTION_TEXT"
,"FUNC_VERSION"
,"SECURE"
,"SECTNOI"
,"VERSION"
,"EXPANSION_REASON"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 21-COLUMN TABLE
,"AP_PLANID"
FROM DSN8@@0.DSN_FUNCTION_TABLE;
-----------------------------------------------------------------------
--MIGRATE A V12 (34-COLUMN) DSN_STATEMNT_TABLE TO V13
INSERT INTO DSN8!!0.DSN_STATEMNT_TABLE
( "QUERYNO"
,"APPLNAME"
,"PROGNAME"
,"COLLID"
,"GROUP_MEMBER"
,"EXPLAIN_TIME"
,"STMT_TYPE"
,"COST_CATEGORY"
,"PROCMS"
,"PROCSU"
,"REASON"
,"STMT_ENCODE"
,"TOTAL_COST"
,"SECTNOI"
,"VERSION"
,"EXPANSION_REASON"
,"APCOMPARE_STATUS"
,"APREUSE_STATUS"
,"APREUSE_VERSION"
,"APREUSE_COPYID"
,"EXPLAIN_TYPE"
,"PER_STMT_ID"
,"QUERY_HASH"
,"FUNCTION_LVL"
--DELETE THE NEXT 10 LINES IF MIGRATING A 24-COLUMN TABLE
,"STMT_HASHID2"
,"STMT_HASH2VER"
,"AP_PLANID"
,"AP_PLANHASH"
,"AP_PLANHASHVER"
,"AP_SERVICE_DATA"
,"CONNECTION_TYPE"
,"CLIENT_USERID"
,"CLIENT_APPLNAME"
,"CLIENT_WRKSTNNAME"
)
SELECT "QUERYNO"
,"APPLNAME"
,"PROGNAME"
,"COLLID"
,"GROUP_MEMBER"
,"EXPLAIN_TIME"
,"STMT_TYPE"
,"COST_CATEGORY"
,"PROCMS"
,"PROCSU"
,"REASON"
,"STMT_ENCODE"
,"TOTAL_COST"
,"SECTNOI"
,"VERSION"
,"EXPANSION_REASON"
,"APCOMPARE_STATUS"
,"APREUSE_STATUS"
,"APREUSE_VERSION"
,"APREUSE_COPYID"
,"EXPLAIN_TYPE"
,"PER_STMT_ID"
,"QUERY_HASH"
,"FUNCTION_LVL"
--DELETE THE NEXT 10 LINES IF MIGRATING A 24-COLUMN TABLE
,"STMT_HASHID2"
,"STMT_HASH2VER"
,"AP_PLANID"
,"AP_PLANHASH"
,"AP_PLANHASHVER"
,"AP_SERVICE_DATA"
,"CONNECTION_TYPE"
,"CLIENT_USERID"
,"CLIENT_APPLNAME"
,"CLIENT_WRKSTNNAME"
FROM DSN8@@0.DSN_STATEMNT_TABLE;
-----------------------------------------------------------------------
--MIGRATE A V12 (104-COLUMN) DSN_STATEMENT_CACHE_TABLE TO V13
INSERT INTO DSN8!!0.DSN_STATEMENT_CACHE_TABLE
( "STMT_ID"
,"STMT_TOKEN"
,"COLLID"
,"PROGRAM_NAME"
,"INV_DROPALT"
,"INV_REVOKE"
,"INV_LRU"
,"INV_RUNSTATS"
,"CACHED_TS"
,"USERS"
,"COPIES"
,"LINES"
,"PRIMAUTH"
,"CURSQLID"
,"BIND_QUALIFIER"
,"BIND_ISO"
,"BIND_CDATA"
,"BIND_DYNRL"
,"BIND_DEGRE"
,"BIND_SQLRL"
,"BIND_CHOLD"
,"STAT_TS"
,"STAT_EXEC"
,"STAT_GPAG"
,"STAT_SYNR"
,"STAT_WRIT"
,"STAT_EROW"
,"STAT_PROW"
,"STAT_SORT"
,"STAT_INDX"
,"STAT_RSCN"
,"STAT_PGRP"
,"STAT_ELAP"
,"STAT_CPU"
,"STAT_SUS_SYNIO"
,"STAT_SUS_LOCK"
,"STAT_SUS_SWIT"
,"STAT_SUS_GLCK"
,"STAT_SUS_OTHR"
,"STAT_SUS_OTHW"
,"STAT_RIDLIMT"
,"STAT_RIDSTOR"
,"EXPLAIN_TS"
,"SCHEMA"
,"STMT_TEXT"
,"BIND_RO_TYPE"
,"BIND_RA_TOT"
,"GROUP_MEMBER"
,"STAT_EXECB"
,"STAT_GPAGB"
,"STAT_SYNRB"
,"STAT_WRITB"
,"STAT_EROWB"
,"STAT_PROWB"
,"STAT_SORTB"
,"STAT_INDXB"
,"STAT_RSCNB"
,"STAT_PGRPB"
,"STAT_RIDLIMTB"
,"STAT_RIDSTORB"
,"LITERAL_REPL"
,"STAT_SUS_LATCH"
,"STAT_SUS_PLATCH"
,"STAT_SUS_DRAIN"
,"STAT_SUS_CLAIM"
,"STAT_SUS_LOG"
,"EXPANSION_REASON"
,"ACCELERATED"
,"STAT_ACC_ELAP"
,"STAT_ACC_CPU"
,"STAT_ACC_ROW"
,"STAT_ACC_BYTE"
,"STAT_ACC_1ROW"
,"STAT_ACC_DB2"
,"STAT_ACC_EXEC"
,"STAT_ACC_WAIT"
,"ACCEL_OFFLOAD_ELIGIBLE"
,"ACCELERATOR_NAME"
,"PER_STMT_ID"
,"STBLGRP"
,"QUERY_HASH"
,"QUERY_HASH_VERSION"
,"STABILIZED"
,"APPLCOMPAT"
,"CNO"
,"STAT_SUS_CHILDLLOCK"
,"STAT_SUS_OTHERLLOCK"
,"STAT_SUS_PAGESETPLOCK"
,"STAT_SUS_PAGEPLOCK"
,"STAT_SUS_OTHERPLOCK"
,"STAT_SUS_PIPE"
,"STAT_SUS_PQSYNC"
--DELETE THE NEXT 2 LINES IF MIGRATING A 93-COLUMN TABLE
,"STAT_ACC_TWDP"
,"STAT_ACC_NWDP"
--DELETE THE NEXT 9 LINES IF MIGRATING A 93-COLUMN OR 95-COLUMN TABLE
,"STMT_HASHID2"
,"STMT_HASH2VER"
,"AP_PLANID"
,"AP_PLANHASH"
,"AP_PLANHASHVER"
,"CONNECTION_TYPE"
,"CLIENT_USERID"
,"CLIENT_APPLNAME"
,"CLIENT_WRKSTNNAME"
)
SELECT "STMT_ID"
,"STMT_TOKEN"
,"COLLID"
,"PROGRAM_NAME"
,"INV_DROPALT"
,"INV_REVOKE"
,"INV_LRU"
,"INV_RUNSTATS"
,"CACHED_TS"
,"USERS"
,"COPIES"
,"LINES"
,"PRIMAUTH"
,"CURSQLID"
,"BIND_QUALIFIER"
,"BIND_ISO"
,"BIND_CDATA"
,"BIND_DYNRL"
,"BIND_DEGRE"
,"BIND_SQLRL"
,"BIND_CHOLD"
,"STAT_TS"
,"STAT_EXEC"
,"STAT_GPAG"
,"STAT_SYNR"
,"STAT_WRIT"
,"STAT_EROW"
,"STAT_PROW"
,"STAT_SORT"
,"STAT_INDX"
,"STAT_RSCN"
,"STAT_PGRP"
,"STAT_ELAP"
,"STAT_CPU"
,"STAT_SUS_SYNIO"
,"STAT_SUS_LOCK"
,"STAT_SUS_SWIT"
,"STAT_SUS_GLCK"
,"STAT_SUS_OTHR"
,"STAT_SUS_OTHW"
,"STAT_RIDLIMT"
,"STAT_RIDSTOR"
,"EXPLAIN_TS"
,"SCHEMA"
,"STMT_TEXT"
,"BIND_RO_TYPE"
,"BIND_RA_TOT"
,"GROUP_MEMBER"
,"STAT_EXECB"
,"STAT_GPAGB"
,"STAT_SYNRB"
,"STAT_WRITB"
,"STAT_EROWB"
,"STAT_PROWB"
,"STAT_SORTB"
,"STAT_INDXB"
,"STAT_RSCNB"
,"STAT_PGRPB"
,"STAT_RIDLIMTB"
,"STAT_RIDSTORB"
,"LITERAL_REPL"
,"STAT_SUS_LATCH"
,"STAT_SUS_PLATCH"
,"STAT_SUS_DRAIN"
,"STAT_SUS_CLAIM"
,"STAT_SUS_LOG"
,"EXPANSION_REASON"
,"ACCELERATED"
,"STAT_ACC_ELAP"
,"STAT_ACC_CPU"
,"STAT_ACC_ROW"
,"STAT_ACC_BYTE"
,"STAT_ACC_1ROW"
,"STAT_ACC_DB2"
,"STAT_ACC_EXEC"
,"STAT_ACC_WAIT"
,"ACCEL_OFFLOAD_ELIGIBLE"
,"ACCELERATOR_NAME"
,"PER_STMT_ID"
,"STBLGRP"
,"QUERY_HASH"
,"QUERY_HASH_VERSION"
,"STABILIZED"
,"APPLCOMPAT"
,"CNO"
,"STAT_SUS_CHILDLLOCK"
,"STAT_SUS_OTHERLLOCK"
,"STAT_SUS_PAGESETPLOCK"
,"STAT_SUS_PAGEPLOCK"
,"STAT_SUS_OTHERPLOCK"
,"STAT_SUS_PIPE"
,"STAT_SUS_PQSYNC"
--DELETE THE NEXT 2 LINES IF MIGRATING A 93-COLUMN TABLE
,"STAT_ACC_TWDP"
,"STAT_ACC_NWDP"
--DELETE THE NEXT 9 LINES IF MIGRATING A 93-COLUMN OR 95-COLUMN TABLE
,"STMT_HASHID2"
,"STMT_HASH2VER"
,"AP_PLANID"
,"AP_PLANHASH"
,"AP_PLANHASHVER"
,"CONNECTION_TYPE"
,"CLIENT_USERID"
,"CLIENT_APPLNAME"
,"CLIENT_WRKSTNNAME"
FROM DSN8@@0.DSN_STATEMENT_CACHE_TABLE;
-----------------------------------------------------------------------
--MIGRATE A V12 (44-COLUMN) DSN_PREDICAT_TABLE TO V13
INSERT INTO DSN8!!0.DSN_PREDICAT_TABLE
( "QUERYNO"
,"QBLOCKNO"
,"APPLNAME"
,"PROGNAME"
,"PREDNO"
,"TYPE"
,"LEFT_HAND_SIDE"
,"LEFT_HAND_PNO"
,"LHS_TABNO"
,"LHS_QBNO"
,"RIGHT_HAND_SIDE"
,"RIGHT_HAND_PNO"
,"RHS_TABNO"
,"RHS_QBNO"
,"FILTER_FACTOR"
,"BOOLEAN_TERM"
,"SEARCHARG"
,"JOIN"
,"AFTER_JOIN"
,"ADDED_PRED"
,"REDUNDANT_PRED"
,"DIRECT_ACCESS"
,"KEYFIELD"
,"EXPLAIN_TIME"
,"CATEGORY"
,"CATEGORY_B"
,"TEXT"
,"PRED_ENCODE"
,"PRED_CCSID"
,"PRED_MCCSID"
,"MARKER"
,"PARENT_PNO"
,"NEGATION"
,"LITERALS"
,"CLAUSE"
,"GROUP_MEMBER"
,"ORIGIN"
,"UNCERTAINTY"
,"SECTNOI"
,"COLLID"
,"VERSION"
,"EXPANSION_REASON"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 43-COLUMN TABLE
,"AP_PLANID"
)
SELECT "QUERYNO"
,"QBLOCKNO"
,"APPLNAME"
,"PROGNAME"
,"PREDNO"
,"TYPE"
,"LEFT_HAND_SIDE"
,"LEFT_HAND_PNO"
,"LHS_TABNO"
,"LHS_QBNO"
,"RIGHT_HAND_SIDE"
,"RIGHT_HAND_PNO"
,"RHS_TABNO"
,"RHS_QBNO"
,"FILTER_FACTOR"
,"BOOLEAN_TERM"
,"SEARCHARG"
,"JOIN"
,"AFTER_JOIN"
,"ADDED_PRED"
,"REDUNDANT_PRED"
,"DIRECT_ACCESS"
,"KEYFIELD"
,"EXPLAIN_TIME"
,"CATEGORY"
,"CATEGORY_B"
,"TEXT"
,"PRED_ENCODE"
,"PRED_CCSID"
,"PRED_MCCSID"
,"MARKER"
,"PARENT_PNO"
,"NEGATION"
,"LITERALS"
,"CLAUSE"
,"GROUP_MEMBER"
,"ORIGIN"
,"UNCERTAINTY"
,"SECTNOI"
,"COLLID"
,"VERSION"
,"EXPANSION_REASON"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 43-COLUMN TABLE
,"AP_PLANID"
FROM DSN8@@0.DSN_PREDICAT_TABLE;
-----------------------------------------------------------------------
--MIGRATE A V12 (22-COLUMN) DSN_STRUCT_TABLE TO V13
INSERT INTO DSN8!!0.DSN_STRUCT_TABLE
( "QUERYNO"
,"QBLOCKNO"
,"APPLNAME"
,"PROGNAME"
,"PARENT"
,"TIMES"
,"ROWCOUNT"
,"ATOPEN"
,"CONTEXT"
,"ORDERNO"
,"DOATOPEN_PARENT"
,"QBLOCK_TYPE"
,"EXPLAIN_TIME"
,"QUERY_STAGE"
,"GROUP_MEMBER"
,"ORIGIN"
,"SECTNOI"
,"COLLID"
,"VERSION"
,"EXPANSION_REASON"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 21-COLUMN TABLE
,"AP_PLANID"
)
SELECT "QUERYNO"
,"QBLOCKNO"
,"APPLNAME"
,"PROGNAME"
,"PARENT"
,"TIMES"
,"ROWCOUNT"
,"ATOPEN"
,"CONTEXT"
,"ORDERNO"
,"DOATOPEN_PARENT"
,"QBLOCK_TYPE"
,"EXPLAIN_TIME"
,"QUERY_STAGE"
,"GROUP_MEMBER"
,"ORIGIN"
,"SECTNOI"
,"COLLID"
,"VERSION"
,"EXPANSION_REASON"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 21-COLUMN TABLE
,"AP_PLANID"
FROM DSN8@@0.DSN_STRUCT_TABLE;
-----------------------------------------------------------------------
--MIGRATE A V12 (44-COLUMN) DSN_PGROUP_TABLE TO V13
INSERT INTO DSN8!!0.DSN_PGROUP_TABLE
( "QUERYNO"
,"QBLOCKNO"
,"PLANNAME"
,"COLLID"
,"PROGNAME"
,"EXPLAIN_TIME"
,"VERSION"
,"GROUPID"
,"FIRSTPLAN"
,"LASTPLAN"
,"CPUCOST"
,"IOCOST"
,"BESTTIME"
,"DEGREE"
,"MODE"
,"REASON"
,"LOCALCPU"
,"TOTALCPU"
,"FIRSTBASE"
,"LARGETS"
,"PARTKIND"
,"GROUPTYPE"
,"ORDER"
,"STYLE"
,"RANGEKIND"
,"NKEYCOLS"
,"LOWBOUND"
,"HIGHBOUND"
,"LOWKEY"
,"HIGHKEY"
,"FIRSTPAGE"
,"LASTPAGE"
,"GROUP_MEMBER"
,"HOST_REASON"
,"PARA_TYPE"
,"PART_INNER"
,"GRNU_KEYRNG"
,"OPEN_KEYRNG"
,"APPLNAME"
,"SECTNOI"
,"STRAW_MODEL"
,"EXPANSION_REASON"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 43-COLUMN TABLE
,"AP_PLANID"
)
SELECT "QUERYNO"
,"QBLOCKNO"
,"PLANNAME"
,"COLLID"
,"PROGNAME"
,"EXPLAIN_TIME"
,"VERSION"
,"GROUPID"
,"FIRSTPLAN"
,"LASTPLAN"
,"CPUCOST"
,"IOCOST"
,"BESTTIME"
,"DEGREE"
,"MODE"
,"REASON"
,"LOCALCPU"
,"TOTALCPU"
,"FIRSTBASE"
,"LARGETS"
,"PARTKIND"
,"GROUPTYPE"
,"ORDER"
,"STYLE"
,"RANGEKIND"
,"NKEYCOLS"
,"LOWBOUND"
,"HIGHBOUND"
,"LOWKEY"
,"HIGHKEY"
,"FIRSTPAGE"
,"LASTPAGE"
,"GROUP_MEMBER"
,"HOST_REASON"
,"PARA_TYPE"
,"PART_INNER"
,"GRNU_KEYRNG"
,"OPEN_KEYRNG"
,"APPLNAME"
,"SECTNOI"
,"STRAW_MODEL"
,"EXPANSION_REASON"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 43-COLUMN TABLE
,"AP_PLANID"
FROM DSN8@@0.DSN_PGROUP_TABLE;
-----------------------------------------------------------------------
--MIGRATE A V12 (27-COLUMN) DSN_PTASK_TABLE TO V13
INSERT INTO DSN8!!0.DSN_PTASK_TABLE
( "QUERYNO"
,"QBLOCKNO"
,"PGDNO"
,"APPLNAME"
,"PROGNAME"
,"LPTNO"
,"KEYCOLID"
,"DPSI"
,"LPTLOKEY"
,"LPTHIKEY"
,"LPTLOPAG"
,"LPTHIPAG"
,"LPTLOPG"
,"LPTHIPG"
,"LPTLOPT"
,"LPTHIPT"
,"KEYCOLDT"
,"KEYCOLPREC"
,"KEYCOLSCAL"
,"EXPLAIN_TIME"
,"GROUP_MEMBER"
,"SECTNOI"
,"COLLID"
,"VERSION"
,"EXPANSION_REASON"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 26-COLUMN TABLE
,"AP_PLANID"
)
SELECT "QUERYNO"
,"QBLOCKNO"
,"PGDNO"
,"APPLNAME"
,"PROGNAME"
,"LPTNO"
,"KEYCOLID"
,"DPSI"
,"LPTLOKEY"
,"LPTHIKEY"
,"LPTLOPAG"
,"LPTHIPAG"
,"LPTLOPG"
,"LPTHIPG"
,"LPTLOPT"
,"LPTHIPT"
,"KEYCOLDT"
,"KEYCOLPREC"
,"KEYCOLSCAL"
,"EXPLAIN_TIME"
,"GROUP_MEMBER"
,"SECTNOI"
,"COLLID"
,"VERSION"
,"EXPANSION_REASON"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 26-COLUMN TABLE
,"AP_PLANID"
FROM DSN8@@0.DSN_PTASK_TABLE;
-----------------------------------------------------------------------
--MIGRATE A V12 (20-COLUMN) DSN_FILTER_TABLE TO V13
INSERT INTO DSN8!!0.DSN_FILTER_TABLE
( "QUERYNO"
,"QBLOCKNO"
,"PLANNO"
,"APPLNAME"
,"PROGNAME"
,"COLLID"
,"ORDERNO"
,"PREDNO"
,"STAGE"
,"ORDERCLASS"
,"EXPLAIN_TIME"
,"MIXOPSEQNO"
,"REEVAL"
,"GROUP_MEMBER"
,"SECTNOI"
,"VERSION"
,"PUSHDOWN"
,"EXPANSION_REASON"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 19-COLUMN TABLE
,"AP_PLANID"
)
SELECT "QUERYNO"
,"QBLOCKNO"
,"PLANNO"
,"APPLNAME"
,"PROGNAME"
,"COLLID"
,"ORDERNO"
,"PREDNO"
,"STAGE"
,"ORDERCLASS"
,"EXPLAIN_TIME"
,"MIXOPSEQNO"
,"REEVAL"
,"GROUP_MEMBER"
,"SECTNOI"
,"VERSION"
,"PUSHDOWN"
,"EXPANSION_REASON"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 19-COLUMN TABLE
,"AP_PLANID"
FROM DSN8@@0.DSN_FILTER_TABLE;
-----------------------------------------------------------------------
--MIGRATE A V12 (139-COLUMN) DSN_DETCOST_TABLE TO V13
INSERT INTO DSN8!!0.DSN_DETCOST_TABLE
( "QUERYNO"
,"QBLOCKNO"
,"APPLNAME"
,"PROGNAME"
,"PLANNO"
,"OPENIO"
,"OPENCPU"
,"OPENCOST"
,"DMIO"
,"DMCPU"
,"DMTOT"
,"SUBQIO"
,"SUBQCPU"
,"SUBQCOST"
,"BASEIO"
,"BASECPU"
,"BASETOT"
,"ONECOMPROWS"
,"IMLEAF"
,"IMIO"
,"IMPREFH"
,"IMMPRED"
,"IMFF"
,"IMSRPRED"
,"IMFFADJ"
,"IMSCANCST"
,"IMROWCST"
,"IMPAGECST"
,"IMRIDSORT"
,"IMMERGCST"
,"IMCPU"
,"IMTOT"
,"IMSEQNO"
,"DMPREFH"
,"DMCLUDIO"
,"DMNCLUDIO"
,"DMPREDS"
,"DMSROWS"
,"DMSCANCST"
,"DMCOLS"
,"DMROWS"
,"RDSROWCST"
,"DMPAGECST"
,"DMDATAIO"
,"DMDATACPU"
,"DMDATATOT"
,"RDSROW"
,"SNCOLS"
,"SNROWS"
,"SNRECSZ"
,"SNPAGES"
,"SNRUNS"
,"SNMERGES"
,"SNIOCOST"
,"SNCPUCOST"
,"SNCOST"
,"SNSCANIO"
,"SNSCANCPU"
,"SNSCANCOST"
,"SCCOLS"
,"SCROWS"
,"SCRECSZ"
,"SCPAGES"
,"SCRUNS"
,"SCMERGES"
,"SCIOCOST"
,"SCCPUCOST"
,"SCCOST"
,"SCSCANIO"
,"SCSCANCPU"
,"SCSCANCOST"
,"COMPCARD"
,"COMPIOCOST"
,"COMPCPUCOST"
,"COMPCOST"
,"JOINCOLS"
,"EXPLAIN_TIME"
,"COSTBLK"
,"COSTSTOR"
,"MPBLK"
,"MPSTOR"
,"COMPOSITES"
,"CLIPPED"
,"PARTITION"
,"TABREF"
,"MAX_COMPOSITES"
,"MAX_STOR"
,"MAX_CPU"
,"MAX_ELAP"
,"TBL_JOINED_THRESH"
,"STOR_USED"
,"CPU_USED"
,"ELAPSED"
,"MIN_CARD_KEEP"
,"MAX_CARD_KEEP"
,"MIN_COST_KEEP"
,"MAX_COST_KEEP"
,"MIN_VALUE_KEEP"
,"MIN_VALUE_CARD_KEEP"
,"MIN_VALUE_COST_KEEP"
,"MAX_VALUE_KEEP"
,"MAX_VALUE_CARD_KEEP"
,"MAX_VALUE_COST_KEEP"
,"MIN_CARD_CLIP"
,"MAX_CARD_CLIP"
,"MIN_COST_CLIP"
,"MAX_COST_CLIP"
,"MIN_VALUE_CLIP"
,"MIN_VALUE_CARD_CLIP"
,"MIN_VALUE_COST_CLIP"
,"MAX_VALUE_CLIP"
,"MAX_VALUE_CARD_CLIP"
,"MAX_VALUE_COST_CLIP"
,"GROUP_MEMBER"
,"PSEQIOCOST"
,"PSEQCPUCOST"
,"PSEQCOST"
,"PADJIOCOST"
,"PADJCPUCOST"
,"PADJCOST"
,"UNCERTAINTY"
,"UNCERTAINTY_1T"
,"SECTNOI"
,"COLLID"
,"VERSION"
,"IMNP"
,"DMNP"
,"IMJC"
,"IMFC"
,"IMJBC"
,"IMJFC"
,"CRED"
,"IXSCAN_SKIP_DUPS"
,"IXSCAN_SKIP_SCREEN"
,"EARLY_OUT"
,"EXPANSION_REASON"
,"BLOCK_FETCH"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 138-COLUMN TABLE
,"AP_PLANID"
)
SELECT "QUERYNO"
,"QBLOCKNO"
,"APPLNAME"
,"PROGNAME"
,"PLANNO"
,"OPENIO"
,"OPENCPU"
,"OPENCOST"
,"DMIO"
,"DMCPU"
,"DMTOT"
,"SUBQIO"
,"SUBQCPU"
,"SUBQCOST"
,"BASEIO"
,"BASECPU"
,"BASETOT"
,"ONECOMPROWS"
,"IMLEAF"
,"IMIO"
,"IMPREFH"
,"IMMPRED"
,"IMFF"
,"IMSRPRED"
,"IMFFADJ"
,"IMSCANCST"
,"IMROWCST"
,"IMPAGECST"
,"IMRIDSORT"
,"IMMERGCST"
,"IMCPU"
,"IMTOT"
,"IMSEQNO"
,"DMPREFH"
,"DMCLUDIO"
,"DMNCLUDIO"
,"DMPREDS"
,"DMSROWS"
,"DMSCANCST"
,"DMCOLS"
,"DMROWS"
,"RDSROWCST"
,"DMPAGECST"
,"DMDATAIO"
,"DMDATACPU"
,"DMDATATOT"
,"RDSROW"
,"SNCOLS"
,"SNROWS"
,"SNRECSZ"
,"SNPAGES"
,"SNRUNS"
,"SNMERGES"
,"SNIOCOST"
,"SNCPUCOST"
,"SNCOST"
,"SNSCANIO"
,"SNSCANCPU"
,"SNSCANCOST"
,"SCCOLS"
,"SCROWS"
,"SCRECSZ"
,"SCPAGES"
,"SCRUNS"
,"SCMERGES"
,"SCIOCOST"
,"SCCPUCOST"
,"SCCOST"
,"SCSCANIO"
,"SCSCANCPU"
,"SCSCANCOST"
,"COMPCARD"
,"COMPIOCOST"
,"COMPCPUCOST"
,"COMPCOST"
,"JOINCOLS"
,"EXPLAIN_TIME"
,"COSTBLK"
,"COSTSTOR"
,"MPBLK"
,"MPSTOR"
,"COMPOSITES"
,"CLIPPED"
,"PARTITION"
,"TABREF"
,"MAX_COMPOSITES"
,"MAX_STOR"
,"MAX_CPU"
,"MAX_ELAP"
,"TBL_JOINED_THRESH"
,"STOR_USED"
,"CPU_USED"
,"ELAPSED"
,"MIN_CARD_KEEP"
,"MAX_CARD_KEEP"
,"MIN_COST_KEEP"
,"MAX_COST_KEEP"
,"MIN_VALUE_KEEP"
,"MIN_VALUE_CARD_KEEP"
,"MIN_VALUE_COST_KEEP"
,"MAX_VALUE_KEEP"
,"MAX_VALUE_CARD_KEEP"
,"MAX_VALUE_COST_KEEP"
,"MIN_CARD_CLIP"
,"MAX_CARD_CLIP"
,"MIN_COST_CLIP"
,"MAX_COST_CLIP"
,"MIN_VALUE_CLIP"
,"MIN_VALUE_CARD_CLIP"
,"MIN_VALUE_COST_CLIP"
,"MAX_VALUE_CLIP"
,"MAX_VALUE_CARD_CLIP"
,"MAX_VALUE_COST_CLIP"
,"GROUP_MEMBER"
,"PSEQIOCOST"
,"PSEQCPUCOST"
,"PSEQCOST"
,"PADJIOCOST"
,"PADJCPUCOST"
,"PADJCOST"
,"UNCERTAINTY"
,"UNCERTAINTY_1T"
,"SECTNOI"
,"COLLID"
,"VERSION"
,"IMNP"
,"DMNP"
,"IMJC"
,"IMFC"
,"IMJBC"
,"IMJFC"
,"CRED"
,"IXSCAN_SKIP_DUPS"
,"IXSCAN_SKIP_SCREEN"
,"EARLY_OUT"
,"EXPANSION_REASON"
,"BLOCK_FETCH"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 138-COLUMN TABLE
,"AP_PLANID"
FROM DSN8@@0.DSN_DETCOST_TABLE;
-----------------------------------------------------------------------
--MIGRATE A V12 (18-COLUMN) DSN_SORT_TABLE TO V13
INSERT INTO DSN8!!0.DSN_SORT_TABLE
( "QUERYNO"
,"QBLOCKNO"
,"PLANNO"
,"APPLNAME"
,"PROGNAME"
,"COLLID"
,"SORTC"
,"SORTN"
,"SORTNO"
,"KEYSIZE"
,"ORDERCLASS"
,"EXPLAIN_TIME"
,"GROUP_MEMBER"
,"SECTNOI"
,"VERSION"
,"EXPANSION_REASON"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 17-COLUMN TABLE
,"AP_PLANID"
)
SELECT "QUERYNO"
,"QBLOCKNO"
,"PLANNO"
,"APPLNAME"
,"PROGNAME"
,"COLLID"
,"SORTC"
,"SORTN"
,"SORTNO"
,"KEYSIZE"
,"ORDERCLASS"
,"EXPLAIN_TIME"
,"GROUP_MEMBER"
,"SECTNOI"
,"VERSION"
,"EXPANSION_REASON"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 17-COLUMN TABLE
,"AP_PLANID"
FROM DSN8@@0.DSN_SORT_TABLE;
-----------------------------------------------------------------------
--MIGRATE A V12 (23-COLUMN) DSN_SORTKEY_TABLE TO V13
INSERT INTO DSN8!!0.DSN_SORTKEY_TABLE
( "QUERYNO"
,"QBLOCKNO"
,"PLANNO"
,"APPLNAME"
,"PROGNAME"
,"COLLID"
,"SORTNO"
,"ORDERNO"
,"EXPTYPE"
,"TEXT"
,"TABNO"
,"COLNO"
,"DATATYPE"
,"LENGTH"
,"CCSID"
,"ORDERCLASS"
,"EXPLAIN_TIME"
,"GROUP_MEMBER"
,"SECTNOI"
,"VERSION"
,"EXPANSION_REASON"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 22-COLUMN TABLE
,"AP_PLANID"
)
SELECT "QUERYNO"
,"QBLOCKNO"
,"PLANNO"
,"APPLNAME"
,"PROGNAME"
,"COLLID"
,"SORTNO"
,"ORDERNO"
,"EXPTYPE"
,"TEXT"
,"TABNO"
,"COLNO"
,"DATATYPE"
,"LENGTH"
,"CCSID"
,"ORDERCLASS"
,"EXPLAIN_TIME"
,"GROUP_MEMBER"
,"SECTNOI"
,"VERSION"
,"EXPANSION_REASON"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 22-COLUMN TABLE
,"AP_PLANID"
FROM DSN8@@0.DSN_SORTKEY_TABLE;
-----------------------------------------------------------------------
--MIGRATE A V12 (17-COLUMN) DSN_PGRANGE_TABLE TO V13
INSERT INTO DSN8!!0.DSN_PGRANGE_TABLE
( "QUERYNO"
,"QBLOCKNO"
,"TABNO"
,"RANGE"
,"FIRSTPART"
,"LASTPART"
,"NUMPARTS"
,"EXPLAIN_TIME"
,"GROUP_MEMBER"
,"SECTNOI"
,"APPLNAME"
,"PROGNAME"
,"COLLID"
,"VERSION"
,"EXPANSION_REASON"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 16-COLUMN TABLE
,"AP_PLANID"
)
SELECT "QUERYNO"
,"QBLOCKNO"
,"TABNO"
,"RANGE"
,"FIRSTPART"
,"LASTPART"
,"NUMPARTS"
,"EXPLAIN_TIME"
,"GROUP_MEMBER"
,"SECTNOI"
,"APPLNAME"
,"PROGNAME"
,"COLLID"
,"VERSION"
,"EXPANSION_REASON"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 16-COLUMN TABLE
,"AP_PLANID"
FROM DSN8@@0.DSN_PGRANGE_TABLE;
-----------------------------------------------------------------------
--MIGRATE A V12 (14-COLUMN) DSN_VIEWREF_TABLE TO V13
INSERT INTO DSN8!!0.DSN_VIEWREF_TABLE
( "QUERYNO"
,"APPLNAME"
,"PROGNAME"
,"VERSION"
,"COLLID"
,"CREATOR"
,"NAME"
,"TYPE"
,"MQTUSE"
,"EXPLAIN_TIME"
,"GROUP_MEMBER"
,"SECTNOI"
,"EXPANSION_REASON"
--DELETE THE NEXT LINE IF MIGRATING A 13-COLUMN TABLE
,"AP_PLANID"
)
SELECT "QUERYNO"
,"APPLNAME"
,"PROGNAME"
,"VERSION"
,"COLLID"
,"CREATOR"
,"NAME"
,"TYPE"
,"MQTUSE"
,"EXPLAIN_TIME"
,"GROUP_MEMBER"
,"SECTNOI"
,"EXPANSION_REASON"
--DELETE THE NEXT LINE IF MIGRATING A 13-COLUMN TABLE
,"AP_PLANID"
FROM DSN8@@0.DSN_VIEWREF_TABLE;
-----------------------------------------------------------------------
--MIGRATE A V12 (16-COLUMN) DSN_QUERY_TABLE TO V13
INSERT INTO DSN8!!0.DSN_QUERY_TABLE
( "QUERYNO"
,"TYPE"
,"QUERY_STAGE"
,"SEQNO"
,"NODE_DATA"
,"EXPLAIN_TIME"
,"QUERY_ROWID"
,"GROUP_MEMBER"
,"HASHKEY"
,"HAS_PRED"
,"SECTNOI"
,"APPLNAME"
,"PROGNAME"
,"COLLID"
,"VERSION"
,"EXPANSION_REASON"
)
SELECT "QUERYNO"
,"TYPE"
,"QUERY_STAGE"
,"SEQNO"
,"NODE_DATA"
,"EXPLAIN_TIME"
,"QUERY_ROWID"
,"GROUP_MEMBER"
,"HASHKEY"
,"HAS_PRED"
,"SECTNOI"
,"APPLNAME"
,"PROGNAME"
,"COLLID"
,"VERSION"
,"EXPANSION_REASON"
FROM DSN8@@0.DSN_QUERY_TABLE;
-----------------------------------------------------------------------
--MIGRATE A V12 (19-COLUMN) DSN_QUERYINFO_TABLE TO V13
INSERT INTO DSN8!!0.DSN_QUERYINFO_TABLE
( "QUERYNO"
, "QBLOCKNO"
, "QINAME1"
, "QINAME2"
, "APPLNAME"
, "PROGNAME"
, "VERSION"
, "COLLID"
, "GROUP_MEMBER"
, "SECTNOI"
, "SEQNO"
, "EXPLAIN_TIME"
, "TYPE"
, "REASON_CODE"
, "QI_DATA"
, "SERVICE_INFO"
,"EXPANSION_REASON"
--DELETE THE NEXT LINE IF MIGRATING A 18-COLUMN TABLE
,"AP_PLANID"
)
SELECT "QUERYNO"
, "QBLOCKNO"
, "QINAME1"
, "QINAME2"
, "APPLNAME"
, "PROGNAME"
, "VERSION"
, "COLLID"
, "GROUP_MEMBER"
, "SECTNOI"
, "SEQNO"
, "EXPLAIN_TIME"
, "TYPE"
, "REASON_CODE"
, "QI_DATA"
, "SERVICE_INFO"
,"EXPANSION_REASON"
--DELETE THE NEXT LINE IF MIGRATING A 18-COLUMN TABLE
,"AP_PLANID"
FROM DSN8@@0.DSN_QUERYINFO_TABLE;
-----------------------------------------------------------------------
--MIGRATE A V12 (150-COLUMN) DSN_VIRTUAL_INDEXES TABLE TO V13
INSERT INTO DSN8!!0.DSN_VIRTUAL_INDEXES
( "TBCREATOR"
,"TBNAME"
,"IXCREATOR"
,"IXNAME"
,"ENABLE"
,"MODE"
,"UNIQUERULE"
,"COLCOUNT"
,"CLUSTERING"
,"NLEAF"
,"NLEVELS"
,"INDEXTYPE"
,"PGSIZE"
,"FIRSTKEYCARDF"
,"FULLKEYCARDF"
,"CLUSTERRATIOF"
,"PADDED"
,"COLNO1"
,"ORDERING1"
,"COLNO2"
,"ORDERING2"
,"COLNO3"
,"ORDERING3"
,"COLNO4"
,"ORDERING4"
,"COLNO5"
,"ORDERING5"
,"COLNO6"
,"ORDERING6"
,"COLNO7"
,"ORDERING7"
,"COLNO8"
,"ORDERING8"
,"COLNO9"
,"ORDERING9"
,"COLNO10"
,"ORDERING10"
,"COLNO11"
,"ORDERING11"
,"COLNO12"
,"ORDERING12"
,"COLNO13"
,"ORDERING13"
,"COLNO14"
,"ORDERING14"
,"COLNO15"
,"ORDERING15"
,"COLNO16"
,"ORDERING16"
,"COLNO17"
,"ORDERING17"
,"COLNO18"
,"ORDERING18"
,"COLNO19"
,"ORDERING19"
,"COLNO20"
,"ORDERING20"
,"COLNO21"
,"ORDERING21"
,"COLNO22"
,"ORDERING22"
,"COLNO23"
,"ORDERING23"
,"COLNO24"
,"ORDERING24"
,"COLNO25"
,"ORDERING25"
,"COLNO26"
,"ORDERING26"
,"COLNO27"
,"ORDERING27"
,"COLNO28"
,"ORDERING28"
,"COLNO29"
,"ORDERING29"
,"COLNO30"
,"ORDERING30"
,"COLNO31"
,"ORDERING31"
,"COLNO32"
,"ORDERING32"
,"COLNO33"
,"ORDERING33"
,"COLNO34"
,"ORDERING34"
,"COLNO35"
,"ORDERING35"
,"COLNO36"
,"ORDERING36"
,"COLNO37"
,"ORDERING37"
,"COLNO38"
,"ORDERING38"
,"COLNO39"
,"ORDERING39"
,"COLNO40"
,"ORDERING40"
,"COLNO41"
,"ORDERING41"
,"COLNO42"
,"ORDERING42"
,"COLNO43"
,"ORDERING43"
,"COLNO44"
,"ORDERING44"
,"COLNO45"
,"ORDERING45"
,"COLNO46"
,"ORDERING46"
,"COLNO47"
,"ORDERING47"
,"COLNO48"
,"ORDERING48"
,"COLNO49"
,"ORDERING49"
,"COLNO50"
,"ORDERING50"
,"COLNO51"
,"ORDERING51"
,"COLNO52"
,"ORDERING52"
,"COLNO53"
,"ORDERING53"
,"COLNO54"
,"ORDERING54"
,"COLNO55"
,"ORDERING55"
,"COLNO56"
,"ORDERING56"
,"COLNO57"
,"ORDERING57"
,"COLNO58"
,"ORDERING58"
,"COLNO59"
,"ORDERING59"
,"COLNO60"
,"ORDERING60"
,"COLNO61"
,"ORDERING61"
,"COLNO62"
,"ORDERING62"
,"COLNO63"
,"ORDERING63"
,"COLNO64"
,"ORDERING64"
,"KEYTARGET_COUNT"
,"UNIQUE_COUNT"
,"IX_EXTENSION_TYPE"
,"DATAREPEATFACTORF"
,"SPARSE"
)
SELECT "TBCREATOR"
,"TBNAME"
,"IXCREATOR"
,"IXNAME"
,"ENABLE"
,"MODE"
,"UNIQUERULE"
,"COLCOUNT"
,"CLUSTERING"
,"NLEAF"
,"NLEVELS"
,"INDEXTYPE"
,"PGSIZE"
,"FIRSTKEYCARDF"
,"FULLKEYCARDF"
,"CLUSTERRATIOF"
,"PADDED"
,"COLNO1"
,"ORDERING1"
,"COLNO2"
,"ORDERING2"
,"COLNO3"
,"ORDERING3"
,"COLNO4"
,"ORDERING4"
,"COLNO5"
,"ORDERING5"
,"COLNO6"
,"ORDERING6"
,"COLNO7"
,"ORDERING7"
,"COLNO8"
,"ORDERING8"
,"COLNO9"
,"ORDERING9"
,"COLNO10"
,"ORDERING10"
,"COLNO11"
,"ORDERING11"
,"COLNO12"
,"ORDERING12"
,"COLNO13"
,"ORDERING13"
,"COLNO14"
,"ORDERING14"
,"COLNO15"
,"ORDERING15"
,"COLNO16"
,"ORDERING16"
,"COLNO17"
,"ORDERING17"
,"COLNO18"
,"ORDERING18"
,"COLNO19"
,"ORDERING19"
,"COLNO20"
,"ORDERING20"
,"COLNO21"
,"ORDERING21"
,"COLNO22"
,"ORDERING22"
,"COLNO23"
,"ORDERING23"
,"COLNO24"
,"ORDERING24"
,"COLNO25"
,"ORDERING25"
,"COLNO26"
,"ORDERING26"
,"COLNO27"
,"ORDERING27"
,"COLNO28"
,"ORDERING28"
,"COLNO29"
,"ORDERING29"
,"COLNO30"
,"ORDERING30"
,"COLNO31"
,"ORDERING31"
,"COLNO32"
,"ORDERING32"
,"COLNO33"
,"ORDERING33"
,"COLNO34"
,"ORDERING34"
,"COLNO35"
,"ORDERING35"
,"COLNO36"
,"ORDERING36"
,"COLNO37"
,"ORDERING37"
,"COLNO38"
,"ORDERING38"
,"COLNO39"
,"ORDERING39"
,"COLNO40"
,"ORDERING40"
,"COLNO41"
,"ORDERING41"
,"COLNO42"
,"ORDERING42"
,"COLNO43"
,"ORDERING43"
,"COLNO44"
,"ORDERING44"
,"COLNO45"
,"ORDERING45"
,"COLNO46"
,"ORDERING46"
,"COLNO47"
,"ORDERING47"
,"COLNO48"
,"ORDERING48"
,"COLNO49"
,"ORDERING49"
,"COLNO50"
,"ORDERING50"
,"COLNO51"
,"ORDERING51"
,"COLNO52"
,"ORDERING52"
,"COLNO53"
,"ORDERING53"
,"COLNO54"
,"ORDERING54"
,"COLNO55"
,"ORDERING55"
,"COLNO56"
,"ORDERING56"
,"COLNO57"
,"ORDERING57"
,"COLNO58"
,"ORDERING58"
,"COLNO59"
,"ORDERING59"
,"COLNO60"
,"ORDERING60"
,"COLNO61"
,"ORDERING61"
,"COLNO62"
,"ORDERING62"
,"COLNO63"
,"ORDERING63"
,"COLNO64"
,"ORDERING64"
,"KEYTARGET_COUNT"
,"UNIQUE_COUNT"
,"IX_EXTENSION_TYPE"
,"DATAREPEATFACTORF"
,"SPARSE"
FROM DSN8@@0.DSN_VIRTUAL_INDEXES;
-----------------------------------------------------------------------
--MIGRATE A V12 (23-COLUMN) DSN_COLDIST_TABLE TO V13
INSERT INTO DSN8!!0.DSN_COLDIST_TABLE
( "QUERYNO"
,"APPLNAME"
,"PROGNAME"
,"COLLID"
,"GROUP_MEMBER"
,"SECTNOI"
,"VERSION"
,"EXPLAIN_TIME"
,"SCHEMA"
,"TBNAME"
,"NAME"
,"COLVALUE"
,"TYPE"
,"CARDF"
,"COLGROUPCOLNO"
,"NUMCOLUMNS"
,"FREQUENCYF"
,"QUANTILENO"
,"LOWVALUE"
,"HIGHVALUE"
,"EXPANSION_REASON"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 22-COLUMN TABLE
,"AP_PLANID"
)
SELECT "QUERYNO"
,"APPLNAME"
,"PROGNAME"
,"COLLID"
,"GROUP_MEMBER"
,"SECTNOI"
,"VERSION"
,"EXPLAIN_TIME"
,"SCHEMA"
,"TBNAME"
,"NAME"
,"COLVALUE"
,"TYPE"
,"CARDF"
,"COLGROUPCOLNO"
,"NUMCOLUMNS"
,"FREQUENCYF"
,"QUANTILENO"
,"LOWVALUE"
,"HIGHVALUE"
,"EXPANSION_REASON"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 22-COLUMN TABLE
,"AP_PLANID"
FROM DSN8@@0.DSN_COLDIST_TABLE;
-----------------------------------------------------------------------
--MIGRATE A V12 (23-COLUMN) DSN_KEYTGTDIST_TABLE TO V13
INSERT INTO DSN8!!0.DSN_KEYTGTDIST_TABLE
( "QUERYNO"
,"APPLNAME"
,"PROGNAME"
,"COLLID"
,"GROUP_MEMBER"
,"SECTNOI"
,"VERSION"
,"EXPLAIN_TIME"
,"IXSCHEMA"
,"IXNAME"
,"KEYSEQ"
,"KEYVALUE"
,"TYPE"
,"CARDF"
,"KEYGROUPKEYNO"
,"NUMKEYS"
,"FREQUENCYF"
,"QUANTILENO"
,"LOWVALUE"
,"HIGHVALUE"
,"EXPANSION_REASON"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 22-COLUMN TABLE
,"AP_PLANID"
)
SELECT "QUERYNO"
,"APPLNAME"
,"PROGNAME"
,"COLLID"
,"GROUP_MEMBER"
,"SECTNOI"
,"VERSION"
,"EXPLAIN_TIME"
,"IXSCHEMA"
,"IXNAME"
,"KEYSEQ"
,"KEYVALUE"
,"TYPE"
,"CARDF"
,"KEYGROUPKEYNO"
,"NUMKEYS"
,"FREQUENCYF"
,"QUANTILENO"
,"LOWVALUE"
,"HIGHVALUE"
,"EXPANSION_REASON"
,"PER_STMT_ID"
--DELETE THE NEXT LINE IF MIGRATING A 22-COLUMN TABLE
,"AP_PLANID"
FROM DSN8@@0.DSN_KEYTGTDIST_TABLE;
-----------------------------------------------------------------------
--MIGRATE A V12 (17-COLUMN) DSN_PREDICATE_SELECTIVITY TABLE TO V13
INSERT INTO DSN8!!0.DSN_PREDICATE_SELECTIVITY
( "QUERYNO"
,"QBLOCKNO"
,"APPLNAME"
,"PROGNAME"
,"SECTNOI"
,"COLLID"
,"VERSION"
,"PREDNO"
,"INSTANCE"
,"SELECTIVITY"
,"WEIGHT"
,"ASSUMPTION"
,"EXPLAIN_TIME"
,"REMARKS"
,"EXPANSION_REASON"
,"PER_STMT_ID"
)
SELECT "QUERYNO"
,"QBLOCKNO"
,"APPLNAME"
,"PROGNAME"
,"SECTNOI"
,"COLLID"
,"VERSION"
,"PREDNO"
,"INSTANCE"
,"SELECTIVITY"
,"WEIGHT"
,"ASSUMPTION"
,"EXPLAIN_TIME"
,"REMARKS"
,"EXPANSION_REASON"
,"PER_STMT_ID"
FROM DSN8@@0.DSN_PREDICATE_SELECTIVITY;
-----------------------------------------------------------------------
--MIGRATE A V12 (21-COLUMN) DSN_STAT_FEEDBACK TABLE TO V13
INSERT INTO DSN8!!0.DSN_STAT_FEEDBACK
( "QUERYNO"
,"APPLNAME"
,"PROGNAME"
,"COLLID"
,"GROUP_MEMBER"
,"EXPLAIN_TIME"
,"SECTNOI"
,"VERSION"
,"TBCREATOR"
,"TBNAME"
,"IXCREATOR"
,"IXNAME"
,"COLNAME"
,"NUMCOLUMNS"
,"COLGROUPCOLNO"
,"TYPE"
,"DBNAME"
,"TSNAME"
,"REASON"
,"REMARKS"
--DELETE THE NEXT LINE IF MIGRATING A 20-COLUMN TABLE
,"AP_PLANID"
)
SELECT "QUERYNO"
,"APPLNAME"
,"PROGNAME"
,"COLLID"
,"GROUP_MEMBER"
,"EXPLAIN_TIME"
,"SECTNOI"
,"VERSION"
,"TBCREATOR"
,"TBNAME"
,"IXCREATOR"
,"IXNAME"
,"COLNAME"
,"NUMCOLUMNS"
,"COLGROUPCOLNO"
,"TYPE"
,"DBNAME"
,"TSNAME"
,"REASON"
,"REMARKS"
--DELETE THE NEXT LINE IF MIGRATING A 20-COLUMN TABLE
,"AP_PLANID"
FROM DSN8@@0.DSN_STAT_FEEDBACK;
-----------------------------------------------------------------------
--MIGRATE A V12 (16-COLUMN) DSN_VIRTUAL_KEYTARGETS TABLE TO V13
INSERT INTO DSN8!!0.DSN_VIRTUAL_KEYTARGETS
( "ENABLE"
,"IXNAME"
,"IXSCHEMA"
,"KEYSEQ"
,"COLNO"
,"ORDERING"
,"TYPESCHEMA"
,"TYPENAME"
,"LENGTH"
,"LENGTH2"
,"SCALE"
,"NULLS"
,"CCSID"
,"SUBTYPE"
,"DERIVED_FROM"
,"CARDF"
)
SELECT "ENABLE"
,"IXNAME"
,"IXSCHEMA"
,"KEYSEQ"
,"COLNO"
,"ORDERING"
,"TYPESCHEMA"
,"TYPENAME"
,"LENGTH"
,"LENGTH2"
,"SCALE"
,"NULLS"
,"CCSID"
,"SUBTYPE"
,"DERIVED_FROM"
,"CARDF"
FROM DSN8@@0.DSN_VIRTUAL_KEYTARGETS;
-----------------------------------------------------------------------
-----------------------------------------------------------------------
--*********************************************************************
--** THE FOLLOWING STATEMENTS CAN BE USED TO DROP THE PREVIOUS **
--** RELEASE SAMPLE EXPLAIN TABLES WHICH WERE CREATED IN PARTITION- **
--** BY-GROWTH TABLE SPACES. DO NOT RUN THESE STATEMENTS **
--** UNTIL YOU HAVE SUCCESSFULLY COMPLETED MIGRATED THE PREVIOUS **
--** VERSIONS OF -ALL- OF THESE TABLES TO V13 **
--*********************************************************************
-----------------------------------------------------------------------
--DROP THE FOLLOWING PREVIOUS-RELEASE SAMPLE PLAN_TABLE
-- DROP TABLESPACE DSN8D@@Z.DSN8S@@A;
-- COMMIT;
-----------------------------------------------------------------------
--DROP THE FOLLOWING PREVIOUS-RELEASE SAMPLE DSN_FUNCTION_TABLE
-- DROP TABLESPACE DSN8D@@Z.DSN8S@@B;
-- COMMIT;
-----------------------------------------------------------------------
--DROP THE FOLLOWING PREVIOUS-RELEASE SAMPLE DSN_STATEMNT_TABLE
-- DROP TABLESPACE DSN8D@@Z.DSN8S@@C;
-- COMMIT;
-----------------------------------------------------------------------
--DROP THE FOLLOWING PREVIOUS-RELEASE SAMPLE DSN_STATEMENT_CACHE_TABLE
--
--IF THE PROGRAM YOU ARE RUNNING TO EXECUTE THE DROP TABLESPACE
--STATEMENTS WAS BOUND WITH AN APPLCOMPAT VALUE GREATER THAN
--OR EQUAL TO V12R1M506, DO NOT UNCOMMENT THE DROP TABLESPACE
--DSN8D@@Z.DSN8L@@D STATEMENT
-- DROP TABLESPACE DSN8D@@Z.DSN8S@@D;
-- DROP TABLESPACE DSN8D@@Z.DSN8L@@D;
-- COMMIT;
-----------------------------------------------------------------------
--DROP THE FOLLOWING PREVIOUS-RELEASE SAMPLE DSN_PREDICAT_TABLE
-- DROP TABLESPACE DSN8D@@Z.DSN8S@@E;
-- COMMIT;
-----------------------------------------------------------------------
--DROP THE FOLLOWING PREVIOUS-RELEASE SAMPLE DSN_STRUCT_TABLE
-- DROP TABLESPACE DSN8D@@Z.DSN8S@@F;
-- COMMIT;
-----------------------------------------------------------------------
--DROP THE FOLLOWING PREVIOUS-RELEASE SAMPLE DSN_PGROUP_TABLE
-- DROP TABLESPACE DSN8D@@Z.DSN8S@@G;
-- COMMIT;
-----------------------------------------------------------------------
--DROP THE FOLLOWING PREVIOUS-RELEASE SAMPLE DSN_PTASK_TABLE
-- DROP TABLESPACE DSN8D@@Z.DSN8S@@H;
-- COMMIT;
-----------------------------------------------------------------------
--DROP THE FOLLOWING PREVIOUS-RELEASE SAMPLE DSN_FILTER_TABLE
-- DROP TABLESPACE DSN8D@@Z.DSN8S@@K;
-- COMMIT;
-----------------------------------------------------------------------
--DROP THE FOLLOWING PREVIOUS-RELEASE SAMPLE DSN_DETCOST_TABLE
-- DROP TABLESPACE DSN8D@@Z.DSN8S@@L;
-- COMMIT;
-----------------------------------------------------------------------
--DROP THE FOLLOWING PREVIOUS-RELEASE SAMPLE DSN_SORT_TABLE
-- DROP TABLESPACE DSN8D@@Z.DSN8S@@M;
-- COMMIT;
-----------------------------------------------------------------------
--DROP THE FOLLOWING PREVIOUS-RELEASE SAMPLE DSN_SORTKEY_TABLE
-- DROP TABLESPACE DSN8D@@Z.DSN8S@@N;
-- COMMIT;
-----------------------------------------------------------------------
--DROP THE FOLLOWING PREVIOUS-RELEASE SAMPLE DSN_PGRANGE_TABLE
-- DROP TABLESPACE DSN8D@@Z.DSN8S@@O;
-- COMMIT;
-----------------------------------------------------------------------
--DROP THE FOLLOWING PREVIOUS-RELEASE SAMPLE DSN_VIEWREF_TABLE
-- DROP TABLESPACE DSN8D@@Z.DSN8S@@P;
-- COMMIT;
-----------------------------------------------------------------------
--DROP THE FOLLOWING PREVIOUS-RELEASE SAMPLE DSN_QUERY_TABLE
--
--IF THE PROGRAM YOU ARE RUNNING TO EXECUTE THE DROP TABLESPACE
--STATEMENTS WAS BOUND WITH AN APPLCOMPAT VALUE GREATER THAN
--OR EQUAL TO V12R1M506, DO NOT UNCOMMENT THE DROP TABLESPACE
--DSN8D@@Z.DSN8L@@I STATEMENT
-- DROP TABLESPACE DSN8D@@Z.DSN8S@@I;
-- DROP TABLESPACE DSN8D@@Z.DSN8L@@I;
-- COMMIT;
-----------------------------------------------------------------------
--DROP THE FOLLOWING PREVIOUS-RELEASE SAMPLE DSN_QUERYINFO_TABLE
--
--IF THE PROGRAM YOU ARE RUNNING TO EXECUTE THE DROP TABLESPACE
--STATEMENTS WAS BOUND WITH AN APPLCOMPAT VALUE GREATER THAN
--OR EQUAL TO V12R1M506, DO NOT UNCOMMENT THE DROP TABLESPACE
--DSN8D@@Z.DSN8L@@* STATEMENTS
-- DROP TABLESPACE DSN8D@@Z.DSN8S@@Q;
-- DROP TABLESPACE DSN8D@@Z.DSN8L@@Q;
-- DROP TABLESPACE DSN8D@@Z.DSN8L@@R;
-- COMMIT;
-----------------------------------------------------------------------
--DROP THE FOLLOWING PREVIOUS-RELEASE SAMPLE DSN_VIRTUAL_INDEXES
-- DROP TABLESPACE DSN8D@@Z.DSN8S@@J;
-- COMMIT;
-----------------------------------------------------------------------
--DROP THE FOLLOWING PREVIOUS-RELEASE SAMPLE DSN_COLDIST_TABLE
-- DROP TABLESPACE DSN8D@@Z.DSN8S@@S;
-- COMMIT;
-----------------------------------------------------------------------
--DROP THE FOLLOWING PREVIOUS-RELEASE SAMPLE DSN_KEYTGTDIST_TABLE
-- DROP TABLESPACE DSN8D@@Z.DSN8S@@T;
-- COMMIT;
-----------------------------------------------------------------------
--DROP THE FOLLOWING PREVIOUS-RELEASE SAMPLE DSN_PREDICATE_SELECTIVITY
-- DROP TABLESPACE DSN8D@@Z.DSN8S@@U;
-- COMMIT;
-----------------------------------------------------------------------
--DROP THE FOLLOWING PREVIOUS-RELEASE SAMPLE DSN_STAT_FEEDBACK
-- DROP TABLESPACE DSN8D@@Z.DSN8S@@W;
-- COMMIT;
-----------------------------------------------------------------------
--DROP THE FOLLOWING PREVIOUS-RELEASE SAMPLE DSN_VIRTUAL_KEYTARGETS
-- DROP TABLESPACE DSN8D@@Z.DSN8S@@X;
-- COMMIT;
