IT28809: DB2 ACTIVITIES EVENT MONITOR MIGHT REPORT INCORRECT METRICS IN ACTIVITIES TABLE FOR BATCHED STATEMENTS

Db2 Version 11.1 Mod 4 Fix Pack 5 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 6 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 6 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• Query metrics captured by event monitor for activities will
  report cumulative values in activities table for statements
  executed as batch (via executeBatch).
  For example, with multiple inserts executed as a single batch:
  
  PreparedStatement pstmt = c.prepareStatement("insert into
  mytable values (?,?)");
  for(int i=0;i<5;i++)
  {
      pstmt.setInt(1,i);
      pstmt.setString(2,String.valueOf(i));
      pstmt.addBatch();
  }
  pstmt.executeBatch();
  
  activities table will report cumulative values for e.g.
  ROWS_MODIFIED in ACTIVITIES table. Values in the ACTIVITYMETRICS
  will be captured correctly, see an example below:
  
  select
      substr(a.appl_id,1,30) as appl_id
      , a.uow_id
      , a.activity_id
      , a.rows_modified
      , a.user_cpu_time
      , m.rows_modified
      , substr(s.stmt_text,1,20) as stmt_text
  from
      activity as a
      , activitymetrics as m
      , activitystmt as s
  where
      a.appl_id     = m.appl_id
      and a.uow_id      = m.uow_id
      and a.activity_id = m.activity_id
      and a.appl_id     = s.appl_id
      and a.uow_id      = s.uow_id
      and a.activity_id = s.activity_id
  order by
      a.time_completed asc "
  
  APPL_ID         . UOW_ID  ACT_ID ROWS_MOD  USER_CPU_T ROWS_MOD
  STMT_TEXT
  ----------------. ------- ------ -------- ----------  ---------
  -----------
  127.0.0.1.58006..       2      1        1         81          1
  insert into
  127.0.0.1.58006..       2      2        2        135          1
  insert into
  127.0.0.1.58006..       2      3        3        172          1
  insert into
  127.0.0.1.58006..       2      4        4        199          1
  insert into
  127.0.0.1.58006..       2      5        5        225          1
  insert into
  
  Note:
  Metrics in activities will be propagated only with the legacy
  statement monitor enabled (update monitor switches using
  statement on)
  

Local fix

• Use the values from the ACTIVITYMETRICS table
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Db2 11.1.4.5 or higher                            *
  ****************************************************************
  

Problem conclusion

• Upgrade to Db2 11.1.4.5 or higher
  

Temporary fix

Comments

APAR Information

• 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 PSN

     UP