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

CREATE FUNCTION QUEST.QP310LOGDATA    
 (SUBSYS VARCHAR(4),                  
  SNAPSEQ INTEGER,                    
  OWNER VARCHAR(8),                   
  NAME VARCHAR(30)                    
 )                                    
  RETURNS TABLE                       
   (DUALACTS INTEGER,                 
    DSN1 VARCHAR(45),                 
    DSN2 VARCHAR(45),                 
    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                  
   )                                  
  SPECIFIC QUEST.QP310LOGDATA         
  EXTERNAL NAME QPLMB310              
  LANGUAGE C                          
  PARAMETER STYLE DB2SQL              
  SCRATCHPAD 512                      
  FINAL CALL                          
  DISALLOW PARALLEL                   
  COLLID %COLLID%                     
  DBINFO                              
  WLM ENVIRONMENT %MULTTCB%           
  ;                                   

COMMIT WORK;


CREATE FUNCTION QUEST.QP310VOLUTIL  
 (SSNAME VARCHAR(4),                
  FUNCCODE INTEGER,                 
  SNAPSEQ INTEGER,                  
  OWNER VARCHAR(8),                 
  NAME VARCHAR(30)                  
 )                                  
  RETURNS TABLE                     
   (VOLSER VARCHAR(7),              
    VOLCOUNT INTEGER,               
    TRKTOTAL INTEGER,               
    TRKFREE INTEGER,                
    PCTALLOC INTEGER,               
    TOTKB INTEGER,                  
    FREEKB INTEGER,                 
    RETCODE INTEGER,                
    REASONCODE INTEGER,             
    CURRTIME VARCHAR(8) FOR BIT DATA
   )                                
SPECIFIC QUEST.QP310VOLUTIL         
  EXTERNAL NAME QPVOL310            
  LANGUAGE C                        
  PARAMETER STYLE DB2SQL            
  SCRATCHPAD 512                    
  FINAL CALL                        
  DISALLOW PARALLEL                 
  COLLID %COLLID%                   
  DBINFO                            
  WLM ENVIRONMENT %MULTTCB%         
  ;                                 

COMMIT WORK;


-- Tablespace for Spotlight Recording Log
CREATE TABLESPACE QCRECLOG 
  IN QCDB                  
  USING STOGROUP %SGNAME%  
    PRIQTY 300             
    SECQTY 500             
    ERASE NO               
  FREEPAGE 0               
  PCTFREE 5                
  GBPCACHE CHANGED         
  TRACKMOD YES             
  DEFINE YES               
  LOCKSIZE ANY             
  LOCKMAX 0                
  CLOSE YES                
  COMPRESS NO              
  CCSID EBCDIC             
  MAXROWS 255;             

COMMIT WORK;


-- Tablespace for Spotlight Recording Data 
CREATE TABLESPACE QCRECTS 
  IN QCDB                 
  USING STOGROUP %SGNAME% 
    PRIQTY 3000           
    SECQTY 5000           
    ERASE NO              
  FREEPAGE 0              
  PCTFREE 5               
  GBPCACHE CHANGED        
  TRACKMOD YES            
  DEFINE YES              
  SEGSIZE 16              
  BUFFERPOOL BP32K        
  LOCKSIZE ANY            
  LOCKMAX 0               
  CLOSE YES               
  COMPRESS NO             
  CCSID EBCDIC            
  MAXROWS 255;            

COMMIT WORK;


-- Spotlight Recording Log
CREATE TABLE QUEST.QCRECORD_LOG_0300                         
 (RECORDING_OWNER          CHAR   (8)   NOT NULL             
 ,RECORDING_NAME           VARCHAR(30)  NOT NULL             
 ,RECORDING_DESC           VARCHAR(30)                       
 ,RECORDING_STATUS         CHAR   (1)   NOT NULL             
 ,RECORDING_RET_CODE       INTEGER      NOT NULL             
 ,REFRESH_RATE_FG          INTEGER      NOT NULL             
 ,REFRESH_RATE_BG          INTEGER      NOT NULL             
 ,START_TIME_SCHED         TIMESTAMP    NOT NULL WITH DEFAULT
 ,START_TIME_ACTUAL        TIMESTAMP    NOT NULL WITH DEFAULT
 ,END_TIME_SCHED           TIMESTAMP    NOT NULL WITH DEFAULT
 ,END_TIME_ACTUAL          TIMESTAMP    NOT NULL WITH DEFAULT
 ,REQUESTED_SAMPLES        INTEGER      NOT NULL             
 ,SAMPLE_COUNT             INTEGER      NOT NULL             
 ,RECORDING_SIZE_REQ       INTEGER      NOT NULL             
 ,RECORDING_SIZE_ACT       INTEGER      NOT NULL             
 ,RECORDING_OPTIONS        CHAR(30)     NOT NULL             
 ,PRIMARY KEY (RECORDING_OWNER, RECORDING_NAME)              
 )                                                           
  IN QCDB.QCRECLOG                                           
  ;                                                          

COMMIT WORK;


CREATE UNIQUE INDEX QUEST.QCRECORD_LIX_0300 
  ON QUEST.QCRECORD_LOG_0300                
 (RECORDING_OWNER ASC                       
 ,RECORDING_NAME  ASC                       
 )                                          
  USING STOGROUP %SGNAME%                    
    PRIQTY 300                              
    SECQTY 300                              
    ERASE NO                                
  FREEPAGE 5                                
  PCTFREE 10                                
  GBPCACHE CHANGED                          
  DEFINE YES                                
  CLOSE YES                                 
  DEFER YES                                 
  COPY NO                                   
  PIECESIZE 2G;                             

COMMIT WORK;


-- Spotlight Recording Data
CREATE TABLE QUEST.QCRECORDING_0300                            
 (RECORDING_OWNER          CHAR   (8)   NOT NULL               
 ,RECORDING_NAME           VARCHAR(30)  NOT NULL               
 ,SNAPSHOT_SEQUENCE        INTEGER      NOT NULL               
 ,SNAPSHOT_TIMESTAMP       TIMESTAMP    NOT NULL WITH DEFAULT  
 ,REFRESH_TYPE             CHAR(1)      NOT NULL               
 ,REFRESH_SEQUENCE         INTEGER      NOT NULL               
 ,RECORDING_DATA_TYP       INTEGER      NOT NULL               
 ,RECORDING_DATA_LEN       INTEGER      NOT NULL               
 ,RECORDING_DATA           VARCHAR(32000) NOT NULL FOR BIT DATA
 ,FOREIGN KEY (RECORDING_OWNER, RECORDING_NAME)                
              REFERENCES QUEST.QCRECORD_LOG_0300               
              ON DELETE CASCADE                                
 )                                                             
  IN QCDB.QCRECTS                                              
  ;                                                            

COMMIT WORK;

                                                               
CREATE UNIQUE INDEX QUEST.QCRECORDING_X_0300                   
  ON QUEST.QCRECORDING_0300                                    
 (RECORDING_OWNER   ASC                                        
 ,RECORDING_NAME    ASC                                        
 ,REFRESH_TYPE      ASC                                        
 ,SNAPSHOT_SEQUENCE ASC                                        
 ,REFRESH_SEQUENCE  ASC                                        
 )                                                             
  USING STOGROUP %SGNAME%                                      
    PRIQTY 300                                                 
    SECQTY 300                                                 
    ERASE NO                                                   
  FREEPAGE 5                                                   
  PCTFREE 10                                                   
  GBPCACHE CHANGED                                             
  DEFINE YES                                                   
  CLOSE YES                                                    
  DEFER YES                                                    
  COPY NO                                                      
  PIECESIZE 2G;                                                

COMMIT WORK;

