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) );