Db2 Tools for z/OS

 View Only

Intelligent application discovery

By Kate Wheat posted Tue August 09, 2022 01:10 PM

  

by the Db2 Performance Tools team


A lot of Db2 customers have scripts and procedures to update their data, but when Db2 objects are changed in bulk, and no referential integrity exists among those tables, it’s difficult to comprehensively update the objects correctly.  With IBM Db2 Log Analysis Tool and IBM Db2 Query Monitor, customers can gain confidence that they’ve covered all critical relationships when performing these mass updates.

This blog looks at application discovery from the Db2 Query Monitor perspective, which can be used independently from the IBM Db2 Log Analysis Tool.  Customers may find deeper insights into applications and object relationships using both tools together, but this is not required.

What is Query Monitor?  Query Monitor enables you to collect, summarize, and display SQL resource consumption down to the level of the individual SQL statement text.  It collects SQL and related objects execution time statistics and details for all/any subsystems it’s monitoring.  Using the included performance history database, a regularly scheduled LOAD utility job can be triggered to update the offload database with the latest performance metrics from the VSAM backstores.  By iteratively using the queries described in SCQMSAMP to produce a ‘report,’ skilled DBAs can derive and validate application relationships that might be uncertain without execution analysis provided by Query Monitor.

Query Monitor provides the following sample queries for the following application insights.  (See the member header information for documentation on how to use as well as any use restrictions.)

  • CQMODSQ    Discovery by Objects by SQL
  • CQMOOBJ    Discovery by Object
  • CQMOPGM    Discovery by program
  • CQMOPLN    Discovery by plan
  • CQMOSCH    Discovery by schema
  • CQMOSSQ    Discovery by statics SQL
  • CQMOXC     Discovery by execution Count
  • CQMOXS     Discovery by execution by a single SQL

The following offload tables are used by the samples above:

  • CQM_SUMM_OBJECTS
  • CQM_SUMM_METRICS
  • CQM_SUMM_TEXT
  • CQM_STMT_TYPES
  • CQM_INTERVALS

After the desired interval data is loaded, begin by choosing an interval start time.  This start time will be the value used for <a>.INTERVAL_START for each subsequent query.

Discovery by Object example

SELECT DISTINCT
A.PLAN, C.STATEMENT_DESC ,
REPLACE(REPLACE(REPLACE(B.OBJECT_TYPE,'I','INDEX'),'T','TABLE'),'V','VI
EW') AS OBJECT_TYPE
, B.OBJECT_CREATOR, B.OBJECT_NAME
FROM SYSTOOLS.CQM_SUMM_METRICS A,
SYSTOOLS.CQM_SUMM_OBJECTS B,
SYSTOOLS.CQM_STMT_TYPES C
WHERE A.METRICS_TOKEN = B.METRICS_TOKEN
AND A.TYPE = C.STATEMENT_TYPE
AND A.DB2_SUBSYSTEM = ‘DB2’
AND A.INTERVAL_START = B.INTERVAL_START
AND A.INTERVAL_START = '2022-02-17 11:00:44.679946'
AND B.OBJECT_CREATOR = 'GLW22NJP'
AND B.OBJECT_NAME = 'GLWXDPT1'AND B.OBJECT_CREATOR <> 'SYSIBM'
ORDER BY B.OBJECT_CREATOR,B.OBJECT_NAME;

 

This query yields something similar to the following result:


Augment the supplied queries for your needs to aid in IT data management.

Technical requirements:

  • Db2 Query Monitor APAR PH47112 / PTF UI81721 (and optionally, Db2 Log Analysis Tool APAR PH46290 / PTF UI80632.)
  • Object collection for Query Monitor enabled: OBJECTS(Y)
  • Db2 Query Monitor interval data offloaded to the Performance History Database. See DDL:
    • hilev.CQM330.IBMTAPE.SCQMSAMP(CQMDDL)
    • hilev.CQM330.IBMTAPE.SCQMSAMP(CQMDDLST)

#Db2Toolsforz/OS
0 comments
11 views

Permalink