Fixes are available
DB2 Version 9.5 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
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