Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    17

    Unanswered: Query System Catalog for composite primary key

    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.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Jul 2009
    Posts
    17
    Thanks Andy,
    that helped. Now I just have to figure out how to join this on syscat.columns without having duplicated rows :-)

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Jul 2009
    Posts
    17
    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

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •