IBM Support

LI73962: THE KEYCOLUSE.COLSEQ COLUMN NOT SHOWING THE CORRECT COLUMN SEQUENCE IN THE FOREIGN KEY IN V9.5 .

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • The KEYCOLUSE.COLSEQ column not showing the correct column
    sequence in the foreign key if an existing unique index is used
    for the primary key (SQL0598W) and the ordering of the columns
    between the index and the primary key don't match.
    
    
    
    For example consider the following-
    create db test ;
    connect to test ;
    
    
    CREATE TABLE  SCHEMA.A   (
              EDUCATIONORG_ID                 CHAR(3) NOT NULL ,
              COURSEOFFERING_ID               CHAR(5) NOT NULL ,
              ADMISSIONROUND_ID               CHAR(10) NOT NULL
    ) IN  USERSPACE1  ;
    
    
    
    CREATE UNIQUE INDEX SCHEMA.UNIINDEX ON SCHEMA.A
    (COURSEOFFERING_ID ASC, EDUCATIONORG_ID ASC, ADMISSIONROUND_ID
    ASC) ALLOW REVERSE SCANS;
    
    
    
    ALTER TABLE SCHEMA.A  ADD CONSTRAINT PRIMKEY PRIMARY KEY (
    ADMISSIONROUND_ID, EDUCATIONORG_ID, COURSEOFFERING_ID);
    
    CREATE TABLE SCHEMA.B (
          ADMISSIONROUND_ID       CHAR(10) NOT NULL,
          EDUCATIONORG_ID         CHAR(3) NOT NULL,
          COURSEOFFERING_ID       CHAR(5) NOT NULL,
    
    ) IN USERSPACE1 ;
    
    
    
    ALTER TABLE SCHEMA.B ADD CONSTRAINT FRKEY FOREIGN KEY
    (ADMISSIONROUND_ID, EDUCATIONORG_ID,COURSEOFFERING_ID)
    REFERENCES SCHEMA.A ON DELETE CASCADE ON UPDATE RESTRICT ;
    
    ................................................................
    .....................................
    Note-For table A the Primary key PRIMKEY internally uses unique
    index UNIINDEX(SQL0598W) as the columns in the
    unique index and priamry key are the same,though the order of
    the column is different.
    
    Now run the following select statement to retrieve the column
    sequence in the primary and foreign key.
    
    SELECT  substr(K.TABNAME,1,25) tabname, substr(K.COLNAME,1,25)
    colname, substr(K.CONSTNAME,1,18) constname, K.COLSEQ FROM
    SYSCAT.KEYCOLUSE K WHERE K.TABSCHEMA = 'SCHEMA' AND K.TABNAME IN
    ('B','A') ;
    
    This should ideally show the following order .Which is the case
    for V8 .
    
    ................................................................
    .....................
    TABNAME                   COLNAME                   CONSTNAME
    COLSEQ
    ------------------------- -------------------------
    ------------------ ------
    A   ADMISSIONROUND_ID         PRIMKEY    1
    A   EDUCATIONORG_ID           PRIMKEY    2
    A   COURSEOFFERING_ID         PRIMKEY    3
    B   ADMISSIONROUND_ID         FRKEY                   1
    B   EDUCATIONORG_ID           FRKEY                   2
    B   COURSEOFFERING_ID         FRKEY     3
    
      6 record(s) selected.
    ................................................................
    ........................
    
    But in V9.5 we get the following.
    ................................................................
    ........................
    TABNAME                   COLNAME                   CONSTNAME
    COLSEQ
    ------------------------- -------------------------
    ------------------ ------
    A   ADMISSIONROUND_ID         PRIMKEY    1
    A   EDUCATIONORG_ID           PRIMKEY    2
    A   COURSEOFFERING_ID         PRIMKEY    3
    B   COURSEOFFERING_ID         FRKEY                   1
    B   EDUCATIONORG_ID           FRKEY                   2
    B   ADMISSIONROUND_ID         FRKEY     3
    
      6 record(s) selected.
    
    ................................................................
    .............................
    
    Here the column sequence in the foreign key is shown to be
    COURSEOFFERING_ID(1),EDUCATIONORG_ID(2),ADMISSIONROUND_ID(3),
    while the actual sequence as per the
    DDL statement is
    ADMISSIONROUND_ID(1),EDUCATIONORG_ID(2),COURSEOFFERING_ID(3) .
    
    This APAR fixes this anomaly and displays the column sequence
    properly.
    
    It is importnat to note that this anomay happens only if both
    the following conditions are satisfied.
    1)There is a pre existing unique index on the parent table with
    same columns as in the priamry key
    2)The order of the column is not same as that in the primary
    key.
    
    If there is no preexisting unique index on the parent table we
    won't see this behaviour.
    Also this issue is specific to V9.5 and doesn't happen in V8.
    

Local fix

  • NA.
    

Problem summary

  • The KEYCOLUSE.COLSEQ column not showing the correct column
    sequence in the foreign key if an existing unique index is used
    for the primary key (SQL0598W) and the ordering of the columns
    between the index and the primary key don't match.
    
    
    
    For example consider the following-
    create db test ;
    connect to test ;
    
    
    CREATE TABLE  SCHEMA.A   (
              EDUCATIONORG_ID                 CHAR(3) NOT NULL ,
              COURSEOFFERING_ID               CHAR(5) NOT NULL ,
              ADMISSIONROUND_ID               CHAR(10) NOT NULL
    ) IN  USERSPACE1  ;
    
    
    
    CREATE UNIQUE INDEX SCHEMA.UNIINDEX ON SCHEMA.A
    (COURSEOFFERING_ID ASC, EDUCATIONORG_ID ASC, ADMISSIONROUND_ID
    ASC) ALLOW REVERSE SCANS;
    
    
    
    ALTER TABLE SCHEMA.A  ADD CONSTRAINT PRIMKEY PRIMARY KEY (
    ADMISSIONROUND_ID, EDUCATIONORG_ID, COURSEOFFERING_ID);
    
    CREATE TABLE SCHEMA.B (
          ADMISSIONROUND_ID       CHAR(10) NOT NULL,
          EDUCATIONORG_ID         CHAR(3) NOT NULL,
          COURSEOFFERING_ID       CHAR(5) NOT NULL,
    
    ) IN USERSPACE1 ;
    
    
    
    ALTER TABLE SCHEMA.B ADD CONSTRAINT FRKEY FOREIGN KEY
    (ADMISSIONROUND_ID, EDUCATIONORG_ID,COURSEOFFERING_ID)
    REFERENCES SCHEMA.A ON DELETE CASCADE ON UPDATE RESTRICT ;
    
    ................................................................
    .....................................
    Note-For table A the Primary key PRIMKEY internally uses unique
    index UNIINDEX(SQL0598W) as the columns in the
    unique index and priamry key are the same,though the order of
    the column is different.
    
    Now run the following select statement to retrieve the column
    sequence in the primary and foreign key.
    
    SELECT  substr(K.TABNAME,1,25) tabname, substr(K.COLNAME,1,25)
    colname, substr(K.CONSTNAME,1,18) constname, K.COLSEQ FROM
    SYSCAT.KEYCOLUSE K WHERE K.TABSCHEMA = 'SCHEMA' AND K.TABNAME IN
    ('B','A') ;
    
    This should ideally show the following order .Which is the case
    for V8 .
    
    ................................................................
    .....................
    TABNAME                   COLNAME                   CONSTNAME
    COLSEQ
    ------------------------- -------------------------
    ------------------ ------
    A   ADMISSIONROUND_ID         PRIMKEY    1
    A   EDUCATIONORG_ID           PRIMKEY    2
    A   COURSEOFFERING_ID         PRIMKEY    3
    B   ADMISSIONROUND_ID         FRKEY                   1
    B   EDUCATIONORG_ID           FRKEY                   2
    B   COURSEOFFERING_ID         FRKEY     3
    
      6 record(s) selected.
    ................................................................
    ........................
    
    But in V9.5 we get the following.
    ................................................................
    ........................
    TABNAME                   COLNAME                   CONSTNAME
    COLSEQ
    ------------------------- -------------------------
    ------------------ ------
    A   ADMISSIONROUND_ID         PRIMKEY    1
    A   EDUCATIONORG_ID           PRIMKEY    2
    A   COURSEOFFERING_ID         PRIMKEY    3
    B   COURSEOFFERING_ID         FRKEY                   1
    B   EDUCATIONORG_ID           FRKEY                   2
    B   ADMISSIONROUND_ID         FRKEY     3
    
      6 record(s) selected.
    
    ................................................................
    .............................
    
    Here the column sequence in the foreign key is shown to be
    COURSEOFFERING_ID(1),EDUCATIONORG_ID(2),ADMISSIONROUND_ID(3),
    while the actual sequence as per the
    DDL statement is
    ADMISSIONROUND_ID(1),EDUCATIONORG_ID(2),COURSEOFFERING_ID(3) .
    
    This APAR fixes this anomaly and displays the column sequence
    properly.
    
    It is importnat to note that this anomay happens only if both
    the following conditions are satisfied.
    1)There is a pre existing unique index on the parent table with
    same columns as in the priamry key
    2)The order of the column is not same as that in the primary
    key.
    
    If there is no preexisting unique index on the parent table we
    won't see this behaviour.
    Also this issue is specific to V9.5 and doesn't happen in V8.
    
    LOCAL FIX:
    NA.
    

Problem conclusion

  • First fixed in DB2 UDB Version 9.5, FixPak 4
    

Temporary fix

Comments

APAR Information

  • APAR number

    LI73962

  • Reported component name

    DB2 UDB WSE LIN

  • Reported component ID

    5765F3504

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-12-16

  • Closed date

    2009-04-21

  • Last modified date

    2009-04-21

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

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

    IC74858 IC74859

Fix information

  • Fixed component name

    DB2 UDB WSE LIN

  • Fixed component ID

    5765F3504

Applicable component levels

  • R950 PSY

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
21 April 2009