IC78378: ERRORS -9791/-9969 FROM STORED PROCEDURES USING COLLECTION TYPESAS PARAMETERS AND RETURN VALUES

APAR status

• Closed as program error.

Error description

• Stored procedures which are using collection types as parameters
  and/or return types
  might fail with error -9791.
  In some cases also SQL error -9969 is shown in onstat -g ses.
  
  Message in online.log:
  
  11:09:19  Assert Failed: Exception Caught. Type: MT_EX_OS,
  Context: mem
  11:09:19  IBM Informix Dynamic Server Version 11.50.FC9
  11:09:19   Who: Session(63, informix@myhost, 225394,
  70000002047d7d8)
                  Thread(157, sqlexec, 70000002043fb70, 1)
                  File: mtex.c Line: 417
  11:09:19   Action: Please notify IBM Informix Technical Support.
  11:09:19  stack trace for pid 1556576 written to
  /opt/ifx1150FC9/tmp/af.48540df
  11:09:19   See Also: /opt/ifx1150FC9/tmp/af.48540df
  11:09:23  Exception Caught. Type: MT_EX_OS, Context: mem
  11:09:24  (-9791): ERROR: Routine execution trap --
  procname=<mysvl_get_oper_0> procid=391
      reason: mem
  
  Stacks:
  
  11.50.FC9
  
  afstack
  afhandler
  mt_ex_throw_sig
  afsig_handler
  loadtypeinfo
  lddecimal_65_20
  valcol
  valload
  udrlm_spl_execute
  udrlm_exec_routine
  udr_execute
  exroutine
  geval
  ev_geval
  new_eval
  ip_evalexpr
  runproc
  ...
  
  11.50.FC8W2
  
  afstack
  afhandler
  mt_ex_throw_sig
  afsig_handler
  loadtypeinfo
  0x700000022e45c48
  valcol
  valload
  valload_lcoll
  ip_sqpushval
  ip_sqpushval
  ip_evalexpr
  runproc
  udrlm_spl_execute
  udrlm_exec_routine
  udr_execute
  exroutine
  geval
  ev_geval
  new_eval
  ip_evalexpr
  runproc
  ...
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of SPLs with multiple return expressions, where        *
  * casting is needed.                                           *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Customer is using SPLs where multiple expressions are        *
  * returned. Some of these expressions are untyped collections  *
  * for which there are no casts. However, server ignores this,  *
  * and allows the procedures to be executed. This results in    *
  * the eventual corruption of the data dictionary entries for   *
  * collection transient types.                                  *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to IDS 11.50.xC10 or later                           *
  ****************************************************************
  

Problem conclusion

• Solution is for server to perform implicit casting for all
  expressions in the return statement of SPLs  (not just the first
  expressions as before). If casting is not possible, for any of
  the expressions in the return statement, server returns error
  during prepare of the the statement within the SPL (at runtime).
  

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

  INFORMIX SERVER

• Fixed component ID

  5725A3900

Applicable component levels

• RB50 PSY

     UP