Summary
NetXMS subagent for DB2 monitoring is designed to provide a way to extract various parameters known as Data Collection Items (DCI) from an instance or several instances of DB2 database.
Configuration
DB2 subagent can be configured in two ways. The first one would be a simple INI file and the second one would be an XML configuration file. Please note that to use the XML configuration, you first need to declare the XML file in the DB2 section of the INI configuration file. The details are below.
INI configuration
The configuration section in INI file looks like the following:
SubAgent = db2.nsm
*DB2
DBName = dbname
DBAlias = dbalias
UserName = dbuser
Password = mypass123
QueryInterval = 60
ReconnectInterval = 30
| Parameter | Format | Description | Default Value |
|---|---|---|---|
| DBName | string | The name of the database to connect to | - |
| DBAlias | string | The alias of the database to connect to | - |
| UserName | string | The name of the user for the database to connect to | - |
| Password | string | The password for the database to connect to | - |
| EncryptedPassword | string | The encrypted password for the database to connect to (use nxencpasswd for encryption) | - |
| QueryInterval | milliseconds | The interval to perform queries with | 60 |
| ReconnectInterval | milliseconds | The interval to try to reconnect to the database if the connection was lost or could not be established | 30 |
XML configuration
XML configuration allows the monitoring of several database instances.
To be able to use the XML configuration file, you first need to specify the file to use in the DB2 section of the INI file. The syntax is as follows:
SubAgent = db2.nsm
*DB2
ConfigFile = /myhome/configs/db2.xml
Note that all other entries in the DB2 will be ignored.
| Parameter | Format | Description | Default Value |
|---|---|---|---|
| ConfigFile | string | The path to the XML configuration file | - |
The XML configuration file itself should look like this:
<config>
<db2sub>
<db2 id="1">
<dbname>dbname</dbname>
<dbalias>dbalias</dbalias>
<username>dbuser</username>
<password>mypass123</password>
<queryinterval>60</queryinterval>
<reconnectinterval>30</reconnectinterval>
</db2>
<db2 id="2">
<dbname>dbname1</dbname>
<dbalias>dbalias1</dbalias>
<username>dbuser1</username>
<password>mypass456</password>
<queryinterval>60</queryinterval>
<reconnectinterval>30</reconnectinterval>
</db2>
</db2sub>
</config>
As you can see, the parameters are the same as the ones from the INI configuration.
Each database declaration must be placed in the db2sub tag and enclosed in the db2 tag. The db2 tag must have a numerical id which has to be a positive integer greater than 0.
Provided parameters
To get a DCI from the subagent, you need to specify the id from the db2 entry in the XML configuration file (in case of INI configuration, the id will be 1). To specify the id, you need to add it enclosed in brackets to the name of the parameter that is being requested (e.g., db2.parameter.to.request(1)). In the example, the parameter db2.parameter.to.request from the database with the id 1 will be returned.
| Parameter | Arguments | Return type | Description |
|---|---|---|---|
| DB2.Instance.Version(*) | Database id | DCI_DT_STRING | DBMS version |
| DB2.Table.Available(*) | Database id | DCI_DT_INT | The number of available tables |
| DB2.Table.Unavailable(*) | Database id | DCI_DT_INT | The number of unavailable tables |
| DB2.Table.Data.LogicalSize(*) | Database id | DCI_DT_INT64 | Data object logical size in kilobytes |
| DB2.Table.Data.PhysicalSize(*) | Database id | DCI_DT_INT64 | Data object physical size in kilobytes |
| DB2.Table.Index.LogicalSize(*) | Database id | DCI_DT_INT64 | Index object logical size in kilobytes |
| DB2.Table.Index.PhysicalSize(*) | Database id | DCI_DT_INT64 | Index object physical size in kilobytes |
| DB2.Table.Long.LogicalSize(*) | Database id | DCI_DT_INT64 | Long object logical size in kilobytes |
| DB2.Table.Long.PhysicalSize(*) | Database id | DCI_DT_INT64 | Long object physical size in kilobytes |
| DB2.Table.Lob.LogicalSize(*) | Database id | DCI_DT_INT64 | LOB object logical size in kilobytes |
| DB2.Table.Lob.PhysicalSize(*) | Database id | DCI_DT_INT64 | LOB object physical size in kilobytes |
| DB2.Table.Xml.LogicalSize(*) | Database id | DCI_DT_INT64 | XML object logical size in kilobytes |
| DB2.Table.Xml.PhysicalSize(*) | Database id | DCI_DT_INT64 | XML object physical size in kilobytes |
| DB2.Table.Index.Type1(*) | Database id | DCI_DT_INT | The number of tables using type-1 indexes |
| DB2.Table.Index.Type2(*) | Database id | DCI_DT_INT | The number of tables using type-2 indexes |
| DB2.Table.Reorg.Pending(*) | Database id | DCI_DT_INT | The number of tables pending reorganization |
| DB2.Table.Reorg.Aborted(*) | Database id | DCI_DT_INT | The number of tables in aborted reorganization state |
| DB2.Table.Reorg.Executing(*) | Database id | DCI_DT_INT | The number of tables in executing reorganization state |
| DB2.Table.Reorg.Null(*) | Database id | DCI_DT_INT | The number of tables in null reorganization state |
| DB2.Table.Reorg.Paused(*) | Database id | DCI_DT_INT | The number of tables in paused reorganization state |
| DB2.Table.Reorg.Alters(*) | Database id | DCI_DT_INT | The number of reorg recommend alter operations |
| DB2.Table.Load.InProgress(*) | Database id | DCI_DT_INT | The number of tables with load in progress status |
| DB2.Table.Load.Pending(*) | Database id | DCI_DT_INT | The number of tables with load pending status |
| DB2.Table.Load.Null(*) | Database id | DCI_DT_INT | The number of tables with load status neither in progress nor pending |
| DB2.Table.Readonly(*) | Database id | DCI_DT_INT | The number of tables in Read Access Only state |
| DB2.Table.NoLoadRestart(*) | Database id | DCI_DT_INT | The number of tables in a state that won't allow a load restart |
| DB2.Table.Index.Rebuild(*) | Database id | DCI_DT_INT | The number of tables with indexes that require rebuild |
| DB2.Table.Rid.Large(*) | Database id | DCI_DT_INT | The number of tables that use large row IDs |
| DB2.Table.Rid.Usual(*) | Database id | DCI_DT_INT | The number of tables that don't use large row IDs |
| DB2.Table.Rid.Pending(*) | Database id | DCI_DT_INT | The number of tables that use large row Ids but not all indexes have been rebuilt yet |
| DB2.Table.Slot.Large(*) | Database id | DCI_DT_INT | The number of tables that use large slots |
| DB2.Table.Slot.Usual(*) | Database id | DCI_DT_INT | The number of tables that don't use large slots |
| DB2.Table.Slot.Pending(*) | Database id | DCI_DT_INT | The number of tables that use large slots but there has not yet been an offline table reorganization or table truncation operation |
| DB2.Table.DictSize(*) | Database id | DCI_DT_INT64 | Size of the dictionary in bytes |
| DB2.Table.Scans(*) | Database id | DCI_DT_INT64 | The number of scans on all tables |
| DB2.Table.Row.Read(*) | Database id | DCI_DT_INT64 | The number of reads on all tables |
| DB2.Table.Row.Inserted(*) | Database id | DCI_DT_INT64 | The number of insertions attempted on all tables |
| DB2.Table.Row.Updated(*) | Database id | DCI_DT_INT64 | The number of updates attempted on all tables |
| DB2.Table.Row.Deleted(*) | Database id | DCI_DT_INT64 | The number of deletes attempted on all tables |
| DB2.Table.Overflow.Accesses(*) | Database id | DCI_DT_INT64 | The number of r/w operations on overflowed rows of all tables |
| DB2.Table.Overflow.Creates(*) | Database id | DCI_DT_INT64 | The number of overflowed rows created on all tables |
| DB2.Table.Reorg.Page(*) | Database id | DCI_DT_INT64 | The number of page reorganizations executed for all tables |
| DB2.Table.Data.LogicalPages(*) | Database id | DCI_DT_INT64 | The number of logical pages used on disk by data |
| DB2.Table.Lob.LogicalPages(*) | Database id | DCI_DT_INT64 | The number of logical pages used on disk by LOBs |
| DB2.Table.Long.LogicalPages(*) | Database id | DCI_DT_INT64 | The number of logical pages used on disk by long data |
| DB2.Table.Index.LogicalPages(*) | Database id | DCI_DT_INT64 | The number of logical pages used on disk by indexes |
| DB2.Table.Xda.LogicalPages(*) | Database id | DCI_DT_INT64 | The number of logical pages used on disk by XDA (XML storage object) |
| DB2.Table.Row.NoChange(*) | Database id | DCI_DT_INT64 | The number of row updates that yielded no changes |
| DB2.Table.Lock.WaitTime(*) | Database id | DCI_DT_INT64 | The total elapsed time spent waiting for locks (ms) |
| DB2.Table.Lock.WaitTimeGlob(*) | Database id | DCI_DT_INT64 | The total elapsed time spent on global lock waits (ms) |
| DB2.Table.Lock.Waits(*) | Database id | DCI_DT_INT64 | The total amount of locks occurred |
| DB2.Table.Lock.WaitsGlob(*) | Database id | DCI_DT_INT64 | The total amount of global locks occurred |
| DB2.Table.Lock.EscalsGlob(*) | Database id | DCI_DT_INT64 | The number of lock escalations on a global lock |
| DB2.Table.Data.Sharing.Shared(*) | Database id | DCI_DT_INT | The number of fully shared tables |
| DB2.Table.Data.Sharing.BecomingShared(*) | Database id | DCI_DT_INT | The number of tables being in the process of becoming shared |
| DB2.Table.Data.Sharing.NotShared(*) | Database id | DCI_DT_INT | The number of tables not being shared |
| DB2.Table.Data.Sharing.BecomingNotShared(*) | Database id | DCI_DT_INT | The number of tables being in the process of becoming not shared |
| DB2.Table.Data.Sharing.RemoteLockWaitCount(*) | Database id | DCI_DT_INT64 | The number of exits from the NOT_SHARED data sharing state |
| DB2.Table.Data.Sharing.RemoteLockWaitTime(*) | Database id | DCI_DT_INT64 | The time spent on waiting for a table to become shared |
| DB2.Table.DirectWrites(*) | Database id | DCI_DT_INT64 | The number of write operations that don't use the buffer pool |
| DB2.Table.DirectWriteReqs(*) | Database id | DCI_DT_INT64 | The number of request to perform a direct write operation |
| DB2.Table.DirectRead(*) | Database id | DCI_DT_INT64 | The number of read operations that don't use the buffer pool |
| DB2.Table.DirectReadReqs(*) | Database id | DCI_DT_INT64 | The number of request to perform a direct read operation |
| DB2.Table.Data.LogicalReads(*) | Database id | DCI_DT_INT64 | The number of data pages that are logically read from the buffer pool |
| DB2.Table.Data.PhysicalReads(*) | Database id | DCI_DT_INT64 | The number of data pages that are physically read |
| DB2.Table.Data.Gbp.LogicalReads(*) | Database id | DCI_DT_INT64 | The number of times that a group buffer pool (GBP) page is requested from the GBP |
| DB2.Table.Data.Gbp.PhysicalReads(*) | Database id | DCI_DT_INT64 | The number of times that a group buffer pool (GBP) page is read into the local buffer pool (LBP) |
| DB2.Table.Data.Gbp.InvalidPages(*) | Database id | DCI_DT_INT64 | The number of times that a group buffer pool (GBP) page is requested from the GBP when the version stored in the local buffer pool (LBP) is invalid |
| DB2.Table.Data.Lbp.PagesFound(*) | Database id | DCI_DT_INT64 | The number of times that a data page is present in the local buffer pool (LBP) |
| DB2.Table.Data.Lbp.IndepPagesFound(*) | Database id | DCI_DT_INT64 | The number of group buffer pool (GBP) independent pages found in a local buffer pool (LBP) |
| DB2.Table.Xda.LogicalReads(*) | Database id | DCI_DT_INT64 | The number of data pages for XML storage objects (XDA) that are logically read from the buffer pool |
| DB2.Table.Xda.PhysicalReads(*) | Database id | DCI_DT_INT64 | The number of data pages for XML storage objects (XDA) that are physically read |
| DB2.Table.Xda.Gbp.LogicalReads(*) | Database id | DCI_DT_INT64 | The number of times that a data page for an XML storage object (XDA) is requested from the group buffer pool (GBP) |
| DB2.Table.Xda.Gbp.PhysicalReads(*) | Database id | DCI_DT_INT64 | The number of times that a group buffer pool (GBP) dependent data page for an XML storage object (XDA) is read into the local buffer pool (LBP) |
| DB2.Table.Xda.Gbp.InvalidPages(*) | Database id | DCI_DT_INT64 | The number of times that a page for an XML storage objects (XDA) is requested from the group buffer pool (GBP) because the version in the local buffer pool (LBP) is invalid |
| DB2.Table.Xda.Lbp.PagesFound(*) | Database id | DCI_DT_INT64 | The number of times that an XML storage objects (XDA) page is present in the local buffer pool (LBP) |
| DB2.Table.Xda.Gbp.IndepPagesFound(*) | Database id | DCI_DT_INT64 | The number of group buffer pool (GBP) independent XML storage object (XDA) pages found in the local buffer pool (LBP) |
| DB2.Table.DictNum(*) | Database id | DCI_DT_INT64 | The number of page-level compression dictionaries created or recreated |
| DB2.Table.StatsRowsModified(*) | Database id | DCI_DT_INT64 | The number of rows modified since the last RUNSTATS |
| DB2.Table.ColObjectLogicalPages(*) | Database id | DCI_DT_INT64 | The number of logical pages used on disk by column-organized data |
| DB2.Table.Organization.Rows(*) | Database id | DCI_DT_INT | The number of tables with row-organized data |
| DB2.Table.Organization.Cols(*) | Database id | DCI_DT_INT | The number of tables with column-organized data |
| DB2.Table.Col.LogicalReads(*) | Database id | DCI_DT_INT | The number of column-organized pages that are logically read from the buffer pool |
| DB2.Table.Col.PhysicalReads(*) | Database id | DCI_DT_INT | The number of column-organized pages that are physically read |
| DB2.Table.Col.Gbp.LogicalReads(*) | Database id | DCI_DT_INT | The number of times that a group buffer pool (GBP) dependent column-organized page is requested from the GBP |
| DB2.Table.Col.Gbp.PhysicalReads(*) | Database id | DCI_DT_INT | The number of times that a group buffer pool (GBP) dependent column-organized page is read into the local buffer pool (LBP) from disk |
| DB2.Table.Col.Gbp.InvalidPages(*) | Database id | DCI_DT_INT | The number of times that a column-organized page is requested from the group buffer pool (GBP) when the page in the local buffer pool (LBP) is invalid |
| DB2.Table.Col.Lbp.PagesFound(*) | Database id | DCI_DT_INT | The number of times that a column-organized page is present in the local buffer pool (LBP) |
| DB2.Table.Col.Gbp.IndepPagesFound(*) | Database id | DCI_DT_INT | The number of group buffer pool (GBP) independent column-organized pages found in the local buffer pool (LBP) |
| DB2.Table.ColsReferenced(*) | Database id | DCI_DT_INT | The number of columns referenced during the execution of a section for an SQL statement |
| DB2.Table.SectionExecutions(*) | Database id | DCI_DT_INT | The number of section executions that referenced columns in tables using a scan |