Plan the Db2 environment

Plan the Db2 environment

If you are using queue sharing groups, IBM MQ needs to attach to a Db2® subsystem that is a member of a data sharing group. Use this topic to help understand the Db2 requirements used to hold IBM MQ data.

IBM MQ needs to know the name of the data sharing group that it is to connect to, and the name of a Db2 subsystem (or Db2 group) to connect to, to reach this data sharing group. These names are specified in the QSGDATA parameter of the CSQ6SYSP system parameter macro (described in Use CSQ6SYSP ).
Within the data sharing group, shared Db2 tables are used to hold:

Configuration information for the queue sharing group.
Properties of IBM MQ shared and group objects.
Optionally, data relating to offloaded IBM MQ messages.
IBM MQ provides sample jobs for defining the Db2 tablespaces, tables, and indexes. Two sets of sample jobs are provided:

One for compatibility with earlier versions of IBM MQ
One for use with Db2 V12 and later, which exploits Universal Table Spaces (UTS)
Since Db2 V11, various types of table space, used by earlier versions of IBM MQ, have been flagged as deprecated. Where possible, when setting up a new data sharing group you are encouraged to choose the set of jobs which exploit Db2 UTS.
Use of preexisting table space types remains supported. There is no non-disruptive migration path from these non-preferred table spaces to UTS. Tables must be unloaded, redefined in UTS, and reloaded, necessitating an IBM MQ queue sharing group outage.

By default Db2 uses the user ID of the person running the jobs as the owner of the Db2 resources. If this user ID is deleted then the resources associated with it are deleted, and so the table is deleted. Consider using a group ID to own the tables, rather than an individual user ID. We can do this by adding GROUP=groupname onto the JOB card, and specifying SET CURRENT SQLID='groupname' before any SQL statements.

IBM MQ uses the RRS Attach facility of Db2. This means that we can specify the name of a Db2 group to connect to. The advantage of connecting to a Db2 group attach name (rather than a specific Db2 subsystem), is that IBM MQ can connect (or reconnect) to any available Db2 subsystem on the z/OS® image that is a member of that group. There must be a Db2 subsystem that is a member of the data sharing group active on each z/OS image where you are going to run a queue-sharing IBM MQ subsystem, and RRS must be active.

Db2 storage

For most installations, the amount of Db2 storage required is about 20 or 30 cylinders on a 3390 device. However, if you want to calculate your storage requirement, the following table gives some information to help you determine how much storage Db2 requires for the IBM MQ data. The table describes the length of each Db2 row, and when each row is added to or deleted from the relevant Db2 table. Use this information together with the information about calculating the space requirements for the Db2 tables and their indexes in the Db2 for z/OS Installation Guide.

Table 1. Planning your Db2 storage requirements

Db2 table name Length of row A row is added when: A row is deleted when:

CSQ.ADMIN_B_QSG 252 bytes A queue sharing group is added to the table with the ADD QSG function of the CSQ5PQSG utility. A queue sharing group is removed from the table with the REMOVE QSG function of the CSQ5PQSG utility. (All rows relating to this queue sharing group are deleted automatically from all the other Db2 tables when the queue-sharing group record is deleted.)

CSQ.ADMIN_B_QMGR Up to 3828 bytes A queue manager is added to the table with the ADD QMGR function of the CSQ5PQSG utility. A queue manager is removed from the table with the REMOVE QMGR function of the CSQ5PQSG utility.

CSQ.ADMIN_B_STRUCTURE 1454 bytes The first local queue definition, specifying the QSGDISP(SHARED) attribute, that names a previously unknown structure within the queue sharing group is defined. The last local queue definition, specifying the QSGDISP(SHARED) attribute, that names a structure within the queue sharing group is deleted.

CSQ.ADMIN_B_SCST 342 bytes A shared channel is started. A shared channel becomes inactive.

CSQ.ADMIN_B_SSKT 254 bytes A shared channel that has the NPMSPEED(NORMAL) attribute is started. A shared channel that has the NPMSPEED(NORMAL) attribute becomes inactive.

CSQ.ADMIN_B_STRBACKUP 514 bytes A new row is added to the CSQ.ADMIN_B_STRUCTURE table. Each entry is a dummy entry until the BACKUP CFSTRUCT command is run, which overwrites the dummy entries. A row is deleted from the CSQ.ADMIN_B_STRUCTURE table.

CSQ.OBJ_B_AUTHINFO 3400 bytes An authentication information object with QSGDISP(GROUP) is defined. An authentication information object with QSGDISP(GROUP) is deleted.

CSQ.OBJ_B_QUEUE Up to 3707 bytes

A queue with the QSGDISP(GROUP) attribute is defined.
A queue with the QSGDISP(SHARED) attribute is defined.
A model queue with the DEFTYPE(SHAREDYN) attribute is opened.

A queue with the QSGDISP(GROUP) attribute is deleted.
A queue with the QSGDISP(SHARED) attribute is deleted.
A dynamic queue with the DEFTYPE(SHAREDYN) attribute is closed with the DELETE option.

CSQ.OBJ_B_NAMELIST Up to 15127 bytes A namelist with the QSGDISP(GROUP) attribute is defined. A namelist with the QSGDISP(GROUP) attribute is deleted.

CSQ.OBJ_B_CHANNEL Up to 14127 bytes A channel with the QSGDISP(GROUP) attribute is defined. A channel with the QSGDISP(GROUP) attribute is deleted.

CSQ.OBJ_B_STGCLASS Up to 2865 bytes A storage class with the QSGDISP(GROUP) attribute is defined. A storage class with the QSGDISP(GROUP) attribute class is deleted.

CSQ.OBJ_B_PROCESS Up to 3347 bytes A process with the QSGDISP(GROUP) attribute is defined. A process with the QSGDISP(GROUP) attribute is deleted.

CSQ.OBJ_B_TOPIC Up to 14520 bytes A topic object with QSGDISP(GROUP) attribute is defined. A topic object with QSGDISP(GROUP) attribute is deleted.

CSQ.EXTEND_B_QMGR Less than 430 bytes A queue manager is added to the table with the ADD QMGR function of the CSQ5PQSG utility. A queue manager is removed from the table with the REMOVE QMGR function of the CSQ5PQSG utility.

CSQ.ADMIN_B_MESSAGES 87 bytes For large message PUT (1 per BLOB). For large message GET (1 per BLOB).

CSQ.ADMIN_MSGS_BAUX1 CSQ.ADMIN_MSGS_BAUX2 CSQ.ADMIN_MSGS_BAUX3 CSQ.ADMIN_MSGS_BAUX4 These 4 tables contain message payload for large messages added into one of these 4 tables for each BLOB of the message. BLOBS are up to 511 KB in length, so if the message size is > 711 KB, there will be multiple BLOBs for this message.

The use of large numbers of shared queue messages of size greater than 63 KB can have significant performance implications on your IBM MQ system. For more information, see SupportPac MP16, Capacity Planning and Tuning for IBM MQ for z/OS, at: Business Integration - IBM MQ SupportPacs.

Parent topic: Plan the coupling facility and offload storage environment

Db2 table name	Length of row	A row is added when:	A row is deleted when:
CSQ.ADMIN_B_QSG	252 bytes	A queue sharing group is added to the table with the ADD QSG function of the CSQ5PQSG utility.	A queue sharing group is removed from the table with the REMOVE QSG function of the CSQ5PQSG utility. (All rows relating to this queue sharing group are deleted automatically from all the other Db2 tables when the queue-sharing group record is deleted.)
CSQ.ADMIN_B_QMGR	Up to 3828 bytes	A queue manager is added to the table with the ADD QMGR function of the CSQ5PQSG utility.	A queue manager is removed from the table with the REMOVE QMGR function of the CSQ5PQSG utility.
CSQ.ADMIN_B_STRUCTURE	1454 bytes	The first local queue definition, specifying the QSGDISP(SHARED) attribute, that names a previously unknown structure within the queue sharing group is defined.	The last local queue definition, specifying the QSGDISP(SHARED) attribute, that names a structure within the queue sharing group is deleted.
CSQ.ADMIN_B_SCST	342 bytes	A shared channel is started.	A shared channel becomes inactive.
CSQ.ADMIN_B_SSKT	254 bytes	A shared channel that has the NPMSPEED(NORMAL) attribute is started.	A shared channel that has the NPMSPEED(NORMAL) attribute becomes inactive.
CSQ.ADMIN_B_STRBACKUP	514 bytes	A new row is added to the CSQ.ADMIN_B_STRUCTURE table. Each entry is a dummy entry until the BACKUP CFSTRUCT command is run, which overwrites the dummy entries.	A row is deleted from the CSQ.ADMIN_B_STRUCTURE table.
CSQ.OBJ_B_AUTHINFO	3400 bytes	An authentication information object with QSGDISP(GROUP) is defined.	An authentication information object with QSGDISP(GROUP) is deleted.
CSQ.OBJ_B_QUEUE	Up to 3707 bytes	A queue with the QSGDISP(GROUP) attribute is defined. A queue with the QSGDISP(SHARED) attribute is defined. A model queue with the DEFTYPE(SHAREDYN) attribute is opened.	A queue with the QSGDISP(GROUP) attribute is deleted. A queue with the QSGDISP(SHARED) attribute is deleted. A dynamic queue with the DEFTYPE(SHAREDYN) attribute is closed with the DELETE option.
CSQ.OBJ_B_NAMELIST	Up to 15127 bytes	A namelist with the QSGDISP(GROUP) attribute is defined.	A namelist with the QSGDISP(GROUP) attribute is deleted.
CSQ.OBJ_B_CHANNEL	Up to 14127 bytes	A channel with the QSGDISP(GROUP) attribute is defined.	A channel with the QSGDISP(GROUP) attribute is deleted.
CSQ.OBJ_B_STGCLASS	Up to 2865 bytes	A storage class with the QSGDISP(GROUP) attribute is defined.	A storage class with the QSGDISP(GROUP) attribute class is deleted.
CSQ.OBJ_B_PROCESS	Up to 3347 bytes	A process with the QSGDISP(GROUP) attribute is defined.	A process with the QSGDISP(GROUP) attribute is deleted.
CSQ.OBJ_B_TOPIC	Up to 14520 bytes	A topic object with QSGDISP(GROUP) attribute is defined.	A topic object with QSGDISP(GROUP) attribute is deleted.
CSQ.EXTEND_B_QMGR	Less than 430 bytes	A queue manager is added to the table with the ADD QMGR function of the CSQ5PQSG utility.	A queue manager is removed from the table with the REMOVE QMGR function of the CSQ5PQSG utility.
CSQ.ADMIN_B_MESSAGES	87 bytes	For large message PUT (1 per BLOB).	For large message GET (1 per BLOB).
CSQ.ADMIN_MSGS_BAUX1 CSQ.ADMIN_MSGS_BAUX2 CSQ.ADMIN_MSGS_BAUX3 CSQ.ADMIN_MSGS_BAUX4		These 4 tables contain message payload for large messages added into one of these 4 tables for each BLOB of the message. BLOBS are up to 511 KB in length, so if the message size is > 711 KB, there will be multiple BLOBs for this message.