Question on syscat.keycoluse.colseq

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@[EMAIL PROTECTED]
 ~/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@[EMAIL PROTECTED]
 ~/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