-- 
--  Quest Central - Object Lists
--  Repository creation script for DB2 on Linux, Windows and UNIX  
--

#DELIMITER ";";

#SYNC 10;

CREATE TABLE QUEST.QCOBJLIST_REPO045
 (NAME         VARCHAR(18)     NOT NULL,
  CREATOR      VARCHAR(8)      NOT NULL,
  DESCRIPTION  VARCHAR(50)     NOT NULL,
  SHARELEVEL   CHARACTER(1)    NOT NULL,
  CREATEDTS    TIMESTAMP       NOT NULL  DEFAULT CURRENT TIMESTAMP,
  MODIFIEDBY   VARCHAR(8)      NOT NULL,
  ALTEREDTS    TIMESTAMP       NOT NULL  DEFAULT CURRENT TIMESTAMP,
  RESERVED     VARCHAR(50)     NOT NULL  DEFAULT ''
 )
  DATA CAPTURE NONE
  IN QUESTSPACE;
  
#SYNC 20;  

ALTER TABLE QUEST.QCOBJLIST_REPO045
  LOCKSIZE ROW
  APPEND OFF
  NOT VOLATILE;
  
#SYNC 30;  

COMMENT ON TABLE QUEST.QCOBJLIST_REPO045
  IS 'QC OBJLIST REPO V01.0';
  
#SYNC 40;  

GRANT CONTROL ON TABLE QUEST.QCOBJLIST_REPO045 TO PUBLIC;

#SYNC 50;

CREATE UNIQUE INDEX QUEST.QCOBLST_REPO045_IX
  ON QUEST.QCOBJLIST_REPO045
 (NAME        ASC
 )
  PCTFREE 10
  DISALLOW REVERSE SCANS;

#SYNC 60;

CREATE TABLE QUEST.QCOBJLIST_STMT045
 (NAME           VARCHAR(18)     NOT NULL,
  SEQNO          SMALLINT        NOT NULL,
  ACTION         CHARACTER(1)    NOT NULL,
  RESULT_OBJECT  SMALLINT        NOT NULL,
  COPY           CHARACTER(1)    NOT NULL,
  SEARCH_OBJECT  SMALLINT        NOT NULL,
  SEARCH_FIELD1  VARCHAR(128)    NOT NULL,
  SEARCH_FIELD2  VARCHAR(128)    NOT NULL,
  PARTLEVEL      VARCHAR(256)    NOT NULL  DEFAULT '',
  RI             CHARACTER(1)    NOT NULL,
  AUX_OBJECTS    CHARACTER(1)    NOT NULL,
  RESERVED       VARCHAR(50)     NOT NULL  DEFAULT ''
 )
  DATA CAPTURE NONE
  IN QUESTSPACE;
  
#SYNC 70;  

ALTER TABLE QUEST.QCOBJLIST_STMT045
  LOCKSIZE ROW
  APPEND OFF
  NOT VOLATILE;
  
#SYNC 80;

COMMENT ON TABLE QUEST.QCOBJLIST_STMT045
  IS 'QC OBJLIST STMT V01.0';
  
#SYNC 90;  

GRANT CONTROL ON TABLE QUEST.QCOBJLIST_STMT045 TO PUBLIC;

#SYNC 100;

CREATE UNIQUE INDEX QUEST.QCOBLST_STMT045_IX
  ON QUEST.QCOBJLIST_STMT045
 (NAME        ASC,
  SEQNO       ASC
 )
  PCTFREE 10
  DISALLOW REVERSE SCANS;

#SYNC 110;

CREATE USER TEMPORARY TABLESPACE QCTEMPTS
  IN NODEGROUP  IBMDEFAULTGROUP
  PAGESIZE 4K
  MANAGED BY SYSTEM 
  USING
   ('QCTEMPTS'
   )
  EXTENTSIZE 32
  PREFETCHSIZE 16
  BUFFERPOOL IBMDEFAULTBP
  OVERHEAD 24.10
  TRANSFERRATE 0.90
  DROPPED TABLE RECOVERY OFF;

#SYNC 120;

GRANT USE OF TABLESPACE QCTEMPTS TO PUBLIC;

#SYNC 130;

-- Insert default object list definitions into Space Management repository

INSERT INTO QUEST.QCOBJLIST_REPO045 ( NAME, CREATOR, DESCRIPTION, SHARELEVEL, 
  CREATEDTS, MODIFIEDBY, ALTEREDTS, RESERVED )
  VALUES ( 'QC_ALL_OBJECTS', 'QUEST', 
    'Default QC list to return all objects', '', 
	current timestamp, 'QUEST', 
	current timestamp, '' ); 

INSERT INTO QUEST.QCOBJLIST_REPO045 ( NAME, CREATOR, DESCRIPTION, SHARELEVEL, 
  CREATEDTS, MODIFIEDBY, ALTEREDTS, RESERVED ) 
  VALUES ( 'QC_ALL_NO_SYSIBM', 'QUEST', 
    'Default QC list to return all except SYSIBM', '', 
	current timestamp, 'QUEST', 
	current timestamp, '' );
 
INSERT INTO QUEST.QCOBJLIST_REPO045 ( NAME, CREATOR, DESCRIPTION, SHARELEVEL, 
  CREATEDTS, MODIFIEDBY, ALTEREDTS, RESERVED )
  VALUES ( 'QC_QUEST_OBJECTS', 'QUEST', 
    'Default QC list to return all Quest objects', '', 
	current timestamp, 'QUEST', 
	current timestamp, '' );
	
INSERT INTO QUEST.QCOBJLIST_STMT045 ( NAME, SEQNO, ACTION, RESULT_OBJECT, COPY, 
  SEARCH_OBJECT, SEARCH_FIELD1, SEARCH_FIELD2, PARTLEVEL, RI, AUX_OBJECTS, RESERVED ) 
  VALUES ( 'QC_ALL_OBJECTS', 1, 'I', 7, '', 7, '%', '%', '', 'N', '', '' );

INSERT INTO QUEST.QCOBJLIST_STMT045 ( NAME, SEQNO, ACTION, RESULT_OBJECT, COPY, 
  SEARCH_OBJECT, SEARCH_FIELD1, SEARCH_FIELD2, PARTLEVEL, RI, AUX_OBJECTS, RESERVED ) 
  VALUES ( 'QC_ALL_OBJECTS', 2, 'I', 13, '', 13, '%', '%', '', 'N', '', '' );

INSERT INTO QUEST.QCOBJLIST_STMT045 ( NAME, SEQNO, ACTION, RESULT_OBJECT, COPY, 
  SEARCH_OBJECT, SEARCH_FIELD1, SEARCH_FIELD2, PARTLEVEL, RI, AUX_OBJECTS, RESERVED )
  VALUES ( 'QC_ALL_OBJECTS', 3, 'I', 14, '', 14, '%', '%', '', 'N', '', '' );

INSERT INTO QUEST.QCOBJLIST_STMT045 ( NAME, SEQNO, ACTION, RESULT_OBJECT, COPY, 
  SEARCH_OBJECT, SEARCH_FIELD1, SEARCH_FIELD2, PARTLEVEL, RI, AUX_OBJECTS, RESERVED )
  VALUES ( 'QC_ALL_NO_SYSIBM', 1, 'I', 7, '', 7, '%', '%', '', 'N', '', '' );

INSERT INTO QUEST.QCOBJLIST_STMT045 ( NAME, SEQNO, ACTION, RESULT_OBJECT, COPY, 
  SEARCH_OBJECT, SEARCH_FIELD1, SEARCH_FIELD2, PARTLEVEL, RI, AUX_OBJECTS, RESERVED )
  VALUES ( 'QC_ALL_NO_SYSIBM', 2, 'I', 13, '', 13, '%', '%', '', 'N', '', '' );

INSERT INTO QUEST.QCOBJLIST_STMT045 ( NAME, SEQNO, ACTION, RESULT_OBJECT, COPY, 
  SEARCH_OBJECT, SEARCH_FIELD1, SEARCH_FIELD2, PARTLEVEL, RI, AUX_OBJECTS, RESERVED )
  VALUES ( 'QC_ALL_NO_SYSIBM', 3, 'E', 7, '', 13, 'SYSIBM', '%', '', 'N', '', '' );

INSERT INTO QUEST.QCOBJLIST_STMT045 ( NAME, SEQNO, ACTION, RESULT_OBJECT, COPY, 
  SEARCH_OBJECT, SEARCH_FIELD1, SEARCH_FIELD2, PARTLEVEL, RI, AUX_OBJECTS, RESERVED )
  VALUES ( 'QC_ALL_NO_SYSIBM', 4, 'E', 13, '', 13, 'SYSIBM', '%', '', 'N', '', '' );

INSERT INTO QUEST.QCOBJLIST_STMT045 ( NAME, SEQNO, ACTION, RESULT_OBJECT, COPY, 
  SEARCH_OBJECT, SEARCH_FIELD1, SEARCH_FIELD2, PARTLEVEL, RI, AUX_OBJECTS, RESERVED )
  VALUES ( 'QC_ALL_NO_SYSIBM', 5, 'I', 14, '', 14, '%', '%', '', 'N', '', '' );

INSERT INTO QUEST.QCOBJLIST_STMT045 ( NAME, SEQNO, ACTION, RESULT_OBJECT, COPY, 
  SEARCH_OBJECT, SEARCH_FIELD1, SEARCH_FIELD2, PARTLEVEL, RI, AUX_OBJECTS, RESERVED )
  VALUES ( 'QC_ALL_NO_SYSIBM', 6, 'E', 14, '', 14, 'SYSIBM', '%', '', 'N', '', '' );

INSERT INTO QUEST.QCOBJLIST_STMT045 ( NAME, SEQNO, ACTION, RESULT_OBJECT, COPY, 
  SEARCH_OBJECT, SEARCH_FIELD1, SEARCH_FIELD2, PARTLEVEL, RI, AUX_OBJECTS, RESERVED )
  VALUES ( 'QC_QUEST_OBJECTS', 1, 'I', 13, '', 13, 'QUEST', '%', '', 'N', '', '' );

INSERT INTO QUEST.QCOBJLIST_STMT045 ( NAME, SEQNO, ACTION, RESULT_OBJECT, COPY, 
  SEARCH_OBJECT, SEARCH_FIELD1, SEARCH_FIELD2, PARTLEVEL, RI, AUX_OBJECTS, RESERVED )
  VALUES ( 'QC_QUEST_OBJECTS', 2, 'I', 7, '', 13, 'QUEST', '%', '', 'N', '', '' );

INSERT INTO QUEST.QCOBJLIST_STMT045 ( NAME, SEQNO, ACTION, RESULT_OBJECT, COPY, 
  SEARCH_OBJECT, SEARCH_FIELD1, SEARCH_FIELD2, PARTLEVEL, RI, AUX_OBJECTS, RESERVED )
  VALUES ( 'QC_QUEST_OBJECTS', 3, 'I', 14, '', 13, 'QUEST', '%', '', 'N', '', '' );

#SYNC 140;

#DELIMITER RESTORE;

#SYNC 150;