Java-Batch-DDL-example.txt

------------------------------------------------------------------------
-- Create the BATCH persistence table spaces
------------------------------------------------------------------------
CREATE TABLESPACE BATCHTS
	FREEPAGE 0
	PCTFREE 5
	GBPCACHE CHANGED
	TRACKMOD YES
	DEFINE YES
	SEGSIZE 32
	BUFFERPOOL BP32K
	LOCKSIZE ROW
	LOCKMAX SYSTEM
	CLOSE YES
	COMPRESS NO
	CCSID UNICODE
	MAXROWS 255
	IN ++database++;

CREATE LOB TABLESPACE LOBTS1
	GBPCACHE SYSTEM
	DEFINE YES
	LOCKSIZE LOB
	CLOSE YES
	IN ++database++;

CREATE LOB TABLESPACE LOBTS2
	GBPCACHE SYSTEM
	DEFINE YES
	LOCKSIZE LOB
	CLOSE YES
	IN ++database++;

CREATE LOB TABLESPACE LOBTS3
	GBPCACHE SYSTEM
	DEFINE YES
	LOCKSIZE LOB
	CLOSE YES
	IN ++database++;

CREATE LOB TABLESPACE LOBTS4
	GBPCACHE SYSTEM
	DEFINE YES
	LOCKSIZE LOB
	CLOSE YES
	IN ++database++;

------------------------------------------------------------------------
-- Create the BATCH persistence tables and indexes
------------------------------------------------------------------------
CREATE TABLE ++database++.STEPTHREADINSTANCE (
	PARTNUM INTEGER NOT NULL,
	STEPNAME VARCHAR(128) NOT NULL,
	THREADTYPE VARCHAR(31),
	CHECKPOINTDATA BLOB(64000),
	FK_JOBINSTANCEID BIGINT NOT NULL,
	FK_LATEST_STEPEXECID BIGINT NOT NULL,
	PARTITIONED SMALLINT DEFAULT 0 NOT NULL,
	PARTITIONPLANSIZE INTEGER, STARTCOUNT INTEGER,
	PRIMARY KEY (PARTNUM, STEPNAME, FK_JOBINSTANCEID)
	) CCSID UNICODE
	IN ++database++.BATCHTS;

CREATE AUXILIARY TABLE ++database++.STEPTHREADINSTANCEAUX IN ++database++.LOBTS1
	STORES ++database++.STEPTHREADINSTANCE COLUMN CHECKPOINTDATA;

CREATE UNIQUE INDEX ++database++.LOB1INDEX ON ++database++.STEPTHREADINSTANCEAUX;

CREATE TABLE ++database++.JOBEXECUTION (
	JOBEXECID BIGINT GENERATED ALWAYS AS IDENTITY NOT NULL,
	BATCHSTATUS INTEGER NOT NULL,
	CREATETIME TIMESTAMP NOT NULL,
	ENDTIME TIMESTAMP,
	EXECNUM INTEGER NOT NULL,
	EXITSTATUS VARCHAR(512),
	JOBPARAMETERS BLOB(64000),
	UPDATETIME TIMESTAMP,
	LOGPATH VARCHAR(512),
	RESTURL VARCHAR(512),
	SERVERID VARCHAR(256),
	STARTTIME TIMESTAMP,
	FK_JOBINSTANCEID BIGINT NOT NULL,
	PRIMARY KEY (JOBEXECID)
	) CCSID UNICODE
	IN ++database++.BATCHTS;

CREATE AUXILIARY TABLE ++database++.JOBEXECUTIONAUX IN ++database++.LOBTS2
	STORES ++database++.JOBEXECUTION COLUMN JOBPARAMETERS;

CREATE UNIQUE INDEX ++database++.LOB2INDEX ON ++database++.JOBEXECUTIONAUX;

CREATE TABLE ++database++.STEPTHREADEXECUTION (
	STEPEXECID BIGINT GENERATED ALWAYS AS IDENTITY NOT NULL,
	THREADTYPE VARCHAR(31),
	BATCHSTATUS INTEGER NOT NULL,
	M_COMMIT BIGINT NOT NULL,
	ENDTIME TIMESTAMP,
	EXITSTATUS VARCHAR(512),
	M_FILTER BIGINT NOT NULL,
	INTERNALSTATUS INTEGER NOT NULL,
	PARTNUM INTEGER NOT NULL,
	USERDATA BLOB(64000),
	M_PROCESSSKIP BIGINT NOT NULL,
	M_READ BIGINT NOT NULL,
	M_READSKIP BIGINT NOT NULL,
	M_ROLLBACK BIGINT NOT NULL,
	STARTTIME TIMESTAMP,
	STEPNAME VARCHAR(128) NOT NULL,
	M_WRITE BIGINT NOT NULL,
	M_WRITESKIP BIGINT NOT NULL,
	FK_JOBEXECID BIGINT NOT NULL,
	FK_TOPLVL_STEPEXECID BIGINT,
	ISPARTITIONEDSTEP SMALLINT DEFAULT 0,
	PRIMARY KEY (STEPEXECID)
	) CCSID UNICODE
	IN ++database++.BATCHTS;

CREATE AUXILIARY TABLE ++database++.STEPTHREADEXECAUX IN ++database++.LOBTS3
	STORES ++database++.STEPTHREADEXECUTION COLUMN USERDATA;

CREATE UNIQUE INDEX ++database++.LOB3INDEX ON ++database++.STEPTHREADEXECAUX;

CREATE TABLE ++database++.JOBINSTANCE (
	JOBINSTANCEID BIGINT GENERATED ALWAYS AS IDENTITY NOT NULL,
	AMCNAME VARCHAR(512),
	BATCHSTATUS INTEGER NOT NULL,
	CREATETIME TIMESTAMP NOT NULL,
	EXITSTATUS VARCHAR(512),
	INSTANCESTATE INTEGER NOT NULL,
	JOBNAME VARCHAR(256),
	JOBXMLNAME VARCHAR(128),
	JOBXML BLOB(64000),
	NUMEXECS INTEGER NOT NULL,
	RESTARTON VARCHAR(128),
	SUBMITTER VARCHAR(256),
	PRIMARY KEY (JOBINSTANCEID)
	) CCSID UNICODE
	IN ++database++.BATCHTS;

CREATE AUXILIARY TABLE ++database++.JOBINSTANCEAUX IN ++database++.LOBTS4
	STORES ++database++.JOBINSTANCE COLUMN JOBXML;

CREATE UNIQUE INDEX ++database++.LOB4INDEX ON ++database++.JOBINSTANCEAUX;

CREATE INDEX ++database++.SQL160728143351870 ON ++database++.JOBEXECUTION (FK_JOBINSTANCEID ASC);

CREATE UNIQUE INDEX ++database++.SQL160728143350810 ON ++database++.JOBEXECUTION (JOBEXECID ASC);

CREATE INDEX ++database++.SQL160728143352150 ON ++database++.STEPTHREADEXECUTION (FK_JOBEXECID ASC);

CREATE UNIQUE INDEX ++database++.SQL160728143351430 ON ++database++.STEPTHREADEXECUTION (FK_JOBEXECID ASC, STEPNAME ASC, PARTNUM ASC);

CREATE INDEX ++database++.SQL160728143352010 ON ++database++.STEPTHREADEXECUTION (FK_TOPLVL_STEPEXECID ASC);

CREATE INDEX ++database++.SQL160728143351600 ON ++database++.STEPTHREADINSTANCE (FK_LATEST_STEPEXECID ASC);

CREATE UNIQUE INDEX ++database++.SQL160728143351230 ON ++database++.JOBINSTANCE (JOBINSTANCEID ASC);

CREATE UNIQUE INDEX ++database++.SQL160728143350580 ON ++database++.STEPTHREADINSTANCE (PARTNUM ASC, STEPNAME ASC, FK_JOBINSTANCEID ASC);

CREATE UNIQUE INDEX ++database++.SQL160728143351010 ON ++database++.STEPTHREADEXECUTION (STEPEXECID ASC);

CREATE INDEX ++database++.SQL160728143351740 ON ++database++.STEPTHREADINSTANCE (FK_JOBINSTANCEID ASC);

ALTER TABLE ++database++.STEPTHREADEXECUTION ADD CONSTRAINT STPTHRADEXECUTION0
	UNIQUE (FK_JOBEXECID, STEPNAME, PARTNUM);

ALTER TABLE ++database++.STEPTHREADINSTANCE ADD CONSTRAINT STPTHRFKLTSTSTPXCD
	FOREIGN KEY (FK_LATEST_STEPEXECID)
	REFERENCES ++database++.STEPTHREADEXECUTION (STEPEXECID);

ALTER TABLE ++database++.STEPTHREADINSTANCE ADD CONSTRAINT STPTHRDNFKJBNSTNCD
	FOREIGN KEY (FK_JOBINSTANCEID)
	REFERENCES ++database++.JOBINSTANCE (JOBINSTANCEID);

ALTER TABLE ++database++.JOBEXECUTION ADD CONSTRAINT JBXCTNFKJBNSTNCEID
	FOREIGN KEY (FK_JOBINSTANCEID)
	REFERENCES ++database++.JOBINSTANCE (JOBINSTANCEID);

ALTER TABLE ++database++.STEPTHREADEXECUTION ADD CONSTRAINT STPTHFKTPLVLSTPXCD
	FOREIGN KEY (FK_TOPLVL_STEPEXECID)
	REFERENCES ++database++.STEPTHREADEXECUTION (STEPEXECID)
	ON DELETE NO ACTION;

ALTER TABLE ++database++.STEPTHREADEXECUTION ADD CONSTRAINT STPTHRDXCTNFKJBXCD
	FOREIGN KEY (FK_JOBEXECID)
	REFERENCES ++database++.JOBEXECUTION (JOBEXECID);