How to query commands issued by Db2 within Sysview for Db2
search cancel

How to query commands issued by Db2 within Sysview for Db2

book

Article ID: 53158

calendar_today

Updated On:

Products

SYSVIEW Performance Management Option for DB2 for z/OS

Issue/Introduction

For auditing purposes you can capture every command issued against Db2 and who issued it with Sysview for Db2 for z/OS (IDB2).

Resolution

In the Sysview Performance Monitor for Db2 for z/OS User Guide section Auditor Reports, there is a section called 'DB2 Commands Issued'.
It states the online request, DB2CMDS, 'displays an audit trail of all DB2 commands and who issued them'. An example of this request is shown in this section.

>>>

Menu  Print  Tools  Help    SYSVIEW for DB2     SSID LPAR     03/26/25 15:30:06
                               20.0.15         XXXXPR20               USERID
 
                                                                      FOCUS OFF
 R/DB2CMDS       DB2 Commands Issued                                  Row 1/4915
 
 Begin: 03/26/25 07:24:22 Plan: DGL1P200 CorrID:  XXXXXXX       EndUser: XXXXXXX
                        Conn: DB2CALL  AuthID: XXXXXX   Workstation: DB2CALL
                                           Step: FSTLOAD
 
        -DISPLAY DATABASE( XXXXXXX  ) SPACENAM(INPINU01) LOCKS    LIMIT(*)
 
   End: 03/26/25 07:24:22 Duration:     0.0021 RetCode: 0    Reason: 00000000
 -------------------------------------------------------------------------------
 Begin: 03/26/25 07:24:47 Plan: DGL1P200 CorrID: XXXXXXX     EndUser:  XXXXXXX

                        Conn: DB2CALL  AuthID: XXXXXX   Workstation: DB2CALL
                                           Step: FSTLOAD
 
        -START DATABASE( XXXXXXX  ) SPACENAM (S6233536)       ACCESS(RW   )
 
   End: 03/26/25 07:24:47 Duration:     0.0022 RetCode: 0    Reason: 00000000
 -------------------------------------------------------------------------------
 Begin: 03/26/25 07:24:47 Plan: DGL1P200 CorrID:  XXXXXXX       EndUser:  XXXXXXX
                        Conn: DB2CALL  AuthID: XXXXXX   Workstation: DB2CALL
                                           Step: FSTLOAD
 
        -DISPLAY DATABASE( XXXXXXX  ) SPACENAM(S6233536) LOCKS    LIMIT(*)
 
   End: 03/26/25 07:24:47 Duration:     0.0042 RetCode: 0    Reason: 00000000
 -------------------------------------------------------------------------------
 Begin: 03/26/25 07:24:47 Plan: DGL1P200 CorrID:  XXXXXXX       EndUser:  XXXXXXX
                        Conn: DB2CALL  AuthID: XXXXXX   Workstation: DB2CALL
                                           Step: FSTLOAD
 
        -START DATABASE( XXXXXXX  ) SPACENAM (INPICU00)       ACCESS(RW   )
 
   End: 03/26/25 07:24:47 Duration:     0.0028 RetCode: 0    Reason: 00000000
 -------------------------------------------------------------------------------
 Begin: 03/26/25 07:24:47 Plan: DGL1P200 CorrID:  XXXXXXX      EndUser:  XXXXXXX

This report can be run in batch also. It is found in the request library, highlvl.CDBATREQ, member name DB2CMDS.

All you need is just give it a different name like DB2CMDS1 since DB2CMDS is started up by the collector like any other request(report) in the STARTUP member.

Please refer Sysview Performance Monitor for Db2 for z/OS Writing Requests document for complete details.

To create data that can be used by the report, start a performance trace when DB2 starts up to capture IFCIDs 90 and 91:

        -START TRACE(PERFM) CLASS(30) IFCID(90,91) DEST(SMF)

Or turn on IFCIDS 90 & 91 in your sysparms HIST-STATS-RECS=(nn,nn,...)