IT31704: MON_GET_TABLE/MON_GET_INDEX table functions might respond slowerwhen executed concurrently on databases with many tables

Db2 Version 11.5 Mod 4 Fix Pack 0 for Linux, UNIX, and Windows
Db2 Version 11.5 Mod 5 Fix Pack 0 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 6 for Linux, UNIX, and Windows
Db2 Version 11.5 Mod 5 Fix Pack 1 for Linux, UNIX, and Windows
Db2 Version 11.5 Mod 6 Fix Pack 0 for Linux, UNIX, and Windows
Db2 Version 11.5 Mod 7 Fix Pack 0 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 7 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• Performance of MON_GET_TABLE/MON_GET_INDEX table functions will
  be affected if those are executed concurrently against databases
  with high number (tens of thousands or more) of table control
  blocks (TCBs) loaded into memory and run with non-NULL tabschema
  or tabname arguments.
  
  Loaded TCBs can be listed by:
  $ db2pd -db <db_name> -tcbstats
  
  or counted with:
  $ db2 "select count(*) from table(mon_get_table('','',-1))"
  
  During concurrent execution response time will degrade due to a
  contention on heap memory allocation by malloc function.
  
  A backtrace on threads of Db2 engine (db2sysc) obtained with
  $ db2pd -stack all
  or a debugger (gstack/procstack) will show threads with
  ossStrCompWithPads near the top of the stack, e.g.
  
  ...
  free_common
  _ossMemFree
  ossStrCompWithPads
  sqlrwFilteredTableIterator::execute
  ...
  monGetTable
  ...
  
  or:
  
  ...
  malloc_common
  _ossMemAlloc
  ossStrCompWithPads
  sqlrwFilteredTableIterator::execute
  ...
  monGetIndex
  ...
  

Local fix

• Database deactivation and activation will remove TCBs from
  memory which temporarily should improve the response time of
  those functions.
  
  On AIX platform performance can be also improved by switching to
  the multiheap malloc implementation, which requires
  1. Adding extra environmental variables to Db2 profile,
  sqllib/userprofile (persistent during across instance upgrades)
  or sqllib/db2profile (which will be replaced by default during
  db2iupdt)
  
  export MALLOCOPTIONS=buckets,multiheap:4
  export
  MALLOCBUCKETS=number_of_buckets:128,bucket_sizing_factor:64,bloc
  ks_per_bucket:1024
  
  2. Setting appending those two options to DB2ENVLIST
  
  db2set DB2ENVLIST="MALLOCOPTIONS MALLOCBUCKETS"
  
  3. Restarting the instance for settings to be picked up by Db2
  engine.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * all                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to 11.5m4fp0 or higher                               *
  ****************************************************************
  

Problem conclusion

• Upgrade to 11.5m4fp0 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:

  IT31795

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RB50 PSN

     UP