• Admin-Scout-small-Banner
           
    CURSOR Admin-Scout
    get the ultimate tool for Informix
    pfeil  
invispix 10x10
invispix 10x10
Latest versionsfixlist
14.10.xC5 FixList
12.10.xC14.X5 FixList
11.70.xC9.XB FixList
11.50.xC9.X2 FixList
11.10.xC3.W5 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26
Question in the chat LiveZilla Live Help

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
not known / see Local fix
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)
invispix 10x10
invispix 10x10
invispix 10x10
Technical InfoTechnical Info

Technical Info

DocumentationDocumentation

Documentation

IBM NewsletterIBM Newsletter

IBM Newsletter

Current BugsCurrent Bugs

Current Bugs

Bug ResearchBug Research

Bug Research

Bug FixlistsBug Fixlists

Bug Fixlists

Release NotesRelease Notes

Release Notes

Machine NotesMachine Notes

Machine Notes

Release NewsRelease News

Release News

Product LifecycleProduct Lifecycle

Lifecycle

Media DownloadMedia Download

Media Download

invispix 10x10
Service und SupportService und Support

Service and Support

Informix MonitoringInformix Monitoring

Informix Monitoring

Admin-Scout forInformixAdmin-Scout forInformix

Admin-Scout for Informix

Informix News & InfosInformix News & Infos

Informix News & Infos

This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.