Creating Additional DB2 Database Queue Tables

Copyright © Cloud Software Group, Inc. All Rights Reserved

Chapter 3 Mbox Sets and Message Queues : Creating Additional DB2 Database Queue Tables

Creating Additional DB2 Database Queue Tables

If you decide to add an additional message queue to your system, you need to:

1.

manually create the database queue table needed to hold the new message queue.

2.

create the new message queue and map the database queue table to it (using the SWDIR\bin\swadm utility).

3.

add the message queue to the appropriate Mbox set (using the SWDIR\bin\swadm utility).

Each individual message queue must be held in its own database queue table.

Each database queue table must have the following characteristics:

•

the same column definitions as a sw_db_bgqueue_n, sw_db_wisqueue_n, or sw_db_predictqueue_n table. (Each of these tables has the same structure.)

•

no primary key.

•

the iProcess background user (default swpro) must have at least insert, select and delete permissions on the table.

•

the iProcess foreground user (default swuser) must have at least insert permissions on the table.

If a table that does not conform to these requirements is used as a message queue, messages will not be able to be enqueued to or dequeued from that queue, and the iProcess Engine may not function correctly.

Example

Suppose that the volume of messages handled by your system has increased significantly, and the default message queues are no longer able to cope. To deal with the additional load you have decided that you need to add a new BGMBOX3 message queue to the BGMBSET Mboxset. This queue requires a new DB2 database queue table sw_db_bgqueue_3. You store your database queue tables in the default iProcess database.

To do this:

1.

Connect to the DB2 Server that holds the iProcess database.

2.

Create a new table in the iProcess database called sw_db_bgqueue_3.

For example:

CREATE TABLE swpro.sw_db_bgqueue_3 (

rowid numeric(15) NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),

last_failed numeric(10) ,

failure_count integer NOT NULL,

msg_id varchar(16) FOR BIT DATA NOT NULL,

msg_hdr varchar(500) ,

msg_data varchar(1000) NOT NULL)/

GRANT REFERENCES, SELECT, INSERT, DELETE, UPDATE ON swpro.sw_db_bgqueue_3 TO USER swpro, USER swuser/

3.

Use the SWDIR\util\swadm utility to add a new message queue called BGMBOX3, which uses the sw_db_bgqueue_3 queue table.

cd SWDIR\util

swadm ADD_QUEUE BGMBOX3 Local 0003:swpro.sw_db_bgqueue_3

4.

Add the BGMBOX3 queue to the BGMBSET Mbox set.

swadm ADD_QUEUE_TO_MBOXSET 1 8

1 is the number of the BGMBSET Mboxset (from the swadm SHOW_MBOXSETS command), and 8 is the number of the message queue (from the swadm SHOW_QUEUES command).