DSNTESH

-----------------------------------------------------------------------
--  NAME = DSNTESH
--
--  DESCRIPTIVE NAME = CREATE THE DB2 V12 SAMPLE DSN_USERQUERY_TABLE
--
--     LICENSED MATERIALS - PROPERTY OF IBM
--     5615-DB2
--     (C) COPYRIGHT 1982, 2013 IBM CORP.  ALL RIGHTS RESERVED.
--
--     STATUS = VERSION 12
--
--  FUNCTION = ILLUSTRATES HOW TO
--   (1) CREATE A SAMPLE DSN_USERQUERY_TABLE, INCLUDING THE
--       CONTAINING TABLE SPACES.
--   (2) MIGRATE THE PREVIOUS RELEASE TABLES TO V12
--   (3) DROP THE PREVIOUS RELEASE TABLE AFTER IT HAS BEEN MIGRATED
--
--  CHANGE ACTIVITY
--    PM29831 Add DSN_USERQUERY_TABLE_AUX_IX
--    11/07/2012 Add SET CURRENT SQLID           dn1651_inst1 / dn1651
--    12/05/2012 Update for V11                                 165085
--    02/04/2014 CREATE PARTITION-BY-GROWTH TABLE SPACES        PI05200
-----------------------------------------------------------------------

-----------------------------------------------------------------------
-- SET THE CURRENT AUTHORIZATION ID TO PERFORM THE WORK
-----------------------------------------------------------------------
  SET CURRENT SQLID = 'SYSADM';

-----------------------------------------------------------------------
-- DROP THE SAMPLE TABLESPACES FOR THE V12 SAMPLE DSN_USERQUERY_TABLE
-- IF CLEANUP IS REQUIRED.
-----------------------------------------------------------------------
--DROP TABLESPACE DSN8D!!Z.DSN8S!!V;
--DROP TABLESPACE DSN8D!!Z.DSN8L!!V;
--COMMIT;


-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACES FOR THE V12 SAMPLE DSN_USERQUERY_TABLE
-----------------------------------------------------------------------
  CREATE TABLESPACE DSN8S!!V
     IN DSN8D!!Z
     BUFFERPOOL BP8K0
     SEGSIZE 16
     MAXPARTITIONS 256
     DSSIZE 4G
     CCSID UNICODE;

  CREATE LOB TABLESPACE DSN8L!!V
     IN DSN8D!!Z
     BUFFERPOOL BP32K;


-----------------------------------------------------------------------
--CREATE THE V12 SAMPLE DSN_USERQUERY_TABLE
-----------------------------------------------------------------------
  CREATE TABLE DSN8!!0.DSN_USERQUERY_TABLE
         ( "QUERYNO"            INTEGER      NOT NULL PRIMARY KEY
         , "SCHEMA"             VARCHAR(128) NOT NULL DEFAULT ' '
         , "HINT_SCOPE"         SMALLINT     NOT NULL DEFAULT 0
         , "QUERY_TEXT"         CLOB(2M)     NOT NULL
         , "QUERY_ROWID"        ROWID        NOT NULL GENERATED ALWAYS
         , "QUERYID"            BIGINT       NOT NULL DEFAULT 0
         , "USERFILTER"         CHAR(8)      NOT NULL DEFAULT ' '
         , "OTHER_OPTIONS"      CHAR(128)    NOT NULL DEFAULT ' '
         , "COLLECTION"         VARCHAR(128) NOT NULL DEFAULT ' '
         , "PACKAGE"            VARCHAR(128) NOT NULL DEFAULT ' '
         , "VERSION"            VARCHAR(128) NOT NULL DEFAULT ' '
         , "REOPT"              CHAR(1)      NOT NULL DEFAULT ' '
         , "STARJOIN"           CHAR(1)      NOT NULL DEFAULT ' '
         , "MAX_PAR_DEGREE"     INTEGER      NOT NULL DEFAULT -1
         , "DEF_CURR_DEGREE"    CHAR(3)      NOT NULL DEFAULT ' '
         , "SJTABLES"           INTEGER      NOT NULL DEFAULT -1
         , "OTHER_PARMS"        VARCHAR(128) NOT NULL DEFAULT ' '
         , "SELECTVTY_OVERRIDE" CHAR(1)      NOT NULL DEFAULT 'N'
         , "ACCESSPATH_HINT"    CHAR(1)      NOT NULL DEFAULT ' '
         , "OPTION_OVERRIDE"    CHAR(1)      NOT NULL DEFAULT ' '
         )
         IN DSN8D!!Z.DSN8S!!V
         CCSID UNICODE;

  CREATE AUX TABLE DSN8!!0.DSN_USERQUERY_TABLE_AUX
         IN DSN8D!!Z.DSN8L!!V
         STORES DSN8!!0.DSN_USERQUERY_TABLE
         COLUMN QUERY_TEXT
         PART 1;

-----------------------------------------------------------------------
--CREATE THE PRIMARY KEY INDEX ON THE V12 SAMPLE DSN_USERQUERY_TABLE
-----------------------------------------------------------------------
  CREATE UNIQUE INDEX DSN8!!0.DSN_USERQUERY_TABLE_IX1
         ON DSN8!!0.DSN_USERQUERY_TABLE
         ( "QUERYNO"            ASC
         );

-----------------------------------------------------------------------
--CREATE THE INDEX ON THE V12 SAMPLE DSN_USERQUERY_TABLE AUXILIARY TBL
-----------------------------------------------------------------------
  CREATE INDEX DSN8!!0.DSN_USERQUERY_TABLE_AUX_IX
         ON DSN8!!0.DSN_USERQUERY_TABLE_AUX;


-----------------------------------------------------------------------
-- THE FOLLOWING STATEMENTS CAN BE USED TO DROP THE PREVIOUS-RELEASE
-- DSN_USERQUERY_TABLE TABLE WHICH WAS CREATED IN A SEGMENTED
-- TABLE SPACE.  DO NOT RUN THESE STATEMENTS UNTIL
-- YOU HAVE SUCCESSFULLY COMPLETED MIGRATING THE PREVIOUS-RELEASE
-- DSN_USERQUERY_TABLE TABLE.
-----------------------------------------------------------------------
-- DROP TABLESPACE DSN8D@@A.DSN8L@@V;
-- DROP TABLESPACE DSN8D@@A.DSN8S@@V;


-----------------------------------------------------------------------
-- THE FOLLOWING STATEMENTS CAN BE USED TO DROP THE PREVIOUS-RELEASE
-- DSN_USERQUERY_TABLE TABLE WHICH WAS CREATED IN A PARTITION-
-- BY-GROWTH TABLE SPACE.  DO NOT RUN THESE STATEMENTS UNTIL
-- YOU HAVE SUCCESSFULLY COMPLETED MIGRATING THE PREVIOUS-RELEASE
-- DSN_USERQUERY_TABLE TABLE.
-----------------------------------------------------------------------
-- DROP TABLESPACE DSN8D@@Z.DSN8L@@V;
-- DROP TABLESPACE DSN8D@@Z.DSN8S@@V;