Create Ibisstore, Ibislock, and Ibisconfig table

When you want to save messages or errors, the Ibisstore table plays an important role. Ibisstore is used for three functional purposes :

Errorstorage ; when reading a message from a queue things can go wrong. The message will go back into the queue for a retry. When this goes wrong again you can opt to place the message in the Ibisstore. This way you will avoid that the messages resides in the queue forever.
Messagestorage : Every message has a unique ID. The sender can keep sending the message until he gets an OK from the Ibis. The sender will then know for sure that the message is saved in the Ibisstore. From here you could add a listener that would send the message elsewhere transactionally.
Messagelog : Every “Fire&Forget” message can be saved in the Ibisstore by default.

This table has to be generated manually. The specific script is dependent on the database you use. We will provide initialisation scripts for the most common db’s.

The Ibisconfig table stores all dynamic configurations, that is all configs that have as classLoaderType a DatabaseClassLoader. These can be sent via the framework API.

ORACLE db :


        CREATE TABLE <schema_owner>.IBISSTORE
        (
        MESSAGEKEY NUMBER(10),
        TYPE CHAR(1 CHAR),
        SLOTID VARCHAR2(100 CHAR),
        HOST VARCHAR2(100 CHAR),
        MESSAGEID VARCHAR2(100 CHAR),
        CORRELATIONID VARCHAR2(256 CHAR),
        MESSAGEDATE TIMESTAMP(6),
        COMMENTS VARCHAR2(1000 CHAR),
        MESSAGE BLOB,
        EXPIRYDATE TIMESTAMP(6),
        LABEL VARCHAR2(100 CHAR),
        CONSTRAINT PK_IBISSTORE PRIMARY KEY (MESSAGEKEY)
        );

        CREATE INDEX <schema_owner>.IX_IBISSTORE ON <schema_owner>.IBISSTORE (TYPE, SLOTID, MESSAGEDATE);
        CREATE INDEX <schema_owner>.IX_IBISSTORE_02 ON <schema_owner>.IBISSTORE (EXPIRYDATE);
        CREATE SEQUENCE <schema_owner>.SEQ_IBISSTORE;

        GRANT DELETE, INSERT, SELECT, UPDATE ON <schema_owner>.IBISSTORE TO <rolename>;
        GRANT SELECT ON <schema_owner>.SEQ_IBISSTORE TO <rolename>;
        GRANT SELECT ON SYS.DBA_PENDING_TRANSACTIONS TO <rolename>;
        
        CREATE TABLE IBISLOCK
        (
        OBJECTID      VARCHAR2(100 CHAR),
        TYPE          CHAR(1 CHAR),
        HOST          VARCHAR2(100 CHAR),
        CREATIONDATE  TIMESTAMP(6),
        EXPIRYDATE    TIMESTAMP(6),
        CONSTRAINT PK_IBISLOCK
        PRIMARY KEY
        (OBJECTID)
        );
 
        CREATE INDEX IX_IBISLOCK ON IBISLOCK
        (EXPIRYDATE);
         
        CREATE TABLE IBISCONFIG
        (
         NAME              VARCHAR2(100 CHAR),
         CONFIG            BLOB,
         CRE_TYDST         TIMESTAMP (6),
         RUSER             VARCHAR2(32 CHAR),
         VERSION           VARCHAR2(50 CHAR),
         FILENAME          VARCHAR2(150 CHAR),
         ACTIVECONFIG      NUMBER(1,0),
         AUTORELOAD        NUMBER(1,0),
         CONSTRAINT PK_IBISCONFIG PRIMARY KEY (NAME, VERSION)
        );
        
        
        COMMIT;

MSSQL db :


use <schema.owner>
go
 
CREATE TABLE dbo.IBISSTORE(
       MESSAGEKEY int identity(1, 1) not null,
       [TYPE] CHAR(1) null,
       SLOTID VARCHAR(100) null,
       HOST VARCHAR(100) null,
       MESSAGEID VARCHAR(100) null,
       CORRELATIONID VARCHAR(256) null,
       MESSAGEDATE datetime null,
       COMMENTS NVARCHAR(1000) null,
       [MESSAGE] varbinary(max) null,
       EXPIRYDATE datetime null,
       LABEL VARCHAR(100) null
)
go
 
ALTER TABLE dbo.IBISSTORE ADD  CONSTRAINT [pk_IBISSTORE] PRIMARY KEY CLUSTERED
(
       [MESSAGEKEY] ASC
)
go
 
CREATE INDEX IX_IBISSTORE ON IBISSTORE ([TYPE], SLOTID, MESSAGEDATE)
go
 
CREATE INDEX IX_IBISSTORE_02 ON IBISSTORE (EXPIRYDATE)
go
 
CREATE TABLE dbo.IBISCONFIG(
       [NAME]                   VARCHAR(100) NOT NULL,
       [VERSION]                VARCHAR(50) NOT NULL,
       [FILENAME]               VARCHAR(150) null,
       CONFIG                   varbinary(max) null,
       CRE_TYDST                datetime null,
       RUSER                    VARCHAR(32) null,
       ACTIVECONFIG             bit null,
       AUTORELOAD               bit null
)
go
 
ALTER TABLE dbo.IBISCONFIG ADD  CONSTRAINT [pk_IBISCONFIG] PRIMARY KEY CLUSTERED
(
       [NAME] ASC,
       [VERSION] ASC
)
go

Generic db (H2 for instance) :

        CREATE TABLE IBISSTORE (
          MESSAGEKEY BIGINT AUTO_INCREMENT NOT NULL,
          TYPE CHAR(1),
          SLOTID VARCHAR(100),
          HOST VARCHAR(100),
          MESSAGEID VARCHAR(100),
          CORRELATIONID VARCHAR(100),
          MESSAGEDATE DATETIME,
          COMMENTS VARCHAR(1000),
          MESSAGE BLOB,
          EXPIRYDATE DATETIME,
          LABEL VARCHAR(100),
          CONSTRAINT PK_IBISSTORE PRIMARY KEY(MESSAGEKEY)
        )
        
        CREATE INDEX ibisstore_idx01 ON IBISSTORE (slotId, messageDate, expiryDate);
        
        CREATE INDEX ibisstore_idx02 ON IBISSTORE (expiryDate);

        CREATE TABLE IBISCONFIG (
          NAME VARCHAR(100) NOT NULL,
          VERSION VARCHAR(50) NOT NULL,
          FILENAME VARCHAR(50),
          CONFIG BLOB,
          CRE_TYDST DATETIME,
          RUSER VARCHAR(32),
          ACTIVECONFIG BOOLEAN,
          AUTORELOAD BOOLEAN,
          CONSTRAINT PK_IBISCONFIG PRIMARY KEY(NAME, VERSION)
        );
        
        CREATE TABLE IBISLOCK (
         OBJECTID VARCHAR(100) NOT NULL,
         TYPE CHAR(1),
         HOST VARCHAR(100),
         CREATIONDATE DATETIME,
         EXPIRYDATE DATETIME,
         CONSTRAINT pk_ibislock PRIMARY KEY (OBJECTID)
        );