IBM DB2 RDBMS - Database Detail Pattern

Product Architecture

Product Architecture

Starting from TKU September 2020 Database Schema node type and Database Table node type were updated to contain DB Server type. (DRDC1-14748) e.g. SQL Database Schema type was changed to DB2 Database Schema and SQL Database Table type was replaced with DB2 Database Table.

Overview

IBMDB2_DBDetails pattern creates Database Detail Nodes for all configured databases within the IBM DB2 instance. It triggers off SoftwareInstance with type 'IBM DB2 Database Server' with 'install_root' and 'instance' attributes defined and creates Detail Node with type 'DB2 Database'.

Configuration Options

There are several configuration options available for this product:

retrieve_schemas := true; - allows to retrieve schemas

ignore_schemas := [ "NULLID", "SQLJ", "SYSCAT", "SYSFUN", "SYSIBM", "SYSIBMADM","SYSIBMINTERNAL", "SYSIBMTS", "SYSPROC", "SYSPUBLIC", "SYSSTAT", "SYSTOOLS"]; - allows to ignore selected schema names

retrieve_tables := false; - allows to retrieve tables for each schema

Modelling Database Details

The pattern launches the following active commands for Windows and UNIX platforms respectively:

'set DB2INSTANCE=<DB2_INSTANCE> && "<DB2_INSTALL_ROOT>\\BIN\\db2cmd" -i -w -c db2 list database directory'
'(. <DB2_INSTALL_ROOT>/sqllib/db2profile && db2 list database directory)'
Then it parses the output and extracts the following attributes for each database using appropriate regular expressions:

database attribute	regex
database name	regex '(?i)Database name\s+=\s+(\S+\w)'
database aliases (list of names)	regex '(?i)Database alias\s+=\s+(\S+\w)'
database directory	regex '(?i)Local database directory\s+=\s+(\S*[\w\\/:])'

The following picture shows IBM DB2 Database Server instance with databases:

The Database Detail has the following model in this case:

Schema Details

If database details were successfully found and 'retrieve_schemas' configuration option of the pattern is enabled (true by default), the following commands are then executed to retrieve schema and respective tables details on Windows and UNIX accordingly:

'set DB2INSTANCE=<DB2_INSTANCE> && "<DB2_INSTALL_ROOT>\\BIN\\db2cmd" -i -w -c "db2 connect to <DATABASE_NAME> & db2 list tables for all show detail"'
'(. <DB2_INSTALL_ROOT>/sqllib/db2profile && db2 connect to <DATABASE_NAME> && list tables for all)'

Schema names are extracted using the following regular expression:

'(?m)^\S+\s+(\S+)\s+\w\s'

Here is the model of the created Schema Detail node:

Table Details

If the Schema Detailswas successful and 'retrieve_tables' configuration option of the pattern is enabled, it extracts table names from the command output with help of regex:

'(?m)^(\S+)\s+<SCHEMA_NAME>\s+T'

and then creates DatabaseDetail node for each extracted table. <SCHEMA_NAME> - name of the respective schema Detail which contains all modeled tables.

IBM DB2 HADR

Pattern will try to extract HADR attributes (hadr_role, hadr_local_host, hadr_remote_host) and if multiple nodes are present, model SoftwareCluster.

The pattern attempts to obtain HADR information on each database by executing the following commands for Windows and UNIX platforms respectively:

'"<DB2_INSTALL_ROOT>\\BIN\\db2cmd" -i -w -c db2 get db cfg for "<DB_Name>"'
'(. <DB2_INSTALL_ROOT>/sqllib/db2profile && db2 get db cfg for "<DB_Name>")'
Then it parses the output and extracts the following HADR attributes for each database using appropriate regular expressions:

Description	Attribute	Regular Expression
HADR database role	hadr_role	regex '(?m)\sHADR database role\s=\s(\S+)\s$'
HADR local host name	hadr_local_host	regex '(?m)$HADR_LOCAL_HOST$\s=\s(\S+)\s*$'
HADR local service name	hadr_local_service_name	regex '(?m)$HADR_LOCAL_SVC$\s=\s(\S+)\s*$'
HADR remote host name	hadr_remote_host	regex '(?m)$HADR_REMOTE_HOST$\s=\s(\S+)\s*$'
HADR remote service name	hadr_remote_service_name	regex '(?m)$HADR_REMOTE_SVC$\s=\s(\S+)\s*$'
HADR instance name of remote server	hadr_remote_instance	regex '(?m)$HADR_REMOTE_INST$\s=\s(\S+)\s*$'

Go Back To Product Page