Question on syscat.keycoluse.colseq
Lennart
Sorry for the length of this post, but I think it will illustrate my
point. As part of my deployment process I run a bunch of verification
scripts. Amongst other thing the column order of primary/foreign keys
is validated. The other day I stumbled upon an error. The foreign keys
are working as expected, but syscat.keycoluse.colseq indicates
otherwise(I'm aware that syscat.references can be used instead, this
is more of a nice to know thing) Platform is:
[db2inst1@wbv7d ~/lelle/keycoluse]$ uname -a
Linux wbv7d 2.6.9-67.ELsmp #1 SMP Wed Nov 7 13:58:04 EST 2007 i686
i686 i386 GNU/Linux
[db2inst1@wbv7d ~/lelle/keycoluse]$ db2level
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release
"SQL09050"
with level identifier "03010107".
Informational tokens are "DB2 v9.5.0.0", "s071001", "LINUXIA3295", and
Fix Pack
"0".
Product is installed at "/opt/IBM/db2/V9.5".
Sample tables:
DROP TABLE NYATMP. COURSE_OFFERING;
CREATE TABLE NYATMP. COURSE_OFFERING (
EDUCATIONORG_ID CHAR(3) NOT NULL ,
COURSEOFFERING_ID CHAR(5) NOT NULL ,
ADMISSIONROUND_ID CHAR(10) NOT NULL
) IN USERSPACE1 ;
CREATE UNIQUE INDEX NYATMP.XPKCOURSE_OFFERING ON NYATMP.
COURSE_OFFERING
( ADMISSIONROUND_ID, EDUCATIONORG_ID, COURSEOFFERING_ID) CLUSTER
ALLOW REVERSE SCANS;
CREATE UNIQUE INDEX NYATMP.XAK1COURSE_OFFER ON NYATMP.COURSE_OFFERING
(COURSEOFFERING_ID ASC, EDUCATIONORG_ID ASC, ADMISSIONROUND_ID
ASC)
ALLOW REVERSE SCANS;
ALTER TABLE NYATMP. COURSE_OFFERING ADD CONSTRAINT XPKCOURSE_OFFERING
PRIMARY KEY ( ADMISSIONROUND_ID, EDUCATIONORG_ID,
COURSEOFFERING_ID);
DROP TABLE NYATMP.COURSE_OFFERING_ADV_PRG_ELIG;
CREATE TABLE NYATMP.COURSE_OFFERING_ADV_PRG_ELIG (
ADMISSIONROUND_ID CHAR(10) NOT NULL,
EDUCATIONORG_ID CHAR(3) NOT NULL,
COURSEOFFERING_ID CHAR(5) NOT NULL,
ELIG_COLLECTION_ID INT NOT NULL,
ELIG_COMPONENTTYPE_ID SMALLINT DEFAULT 0 NOT NULL,
ELIG_TYPE SMALLINT DEFAULT 3 NOT NULL
) IN USERSPACE1 ;
CREATE UNIQUE INDEX NYATMP.XPKCO_ADV_PRG_ELG ON
NYATMP.COURSE_OFFERING_ADV_PRG_ELIG
(ADMISSIONROUND_ID, EDUCATIONORG_ID, COURSEOFFERING_ID) INCLUDE
(ELIG_COLLECTION_ID, ELIG_COMPONENTTYPE_ID, ELIG_TYPE) ALLOW REVERSE
SCANS ;
ALTER TABLE NYATMP.COURSE_OFFERING_ADV_PRG_ELIG ADD CONSTRAINT
XPKCO_ADV_PRG_ELG
PRIMARY KEY (ADMISSIONROUND_ID, EDUCATIONORG_ID,
COURSEOFFERING_ID) ;
ALTER TABLE NYATMP.COURSE_OFFERING_ADV_PRG_ELIG ADD CONSTRAINT
XFK1CO_ADV_PRG_ELG
FOREIGN KEY (ADMISSIONROUND_ID, EDUCATIONORG_ID,
COURSEOFFERING_ID)
REFERENCES NYATMP.COURSE_OFFERING
ON DELETE CASCADE
ON UPDATE RESTRICT ;
Query:
db2 -tvf q.sql
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 = 'NYATMP' AND K.TABNAME IN
('COURSE_OFFERING_ADV_PRG_ELIG','COURSE_OFFERING') AND K.CONSTNAME IN
('XFK1CO_ADV_PRG_ELG','XPKCOURSE_OFFERING') ORDER BY
K.TABNAME,K.CONSTNAME,K.COLSEQ
TABNAME COLNAME CONSTNAME
COLSEQ
------------------------- ------------------------- ------------------
------
COURSE_OFFERING ADMISSIONROUND_ID
XPKCOURSE_OFFERING 1
COURSE_OFFERING EDUCATIONORG_ID
XPKCOURSE_OFFERING 2
COURSE_OFFERING COURSEOFFERING_ID
XPKCOURSE_OFFERING 3
COURSE_OFFERING_ADV_PRG_E COURSEOFFERING_ID
XFK1CO_ADV_PRG_ELG 1
COURSE_OFFERING_ADV_PRG_E EDUCATIONORG_ID
XFK1CO_ADV_PRG_ELG 2
COURSE_OFFERING_ADV_PRG_E ADMISSIONROUND_ID
XFK1CO_ADV_PRG_ELG 3
6 record(s) selected.
I.e. the col order in XPKCOURSE_OFFERING starts with
ADMISSIONROUND_ID, but in XFK1CO_ADV_PRG_ELG it starts with
COURSEOFFERING_ID
I get the expected result from my query if I ether
a) create XAK1COURSE_OFFERING before XPKCOURSE_OFFERING (might be pure
coincidence)
b) XAK1COURSE_OFFERING is created as a not UNIQUE index
c) XAK1COURSE_OFFERING is not created
However, if I create tables as above, and then drop
XAK1COURSE_OFFERING, the colseq is still in the /wrong/ order.
I was under the impression that colseq should indicate the position in
the key, not in the index, but in this case that is apparently not so.
Now finally my question :-) Is this deliberate and thus keycoluse
doesn't say anything about how the key is created, but rather how the
underlaying index is created?
/Lennart
Lennart
[...]
> The other day I stumbled upon an error. The foreign keys
> are working as expected, but syscat.keycoluse.colseq indicates
> otherwise(I'm aware that syscat.references can be used instead, this
> is more of a nice to know thing)
Hmm, on second thought. Looking in:
SQL Reference Volume 1, Version 8. (havent checked 9.5 yet) in the
comment on syscat.references it says:
FK_COLNAMES:"List of foreign key column names. Warning: This column
will be removed in the future.Use SYSCAT.KEYCOLUSE for this
information."
PK_COLNAMES: "List of parent key column names. Warning: This column
will be removed in the future. Use SYSCAT.KEYCOLUSE for this
information."
But as shown in my earlier post, KEYCOLUSE.COLSEQ does not necessarily
reflect the colorder in the key. Reflections anyone?
/Lennart
[...]
Dave Hughes
Indeed, FK_COLNAMES and PK_COLNAMES are still there in 9.5, but they've
been deprecated for a while now. I tested out the DDL you posted on our
9.5 FP1 box and came up with the exact same results (incorrect ordering
in KEYCOLUSE).
Insertions and deletions to both tables worked fine, so the keys are
defined properly - it's just that SYSCAT.KEYCOLUSE isn't reporting them
correctly. The deprecated FK_COLNAMES and PK_COLNAMES columns are
reporting them in the correct order.
I also tested the following query, which is a tweaked version of a
query I use in my documentation system for obtaining the mapping of
foreign key columns to primary key columns:
SELECT
SUBSTR(R.CONSTNAME, 1, 18) AS KEYNAME,
SUBSTR(KF.COLNAME, 1, 18) AS COLNAME,
SUBSTR(KP.COLNAME, 1, 18) AS REFCOLNAME
FROM
SYSCAT.REFERENCES R
INNER JOIN SYSCAT.KEYCOLUSE KF
ON R.TABSCHEMA = KF.TABSCHEMA
AND R.TABNAME = KF.TABNAME
AND R.CONSTNAME = KF.CONSTNAME
INNER JOIN SYSCAT.KEYCOLUSE KP
ON R.REFTABSCHEMA = KP.TABSCHEMA
AND R.REFTABNAME = KP.TABNAME
AND R.REFKEYNAME = KP.CONSTNAME
WHERE
R.TABNAME = 'COURSE_OFFERING_ADV_PRG_ELIG'
AND KF.COLSEQ = KP.COLSEQ
ORDER BY
R.TABSCHEMA,
R.TABNAME,
R.CONSTNAME,
KF.COLSEQ
WITH UR;
Naturally, the results were:
KEYNAME COLNAME REFCOLNAME
------------------ ------------------ ------------------
XFK1CO_ADV_PRG_ELG COURSEOFFERING_ID ADMISSIONROUND_ID
XFK1CO_ADV_PRG_ELG EDUCATIONORG_ID EDUCATIONORG_ID
XFK1CO_ADV_PRG_ELG ADMISSIONROUND_ID COURSEOFFERING_ID
Oh dear. I'd suggest opening a PMR for this - looks like a bug to me.
Cheers,
Dave.
Lennart
[...]
>
> Oh dear. I'd suggest opening a PMR for this - looks like a bug to me.
>
Thanks for taking the time Dave. I will open a PMR and post back
whatever the outcome is. Always nice to have someone else's opinion
before rushing ahead and opening a PMR.
Thanks
/Lennart
Arun Srinivasan
Am confused...
DROP TABLE ARUN.TEST2;
DROP TABLE ARUN.TEST1;
create table arun.test1 (a char(1) not null, b char(1) not null);
create unique index arun.test1_idx2 on arun.test1(b,a) CLUSTER;
create unique index arun.test1_idx1 on arun.test1(a,b) ;
alter table arun.test1 add constraint pk_test1 primary key(a,b);
create table arun.test2(a2 char(1),b2 char(1));
create unique index arun.test2_idx1 on arun.test2(a2,b2) cluster;
alter table arun.test2 ADD constraint fk_test2 foreign key(b2,a2)
references arun.test1;
SELECT
SUBSTR(R.CONSTNAME, 1, 18) AS KEYNAME,
SUBSTR(KF.COLNAME, 1, 18) AS COLNAME,
SUBSTR(KP.COLNAME, 1, 18) AS REFCOLNAME
FROM
SYSCAT.REFERENCES R
INNER JOIN SYSCAT.KEYCOLUSE KF
ON R.TABSCHEMA = KF.TABSCHEMA
AND R.TABNAME = KF.TABNAME
AND R.CONSTNAME = KF.CONSTNAME
INNER JOIN SYSCAT.KEYCOLUSE KP
ON R.REFTABSCHEMA = KP.TABSCHEMA
AND R.REFTABNAME = KP.TABNAME
AND R.REFKEYNAME = KP.CONSTNAME
WHERE
R.TABSCHEMA = 'ARUN' AND
R.TABNAME = 'TEST2'
AND KF.COLSEQ = KP.COLSEQ
ORDER BY
R.TABSCHEMA,
R.TABNAME,
R.CONSTNAME,
KF.COLSEQ
WITH UR;
'FK_TEST2 ' 'B2 ' 'A '
'FK_TEST2 ' 'A2 ' 'B '
This is huge....
Lennart
FYI, We have opened a PMR on this. IBM admitted that it is likely a
bug, but since it is not critical to us we agreed to lower the
severity of this PMR to 4. If anyone thinks is more urgent than that
and want the PMR number as a reference, contact me via email,
/Lennart