IT31709: SESSION MEMORY GROWTH IN RALLOC WHEN RUNNING INSERTS WITH CASE EXPR IN A LOOP INSIDE OF A SP/TRIGGER ENVIRONMENT

APAR status

• Closed as program error.

Error description

• After a migration from 12.10.FC10 to 12.10.FC13, a stored
  procedure that simply reads data from an external source, does
  some evaluation and performs an insert like this:
  
              INSERT INTO <tablename> (a_acc_sid, a_agr_sid,
  a_cust_sid, a_acc_nm, a_acc_opn_dt, a_acc_opn_rsn, a_acc_cls_dt,
  a_acc_cls_rsn, a_crst_cd, a_bil_ctry_cd, a_allw_arch_flg,
  a_accno, a_own_ctry_cd, a_del_flg, a_own_srv_area_cd,
  a_acc_sgm_ty, a_skp_wh_prcs, a_prt_own_labl, a_ib_pod_flg,
  a_ob_pod_flg, a_acc_st, a_acc_act_st, a_acc_crdt_st,
  a_crdt_stp_dt, a_crdt_stp_rsn, a_prnt_chld_cd,
  a_on_wtch_lst_flg, a_cr_usr, a_cr_dtm, a_upd_usr, a_upd_dtm,
  a_version, a_source, a_frg_ctry_cd, a_frg_dat_st,a_srva_fc_cd)
  
              VALUES (p_a_acc_sid, p_a_agr_sid, p_a_cust_sid,
  v_acc_nm, p_acc_accnt_opn_dt, p_acc_accnt_opn_rsn,
  p_acc_accnt_cls_dt, (case when p_acc_accnt_cls_dt is null then
  NULL ELSE p_acc_accnt_cls_rsn END), p_a_crst_cd,  p_cntry_cd,
  p_acc_allow_archv_yn, p_acc_accnt_no, p_acc_ownr_cntry_cd,
  p_acc_flg_arc_yn, p_acc_ownr_srv_area_cd,
                  (CASE WHEN (p_acc_ownr_cntry_cd IN ('ID','MY')
  AND p_acc_sgmnt_ty = 'C' AND p_a_acc_purpose_cd = 'CSH' AND
  p_a_accty_cd = 'GEN') THEN 'O' ELSE p_acc_sgmnt_ty END),
                  'N', 'Y', p_ref_inbd_pod_ind,
  p_ref_otbd_pod_ind, p_a_acc_st, p_active_status,
                  v_acc_crdt_st,
                  (CASE WHEN v_acc_crdt_st = 'S' THEN
                      (CASE WHEN p_acc_accnt_cls_dt IS NOT NULL
  THEN p_acc_accnt_cls_dt ELSE DATE(p_acc_last_mdfy_ts) END)
                  ELSE NULL END),
                  v_crdt_stp_rsn,
                  p_acc_parent_child_cd, 'N',
  p_acc_last_mdfy_user,
                 (CASE WHEN p_acc_creation_dt IS NOT NULL THEN
  p_acc_creation_dt ELSE (CASE WHEN p_acc_accnt_opn_dt IS NOT NULL
  THEN p_acc_accnt_opn_dt ELSE DATE(p_acc_last_mdfy_ts) END) END),
              p_acc_last_mdfy_user, p_acc_last_mdfy_ts, p_version,
  p_source, p_acc_ownr_cntry_cd, p_a_acc_st, p_acc_srva_fc_cd);
  
  ... which is executed thousands of times in the same SPL call,
  results in continuous session ralloc memory pool growth:
  
  
  ralloc         0          9511808        gentcb         0
  4104
  ralloc         0          9912120        gentcb         0
  4104
  ralloc         0          10172288       gentcb         0
  4104
  ralloc         0          10631608       gentcb         0
  4104
  ralloc         0          10957144       gentcb         0
  4168
  ralloc         0          11301208       gentcb         0
  4176
  ralloc         0          11747760       gentcb         0
  4104
  ralloc         0          12069760       gentcb         0
  4176
  ralloc         0          12331904       gentcb         0
  4176
  ralloc         0          12725120       gentcb         0
  4032
  ralloc         0          13075024       gentcb         0
  4104
  ralloc         0          13447512       gentcb         0
  4176
  ralloc         0          13775192       gentcb         0
  4176
  ralloc         0          14165808       gentcb         0
  4104
  ralloc         0          14478992       gentcb         0
  4104
  ralloc         0          14871504       gentcb         0
  4104
  ralloc         0          15220056       gentcb         0
  4104
  ralloc         0          15641320       gentcb         0
  4104
  
  onstat -g afr output indicates the memory allocation comes from:
  
  26fb00000        16384      ralloc       2986   sqalloc.c:1949
  d0dd9000         16384      ralloc       2986   sqalloc.c:1949
  d0cb1000         16384      ralloc       2986   sqalloc.c:1949
  c0b04000         16384      ralloc       2986   sqalloc.c:1949
  bc2a1000         16384      ralloc       2986   sqalloc.c:1949
  4d71f5000        16384      ralloc       2986   sqalloc.c:1949
  d2673000         16384      ralloc       2986   sqalloc.c:1949
  367fae000        16384      ralloc       2986   sqalloc.c:1949
  306bd7000        16384      ralloc       2986   sqalloc.c:1949
  33acfe000        16384      ralloc       2986   sqalloc.c:1949
  27d0fb000        16384      ralloc       2986   sqalloc.c:1949
  bf8d7000         16384      ralloc       2986   sqalloc.c:1949
  bc1ed000         16384      ralloc       2986   sqalloc.c:1949
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of Informix Server prior to 12.10.xC14 and 14.10.xC4.  *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Update to Informix Server 12.10.xC14 or 14.10.xC4 (when      *
  * available).                                                  *
  ****************************************************************
  

Problem conclusion

• Fixed in Informix Server 12.10.xC14 and 14.10.xC4.
  

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