Example reports for DB2 for z/OS

This section includes queries that you can use as a base for building your own DB2 for z/OS reports.

Start by looking at the following queries:

  • DB2 z/OS Connection Report (Main Entity: Session Start). This report can help you get the “big picture” of what types of applications and users are connecting to your DB2 environment.
  • DML on Sensitive Objects (Main Entity: Object) This report depends on two groups: One that contains your sensitive objects and one that contains DML commands. You can easily clone the beginning of this report from the System-defined report “DML Execution on Sensitive Objects” and add the additional columns such as Network Protocol and Service Name.
Table 1. DB2 z/OS Connection Report Query Fields (Main Entity: Session Start)
Entity Attribute Field Mode Comments
Session Session Start Value (Check Order-by, Sort Rank 1 and check Dec end box)  
Client/Server Service Name Value This is the SSID
Client/Server DB User Name Value  
Client/Server Network Protocol Value Concatenation of Connection Name and Connection Type. Example TSO:BATCH and DRDA:SERVER
Client/Server Client IP    
Client/Server Server IP    
Client/Server Source Program   Concatenation of server name with correlation name and ID. Could vary based on what the application sends. Example from distributed application (DB2JCC) could be: GUARDIUMZ4.SVL.I:DB2JCC_APPLI A CICS transaction could be the transaction identifier.
Table 2. DB2 z/OS Connection Report: Query Conditions
Entity Attribute and Operator Comments
WHERE Client/Server DB Protocol=Value DB2/Z This narrows report to just DB2 z/OS traffic.
AND Client/Server Service Name LIKE Parameter ServiceName Use % in your runtime parameters to display data from all SSID data.
AND Client/Server DB User Name LIKE Parameter DBUser Use % in your runtime parameters to display all data.
Table 3. DML on Sensitive Objects Query Fields (Main Entity: Object)
Entity Attribute Field Mode Comments
FULL SQL Timestamp Value  
Client/Server Service name Value This is the SSID.
Client/Server Network Protocol Value Concatenation of Connection Name and Connection Type. Example TSO:BATCH and DRDA:SERVER
Client/Server DB User Name Value  
Object Object Name Value  
Command SQL Verb    
Client/Server Client IP    
FULL SQL Full SQL   This field is populated only with LOG FULL DETAILS policy rule.
FULL SQL Full SQL ID Value with Order-by checked, Sort-by =1 and Descend checked This can help order events within a second in the correct order.

This field is populated only with LOG FULL DETAILS policy rule.
App User Name App User name Value

Consists of PLAN, SQLID, PROG and DB_NAME Example: PLAN=DISTSERV ; SQLID=SYSADM ; PROG=SYSLH200 ; DB_NAME=DSN00006

Note: If AppEvents mechanism is used to collect ‘real’ end user, this field will display that.
Application Events DB2 Client Info Value

Information in the DB2 Client Info depends on what the application assigns to these fields. None, some, or all values could be populated: WSUSER, APPL, WKSTN.

Table 4. DML on Sensitive Objects Query Conditions
Entity Attribute and Operator Comments
WHERE Client/Server DB Protocol=Value DB2/Z This narrows report to just DB2 z/OS traffic.
AND Client/Server Service Name LIKE Parameter Service Name Use % in your runtime parameters to display data from all SSIDs.
AND Client/Server DB User Name LIKE Parameter DB User Use % in your run time parameters to display all data
AND Object Object Name LIKE GROUP Sensitive Objects (or whatever you have named your sensitve objects group) Objects could be views, aliases, package names, or plan names. If Quick Parse Native is used, the schema.base-table-name is used instead of view or aliases. Make sure your groups accommodate this flexibility.
AND Command SQL Verb IN GROUP DML Commands (or whatever you have named your DML group)  
Tip: Reporting on SELECT * Users: You can choose whichever query fields you like and include a query condition for Full SQL LIKE Value %select%*%. Alternatively, you can specify this as a Parameter and specify it at run time (using the wrench icon to customize the run time parameters).