MIME-Version: 1.0
References: <CANoyZGr5nSNGmWHT1bWY+F_k20WnvOhnKcZk0uq55GEYBAeRgA@mail.gmail.com>
In-Reply-To: <CANoyZGr5nSNGmWHT1bWY+F_k20WnvOhnKcZk0uq55GEYBAeRgA@mail.gmail.com>
From: Juan Rodrigo Alejandro Burgos Mella <rodrigoburgosmella@gmail.com>
Date: Tue, 9 Dec 2025 15:27:41 -0500
Message-ID: <CAHbZ42zwfodkVVHRU_4C4xOVh_hr1Pw0w4FueXj=srEZXwn=ag@mail.gmail.com>
Subject: Re: Ecpg fetch issue
To: Tzuriel Kahlon <tzuriel1986@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000000cb5d406458abfee"
Archived-At: <https://www.postgresql.org/message-id/CAHbZ42zwfodkVVHRU_4C4xOVh_hr1Pw0w4FueXj%3DsrEZXwn%3Dag%40mail.gmail.com>
Precedence: bulk

--0000000000000cb5d406458abfee
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi
When using ECPG, if you have a simple FETCH, it will do it row by row; the
way to download more than one row at a time is by using data arrays.
In my case, when I want to extract, for example, 100 records at once, I use
the following model:

I define a data array:

EXEC SQL BEGIN DECLARE SECTION;
struct _resultData {
int  int_data1;
char chr_data2[11];
} resultData[100];
EXEC SQL END DECLARE SECTION;

and then in the fetch:

EXEC SQL
FETCH FORWARD 100 cCursor
INTO :resultData;

and the variable sqlca.sqlerrd[2] controls the number of records returned

I hope it helps you

Atte
JRBM


El mar, 9 dic 2025 a las 14:53, Tzuriel Kahlon (<tzuriel1986@gmail.com>)
escribi=C3=B3:

> Hey all ,
> We are using IBM for cobol and connextint postgres db with ecpg the
> precompiler , problem is when we running cursor against db the prefetch a=
re
> not set correctly and provide one row each time he approach to db and thi=
s
> decrease the performance significantly if twvle have 100000 rows it take =
it
> one by one each run .
> In procob which is the precompiler of Oracle there are option to set the
> prefetch to take in each time it approach the db x number of rows you wan=
t
> and this ease the db retrieved and enhance the performance .
> I know that there is a way to change the setting in the application code,
> but I want to eliminate touch any code and configure only the ecpg which =
is
> the middle between cobol and db.
> There is anyone from you which encountered same issue?
>
>
>
>
>
> *=D7=91=D7=91=D7=A8=D7=9B=D7=94 *
> *=D7=A6=D7=95=D7=A8=D7=99=D7=90=D7=9C =D7=9B=D7=97=D7=9C=D7=95=D7=9F *
> 0523851143
>

--0000000000000cb5d406458abfee
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi</div><div>When using ECPG, if you have a simple FE=
TCH, it will do it row by row; the way to download more than one row at a t=
ime is by using data arrays.</div><div></div><div>In my case, when I want t=
o extract, for example, 100 records at once, I use the following model:</di=
v><div><br></div><div>I define a data array:</div><div><br></div><div>EXEC =
SQL BEGIN DECLARE SECTION;<br>struct _resultData {<br>	int =C2=A0int_data1;=
<br>	char chr_data2[11];<br>} resultData[100];<br>EXEC SQL END DECLARE SECT=
ION;<br><br></div><div>and then in the fetch:</div><div><br></div><div>	EXE=
C SQL<br>	FETCH FORWARD 100 cCursor<br>	INTO :resultData;<br><br></div><div=
>and the variable sqlca.sqlerrd[2] controls the number of records returned<=
/div><div><br></div><div>I hope it helps you</div><div><br></div><div>Atte<=
/div><div>JRBM</div><div><br></div></div><br><div class=3D"gmail_quote gmai=
l_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">El mar, 9 dic 2025=
 a las 14:53, Tzuriel Kahlon (&lt;<a href=3D"mailto:tzuriel1986@gmail.com">=
tzuriel1986@gmail.com</a>&gt;) escribi=C3=B3:<br></div><blockquote class=3D=
"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(2=
04,204,204);padding-left:1ex"><div dir=3D"auto"><div>Hey all ,</div><div di=
r=3D"auto">We are using IBM for cobol and connextint postgres db with ecpg =
the precompiler , problem is when we running cursor against db the prefetch=
 are not set correctly and provide one row each time he approach to db and =
this decrease the performance significantly if twvle have 100000 rows it ta=
ke it one by one each run .</div><div dir=3D"auto">In procob which is the p=
recompiler of Oracle there are option to set the prefetch to take in each t=
ime it approach the db x number of rows you want and this ease the db retri=
eved and enhance the performance .</div><div dir=3D"auto">I know that there=
 is a way to change the setting in the application code, but I want to elim=
inate touch any code and configure only the ecpg which is the middle betwee=
n cobol and db.</div><div dir=3D"auto">There is anyone from you which encou=
ntered same issue?</div><div dir=3D"auto"><br></div><div><br></div><div><di=
v dir=3D"rtl"><br><div><br></div><div><br></div><div><i>=D7=91=D7=91=D7=A8=
=D7=9B=D7=94=C2=A0</i></div><div><b>=D7=A6=D7=95=D7=A8=D7=99=D7=90=D7=9C =
=D7=9B=D7=97=D7=9C=D7=95=D7=9F=C2=A0</b></div><div>0523851143</div></div></=
div></div>
</blockquote></div>

--0000000000000cb5d406458abfee--