Problems After Adding Columns to a Table

Introduction

DB2 allows you to add a column to a table without having to drop and recreate the table. For instance, I may have a table POLICY with these columns:

POLNO CHAR(8) AMOUNT SMALLINT

I can then add another column using ALTER TABLE:

ALTER TABLE ADD CUSTNAME CHAR(32)

so the table now has these columns:

POLNO CHAR(8) AMOUNT SMALLINT CUSTNAME CHAR(32)

Rows which already existed in the table are not changed, but take on the default value for the new column, so the new column must either be nullable, as in this case (because NOT NULL was not specified), or have a default specified when the column is added.

The Problem

Because the existing rows are not changed at the time of the ALTER, they are shorter than any new rows. They will stay like this until they are updated or the tablespace is REORGed.

This difference in row length means that DB2 treats the table as having variable-length rows until it is REORGed and the MODIFY RECOVER utility has been used to remove all image copies which were taken up to the time of that REORG (this is because we might recover back to a table containing variable-length rows).

So DB2 is quite happy when it retrieves rows which are shorter than you would expect from the column definition.

However, problems can arise under these circumstances:

1. Recovery is being done outside DB2 — for instance:
   • an old image copy, no longer registered in DB2, is being used to restore a tablespace using DSN1COPY
   • a table has been dropped and recreated and the old data is being restored with DSN1COPY
   • the tablespace’s underlying VSAM dataset is being recovered from a DSSDUMP
2. Some sort of snapshot technique is being used to copy data from one subsystem (the source) to another (the target). Disk suppliers like EMC and IBM provide methods to take almost instantaneous copies of groups of disks — the disks can then be relabelled and the datasets renamed so that the target subsystem believes that the VSAM datasets relate to its own tablespaces and indexes.

   The target objects need to be defined exactly the same as the source objects and, after each ‘copy’, LEVELIDs have to be repaired. When the target objects are set up in the target subsystem for the first time — DDL is used to create them in the normal way — they will be created to have the same definitions as the current state of the source subsystem.

   For instance, if we were creating the POLICY table above, we would define it with all three columns. The POLICY table will then be viewed by the target subsystem as having fixed-length rows. Its tablespace’s underlying VSAM dataset, which is a copy from the source subsystem, will contain some short rows unless the tablespace has been REORGed since the column was added to the table. A process trying to read these rows in the target subsystem will abend.

3. The UNLOAD utility is being used. Even late in DB2 V7, the UNLOAD utility abends if it finds short length rows in what it thinks is a table with fixed length rows. This is a bug and will hopefully be fixed in a future release.

The Solution

The solution is to REORG the tablespace whenever a column is added to a table. This should be added to your DBA processes so that no new problems are created. You need also to identify tables which already have the problem.

Identifying Problem Tables

There is nothing in the catalog tables which say “this table has had a column added, but hasn't been REORGed”. Instead we can look in a few places to identify some candidates.

• ALTEREDTS. SYSIBM.SYSTABLES has a column called ALTEREDTS — a timestamp which records when the table was last ALTERed. Unfortunately, the table could have had another sort of ALTER done to it, such as RESTRICT ON DROP added.
• Last REORG time. If a REORG has been done later than ALTEREDTS, you should be OK. The REORG utility creates an entry in SYSIBM.SYSTABLES (type W for a REORG LOG(NO)). Unfortunately, if the last REORG happened two months ago and a MODIFY RECOVER has been done to clear out entries over a month old, this row will have been lost. If Real-time statistics have been activated (these were new in DB2 V7), SYSIBM.TABLESPACESTATS has a column called REORGLASTTIME which has the timestamp of when REORG was last run. This will be NULL if REORG has not been run since real-time stats has been started. If the tablespace is partitioned, the partitions can be REORGed individually. All partitions will need to be REORGed to get rid of the short rows.
• Last LOAD REPLACE time. For our purposes, a LOAD REPLACE is as good as a REORG. The entry in SYSCOPY for a LOAD REPLACE LOG(NO) is S and SYSIBM.TABLESPACESTATS has a column called LOADRLASTTIME.
• Only tables with ‘fixed-length’ rows are affected. That is, only tables with rows which should have a fixed length — i.e. they don't contain VARCHARs and the tablespace isn’t compressed — can possibly be affected. So tables with VARCHARs and ones which are in compressed tablespaces don't need to be looked at.
• Look at RECLENGTH and AVGROWLENGTH in SYSIBM.SYSTABLES. RECLENGTH is the maximum length of any row in the table (including row overhead bytes). AVGROWLENGTH is a statistics column and contains the average record length in the table — RUNSTATS needs to be run to populate the column. If the values are the same for a table, it’s OK.

This query brings up tables which should have fixed length rows but have different RECLENGTH and AVGROWLENGTH:

SELECT TS.DBNAME,TS.NAME,T.RECLENGTH,T.AVGROWLEN FROM SYSIBM.SYSTABLES T INNER JOIN SYSIBM.SYSTABLESPACE TS ON T.DBNAME = TS.DBNAME AND T.TSNAME = TS.NAME INNER JOIN SYSIBM.SYSTABLEPART TSP ON TS.DBNAME = TSP.DBNAME AND TS.NAME = TSP.TSNAME WHERE T.CREATOR = 'XXXX' AND TSP.COMPRESS <> 'Y' AND T.AVGROWLEN <> T.RECLENGTH AND T.AVGROWLEN > 0 AND NOT EXISTS (SELECT 1 FROM SYSIBM.SYSCOLUMNS C WHERE C.TBNAME = T.NAME AND C.TBCREATOR = T.CREATOR AND COLTYPE = 'VARCHAR' ) AND T.ALTEREDTS > T.CREATEDTS WITH UR

Ignore compressed tablespaces Only choose tablespaces which don't have a VARCHAR Select tablespaces where the table has statistics and the maximum record length is different from the average. Select tables which have been altered.

This gives us a good idea of problem tables but to check for sure, we need to use the DIAGNOSE utility.

DIAGNOSE

The DIAGNOSE utility can be used to show details of module levels, utilities and database descriptor info. Here we'll use it to show the object descriptor (OBD) of the table in a named tablespace:

//STEP1 EXEC DSNUPROC,SYSTEM=DNNG //SYSIN DD * DIAGNOSE DISPLAY OBD DNDHUNA1.SBILINS TABLES

The utility produces this fairly uninteresting output:

DSNU000I DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = SMITHAC@ DSNU050I DSNUGUTC - DIAGNOSE DISPLAY OBD DNDHUNA1.SBILINS TABLES DSNU863I -DNN3 DSNUDISP - DISPLAY OBD DNDHUNA1.SBILINS DSNU864I -DNN3 DSNUDISO - DISPLAY OBD, DBID=0106 OBID=0017 OBD TYPE=FILE 0000 48E0004C 00170000 001800B8 00000001 00002A08 00000030 20131000 000000C1 *...<...........................A* 0020 E2C2C9D3 C9D5E240 00000000 00000000 00AF00AF 00AF0004 001812D5 00430000 *SBILINS ...................N....* 0040 FFFFFFFE 100001F4 00000000 *.......4.... * DSNU864I -DNN3 DSNUDISO - DISPLAY OBD, DBID=0106 OBID=0018 OBD TYPE=RECORD 0000 20400284 00180204 00170000 00B90000 00A70006 04010000 00000000 00000000 *. ..............................* 0020 00000000 00000000 00000000 00000000 00000000 0000BB96 2468F948 404040D5 *..........................9. N* 0040 002C0000 00010000 00000118 00400002 00000002 01020040 00030002 00030116 *............. ......... ........* 0060 00400004 00050005 00180040 00020009 00000116 00400004 000B0009 00160040 *. ......... ......... ......... * 0080 0004000F 00000002 00400004 00130000 00020040 00010017 00000118 00400002 *......... ......... ......... ..* 00A0 0018FFFF 00020040 0002001A 00000016 00400004 001C0000 00020040 00010020 *....... ......... ......... ....* 00C0 00000002 00400001 00210000 00180040 00020022 00000014 00400006 00240000 *..... ......... ......... ......* 00E0 00140040 0006002A 00000014 00400006 00300000 00020040 00010036 00000002 *... ......... ......... ........* 0100 00400001 00370000 00140040 00060038 00000014 00400006 003E0000 00140040 *. ......... ......... ......... * 0120 00060044 00000014 00400006 004A0000 00140040 00060050 00000014 00400006 *......... ...õ..... ...&..... ..* 0140 00560000 00140040 0006005C 00000014 00400006 00620000 00140040 00060068 *....... ...*..... ......... ....* 0160 00000014 00400006 006E0000 00180040 00020074 00000016 00400004 00760000 *..... ...>..... ......... ......* 0180 00140040 0006007A 00000016 00400004 00800000 00140040 00060084 00000016 *... ...:..... ......... ........* 01A0 00400004 008A0000 00160040 0004008E 00000002 00400001 00920000 00180040 *. ......... ......... ......... * 01C0 00020093 00000018 00400002 00950000 00160040 00040097 00000002 00400001 *......... ......... ......... ..* 01E0 009B0000 00164040 0004009C 00000014 40400006 00A00000 02024040 000100A6 *...... ........ ........ ....* 0200 00000000 20AD0040 D6C2C4E7 00000000 00000000 00000000 02FEFB68 E05CD5E4 *....... OBDX.................*NU* 0220 D5C44040 40400006 C2C9D3C9 D5E24040 40404040 40404040 40400000 00000000 *ND ..BILINS ......* 0240 00400000 22AB0040 D6C2E7F2 00000800 00000000 00000000 00000000 00000000 *. ..... OBX2....................* 0260 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 *................................* 0280 00000000 *.... * DSNU865I -DNN3 DSNUDISP - DISPLAY OBD DNDHUNA1.SBILINS COMPLETE DSNU860I DSNUGUTC - DIAGNOSE UTILITY COMPLETE DSNU010I DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=0

It’s the first four bits of the second byte of the second block of data (highlighted) that’s of interest. Here it's set to 0100 (4). It can take on these values:

0100	Table has had column added
0110	Table has VARCHAR
0000	Table has fixed-length rows (even if compressed)

So if it's set to 4, you need to REORG.

Alan C Smith, 2005