Q and A

Asked and Answered

1. We have a segmented tablespace growing over the 2G dataset limit that's now in the second 2G dataset. But we don't have the proper operations window to convert the tablespace into a partitioned tablespace. What is the maximum limit (in GBs) for a segmented tablespace?

2. What is the maximum limit (in GBs) of a partition in a partitioned tablespace?

Because our database wasn't set up correctly from the start, I'm concerned that some of those tables will abend in the middle of some production runs.

For nonpartitioned tablespaces, we don't use DSSIZE — that means our max partition size can only be 4GB. We need to change the tablespace to DSSIZE to extend to 64GB. However, we don't have a down time in which to change the tablespace definition and unload/reload these large tables. Any suggestions?

Robert Catterall responds:

The maximum size of a segmented tablespace is 64GB. Note that through the use of DB2 for OS/390 and z/OS hardware-assisted data compression (which we use extensively at CheckFree), you can get more than 64GB of data in a 64GB tablespace. We get compression ratios of more than 60 percent for some of our tables that have relatively long rows.

The maximum size of a partition in a partitioned tablespace is, I believe, 64GB. I think you can have 254 partitions in a tablespace, so the maximum size of a partitioned tablespace would be 16 terabytes.

If a table has one or more large object (LOB) columns, the "logical table" (base tablespace plus a LOB tablespace associated with each LOB column) can be much greater than 16 terabytes in size (think thousands of terabytes).

Here's what we do at CheckFree to convert nonpartitioned tablespaces without down time:

1. Create a new tablespace (and table and associated indexes) with the desired specifications (for example, DSSIZE 64G).
2. Unload data from the "original" table, and load into the new tablespace.
3. Assuming that the data in the "original" tablespace was updated in the course of the unload/load procedure, use a DB2 log analysis tool (available from IBM and third-party software vendors) to extract the changes from the log and apply them to the "new" tablespace.
4. Do step 3 iteratively to bring the "new" tablespace closer, in terms of content currency, to the "original" tablespace.
5. When a brief window of no access or read-only access to the "original" tablespace is available, do a final log extract and apply to achieve data currency synchronization.
6. If step 5 is accomplished during a read-only period (re: the "original" tablespace), go briefly to non-access, and change the name of the "original" table to something different (other than the name of the "new" table). Then, change the name of the "new" table to the former name of the "original" table.
7. Rebind packages invalidated via the RENAME TABLE operation.
8. Restore application program access to the tablespace.

It's strongly recommended that you keep the "old" tablespace and table (what I referred to as the "original" tablespace and table), around for a while (2-4 weeks is our norm), just in case something goes wrong. Make sure that when you finally drop the "old" tablespace, you don't accidentally drop the new one. The RESTRICT ON DROP option of CREATE TABLE can help here.

We've successfully executed this procedure a number of times. It's similar to what happens under the covers when you run an online REORG of an actively updated tablespace.

See a complete archive of reader/author Q&As.