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:
  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.

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                                              

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:

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

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

 

 

Alan C Smith, 2005