Question on syscat.keycoluse.colseq
in [DB2]
|
From: Lennart on 27 Jun 2008 04:31 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(a)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(a)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
From: Lennart on 27 Jun 2008 05:10 On Jun 27, 10:31 am, Lennart <Erik.Lennart.Jons...(a)gmail.com> wrote: [...] > 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 [...]
From: Dave Hughes on 27 Jun 2008 09:26 Lennart wrote: > On Jun 27, 10:31 am, Lennart <Erik.Lennart.Jons...(a)gmail.com> wrote: > [...] > > 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? 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.
From: Lennart on 27 Jun 2008 10:18 On Jun 27, 3:26 pm, "Dave Hughes" <d...(a)waveform.plus.com> wrote: [...] > > 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
From: Arun Srinivasan on 30 Jun 2008 11:05
I just executed the following statements but got the result I needed. 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.... |