IZ37742: RUNSTATS WITH TABLESAMPLE OPTION ENTERS INFINITE AND UN-INTERRUPTABLE LOOP, INCURRING CPU CYCLES.

DB2 Version 9.1 Fix Pack 7  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

APAR status

• Closed as program error.

Error description

• Runstats with TABLESAMPLE option enters infinite and
  un-interruptable loop, incurring CPU cycles.   The runstats may
  be a user initiated RUNSTATS.   The runstats may also be a
  runstats initiated by automatic statistics collection during its
  analysis phase.   Automatic statistics collection is enabled
  when all three database configuration parameters AUTO_MAINT,
  AUTO_TBL_MAINT, and AUTO_RUNSTATS are set ON.
  
  db2pd -stack for the db2agent pid will show a call stack of:
  __1cbBsqldStatSamplingInvCardenas6Fdlpll_i_(0x0,0xbba5,0x2c947b2
  80,0xea99115,0x1,0x2c947b0d8) + 0xc4
  __1cbBsqldStatSamplingAdjustColcd6FpnKSQdDLD_TSTAT_lplpnJsqld_cc
  wa__i_(0x2c947af80,0x100000000,0x2c947b280,0x102c70020,0x3c6c00,
  0x1003c6c00) + 0xe4
  __1cbDsqldStatSamplingFinalizeColcd6FpnKSQdDLD_TSTAT__i_(0x2c947
  af80,0x0,0xea991,0x3,0x1,0x66) + 0x70__1cR
  sqlrLocalRunstats6FpnIsqlrr_cb_pnLsqlrrstring_33CHHppnIsqlrg_pd_
  iiiipnQsqlrl_stats_cols_pphIppHIdIHppnUsqlr_runstats_result_9AHH
  HkII_i_(0xe
  
  For the problem to occur, the particular table involved must be
  empty after the runstats command starts execution.  It is likely
  that re-trying the operation will succeed if the table contains
  rows.
  

Local fix

• To avoid the problem if doing user-initiated runstats, do it at
  a time when the table is not empty.
  If the problem is experienced, attempt to terminate the db2agent
  pid via DB2 or the OS.  As a last resort, DB2 may need to be
  restarted after ensuring all processes and resources are clean.
  

Problem summary

• User Affected: ALL
  Problem Description:
  RUNSTATS WITH TABLESAMPLE OPTION ENTERS INFINITE AND
  UN-INTERRUPTABLE LOOP, INCURRING CPU CYCLES.
  Problem Summary:
  Runstats with TABLESAMPLE option enters infinite and
  un-interruptable loop, incurring CPU cycles.   The runstats may
  be a user initiated RUNSTATS.   The runstats may also be a
  runstats initiated by automatic statistics collection during its
  analysis phase.   Automatic statistics collection is enabled
  when all three database configuration parameters AUTO_MAINT,
  AUTO_TBL_MAINT, and AUTO_RUNSTATS are set ON.
  
  db2pd -stack for the db2agent pid will show a call stack of:
  __1cbBsqldStatSamplingInvCardenas6Fdlpll_i_(0x0,0xbba5,0x2c947b2
  80,0xea99115,0x1,0x2c947b0d8) + 0xc4
  __1cbBsqldStatSamplingAdjustColcd6FpnKSQdDLD_TSTAT_lplpnJsqld_cc
  wa__i_(0x2c947af80,0x100000000,0x2c947b280,0x102c70020,0x3c6c00,
  0x1003c6c00) + 0xe4
  __1cbDsqldStatSamplingFinalizeColcd6FpnKSQdDLD_TSTAT__i_(0x2c947
  af80,0x0,0xea991,0x3,0x1,0x66) + 0x70__1cR
  sqlrLocalRunstats6FpnIsqlrr_cb_pnLsqlrrstring_33CHHppnIsqlrg_pd
  iiiipnQsqlrl_stats_cols_pphIppHIdIHppnUsqlr_runstats_result_9AHH
  HkII_i_(0xe
  
  For the problem to occur, the particular table involved must be
  empty after the runstats command starts execution.  It is likely
  that re-trying the operation will succeed if the table contains
  rows.
  

Problem conclusion

• Problem was first fixed in Version 9.1 Fix Pack 7
  

Temporary fix

• To avoid the problem if doing user-initiated runstats, do it at
  a time when the table is not empty.
  If the problem is experienced, attempt to terminate the db2agent
  pid via DB2 or the OS.  As a last resort, DB2 may need to be
  restarted after ensuring all processes and resources are clean.
  

Comments

APAR Information

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

  IZ36843

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

Fix information

• Fixed component name

  DB2 UDB ESE SOL

• Fixed component ID

  5765F4102

Applicable component levels

• R910 PSY

     UP