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.
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'.
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
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:
regex '(?i)Database name\s+=\s+(\S+\w)'
|database aliases (list of names)|
regex '(?i)Database alias\s+=\s+(\S+\w)'
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:
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:
Here is the model of the created Schema Detail node:
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:
and then creates DatabaseDetail node for each extracted table. <SCHEMA_NAME> - name of the respective schema Detail which contains all modeled tables.