IBM Support

IT32654: ACTIVITY EVENT MONITORS RETURN INCORRECT CPU TIME FOR SP CALLS WITH NESTED STATEMENTS

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 

APAR status

  • Closed as program error.

Error description

  • If you have defined activity event monitors to get CPU times for
    stored procedures with nested statements, you may get incorrect
    results.
    
    Example :
    
    CREATE PROCEDURE SP1 (
    IN IN_STMT VARCHAR(10000)
    )
    LANGUAGE SQL
    SPECIFIC EXECSQL
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    COMMIT ON RETURN NO
    NO EXTERNAL ACTION
    BEGIN
    EXECUTE IMMEDIATE in_stmt;--
    END@
    
    
    CREATE EVENT MONITOR "DB2ACTIVITIES"
    FOR ACTIVITIES
    WRITE TO TABLE ACTIVITY (TABLE ACTIVITY_DB2ACTIVITIES"),
    ACTIVITYMETRICS (TABLE "ACTIVITYMETRICS_DB2ACTIVITIES"),
    ACTIVITYSTMT (TABLE ACTIVITYSTMT_DB2ACTIVITIES"),
    ACTIVITYVALS (TABLE "ACTIVITYVALS_DB2ACTIVITIES"),
    CONTROL (TABLE "CONTROL_DB2ACTIVITIES")
    AUTOSTART@
    
    
    call sp ( 'delete from tab1 where 1=1')
    
    db2 "SELECT UOW_ID,ACTIVITY_ID,SYSTEM_CPU_TIME,USER_CPU_TIME
    From ACTIVITY_DB2ACTIVITIES"
    
    UOW_ID ACTIVITY_ID SYSTEM_CPU_TIME USER_CPU_TIME
    ----------- -------------------- --------------------
    --------------------
    1007 2 21 2616
    1007 1 374 5752
    
    $ db2 "SELECT UOW_ID,ACTIVITY_ID, SUBSTR(STMT_TEXT, 1,70) AS
    STMT_TEXT FROM ACTIVITYSTMT_DB2ACTIVITIES"
    
    UOW_ID ACTIVITY_ID STMT_TEXT
    ----------- --------------------
    ----------------------------------------------------------------
    ------
    1007 2 delete from cpu where 1=1
    1007 1 CALL sp1(?)
    
    CPU time for the SP is twice the CPU time of the delete.
    

Local fix

  • Use TOTAL_CPU_TIME returned by ACTIVITYMETRICS
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All Db2 users                                                *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to at least Db2 11.1 Mod 4 Fix Pack 6                *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed Db2 11.1 Mod 4 Fix Pack 6
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT32654

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2020-04-25

  • Closed date

    2021-03-19

  • Last modified date

    2021-03-19

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1"}]

Document Information

Modified date:
20 March 2021