Query to count number of coulmns in constraints

singhipst · #1 (**permalink**) 07-12-10, 07:41

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?

dav1mo · #2 (**permalink**) 07-12-10, 10:05

Do you mean that you, also, want to get the count of columns in a unique constraint, primary key and check constraints?
Dave

singhipst · #3 (**permalink**) 07-12-10, 23:47

Yes, This is what i want to do.

n_i · #4 (**permalink**) 07-13-10, 09:14

You may also want to look at SYSCAT.KEYCOLUSE and SYSCAT.COLCHECKS.

tonkuma · #5 (**permalink**) 07-13-10, 14:55

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.

singhipst · #6 (**permalink**) 07-14-10, 06:03

Thank you tonkuma,

I must appreciate your effort.

Thanks guys :-)

tonkuma · #7 (**permalink**) 07-15-10, 11:46

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')
;

Internet Services

Web Development

Digital Marketing

Consumer Tech