Question & Answer
Question
How can auto-resize enabled tablespaces be monitored using attributes from two attribute groups "DB2 Tablespace Auto-resize" and "DB2 Tablespace" of ITCAM agent for DB2?
Cause
If creating a situation alert for the tablespace utilization of DB2 tablespaces with Automatic Storage enabled, you may have the need to create situations which include the page size of tablespace.
For example:
IF DB2 Tablespace Auto-resize.Using Auto Storage=YES and DB2 Tablespace.Page Size=4096 and DB2 Tablespace Auto-resize.Total Pages = 8192 and DB2 Tablespace Auto-resize.TBSP Utilization(%) >= 95%
Since attribute group DB2 Tablespace Auto-resize does not include the "Page Size" attribute, the above situation will require using two attribute groups "Tablespace Auto-resize" and "Tablespace" as shown above. Combining these two attribute groups in a situation is not feasible, but this monitoring can be achieved by implementing a customized SQL solution.
Answer
Note: The 7.1.0 IF0001 includes following enhancement:
RFE: 33271
Abstract: Add 'Page Size' and 'Tablespace Type' attributes into Tablespace Auto-Resize attribute group
If running 7.1.0 IF0001 or higher level of DB2 agent, the Customized SQL solution is not required as the Page Size and Tablespace Type attributes are included.
Following steps describe the customized SQL solution. This technote also serves as an example of how the Customized SQL feature can be used.
1. Modify <ITMHOME>/config/ kudcussql.properties and add below section, use any string for SQL_ID:
[SELECTSQL]
SQL_ID=non-autoresize_utli
SQL_TEXT=select TBSP_ID,TBSP_NAME,TBSP_UTILIZATION_PERCENT,TBSP_USING_AUTO_STORAGE,TBSP_TOTAL_PAGES,TBSP_TYPE,TBSP_PAGE_SIZE from SYSIBMADM.TBSP_UTILIZATION
Refer to URL for details of Customized SQL monitoring feature of DB2 agent:
http://publib.boulder.ibm.com/infocenter/tivihelp/v24r1/index.jsp?topic=%2Fcom.ibm.itcama.doc_7.1%2Fdb2agent71_user22.htm&path=2_4_4_3_3
2. Restart the agent and navigate to Customized SQLs workspace.
3. Find the SQL_ID in Customized SQL Definition table view and right click that row. Select "Customized SQL Result" link from the context menu:
4. Input the database alias name when prompted, and click OK.
5. You will see the result as shown (single table view has been split into two screen prints to accommodate on the display page):
6. Based on the order of columns in the customized SQL, the displayed column names in Customized SQL Result view correspond to attributes as shown in the table below:
Column Name | Attribute |
Second Number Value | Table space Utilization % |
Second String Value | Table space Type |
Third Number Value | Table space using Auto Storage |
Fourth Number Value | Table space Total Pages |
Fifth Number Value | Table space Page Size |
7. Create a new situation as shown in the situation formula screen print below, the new situation must contain SQL ID and DB Alias Filter Name.
When creating the situation, you can input * in DB Alias Filter Name to include all databases, instead of creating one situation for each individual database. Select DB_Alias as the display name, so that when the situation fires, the situation event data will identify the database for which the event is true.
Situation formula ( example below uses Table space utilization % > 20 )
8. The situation fires as shown:
Was this topic helpful?
Document Information
Modified date:
17 June 2018
UID
swg21634712