Changing the DB2 database creation parameters to deal with large objects

This is part of the larger task of creating the broker database on z/OS.

DB2 needs to allocate auxiliary tables to hold large objects (LOBs). The default sizes that are specified for the PRIQTY and SECQTY of these tables might be too small for your requirements. This can mean that the maximum number of extents for the table space is quickly used up. If you have large and frequent updates to these tables, edit the BIPDBTS member in the CNTL library for their broker. Existing brokers will need to be re-created to pick up changes to this member.

For the create tablespace statement, take the following into consideration:
PRIQTY n
Specifies the minimum primary space allocation for a DB2-managed data set. The primary space allocation is at least the integer n in kilobytes; the actual value will vary depending on the page size for the buffer pool used. If PRIQTY is omitted, n is 200, 400, 800, or 1600 for 4KB, 8KB, 16KB, and 32KB page sizes, respectively.
SECQTY n
Specifies the minimum secondary space allocation for a DB2-managed data set. The secondary space allocation is at least the integer n in kilobytes. This will again vary with respect to the page size for the buffer pool used. If SECQTY is omitted, n is either 10% of PRIQTY, or 50 times the page size of the table space, whichever is larger.
The DSNTEJ7 sample shipped with DB2 demonstrates the above.

For existing brokers, a DB2 utility tools such as RUNSTATS or REORG, can be used on the default named table spaces to increase the primary and secondary quantities.

By default, in the WebSphere Business Integration Message Broker tables, LOB columns are defined as 1GB. Be aware that LOBs defined with a maximum size of > 1GB are not logged by DB2. This means that, if you restore a backup copy of a database with LOBs > 1 GB, DB2 will not recover changes to that table at restart.

To log changes to LOB columns ensure that the size of the LOB column is less than or equal to 1 GB, and specify LOG YES on the CREATE LOB TABLESPACE statement (the default if the LOG clause is not specified is LOG YES). This might affect the performance of the updates to these tables. If you do not want a LOB column to be logged, and the size of the LOB column is not greater than 1 GB, specify LOG NO for the table space concerned. By default, the LOB table spaces for WebSphere Business Integration Message Broker are tuned to enable logging.

If you are migrating from an existing broker to one that supports logging of LOB columns, recreate the broker.

Related tasks
Creating the broker database