Page tree

Skip to end of metadata
Go to start of metadata

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 attributeregex
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"'


  • '(. <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 active command was 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.