Creating a Standard for MQ Messages to/from SQLite DB

Yesterday, I wrote about MQ Messages to/from SQLite DB which are a pair of utilities for offloading and loading messages to/from SQLite database.

I thought it would be a good idea to publish what I designed so that other vendors, companies or users can easy use the standard.

First, the SQL to create the table is:

CREATE TABLE IBM_MQ_MESSAGES(Version            INT,
                             Report             INT,
                             MsgType            INT,
                             Expiry             INT,
                             Feedback           INT,
                             Encoding           INT,
                             CodedCharSetId     INT,
                             Priority           INT,
                             Persistence        INT,
                             BackoutCount       INT,
                             PutApplType        INT,
                             MsgSeqNumber       INT,
                             Offset             INT,
                             MsgFlags           INT,
                             OriginalLength     INT,
                             Format             CHAR(8),
                             ReplyToQ           CHAR(48),
                             ReplyToQMgr        CHAR(48),
                             UserIdentifier     CHAR(12),
                             ApplIdentityData   CHAR(32),
                             PutApplName        CHAR(28),
                             PutDate            CHAR(8),
                             PutTime            CHAR(8),
                             ApplOriginData     CHAR(4),
                             MsgId              BLOB,
                             CorrelId           BLOB,
                             AccountingToken    BLOB,
                             GroupId            BLOB,
                             Data               BLOB );

I like the KISS principle, so the name of the table is “IBM_MQ_MESSAGES”. Clean and obvious. The column names are directly from the MQMD structure in cmqc.h file.

Column layout for the table:
• The MQMD MQLONG fields are all INT (32-bit) columns for SQLite.
• The MQMD MQCHAR fields are all CHAR(*) columns for SQLite.
• The MQMD MQBYTE fields are all BLOB columns for SQLite.
• The message data (payload) is stored as BLOB column in SQLite.

The naming standard for the SQLite database file is based on the queue manager’s name, the queue name and the current day’s date.
I.e.

QMgrName–QueueName–YYYY_MM_DD.mqsdb
• QMgrName is the name of the source MQ queue manager
• QueueName is the name of the source MQ queue
• YYYY_MM_DD is the current year, month and day.

For a file extension, I decided to create a uniquely identifyible file extension called ‘mqsdb’. Now, I could have gone with a more standard file extension like ‘db’ or ‘sqlite’ but I wanted something more descriptive that would clearly identify the file’s purpose.

Next, reducing the database size or attempting to keep it as small as possible. Hence, any MQMD field that was either all blank or null, I set the table column to null for that record. This is done for all 9 CHAR fields and 5 BLOB fields.
i.e.

/* Is the Format field all blank? */
if (memcmp(pMD->Format, MQFMT_NONE, MQ_FORMAT_LENGTH) == 0)
   sqlite3_bind_null(hStmt, 16);
else
   sqlite3_bind_text(hStmt,  16, pMD->Format, MQ_FORMAT_LENGTH, SQLITE_STATIC);

/* Is the CorrelId field all null? */
if (memcmp(pMD->CorrelId, MQCI_NONE, MQ_CORREL_ID_LENGTH) == 0)
   sqlite3_bind_null(hStmt, 26);
else
   sqlite3_bind_blob(hStmt, 26, (char *)&(pMD->CorrelId), MQ_CORREL_ID_LENGTH, SQLITE_STATIC);

So, in theory, if the Format field is blank or the CorrelId field is null, then by setting the column field to null, it should save disk space.

Finally, reading the SQLite database where certain columns may or may not be null.

/* Retrieve the Format field, is it null? */
p = (char *)sqlite3_column_text(hStmt,  15);
if (p != NULL)
   memcpy(md.Format, p, MQ_FORMAT_LENGTH);

/* Retrieve the CorrelId field, is it null? */
p = (char *)sqlite3_column_blob(hStmt, 25);
if (p != NULL)
   memcpy(md.CorrelId, p, MQ_CORREL_ID_LENGTH);

So there you have it, a new MQ Messages to SQLite database standard that I hope everyone will adopt. 🙂

Now, for those people who REALLY like to read code, please don’t get your shorts in a knot because the column numbers are different between writing and reading the database. It is one of those quirks in SQLite. For sqlite3_bind_* API calls, the column number begin with ‘1’ whereas for sqlite3_column_* API calls, the column number begin with ‘0’. Hence, that is why they differ by 1 and it is something you just need to remember. 🙂

Maybe some time in the near future, I’ll write another blog posting on how to handle this new MQ Messages to SQLite database standard from Java.

Regards,
Roger Lacroix
Capitalware Inc.