Query System Catalog for composite primary key

dajense · #1 (**permalink**) 01-08-10, 12:29

Hi,
can somebody tell me how I can query the system catalog views for composity primary keys associated to columns?

A query like

Code:

SELECT 	KEYSEQ  
FROM 	SYSCAT.COLUMNS 
WHERE 	TABSCHEMA NOT LIKE 'SYS%'
ORDER BY 	COLNO

Will show me '1' for primary keys and '2' for foreign keys. The problem is that elements of composite keys that are also foreign keys are allways designated '2'. But I need both that information.

Thx.

ARWinner · #2 (**permalink**) 01-08-10, 12:59

Something like this should work:

select t.constname,c.colname,c.colseq from syscat.tabconst as t inner join syscat.keycoluse as c on (t.constname = c.constname) where t.tabschema = 'MYSCHEMA' and t.tabname = 'MYTABLE' and t.type = 'P' order by c.colseq

Andy

dajense · #3 (**permalink**) 01-08-10, 13:14

Thanks Andy,
that helped. Now I just have to figure out how to join this on syscat.columns without having duplicated rows :-)

ARWinner · #4 (**permalink**) 01-08-10, 13:17

Wouldn't that just be a inner join?

inner join syscat.columns as l on (c.tabschema = l.tabschema and c.tabname = l.tabname and c.colname = l.colname)

Andy

dajense · #5 (**permalink**) 01-08-10, 13:29

I guess, but I can't really say right now, cause I am just not fit enough with SQL, and I just noticed that I am running out of time (Deadline on Mondy). I guess I am just gonna do it by hand for now.
Thanks for your help

tonkuma · #6 (**permalink**) 01-08-10, 15:24

It might be too late...

Code:

------------------------------ Commands Entered ------------------------------
SELECT
       VARCHAR(
       CASE keyseq
       WHEN 1 THEN
            c.tabschema || '.' || c.tabname
       ELSE ''
       END
       , 40)  AS table_name
     , CASE keyseq
       WHEN 1 THEN
            VARCHAR(tc.constname , 20)
       ELSE ''
       END  AS constraint_name
     , c.keyseq
     , VARCHAR(c.colname , 30) AS column_name
  FROM
       syscat.columns   c
  JOIN
       syscat.tabconst  tc
   ON  tc.tabschema = c.tabschema
   AND tc.tabname   = c.tabname
   AND tc.type      = 'P'
 WHERE c.tabschema NOT LIKE 'SYS%'
   AND c.keyseq IS NOT NULL
 ORDER BY
       c.tabschema
     , c.tabname
     , c.keyseq
;
------------------------------------------------------------------------------

TABLE_NAME                               CONSTRAINT_NAME      KEYSEQ COLUMN_NAME                   
---------------------------------------- -------------------- ------ ------------------------------
DB2ADMIN.ACT                             PK_ACT                    1 ACTNO                         
DB2ADMIN.C                               SQL091014175510600        1 PK                            
DB2ADMIN.CATALOG                         PK_CATALOG                1 NAME                          
DB2ADMIN.CUSTOMER                        PK_CUSTOMER               1 CID                           
DB2ADMIN.DEPARTMENT                      PK_DEPARTMENT             1 DEPTNO                        
DB2ADMIN.EMPLOYEE                        PK_EMPLOYEE               1 EMPNO                         
DB2ADMIN.EMP_PHOTO                       PK_EMP_PHOTO              1 EMPNO                         
                                                                   2 PHOTO_FORMAT                  
DB2ADMIN.EMP_RESUME                      PK_EMP_RESUME             1 EMPNO                         
                                                                   2 RESUME_FORMAT                 
DB2ADMIN.EXPLAIN_DIAGNOSTIC              SQL090926100903010        1 EXPLAIN_REQUESTER             
                                                                   2 EXPLAIN_TIME                  
                                                                   3 SOURCE_NAME                   
                                                                   4 SOURCE_SCHEMA                 
                                                                   5 SOURCE_VERSION                
                                                                   6 EXPLAIN_LEVEL                 
                                                                   7 STMTNO                        
                                                                   8 SECTNO                        
                                                                   9 DIAGNOSTIC_ID                 
DB2ADMIN.EXPLAIN_INSTANCE                SQL090926100901650        1 EXPLAIN_REQUESTER             
                                                                   2 EXPLAIN_TIME                  
                                                                   3 SOURCE_NAME                   
                                                                   4 SOURCE_SCHEMA                 
                                                                   5 SOURCE_VERSION                
DB2ADMIN.EXPLAIN_STATEMENT               SQL090926100902000        1 EXPLAIN_REQUESTER             
                                                                   2 EXPLAIN_TIME                  
                                                                   3 SOURCE_NAME                   
                                                                   4 SOURCE_SCHEMA                 
                                                                   5 SOURCE_VERSION                
                                                                   6 EXPLAIN_LEVEL                 
                                                                   7 STMTNO                        
                                                                   8 SECTNO                        
DB2ADMIN.INVENTORY                       PK_INVENTORY              1 PID                           
DB2ADMIN.LOAD_MESSAGES                   SQL091224024121240        1 MSG_LOAD_TIME                 
                                                                   2 LINE_ID                       
DB2ADMIN.LOAD_STATISTICS                 SQL091224024148390        1 MSG_LOAD_TIME                 
DB2ADMIN.P                               SQL091014175451670        1 PK                            
DB2ADMIN.PRODUCT                         PK_PRODUCT                1 PID                           
DB2ADMIN.PRODUCTSUPPLIER                 PK_PRODUCTSUPPLIER        1 PID                           
                                                                   2 SID                           
DB2ADMIN.PROJACT                         PK_PROJACT                1 PROJNO                        
                                                                   2 ACTNO                         
                                                                   3 ACSTDATE                      
DB2ADMIN.PROJECT                         PK_PROJECT                1 PROJNO                        
DB2ADMIN.PURCHASEORDER                   PK_PURCHASEORDER          1 POID                          
DB2ADMIN.STRING_AGG                      SQL091220213303920        1 ID                            
                                                                   2 SUB_ID                        
DB2ADMIN.SUPPLIERS                       PK_PRODUCTSUPPLIER        1 SID                           
DB2ADMIN.TABLE_NAME                      SQL091218085147390        1 A                             
                                                                   2 B                             
DB2ADMIN.TEST2                           SQL091117070042020        1 PK                            

  51 record(s) selected.

Internet Services

Web Development

Digital Marketing

Consumer Tech