IC65352: -392 WITH AF IN COMP_UDR WHEN PREPARED SQL CONTAINING TO_DATE() CONVERSION IS EXECUTED IN EXCESS OF STMT_CACHE_HITS

APAR status

• Closed as program error.

Error description

• An error -392 followed by an AF in comp_udr will be thrown when
  executing a prepared SQL statement that contains a character to
  datetime conversion using the TO_DATE() function.  This error
  will occur if the SQL statement is executed a number of times
  that exceeds the setting of the onconfig parameter
  STMT_CACHE_HITS.  In addition, this only occurs if STMT_CACHE,
  itself, is set to the value 2.
  
  This does not reproduce with a value of STMT_CACHE=0.
  
  This does reproduce with an onconfig value of STMT_CACHE=1, but
  only if the user environment has also either exported
  STMT_CACHE=1 as an environment variable, or issued the "SET
  STATEMENT CACHE ON" statement.  If neither of these has been
  done, the error/AF does not occur.
  
  This has been reproduced on both IDS 10 and 11.  The resulting
  AF will contain output similar to one of the following two
  stacks:
  
  Example stack from IDS 11.50 on HP-UX 11.11
  
  legacy_hp_afstack
  afstack + 0x64
  mt_ex_throw_sig
  afsig_handler
  _sigreturn
  comp_udr
  comp_expr
  comp_binary
  comp_exn/oninit
  comp_and_or
  comp_expr
  comp_case
  comp_expr
  compile_expression
  p3sort
  dogen3
  itbldroot
  ssc_link
  parsecmd
  sqcmd
  sq_cmnd
  sq_prepare
  sqmain
  listen_verify
  spawn_thread
  startup
  resume
  
  
  Example stack from IDS 10 on AIX 5.3
  
  afstack
  mt_ex_throw_sig
  afsig_handler
  comp_udr
  comp_udr
  comp_expr
  comp_binary
  comp_expr
  comp_and_or
  comp_expr
  comp_case
  comp_expr
  comp_binary
  comp_expr
  compile_expression
  p3sort_14_7
  dogen3
  itbldroot
  ssc_link
  parsecmd
  sqcmd
  sq_cmnd
  sq_prepare
  sqmain
  listen_verify
  spawn_thread
  startup
  

Local fix

• If the character to datetime conversions are removed from the
  SQL statement, (i.e. use datetime host variables instead of
  character) the error and AF do not occur.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Statement cache users                                        *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * When statement cache is enabled, certain statements which    *
  * have functions such as to_date() in projection clause and    *
  * also order by or group by clauses can cause a server crash   *
  * if the function arguments have host variables.               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to 11.50.xC7 and above.                              *
  ****************************************************************
  

Problem conclusion

• Fixed in 11.50 xC7.
  The new behavior is that statements described above (in problem
  description) will not be cached in statement cache.
  [ Statements should have functions such as to_date() in
  projection clause and also order by or group by clauses. The
  function should also have
  host variables as arguments. ]
  

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 PSY

     UP