Discussion:
insert cursors
(too old to reply)
Randall Young (ADE)
2013-02-12 15:26:26 UTC
Permalink
I have had some good experience with insert cursors in esql/c. The documentation seems to say that I cannot use this feature in 4GL. Is there some analogous way to speed up inserts in 4GL?

IBM INFORMIX-4GL Version 7.50.FC5
DB-Access Version 11.50.FC5

Thanks.

--
Randall Young
***@arkansas.gov<mailto:***@arkansas.gov>

"Blessed are we who can laugh at our surroundings for we shall never be considered completely sane."
Art Kagel
2013-02-12 16:00:07 UTC
Permalink
It's not so much that it cannot be used in 4GL, just that there is no 4GL
syntax support for INSERT or PUT cursors. However, note that 4GL compiles
down to ESQL/C so the library support is there, you just have to write the
function or functions that implement the insert cursor using ESQL/C and
call it/them from 4GL. You could either write the entire insert processing
module in ESQL/C or you could just package up the PREPARE, DECLARE, & OPEN,
PUT, and FLUSH & CLOSE calls in separate 4GL callable ESQL/C functions.
Since ESQL/C supports PREPARE and DESCRIBE and also supports naming
prepared statements and cursors using string variables you could actually
write a generic library of using PUT cursors in 4GL that handles partial
transactions, etc.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.


On Tue, Feb 12, 2013 at 10:26 AM, Randall Young (ADE) <
Post by Randall Young (ADE)
I have had some good experience with insert cursors in esql/c. The
documentation seems to say that I cannot use this feature in 4GL. Is there
some analogous way to speed up inserts in 4GL? ****
** **
IBM INFORMIX-4GL Version 7.50.FC5****
DB-Access Version 11.50.FC5****
** **
Thanks.****
** **
--****
Randall Young****
** **
“Blessed are we who can laugh at our surroundings for we shall never be
considered completely sane.”****
** **
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list
Jonathan Leffler
2013-02-12 16:53:33 UTC
Permalink
On Tue, Feb 12, 2013 at 7:26 AM, Randall Young (ADE) <
Post by Randall Young (ADE)
I have had some good experience with insert cursors in esql/c. The
documentation seems to say that I cannot use this feature in 4GL. Is there
some analogous way to speed up inserts in 4GL? ****
** **
IBM INFORMIX-4GL Version 7.50.FC5****
DB-Access Version 11.50.FC5****
INSERT cursors were added to I4GL at the time they were introduced in
ESQL/C, way back in the distant mists of time (circa 1988). They should
still be supported. Any documentation to the contrary needs to be
reported, please, so we can get it fixed.

--
Jonathan Leffler <***@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2013.0118 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
Randall Young (ADE)
2013-02-12 20:54:53 UTC
Permalink
Thanks for your prompt response. I did manage to get the insert cursor working under 4GL. Unfortunately, it didn't seem to increase the speed on the insert. The last time I tried this method was in late 1990s. Perhaps the hardware and the storage devices are much faster these days. Thanks for you help.

--
Randall Young
***@arkansas.gov<mailto:***@arkansas.gov>

"Blessed are we who can laugh at our surroundings for we shall never be considered completely sane."

From: informix-list-***@iiug.org [mailto:informix-list-***@iiug.org] On Behalf Of Jonathan Leffler
Sent: Tuesday, February 12, 2013 10:54 AM
To: Randall Young (ADE)
Cc: Informix List - IIUG
Subject: Re: insert cursors


On Tue, Feb 12, 2013 at 7:26 AM, Randall Young (ADE) <***@arkansas.gov<mailto:***@arkansas.gov>> wrote:
I have had some good experience with insert cursors in esql/c. The documentation seems to say that I cannot use this feature in 4GL. Is there some analogous way to speed up inserts in 4GL?

IBM INFORMIX-4GL Version 7.50.FC5
DB-Access Version 11.50.FC5


INSERT cursors were added to I4GL at the time they were introduced in ESQL/C, way back in the distant mists of time (circa 1988). They should still be supported. Any documentation to the contrary needs to be reported, please, so we can get it fixed.

--
Jonathan Leffler <***@gmail.com<mailto:***@gmail.com>> #include <disclaimer.h>
Guardian of DBD::Informix - v2013.0118 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be amused."
John Miller iii
2013-02-12 21:00:04 UTC
Permalink
You can look at setting the FET_BUF_SIZE environment variable before using
the cursor (or starting you application) as it
will increase the cursor buffer size. Depending on your version 32K or
2GB is the maximum size.


John F. Miller III
STSM, Embedability Architect
***@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
Date: 02/12/2013 12:56 PM
Subject: RE: insert cursors
Thanks for your prompt response. I did manage to get the insert
cursor working under 4GL. Unfortunately, it didn’t seem to increase
the speed on the insert. The last time I tried this method was in
late 1990s. Perhaps the hardware and the storage devices are much
faster these days. Thanks for you help.
--
Randall Young
“Blessed are we who can laugh at our surroundings for we shall never
be considered completely sane.”
On Behalf Of Jonathan Leffler
Sent: Tuesday, February 12, 2013 10:54 AM
To: Randall Young (ADE)
Cc: Informix List - IIUG
Subject: Re: insert cursors
On Tue, Feb 12, 2013 at 7:26 AM, Randall Young (ADE) <
I have had some good experience with insert cursors in esql/c. The
documentation seems to say that I cannot use this feature in 4GL.
Is there some analogous way to speed up inserts in 4GL?
IBM INFORMIX-4GL Version 7.50.FC5
DB-Access Version 11.50.FC5
INSERT cursors were added to I4GL at the time they were introduced
in ESQL/C, way back in the distant mists of time (circa 1988). They
should still be supported. Any documentation to the contrary needs
to be reported, please, so we can get it fixed.
--
Guardian of DBD::Informix - v2013.0118 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease
to be amused." _______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list
Art Kagel
2013-02-12 21:01:45 UTC
Permalink
One thing that makes a big difference when using insert cursors is to
increase the size of the buffer that the cursor uses. You can do this by
exporting the environment variable FET_BUF_SIZE in the task's environment
and setting it to a larger buffer. The default is 4096 and the maximum is
65530. I am assuming that you are not forcing a flush of the cursor after
each PUT but are allowing the cursor to flush itself only when the buffer
fills.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.


On Tue, Feb 12, 2013 at 3:54 PM, Randall Young (ADE) <
Post by Randall Young (ADE)
Thanks for your prompt response. I did manage to get the insert cursor
working under 4GL. Unfortunately, it didn’t seem to increase the speed on
the insert. The last time I tried this method was in late 1990s. Perhaps
the hardware and the storage devices are much faster these days. Thanks
for you help.****
** **
--****
Randall Young****
** **
“Blessed are we who can laugh at our surroundings for we shall never be
considered completely sane.”****
** **
*Sent:* Tuesday, February 12, 2013 10:54 AM
*To:* Randall Young (ADE)
*Cc:* Informix List - IIUG
*Subject:* Re: insert cursors****
** **
** **
On Tue, Feb 12, 2013 at 7:26 AM, Randall Young (ADE) <
I have had some good experience with insert cursors in esql/c. The
documentation seems to say that I cannot use this feature in 4GL. Is there
some analogous way to speed up inserts in 4GL? ****
****
IBM INFORMIX-4GL Version 7.50.FC5****
DB-Access Version 11.50.FC5****
INSERT cursors were added to I4GL at the time they were introduced in
ESQL/C, way back in the distant mists of time (circa 1988). They should
still be supported. Any documentation to the contrary needs to be
reported, please, so we can get it fixed.
-- ****
Guardian of DBD::Informix - v2013.0118 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused." ****
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list
Jonathan Leffler
2013-02-12 21:06:26 UTC
Permalink
On Tue, Feb 12, 2013 at 12:54 PM, Randall Young (ADE) <
Post by Randall Young (ADE)
Thanks for your prompt response. I did manage to get the insert cursor
working under 4GL. Unfortunately, it didn’t seem to increase the speed on
the insert. The last time I tried this method was in late 1990s. Perhaps
the hardware and the storage devices are much faster these days.
The speed-up available depends on a variety of factors. One is the buffer
sizes used (FET_BUF_SIZE etc); another is the size of the rows you're
inserting; another is the distance of the Informix data server from the
client application (the longer the network connection, the more benefit you
get); another is the way you use the cursor (using FLUSH after each PUT
partially defeats the benefit of using an insert cursor at all). Data
types can affect this too; if there are blobs involved, then the insert
cursor operates one row at a time, IIRC.
Post by Randall Young (ADE)
*Sent:* Tuesday, February 12, 2013 10:54 AM
*Subject:* Re: insert cursors
** **
** **
On Tue, Feb 12, 2013 at 7:26 AM, Randall Young (ADE) <
I have had some good experience with insert cursors in esql/c. The
documentation seems to say that I cannot use this feature in 4GL. Is there
some analogous way to speed up inserts in 4GL? ****
****
IBM INFORMIX-4GL Version 7.50.FC5****
DB-Access Version 11.50.FC5****
INSERT cursors were added to I4GL at the time they were introduced in
ESQL/C, way back in the distant mists of time (circa 1988). They should
still be supported. Any documentation to the contrary needs to be
reported, please, so we can get it fixed.
--
Jonathan Leffler <***@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2013.0118 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
Loading...