11.10.2　HiRDB用のSQLファイル

HiRDB用に提供するSQLファイル（jsr352-hirdb.sql）の内容を次に示します。

DROP PROCEDURE SEQUENTIALIDINFO_NEXTID;
DROP TABLE SEQUENTIALIDINFO;
DROP INDEX CHK_INDEX;
DROP TABLE CHECKPOINTDATA;
DROP TABLE STEPSTATUS;
DROP TABLE JOBSTATUS;
DROP TABLE STEPEXECUTIONINSTANCEDATA;
DROP TABLE EXECUTIONINSTANCEDATA;
DROP TABLE JOBINSTANCEDATA;
 
CREATE TABLE JOBINSTANCEDATA(
  jobinstanceid   DECIMAL(19) PRIMARY KEY,
  name            VARCHAR(512), 
  apptag          VARCHAR(512)
);
 
CREATE TABLE EXECUTIONINSTANCEDATA(
  jobexecid       DECIMAL(19) PRIMARY KEY,
  jobinstanceid   DECIMAL(19),
  createtime      TIMESTAMP(4),
  starttime       TIMESTAMP(4),
  endtime         TIMESTAMP(4),
  updatetime      TIMESTAMP(4),
  "parameters"    BINARY(65536),
  batchstatus     VARCHAR(512),
  exitstatus      VARCHAR(512),
  CONSTRAINT JOBINST_JOBEXEC_FK FOREIGN KEY (jobinstanceid) REFERENCES JOBINSTANCEDATA
);
 
CREATE TABLE STEPEXECUTIONINSTANCEDATA(
  stepexecid          DECIMAL(19) PRIMARY KEY,
  jobexecid           DECIMAL(19),
  batchstatus         VARCHAR(512),
  exitstatus          VARCHAR(512),
  stepname            VARCHAR(512),
  readcount           DECIMAL(11),
  writecount          DECIMAL(11),
  commitcount         DECIMAL(11),
  rollbackcount       DECIMAL(11),
  readskipcount       DECIMAL(11),
  processskipcount    DECIMAL(11),
  filtercount         DECIMAL(11),
  writeskipcount      DECIMAL(11),
  startTime           TIMESTAMP(4),
  endTime             TIMESTAMP(4),
  persistentData      BINARY(65536),
  CONSTRAINT JOBEXEC_STEPEXEC_FK FOREIGN KEY (jobexecid) REFERENCES EXECUTIONINSTANCEDATA
);
 
CREATE TABLE JOBSTATUS (
  id  DECIMAL(19) PRIMARY KEY,
  obj BINARY(65536),
  CONSTRAINT JOBSTATUS_JOBINST_FK FOREIGN KEY (id) REFERENCES JOBINSTANCEDATA ON DELETE CASCADE
);
 
CREATE TABLE STEPSTATUS(
  id  DECIMAL(19) PRIMARY KEY,
  obj BINARY(65536),
  CONSTRAINT STEPSTATUS_STEPEXEC_FK FOREIGN KEY (id) REFERENCES STEPEXECUTIONINSTANCEDATA ON DELETE CASCADE
);
 
CREATE TABLE CHECKPOINTDATA(
  id  VARCHAR(512),
  obj BINARY(65536)
);
 
CREATE INDEX CHK_INDEX ON CHECKPOINTDATA(id);
 
CREATE FIX TABLE SEQUENTIALIDINFO(
  name CHAR(64),
  id   DECIMAL(19)
) WITHOUT ROLLBACK;
INSERT INTO SEQUENTIALIDINFO VALUES('jobinstanceid', 1);
INSERT INTO SEQUENTIALIDINFO VALUES('jobexecid', 1);
INSERT INTO SEQUENTIALIDINFO VALUES('stepexecid', 1);
 
CREATE PROCEDURE SEQUENTIALIDINFO_NEXTID(
  IN  seq_name CHAR(64),
  OUT next_id  DECIMAL(19)
)
  BEGIN
    DECLARE update_id DECIMAL(19);
    DECLARE cr1 CURSOR FOR
      SELECT id FROM SEQUENTIALIDINFO
        WHERE name=seq_name
        FOR UPDATE;
    OPEN cr1;
    FETCH cr1 INTO update_id;
    SET next_id=update_id;
    UPDATE SEQUENTIALIDINFO SET id=update_id+1
      WHERE CURRENT OF cr1;
    CLOSE cr1;
  END
END_PROC;

ページの先頭へ