Db/2 – LUW – Managing Transaction Log Backup

Background

Wanted to document some of the steps to take when wanting to implement Transaction Log backup in DB/2 LUW.

 

Outline

  1. Configuration
    • Review & Apply Settings
      • LOGRETAIN
        • Deprecated
      •  LOGARCHMETH1
        • Set folder to place LOG backups
  2. Stop Database
  3. Restart Database
  4. Take full database backup
  5. Review
    • Review File System Folder
      • Active Logs
      • Archive Logs

 

Configuration

 

Outline

Here are the entries that need to be reviewed, set, and tweaked for Db/2 Log Backup.

Items

  1. Path to log files
    • Path where active log files are kept
    • Current :- M:\DB2\NODE0000\SQL00002\LOGSTREAM0000\
  2. LogRetain
    • Text :- Log retain for recovery status
    •  Setting :- NO
    • References
      • Stackoverflow
        • Need to properly set USEREXIT and LOGARCHMETH1
          Link
  3. Primary
    • LOGARCHMETH1 ( Change )
      • Text :- First log archive method (LOGARCHMETH1)
      • Current :- DISK:M:\DB2\NODE0000\Backup\Log\WideWrld\
    • LOGPRIMARY
      • Text :- Number of primary log files (LOGPRIMARY)
      • Current :- 13
    • LOGARCHCOMPR1
      • Text :- Archive compression for logarchmeth1 (LOGARCHCOMPR1)
      • Current :- OFF
  4. Secondary
    • LOGARCHMETH2 ( Change )
      • Text :- First log archive method (LOGARCHMETH2)
      • Current :- DISK:M:\DB2\NODE0000\Backup\Log\WideWrld\
    • LOGSECOND
      • Text :- Number of primary log files (LOGPRIMARY)
      • Current :- 13
    • LOGARCHCOMPR2
      • Text :- Archive compression for logarchmeth2 (LOGARCHCOMPR2)
      • Current :- OFF

 

Settings – Set

Code


setlocal

set "_folder=M:\DB2\NODE0000\Backup\Log\WideWrld"

set "_database=WideWrld"

set "_logRetain=Recovery"

set "_logfileSize=1024"

if not exist %_folder%  mkdir %_folder%

db2 connect to %_database%

db2 update db cfg for %_database% using LOGARCHMETH1 "DISK:%_folder%"

db2 update db cfg for %_database% using LOGFILSIZ %_logfileSize%

db2 CONNECT RESET

Images

 

Settings – Get

Outline

Using “db2 get database configuration” highlight pertinent entries.

Textual


setlocal

set _database=WideWrld

db2 connect to %_database%

db2 get database configuration | findstr /I /C:LOGFILSIZ /C:LOGPRIMARY /C:LOGSECOND /C:LOGARCHMETH /C:"Log retain for recovery status"  /C:"Path to log files" 

db2 CONNECT RESET

endlocal

Images

 

 

Database Backup

Outline

Syntax

Code


db2 backup database {database} ONLINE to {folder} INCLUDE LOGS

Sample

Take full database backup

Code

@echo on

set "_database=WideWrld"

set "_folder=M:\DB2\NODE0000\Backup\Full\WideWrld"

if not exist %_folder%  mkdir %_folder%

rem terminate current connection
db2 terminate

rem deactivate database
db2 deactivate database %_database%

rem backup database
db2 backup database %_database% ONLINE to %_folder% INCLUDE LOGS

Review

Review File System

Active Logs

File Explorer

Image

 

Archive Logs

File Explorer

Image

 

Transaction Log Utilization

CLP

DB2 GET SNAPSHOT FOR DATABASE

Code
Syntax

DB2 GET SNAPSHOT FOR DATABASE ON {database}

Sample

DB2 GET SNAPSHOT FOR DATABASE ON WideWrld   | findstr /I /C:"log space used" /C:"active"

 

Output – Image

Output – Textual
Log space used by the database (Bytes) = 2114776
Maximum secondary log space used (Bytes) = 3577958
Maximum total log space used (Bytes) = 57837670
File number of first active log = 2060
File number of last active log = 2072
File number of current active log = 2072

Explanation
  1. Log space used by the database (Bytes)
  2. Maximum secondary log space used (Bytes)
  3. Maximum total log space used (Bytes)
  4. File number of first active log
  5. File number of last active log
  6. File number of current active log

Db2 Inspect

Background

As we discussed in an earlier post, one has to be careful when restarting a box that houses a database server.

The post that discusses the proper steps are here :-

DB/2 LUW – Stopping DB2 Subsystem ( On MS Windows )
Link

Db2 Inspect

Introduction

There are a couple of options for validating Db2 databases.

Our options include :-

  1. Db2 Inspect
  2. db2dart

Outline

  1. Preparation
    • Get diag folder
  2. Inspect Database
    • db2 inspect
  3. Convent binary file from db2 inspect to text file
    • db2inspf
  4. Review prepared text file

Preparation

Outline

  1. Get diag folder

Get Diag Folder

Linux

db2 get dbm configuration | grep "DIAGPATH"

Windows

db2 get dbm configuration | find /i "DIAGPATH"

db2 inspect

Outline

The steps to take are :

  1. Launch db2cmd window by issuing db2cmd
  2. connect to the database ( connect database [database] )
  3. Issue db2 inspect command

Check Database

Sample


db2cmd
db2 connect to sample
db2 "inspect check database RESULTS KEEP db2_inspect_database_20180915_0810AM.txt ON DBPARTITIONNUM (1)"

Output


DB20000I  The INSPECT command completed successfully.

Check Table

Sample


db2cmd
db2 connect to sample
db2 "inspect check table NAME ACT SCHEMA DADENIJI RESULTS KEEP db2_inspect_table_act.txt ON DBPARTITIONNUM (1)"

Output


DB20000I  The INSPECT command completed successfully.

db2inspf

db2inspf ( No Parameters )

Sample


C:\>db2inspf db2_database_sample_20180915_0807AM.txt db2_database_sample_clear_20180915_0807AM.txt

Trouble Shooting

db2 inspect

SQL1143N The operation cannot complete because a file error occurred for the file “e:\db2temp\db2_inspect.txt”

Error

Error Image

SQL1143N_20180915_0851AM

Error Text

SQL1143N  The operation cannot complete because a file error occurred for the
file "e:\db2temp\db2_inspect.txt".
Remediation

One does not need to use the full file path.

The file is placed in the db2 dump directory.

BTW, issue “db2 get dbm configuration” to get database configuration and deduce the db2diag folder.

File Is Already In use

Error

Error Image

fileIsAlreadyInUse_20180915_0841AM.PNG

Error Text

SQL1142N  The operation cannot complete because the file "db2_database_sample.txt" is already in use.

Remediation

Please use a new file name

DB/2 LUW – Stopping DB2 Subsystem ( On MS Windows )

Background

On MS Windows, the first databases were simple database files such as DBase, Clipper, Paradox, Foxpro, Access, etc.

With the advent of the Client Servers architecture,  databases that started in the Unix area started moving down stream.

Prudent

It is prudent to take time and better understand how each database management system prefers to be shut-down.

In this exercise we will start tackling IBM’s DB2 LUW on MS Windows.

Outline

  1. List Db2 Instances
    • db2ilist
  2. List / Set Environment Variable
    • set db2
  3. List Connected Applications
    • CLI
      • db2 list applications
    • metadata
      • SYSIBMADM.APPLICATIONS
  4. Terminate Database Applications
    • db2 force application all
  5. DB2 Terminate
    • db2 terminate
  6. Stop License Daemon
    • db2licd -end
  7. Issue db2stop
    • db2stop
  8. db2diag
    • Review db2 log files

Steps

List Db2 Instances ( db2ilist )

Syntax


db2ilist

Sample


db2ilist

Output

db2ilist_20180912_0238PM.PNG

List / Set Environment Variable ( set db2 )

Syntax


set [environment-variable]

Sample


set db2

Output

envvar_set_20180912_0422PM.PNG

List Applications

CLI

db2 list applications
Syntax

db2 list applications

Sample

db2 list applications

Output
Output – “No Data Was Returned”
Image

db2ListApplications_20180913_0648AM.PNG

Textual

SQL1611W No data was returned by Database System Monitor.

Output – “List Applications #1”

Image
Textual

listApplications.20180913.0822AM.PNG


Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DADENIJI db2jcc_applica 462 10.11.203.114.63024.180913144347 SAMPLE 1

List Applications ( Using SYSIBMADM.APPLICATIONS )

SYSIBMADM.APPLICATIONS

Sample

SELECT 

          tblApp.AGENT_ID           AS "agentID"
        , tblApp.AUTHID             AS "authID"
        , tblApp.TPMON_CLIENT_APP   AS "clientApp"
        , tblApp.APPL_NAME          AS "Application"
        , tblApp.DB_NAME            AS "database"
        , tblApp.APPL_STATUS        AS "appStatus"
        , tblApp.CLIENT_PROTOCOL    AS "clientProtocol"
        , tblApp.CLIENT_NNAME       AS "clientName"
        , tblApp.NUM_ASSOC_AGENTS   AS "#ofAgents"
--      , tblApp.*

FROM   SYSIBMADM.APPLICATIONS tblApp

WHERE
        (

            ( tblApp.TPMON_CLIENT_APP IS NOT NULL )

        )

Output

SYSIBMADM.APPLICATIONS.20180913.1123AM..PNG

Terminate Application

Syntax


db2 force application ( appl-id-1, appl-id-2, appl-id-3, ...., appl-id=n)

Sample

Sample – Specific Application ID

db2 force application ( 462 )

Output

db2forceapplication_01.20180913.1134AM.PNG

Sample – All Applications

db2 force application all

DB2 Terminate

Syntax


db2 terminate

Sample


db2 terminate

Output

db2terminate_20180913_1137AM.PNG

db2stop

Syntax


db2stop

Sample


db2stop

Output

db2stop.20180913.1139AM

db2licd

Syntax


db2licd

Sample


db2licd -end

Output

db2licd.stop.20180913.1143AM.PNG

db2diag

Syntax


db2diag

Sample


db2diag

Output

Output – 01

db2diag_20180912_0432PM.PNG

Output – 02

db2diag_20180913_1146AM.PNG

References

  1. IBM
    • IBM Knowledge Center
      •  Home > Db2 11.1 > Database fundamentals > Applying fix packs > Applying fix packs in Db2 database environments > Preparing to install a fix pack
        • Stopping all Db2 processes (Linux and UNIX)
          Link
      • Home > Tivoli Storage Productivity Center 5.2.6 > Administering > Administering DB2
        • Manually stopping DB2 on Windows
          Link

DB/2 – LUW :- DDL & DML – Schema, Table & Adding Data

Background

Now that we have a sample database, let us start creating database objects and manipulating data.

Data Definition Language ( DDL )

Schema

government

syntax


CREATE SCHEMA [schema];

Sample


CREATE SCHEMA "government";

Table

government.city

Sample


DROP TABLE government.city;

create table "government"."city"
(

	  "state"  varchar(60) not null
	, "city"   varchar(60) not null

	, constraint "PK_Government_City"
	  primary key
	  (
		  "state"
		, "city"
	  )

)
;

Data Manipulation Language ( DML )

Table

Add data

Add data using INSERT/VALUES

Sample

/*
	DELETE
	FROM government.city
	WHERE 1=1
	;

*/
TRUNCATE TABLE "government"."city"
	REUSE STORAGE
    IGNORE DELETE TRIGGERS
    IMMEDIATE
    ;

insert into "government"."city"

( "state", "city")

VALUES
	  ( 'CA', 'San Francisco' )
	, ( 'CA', 'Sacramento' )
	, ( 'CA', 'Santa Clarita' )

	, ( 'GA', 'Augusta' )
	, ( 'GA', 'Atlanta' )
	, ( 'GA', 'Macon' )
	, ( 'GA', 'Savanna' )

	, ( 'NY', 'New York' )
	, ( 'NY', 'Sacramento' )

	, ( 'TX', 'Austin' )
	, ( 'TX', 'Dallas' )
	, ( 'TX', 'Houston' )
	, ( 'TX', 'San Antonio' )				


Add data using Table Function

Sample
/*

	DELETE
	FROM "government"."city"
	WHERE 1=1;

*/

TRUNCATE TABLE "government"."city"
    REUSE STORAGE
    IGNORE DELETE TRIGGERS
    IMMEDIATE
    ;

insert into "government"."city"
( "state", "city")

SELECT *

FROM TABLE
(

      VALUES
          ( 'CA', 'San Francisco' )
        , ( 'CA', 'Sacramento' )
        , ( 'CA', 'Santa Clarita' )

        , ( 'GA', 'Augusta' )
        , ( 'GA', 'Atlanta' )
        , ( 'GA', 'Macon' )
        , ( 'GA', 'Savanna' )

        , ( 'NY', 'New York' )
        , ( 'NY', 'Sacramento' )

        , ( 'TX', 'Austin' )
        , ( 'TX', 'Dallas' )
        , ( 'TX', 'Houston' )
        , ( 'TX', 'San Antonio' )               

)
;

Crediting

Insert multiple rows into DB2 database
Crediting Hogan & Esperento57
Link

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

Db2/LUW – Sample Database

Background

Upon completing the installation of our own DB/2 Instance, it is time to provision a sample database.

Sample Database

db2sampl

IBM bundles a binary for creating a sample database.

The name of the binary is db2sampl.

Windows

Preparation

Thankfully in MS Windows, there is not a lot of preparatory work that needs to occur.

Environment Variables

Her are the environment variables that need to be set:

  1. DB2INSTANCE
Environment Variables – Read
Syntax

set DB2

Output

read_20180910_0346AM

 

Processing

The actual execution is just as straight forward.

Simply execute db2sampl.

Syntax


db2sampl

Output

Here is the output

create_sampl_20180907_0954PM.PNG

Explanation

The SAMPLE database is created as is did not previously exist.

We are currently logged into the OS as DADENIJI.

That account is used in creating a corresponding schema DADENIJI.

Tables are created and records added.

IBM Db2 Developer ( v11 ) – Post Installation Review

Background

Upon installing IBM Db2 Developer ( v11) wanted to go in and review our installation.

BTW, here is the Install post for Windows :-

IBM Db2 Developer ( v11 ) – Installation on Windows
Link

Review

Outline

  1. DB2
    • Registry
  2. Network
    • Network Ports
  3. OS Security Groups
    • Windows
      • Security Account Management Database

Db/2

Registry

db2set

Syntax

db2set

Sample

db2set -all

Output

db2set_20180909_1128AM.PNG

Network

Network Ports

Linux

/etc/services
File Info

Port are listed in the /etc/services file.

Sample

cat /etc/services | grep -i db2

Output

etc.services.20180909.0641PM.PNG

Windows

c:\windows\system32\drivers\etc\services
File Info

Port are listed in the c:\windows\system32\drivers\etc\services file.

Sample

type c:\windows\system32\drivers\etc\services | find "DB2"

Output

etc.services.20180909.0602PM.PNG

 

 

OS Security Groups

Introduction

If the DB/2 Register item DB2_EXTSECURITY is enabled, the accounts that are listed in the DB2ADMNS and DB2USERS groups are authorized accordingly.

Windows

SAM Account Database

net localgroups
Syntax

net localgroups {group-name}

Sample

net localgroups DB2ADMNS

Output

netlocalgroup_db2users_20180909_0627PM