ALTER TABLE ... ADD COLUMN renders SYNCHRONIZE STAMPS unsafe

Products

• IDMS

Issue/Introduction

When using ALTER TABLE to add columns to a table, the resulting physical structure of the table may not be the same as if the same table structure had been defined from the beginning.

This means that the practice of using SYNCHRONIZE STAMPS … UPDATE DATABASE to allow the backup and restore of a database independently of its related catalog may not work if ALTER TABLE had been performed on one of the tables.

Environment

Release: All supported releases.
Component: SQL Option.

Resolution

Consider the following example:-

CREATE TABLE SQL.T1
  ( COL1                             CHARACTER(5) NOT NULL,
    COL2                             CHARACTER(3) NOT NULL
  )
    IN SQLWORK.SQL-AREA-1
    ;
*+ Status = 0        SQLSTATE = 00000
INSERT INTO SQL.T1 VALUES ('AAAAA', 'BBB');
*+ Status = 0        SQLSTATE = 00000
*+ 1 row processed

SELECT TABLE, NAME, VOFFSET, VLENGTH
  FROM SYSTEM.COLUMN
 WHERE SCHEMA='SQL'
   AND TABLE='T1';
*+
*+ TABLE               NAME                              VOFFSET  VLENGTH
*+ -----               ----                              -------  -------
*+ T1                  COL1                                    0        5
*+ T1                  COL2                                    5        3
*+
*+ 2 rows processed

*+ PAGE 251,038             PAGE GROUP 0        AVAILABLE SPACE 4,188
*+ 000000   0003D49E 03D49E02 03D49E02 105C0000    *..M..M...M...*..*
*+ 000010   C1C1C1C1 C1C2C2C2 03D49E00 03D49E00    *AAAAABBB.M...M..*
*+ 000020   03D4A001 03D4A001 C9D5C4C5 E760F0F0    *.M...M..INDEX-00*
*+ 000030   F0F0F0F0 F0F0F0F2 00000000 00000000    *00000002........*
*+ 000040   00000000 00000000 00000000 00000000    *................*
*+ 000050   --SAME--
*+ 001090   00000000 00070018 00200010 04010010    *................*
*+ 0010A0   00080000 00010004 000C0008 00200010    *................*
*+ 0010B0   0003D49E                               *..M.*
*+
*+      1      4     0  0004    251,038-002    251,038-002
*+  1,025      8     1  0010
*+                            *AAAAABBB*
*+      7     16     2  0018    251,038-000    251,038-000    251,040-001
*+                              251,040-001
*+                            *INDEX-0000000002*

Note that the VOFFSET value for column COL2 is 5, which is the offset of that column within the row. Also note that the PRINT PAGE output reflects that – the value of ‘BBB’ starts 5 bytes from the value ‘AAAAA’ which is at the start of the row.

But what if the history of the table definition had been such that COL2 was added with an ALTER TABLE command after the initial creation of the table?

CREATE TABLE SQL.T2
  ( COL1                             CHARACTER(5) NOT NULL
  )
    IN SQLWORK.SQL-AREA-1
    ;
*+ Status = 0        SQLSTATE = 00000
ALTER  TABLE SQL.T2
  ADD COLUMN COL2 CHARACTER(3) NOT NULL
  ;
*+ Status = 0        SQLSTATE = 00000
INSERT INTO SQL.T2 VALUES ('AAAAA', 'BBB');
*+ Status = 0        SQLSTATE = 00000
*+ 1 row processed

SELECT TABLE, NAME, VOFFSET, VLENGTH
  FROM SYSTEM.COLUMN
 WHERE SCHEMA='SQL'
   AND TABLE='T2';
*+
*+ TABLE               NAME                              VOFFSET  VLENGTH
*+ -----               ----                              -------  -------
*+ T2                  COL1                                    0        5
*+ T2                  COL2                                    8        3
*+
*+ 2 rows processed

*+ PAGE 251,034             PAGE GROUP 0        AVAILABLE SPACE 4,184
*+ 000000   0003D49A 03D49A02 03D49A02 10580000    *..M..M...M......*
*+ 000010   C1C1C1C1 C1000000 C2C2C200 03D49A00    *AAAAA...BBB..M..*
*+ 000020   03D49A00 03D49C01 03D49C01 C9D5C4C5    *.M...M...M..INDE*
*+ 000030   E760F0F0 F0F0F0F0 F0F0F0F4 00000000    *X-0000000004....*
*+ 000040   00000000 00000000 00000000 00000000    *................*
*+ 000050   --SAME--
*+ 001090   00000000 0007001C 00200010 04040010    *................*
*+ 0010A0   000C0000 00010004 000C0008 00200010    *................*
*+ 0010B0   0003D49A                               *..M.*
*+
*+      1      4     0  0004    251,034-002    251,034-002
*+  1,028     12     1  0010
*+                            *AAAAA...BBB.*
*+      7     16     2  001C    251,034-000    251,034-000    251,036-001
*+                              251,036-001
*+                            *INDEX-0000000004*

In this case, the VOFFSET value for column COL2 is now 8 and due to the inflight restructure of the database, this is also reflected in the PRINT PAGE showing that specific row.

The reason for this is that when an ALTER TABLE is performed, the offset of the first new column in the ADD COLUMN clause is the original table length rounded up to the next multiple of 4.

This is a situation where the logical definition (and the syntactical DISPLAY) of T1 and T2 are the same, but the way in which the data is stored on the database are not. The difference is maintained in the SYSTEM.COLUMN table rows in the catalog.

So, consider the following process:-

1. Table CREATEd.
2. New column(s) added with ALTER TABLE … ADD COLUMN.
3. The database is backed up alone (i.e., not with the catalog).
4. The table is re-CREATEd with a single CREATE statement containing all the columns and no ALTER TABLE is required.
5. The backup taken in step 3 is restored.
6. SYNCHRONIZE STAMPS … UPDATE DATABASE is used to bring the catalog and database in sync again.

Without any ALTER TABLE … ADD COLUMN statements, this process would work. However, the usage of ALTER TABLE … ADD COLUMN and the re-CREATEing of the table introduces this discrepancy between the backed up data and what the newer catalog definition would expect.

This can result in invalid data being returned as the output of SELECT statements.

The SYNCHRONIZE STAMPS statement should be used sparingly and only when you are absolutely sure that the catalog definition of the tables and the table data in the database are in sync.