Db2/LUW – Sample Database – Metadata

Background

Now that we have the Sample database provisioned, let us get to know it a bit.

Metadata

Outline

Item Object Link
Container SYSIBMADM.CONTAINER_UTILIZATION Link
Tablespace SYSIBM.SYSTABLESPACES Link
SYSPROC.SNAP_GET_TBSP_PART Link
SYSPROC.MON_GET_TABLESPACE Link
Table SYSCAT.TABLES Link
Table Constraints – Primary Key ( PK ) SYSCAT.TABCONST & SYSCAT.KEYCOLUSE Link
Table Constraints – Foreign Key ( FK ) SYSCAT.REFERENCES & SYSCAT.KEYCOLUSE Link
Check Constraints SYSCAT.CHECKS Link
Triggers SYSCAT.TRIGGER Link
Table Indexes SYSCAT.INDEXES & SYSCAT.INDEXCOLUSE Link

Tablespace Container

Code


 /*
 * CONTAINER_UTILIZATION administrative view - Retrieve table space container and utilization information
 * https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0022016.html
 *
 */
SELECT

		  SUBSTR(tblCU.CONTAINER_NAME,1,45) AS container
		, tblCU.CONTAINER_ID AS "id"
		, tblCU.CONTAINER_TYPE AS "type"
		, tblCU.TBSP_NAME AS "TS"
		, tblCU.TBSP_ID AS "TSID"
		, tblCU.ACCESSIBLE AS "accessible"
		, tblCU.TOTAL_PAGES AS "totalPages"
		--, tblTS.PAGESIZE AS "pageSize"
		, ( tblCU.TOTAL_PAGES * tblTS.PAGESIZE )
			/ ( 1024 * 1024 )
				AS "totalMB"
		--, tblCU.*

FROM SYSIBMADM.CONTAINER_UTILIZATION tblCU

INNER JOIN SYSIBM.SYSTABLESPACES  tblTS

	ON tblCU.TBSP_ID = tblTS.TBSPACEID 

Output

SYSIBMADM.CONTAINER_UTILIZATION.20180910.0415AM.PNG

Tablespace

SYSIBM.SYSTABLESPACES

Code


SELECT 

            tblTS.TBSPACE AS TS
          , tblTS.TBSPACEID AS TSID
          , tblTS.DEFINER AS "Definer"
          , tblTS.CREATE_TIME AS "CreateTS"
          , tblTS.NGNAME AS "NGNAME"
          , tblTS.BUFFERPOOLID AS "BPID"
          , tblTS.PAGESIZE AS "PageSize"
          , tblTS.DEFINERTYPE AS "DefinerType"

FROM   SYSIBM.SYSTABLESPACES  tblTS

ORDER BY
     tblTS.TBSPACE ASC

Output

SYSIBM.SYSTABLESPACES.20180910.0427AM.PNG

SYSPROC.MON_GET_TABLESPACE & SYSPROC.SNAP_GET_TBSP_PART

Code


/*
 *
 * SNAP_GET_TBSP_PART_V91 table function
   The SNAP_GET_TBSP_PART_V91 table function returns the same information as the SNAPTBSP_PART administrative view,
   but allows you to retrieve the information for a specific database on a specific database partition,
   aggregate of all database partitions or all database partitions.

   https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0022009.html
*/

SELECT

           tblTSP.TBSP_NAME AS "name"

         , tblTSP.TBSP_ID AS "id"
          --, t.TBSP_STATE AS "state"  

         , tblTSM.TBSP_TYPE        AS "TSType"

         , tblTSM.TBSP_CONTENT_TYPE AS "TSContentType"

         , tblTS.PAGESIZE        AS "pageSize"

         , tblTSP.TBSP_TOTAL_PAGES AS "totalPages"

         , ( tblTSP.TBSP_TOTAL_PAGES * tblTS.PAGESIZE )
            / ( 1024 * 1024 )
                AS "totalInMB"

         , tblTSP.TBSP_USED_PAGES AS "usedPages"

         , ( tblTSP.TBSP_USED_PAGES * tblTS.PAGESIZE )
            / ( 1024 * 1024 )
                AS "usedInMB"

         , tblTSP.TBSP_FREE_PAGES AS "freePages"

         , ( tblTSP.TBSP_FREE_PAGES * tblTS.PAGESIZE )
            / ( 1024 * 1024 )
                AS "freeInMB"

         --t.*
         -- , tblTS.*

FROM SYSIBM.SYSTABLESPACES  tblTS

INNER JOIN TABLE
        (

            --SYSPROC.MON_GET_TABLESPACE
            SYSPROC.MON_GET_TABLESPACE
            (
                ''
                ,-2
            )
        ) AS tblTSM

    ON tblTS.TBSPACEID = tblTSM.TBSP_ID

INNER JOIN TABLE
        (

            -- SYSPROC.SNAPSHOT_TBS_CFG
            SYSPROC.SNAP_GET_TBSP_PART
            (
                ''
                ,-1
            )

        ) AS tblTSP 

    ON tblTS.TBSPACEID = tblTSP.TBSP_ID

ORDER BY

    tblTSP.TBSP_NAME ASC

Output

SYSPROC.SNAP_GET_TBSP_PART.20180915.0104PM.PNG

Tables

Code


SELECT 

                   tblT.TABSCHEMA AS "schema"
		 , tblT.TABNAME AS "table"
		-- , tblT.OWNER AS "owner"
		-- , tblT.DEFINER AS "definer"
		-- , tblT.TABLEID AS "tableID"
		-- , TO_CHAR(tblT.CREATE_TIME, 'YYYY-MM-DD HH:MI AM') AS "tsCreate"
		 , TO_CHAR(tblT.STATS_TIME, 'YYYY-MM-DD HH:MI AM') AS "tsStats"
		-- , tblT.TABLESIZE AS "tableSize"
		--, tblT.PCTFREE AS "%free"
		, tblT.CARD AS "# of Rows"
		, tblT.NPAGES AS "# of Pages"
	--	, tblT.OVERFLOW AS "Overflow"
		, tblT.TBSPACE AS "TS"
		, tblT.KEYINDEXID AS "keyIndexID"
		--, tblT.PCTFREE AS "% Free"
		--, tblT.LOCKSIZE AS "lockSize"
		, tblT.COMPRESSION AS "compression"
		, tblT.AVGROWSIZE AS "avgRowSize"
		, tblT.LASTUSED AS "tsLastUsed"

FROM   SYSCAT.TABLES tblT

-- Type Tables ( Skip Views)
WHERE  tblT.TYPE = 'T' 

AND    tblT.TABSCHEMA NOT IN ( 'SYSIBM', 'SYSTOOLS')

-- Only include User defined tables
AND   tblT.OWNERTYPE = 'U'

ORDER BY

          tblT.TABSCHEMA
        , tblT.OWNER
        , tblT.TABNAME    

Output

SYSCAT.TABLES.20180910.0452AM

Table Constraints – Primary Key ( PK )

Code


SELECT 

		   tblTC.TABSCHEMA		AS "schema"     	

		 , tblTC.TABNAME        AS "table"

		 , tblTC.CONSTNAME 	    AS "constraint"

		 , LISTAGG
		 		(
		 			  tblKCU.COLNAME
		 			, ', '
	 			)
		 		WITHIN GROUP
		 			(
		 				ORDER BY
		 					tblKCU.COLSEQ
 					)
				AS "colList"

FROM  SYSCAT.TABCONST tblTC

INNER JOIN SYSCAT.KEYCOLUSE tblKCU

		ON  tblTC.TABSCHEMA = tblKCU.TABSCHEMA
		AND tblTC.TABNAME = tblKCU.TABNAME
		AND tblTC.CONSTNAME = tblKCU.CONSTNAME

WHERE  tblTC.TABSCHEMA NOT IN ( 'SYSIBM', 'SYSTOOLS')

AND    tblTC.TYPE = 'P'

GROUP by
		   tblTC.TABSCHEMA

		 , tblTC.TABNAME

		 , tblTC.CONSTNAME

		 , tblTC.TYPE

		 , tblTC.ENFORCED

		 , tblTC.TRUSTED

		 , tblTC.CHECKEXISTINGDATA

ORDER BY
		   tblTC.TABSCHEMA

		 , tblTC.TABNAME

Output

SYSCAT.TABCONST.PK.20180910.0559AM.

Table Constraints – Foreign Key ( FK )

Code


/*
 * a) SYSCAT.REFERENCES catalog view
 * https://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r0001057.html
 */
WITH cteColList
(
      TABSCHEMA
    , TABNAME
    , CONSTNAME
    , COLLIST
)
AS
(

    SELECT
              tblR.TABSCHEMA
            , tblR.TABNAME
            , tblR.CONSTNAME
            , LISTAGG
                (
                      tblKCU.COLNAME
                    , ', '
                )
                WITHIN GROUP
                    (
                        ORDER BY
                            tblKCU.COLSEQ
                    )
                AS "colList"        

    FROM   SYSCAT.REFERENCES tblR

    INNER JOIN SYSCAT.KEYCOLUSE tblKCU

        ON  tblR.TABSCHEMA   = tblKCU.TABSCHEMA
        AND tblR.TABNAME     = tblKCU.TABNAME
        AND tblR.CONSTNAME   = tblKCU.CONSTNAME 

    GROUP BY
           tblR.TABSCHEMA
         , tblR.TABNAME
         , tblR.FK_COLNAMES

        , tblR.CONSTNAME

)
, cteColListR
(
      TABSCHEMA
    , TABNAME
    , REFKEYNAME
    , CONSTNAME
    , COLLIST
)
AS
(

    SELECT

              tblR.REFTABSCHEMA
            , tblR.REFTABNAME
            , tblR.REFKEYNAME
            , tblR.CONSTNAME            

            , LISTAGG
                (
                      tblKCU.COLNAME
                    , ', '
                )
                WITHIN GROUP
                    (
                        ORDER BY
                            tblKCU.COLSEQ
                    )
                AS "colList"        

    FROM   SYSCAT.REFERENCES tblR

    INNER JOIN SYSCAT.KEYCOLUSE tblKCU

        ON  tblR.REFTABSCHEMA   = tblKCU.TABSCHEMA
        AND tblR.REFTABNAME     = tblKCU.TABNAME
        AND tblR.REFKEYNAME     = tblKCU.CONSTNAME 

    GROUP BY
           tblR.CONSTNAME
         , tblR.REFTABSCHEMA
         , tblR.REFTABNAME
         , tblR.REFKEYNAME

)
SELECT

           tblR.TABSCHEMA           AS "schema"
         , tblR.TABNAME             AS "table"
         , tblR.CONSTNAME           AS "constraint"

         --, tblR.FK_COLNAMES           AS "columns"
         , cteCLP.COLLIST           AS "colListP"       

         , tblR.REFTABSCHEMA        AS "refSchema"
         , tblR.REFTABNAME          AS "refTable"
         --, tblR.PK_COLNAMES           AS "refColumns"
         , cteCLR.COLLIST           AS "colListR"

FROM   SYSCAT.REFERENCES tblR

INNER JOIN cteColList cteCLP 

        ON  tblR.TABSCHEMA = cteCLP.TABSCHEMA
        AND tblR.TABNAME   = cteCLP.TABNAME
        AND tblR.CONSTNAME   = cteCLP.CONSTNAME     

INNER JOIN cteColListR cteCLR

        ON  tblR.REFTABSCHEMA = cteCLR.TABSCHEMA
        AND tblR.REFTABNAME   = cteCLR.TABNAME
        AND tblR.REFKEYNAME   = cteCLR.REFKEYNAME       

GROUP BY
           tblR.TABSCHEMA
         , tblR.TABNAME
         , tblR.FK_COLNAMES
         , cteCLP.COLLIST   

         , tblR.CONSTNAME

         , tblR.REFTABSCHEMA
         , tblR.REFTABNAME
         , tblR.PK_COLNAMES
         , cteCLR.COLLIST

ORDER BY
           tblR.TABSCHEMA
         , tblR.TABNAME     

         , tblR.CONSTNAME

         , tblR.REFTABSCHEMA
         , tblR.REFTABNAME    

Output

SYSCAT.REFERENCES.20180910.0854AM

Check Constraints

Code


/*
 * SYSCAT.CHECKS catalog view
 * https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0001034.html
 *
 */

SELECT 

          tblSC.TABSCHEMA AS "schema"

        , tblSC.TABNAME AS "table"

        , tblSC.CONSTNAME AS "name"

        , tblSC.TYPE AS "type"

        /*,
            TYPE    CHAR (1)        Type of check constraint:
            C = Check constraint
            F = Functional dependency
            O = Constraint is an object property
            S = System-generated check constraint for a GENERATED ALWAYS column
        */

        , CASE tblSC.TYPE

                WHEN 'C' THEN 'Check constraint'
                WHEN 'F' THEN 'Functional dependency'
                WHEN 'O' THEN 'Constraint is an object property'
                WHEN 'S' THEN 'System-generated check constraint for a GENERATED ALWAYS column'

          END  AS "typeLiteral"

        , tblSC.TEXT AS "text"

        , tblSC.PERCENTVALID AS "% valid"
        --, tblSC.*

FROM   SYSCAT.CHECKS tblSC

WHERE  tblSC.OWNERTYPE = 'U'

Output

SYSCAT.CHECKS.20180910.0911AM

Triggers

Code


SELECT 

          tblST.TABSCHEMA AS "schema"

        , tblST.TABNAME AS "TABLE"

        , tblST.TRIGSCHEMA AS "triggerSchema"

        , tblST.TRIGNAME AS "trigger"

        , CASE ( tblST.TRIGTIME )
              WHEN 'A' THEN 'After'
              WHEN 'B' THEN 'Before'
              WHEN 'I' THEN 'Instead of'
          END AS "triggerTime"

        , CASE ( tblST.TRIGEVENT )
              WHEN 'I' THEN 'Insert'
              WHEN 'D' THEN 'Delete'
              WHEN 'U' THEN 'Update'
          END AS "triggerEvent"

          , CASE ( tblST.GRANULARITY )

              WHEN 'R' THEN 'Row'
              WHEN 'S' THEN 'Statement'

            END AS "granularity"

        , tblST.TEXT AS "text"

        --, tblST.*

FROM   SYSCAT.TRIGGERS tblST

WHERE  tblST.OWNERTYPE = 'U'

AND    tblST.TABSCHEMA NOT IN ( 'SYSTOOLS' )

Output

SYSCAT.TRIGGERS.20180910.0918AM.PNG

>

Indexes

Code


WITH cteIndexCol
(
      INDSCHEMA
    , INDNAME
    , COLLIST
)
AS
(

        SELECT
                  tblSICU.INDSCHEMA
                , tblSICU.INDNAME

                , LISTAGG
                (
                      tblSICU.COLNAME

                        CONCAT

                         CASE COLORDER
                            WHEN 'A' THEN ''
                            WHEN 'D' THEN '(D)'
                            WHEN 'I' THEN '(I)'
                            ELSE ''
                         END

                    , ', '

                )
                WITHIN GROUP
                    (
                        ORDER BY
                            tblSICU.COLSEQ
                    )
                AS "colList"

        FROM   SYSCAT.INDEXCOLUSE tblSICU

        GROUP BY

                  tblSICU.INDSCHEMA
                , tblSICU.INDNAME

)

SELECT 

          tblSI.TABSCHEMA AS "schema"

        , tblSI.TABNAME   AS "table"

        ,  tblSI.INDSCHEMA AS "indexSchema"

        , tblSI.INDNAME   AS "index"

        , tblSI.OWNERTYPE AS "ownerType"

        , tblSI.COLNAMES AS "columns"

        , cteIC.COLLIST AS "colList"

        , tblSI.UNIQUERULE

        , tblSI.INDEXTYPE AS "type"

        , tblSI.FIRSTKEYCARD

    --  , tblSI.FIRST2KEYCARD

        , tblSI.FULLKEYCARD

    --  , tblSI.NUMRIDS

        , tblT.CARD AS "# of Records in Table"

        , tblSI.USER_DEFINED

        , tblSI.SYSTEM_REQUIRED

        , tblSI.COMPRESSION

        --, tblSI.*

FROM   SYSCAT.INDEXES tblSI

INNER JOIN SYSCAT.TABLES tblT

        ON   tblSI.TABSCHEMA = tblT.TABSCHEMA

        AND  tblSI.TABNAME = tblT.TABNAME

--INNER JOIN SYSCAT.INDEXCOLUSE tblSICU

--INNER JOIN cteIndexCol  cteIC

LEFT OUTER JOIN cteIndexCol  cteIC

        ON  tblSI.INDSCHEMA = cteIC.INDSCHEMA

        AND tblSI.INDNAME = cteIC.INDNAME

WHERE  tblSI.OWNERTYPE = 'U'

AND    tblSI.TABSCHEMA NOT IN
            (
                  'SYSIBM'
                , 'SYSTOOLS'
            )

ORDER BY

          tblSI.TABSCHEMA

        , tblSI.TABNAME     

        , tblSI.INDNAME     

Output

SYSCAT.INDEXES.20180910.1132AM

References

  1. IBM
    • Home > DB2 for Linux UNIX and Windows 9.7.0 > DB2 for Linux UNIX and Windows 9.7.0 > Database fundamentals > SQL > Catalog views
      • SYSSTAT.CHECKS
      • SYSCAT.INDEXES
      • SYSCAT.INDEXCOLUSE
      • SYSCAT.REFERENCES
      • SYSCAT.TABLES
    • Home > DB2 for Linux UNIX and Windows 9.7.0Database administration > Interfaces (Tools, Commands, APIs) > System-defined routines and views > Supported routines and views > Snapshot routines and views
      • CONTAINER_UTILIZATION administrative view – Retrieve table space container and utilization information
  2. SQL Functions
    • LISTAGG
      • Oracle
        • Database SQL Language Reference
  3. Shibu Kalluvila Raj
    • devx.com
      • DB2 System Catalog Views: Everything You Need to Know About Your DB Objects
        Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s