--
-- Upgrade DDL for Quest Central 2.1 to 3.0 Install
--

CREATE TABLESPACE QCBLUEPT
  IN QCDB
  USING STOGROUP %SGNAME%
    PRIQTY 300
    SECQTY 500
    ERASE NO
  FREEPAGE 0
  PCTFREE 5
  GBPCACHE CHANGED
  TRACKMOD YES
  DEFINE YES
  BUFFERPOOL %TSBUFFPOOL%
  LOCKSIZE ANY
  LOCKMAX 0
  CLOSE YES
  COMPRESS NO
  CCSID EBCDIC
  MAXROWS 255;

COMMIT WORK;


CREATE TABLE QUEST.QCBLUEPT_REPO030
 (NAME               VARCHAR(18) FOR SBCS DATA     NOT NULL,
  CREATOR            VARCHAR(8) FOR SBCS DATA      NOT NULL,
  DESCRIPTION        VARCHAR(50) FOR SBCS DATA     NOT NULL,
  TEMPLATE           SMALLINT                      NOT NULL,
  ONE_STMT_PER_STEP  CHARACTER(1) FOR SBCS DATA    NOT NULL,
  QUEST_STMTS        CHARACTER(1) FOR SBCS DATA    NOT NULL,
  UTIL_SYNTAX_VER    INTEGER                       NOT NULL,
  UTIL_DELIM         CHARACTER(1) FOR SBCS DATA    NOT NULL,
  CREATEDTS          TIMESTAMP                     NOT NULL  DEFAULT,
  MODIFIEDBY         VARCHAR(8) FOR SBCS DATA      NOT NULL,
  ALTEREDTS          TIMESTAMP                     NOT NULL  DEFAULT,
  RESERVED           VARCHAR(50) FOR SBCS DATA     NOT NULL  DEFAULT
  )
  IN QCDB.QCBLUEPT
  DATA CAPTURE NONE
  AUDIT NONE
  CCSID EBCDIC;

COMMIT WORK;


CREATE TABLE QUEST.QCBLUEPT_STMT030
 (NAME     VARCHAR(18) FOR SBCS DATA     NOT NULL,
  CREATOR  VARCHAR(8) FOR SBCS DATA      NOT NULL,
  SEQ      INTEGER                       NOT NULL,
  STMT     VARCHAR(100) FOR SBCS DATA    NOT NULL
 )
  IN QCDB.QCBLUEPT
  DATA CAPTURE NONE
  AUDIT NONE
  CCSID EBCDIC;

COMMIT WORK;


CREATE UNIQUE INDEX QUEST.QCBLUEPT_REPO030
  ON QUEST.QCBLUEPT_REPO030
 (NAME   ASC
 )
  USING STOGROUP %SGNAME%
    PRIQTY 12
    SECQTY 12
    ERASE NO
  FREEPAGE 0
  PCTFREE 10
  GBPCACHE CHANGED
  DEFINE YES
  BUFFERPOOL %IXBUFFPOOL%
  CLOSE YES
  DEFER NO
  COPY NO
  PIECESIZE 2097152 K;

COMMIT WORK;


CREATE UNIQUE INDEX QUEST.QCBLUEPT_STMT030
  ON QUEST.QCBLUEPT_STMT030
 (NAME      ASC,
  CREATOR   ASC,
  SEQ       ASC
 )
  USING STOGROUP %SGNAME%
    PRIQTY 12
    SECQTY 12
    ERASE NO
  FREEPAGE 0
  PCTFREE 10
  GBPCACHE CHANGED
  DEFINE YES
  BUFFERPOOL  %IXBUFFPOOL%
  CLOSE YES
  DEFER NO
  COPY NO
  PIECESIZE 2097152 K;

COMMIT WORK;

                                                              
CREATE PROCEDURE QUEST.QC300JFI                             
  ( IN  FUNC    CHAR(2)     FOR SBCS DATA CCSID EBCDIC,      
    IN  DATA    VARCHAR(45) FOR SBCS DATA CCSID EBCDIC,      
    IN  USERID  CHAR(8)     FOR SBCS DATA CCSID EBCDIC,      
    IN  TIMETAG TIMESTAMP,                                   
    OUT RETCODE INTEGER                                     
  )                                                         
  EXTERNAL NAME QCJFI030                                    
  LANGUAGE C                                                
  PARAMETER STYLE GENERAL                                   
  NOT DETERMINISTIC                                         
  FENCED                                                    
  MODIFIES SQL DATA                                         
  NO DBINFO                                                 
  CALLED ON NULL INPUT                                      
  RESULT SETS 1                                             
  COLLID %COLLID%                                           
  WLM ENVIRONMENT %MULTTCB%                                  
  STAY RESIDENT NO                                          
  PROGRAM TYPE MAIN                                         
  SECURITY USER                                             
  COMMIT ON RETURN NO;                                      
                                                              
COMMIT;                                                     


CREATE TABLESPACE QCJFITS 
  IN QCDB   
  USING STOGROUP %SGNAME%         
    PRIQTY 500
    SECQTY 500
    ERASE NO       
  FREEPAGE 0
  PCTFREE 5
  GBPCACHE CHANGED
  TRACKMOD YES
  DEFINE YES
  SEGSIZE 32
  BUFFERPOOL %TSBUFFPOOL%
  LOCKSIZE ROW
  LOCKMAX 0
  CLOSE YES
  COMPRESS NO
  CCSID EBCDIC
  MAXROWS 1
  ;                               

COMMIT ;                               
                                         
                                                               
CREATE TABLE QUEST.QCJFI300_INPUT                  
  ( USERID    CHAR(8)   NOT NULL WITH DEFAULT         
  , TIMEST    TIMESTAMP NOT NULL WITH DEFAULT         
  , SEQ       SMALLINT  NOT NULL WITH DEFAULT         
  , DATA      CHAR(80)  NOT NULL WITH DEFAULT         
  )                                                     
  IN QCDB.QCJFITS                                    
  DATA CAPTURE NONE
  AUDIT NONE
  CCSID EBCDIC
  ;                                                     

COMMIT ;                                                     

                                                               
CREATE TABLE QUEST.QCJFI300_OUTPUT                                 
  ( USERID    CHAR(8)   NOT NULL WITH DEFAULT         
  , TIMEST    TIMESTAMP NOT NULL WITH DEFAULT         
  , ROWTYPE   SMALLINT  NOT NULL WITH DEFAULT         
  , SEQ       SMALLINT  NOT NULL WITH DEFAULT         
  , VARDATA   VARCHAR(3900) NOT NULL WITH DEFAULT    
  )                                                     
  IN QCDB.QCJFITS                                    
  DATA CAPTURE NONE
  AUDIT NONE
  CCSID EBCDIC
  ;                                                     

COMMIT ;                                                     


--QCEXEC blueprint
INSERT INTO QUEST.QCBLUEPT_REPO030
   (
   NAME, 
   CREATOR, 
   DESCRIPTION, 
   TEMPLATE, 
   ONE_STMT_PER_STEP, 
   QUEST_STMTS, 
   UTIL_SYNTAX_VER, 
   UTIL_DELIM, 
   CREATEDTS, 
   MODIFIEDBY, 
   ALTEREDTS, 
   RESERVED
   ) 
VALUES 
   (
   'QCEXEC', 
   'QUEST', 
   'Quest Central command execution', 
   0, 
   'N', 
   'Y', 
   7010000, 
   ';', 
   CURRENT TIMESTAMP, 
   'QUEST', 
   CURRENT TIMESTAMP, 
   ''
   );

COMMIT WORK;

INSERT INTO QUEST.QCBLUEPT_STMT030
   (
   NAME, 
   CREATOR, 
   SEQ, 
   STMT
   ) 
VALUES 
   (
   'QCEXEC', 
   'QUEST', 
   1, 
   '//*Restart Parm Description'
   );

INSERT INTO QUEST.QCBLUEPT_STMT030
   (
   NAME, 
   CREATOR, 
   SEQ, 
   STMT
   ) 
VALUES 
   (
   'QCEXEC', 
   'QUEST', 
   2, 
   '//*TARGET=%LOCATION          (1-16 char SUBSYSTEM LOCATION)'
   );

INSERT INTO QUEST.QCBLUEPT_STMT030
   (
   NAME, 
   CREATOR, 
   SEQ, 
   STMT
   ) 
VALUES 
   (
   'QCEXEC', 
   'QUEST', 
   3, 
   '//*RESTART=9999999         (7 digit sync point number for restart)'
   );

INSERT INTO QUEST.QCBLUEPT_STMT030
   (
   NAME, 
   CREATOR, 
   SEQ, 
   STMT
   ) 
VALUES 
   (
   'QCEXEC', 
   'QUEST', 
   4, 
   '//*UTIL=m:XXXXXXXXXXXXXXXX (m=CURRENT,PHASE or TERM:16 char util ID)'
   );


INSERT INTO QUEST.QCBLUEPT_STMT030
   (
   NAME, 
   CREATOR, 
   SEQ, 
   STMT
   ) 
VALUES 
   (
   'QCEXEC', 
   'QUEST', 
   5, 
   '//*TSTAMP=YYYYMMDDHHMMSS   (14 char time stamp for template datasets)'
   );

INSERT INTO QUEST.QCBLUEPT_STMT030
   (
   NAME, 
   CREATOR, 
   SEQ, 
   STMT
   ) 
VALUES 
   (
   'QCEXEC', 
   'QUEST', 
   6, 
   '//*SEQ=999999              (6 digit seq number for SM commands)'
   );

INSERT INTO QUEST.QCBLUEPT_STMT030
   (
   NAME, 
   CREATOR, 
   SEQ, 
   STMT
   ) 
VALUES 
   (
   'QCEXEC', 
   'QUEST', 
   7, 
   '//*EXAMPLE:'
   );

INSERT INTO QUEST.QCBLUEPT_STMT030
   (
   NAME, 
   CREATOR, 
   SEQ, 
   STMT
   ) 
VALUES 
   (
   'QCEXEC', 
   'QUEST', 
   8, 
   '//*PARM=(''TARGET=%LOCATION RESTART=120 UTIL=CURRENT:QCYYYYMMDDHHMMSS'','
   );

INSERT INTO QUEST.QCBLUEPT_STMT030
   (
   NAME, 
   CREATOR, 
   SEQ, 
   STMT
   ) 
VALUES 
   (
   'QCEXEC', 
   'QUEST', 
   9, 
   '//*      ''TSTAMP=YYYYMMDDHHMMSS SEQ=5'')'
   );

INSERT INTO QUEST.QCBLUEPT_STMT030
   (
   NAME, 
   CREATOR, 
   SEQ, 
   STMT
   ) 
VALUES 
   (
   'QCEXEC', 
   'QUEST', 
   10, 
   '//*'
   );

INSERT INTO QUEST.QCBLUEPT_STMT030
   (
   NAME, 
   CREATOR, 
   SEQ, 
   STMT
   ) 
VALUES 
   (
   'QCEXEC', 
   'QUEST', 
   11, 
   '//%STEP  EXEC PGM=QCEXEC,PARM=(''TARGET=%LOCATION'')'
   );

INSERT INTO QUEST.QCBLUEPT_STMT030
   (
   NAME, 
   CREATOR, 
   SEQ, 
   STMT
   ) 
VALUES 
   (
   'QCEXEC', 
   'QUEST', 
   12, 
   '%DB2LOADLIBS'
   );

INSERT INTO QUEST.QCBLUEPT_STMT030
   (
   NAME, 
   CREATOR, 
   SEQ, 
   STMT
   ) 
VALUES 
   (
   'QCEXEC', 
   'QUEST', 
   13, 
   '%MSGLIBS'
   );

INSERT INTO QUEST.QCBLUEPT_STMT030
   (
   NAME, 
   CREATOR, 
   SEQ, 
   STMT
   ) 
VALUES 
   (
   'QCEXEC', 
   'QUEST', 
   14, 
   '//QSLOG    DD SYSOUT=*'
   );

INSERT INTO QUEST.QCBLUEPT_STMT030
   (
   NAME, 
   CREATOR, 
   SEQ, 
   STMT
   ) 
VALUES 
   (
   'QCEXEC', 
   'QUEST', 
   15, 
   '//QSDBG    DD SYSOUT=*'
   );

INSERT INTO QUEST.QCBLUEPT_STMT030
   (
   NAME, 
   CREATOR, 
   SEQ, 
   STMT
   ) 
VALUES 
   (
   'QCEXEC', 
   'QUEST', 
   16, 
   '//QSSCRIPT DD *'
   );

COMMIT WORK;

--DSNUTILB blueprint
INSERT INTO QUEST.QCBLUEPT_REPO030
   (
   NAME, 
   CREATOR, 
   DESCRIPTION, 
   TEMPLATE, 
   ONE_STMT_PER_STEP, 
   QUEST_STMTS, 
   UTIL_SYNTAX_VER, 
   UTIL_DELIM, 
   CREATEDTS, 
   MODIFIEDBY, 
   ALTEREDTS, 
   RESERVED
   ) 
VALUES 
   (
   'DSNUTILB',
   'QUEST', 
   'DSNUTILB execution', 
   1, 
   'N', 
   'N', 
   7010000, 
   ';', 
   CURRENT TIMESTAMP, 
   'QUEST', 
   CURRENT TIMESTAMP, 
   ''
   );

COMMIT WORK;

INSERT INTO QUEST.QCBLUEPT_STMT030
   (
   NAME, 
   CREATOR, 
   SEQ, 
   STMT
   ) 
VALUES 
   (
   'DSNUTILB',
   'QUEST', 
   1, 
   '//UTIL     EXEC  PGM=DSNUTILB,PARM=''%SSID'',REGION=0M'
   );

INSERT INTO QUEST.QCBLUEPT_STMT030
   (
   NAME, 
   CREATOR, 
   SEQ, 
   STMT
   ) 
VALUES 
   (
   'DSNUTILB',
   'QUEST', 
   2, 
   '%DB2LOADLIBS'
   );

INSERT INTO QUEST.QCBLUEPT_STMT030
   (
   NAME, 
   CREATOR, 
   SEQ, 
   STMT
   ) 
VALUES 
   (
   'DSNUTILB',
   'QUEST', 
   3, 
   '//SYSPRINT DD  SYSOUT=*'
   );

INSERT INTO QUEST.QCBLUEPT_STMT030
   (
   NAME, 
   CREATOR, 
   SEQ, 
   STMT
   ) 
VALUES 
   (
   'DSNUTILB',
   'QUEST', 
   4, 
   '//UTPRINT DD  SYSOUT=*'
   );

INSERT INTO QUEST.QCBLUEPT_STMT030
   (
   NAME, 
   CREATOR, 
   SEQ, 
   STMT
   ) 
VALUES 
   (
   'DSNUTILB',
   'QUEST', 
   5,
   '//SYSUDUMP DD  SYSOUT=*'
   );

INSERT INTO QUEST.QCBLUEPT_STMT030
   (
   NAME, 
   CREATOR, 
   SEQ, 
   STMT
   ) 
VALUES 
   (
   'DSNUTILB',
   'QUEST', 
   6,
   '//SYSIN    DD  *'
   );

COMMIT WORK;

--Mapping Template
INSERT INTO QUEST.QCTEMPLATE_REPO200 VALUES
 ('QCMAP', 'QUEST', 'QUEST',
  '&USERID..&SSID..&DB..&TS..T&TIME..MAP',
  '', '', '', '', '', 0, 0,
  '', '', '', '', 0, '', 0, 'D',
  100, 500, 'TRK', 0, 0, 0, 0,
  0, 'N', '', '',
  'Used for mapping dataset', ''
 );

COMMIT WORK;

--Discard Template
INSERT INTO QUEST.QCTEMPLATE_REPO200 VALUES
 ('QCDISC', 'QUEST', 'QUEST',
  '&USERID..&SSID..&DB..&TS..T&TIME..DISC',
  '', '', '', '', '', 0, 0,
  '', '', '', '', 0, '', 0, 'D',
  100, 500, 'TRK', 0, 0, 0, 0,
  0, 'N', '', '',
  'Used for discard dataset', ''
 );

COMMIT WORK;
