[DB2-L] Single or cursor - which is better?

PUSCH Othmar

[DB2-L] Single or cursor - which is better?
Hi dear Martin ! Info only to CA's 'Coolgen' ... some different product-names, of the same software, in this story:

JMA (JamesMArtin) --> IEF --> Composer --> Cool:Gen --> Advantage:Gen --> Allfusion:Gen --> (now !) CA:Gen ... *g*

PS: We run, right now with no problems, in our shop's CA's "CA:Gen/7.6.1" with all it's nice "additional IT-stuff" (DB2, Cobol, Java, PL/1, Assembler, CTS/CICS, Proxy, WEB, IP-Listener, Assembler, LDAP-Server, IMS; ... etc.,etc.,etc.) *gg*

Kinds and sincerely yours / Othmar :-)
Kollegiale & freundliche Grüße / Othmar E.R. PUSCH sen. (Tel.: ++43/(0)1-21717_(79-)59739)
Senior-Systemsprogrammer & Betriebsrat der Unicredit Bank Austria AG

Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Martin Hubel
Gesendet: Dienstag, 12. Jänner 2010 21:07
An: [login to unmask email]
Betreff: Re: [DB2-L] Single or cursor - which is better?

I agree with Roger; a singleton Select is best, but you need to make sure you only get zero or one rows returned.

It brings back memories of IEF, now Coolgen, that used to blindly issue a singleton select for every query. If it received a -811, it would then use a cursor. Ugh.

--Martin

>> On the whole, I would have to go with the singleton select, because it
>> only involves one transit to DB2. From an overall performance
>> perspective, however, the difference betweent he two is probably
>> insignificant when compared to the importance assuring an optimal access
>> path, only returning the desired columns, etc. Obviously, the program
>> would have to accommodate the -811 SQL code, if that was a valid
>> possibility.

>> Roger Hecq
>> MF IB USA DB Support
>> 203-719-0492 / 19-337-0492

>> -----Original Message-----
>> From: IDUG DB2-L [mailto:[login to unmask email]<mailto:[login to unmask email]> On Behalf Of Sheldon Rich
>> Sent: Tuesday, January 12, 2010 11:00 AM
>> To: [login to unmask email]<mailto:[login to unmask email]>
>> Subject: [DB2-L] Single or cursor - which is better?

>> We are undergoing an overall DB2 performance review which has led to
>> some interesting questions. What is most efficient way to "select" a
>> single row from within an application program. Is it better to create a
>> cursor and Open, Fetch, and Close OR is it better to do a singleton
>> select?

>> The singleton select has fewer exec-sqls calls, but it must check for a
>> second row to decide if an sqlcode of -811 is needed.

>> So - which choice is more efficient. If you have a clue help us decide.

>> Thank you all,

>> Sheldon Rich
>> Bank Tfahot
>> [login to unmask email]<mailto:[login to unmask email]>

>> _____________________________________________________________________

>> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
>> http://IDUG.ORG/NA *
>> _____________________________________________________________________

>> http://www.idug.org/db2-videos.html has hundreds of video presentations!
>> Did you miss out on attending an IDUG conference?
>> Many of the presentations were recorded and are available on our
>> website!
>> _____________________________________________________________________

>> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
>> is the home of IDUG's DB2-L
>> Visit our website at http://www.ubs.com

>> This message contains confidential information and is intended only
>> for the individual named. If you are not the named addressee you
>> should not disseminate, distribute or copy this e-mail. Please
>> notify the sender immediately by e-mail if you have received this
>> e-mail by mistake and delete this e-mail from your system.
>>
>> E-mails are not encrypted and cannot be guaranteed to be secure or
>> error-free as information could be intercepted, corrupted, lost,
>> destroyed, arrive late or incomplete, or contain viruses. The sender
>> therefore does not accept liability for any errors or omissions in the
>> contents of this message which arise as a result of e-mail transmission.
>> If verification is required please request a hard-copy version. This
>> message is provided for informational purposes and should not be
>> construed as a solicitation or offer to buy or sell any securities
>> or related financial instruments.

>>
>> UBS reserves the right to retain all messages. Messages are protected
>> and accessed only in legally justified cases.

>> _____________________________________________________________________

>> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
>> http://IDUG.ORG/NA *
>> _____________________________________________________________________

>> http://www.idug.org/db2-videos.html has hundreds of video presentations!
>> Did you miss out on attending an IDUG conference?
>> Many of the presentations were recorded and are available on our website!
>> _____________________________________________________________________

>> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
>> the home of IDUG's DB2-L






====================
Martin Hubel
MHC Inc.
[login to unmask email]<mailto:[login to unmask email]>
+1 905-764-7498
+1 416-670-7498 Mobile
Skype: db2hubel
Yahoo IM: db2hubel

Charter Member - IBM Gold Consultant Program
IBM Information Champion

Fight the Right Fires - Let us show you how
====================

________________________________

[cid:[login to unmask email] ] < http://www.idug.org/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

ron thomas

DB2 CURSOR
(in response to PUSCH Othmar)
Hi,

I have a table in which the primary key is store no, orderid and another field
order entry which is of time stamp , i need to download all the records from
the table which is 2 yrs old, in the cursor i have defined as below

select col1, col1.. from table1 where order_entry <= Current timestamp - 2
year,

Here in this case if we want to do the restart processing do we need to
include one of the primary keys in the query? appreciate any help in this

Regards
Ron

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Jack Campbell

Re: DB2 CURSOR
(in response to ron thomas)
Ron,

It is not clear if you are using an UNLOAD utiltiy or a program to perform the
download........ As this is a select it can't really enable restart processing.

Instead select smaller groups of data to reduce the re-run time if any part
fails. To do this you will need to apply a little more control over the select SQl

Your SQl:
select col1, col1.. from table1 where order_entry <= Current timestamp - 2
years

The SQL Does not exert an control over the sequence of the rows selected,
nor provide for a restart.


Instead....A couple of options you might consider:

1) Select rows in smaller increments (to reduce the recovery period)
for example select rows > 5 years , then > 4 years, then > 3 years.....etc
Use "Between" to ensure you select rows for an exclusive year

2) Select where order_entry > 2 years old and store between ? and ?
*then repeat the SQL for additional "store no ranges"

regards

Jack

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

ron thomas

Re: DB2 CURSOR
(in response to Jack Campbell)
Thanks Jask for the suggestion, this i am doing thru program & will go with
suggestion 2.

Regards
Ron

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Peter Vanroose

Re: DB2 CURSOR
(in response to ron thomas)
On Mon, 18 Jan 2010 17:13:34 +0000, Ron Thomas <[login to unmask email]> wrote:
> I have a table in which the primary key is store no, orderid and another
field
> order entry which is of time stamp , i need to download all the records from
>the table which is 2 yrs old, in the cursor i have defined as below
>
>select col1, col1.. from table1 where order_entry <= Current timestamp - 2
>year,
>
>Here in this case if we want to do the restart processing do we need to
>include one of the primary keys in the query? appreciate any help in this


The typical way to make this kind of program restartable is the following:
(I'm assuming here that no two timestamps are identical, and that you want
to write all entries which are at least 2 years old into an external file.)

* Add an "ORDER BY order_entry" to the SELECT statement (a "declare cursor")
* Add an "AND order_entry >= :LAST_SEEN" to the SELECT's WHERE condition
* Add "WITH HOLD" to the cursor declaration
* In the FETCH iteration, foresee regular COMMIT points; just before such a
COMMIT, write the last seen value of order_entry into a 1-row table.
* Add restart logic at the beginning of your program: read the 1-row table
into the host variable LAST_SEEN.
* Add restart logic at the (normal) end of your program: empty the 1-row
table, or write the timestamp '0001-01-01' into it.

Now your program has become restartable, i.e.: when it is abended somewhere
half-way, and then restarted, it will start where it left off instead of
start reading from the beginning of the table.
(Of course, you must make sure to append to the output file, not overwrite
it, in case of a restart.)

So the short answer to your question is: NO.

-- Peter Vanroose,
ABIS Training & Consulting.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Troy L Coleman

Re: Single or cursor - which is better?
(in response to Peter Vanroose)
Hi Sheldon,
If you know you are going to always have 1 row returned then use SELECT INTO.
If you are doing an existence check then you can use "FETCH FIRST 1 ROW ONLY" to avoid error checking.
If you don't know what the result set is going to be. It could be one or more then use the declare/open/close cursor option.


-Troy


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sheldon Rich
Sent: Tuesday, January 12, 2010 10:00 AM
To: [login to unmask email]
Subject: [DB2-L] Single or cursor - which is better?

We are undergoing an overall DB2 performance review which has led to
some interesting questions. What is most efficient way to "select" a single
row from within an application program. Is it better to create a cursor and
Open, Fetch, and Close OR is it better to do a singleton select?

The singleton select has fewer exec-sqls calls, but it must check for a second
row to decide if an sqlcode of -811 is needed.

So - which choice is more efficient. If you have a clue help us decide.

Thank you all,

Sheldon Rich
Bank Tfahot
[login to unmask email]

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L