IC63322: UDPATE STATISTICS HIGH ON FUNCTIONAL INDEX RESULTS IN ASSERT FAILED

APAR status

• Closed as program error.

Error description

• An UPDATE STATISTICS HIGH on a functional index results in an
  Assert Failed. The data type of the return value of the function
  is varchar(43).
  
  Definition of the table:
  
  create table testtab(
  f1 integer,
  f2 varchar(12));
  create index testtab_i1 on testtab (f1, mylower(f2));
  
  Definition of the function:
  create function mylower(arg1 varchar(12)) returning varchar(43);
  
  Update Statistics high:
  update statistics high for table testtab (f1);
  
  Stack of Assert Failed
  
  0x08931650 (oninit)afstack
  0x08934bb3 (oninit)mt_ex_throw_sig
  0x089069ee (oninit)afsig_handler
  0x0830aba0 (oninit)uswrcdist
  0x0830cba2 (oninit)usbldist
  0x0830eaa2 (oninit)do_distrib
  0x08310135 (oninit)ustable
  0x0831041f (oninit)aud_ustable
  0x08311422 (oninit)ustatistics
  0x084638ae (oninit)excommand
  0x083a5a2b (oninit)sq_execute
  0x0841a09a (oninit)sqmain
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * IDS users running update statistics high on a SINGLE column  *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * If Update Statistics high is run on a SINGLE column and      *
  * there exists a composite index on the column as a leading    *
  * key and the secondary keys of the index are functional keys  *
  * where a function takes another column as input and returns   *
  * an output, the output of the function should be of a larger  *
  * size than the input column. In this case, when the composite *
  * index is chosen to read the leading column values, it can    *
  * cause corruption in shared memory and a AF results.          *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to IDS 11.50xC6 or higher.                           *
  ****************************************************************
  

Problem conclusion

• The problem has been fixed in IDS 11.50xC6 onwards. Update
  statistics on a single column wiil not choose an index with
  functional keys.
  

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

  IBM IDS ENTRP E

• Fixed component ID

  5724L2304

Applicable component levels

• RB15 PSN

     UP