IT17317: THE SMARTGUIDE MAY OVERSIZE BUFFERPOOLS FOR NEW DATABASES ON DPFSETUP.

APAR status

• Closed as program error.

Error description

• The issue defined in this APAR is related to problems with
  database activation on DPF environments (especially ones with
  many logical nodes on the same physical box). The problem
  persist only when INSTANCE_MEMORY is set to AUTOMATIC, and
  happens during database creation. During subsequent activation
  DB2 tries to start with extensively large bufferpools. We
  observe that those bufferpools are configured by configuration
  advisor (SmartGuide). Snippet from traces:
  
  151237    data DB2 UDB config/install sqlf_db2cfgsg cei
  (3.3.6.87.2.40)
      pid 42551 tid 140737353975584 cpid 105 node 0 probe 40
      bytes 32680
  ...
      0A62 703D 310A 4942 4D44 4546 4155 4C54          IBMDEFAULT
      4250 3D34 3037 3333 3938 3A38 3139 320A    BP=4073398:8192.
      6E65 7757 413D 310A 5359 5344 4546 4155    newWA
  ...
      6442 703D 310A 4942 4D44 4546 4155 4C54          IBMDEFAULT
      4250 3D31 3030 303A 3831 3932 0A6F 6C64    BP=1000:8192.old
      5741 3D31 0A53 5953 4445 4641 554C 5455    WA
  
  The calculation in sqlf_db2cfgsg function might end up in to
  high values which later on results in oversized memory for the
  DB. We have here underwrap problem during compute of bufferpools
  parameters. The underwrap is caused by SHEAPTHRES parameter
  which influences memory available for bufferpool area (for DPF
  systems using legacy SHEAPTHRES, the value is configured to 25%
  of machine size, spread across partitions). As the consequence,
  everything else will be configured to minimum since SHEAPTHRES
  takes up the entire 25% default, and furthermore we have the
  underwrap problem mentioned earlier.
  
  Problem above may cause paging and might result in system hang.
  Once this happens, we might observe OOM kille in action:
  

Local fix

• You can do one of following:
  
  1) Disable automatic configuration:
  db2set DB2_ENABLE_AUTOCONFIG_DEFAULT=OFF
  
  2) Create database with "autoconfigure using mem_percent "
  clause:
  db2 create db <db> autoconfigure using mem_percent <50-90> apply
  db only
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * DPF                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Db2 version 11.1m4fp6                             *
  ****************************************************************
  

Problem conclusion

• The problem was first fixed in Db2 version 11.1m4fp6
  

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