ZenPack:IBM DB2
Note: This ZenPack is available in commercial versions of Zenoss. Click here to request more information about this commercial ZenPack. Click here to see all commercial ZenPacks.
- Organization
- Zenoss, Inc.
- ZenPack name
- ZenPacks.zenoss.DB2
- IBM DB2 (10.5+)
IBM DB2 ZenPack
Warning
The ZenPack Catalog has moved to its new home at https://www.zenoss.com/product/zenpacks as of January 17, 2017. The following information may be out of date, and this page will eventually be removed.
Support
This ZenPack is included with commercial versions of Zenoss and enterprise support for this ZenPack is provided to Zenoss customers with an active subscription.
Releases
- Version 1.0.5- Download
- Summary of changes: .
- Impove sanity checks for ConnectionString in modeler (ZEN-24868)
- Released on 2017/01/03
- Compatible with Zenoss Core 4.2.x, Zenoss Core 5.0.x, Zenoss Core 5.1.x
- Version 1.0.4- Download
- Summary of changes: .
- Improve Graphs
- Added failsafe for multi-partitioned databases
- Minor UI fixes
- Released on 2016/08/19
- Compatible with Zenoss Resource Manager 4.2.x, Zenoss Resource Manager 5.0.x, Zenoss Resource Manager 5.1.x
- Version 1.0.3- Download
- Released on 2015/02/03
- Compatible with Zenoss Resource Manager 4.2.x, Zenoss Resource Manager 5.0.x
Background
DB2 Zenpack
This ZenPack provides support for monitoring IBM DB2 Database on Linux, Unix, or Windows (LUW) hosts. Monitoring is performed using IBM JDBC Driver.
Support
This ZenPack is included with commercial versions of Zenoss and enterprise support for this ZenPack is provided to Zenoss customers with an active subscription.
Background
This ZenPack treats the databases as the fundamental component thereby allowing multiple databases to be associated with a single server.
Prerequisites
- DB2 must be running and listening on a target device's TCP port.
- DB2 access for the monitor user must be provided
Gallery
Features
The features added by this ZenPack are listed below. They are each detailed further below.
- Data Collection done with DB2 JDBC (Java)
- Multiple instance support via components
- TableSpace Support
- Split TableSpace Support
- Custom BufferPool Support
Connection String Formats
The Connection String is the data format that is used to specify a database. The connection string can have the following formats:
<fqdn.domain.com>:<Port>/<DB-Name>:user=<Username>;password=<Password>; or (the official DB2 form): jdbc:db2://<fqdn.domain.com>:<Port>/<DB-Name>:user=<Username>;password=<Password>;
Make sure to note the use of colons (:) and semi-colons(;) in the string, as DB2 is quite picky about the format.
For example:
Ex 1: s1.example.com:50000/SAMPLE:user=db2inst1;password=bird_flu; Ex 2: jdbc:db2://s1.example.com:50000/SAMPLE:user=db2inst1;password=bird_flu;
Discovery
Databases will be automatically discovered via connection string which contains username, password, port, and instance-name you provide. If starting from a new server without any DB2DB monitoring, simply bind the DB2 template from the gear menu on the bottom left of the server's infrastructure page.
Performance Monitoring
Host monitoring is separately done by the server monitoring template. This zenpack is a component-only addition to the host system. The host statistics are thus handled by the respective host template independently.
The following metrics are collected every 5 minutes by default.
NAME | System | Description |
---|---|---|
CURRENT_PARTITION_MEM | Instance | Current partition memory |
INSTANCE_MEMORY | Instance | Instance memory |
MAX_PARTITION_MEM | Instance | Total memory use for an instance |
APPLS_CUR_CONS | Database | Applications connected currently |
BP_NPAGES | Database | Number of buffer pool pages allocd |
COMMIT_SQL_STMTS | Database | Total SQL statements committed |
DBPARTITIONNUM | Database | DB partition number |
DEADLOCKS | Database | Deadlocks detected |
LOCK_WAIT_TIME | Database | Lock wait time |
LOG_WRITE_TIME_NS | Database | Log write time in nano-seconds |
LOG_WRITE_TIME_S | Database | Log write time in seconds |
POOL_DATA_WRITES | Database | BP data page writes to disk |
POOL_WATERMARK | Database | Highest memory pool use from db start |
ROWS_DELETED | Database | Rows deleted |
ROWS_INSERTED | Database | Rows inserted |
ROWS_READ | Database | Rows read |
ROWS_SELECTED | Database | Rows selected |
ROWS_UPDATED | Database | Rows updated |
SELECT_SQL_STMTS | Database | Total select SQL statements |
TOTAL_CONS | Database | Total connections to the DB |
TOTAL_HIT_RATIO_PERCENT | Database | Buffer Pool hit ratio |
TOTAL_PHYSICAL_READS | Database | Physical reads from storage |
TOTAL_SORT_TIME | Database | Total Sort Time |
UID_SQL_STMTS | Database | Update/Insert/Merge/Delete Statements |
POOL_DATA_L_READS | TableSpace | Pool Logical Reads |
POOL_DATA_P_READS | TableSpace | Pool Physical Reads |
POOL_INDEX_L_READS | TableSpace | Pool Index Logical Reads |
POOL_INDEX_P_READS | TableSpace | Pool Index Physical Reads |
POOL_TEMP_DATA_L_READS | TableSpace | Pool Temp Data Logical Reads |
POOL_TEMP_DATA_P_READS | TableSpace | Pool Temp Data Physical Reads |
POOL_TEMP_INDEX_L_READS | TableSpace | Pool Temp Index Logical Reads |
POOL_TEMP_INDEX_P_READS | TableSpace | Pool Temp Index Physical Reads |
TBSP_FREE_SIZE_KB | TableSpace | Tablespace Free Size (KB) |
TBSP_TOTAL_PAGES | TableSpace | Tablespace total pages |
TBSP_TOTAL_SIZE_KB | TableSpace | Tablespace Total Size (KB) |
TBSP_USABLE_PAGES | TableSpace | Tablespace usable pages |
TBSP_USABLE_SIZE_KB | TableSpace | Tablespace usable size (KB) |
TBSP_USED_PAGES | TableSpace | Tablespace used pages |
TBSP_USED_SIZE_KB | TableSpace | Tablespace used size (KB) |
TBSP_UTILIZATION_PERCENT | TableSpace | Tablespace Utilization Percentage |
Database Support
This ZenPack allows for multiple database per server to be monitored. The Context Display area has the following:
- Graphs
- Events
- Details
- Templates
- TableSpaces
Database Graphs
Database support includes the following graphs:
- Cache Hit Ratio
- SQL Statements
- Write/Read Ratio
- Deadlocks
- Physical Reads and Writes
- Row Statistics
- Connections: Current and New
- Sort Time per Transaction
- Lock Wait Time
Tablespace Support
DB2 TableSpace is included. The TableSpace grid has links to the parent Database. The Context Display area has the following:
- Graphs
- Events
- Details
- TableSpaces
- Templates
TableSpace Graphs
DB2 TableSpaces can have dynamically allocated disk space. This can cause some confusion when considering currently allocated space vs reserve allocated space.
TableSpace graphs include the following:
- Disk Space: Used, Free, Total
- Utilization
- Buffer Pool Hit Ratios: Data, Index, Temp
Impact Support
In DB2, the natural object of connection is the "Database" (rather than "Instance" as in Oracle). We therefore focus on the database and associated tablespaces. Here is a diagram of the DB2 structural dependency tree:
Given that we ignore the DB2 Instance objects, the Zenpack supports impact as follows:
Database objects depend on
- The containing host systems
- The contained tablespace objects
- Tablespaces depend on Database objects
Limitations
The following extra features are not supported:
- Advanced Copy Services
- Connection concentrator
- DBF partitioning
- DB2 governor
- PureScale data sharing
- Geodetic Extender
- HADR
- Homogeneous Q replication
- Sybase compatibility
- MDC
- Table partitioning and workload management
Installed Items
Installing this ZenPack will add the following items to your Zenoss system:
Configuration Properties
- zDB2ConnectionStrings
- zDB2User
- zDB2Password
Modeler Plugin
- zenoss.db2jdbc.Databases
Datasource Types
- DB2
Monitoring Templates
- DB2Database: SnapDB (Type DB2)
- DB2TableSpaces: SnapTBSP (Type Command)
Installation and Configuration
Installation consists of the following steps which will be covered in depth:
- Configure DB2 Server for monitoring
- Install the target device in Zenoss
- Install the ZenPack on Zenoss
- Bind the plugin modeler template to server
- Set the zDB2ConnectionStrings property
- Model the device
Configure DB2 Server
The DB2 server must be configured correctly and granted access in order to allow access to the tables needed for monitoring. These steps are required:
- Make sure DB2 server is listening on the correct TCP port (usually 50000)
- Configure DB2 for monitoring Access. We recommend setting the system up with a dedicated user as per the Security Section (see Security_.)
Install the Target Device in Zenoss
Install the target server as you normally would. Just select the type of server that DB2 will run on (Linux, Solaris, Windows, etc..). The host server should have it's base data collection services running as usual in order to monitor its vitals. Make sure to set the zProperty: zCommandCommandTimeout to 120.
Installing the ZenPack
You can install the ZenPack egg via the GUI or manually.
To manually install the ZenPack egg, take the following steps:
sudo su - zenoss zenpack --install ZenPacks.zenoss.DB2-*.egg zenoss restart
where ZenPacks.zenoss.DB2-*.egg should match your egg version.
Now you are ready to bind the modeler:
Bind the Plugin Modeler Template to Server
- From the Infrastructure page, select your server.
- From Modeler Plugins, push zenoss.db2jdbc.Databases to the Selected group
- Save
- Now you are ready to add the ConnectionStrings
Set the zConnectionStrings Property
- Select your server from the Infrastructures Tab
- Click on Configuration Properties
- Search for zDB2User: Enter your username for the DB (zenoss by default)
- Search for zDB2Password: Enter your password for the DB (zenoss by default)
- Search for zDB2 and locate and select zDB2ConnectionStrings
Double-click (open) the zDB2ConnectionStrings dialog. You can protect your passwords by setting ${here/zDB2Password} in the connection string as in the following examples:
s1.db2.com:50000/SAMPLE:user=${here/zDB2User};password=${here/zDB2Password}; ${here/manageIp}:50000/SAMPLE:user=${here/zDB2User};password=${here/zDB2Password};
We STRONGLY recommend that you install the same read-only user in your databases so that you don't have to chase down usernames and passwords for your instances. A read-only DB user will also enhance your security and be less of a risk to data corruption in your DB. See the Security_ section below for details.
Optional Java Path
You can use the default version of Java or select a custom version. To use a custom version you can set one of the following environment variables in the zenoss account (in order of priority):
- JAVA_BIN
- JAVA_HOME
Batch Configuration with zenbatchload
You can also add your devices in batch for convenience and automation.
- Create a text file (filename: /tmp/db2.txt). Each server has a stanza like
'/Devices/Server/SSH/Linux' 'test.com' setManageIP='10.175.210.160', zDB2ConnectionStrings=['jdbc:db2://test.com:50000/SAMPLE:user=zenoss;password=${here/zDB2Password};', 'jdbc:db2://test.com:50000/HOLD:user=zenoss;password=${here/zDB2Password};', 'jdbc:db2://test.com:50002/EVAL:user=zenoss;password=${here/zDB2Password};',]
- Run the command on the terminal
zenbatchload /tmp/db2.txt
Model the Component
- From the device view, select Model Device from the gear menu.
- If all goes well, Zenoss should model the device.
- Since the Instances are just components of the server, you should see them hanging off of the device as components.
Reconfiguration and Remodeling
Deleting or Changing
If you want to delete or change a database from the zDB2ConnectionStrings, follow these steps:
- Delete the or change connection string from zDB2ConnectionStrings
- Manually delete the Database component
- Model the device as before
Adding a Database
To add a device, you simply:
- Add the connection string to zDB2ConnectionStrings
- Model the device as before
Security: DB2 Credentials
In order to secure the DB2 credentials, we recommend that you create a dedicated user that has read-only access, and then use TALES masked zProperties to further protect them. The process is as follows:
- Create a dedicated system user and group (zenoss, zenoss)
- Set this users group permissions in DB2 into the SYSMON group
- Setup zProperties for User and Password
- Setup the Connection string to use TALES expressions for credentials
- Test the setup
Create a Dedicated DB2 User and Group
From inside your Linux/Unix system create user (zenoss) and group (zenoss) as you would any user. You should give that use the least privileges that your security system provides. The user's group identity will be added to DB2 in the next section to give access to the DB.
Set the Group Permissions in DB2
For each running instance, you must log into that instance account. Then connect to DB2 Instance account. For DB2 10.5 it goes like this:
[db2inst1@mp3:~]: source sqllib/db2profile [db2inst1@mp3:~]: db2 attach to DB2INST1 [db2inst1@mp3:~]: db2 update dbm cfg using SYSMON_GROUP zenoss [db2inst1@mp3:~]: db2 detach
You must restart the DB2 instance in order for these changes to take effect.
You can test the setup with the following:
[db2inst1@mp3:~]: db2 connect to SAMPLE [db2inst1@mp3:~]: db2 GET DATABASE MANAGER CONFIGURATION | grep SYSMON *SYSMON group name (SYSMON_GROUP) = ZENOSS*
The last line indicate sucess.
Setup the zProperties for User/Password
In Infrastructure -> DeviceName you select Configuration Properties and set:
zDB2User: zenoss zDB2Password: YourSecretPassword
Setup the ConnectionString with TALES
The full ConnectionString format is:
jdbc:db2://host.example.net:Port/DB_NAME:user=USERNAME;password=PASSWORD; or optionally without the prefix: host.example.net:Port/DB_NAME:user=USERNAME;password=PASSWORD;
With our actual values of username, password:
mp3.zenoss.loc:50000/SAMPLE:user=zenoss;password=zenoss;
We can generify this considerably by using our TALES expressions:
${here/manageIp}:50000/SAMPLE:user=${here/zDB2User};password=${here/DB2Password};
If you use the recommended username and password you would only need to modify Port and DB_NAME for each connection string
${here/manageIp}:50000/SAMPLE:user=${here/zDB2User};password=${here/zDB2Password}; ${here/manageIp}:50001/HOLD:user=${here/zDB2User};password=${here/zDB2Password}; ${here/manageIp}:50002/DEFECT:user=${here/zDB2User};password=${here/zDB2Password}; ${here/manageIp}:50002/RELOAD:user=${here/zDB2User};password=${here/zDB2Password};
Custom Queries and DataPoints
In order to setup a custom query you should create a query that returns a single row of data. The datapoints should be uppercase as that is how DB2 returns the data.
Create the DataSource
Here are the steps to create a call at the database level:
- Goto Advanced -> Monitoring Templates -> DB2Database
- Click on plus to add a new datasource, type db2sql, provide a unique Name
- Fill in the other values as the original datasource
Provide the SQL query. Here is an example:
select * from sysibmadm.snapdb
Create the DataPoints
- From your new DataSource, select the options wheel, select "Add Data Point"
- Make sure your datapoint name is uppercase to match the SQL query return
Create Graphs and Thresholds
You can now use your new datapoints to create graphs and thresholds in the standard way.
References
- IBM JDBC Drivers: http://www-01.ibm.com/support/docview.wss?uid=swg21363866
Changes
1.0.5
- Impove sanity checks for ConnectionString in modeler (ZEN-24868)
1.0.4
- Improve Graphs
- Added failsafe for multi-partitioned databases
- Datapoints Changed from Derive to Gauge:
- COMMIT_SQL_STMTS:
- LOCK_WAIT_TIME
- ROWS_READ
- ROWS_SELECTED
- TOTAL_CONS
- Affected Graphs due to datapoint change:
- SQL Statements
- Lock Wait Time
- Rows Stats
- Connections
Note: 4.2.X users will have to remove the RRD data files associated with those datapoints so that affected graphs will populate.
1.0.3
- Improved Java detection
- Allow Java Binary to be set by environment variable
- Improve ConnectionString handling
- Add Europa Support for graphs
1.0.2
- Improved modeler handling
Installation
Normal Installation (packaged egg)
- Download the appropriate egg file for the version of Zenoss you are running.
- Ensure you are logged in as the zenoss user:
$ sudo su - zenoss
- Install the ZenPack:
$ zenpack --install ZenPacks.zenoss.DB2-*.egg
- Restart these services:
$ zenoss restart
Discuss
New: Don't forget to add yourself to the Zenoss User Map!