Informix - Problem description
Problem IT31709 | Status: Closed |
SESSION MEMORY GROWTH IN RALLOC WHEN RUNNING INSERTS WITH CASE EXPR IN A LOOP INSIDE OF A SP/TRIGGER ENVIRONMENT | |
product: | |
INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10 | |
Problem 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 (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 | |
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). * **************************************************************** | |
Local Fix: | |
Solution | |
Workaround | |
**************************************************************** * 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). * **************************************************************** | |
Comment | |
Fixed in Informix Server 12.10.xC14 and 14.10.xC4. | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 31.01.2020 04.03.2020 04.03.2020 |
Problem solved at the following versions (IBM BugInfos) | |
12.10.xC14, 14.10.xC4 | |
Problem solved according to the fixlist(s) of the following version(s) |