connect 'jdbc:derby:<DERBY_INSTALL_DIR>\Isight;create=true';
CREATE SCHEMA FIPERACS;

CREATE TABLE FIPERACS.ACCESSCONTROLLIST ( 
	PRIORITY             INTEGER NOT NULL,
	RESOURCEID           VARCHAR( 250 ) NOT NULL,
	ACCESSID             VARCHAR( 20 ),
	ACCESSLEVEL          VARCHAR( 250 ),
	ACCESSTYPE           VARCHAR( 250 ),
	CONSTRAINT SQL120828150347410 PRIMARY KEY ( PRIORITY, RESOURCEID )
 );

CREATE INDEX ACL_INDEX1 ON FIPERACS.ACCESSCONTROLLIST ( ACCESSID );

CREATE INDEX ACL_INDEX2 ON FIPERACS.ACCESSCONTROLLIST ( ACCESSID, ACCESSLEVEL );

CREATE INDEX ACL_INDEX3 ON FIPERACS.ACCESSCONTROLLIST ( RESOURCEID );

CREATE TABLE FIPERACS.ACSPKIDESCRIPTOR ( 
	PRIMKEY              VARCHAR( 20 ) NOT NULL,
	CREATEDTIME          BIGINT,
	PRIVATEKEY           BLOB NOT NULL,
	PUBLICKEY            BLOB NOT NULL,
	CONSTRAINT SQL120828150347770 PRIMARY KEY ( PRIMKEY )
 );

CREATE TABLE FIPERACS.AFFINITIES ( 
	PRIMKEY              VARCHAR( 20 ) NOT NULL,
	HOSTNAME             VARCHAR( 250 ) NOT NULL,
	OS                   VARCHAR( 50 ) NOT NULL,
	OSARCH               VARCHAR( 50 ) NOT NULL,
	OSNAME               VARCHAR( 50 ) NOT NULL,
	OSVER                VARCHAR( 50 ) NOT NULL,
	OTHERS               VARCHAR( 250 ) NOT NULL,
	STATIONS             VARCHAR( 100 ) NOT NULL,
	CONSTRAINT SQL120828150347910 PRIMARY KEY ( PRIMKEY )
 );

CREATE INDEX AFFINITIES_IDX2 ON FIPERACS.AFFINITIES ( OS );

CREATE INDEX AFFINITIES_IDX3 ON FIPERACS.AFFINITIES ( OS, OSNAME );

CREATE TABLE FIPERACS.AFFINITYGROUPBEAN ( 
	WORKFLOWID           VARCHAR( 20 ) NOT NULL,
	GROUPNAME            VARCHAR( 50 ) NOT NULL,
	JOBID                VARCHAR( 20 ),
	STATIONNAME          VARCHAR( 250 ),
	CONSTRAINT SQL120828150348210 PRIMARY KEY ( WORKFLOWID, GROUPNAME )
 );

CREATE INDEX AFFGRP_IDX2 ON FIPERACS.AFFINITYGROUPBEAN ( WORKFLOWID, STATIONNAME, GROUPNAME );

CREATE INDEX AFFGRP_IDX3 ON FIPERACS.AFFINITYGROUPBEAN ( WORKFLOWID );

CREATE INDEX AFFGRP_IDX4 ON FIPERACS.AFFINITYGROUPBEAN ( JOBID );

CREATE TABLE FIPERACS.APPROXDATA ( 
	PRIMKEY              VARCHAR( 20 ) NOT NULL,
	APPROXID             VARCHAR( 130 ),
	APPROXOBJ            BLOB,
	COMPONENTID          VARCHAR( 130 ),
	JOBID                VARCHAR( 20 ),
	CONSTRAINT SQL120828150348580 PRIMARY KEY ( PRIMKEY )
 );

CREATE INDEX APPROXDATA_INDEX1 ON FIPERACS.APPROXDATA ( JOBID, COMPONENTID );

CREATE INDEX APPROXDATA_INDEX2 ON FIPERACS.APPROXDATA ( JOBID );

CREATE TABLE FIPERACS.BULLETINBOARD ( 
	NOTEID               VARCHAR( 20 ) NOT NULL,
	BODY                 VARCHAR( 1000 ),
	JOBID                VARCHAR( 20 ),
	PUBDATE              BIGINT,
	SCOPEID              VARCHAR( 20 ),
	SCOPETYPE            INTEGER,
	SRCCOMPNAME          VARCHAR( 130 ),
	SRCHOST              VARCHAR( 250 ),
	SRCWIID              VARCHAR( 20 ),
	TOPIC                VARCHAR( 100 ),
	CONSTRAINT SQL120828150349160 PRIMARY KEY ( NOTEID )
 );

CREATE INDEX BB_IDX1 ON FIPERACS.BULLETINBOARD ( JOBID );

CREATE INDEX BB_IDX2 ON FIPERACS.BULLETINBOARD ( SCOPEID );

CREATE TABLE FIPERACS.DATA_TYPES ( 
	DATA_TYPE_ABBREV     VARCHAR( 4 ) NOT NULL,
	MAJOR_VERSION_NUM    INTEGER NOT NULL,
	METAMODEL_TYPE       VARCHAR( 130 ) NOT NULL,
	CONSTRAINT PDATA_TYPES PRIMARY KEY ( DATA_TYPE_ABBREV ),
	CONSTRAINT DATA_TYPES_INDEX UNIQUE ( METAMODEL_TYPE, MAJOR_VERSION_NUM )
 );

CREATE TABLE FIPERACS.DBVERSIONCONTROLLER ( 
	PRIMKEY              VARCHAR( 20 ) NOT NULL,
	FILECONTENT          BLOB,
	CONSTRAINT SQL120828150349490 PRIMARY KEY ( PRIMKEY )
 );

CREATE TABLE FIPERACS.DEFAULTACL ( 
	IMPLICITACCESSTYPE   VARCHAR( 250 ) NOT NULL,
	PRIORITY             INTEGER NOT NULL,
	ACCESSID             VARCHAR( 250 ),
	ACCESSLEVEL          VARCHAR( 250 ),
	ACCESSTYPE           VARCHAR( 250 ),
	CONSTRAINT SQL120828150349610 PRIMARY KEY ( IMPLICITACCESSTYPE, PRIORITY )
 );

CREATE INDEX DEFACL_INDEX1 ON FIPERACS.DEFAULTACL ( ACCESSID );

CREATE TABLE FIPERACS.FILESERVERDETAIL ( 
	HIERARCHY            VARCHAR( 250 ) NOT NULL,
	HOMEDIRECTORY        VARCHAR( 250 ),
	HOSTNAME             VARCHAR( 250 ),
	LOGINNAME            VARCHAR( 250 ),
	PASSWORD             VARCHAR( 250 ),
	PROTOCOL             VARCHAR( 250 ),
	CONSTRAINT SQL120828150349850 PRIMARY KEY ( HIERARCHY )
 );

CREATE TABLE FIPERACS.FILESTORE ( 
	PRIMKEY              VARCHAR( 20 ) NOT NULL,
	FNAME                VARCHAR( 1024 ),
	JOBID                VARCHAR( 20 ),
	PERSIST              SMALLINT,
	WFID                 VARCHAR( 20 ),
	WIID                 VARCHAR( 20 ),
	CONSTRAINT PFILESTORE PRIMARY KEY ( PRIMKEY )
 );

CREATE INDEX FS_IDX1 ON FIPERACS.FILESTORE ( JOBID, PRIMKEY );

CREATE INDEX FS_IDX2 ON FIPERACS.FILESTORE ( JOBID );

CREATE INDEX FS_IDX3 ON FIPERACS.FILESTORE ( WIID );

CREATE TABLE FIPERACS.FIPERLOCK ( 
	LOCKHOLDER           VARCHAR( 250 ),
	LOCKNAME             VARCHAR( 500 ) NOT NULL
 );

CREATE TABLE FIPERACS.FIPERSYS ( 
	KEYNAME              VARCHAR( 250 ) NOT NULL,
	VALUEINT             INTEGER,
	VALUESTR             VARCHAR( 250 ),
	CONSTRAINT PFIPERSYS PRIMARY KEY ( KEYNAME )
 );

CREATE TABLE FIPERACS.GROUPMASTER ( 
	GROUPNAME            VARCHAR( 250 ) NOT NULL,
	GROUPDESC            VARCHAR( 250 ),
	CONSTRAINT SQL120828150350050 PRIMARY KEY ( GROUPNAME )
 );

CREATE TABLE FIPERACS.GROUPUSERMAP ( 
	ACCESSID             VARCHAR( 250 ) NOT NULL,
	ACCESSLEVEL          VARCHAR( 250 ) NOT NULL,
	GROUPNAME            VARCHAR( 250 ) NOT NULL
 );

CREATE INDEX GUM_INDEX1 ON FIPERACS.GROUPUSERMAP ( ACCESSID );

CREATE TABLE FIPERACS.HIERARCHYMASTER ( 
	PACKAGEID            VARCHAR( 20 ) NOT NULL,
	PACKAGENAME          VARCHAR( 250 ),
	PARENTID             VARCHAR( 20 ),
	CONSTRAINT SQL120828150350500 PRIMARY KEY ( PACKAGEID )
 );

CREATE INDEX HM_INDEX1 ON FIPERACS.HIERARCHYMASTER ( PACKAGENAME, PARENTID );

CREATE INDEX HM_INDEX2 ON FIPERACS.HIERARCHYMASTER ( PARENTID );

CREATE TABLE FIPERACS.IMPORTDETAILS ( 
	PRIMKEY              VARCHAR( 20 ) NOT NULL,
	CANCELLED            SMALLINT NOT NULL,
	IMPORTED_DATE        TIMESTAMP NOT NULL,
	ORIG_JOBID           VARCHAR( 20 ) NOT NULL,
	ORIG_SYSID           VARCHAR( 130 ) NOT NULL,
	ORIG_USERID          VARCHAR( 250 ) NOT NULL,
	PERCENT_COMPLETE     INTEGER NOT NULL,
	CONSTRAINT PIMPORTDETAILS PRIMARY KEY ( PRIMKEY )
 );

CREATE TABLE FIPERACS.JOBBEAN ( 
	PRIMKEY              VARCHAR( 20 ) NOT NULL,
	CC                   INTEGER,
	ENDDATE              TIMESTAMP,
	EXACTLOOKUPIGNOREFILES INTEGER,
	EXACTLOOKUPMODE      INTEGER,
	JOBDESC              VARCHAR( 250 ),
	LICENSE              VARCHAR( 2000 ),
	LICENSECONCURRENCY   INTEGER NOT NULL DEFAULT 0,
	MAXSUBFLOWBATCHSIZE  INTEGER NOT NULL DEFAULT 2147483647,
	LICENSETYPE            INTEGER NOT NULL DEFAULT 0,
	MODELID              VARCHAR( 130 ),
	MODELNAME            VARCHAR( 130 ),
	MODELVERSION         VARCHAR( 24 ),
	PARENTJOBID          VARCHAR( 20 ),
	PARTNERID            VARCHAR( 20 ),
	PENDING              INTEGER NOT NULL DEFAULT 0,
	PURGEDATE            TIMESTAMP,
	REMOTECORRELATIONID  VARCHAR( 250 ),
	REMOTEDOMAIN         VARCHAR( 250 ),
	REMOTEUSERID         VARCHAR( 250 ),
	SEEDVALUE            BIGINT,
	SEVERITY             INTEGER,
	STARTDATE            TIMESTAMP,
	STATUS               INTEGER,
	SUBMISSIONHOSTNAME   VARCHAR( 250 ),
	USERNAME             VARCHAR( 250 ),
	CONSTRAINT PJOBBEAN PRIMARY KEY ( PRIMKEY )
 );

CREATE INDEX JOBINDEX ON FIPERACS.JOBBEAN ( ENDDATE, PRIMKEY );

CREATE INDEX JOBINDEX2 ON FIPERACS.JOBBEAN ( STATUS, PRIMKEY );

CREATE INDEX JOB_IDX5 ON FIPERACS.JOBBEAN ( USERNAME );

CREATE INDEX JOB_IDX6 ON FIPERACS.JOBBEAN ( MODELNAME );

CREATE INDEX JOB_IDX7 ON FIPERACS.JOBBEAN ( JOBDESC );

CREATE INDEX JOB_IDX8 ON FIPERACS.JOBBEAN ( STARTDATE );

CREATE TABLE FIPERACS.JOBLOGDATABEAN ( 
	PRIMKEY              VARCHAR( 20 ) NOT NULL,
	CHAINEDEXCEPTION     BLOB,
	CLASSNAME            VARCHAR( 250 ),
	COMPNAME             VARCHAR( 130 ),
	COMPPATH             VARCHAR( 2000 ),
	COMPVER              VARCHAR( 24 ),
	ISTRINGID            INTEGER,
	JOBID                VARCHAR( 20 ),
	LOGDTTIME            TIMESTAMP,
	MSG                  VARCHAR( 1000 ),
	NUMPARAMETERS        INTEGER,
	SERVERTIME           BIGINT,
	SEVERITY             INTEGER,
	SOURCE               VARCHAR( 250 ),
	VAR0                 VARCHAR( 1000 ),
	VAR1                 VARCHAR( 1000 ),
	VAR2                 VARCHAR( 1000 ),
	VAR3                 VARCHAR( 1000 ),
	VAR4                 VARCHAR( 1000 ),
	VAR5                 VARCHAR( 1000 ),
	VAR6                 VARCHAR( 1000 ),
	VAR7                 VARCHAR( 1000 ),
	VAR8                 VARCHAR( 1000 ),
	VAR9                 VARCHAR( 1000 ),
	WORKITEMID           VARCHAR( 20 ),
	CONSTRAINT PJOBLOGDATABEAN PRIMARY KEY ( PRIMKEY )
 );

CREATE INDEX JOBLOGDATAINDEX ON FIPERACS.JOBLOGDATABEAN ( JOBID );

CREATE TABLE FIPERACS.JOBMODEL ( 
	JOBID                VARCHAR( 20 ) NOT NULL,
	WORKITEMID           VARCHAR( 20 ) NOT NULL,
	REFCOMPID            VARCHAR( 130 ) NOT NULL,
	MODELXML             BLOB,
	VERSIONSCOPE         BLOB,
	CONSTRAINT PJOBMODEL PRIMARY KEY ( JOBID, WORKITEMID, REFCOMPID )
 );

CREATE TABLE FIPERACS.JOBMONITOR ( 
	PRIMKEY              VARCHAR( 20 ) NOT NULL,
	DEST                 BLOB,
	JOBID                VARCHAR( 20 ),
	CONSTRAINT SQL120828150351410 PRIMARY KEY ( PRIMKEY )
 );

CREATE INDEX JM_INDEX1 ON FIPERACS.JOBMONITOR ( JOBID );

CREATE TABLE FIPERACS.JOBOWNER ( 
	JOBID                VARCHAR( 20 ) NOT NULL,
	JOBOWNERDOMAIN       VARCHAR( 255 ),
	JOBOWNERMETADATA     BLOB NOT NULL,
	JOBOWNERNAME         VARCHAR( 255 ),
	JOBOWNERUNIXMETA     BLOB,
	JOBOWNERUNIXNAME     VARCHAR( 255 ),
	JOBOWNERWINMETA      BLOB,
	JOBOWNERWINNAME      VARCHAR( 255 ),
	CONSTRAINT SQL120828150351660 PRIMARY KEY ( JOBID )
 );

CREATE TABLE FIPERACS.JOBVIEWDATA ( 
	JOBID                VARCHAR( 20 ) NOT NULL,
	CONFIGNAME           VARCHAR( 130 ) NOT NULL,
	CONFIGXML            BLOB NOT NULL,
	CONSTRAINT PJOBVIEWDATA PRIMARY KEY ( JOBID, CONFIGNAME )
 );

CREATE TABLE FIPERACS.MASTERMODEL ( 
	MODEL_KEY            VARCHAR( 20 ) NOT NULL,
	BASE_TABLE_NAME      VARCHAR( 10 ) NOT NULL,
	MODEL_ID             VARCHAR( 130 ) NOT NULL,
	MODEL_NAME           VARCHAR( 130 ) NOT NULL,
	SCHEMA_VERSION       INTEGER NOT NULL,
	CONSTRAINT PMASTERMODEL PRIMARY KEY ( MODEL_KEY ),
	CONSTRAINT MASTERMODELINDEX UNIQUE ( MODEL_ID, MODEL_NAME ),
	CONSTRAINT MASTERMODELINDX1 UNIQUE ( MODEL_ID, MODEL_NAME, SCHEMA_VERSION ),
	CONSTRAINT MASTERMODELINDX2 UNIQUE ( BASE_TABLE_NAME )
 );

CREATE TABLE FIPERACS.METAINFO ( 
	PRIMKEY              VARCHAR( 20 ) NOT NULL,
	FOREIGNID            VARCHAR( 100 ),
	LINKREF              VARCHAR( 20 ),
	LOCALID              VARCHAR( 100 ),
	LOCALMETA            BLOB,
	CONSTRAINT SQL120828150351970 PRIMARY KEY ( PRIMKEY )
 );

CREATE INDEX MI_INDEX1 ON FIPERACS.METAINFO ( LINKREF );

CREATE INDEX MI_INDEX2 ON FIPERACS.METAINFO ( LINKREF, FOREIGNID );

CREATE TABLE FIPERACS.OBJECTMASTER ( 
	OBJECTID             VARCHAR( 20 ) NOT NULL,
	CHECKOUTBY           VARCHAR( 250 ),
	CHECKOUTDATE         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CHECKOUTFLAG         VARCHAR( 250 ),
	DESCRIPTION          VARCHAR( 2000 ),
	OBJECTNAME           VARCHAR( 250 ),
	OBJECTPRIVILEGES     INTEGER,
	OBJECTSUBTYPE        VARCHAR( 250 ),
	OBJECTTYPE           INTEGER,
	PACKAGENAME          VARCHAR( 250 ),
	PARENTPACKAGEID      VARCHAR( 20 ),
	CONSTRAINT SQL120828150352360 PRIMARY KEY ( OBJECTID )
 );

CREATE INDEX OM_INDEX1 ON FIPERACS.OBJECTMASTER ( PARENTPACKAGEID );

CREATE INDEX OM_INDEX3 ON FIPERACS.OBJECTMASTER ( PACKAGENAME );

CREATE TABLE FIPERACS.OBJECTNAMEVALUE ( 
	ATTRIBUTENAME        VARCHAR( 250 ) NOT NULL,
	ATTRIBUTEVALUE       VARCHAR( 250 ) NOT NULL,
	OBJECTID             VARCHAR( 20 ) NOT NULL,
	OBJECTVERSION        VARCHAR( 24 ) NOT NULL,
	OBJECTVERSIONID      VARCHAR( 250 )
 );

CREATE INDEX NV_INDEX1 ON FIPERACS.OBJECTNAMEVALUE ( OBJECTID, OBJECTVERSION );

CREATE INDEX NV_INDEX2 ON FIPERACS.OBJECTNAMEVALUE ( OBJECTID );

CREATE TABLE FIPERACS.OBJECTREFERENCELIST ( 
	REFERENCEID          VARCHAR( 20 ) NOT NULL,
	OBJECTID             VARCHAR( 20 ),
	OBJECTREFERENCE      VARCHAR( 250 ),
	OBJECTVERSION        VARCHAR( 24 ),
	OBJECTVERSIONID      VARCHAR( 250 ),
	REFERENCEVERSIONID   VARCHAR( 24 ),
	CONSTRAINT SQL120828150353050 PRIMARY KEY ( REFERENCEID )
 );

CREATE TABLE FIPERACS.OBJECTVERSIONMASTER ( 
	OBJECTID             VARCHAR( 20 ) NOT NULL,
	OBJECTMAJORVERSION   INTEGER NOT NULL,
	OBJECTMINORVERSION   INTEGER NOT NULL,
	OBJECTINCRVERSION    INTEGER NOT NULL,
	AUTHORID             VARCHAR( 250 ),
	CREATEDATE           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	DESCRIPTION          VARCHAR( 2000 ),
	INTERNALLOCATIONID   VARCHAR( 250 ),
	OBJECTSIZE           INTEGER NOT NULL DEFAULT 0,
	OBJECTVERSIONID      VARCHAR( 250 ),
	CONSTRAINT SQL120828150353170 PRIMARY KEY ( OBJECTID, OBJECTMAJORVERSION, OBJECTMINORVERSION, OBJECTINCRVERSION )
 );

CREATE INDEX OVM_INDEX1 ON FIPERACS.OBJECTVERSIONMASTER ( INTERNALLOCATIONID );

CREATE TABLE FIPERACS.PARTNERPROFILEBEAN ( 
	PRIMKEY              VARCHAR( 20 ) NOT NULL,
	CALLBACKURL          VARCHAR( 250 ),
	DISPLAYNAME          VARCHAR( 100 ),
	LOCATIONURL          VARCHAR( 250 ),
	SOAPTYPE             VARCHAR( 10 ),
	CONSTRAINT SQL120828150353440 PRIMARY KEY ( PRIMKEY )
 );

CREATE TABLE FIPERACS.PRIMARYKEY ( 
	SEQNAME              VARCHAR( 12 ) NOT NULL,
	HIGHKEY              VARCHAR( 16 ),
	CONSTRAINT PPRIMARYKEY PRIMARY KEY ( SEQNAME )
 );

CREATE TABLE FIPERACS.RESULTREQUESTREGISTRY ( 
	PRIMARYKEY           VARCHAR( 20 ) NOT NULL,
	COMPONENTPATH        VARCHAR( 2000 ) NOT NULL,
	JMSTOPIC             BLOB NOT NULL,
	JOBID                VARCHAR( 20 ) NOT NULL,
	RESULTREQUEST        BLOB NOT NULL,
	CONSTRAINT SQL120828150353560 PRIMARY KEY ( PRIMARYKEY )
 );

CREATE INDEX RRR_INDEX1 ON FIPERACS.RESULTREQUESTREGISTRY ( JOBID );

CREATE TABLE FIPERACS.STATIONBEAN ( 
	PRIMKEY              VARCHAR( 20 ) NOT NULL,
	AFFINITYHOST         VARCHAR( 60 ),
	AFFINITYOS           VARCHAR( 30 ),
	AFFINITYOSARCH       VARCHAR( 30 ),
	AFFINITYOSNAME       VARCHAR( 30 ),
	AFFINITYOSVER        VARCHAR( 60 ),
	AFFINITYOTHERS       VARCHAR( 250 ),
	ALLOWEDUSERS         VARCHAR( 250 ),
	CONCURRENCY          INTEGER NOT NULL,
	DESCRIPTION          VARCHAR( 250 ),
	DRMMODE              VARCHAR( 10 ) NOT NULL,
	EXPIRETIME           BIGINT,
	LEASEINTERVAL        BIGINT,
	PUBLICKEYDESCRIPTOR  BLOB,
	SNAME                VARCHAR( 250 ),
	STARTED              BIGINT,
	STATIONHOSTNAME      VARCHAR( 250 ) NOT NULL,
	STATUS               INTEGER,
	STOPPED              BIGINT,
	USECOUNT             INTEGER,
	USERNAME             VARCHAR( 64 ) NOT NULL,
	CONSTRAINT SQL120828150353840 PRIMARY KEY ( PRIMKEY )
 );

CREATE INDEX SB_INDEX1 ON FIPERACS.STATIONBEAN ( SNAME );

CREATE INDEX STATION_IDX1 ON FIPERACS.STATIONBEAN ( EXPIRETIME );

CREATE TABLE FIPERACS.SYNCPOINT ( 
	PRIMKEY              VARCHAR( 20 ) NOT NULL,
	BRANCHES             INTEGER,
	COMPPATH             VARCHAR( 2000 ),
	JOBID                VARCHAR( 20 ),
	TAKEN                INTEGER,
	WAITERS              INTEGER,
	WFID                 VARCHAR( 20 ),
	CONSTRAINT SQL120828150354590 PRIMARY KEY ( PRIMKEY )
 );

CREATE INDEX SP_INDEX1 ON FIPERACS.SYNCPOINT ( WFID );

CREATE INDEX SP_INDEX2 ON FIPERACS.SYNCPOINT ( JOBID );

CREATE TABLE FIPERACS.TCBASICSEARCHPARMS ( 
	PRIMARYKEY           VARCHAR( 250 ) NOT NULL,
	OPERATOR             VARCHAR( 250 ),
	TEXT                 VARCHAR( 250 ),
	USEAUTHORS           SMALLINT NOT NULL,
	USEDESCRIPTIONS      SMALLINT NOT NULL,
	USENAMES             SMALLINT NOT NULL,
	CONSTRAINT SQL120828150354980 PRIMARY KEY ( PRIMARYKEY )
 );

CREATE TABLE FIPERACS.TCMODEL ( 
	PRIMARYKEY           VARCHAR( 250 ) NOT NULL,
	MODELDATA            BLOB,
	NAME                 VARCHAR( 250 ),
	USERID               VARCHAR( 250 ),
	VERSION1             VARCHAR( 24 ),
	CONSTRAINT SQL120828150355150 PRIMARY KEY ( PRIMARYKEY )
 );

CREATE INDEX TCM_INDEX1 ON FIPERACS.TCMODEL ( USERID );

CREATE TABLE FIPERACS.TCPREFERENCES ( 
	PRIMARYKEY           VARCHAR( 250 ) NOT NULL,
	CURRENTMODELNAME     VARCHAR( 250 ),
	CURRENTMODELVERSION  VARCHAR( 24 ),
	CURRENTVIEW          VARCHAR( 250 ),
	IPADDR               VARCHAR( 32 ),
	LASTACCESS           BIGINT,
	LOGLEVEL             INTEGER,
	RECOVERING           INTEGER,
	USERID               VARCHAR( 250 ),
	VIEWDATA             VARCHAR( 250 ),
	CONSTRAINT SQL120828150355590 PRIMARY KEY ( PRIMARYKEY )
 );

CREATE INDEX TCPREF_INDEX1 ON FIPERACS.TCPREFERENCES ( LASTACCESS );

CREATE INDEX TCPREF_INDEX2 ON FIPERACS.TCPREFERENCES ( USERID );

CREATE TABLE FIPERACS.UIDGEN ( 
	NAME                 CHAR( 8 ),
	VALUE                CHAR( 16 )
 );

CREATE TABLE FIPERACS.VERSIONCONTROLIMPLEMENTATION ( 
	PRIMKEY              VARCHAR( 20 ) NOT NULL,
	CONFIG               VARCHAR( 1000 ),
	HIERARCHY            VARCHAR( 250 ) NOT NULL,
	IMPLEMENTATIONCLASS  VARCHAR( 250 ),
	VERSIONCONTROLLER    VARCHAR( 250 ),
	CONSTRAINT SQL120828150356070 PRIMARY KEY ( PRIMKEY )
 );

CREATE TABLE FIPERACS.VERSIONCONTROLMASTER ( 
	OBJECTID             VARCHAR( 20 ) NOT NULL,
	VERSIONID            INTEGER NOT NULL,
	CHECKOUTFLAG         VARCHAR( 250 ),
	HIERARCHY            VARCHAR( 250 ),
	INTERNALFILENAME     VARCHAR( 250 ),
	CONSTRAINT SQL120828150356230 PRIMARY KEY ( OBJECTID, VERSIONID )
 );

CREATE TABLE FIPERACS.WORKCONTEXTBEAN ( 
	PRIMKEY              VARCHAR( 20 ) NOT NULL,
	JOBID                VARCHAR( 20 ),
	SYNCID               BIGINT NOT NULL,
	WORKCONTEXTBYTES     BLOB,
	CONSTRAINT SQL120828150356450 PRIMARY KEY ( PRIMKEY )
 );

CREATE INDEX WCB_INDEX1 ON FIPERACS.WORKCONTEXTBEAN ( JOBID );

CREATE TABLE FIPERACS.WORKFLOWBEAN ( 
	PRIMKEY              VARCHAR( 20 ) NOT NULL,
	CC                   INTEGER,
	EVALTYPE             INTEGER,
	JOBID                VARCHAR( 20 ),
	ORIGID               VARCHAR( 200 ),
	ORIGNAME             VARCHAR( 250 ),
	PARENTWORKITEMID     VARCHAR( 20 ),
	SF_NUM_DB_BYTES      BIGINT,
	SF_NUM_DISK_BYTES    BIGINT,
	SF_NUM_VALUES        BIGINT,
	STATUS               VARCHAR( 10 ) NOT NULL,
	CONSTRAINT SQL120828150356710 PRIMARY KEY ( PRIMKEY )
 );

CREATE INDEX WFB_INDEX1 ON FIPERACS.WORKFLOWBEAN ( ORIGID, PARENTWORKITEMID );

CREATE INDEX WFB_INDEX2 ON FIPERACS.WORKFLOWBEAN ( JOBID, ORIGID );

CREATE INDEX WFB_INDEX3 ON FIPERACS.WORKFLOWBEAN ( JOBID );

CREATE TABLE FIPERACS.WORKITEMBEAN ( 
	PRIMKEY              VARCHAR( 20 ) NOT NULL,
	AFFINITIES           VARCHAR( 20 ),
	APPROXORIGID         VARCHAR( 15 ),
	APPROXWIID           VARCHAR( 20 ),
	CC                   INTEGER,
	COMPMODELID          INTEGER,
	COMPMODELIDSTR       VARCHAR( 130 ) NOT NULL,
	COMPONENTCRC         BIGINT,
	COMPPATH             VARCHAR( 2000 ),
	COMPTYPE             VARCHAR( 250 ),
	COMPVERSION          INTEGER,
	COMPVERSTR           VARCHAR( 24 ) NOT NULL,
	COSIMGROUPID         VARCHAR( 20 ),
	CREATEDATE           TIMESTAMP,
	DISPATCHDATE         TIMESTAMP,
	DISPATCHTARGET       VARCHAR( 250 ),
	DRMJOBID             VARCHAR( 250 ),
	DRMTYPE              VARCHAR( 130 ),
	ENDDATE              TIMESTAMP,
	EVALTYPE             INTEGER,
	EXECUTEDURATION      BIGINT,
	GROUPNAME            VARCHAR( 50 ),
	ITERATIONPATH        VARCHAR( 2000 ),
	JOBID                VARCHAR( 20 ),
	LOOKUPREASONCODE     INTEGER,
	MAXRETRY             BIGINT,
	NOSAVEDESCENDANTPARMS SMALLINT,
	PENDINGREPORTED      SMALLINT,
	REFWIID              VARCHAR( 20 ),
	RETRYCOUNT           BIGINT,
	SF_NUM_DB_BYTES      BIGINT,
	SF_NUM_DISK_BYTES    BIGINT,
	SF_NUM_VALUES        BIGINT,
	STATUS               INTEGER,
	TIMEOUT              BIGINT,
	WI_NUM_DB_BYTES      BIGINT,
	WI_NUM_DISK_BYTES    BIGINT,
	WI_NUM_VALUES        BIGINT,
	WORKFLOWID           VARCHAR( 20 ),
	CONSTRAINT SQL120828150357240 PRIMARY KEY ( PRIMKEY )
 );

CREATE INDEX WIB_INDEX1 ON FIPERACS.WORKITEMBEAN ( JOBID, STATUS );

CREATE INDEX WIB_INDEX2 ON FIPERACS.WORKITEMBEAN ( WORKFLOWID, STATUS );

CREATE INDEX WIB_INDEX3 ON FIPERACS.WORKITEMBEAN ( DISPATCHTARGET, STATUS );

CREATE INDEX WIB_INDEX4 ON FIPERACS.WORKITEMBEAN ( JOBID );

CREATE INDEX WIB_INDEX5 ON FIPERACS.WORKITEMBEAN ( WORKFLOWID );

CREATE INDEX WIB_INDEX6 ON FIPERACS.WORKITEMBEAN ( EVALTYPE, WORKFLOWID );

CREATE INDEX WIB_INDEX7 ON FIPERACS.WORKITEMBEAN ( AFFINITIES );

CREATE INDEX WIB_INDEX8 ON FIPERACS.WORKITEMBEAN ( STATUS, PENDINGREPORTED );

CREATE INDEX WIB_INDEX9 ON FIPERACS.WORKITEMBEAN ( COSIMGROUPID );

CREATE INDEX WORKITEMBEAN_AC1 ON FIPERACS.WORKITEMBEAN ( JOBID, STATUS, AFFINITIES );

CREATE TABLE FIPERACS.WORKITEMSUMMARY ( 
	PRIMKEY              VARCHAR( 20 ) NOT NULL,
	COMPPATHKEY          INTEGER NOT NULL,
	STATUS               INTEGER NOT NULL,
	CC                   INTEGER NOT NULL,
	EVALTYPE             INTEGER NOT NULL,
	COMPPATH             VARCHAR( 2000 ) NOT NULL,
	MAX_DURATION         BIGINT,
	MIN_DURATION         BIGINT,
	SF_NUM_DB_BYTES      BIGINT,
	SF_NUM_DISK_BYTES    BIGINT,
	SF_NUM_VALUES        BIGINT,
	SUM_DURATION         BIGINT,
	WI_NUM_DB_BYTES      BIGINT,
	WI_NUM_DISK_BYTES    BIGINT,
	WI_NUM_VALUES        BIGINT,
	WORKITEM_COUNT       INTEGER NOT NULL,
	CONSTRAINT PWORKITEMSUMMARY PRIMARY KEY ( PRIMKEY, COMPPATHKEY, STATUS, CC, EVALTYPE )
 );

CREATE INDEX WORKITEMSUM_IDX1 ON FIPERACS.WORKITEMSUMMARY ( PRIMKEY );

INSERT INTO FIPERACS.FIPERSYS( KEYNAME, VALUESTR, VALUEINT ) VALUES ( 'CreatedByBuildVersion', '5.23.1', null ); 
INSERT INTO FIPERACS.FIPERSYS( KEYNAME, VALUESTR, VALUEINT ) VALUES ( 'SchemaVersion', null, 52300 ); 

CREATE TABLE FIPERACS.PARAMHISTORYBEAN (
	PRIMKEY VARCHAR( 20 ) NOT NULL,
	JOBID VARCHAR( 20 ),
    COMPPATH VARCHAR( 2000 ),
    PARAMHISTORYDATA BLOB,
    ROWINDEX BIGINT,
	CONSTRAINT PPARAMHISTORY PRIMARY KEY ( PRIMKEY )
   );

 CREATE SEQUENCE pramhist_seq
 AS BIGINT
 START WITH     1
 INCREMENT BY   1
 NO CYCLE;
   
CREATE TRIGGER T_PARAMHIST_ROWINDEX
AFTER INSERT ON FIPERACS.PARAMHISTORYBEAN
REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
UPDATE FIPERACS.PARAMHISTORYBEAN SET ROWINDEX = (NEXT VALUE FOR pramhist_seq ) WHERE ROWINDEX = 0 OR ROWINDEX is NULL;



