--
-- Upgrade DDL for Quest Central 3.1 to 4.5 Install
--


-- Tablespace: Spotlight Volume Space Info 

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

COMMIT ;                               
                                         
                                                               
-- Table: Spotlight Volume Space Info 

CREATE TABLE QUEST.QPVOLTB                  
  ( MVSID    CHAR(4)   FOR SBCS DATA NOT NULL WITH DEFAULT
  , DB2ID    CHAR(8)   FOR SBCS DATA NOT NULL WITH DEFAULT
  , VOLID    CHAR(6)   FOR SBCS DATA NOT NULL WITH DEFAULT
  , VOLCOUNT INT       NOT NULL WITH DEFAULT
  , TRKTOTAL INT       NOT NULL WITH DEFAULT
  , TRKFREE  INT       NOT NULL WITH DEFAULT
  , PCTALLOC INT       NOT NULL WITH DEFAULT
  , TOTKB    INT       NOT NULL WITH DEFAULT
  , FREEKB   INT       NOT NULL WITH DEFAULT
  , RETCDE   INT       NOT NULL WITH DEFAULT
  , REASCDE  INT       NOT NULL WITH DEFAULT
  , CURRTIME CHAR(8)   FOR BIT DATA NOT NULL WITH DEFAULT
  , DB2COUNT INTEGER
  , DB2INT   VARCHAR(257) FOR SBCS DATA NOT NULL WITH DEFAULT
  , SYSINT   VARCHAR(129) FOR SBCS DATA NOT NULL WITH DEFAULT
  )                                                     
  IN QCDB.QPVOLTS                                    
  DATA CAPTURE NONE
  AUDIT NONE
  CCSID EBCDIC
  ;                                                     

COMMIT ;                                                     
                                        

                                                               
-- Index: Spotlight Volume Space Info 

CREATE TYPE 2 UNIQUE INDEX
  QUEST.QPVOLTB_IX1
  ON QUEST.QPVOLTB
  ( VOLID ASC
  )
  USING STOGROUP %SGNAME%
        PRIQTY   1440
        SECQTY   1440
        ERASE    NO
  GBPCACHE   ALL
  BUFFERPOOL %IXBUFFPOOL%
  CLOSE      NO
  ;                                                     

COMMIT ;                                                     



-- Function: Spotlight Volume Space Info 

CREATE FUNCTION QUEST.QP450VOLUTIL
  ( SSNAME    VARCHAR(4) FOR SBCS DATA CCSID EBCDIC
  , FUNCCODE  INTEGER
  , SNAPSEQ   INTEGER
  , OWNER     VARCHAR(8) FOR SBCS DATA CCSID EBCDIC
  , NAME      VARCHAR(30) FOR SBCS DATA CCSID EBCDIC
  )
  RETURNS TABLE
  ( VOLSER   VARCHAR(7) FOR SBCS DATA CCSID EBCDIC
  , VOLCOUNT INTEGER
  , TRKTOTAL INTEGER
  , TRKFREE  INTEGER
  , PCTALLOC INTEGER
  , TOTKB    INTEGER
  , FREEKB   INTEGER
  , RETCODE  INTEGER
  , REASONCODE INTEGER
  , CURRTIME VARCHAR(8) FOR BIT DATA CCSID EBCDIC
  , DB2COUNT INTEGER
  , DB2INT   VARCHAR(257) FOR SBCS DATA CCSID EBCDIC
  , SYSINT   VARCHAR(129) FOR SBCS DATA CCSID EBCDIC
  )
  SPECIFIC QUEST.QP450VOLUTIL
  EXTERNAL NAME QPVOL450
  READS SQL DATA
  LANGUAGE C
  PARAMETER STYLE DB2SQL
  NOT DETERMINISTIC
  FENCED
  RETURNS NULL ON NULL INPUT
  EXTERNAL ACTION
  SCRATCHPAD 512
  FINAL CALL
  DISALLOW PARALLEL
  DBINFO
  COLLID %COLLID%
  WLM ENVIRONMENT %MULTTCB%
  STAY RESIDENT YES
  PROGRAM TYPE SUB
  SECURITY USER
  ;

COMMIT;



-- Tablespace: Spotlight Log Space Info 

CREATE TABLESPACE QPLOGTS 
  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 NO
  COMPRESS NO
  CCSID EBCDIC
  MAXROWS 1
  ;                               

COMMIT ;                               
                                         
                                                               

-- Table: Spotlight Log Space Info 

CREATE TABLE QUEST.QPLOGTB                  
  ( MVSID    CHAR(4)  FOR SBCS DATA NOT NULL WITH DEFAULT
  , DB2ID    CHAR(8)  FOR SBCS DATA NOT NULL WITH DEFAULT
  , DUALACTS INTEGER  NOT NULL WITH DEFAULT                
  , DSN1     VARCHAR(45) FOR SBCS DATA NOT NULL WITH DEFAULT
  , DSN2     VARCHAR(45) FOR SBCS DATA NOT NULL WITH DEFAULT
  , DSN1UTIL INTEGER  NOT NULL WITH DEFAULT                
  , DSN2UTIL INTEGER  NOT NULL WITH DEFAULT                
  , NUMACTS  INTEGER  NOT NULL WITH DEFAULT                 
  , NUMFULL  INTEGER  NOT NULL WITH DEFAULT                 
  , STARTSTCK CHAR(8) FOR BIT DATA NOT NULL WITH DEFAULT
  , STARTRBA CHAR(8)  FOR BIT DATA NOT NULL WITH DEFAULT 
  , LASTRBA  CHAR(8)  FOR BIT DATA NOT NULL WITH DEFAULT  
  , CURRTIME CHAR(8)  FOR BIT DATA NOT NULL WITH DEFAULT 
  , RETCDE   INT      NOT NULL WITH DEFAULT 
  , REASCDE  INT      NOT NULL WITH DEFAULT 
  , DSN1USED INTEGER  NOT NULL WITH DEFAULT
  , DSN1TOT  INTEGER  NOT NULL WITH DEFAULT
  , DSN2USED INTEGER  NOT NULL WITH DEFAULT
  , DSN2TOT  INTEGER  NOT NULL WITH DEFAULT
  )                                                     
  IN QCDB.QPLOGTS                                    
  DATA CAPTURE NONE
  AUDIT NONE
  CCSID EBCDIC
  ;                                                     

COMMIT ;                                                     



-- Function: Spotlight Log Space Info 

CREATE FUNCTION QUEST.QP450LOGDATA
  ( SUBSYS  VARCHAR(4)  FOR SBCS DATA CCSID EBCDIC                   
  , SNAPSEQ INTEGER                     
  , OWNER   VARCHAR(8)  FOR SBCS DATA CCSID EBCDIC                    
  , NAME    VARCHAR(30) FOR SBCS DATA CCSID EBCDIC                    
  )                                    
  RETURNS TABLE
  ( DUALACTS INTEGER                 
  , DSN1     VARCHAR(45) FOR SBCS DATA CCSID EBCDIC                  
  , DSN2     VARCHAR(45) FOR SBCS DATA CCSID EBCDIC                  
  , DSN1UTIL INTEGER                  
  , DSN2UTIL INTEGER                  
  , NUMACTS  INTEGER                   
  , NUMFULL  INTEGER                   
  , STARTSTCK VARCHAR(8) FOR BIT DATA 
  , STARTRBA VARCHAR(8) FOR BIT DATA  
  , LASTRBA  VARCHAR(8) FOR BIT DATA   
  , CURRTIME VARCHAR(8) FOR BIT DATA  
  , RETCODE  INTEGER                   
  , REASCODE INTEGER                  
  , MVSID    CHAR(5) FOR SBCS DATA CCSID EBCDIC                  
  , DB2ID    CHAR(5) FOR SBCS DATA CCSID EBCDIC                  
  , DSN1USED INTEGER
  , DSN1TOT  INTEGER
  , DSN2USED INTEGER
  , DSN2TOT  INTEGER
  )                                  
  SPECIFIC QUEST.QP450LOGDATA         
  EXTERNAL NAME QPLMB450              
  READS SQL DATA
  LANGUAGE C
  PARAMETER STYLE DB2SQL
  NOT DETERMINISTIC
  FENCED
  RETURNS NULL ON NULL INPUT
  EXTERNAL ACTION
  SCRATCHPAD 512
  FINAL CALL
  DISALLOW PARALLEL
  DBINFO
  COLLID %COLLID%
  WLM ENVIRONMENT %MULTTCB%
  STAY RESIDENT YES
  PROGRAM TYPE SUB
  SECURITY USER
  ;

COMMIT;

                                                             
-- Function: Spotlight IFI  

CREATE FUNCTION QUEST.QP450READSDIR
  ( REQTYPE0 INTEGER
  , REQTYPE1 INTEGER
  , REQTYPE2 INTEGER
  , REQTYPE3 INTEGER
  , REQTYPE4 INTEGER
  , REQTYPE5 INTEGER
  , REQTYPE6 INTEGER
  , REQTYPE7 INTEGER
  , REQTYPE8 INTEGER
  , REQTYPE9 INTEGER
  , REQTYPE10 INTEGER
  , REQTYPE11 INTEGER
  , REQTYPE12 INTEGER
  , REQTYPE13 VARCHAR(8)
  )
  RETURNS TABLE
  ( RECTYPE INTEGER
  , RECLENG INTEGER
  , TRACREC VARCHAR(32000) FOR BIT DATA CCSID EBCDIC
  )
  SPECIFIC QUEST.QP450READSDIR
  EXTERNAL NAME QPREAD45
  LANGUAGE C
  PARAMETER STYLE DB2SQL
  NOT DETERMINISTIC
  FENCED
  RETURNS NULL ON NULL INPUT
  READS SQL DATA
  EXTERNAL ACTION
  SCRATCHPAD 512
  NO FINAL CALL
  DISALLOW PARALLEL
  NO DBINFO
  COLLID %COLLID% 
  WLM ENVIRONMENT %MULTTCB%
  STAY RESIDENT YES
  PROGRAM TYPE SUB
  SECURITY DB2
  ;

COMMIT;


-- Stored Procedure: QC Object List Expansion

CREATE PROCEDURE QUEST.QC45_OBJLIST_390
 (IN FUNCTION INTEGER,
  IN LISTNAME CHARACTER(18) FOR SBCS DATA CCSID EBCDIC,
  IN TRACE SMALLINT,
  OUT RETCODE INTEGER
 ) 
  DYNAMIC RESULT SETS 1
  EXTERNAL NAME QCOBJLST
  LANGUAGE C
  PARAMETER STYLE GENERAL WITH NULLS
  NOT DETERMINISTIC
  FENCED
  MODIFIES SQL DATA
  NO DBINFO
  CALLED ON NULL INPUT
  PROGRAM TYPE SUB
  COLLID %COLLID%
  WLM ENVIRONMENT %MULTTCB%
  STAY RESIDENT NO
  SECURITY USER
  RUN OPTIONS 'STACK(40960,40960,BELOW,KEEP,40960,40960)'
  COMMIT ON RETURN NO;

COMMIT WORK;

-- Stored Procedure: QC Object List Expansion

CREATE PROCEDURE QUEST.QC450RETHLQ
 (OUT HLQ VARCHAR(44) FOR SBCS DATA CCSID EBCDIC
 ) 
  EXTERNAL NAME QPRETHLQ
  LANGUAGE C
  PARAMETER STYLE DB2SQL
  NOT DETERMINISTIC
  FENCED
  MODIFIES SQL DATA
  NO DBINFO
  CALLED ON NULL INPUT
  PROGRAM TYPE SUB
  COLLID %COLLID%
  WLM ENVIRONMENT %MULTTCB%
  STAY RESIDENT NO
  SECURITY USER
  COMMIT ON RETURN NO;


-- 
--  Quest Central - Object Lists Tables
--  Repository creation script for DB2 on OS/390  
--

#DELIMITER ";";


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

COMMIT WORK;

CREATE TABLE QUEST.QCOBJLIST_REPO045
 (NAME         VARCHAR(18) FOR SBCS DATA     NOT NULL,
  CREATOR      VARCHAR(8) FOR SBCS DATA      NOT NULL,
  DESCRIPTION  VARCHAR(50) FOR SBCS DATA     NOT NULL,
  SHARELEVEL   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.QCOBJLST
  DATA CAPTURE NONE
  AUDIT NONE
  CCSID EBCDIC;

COMMIT WORK;

CREATE UNIQUE INDEX QUEST.QCOBJLIST_REPO045
  ON QUEST.QCOBJLIST_REPO045
 (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 TABLE QUEST.QCOBJLIST_STMT045
 (NAME           VARCHAR(18) FOR SBCS DATA     NOT NULL,
  SEQNO          SMALLINT                      NOT NULL,
  ACTION         CHARACTER(1) FOR SBCS DATA    NOT NULL,
  RESULT_OBJECT  SMALLINT                      NOT NULL,
  COPY           CHARACTER(1) FOR SBCS DATA    NOT NULL,
  SEARCH_OBJECT  SMALLINT                      NOT NULL,
  SEARCH_FIELD1  VARCHAR(18) FOR SBCS DATA     NOT NULL,
  SEARCH_FIELD2  VARCHAR(18) FOR SBCS DATA     NOT NULL,
  PARTLEVEL      VARCHAR(256) FOR SBCS DATA    NOT NULL  DEFAULT,
  RI             CHARACTER(1) FOR SBCS DATA    NOT NULL,
  AUX_OBJECTS    CHARACTER(1) FOR SBCS DATA    NOT NULL,
  RESERVED       VARCHAR(50) FOR SBCS DATA     NOT NULL  DEFAULT
 )
  IN QCDB.QCOBJLST
  DATA CAPTURE NONE
  AUDIT NONE
  CCSID EBCDIC;

COMMIT WORK;


CREATE UNIQUE INDEX QUEST.QCOBJLIST_STMT040
  ON QUEST.QCOBJLIST_STMT045
 (NAME   ASC,
  SEQNO  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 DATABASE QCTEMPDB
--  STOGROUP %SGNAME%
--  BUFFERPOOL %TSBUFFPOOL%
--  INDEXBP %IXBUFFPOOL%
--  AS TEMP;

--COMMIT WORK;

--CREATE TABLESPACE QCTEMPTS
--  IN QCTEMPDB
--  USING STOGROUP %SGNAME%
--    PRIQTY 100
--    SECQTY 200
--    ERASE NO
--  SEGSIZE 4
--  BUFFERPOOL %TSBUFFPOOL%
--  LOCKMAX SYSTEM
--  CLOSE YES
--  MAXROWS 255;

--COMMIT WORK;

-- End of generated script for Quest Central - Object Lists

-- 
--  Quest Central - Reports
--  Repository creation script for DB2 on OS/390  
--

#DELIMITER ";";

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

COMMIT WORK;

CREATE TABLE QUEST.QCREPORT_REPO045
 (RPT_NAME       CHARACTER(30) FOR SBCS DATA   NOT NULL,
  RPT_CREATOR    VARCHAR(128) FOR SBCS DATA    NOT NULL DEFAULT CURRENT SQLID,
  RPT_TIMESTAMP  TIMESTAMP                     NOT NULL,
  FILENAME       VARCHAR(200) FOR SBCS DATA,
  FILE_TYPE      VARCHAR(100) FOR SBCS DATA,
  ROWID          ROWID                         NOT NULL  GENERATED ALWAYS
 )
  IN QCDB.QCRPT
  DATA CAPTURE NONE
  AUDIT NONE
  CCSID EBCDIC;

COMMIT WORK;

CREATE UNIQUE INDEX QUEST.QCREPORT_REPO_IX
  ON QUEST.QCREPORT_REPO045
 (RPT_NAME        ASC,
  RPT_CREATOR     ASC,
  RPT_TIMESTAMP   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 TABLE QUEST.QCREPORT_DETAIL045
 (RPT_NAME       CHARACTER(30) FOR SBCS DATA    NOT NULL,
  RPT_CREATOR    VARCHAR(128) FOR SBCS DATA     NOT NULL DEFAULT CURRENT SQLID,
  RPT_TIMESTAMP  TIMESTAMP                      NOT NULL,
  FILENAME       VARCHAR(200) FOR SBCS DATA,
  SEQUENCE       INTEGER,
  RPT_DATA       CLOB(65536) FOR SBCS DATA      NOT NULL,
  ROWID          ROWID                          NOT NULL  GENERATED ALWAYS
 )
  IN QCDB.QCRPT;

COMMIT WORK;

CREATE INDEX QUEST.QCREPORT_DET_IX
  ON QUEST.QCREPORT_DETAIL045
 (RPT_NAME        ASC,
  RPT_CREATOR     ASC,
  RPT_TIMESTAMP   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 YES
  PIECESIZE 2097152 K;

COMMIT WORK;

--- AUXILIARY OBJECTS

-- Tablespace

CREATE LOB TABLESPACE QCLOBAUX
  IN QCDB
  USING STOGROUP %SGNAME%
    PRIQTY 200
    SECQTY 200
    ERASE NO
  GBPCACHE CHANGED
  LOG YES
  DEFINE YES
  DSSIZE 4 G
  BUFFERPOOL %TSBUFFPOOL%
  LOCKSIZE ANY
  LOCKMAX SYSTEM
  CLOSE YES;

COMMIT WORK;

-- Table

CREATE AUXILIARY TABLE QUEST.QC_LOBAUXTB045
  IN QCDB.QCLOBAUX
  STORES QUEST.QCREPORT_DETAIL045 
  COLUMN RPT_DATA;

COMMIT WORK;

-- Index

CREATE UNIQUE INDEX QUEST.QCLOBAUX045IX
  ON QUEST.QC_LOBAUXTB045
  USING STOGROUP %SGNAME%
    PRIQTY 12
    SECQTY 12
    ERASE NO
  FREEPAGE 0
  PCTFREE 10
  GBPCACHE CHANGED
  DEFINE YES
  BUFFERPOOL %IXBUFFPOOL%
  CLOSE YES
  COPY NO
  PIECESIZE 2097152 K;

COMMIT WORK;


