IZ33709: ERROR SQLO_BUFFSMALL CAN BE RETURNED WHEN COMPILING A QUERY

DB2 Version 9.5 Fix Pack 3b for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• When the db2 query optimizer compiles a plan it will evaluate
  values from the packed descriptor / statistics of the table.
  It is possible that the evaulation of some of these values cause
  error SQLO_BUFFSMALL or "Output buffer too small" leading to not
  only the query failing but also the database being marked as
  bad.
  
  A typical db2diag.log entry will show :
  
  FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:600
  MESSAGE : ZRC=0x870F00BC=-2029059908=SQLO_BUFFSMALL "Output
  buffer too small"
  DATA #1 : String, 555 bytes
  Compiler error stack for rc = -2029059908:
  sqlnn_cmpl[390]
  sqlno_exe[900]
  sqlno_planning_phase[200]
  sqlno_planning_scan[400]
  sqlno_scan_utils_qgm[200]
  sqlno_each_qur[400]
  sqlno_top_qtb[100]
  sqlno_each_opr[200]
  sqlno_walk_qun[100]
  sqlno_each_opr[800]
  sqlno_plan_qun[300]
  sqlno_crule_access[100]
  sqlno_crule_FETCH[200]
  sqlno_prop_fetch[90]
  sqlno_cost_fetch[1000]
  sqlno_get_buffer_size[100]
  sqlno_cols_width[125]
  sqlno_global_cols_width[120]
  sqlnq_fcs::need_statistics[140]
  sqlnq_cst::populate_from_c[140]
  sqlno_conv_literal[110]
  sqlno_conv_collate_map[400]
  

Local fix

• A temporary workaround may be to manually clear out the
  statistics values for the affected table by running :
  
  update sysstat.coldist set COLVALUE  = '' where tabname =
  '<tabname>';
  update sysstat.columns set high2key = '', low2key='' where
  tabname = '<tabname>'
  
  However queries will run suboptimal after this.
  

Problem summary

• Users affected : all
  

Problem conclusion

• This problem was first fixed in v9.5 fixpak 3
  

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 UDB ESE SOL

• Fixed component ID

  5765F4102

Applicable component levels

• R910 PSN

     UP

• R950 PSN

     UP