[USflag] The American Programmer [USflag]
Home Books for Computer Professionals Privacy Terms
           Home   > Programming   > Manuals   > SQL Manuals   > SQL Just Examples

Books on DB2

DB2 Manuals





--THERE ARE VARIOUS CATALOG QUERIES AND MORE ADVANCED QUERIES USING SQL FOR DB2
--THESE WORK, BUT NO GUARANTEES. COMPLIMENTS OF The American Programmer.com
--LAST UPDATE 06/01
-- CHANGE ALL TTTT YOUR TABLE NAME
-- CHANGE ALL CCCC TABLE CREATOR
-- CHANGE ALL DDDD DATA BASE NAME
-- CHANGE ALL VVVV NAME OF THE VIEW



-- 1 TELL ME ALL ABOUT THE TABLES WITH THIS NAME
SELECT NAME, CREATOR, TYPE  --T = TABLE, V = VIEW
  FROM SYSIBM.SYSTABLES
   WHERE NAME     = 'TTTT'
     AND CREATOR  = 'CCCC'
  ORDER BY NAME, CREATOR
;



-- 2 IS THIS A TABLE OR A VIEW?
SELECT 'TABLE', CREATOR, NAME
   FROM SYSIBM.SYSTABLES
   WHERE TYPE    = 'T'
     AND   NAME  = 'TTTT' --TABLE NAME HERE
--   AND CREATOR = 'CCCC' --PUT THE CREATOR NAME HERE
UNION ALL
SELECT 'VIEW', CREATOR, NAME
-- FROM SYSIBM.SYSVIEWS
   FROM SYSIBM.SYSTABLES
   WHERE TYPE    = 'V'
     AND   NAME  = 'TTTT' -- TABLE NAME HERE
--   AND CREATOR = 'CCCC' --PUT THE CREATOR NAME HERE
;



-- 3 THE VIEW XXXXXX REFERS TO BASE TABLE OR VIEW ????????
SELECT 'THE VIEW', DCREATOR, DNAME, 'REFERS TO ',
       'BASE TABLE OR VIEW', BCREATOR, BNAME
   FROM SYSIBM.SYSVIEWDEP
   WHERE DNAME      = 'VVVV'    -- VIEW  NAME
--   AND DCREATOR IN ('CCCC') --PUT THE CREATOR NAME HERE
;




-- 4 WHAT ARE THE VIEWS ON MY BASE TABLE?
 SELECT 'BASE TABLE (OR VIEW!):', VD.BCREATOR, VD.BNAME,
  --VD.BTYPE, --BASE TABLE TYPE, T=TABLE, V=VIEW
  'VIEW:', VD.DCREATOR, VD.DNAME, V.TEXT
  FROM SYSIBM.SYSVIEWDEP VD,
       SYSIBM.SYSVIEWS V
  WHERE VD.DNAME      = V.NAME
    AND   VD.BCREATOR = V.CREATOR
    AND   VD.BNAME    = 'TTTT'    -- INSERT NAME OF VIEW
--  AND   VD.BCREATOR IN ('CCCC') --TABLE CREATOR HERE
  ORDER BY VD.BCREATOR, VD.BNAME, VD.DCREATOR, VD.DNAME
;


--4A WHAT IS THE FULL TEXT OF THE VIEW?
SELECT TEXT
   FROM SYSIBM.SYSVIEWS
   WHERE NAME  = 'VVVVV'  -- INSERT NAME OF VIEW
   AND CREATOR = 'CCCCC'  -- INSERT CREATOR S NAME

;




-- 5 WHERE DOES MY TABLE LIVE?
--   WHAT TABLESPACE AND DATABASE IS MY TABLE IN?
SELECT T.CREATOR, T.NAME AS TABLE_NAME,
       T.TSNAME AS TABLE_SPACE,
       D.NAME AS DATABASE, D.STGROUP, D.BPOOL
  FROM SYSIBM.SYSTABLES T, SYSIBM.SYSDATABASE D
  WHERE T.DBNAME = D.NAME
--  AND T.NAME    = 'TTTT' --FILL IN TABLE NAME
--  AND T.CREATOR = 'CCCC' --FILL IN CREATOR NAME
  ORDER BY T.CREATOR, TABLE_NAME, TABLE_SPACE
;

-- 6 WHAT IS MY TABLESPACE LIKE?
--what.is.ts.like.txt
--SIMPLE TABLESPACE
SELECT T.CREATOR AS TABLE_CREATOR, T.NAME AS TABLE_NAME,
  TS.DBNAME AS DATABASE, TS.NAME AS TABLE_SPACE,
  'SIMPLE'

  FROM SYSIBM.SYSTABLES T, SYSIBM.SYSTABLESPACE TS
  WHERE T.TSNAME      = TS.NAME
    AND T.DBNAME      = TS.DBNAME
    AND TS.PARTITIONS = 0
    AND TS.SEGSIZE    = 0
--  AND T.NAME        = 'TTTT' --FILL IN YOUR TABLE S NAME
--  AND T.CREATOR     = 'CCCC' --FILL IN CREATOR NAME
  UNION ALL

--SEGMENTED
SELECT T.CREATOR AS TABLE_CREATOR, T.NAME AS TABLE_NAME,
  TS.DBNAME AS DATABASE, TS.NAME AS TABLE_SPACE,
  'SEGMENTED'

  FROM SYSIBM.SYSTABLES T, SYSIBM.SYSTABLESPACE TS
  WHERE T.TSNAME      = TS.NAME
    AND T.DBNAME      = TS.DBNAME
    AND TS.PARTITIONS = 0
    AND TS.SEGSIZE    > 0
--  AND T.NAME        = 'TTTT' --FILL IN YOUR TABLE S NAME
--  AND T.CREATOR     = 'CCCC' --FILL IN CREATOR NAME
  UNION ALL

--PARTITIONED
SELECT T.CREATOR AS TABLE_CREATOR, T.NAME AS TABLE_NAME,
  TS.DBNAME AS DATABASE, TS.NAME AS TABLE_SPACE,
  'PARTITIONED'

  FROM SYSIBM.SYSTABLES T, SYSIBM.SYSTABLESPACE TS
  WHERE T.TSNAME      = TS.NAME
    AND T.DBNAME      = TS.DBNAME
    AND TS.PARTITIONS > 0
--  AND T.NAME        = 'TTTT' --FILL IN YOUR TABLE S NAME
--  AND T.CREATOR     = 'CCCC' --FILL IN CREATOR NAME
  ORDER BY 1, 2, 3
;



-- 7 WHAT ABOUT INDEXES ON MY TABLE?
-- what.about.indexes.txt
 SELECT K.IXNAME, I.CREATOR, I.TBNAME,
  K.COLNAME,K.COLSEQ,
  I.UNIQUERULE, I.CLUSTERING, K.ORDERING
  FROM SYSIBM.SYSINDEXES I, SYSIBM.SYSKEYS K
  WHERE I.NAME      = K.IXNAME  -- join cond
    AND I.TBCREATOR = K.IXCREATOR -- join cond

--  AND I.TBNAME    = 'TTTT' --FILL IN TABLE NAME
--  AND I.TBCREATOR = 'CCCC' --FILL IN CREATOR if known
  ORDER BY K.IXNAME, I.CREATOR, I.TBNAME,  K.COLSEQ
  --UNIQUE RULE:
  --  U = UNIQUE
  --  D = DUPLICATESOK
  --  P = UNIQUE PRIMARY KEY
  --  C = UNIQUE CONSTRAINT
  --  N = UNIQUE WHERE NOT NULL
  --  R = UNIQUE FOR NON-PRIMARY KEY PARENT KEY
;



-- 8 WHAT ARE MY FOREIGN KEYS
--AND WHAT TABLES DO THEY REFERENCE?
-- what.are.my.fks.txt
SELECT F.CREATOR  AS FK_CREATOR,
   F.TBNAME       AS FK_TABLE,
   F.COLNAME      AS FK_COLNAME,
   F.COLSEQ       AS FK_COLSEQ,
   R.REFTBCREATOR AS PK_CREATOR,
   R.REFTBNAME    AS PK_TABLE,
   R.DELETERULE

FROM SYSIBM.SYSFOREIGNKEYS F
INNER JOIN SYSIBM.SYSRELS R
ON  F.RELNAME = R.RELNAME
AND F.CREATOR = R.CREATOR

WHERE F.TBNAME  = 'TTTT' --FILL IN TABLE NAME
  AND F.CREATOR = 'CCCC' --FILL IN CREATOR NAME

ORDER BY FK_CREATOR, FK_TABLE, FK_COLSEQ
;


--9 RI LISTED BY PARENT TABLE
-- ri.by.parent.txt
SELECT 'PK:', R.REFTBCREATOR, R.REFTBNAME,
       F.CREATOR, 'FK:', F.TBNAME,
       R.RELNAME, R.DELETERULE, F.COLSEQ, F.COLNAME
  FROM SYSIBM.SYSFOREIGNKEYS F,
       SYSIBM.SYSRELS R
  WHERE F.CREATOR   = R.CREATOR
    AND   F.TBNAME  = R.TBNAME
    AND   F.RELNAME = R.RELNAME
    AND   F.TBNAME  = 'TTTT' -- THE NAME OF YOUR TABLE
    AND   F.CREATOR = 'CCCC' -- THE NAME OF THE TABLE CREATOR
  ORDER BY R.REFTBCREATOR, R.REFTBNAME, F.CREATOR, F.TBNAME, F.COLSEQ
;


-- 10 SIMPLE RI, LISTED BY DEPENDENT TABLES
-- TELLS YOU THE DEPENDENT TABLE AND THE PARENT TABLE
-- CAN T TELL YOU THE PARENT S TABLE S PK COLUMNS
-- ri.by.dependant.txt
SELECT 'FK:', F.CREATOR, F.TBNAME, F.COLSEQ, F.COLNAME, F.RELNAME,
  'PK:', R.REFTBCREATOR, R.REFTBNAME, R.DELETERULE
  FROM SYSIBM.SYSFOREIGNKEYS F, SYSIBM.SYSRELS R
  WHERE F.CREATOR = R.CREATOR
    AND F.TBNAME  = R.TBNAME
    AND F.RELNAME = R.RELNAME
--  AND F.TBNAME  = 'TTTT' --PUT IN DEPENDANT TABLE NAME
--  AND F.CREATOR = 'CCCC' --PUT IN DEPENDANT TABLE S CREATOR NAME
  ORDER BY F.CREATOR, F.TBNAME, F.COLSEQ
;


-- 11 WHICH TABLES HAVE A PK?
--which.tables.have.pk.txt
SELECT CREATOR, NAME, STATUS, CHILDREN
  FROM SYSIBM.SYSTABLES
  WHERE STATUS IN ('I', 'X')
    AND KEYCOLUMNS > 0       -- # OF COLUMNS IN PK
--  AND CREATOR   IN ('CCCC') --PUT IN CREATOR NAME
--  AND TBNAME    = 'TTTT' --PUT IN TABLE NAME IF DESIRED
  ORDER BY CREATOR, NAME
  -- X - UNIQUE INDEX EXISTS FOR THIS TABLES PK
  -- I - UNIQUE INDEX DOES NOT EXIST YET; NEEDS TO BE DEFINED
  -- CHILDREN: NUMBER OF RI CONSTRAINTS DEFINED
  --     REFERENCING THIS PK
;



-- 12 WHAT ARE THE PK COLUMNS OF MY TABLE?
-- what.pk.columns.txt
SELECT TBCREATOR AS PK_CREATOR,
TBNAME        AS PK_TABLE,
NAME          AS PK_COLUMN,
KEYSEQ        AS PK_COLUMN_SEQUENCE

FROM SYSIBM.SYSCOLUMNS

WHERE KEYSEQ    > 0
--  AND TBNAME    = 'TTTT' --PUT IN TABLE NAME
--  AND TBCREATOR = 'CCCC' --PUT IN CREATOR NAME

ORDER BY PK_CREATOR, PK_TABLE, PK_COLUMN_SEQUENCE
;


-- 13 ALL THE WAY WITH FK S AND PK S
--THIS IS SQL TO PRODUCE A REPORT OF FOREIGN KEYS
--AND THE PRIMARY KEYS THEY REFERENCE
--MAY TAKE A LONG TIME TO RUN

SELECT F.CREATOR      AS FK_CREATOR,
       F.TBNAME       AS FK_TABLE,
       F.COLNAME      AS FK_COLNAME,
       F.COLSEQ       AS FK_COLSEQ,
       R.REFTBCREATOR AS PK_CREATOR,
       R.REFTBNAME    AS PK_TABLE,
       --C.TBCREATOR  AS PK_CREATOR2,
       --C.TBNAME     AS PK_TABLE2,
       C.NAME         AS PK_COLNAME,
       --C.KEYSEQ     AS PK_COLSEQ,
       --R.DELETERULE

  FROM SYSIBM.SYSFOREIGNKEYS F
       INNER JOIN SYSIBM.SYSRELS R
       ON F.RELNAME  = R.RELNAME
       AND F.CREATOR = R.CREATOR
       AND F.TBNAME  = R.TBNAME
          INNER JOIN SYSIBM.SYSCOLUMNS C
          ON R.REFTBCREATOR = C.TBCREATOR
          AND R.REFTBNAME   = C.TBNAME

WHERE F.COLSEQ = C.KEYSEQ
  AND C.KEYSEQ > 0
  --AND F.CREATOR = 'CCCCC' -- PUT IN CREATOR/HIGH LEVEL QUALIFIER
                            -- THEN REMOVE COMMENT DELIMITER

ORDER BY FK_CREATOR, FK_TABLE, PK_TABLE, FK_COLSEQ
;
--WHEN YOU USE THIS IN QMF, SET UP YOUR FORM AS FOLLOWS:
--  FK_CREATOR BREAK1
--  FK_TABLE   BREAK1
--  PK_TABLE   BREAK2
--  DEFAULT BREAK TEXT NO
--  COMPLIMENTS OF GABE GARGIULO



-- 14
-- WHAT PLANS USE MY TABLE, OR
-- WHAT TABLES USE MY PLAN?
--plans.on.my.table.txt
SELECT BCREATOR AS CREATOR, BNAME AS TABLE_NAME,
      DNAME AS PLAN_NAME, BTYPE
FROM SYSIBM.SYSPLANDEP

WHERE BTYPE IN ('S','T','V') -- SYNONYM TABLE VIEW
--AND BCREATOR = 'CCCC' -- INSERT CREATOR NAME IF KNOWN
--AND BNAME    = 'TTTT' -- INSERT TABLE NAME IF KNOWN
--AND DNAME    = 'PPPP' -- INSERT PLAN NAME IF KNOWN
 ORDER BY CREATOR, TABLE_NAME, PLAN_NAME
;

-- 14A TELL ME ABOUT MY PLAN
SELECT CREATOR, BINDDATE, BINDTIME, VALID,
       BOUNDBY, QUALIFIER

FROM SYSIBM.SYSPLAN

WHERE NAME LIKE 'plan-name'      -- PLAN NAME HERE
;



--15
--WHAT PACKAGES USE MY TABLE, OR
-- WHAT TABLES USE MY PACKAGE?
-- packages.on.my.table.txt

SELECT BQUALIFIER AS QUALIFIER, BNAME AS TABLE_NAME,
      DNAME AS PACKAGE_NAME, BTYPE
FROM SYSIBM.SYSPACKDEP

WHERE BTYPE IN ('S','T','V') -- SYNONYM TABLE VIEW
--AND BQUALIFIER = 'CCCC' -- INSERT QUALIFIER NAME IF KNOWN
--AND BNAME    = 'TTTT' -- INSERT TABLE NAME IF KNOWN
--AND DNAME    = 'PPPP' -- INSERT PACKAGE NAME IF KNOWN
--ORDER BY BQUALIFIER, TABLE_NAME, PACKAGE_NAME
;

--16 WHAT AM I AUTHORIZED FOR?
  SELECT GRANTOR, GRANTEE, TCREATOR AS TABLE_CREATOR,
    TTNAME AS TABLE_NAME,
    UPDATECOLS, ALTERAUTH, DELETEAUTH, INDEXAUTH, INSERTAUTH,
    SELECTAUTH, UPDATEAUTH

    FROM SYSIBM.SYSTABAUTH
     WHERE GRANTEE IN ('@TSOU01', 'PUBLIC') -- INSERT YOUR @TSOU01
--   AND   TCREATOR = 'CCCCCC'             -- INSERT TBL CREATOR
--   AND   TTNAME   = 'TTTTTT'             -- INSERT TBL NAME
;


--17 WHAT ARE CHECKS ON THIS TABLE?
-- what.are.checks.on.my.table.txt
SELECT CHECKCONDITION, CHECKNAME, TBOWNER, CREATOR, TBNAME
FROM SYSIBM.SYSCHECKS
WHERE TBNAME = 'TTTTTTT' -- INSERT TABLE NAME
--AND TBOWNER = 'OOOOO'  -- INSERT TABLE OWNER, IF KNOWN
;


--18 WHAT ARE MY DB AUTHORIZATIONS?
-- what.are.my.db.authorizations.txt
SELECT GRANTOR, GRANTEE, NAME,
  CREATETABAUTH, CREATETSAUTH, DBADMAUTH, DBCTRLAUTH,
  DBMAINTAUTH, DISPLAYDBAUTH,  DROPAUTH,  IMAGCOPYAUTH,
  LOADAUTH, REORGAUTH, RECOVERDBAUTH,
  REPAIRAUTH, STARTDBAUTH, STATSAUTH, STOPAUTH
FROM SYSIBM.SYSDBAUTH

     WHERE GRANTEE  = '@TSOU01' -- INSERT YOUR @TSOU01
     OR    GRANTEE  = 'PUBLIC'
;


--19 WHAT ARE MY PACKAGE AUTHORIZATIONS?
-- what.are.my.package.authorizations.txt
SELECT GRANTOR, GRANTEE, COLLID, NAME,
    BINDAUTH, COPYAUTH, EXECUTEAUTH
FROM SYSIBM.SYSPACKAUTH

     WHERE GRANTEE  = '@TSOU01' -- INSERT YOUR @TSOU01
     OR    GRANTEE  = 'PUBLIC'

;


--20 WHAT ARE MY PLAN AUTHORIZATIONS?
-- what.are.my.plan.authorizations.txt
SELECT GRANTOR, GRANTEE, NAME, BINDAUTH, EXECUTEAUTH

 FROM SYSIBM.SYSPLANAUTH

     WHERE GRANTEE  = '@TSOU01' -- INSERT YOUR @TSOU01
     OR    GRANTEE  = 'PUBLIC'
;


--22 WHAT TRIGGERS ARE THERE?
-- what.triggers.are.there.txt
 SELECT TBOWNER,  TRIGTIME, TRIGEVENT, GRANULARITY, TEXT
   FROM SYSIBM.SYSTRIGGERS
;


--24 WHAT SYNONYMS ARE THERE?
--what.synonyms.are.there.txt
SELECT *
  FROM SYSIBM.SYSSYNONYMS
;


Top of Page




















































































List of books on JCL and other mainframe topics

[Books Computer]

Home Books for Computer Professionals Privacy Terms |
Site Map and Site Search Programming Manuals and Tutorials The REXX Files Top of Page |