How to report on the execution count of a stored procedure?

Version 2
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    MainView for DB2


    How to report on the execution count of a stored procedure?


    You can report on the execution count of stored procedure using DB2 field QPACSWITCH. QPACSWITCH field reports on the number of times package was invoked from a different package. For the first package run by an application, the initial call counts as a package switch. If this package called a nested package (such as a trigger, UDF, or stored procedure), a switch will not be counted upon return from such a package. 

    1) Data Collector batch report BACCPKSR - Thread accounting package summary report field  "Exec Count" .  

    1                       BACCPKSR: THREAD ACCOUNTING PKG SUMMARY                          02/16/11 14:14:48    PAGE     1      DS Group : DSNDIA        Member: DIA1     DB2 SSID : DIA1 9.1    Location: DIA                                                       Actual From: 2011-02-15-00.08.37                                                                                                         to: 2011-02-15-22.35.56                                     /------------------------------- Averages per package execution -----------------------------\     Collection ID/      Thrd  Exec Stmt GetPage   Class 2     CPU Time                 Class 8      I/O        Lock      Group     Package Name       Count Count Count   Count Elapsd Time     (All)     zIIP Time   Wait Time  Wait Time  Wait Time  Wait Time     -----------------  ----- ----- ----- ------- ----------- ----------- ----------- ----------- ---------- ---------- ----------  +  ABCCOLL              28    42    42     211  00:00.2569  00:00.0131  00:00.0000  00:00.0000 00:00.0000 00:00.0000 00:00.0000     ABCSPPKG                                                     5.12%       0.00%       0.00%      0.00%      0.00%      0.00%

    2) Performance Reporter DB2 table  DMRAPDTL - Package accounting detail table column PKGSWITCH.  


    Article Number:


    Article Type:


      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles