IBM Support

DB2 agent 7.1, Monitor auto resize tablespace with page size using Customized SQL feature

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:




[{"Product":{"code":"SS3JRN","label":"Tivoli Composite Application Manager for Applications"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"ITCAM Agent for DB2 - 5724B96DO","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"7.1","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]

Document Information

Modified date:
17 June 2018

UID

swg21634712