Fixes are available
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
RB10 PSY
UP
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
04 May 2022