IZ12171: IDENTITY/SEQ. COLumns ARE RESET back to 'START WITH' value AFTER'RESTART' AND 'SET' ARE ISSUED IN SEQUENCE AGAINST THE TABLE

DB2 Version 9.1 Fix Pack 7  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 5   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

APAR status

• Closed as program error.

Error description

• An Identiy or Sequence column starting value can be set back to
  the 'START WITH' value after issuing a set of alter statements
  against the table. The two alter statements need to be issued in
  sequence that consists of a combination of a restart and set
  statement.  (NOTE:  Regardless of the restart value used, if a
  set statement is issued right afterwards then the starting value
  will be set back to 'START WITH' value used when the table was
  first created.)
  
  For example, given the following table T1 (C1 int, C2
  [identity/sequence])
  
       C1         C2
       ---------- ----------
                1          1
                2          2
                3          3
                4          4
                5          5
  
  
  NOTE:  The DDL for the T1 and the identity(sequence) column has
  been left out for simplicity.  But the 'START WITH' value was 1.
  
  If the following two alter statements are issued against table,
  T1, for the identity(sequence) column:
  
       ALTER TABLE T1 ALTER C2 RESTART WITH 100
       ALTER TABLE T1 ALTER C2 SET CACHE 1000
  
  the following insert statement:
  
       INSERT INTO T1 VALUES (6)
  
  should produce the following row in the table:
  
       C1         C2
       ---------- ----------
                6        100
  
  however, instead the following row is produced:
  
       C1         C2
       ---------- ----------
                6          1
  
  so the full table would be:
  
       C1         C2
       ---------- ----------
                1          1
                2          2
                3          3
                4          4
                5          5
                6          1
  
  This will occur for every set statement issued against an
  identity(/sequence) column righ after a restart is issued
  against the same column.
  

Local fix

• Do not issue a set statement right after issuing a restart
  statement in the alter command against an identity(/sequence)
  column.
  
  OR
  
  The 'RESET' and 'SET' can be issued in the same ALTER DDL
  statment.  For example:
  
       ALTER TABLE T1 ALTER C2 RESTART WITH 100 SET CACHE 1000
  

Problem summary

• Users Affected:  ALL
  
  Problem Description:  Please refer to the 'Error Description.'
  
  Problem Summary:  Please refer to the 'Error Description.'
  

Problem conclusion

• Problem was first fixed in Version 9.1 FixPack 4 (s080512.)
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  IZ12172 IZ12174

Fix information

• Fixed component name

  DB2 UDB WSE AIX

• Fixed component ID

  5765F3500

Applicable component levels

• R810 PSN

     UP

• R820 PSN

     UP

• R910 PSN

     UP

• R950 PSN

     UP