-- Scriptfile to create schema for DB2
-- Replace all occurrances of edu to the Table Prefix you will use in the
-- configured Scheduler resource.
-- Replace all occurrances of @SCHED_TABLESPACE@ with a valid tablespace that was 
-- created by the createTablesapceDb2.ddl script.
-- Process this script in the DB2 command line processor
-- Example:
--             db2 connect to SCHEDDB
--             db2 -tf createSchemaDb2.ddl
-- The schema assumes existing tablespaces that should have been
-- created before using createTablespaceDb2.ddl script.

CREATE TABLE "EDUTASK"  
(
	  "TASKID"                     CHAR(36)           NOT NULL , 
	  "VERSION"                    DOUBLE             NOT NULL , 
	  "ROW_VERSION"                INTEGER            NOT NULL , 
	  "TASKTYPE"                   INTEGER            NOT NULL , 
	  "TASKSUSPENDED"              SMALLINT           NOT NULL , 
	  "CANCELLED"                  SMALLINT           NOT NULL , 
	  "NEXTFIRETIME"               BIGINT             NOT NULL , 
	  "STARTBYINTERVAL"            VARCHAR(254)        , 
	  "STARTBYTIME"                BIGINT              , 
	  "VALIDFROMTIME"              BIGINT              , 
	  "VALIDTOTIME"                BIGINT              , 
	  "REPEATINTERVAL"             VARCHAR(254)        , 
	  "MAXREPEATS"                 INTEGER            NOT NULL , 
	  "REPEATSLEFT"                INTEGER            NOT NULL , 
	  "USERCALENDAR"               BLOB(5120)         LOGGED NOT COMPACT , 
	  "NOTIFICATIONSINK"           BLOB(5120)         LOGGED NOT COMPACT , 
	  "TARGETRUNNABLE"             BLOB(102400)       LOGGED NOT COMPACT , 
	  "USERCONTEXT"                BLOB(5120)         LOGGED NOT COMPACT , 
	  "NAME"                       VARCHAR(254)        , 
	  "AUTOPURGE"                  INTEGER            NOT NULL , 
	  "THREADCONTEXT"              BLOB(5120)         LOGGED NOT COMPACT , 
	  "FAILUREACTION"              INTEGER             , 
	  "MAXATTEMPTS"                INTEGER             , 
	  "QOS"                        INTEGER             , 
	  "PARTITIONID"                INTEGER      
)
 IN "EDUTABLES" ; 

ALTER TABLE "EDUTASK" 
	ADD PRIMARY KEY
		("TASKID");

CREATE UNIQUE INDEX "EDUTASK_IDX1" 
  ON "EDUTASK"
(
    "TASKID","ROW_VERSION"
); 
 

CREATE INDEX "EDUTASK_IDX2" 
  ON "EDUTASK"
(
    "NEXTFIRETIME" ASC, "REPEATSLEFT", "PARTITIONID"
);


