Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2006
    Location
    Bangalore
    Posts
    57

    Exclamation Unanswered: Query to count number of coulmns in constraints

    Hi All,

    I am looking for a query in DB2 which would work same as below oracle query.
    HTML Code:
    SELECT B.CONSTRAINT_NAME,B.CONSTRAINT_TYPE,B.TABLE_NAME,COUNT(A.COLUMN_NAME) FROM DBA_CONS_columns A,DBA_CONSTRAINTS B 
    WHERE A.CONSTRAINT_NAME=B.CONSTRAINT_NAME  AND B.OWNER='HR'
    GROUP BY B.CONSTRAINT_NAME,B.CONSTRAINT_TYPE,B.TABLE_NAME;
    So far i tried below query but not able to count the number coulumns in each constraints

    HTML Code:
    DB2 "SELECT A.CONSTNAME AS CONSTRAINT_NAME, A.TYPE AS CONSTRAINT_TYPE, A.TABNAME AS TABLE_NAME FROM SYSCAT.TABCONST A WHERE A.TABSCHEMA='SAMPLE'
    Below query works only for foreign key constaints
    HTML Code:
    SELECT A.CONSTNAME AS CONSTRAINT_NAME, A.TYPE AS CONSTRAINT_TYPE, A.TABNAME AS TABLE_NAME , B.COLCOUNT AS COL_COUNT 
    FROM SYSCAT.TABCONST A, SYSCAT.REFERENCES B WHERE B.CONSTNAME=A.CONSTNAME AND B.TABNAME=A.TABNAME AND A.TABSCHEMA='SAMPLE' 
    ORDER BY A.CONSTNAME
    Now the problem is how can i count the number of columns used in each constraints?
    Ritesh Kumar Singh
    IBM Certified DB2 DBA for LUW
    **Knowledge Is Theft If Not Shared !!**

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Do you mean that you, also, want to get the count of columns in a unique constraint, primary key and check constraints?
    Dave

  3. #3
    Join Date
    Jul 2006
    Location
    Bangalore
    Posts
    57
    Yes, This is what i want to do.
    Last edited by singhipst; 07-13-10 at 01:42.
    Ritesh Kumar Singh
    IBM Certified DB2 DBA for LUW
    **Knowledge Is Theft If Not Shared !!**

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You may also want to look at SYSCAT.KEYCOLUSE and SYSCAT.COLCHECKS.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Used syscat.tabconst, syscat.keycoluse, syscat.references and syscat.colchecks,
    then format output.
    (Some people don't like to format output in an SQL query.)

    Sample result:

    Code:
    CONSTRAINT_NAME                CONSTRAINT_TYPE         SCHEMA_NAME        TABLE_NAME                       COL_COUNT   PARENT_CNT 
    ------------------------------ ----------------------- ------------------ -------------------------------- ----------- -----------
    SQL100714014017460             P:Primary key           DB2ADMIN           CONSTRAINT_TEST                            1           0
    SQL100714014017470             U:Unique                DB2ADMIN           CONSTRAINT_TEST                            3           0
    SQL100714014017480             F:Foreign key           DB2ADMIN           CONSTRAINT_TEST                            2           0
    SQL100714014017610             F:Foreign key           DB2ADMIN           CONSTRAINT_TEST                            1           0
    CHECK_CONST_TEST_FB_FA1_FA2    K:Check                 DB2ADMIN           CONSTRAINT_TEST                            3           0
    FUNC_DEP_UA1_UA2_UA3           I:Functional dependency DB2ADMIN           CONSTRAINT_TEST                            2           1
    
      6 record(s) selected.
    Query:

    Code:
    SELECT SUBSTR(tc.constname , 1 , 30) AS constraint_name
         , tc.type ||
           SUBSTR( ':Primary key          :Unique               :Foreign key          :Check                :Functional dependency'
                 , LOCATE(tc.type , 'PUFKI') * 22 - 21
                 , 22
                 ) AS constraint_type
         , SUBSTR(tc.tabschema , 1 , 18) AS schema_name
         , SUBSTR(tc.tabname ,   1 , 32) AS table_name
         , COALESCE( MAX(kc.colseq)
                   , MAX(rf.colcount)
                   , COUNT(CASE WHEN cc.usage IN('R' , 'D') THEN 0 END)
                   ) AS col_count
         , COUNT(CASE cc.usage WHEN 'P' THEN 0 END) parent_cnt
      FROM syscat.tabconst tc
      LEFT OUTER JOIN
           syscat.keycoluse kc
       ON  tc.type IN ( 'P' -- Primary key
                      , 'U' -- Unique
                      )
       AND kc.tabschema  = tc.tabschema
       AND kc.tabname    = tc.tabname
       AND kc.constname  = tc.constname
      LEFT OUTER JOIN
           syscat.references rf
       ON  tc.type = 'F' -- Foreign key
       AND rf.tabschema  = tc.tabschema
       AND rf.tabname    = tc.tabname
       AND rf.constname  = tc.constname
      LEFT OUTER JOIN
           syscat.colchecks cc
       ON  tc.type IN ( 'K' -- Check
                      , 'I' -- Fuctional dependency
                      )
       AND cc.tabschema  = tc.tabschema
       AND cc.tabname    = tc.tabname
       AND cc.constname  = tc.constname
     WHERE tc.tabname = 'CONSTRAINT_TEST'
     GROUP BY
           tc.constname
         , tc.type
         , tc.tabschema
         , tc.tabname
     ORDER BY
           tc.tabschema
         , tc.tabname
         , LOCATE(tc.type , 'PUFKI')
    ;
    Test data:

    Code:
    ------------------------------ Commands Entered ------------------------------
    ALTER TABLE employee
    ADD CONSTRAINT unique_empno_lastname
    UNIQUE(empno , lastname)
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE constraint_test
    ( pk  INTEGER NOT NULL PRIMARY KEY
    , ua1 INTEGER NOT NULL
    , ua2 INTEGER NOT NULL
    , ua3 INTEGER NOT NULL
    , fa1 CHAR(6) NOT NULL
    , fa2 VARCHAR(15)
    , fb  CHAR(3)
    , UNIQUE(ua1 , ua2 , ua3)
    , FOREIGN KEY(fa1 , fa2)
        REFERENCES employee(empno , lastname)
    , FOREIGN KEY(fb)
        REFERENCES department(deptno)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    ALTER TABLE constraint_test
    ADD CONSTRAINT check_const_test_fb_fa1_fa2
    CHECK(fb IS NULL OR (fa1 IS NOT NULL AND fa2 IS NOT NULL) );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    ALTER TABLE constraint_test
    ADD CONSTRAINT func_dep_ua1_ua2_ua3
    CHECK(ua3 DETERMINED BY (ua1 , ua2) )
    NOT ENFORCED
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.

  6. #6
    Join Date
    Jul 2006
    Location
    Bangalore
    Posts
    57
    Thank you tonkuma,

    I must appreciate your effort.

    Thanks guys :-)
    Last edited by singhipst; 07-14-10 at 07:17.
    Ritesh Kumar Singh
    IBM Certified DB2 DBA for LUW
    **Knowledge Is Theft If Not Shared !!**

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    1) I think that I sould not use strange expression for constraint_type.
    2) The expression for col_count and parent_cnt could make shorter, but a little perplexed.

    Code:
    SELECT SUBSTR(tc.constname , 1 , 30) AS constraint_name
         , tc.type ||
        /* SUBSTR( ':Primary key          :Unique               :Foreign key          :Check                :Functional dependency'
                 , LOCATE(tc.type , 'PUFKI') * 22 - 21
                 , 22
                 )
        */
           CASE tc.type
           WHEN 'P' THEN ':Primary key'
           WHEN 'U' THEN ':Unique'
           WHEN 'F' THEN ':Foreign key'
           WHEN 'K' THEN ':Check'
           WHEN 'I' THEN ':Functional dependency'
           ELSE '*** Unknown ***'
           END  AS constraint_type
         , SUBSTR(tc.tabschema , 1 , 18) AS schema_name
         , SUBSTR(tc.tabname ,   1 , 32) AS table_name
         , COALESCE( MAX(kc.colseq)
                   , MAX(rf.colcount)
                /* , COUNT(CASE WHEN cc.usage IN('R' , 'D') THEN 0 END) */
                   , SUM( SIGN( LOCATE(cc.usage , 'RD') ) )
                   ) AS col_count
      /* , COUNT(CASE cc.usage WHEN 'P' THEN 0 END) AS parent_cnt */
         , SUM( LOCATE(cc.usage , 'P') ) AS parent_cnt
      FROM syscat.tabconst tc
      LEFT OUTER JOIN
           syscat.keycoluse kc
       ON  tc.type IN ( 'P' -- Primary key
                      , 'U' -- Unique
                      )
       AND kc.tabschema  = tc.tabschema
       AND kc.tabname    = tc.tabname
       AND kc.constname  = tc.constname
      LEFT OUTER JOIN
           syscat.references rf
       ON  tc.type = 'F' -- Foreign key
       AND rf.tabschema  = tc.tabschema
       AND rf.tabname    = tc.tabname
       AND rf.constname  = tc.constname
      LEFT OUTER JOIN
           syscat.colchecks cc
       ON  tc.type IN ( 'K' -- Check
                      , 'I' -- Fuctional dependency
                      )
       AND cc.tabschema  = tc.tabschema
       AND cc.tabname    = tc.tabname
       AND cc.constname  = tc.constname
     WHERE tc.tabname = 'CONSTRAINT_TEST'
     GROUP BY
           tc.constname
         , tc.type
         , tc.tabschema
         , tc.tabname
     ORDER BY
           tc.tabschema
         , tc.tabname
         , LOCATE(tc.type , 'PUFKI')
    ;

Posting Permissions

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