Approximate "break-even" when using Rowset processing

Gary Joehlin

Approximate "break-even" when using Rowset processing
Hello Listers:


Has anyone derived an approximate break-even point, rule of thumb, when considering coding options: a regular cursor (open - fetch loop) and using Rowset processing in a regular batch (read COBOL) program that is not a stored procedure?

I'm sure that there is a 'price' to pay for setting up and calling DB2 using ROWSET processing. But, where (within a range) would that be a better pay off against a regular cursor and fetch-loop logic?

I understand that if you have no clue on the number of rows being returned, you're vulnerable to the variations in the fixed-cost of each method, but you still need to hard-code a number in the program for ROWSET.

I know that it certainly does depend, but I'd like to know that break-even point, there must be some reasonable calculation to consider.

TIA,
Gary Joehlin

_____________________________________________________________________

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

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

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

Dave Nance

Re: Approximate "break-even" when using Rowset processing
(in response to Gary Joehlin)
 Dan Lukesetich(?sp?) from YL&A has some slides that show the savings/costs associated with multi-row fetch on the YL&A website. I think there is even a behind the bar podcast where he and Susan discuss it. If I remember correct, the best savings was right around 100 records.
 
David Nance




________________________________
From: "Joehlin, Gary" <[login to unmask email]>
To: [login to unmask email]
Sent: Mon, November 30, 2009 4:22:04 PM
Subject: [DB2-L] Approximate "break-even" when using Rowset processing


Hello Listers:
 
 
Has anyone derived an approximate break-even point, rule of thumb, when considering coding options: a regular cursor (open – fetch loop) and using Rowset processing in a regular batch (read COBOL) program that is not a stored procedure?
 
I’m sure that there is a ‘price’ to pay for setting up and calling DB2 using ROWSET processing. But, where (within a range) would that be a better pay off against a regular cursor and fetch-loop logic?
 
I understand that if you have no clue on the number of rows being returned, you’re vulnerable to the variations in the fixed-cost of each method, but you still need to hard-code a number in the program for ROWSET.
 
I know that it certainly does depend, but I’d like to know that break-even point, there must be some reasonable calculation to consider.
 
TIA,
Gary Joehlin
________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.




_____________________________________________________________________

* 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

David Simpson

Re: Approximate "break-even" when using Rowset processing
(in response to Dave Nance)
Another thing to keep in mind for this….



People like me hammer home the need to use GET DIAGNOSTICS when doing multi-row fetch & insert. This is true if you want good messages for any errors encountered. HOWEVER, I’ve seen a few people substitute GET DIAGNOSTICS for the normal SQLCODE check at the end of a multi-row operation. This could KILL your performance and drive your break-even point up in the stratosphere. If you perform a multi-row operation and receive an SQLCODE of 0 then you generally have no need for GET DIAGNOSTICS, which is VERY expensive. So, check SQLCODE first and only go to the DIAGNOSTICS when errors are encountered.



My 2¢ (with a nod to Faz for the ¢ sign that I cut and pasted)...



______________________________________________________________________________
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email] <mailto:[login to unmask email]>

For more information about Themis, visit www.themisinc.com < http://www.themisinc.com/ >



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dave Nance
Sent: Tuesday, December 01, 2009 6:08 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing



Dan Lukesetich(?sp?) from YL&A has some slides that show the savings/costs associated with multi-row fetch on the YL&A website. I think there is even a behind the bar podcast where he and Susan discuss it. If I remember correct, the best savings was right around 100 records.


David Nance





________________________________

From: "Joehlin, Gary" <[login to unmask email]>
To: [login to unmask email]
Sent: Mon, November 30, 2009 4:22:04 PM
Subject: [DB2-L] Approximate "break-even" when using Rowset processing

Hello Listers:





Has anyone derived an approximate break-even point, rule of thumb, when considering coding options: a regular cursor (open – fetch loop) and using Rowset processing in a regular batch (read COBOL) program that is not a stored procedure?



I’m sure that there is a ‘price’ to pay for setting up and calling DB2 using ROWSET processing. But, where (within a range) would that be a better pay off against a regular cursor and fetch-loop logic?



I understand that if you have no clue on the number of rows being returned, you’re vulnerable to the variations in the fixed-cost of each method, but you still need to hard-code a number in the program for ROWSET.



I know that it certainly does depend, but I’d like to know that break-even point, there must be some reasonable calculation to consider.



TIA,

Gary Joehlin



________________________________

Richard Fazio

Re: Approximate "break-even" when using Rowset processing
(in response to David Simpson)
Nice!



_____

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of David Simpson
Sent: Tuesday, December 01, 2009 8:45 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing



Another thing to keep in mind for this....



People like me hammer home the need to use GET DIAGNOSTICS when doing multi-row fetch & insert. This is true if you want good messages for any errors encountered. HOWEVER, I've seen a few people substitute GET DIAGNOSTICS for the normal SQLCODE check at the end of a multi-row operation. This could KILL your performance and drive your break-even point up in the stratosphere. If you perform a multi-row operation and receive an SQLCODE of 0 then you generally have no need for GET DIAGNOSTICS, which is VERY expensive. So, check SQLCODE first and only go to the DIAGNOSTICS when errors are encountered.



My 2¢ (with a nod to Faz for the ¢ sign that I cut and pasted)...



cid:[login to unmask email]______________________________________________________________________________
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email]

For more information about Themis, visit www.themisinc.com < http://www.themisinc.com/ >



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dave Nance
Sent: Tuesday, December 01, 2009 6:08 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing



Dan Lukesetich(?sp?) from YL&A has some slides that show the savings/costs associated with multi-row fetch on the YL&A website. I think there is even a behind the bar podcast where he and Susan discuss it. If I remember correct, the best savings was right around 100 records.


David Nance





_____

From: "Joehlin, Gary" <[login to unmask email]>
To: [login to unmask email]
Sent: Mon, November 30, 2009 4:22:04 PM
Subject: [DB2-L] Approximate "break-even" when using Rowset processing

Hello Listers:





Has anyone derived an approximate break-even point, rule of thumb, when considering coding options: a regular cursor (open - fetch loop) and using Rowset processing in a regular batch (read COBOL) program that is not a stored procedure?



I'm sure that there is a 'price' to pay for setting up and calling DB2 using ROWSET processing. But, where (within a range) would that be a better pay off against a regular cursor and fetch-loop logic?



I understand that if you have no clue on the number of rows being returned, you're vulnerable to the variations in the fixed-cost of each method, but you still need to hard-code a number in the program for ROWSET.



I know that it certainly does depend, but I'd like to know that break-even point, there must be some reasonable calculation to consider.



TIA,

Gary Joehlin



_____


_____________________________________________________________________

* 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

Raymond Bell

Re: Approximate "break-even" when using Rowset processing
(in response to Richard Fazio)
Hi Gary,

On 5th November BMC hosted a Webinar/Webex/Web-based presentation in conjunction with YL&A. About 17 minutes into it, Dan Luksetich showed a slide that charted the approx. CPU and elapsed times of a read process with varying number of rows in the rowset - 1, 2, 5, 10, 20, 50 and 100. For his particular test the performance started to tail off about 10 rows per rowset but continued to improve until about 50 when it started to flat-line. Your mileage will almost certainly vary so I'm afraid you'll need to try it out and see.

But you don't have to hard-code the rowset value. The BMC products that can optionally use rowset processing have a parameter (ROWSETSZ) where you can specify the value you want. We currently ship with a default of 100 but it's up to you. In writing your own multi-row fetch code you could parameterize it too. I'm guessing here but I imagine you'd need to have a maximum rowset size and code an array in your program to cater for the max. Then pass the actual value as a parm to your job and test/tweak it as appropriate. Easier said than done, no doubt.

If you want to download the recording, start from BMC.com, select Events, On Demand and look for the one recorded on 5th November entitled, 'Reduce CPU Usage with SMART SQL < http://110509-reducecpuusagewith-smartsql.accessbmc.com/%20 > '. Or click on this link...

Cheers,


Raymond

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Joehlin, Gary
Sent: 30 November 2009 21:22
To: [login to unmask email]
Subject: [DB2-L] Approximate "break-even" when using Rowset processing

Hello Listers:


Has anyone derived an approximate break-even point, rule of thumb, when considering coding options: a regular cursor (open - fetch loop) and using Rowset processing in a regular batch (read COBOL) program that is not a stored procedure?

I'm sure that there is a 'price' to pay for setting up and calling DB2 using ROWSET processing. But, where (within a range) would that be a better pay off against a regular cursor and fetch-loop logic?

I understand that if you have no clue on the number of rows being returned, you're vulnerable to the variations in the fixed-cost of each method, but you still need to hard-code a number in the program for ROWSET.

I know that it certainly does depend, but I'd like to know that break-even point, there must be some reasonable calculation to consider.

TIA,
Gary Joehlin

________________________________

[ http://www.idug.org/images/M_images/idug%20na3.jpg ] < http://www.idug.org/db2-north-america-conference/index.html >

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/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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

Suresh Sane

Re: Approximate "break-even" when using Rowset processing
(in response to Raymond Bell)

Gary,



I presented some early benchmarks on this at IDUG 2006 (session E05). CPU savings can be obtained for as few as 5 rows, but taper off around 50-100.



However, there is a bigger issue here which has not been touched on - especially if you are contemplating retro-fitting into an existing application.



1. The fetch time is generally a tiny fraction of the total time. "Lab cases" by their nature are simplistic - 40% savings in CPU may mean just 0.4% savings since a complex real-life program may spend only 1% of its time in fetch. So beware of any over-selling (I have been guilty of that in the past, as well).



2. Keep in mind the additional program complexity - SQL is easy, the COBOL is not. Having to deal with partial sets, multiple errors via GET DIAGNOSTICS, proper restart etc all add to the programming time. It is not hard, but we tend to ignore it completely.



3. Our experience at DST for retro-fitting has been this: it is worth it only for large volumes of data - e.g. if program intends to fetch say 200 rows, it is generally not economical for us to pay the programming resources - your metrics will be different.



If it can be externally controlled (e.g. DSNTEP4 for dynamic SQL), by all means use it. If you have to implant in an existing program - it may not be all rosy.



Thx

Suresh


Date: Fri, 4 Dec 2009 12:36:57 +0100
From: [login to unmask email]
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing







Hi Gary,

On 5th November BMC hosted a Webinar/Webex/Web-based presentation in conjunction with YL&A. About 17 minutes into it, Dan Luksetich showed a slide that charted the approx. CPU and elapsed times of a read process with varying number of rows in the rowset – 1, 2, 5, 10, 20, 50 and 100. For his particular test the performance started to tail off about 10 rows per rowset but continued to improve until about 50 when it started to flat-line. Your mileage will almost certainly vary so I’m afraid you’ll need to try it out and see.

But you don’t have to hard-code the rowset value. The BMC products that can optionally use rowset processing have a parameter (ROWSETSZ) where you can specify the value you want. We currently ship with a default of 100 but it’s up to you. In writing your own multi-row fetch code you could parameterize it too. I’m guessing here but I imagine you’d need to have a maximum rowset size and code an array in your program to cater for the max. Then pass the actual value as a parm to your job and test/tweak it as appropriate. Easier said than done, no doubt.

If you want to download the recording, start from BMC.com, select Events, On Demand and look for the one recorded on 5th November entitled, ‘Reduce CPU Usage with SMART SQL’. Or click on this link...

Cheers,


Raymond



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Joehlin, Gary
Sent: 30 November 2009 21:22
To: [login to unmask email]
Subject: [DB2-L] Approximate "break-even" when using Rowset processing

Hello Listers:


Has anyone derived an approximate break-even point, rule of thumb, when considering coding options: a regular cursor (open – fetch loop) and using Rowset processing in a regular batch (read COBOL) program that is not a stored procedure?

I’m sure that there is a ‘price’ to pay for setting up and calling DB2 using ROWSET processing. But, where (within a range) would that be a better pay off against a regular cursor and fetch-loop logic?

I understand that if you have no clue on the number of rows being returned, you’re vulnerable to the variations in the fixed-cost of each method, but you still need to hard-code a number in the program for ROWSET.

I know that it certainly does depend, but I’d like to know that break-even point, there must be some reasonable calculation to consider.

TIA,
Gary Joehlin





The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.





The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

_________________________________________________________________
Windows 7: Unclutter your desktop. Learn more.
http://www.microsoft.com/windows/windows-7/videos-tours.aspx?h=7sec&slideid=1&media=aero-shake-7second&listid=1&stop=1&ocid=PID24727::T:WLMTAGL:ON:WL:en-US:WWL_WIN_7secdemo:122009
_____________________________________________________________________

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

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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

Myron Miller

Re: Approximate "break-even" when using Rowset processing
(in response to Suresh Sane)
Amen, brother. The COBOL programming complexity is way beyond what I've seen many COBOL programmers able to handle at my site.

In addition, we had a very very experienced programmer try it in some careful benchmarks. We tried it on several situations where lots of rows were being returned. And in every one of our cases, the result was a net negative difference. The processing and CPU were faster on singleton fetches versus multiple rowsets (ranging from tests on 10 to 200).

Understand that your mileage may vary, these were our carefully benchmarked and documented tests on our data and tables.

As a consequence, we're not using it much at all, especially for fitting into existing programs.

Myron




________________________________
From: Suresh Sane <[login to unmask email]>
To: [login to unmask email]
Sent: Fri, December 4, 2009 12:50:38 PM
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing

Gary,

I presented some early benchmarks on this at IDUG 2006 (session E05). CPU savings can be obtained for as few as 5 rows, but taper off around 50-100.

However, there is a bigger issue here which has not been touched on - especially if you are contemplating retro-fitting into an existing application.

1. The fetch time is generally a tiny fraction of the total time. "Lab cases" by their nature are simplistic - 40% savings in CPU may mean just 0.4% savings since a complex real-life program may spend only 1% of its time in fetch. So beware of any over-selling (I have been guilty of that in the past, as well).

2. Keep in mind the additional program complexity - SQL is easy, the COBOL is not. Having to deal with partial sets, multiple errors via GET DIAGNOSTICS, proper restart etc all add to the programming time. It is not hard, but we tend to ignore it completely.

3. Our experience at DST for retro-fitting has been this: it is worth it only for large volumes of data - e.g. if program intends to fetch say 200 rows, it is generally not economical for us to pay the programming resources - your metrics will be different.

If it can be externally controlled (e.g. DSNTEP4 for dynamic SQL), by all means use it. If you have to implant in an existing program - it may not be all rosy.

Thx
Suresh
________________________________
Date: Fri, 4 Dec 2009 12:36:57 +0100
From: [login to unmask email]
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing


Hi Gary,

On 5th November BMC hosted a Webinar/Webex/Web-based presentation in conjunction with YL&A. About 17 minutes into it, Dan Luksetich showed a slide that charted the approx. CPU and elapsed times of a read process with varying number of rows in the rowset – 1, 2, 5, 10, 20, 50 and 100. For his particular test the performance started to tail off about 10 rows per rowset but continued to improve until about 50 when it started to flat-line. Your mileage will almost certainly vary so I’m afraid you’ll need to try it out and see.

But you don’t have to hard-code the rowset value. The BMC products that can optionally use rowset processing have a parameter (ROWSETSZ) where you can specify the value you want. We currently ship with a default of 100 but it’s up to you. In writing your own multi-row fetch code you could parameterize it too. I’m guessing here but I imagine you’d need to have a maximum rowset size and code an array in your program to cater for the max. Then pass the actual value as a parm to your job and test/tweak it as appropriate. Easier said than done, no doubt.

If you want to download the recording, start from BMC.com, select Events, On Demand and look for the one recorded on 5th November entitled, ‘Reduce CPU Usage with SMART SQL’. Or click on this link...

Cheers,


Raymond

From:IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Joehlin, Gary
Sent: 30 November 2009 21:22
To: [login to unmask email]
Subject: [DB2-L] Approximate "break-even" when using Rowset processing

Hello Listers:


Has anyone derived an approximate break-even point, rule of thumb, when considering coding options: a regular cursor (open – fetch loop) and using Rowset processing in a regular batch (read COBOL) program that is not a stored procedure?

I’m sure that there is a ‘price’ to pay for setting up and calling DB2 using ROWSET processing. But, where (within a range) would that be a better pay off against a regular cursor and fetch-loop logic?

I understand that if you have no clue on the number of rows being returned, you’re vulnerable to the variations in the fixed-cost of each method, but you still need to hard-code a number in the program for ROWSET.

I know that it certainly does depend, but I’d like to know that break-even point, there must be some reasonable calculation to consider.

TIA,
Gary Joehlin


________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

________________________________
Windows 7: Unclutter your desktop. Learn more.
________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

_____________________________________________________________________

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

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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

M. Khalid Khan

Re: Approximate "break-even" when using Rowset processing
(in response to Myron Miller)
There is another case where it might be worthwile to retrofit multi-row
fetch - a CICS transaction that's not threadsafe. Such a transaction
incurrs a task switch before and another after every fetch plus any delays
involved with the switch. There is a potential for savings on both cpu and
suspend time.
HTH
Khalid




<snip>

3. Our experience at DST for retro-fitting has been this: it is worth it
only for large volumes of data - e.g. if program intends to fetch say 200
rows, it is generally not economical for us to pay the programming
resources - your metrics will be different.

<snip>



HCSC Company Disclaimer

The information contained in this communication is confidential, private,
proprietary, or otherwise privileged and is intended only for the use of
the addressee. Unauthorized use, disclosure, distribution or copying is
strictly prohibited and may be unlawful. If you have received this
communication in error, please notify the sender immediately at (312)
653-6000 in Illinois; (800)835-8699 in New Mexico; (918)560-3500 in
Oklahoma; or (972)766-6900 in Texas.

_____________________________________________________________________

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

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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: Approximate "break-even" when using Rowset processing
(in response to M. Khalid Khan)
When searching for a "break-even" (runtime), don't forget to take into
consideration the length of the data rows being fetched.
Multi-row fetch will be especially beneficial (even for just a few rows) if
the rows are very *short*.

E.g.: you want to retrieve a list of PKs (INTs or SMALLINTs), to be stored
locally for some (limited) time.

Speaking of complexity in COBOL: there is one situation where the multi-row
fetch will be easier to write (and the program complexity will be lower)
than a programmed iteration:
if all returned data is to be stored in memory anyhow (in a COBOL table),
and there is a known upper bound to the size of that table.

The "classical" approach will need an interation, and an increment of the
COBOL table subscript inside the loop. The multi-row fetch solution just
requires a single FETCH, no iteration.

(Agreed, dealing with non-zero SQLCODEs is going to be a more complex issue...)


-- Peter Vanroose
ABIS Training & Consulting,
Leuven, Belgium.

_____________________________________________________________________

* 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: Approximate "break-even" when using Rowset processing
(in response to Peter Vanroose)
Hi David,

Sorry I’m just getting back to DB2-L.

This is a great idea. Have you verified this?

Is the SQLCODE set to the highest SQLCODE?

The reason I ask is if you get several different errors but the last row processed is successful what do you get?





< http://www.ca.com/mainframe2 >

Troy Coleman

CA, Inc.

Principal Product Manager

Office: +1630 505 6025

Mobile: +1224 343 0073

Blog: http://ibmsystemsmag.blogs.com/db2utor/

Twitter: http://twitter.com/DB2utor

Twitter: http://twitter.com/troycoleman

Linkedin: http://www.linkedin.com/in/troylcoleman



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of David Simpson
Sent: Tuesday, December 01, 2009 8:45 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing



Another thing to keep in mind for this….



People like me hammer home the need to use GET DIAGNOSTICS when doing multi-row fetch & insert. This is true if you want good messages for any errors encountered. HOWEVER, I’ve seen a few people substitute GET DIAGNOSTICS for the normal SQLCODE check at the end of a multi-row operation. This could KILL your performance and drive your break-even point up in the stratosphere. If you perform a multi-row operation and receive an SQLCODE of 0 then you generally have no need for GET DIAGNOSTICS, which is VERY expensive. So, check SQLCODE first and only go to the DIAGNOSTICS when errors are encountered.



My 2¢ (with a nod to Faz for the ¢ sign that I cut and pasted)...



cid:[login to unmask email]______________________________________________________________________________
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email] <mailto:[login to unmask email]>

For more information about Themis, visit www.themisinc.com < http://www.themisinc.com/ >



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dave Nance
Sent: Tuesday, December 01, 2009 6:08 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing



Dan Lukesetich(?sp?) from YL&A has some slides that show the savings/costs associated with multi-row fetch on the YL&A website. I think there is even a behind the bar podcast where he and Susan discuss it. If I remember correct, the best savings was right around 100 records.


David Nance





________________________________

From: "Joehlin, Gary" <[login to unmask email]>
To: [login to unmask email]
Sent: Mon, November 30, 2009 4:22:04 PM
Subject: [DB2-L] Approximate "break-even" when using Rowset processing

Hello Listers:





Has anyone derived an approximate break-even point, rule of thumb, when considering coding options: a regular cursor (open – fetch loop) and using Rowset processing in a regular batch (read COBOL) program that is not a stored procedure?



I’m sure that there is a ‘price’ to pay for setting up and calling DB2 using ROWSET processing. But, where (within a range) would that be a better pay off against a regular cursor and fetch-loop logic?



I understand that if you have no clue on the number of rows being returned, you’re vulnerable to the variations in the fixed-cost of each method, but you still need to hard-code a number in the program for ROWSET.



I know that it certainly does depend, but I’d like to know that break-even point, there must be some reasonable calculation to consider.



TIA,

Gary Joehlin



________________________________

David Simpson

Re: Approximate "break-even" when using Rowset processing
(in response to Troy L Coleman)
For multi-row fetch you get a +345 SQLCODE which essentially says "one or more errors may have occurred... have a nice day"

For a non atomic multi-row insert you get a -253 if some of the rows fail or a -254 if all of them fail. Again, no details just "an error has occurred".

For an atomic multi-row insert you get the real SQLCODE of the first failure (since atomic means any failure backs out the entire insert), but you still need the diagnostics to determine which row actually tripped the error.

______________________________________________________________________________
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email] <mailto:[login to unmask email]>
For more information about Themis, visit www.themisinc.com <https://mail.themisinc.com/exchweb/bin/redir.asp?URL= http://www.themisinc.com/ >

________________________________

From: IDUG DB2-L on behalf of Coleman, Troy L
Sent: Sat 12/12/2009 10:24 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing



Hi David,

Sorry I'm just getting back to DB2-L.

This is a great idea. Have you verified this?

Is the SQLCODE set to the highest SQLCODE?

The reason I ask is if you get several different errors but the last row processed is successful what do you get?





cid:[login to unmask email] < http://www.ca.com/mainframe2 >

Troy Coleman

CA, Inc.

Principal Product Manager

Office: +1630 505 6025

Mobile: +1224 343 0073

Blog: http://ibmsystemsmag.blogs.com/db2utor/

Twitter: http://twitter.com/DB2utor

Twitter: http://twitter.com/troycoleman

Linkedin: http://www.linkedin.com/in/troylcoleman



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of David Simpson
Sent: Tuesday, December 01, 2009 8:45 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing



Another thing to keep in mind for this....



People like me hammer home the need to use GET DIAGNOSTICS when doing multi-row fetch & insert. This is true if you want good messages for any errors encountered. HOWEVER, I've seen a few people substitute GET DIAGNOSTICS for the normal SQLCODE check at the end of a multi-row operation. This could KILL your performance and drive your break-even point up in the stratosphere. If you perform a multi-row operation and receive an SQLCODE of 0 then you generally have no need for GET DIAGNOSTICS, which is VERY expensive. So, check SQLCODE first and only go to the DIAGNOSTICS when errors are encountered.



My 2¢ (with a nod to Faz for the ¢ sign that I cut and pasted)...



cid:[login to unmask email] < https://mail.themisinc.com/exchange/dsimpson/Drafts/RE:%20[DB2-L]%[login to unmask email] > ______________________________________________________________________________
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email] <mailto:[login to unmask email]>

For more information about Themis, visit www.themisinc.com < http://www.themisinc.com/ >



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dave Nance
Sent: Tuesday, December 01, 2009 6:08 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing



Dan Lukesetich(?sp?) from YL&A has some slides that show the savings/costs associated with multi-row fetch on the YL&A website. I think there is even a behind the bar podcast where he and Susan discuss it. If I remember correct, the best savings was right around 100 records.


David Nance





________________________________

From: "Joehlin, Gary" <[login to unmask email]>
To: [login to unmask email]
Sent: Mon, November 30, 2009 4:22:04 PM
Subject: [DB2-L] Approximate "break-even" when using Rowset processing

Hello Listers:





Has anyone derived an approximate break-even point, rule of thumb, when considering coding options: a regular cursor (open - fetch loop) and using Rowset processing in a regular batch (read COBOL) program that is not a stored procedure?



I'm sure that there is a 'price' to pay for setting up and calling DB2 using ROWSET processing. But, where (within a range) would that be a better pay off against a regular cursor and fetch-loop logic?



I understand that if you have no clue on the number of rows being returned, you're vulnerable to the variations in the fixed-cost of each method, but you still need to hard-code a number in the program for ROWSET.



I know that it certainly does depend, but I'd like to know that break-even point, there must be some reasonable calculation to consider.



TIA,

Gary Joehlin



________________________________


_____________________________________________________________________

* 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 Suhner

Re: Approximate "break-even" when using Rowset processing
(in response to David Simpson)

David, Troy,

looks like a typo: The SQLCODE for multi-row fetch actually is +354.

And - yes - that's the way we do it. Only call DIAGNOSTICS when one of the respective SQLCODEs were encountered.

Regards,
Peter

Date: Sun, 13 Dec 2009 14:26:22 -0500
From: [login to unmask email]
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing










For multi-row fetch you get a +345 SQLCODE which essentially says "one or more errors may have occurred... have a nice day"

For a non atomic multi-row insert you get a -253 if some of the rows fail or a -254 if all of them fail. Again, no details just "an error has occurred".

For an atomic multi-row insert you get the real SQLCODE of the first failure (since atomic means any failure backs out the entire insert), but you still need the diagnostics to determine which row actually tripped the error.



______________________________________________________________________________
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email]
For more information about Themis, visit www.themisinc.com



From: IDUG DB2-L on behalf of Coleman, Troy L
Sent: Sat 12/12/2009 10:24 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing




Hi David,
Sorry I’m just getting back to DB2-L.
This is a great idea. Have you verified this?
Is the SQLCODE set to the highest SQLCODE?
The reason I ask is if you get several different errors but the last row processed is successful what do you get?









Troy Coleman
CA, Inc.
Principal Product Manager
Office: +1630 505 6025
Mobile: +1224 343 0073
Blog: http://ibmsystemsmag.blogs.com/db2utor/
Twitter: http://twitter.com/DB2utor
Twitter: http://twitter.com/troycoleman
Linkedin: http://www.linkedin.com/in/troylcoleman



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of David Simpson
Sent: Tuesday, December 01, 2009 8:45 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing

Another thing to keep in mind for this….

People like me hammer home the need to use GET DIAGNOSTICS when doing multi-row fetch & insert. This is true if you want good messages for any errors encountered. HOWEVER, I’ve seen a few people substitute GET DIAGNOSTICS for the normal SQLCODE check at the end of a multi-row operation. This could KILL your performance and drive your break-even point up in the stratosphere. If you perform a multi-row operation and receive an SQLCODE of 0 then you generally have no need for GET DIAGNOSTICS, which is VERY expensive. So, check SQLCODE first and only go to the DIAGNOSTICS when errors are encountered.

My 2¢ (with a nod to Faz for the ¢ sign that I cut and pasted)...


______________________________________________________________________________
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email]
For more information about Themis, visit www.themisinc.com



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dave Nance
Sent: Tuesday, December 01, 2009 6:08 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing



Dan Lukesetich(?sp?) from YL&A has some slides that show the savings/costs associated with multi-row fetch on the YL&A website. I think there is even a behind the bar podcast where he and Susan discuss it. If I remember correct, the best savings was right around 100 records.


David Nance








From: "Joehlin, Gary" <[login to unmask email]>
To: [login to unmask email]
Sent: Mon, November 30, 2009 4:22:04 PM
Subject: [DB2-L] Approximate "break-even" when using Rowset processing

Hello Listers:


Has anyone derived an approximate break-even point, rule of thumb, when considering coding options: a regular cursor (open – fetch loop) and using Rowset processing in a regular batch (read COBOL) program that is not a stored procedure?

I’m sure that there is a ‘price’ to pay for setting up and calling DB2 using ROWSET processing. But, where (within a range) would that be a better pay off against a regular cursor and fetch-loop logic?

I understand that if you have no clue on the number of rows being returned, you’re vulnerable to the variations in the fixed-cost of each method, but you still need to hard-code a number in the program for ROWSET.

I know that it certainly does depend, but I’d like to know that break-even point, there must be some reasonable calculation to consider.

TIA,
Gary Joehlin










The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.


_________________________________________________________________
Windows Live: Friends get your Flickr, Yelp, and Digg updates when they e-mail you.
http://www.microsoft.com/middleeast/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:en-xm:SI_SB_3:092010
_____________________________________________________________________

* 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

David Simpson

Re: Approximate "break-even" when using Rowset processing
(in response to Peter Suhner)
Indeed... I cannot type. +354 it is.



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Peter Suhner
Sent: Sunday, December 13, 2009 2:32 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing



David, Troy,

looks like a typo: The SQLCODE for multi-row fetch actually is +354.

And - yes - that's the way we do it. Only call DIAGNOSTICS when one of the respective SQLCODEs were encountered.

Regards,
Peter

________________________________

Date: Sun, 13 Dec 2009 14:26:22 -0500
From: [login to unmask email]
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing

For multi-row fetch you get a +345 SQLCODE which essentially says "one or more errors may have occurred... have a nice day"



For a non atomic multi-row insert you get a -253 if some of the rows fail or a -254 if all of them fail. Again, no details just "an error has occurred".



For an atomic multi-row insert you get the real SQLCODE of the first failure (since atomic means any failure backs out the entire insert), but you still need the diagnostics to determine which row actually tripped the error.



______________________________________________________________________________
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email]

For more information about Themis, visit www.themisinc.com <https://mail.themisinc.com/exchweb/bin/redir.asp?URL= http://www.themisinc.com/ >



________________________________

From: IDUG DB2-L on behalf of Coleman, Troy L
Sent: Sat 12/12/2009 10:24 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing

Hi David,

Sorry I'm just getting back to DB2-L.

This is a great idea. Have you verified this?

Is the SQLCODE set to the highest SQLCODE?

The reason I ask is if you get several different errors but the last row processed is successful what do you get?





cid:[login to unmask email] < http://www.ca.com/mainframe2 >

Troy Coleman

CA, Inc.

Principal Product Manager

Office: +1630 505 6025

Mobile: +1224 343 0073

Blog: http://ibmsystemsmag.blogs.com/db2utor/

Twitter: http://twitter.com/DB2utor

Twitter: http://twitter.com/troycoleman

Linkedin: http://www.linkedin.com/in/troylcoleman



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of David Simpson
Sent: Tuesday, December 01, 2009 8:45 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing



Another thing to keep in mind for this....



People like me hammer home the need to use GET DIAGNOSTICS when doing multi-row fetch & insert. This is true if you want good messages for any errors encountered. HOWEVER, I've seen a few people substitute GET DIAGNOSTICS for the normal SQLCODE check at the end of a multi-row operation. This could KILL your performance and drive your break-even point up in the stratosphere. If you perform a multi-row operation and receive an SQLCODE of 0 then you generally have no need for GET DIAGNOSTICS, which is VERY expensive. So, check SQLCODE first and only go to the DIAGNOSTICS when errors are encountered.



My 2¢ (with a nod to Faz for the ¢ sign that I cut and pasted)...



cid:[login to unmask email] < https://mail.themisinc.com/exchange/dsimpson/Drafts/RE:%[login to unmask email] > ______________________________________________________________________________
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email] <mailto:[login to unmask email]>

For more information about Themis, visit www.themisinc.com < http://www.themisinc.com/ >



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dave Nance
Sent: Tuesday, December 01, 2009 6:08 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing



Dan Lukesetich(?sp?) from YL&A has some slides that show the savings/costs associated with multi-row fetch on the YL&A website. I think there is even a behind the bar podcast where he and Susan discuss it. If I remember correct, the best savings was right around 100 records.


David Nance





________________________________

From: "Joehlin, Gary" <[login to unmask email]>
To: [login to unmask email]
Sent: Mon, November 30, 2009 4:22:04 PM
Subject: [DB2-L] Approximate "break-even" when using Rowset processing

Hello Listers:





Has anyone derived an approximate break-even point, rule of thumb, when considering coding options: a regular cursor (open - fetch loop) and using Rowset processing in a regular batch (read COBOL) program that is not a stored procedure?



I'm sure that there is a 'price' to pay for setting up and calling DB2 using ROWSET processing. But, where (within a range) would that be a better pay off against a regular cursor and fetch-loop logic?



I understand that if you have no clue on the number of rows being returned, you're vulnerable to the variations in the fixed-cost of each method, but you still need to hard-code a number in the program for ROWSET.



I know that it certainly does depend, but I'd like to know that break-even point, there must be some reasonable calculation to consider.



TIA,

Gary Joehlin



________________________________



________________________________

IDUG - The Worldwide DB2 User Community! < 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 >

________________________________

Windows Live: Friends get your Flickr, Yelp, and Digg updates when they e-mail you. < http://www.microsoft.com/middleeast/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:en-xm:SI_SB_3:092010 >



________________________________

IDUG - The Worldwide DB2 User Community! < 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/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: Approximate "break-even" when using Rowset processing
(in response to David Simpson)
Thanks. I love this list.





< http://www.ca.com/mainframe2 >

Troy Coleman

CA, Inc.

Principal Product Manager

Office: +1630 505 6025

Mobile: +1224 343 0073

Blog: http://ibmsystemsmag.blogs.com/db2utor/

Twitter: http://twitter.com/DB2utor

Twitter: http://twitter.com/troycoleman

Linkedin: http://www.linkedin.com/in/troylcoleman



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of David Simpson
Sent: Sunday, December 13, 2009 4:47 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing



Indeed... I cannot type. +354 it is.



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Peter Suhner
Sent: Sunday, December 13, 2009 2:32 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing



David, Troy,

looks like a typo: The SQLCODE for multi-row fetch actually is +354.

And - yes - that's the way we do it. Only call DIAGNOSTICS when one of the respective SQLCODEs were encountered.

Regards,
Peter

________________________________

Date: Sun, 13 Dec 2009 14:26:22 -0500
From: [login to unmask email]
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing

For multi-row fetch you get a +345 SQLCODE which essentially says "one or more errors may have occurred... have a nice day"



For a non atomic multi-row insert you get a -253 if some of the rows fail or a -254 if all of them fail. Again, no details just "an error has occurred".



For an atomic multi-row insert you get the real SQLCODE of the first failure (since atomic means any failure backs out the entire insert), but you still need the diagnostics to determine which row actually tripped the error.



______________________________________________________________________________
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email]

For more information about Themis, visit www.themisinc.com <https://mail.themisinc.com/exchweb/bin/redir.asp?URL= http://www.themisinc.com/ >



________________________________

From: IDUG DB2-L on behalf of Coleman, Troy L
Sent: Sat 12/12/2009 10:24 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing

Hi David,

Sorry I'm just getting back to DB2-L.

This is a great idea. Have you verified this?

Is the SQLCODE set to the highest SQLCODE?

The reason I ask is if you get several different errors but the last row processed is successful what do you get?





cid:[login to unmask email] < http://www.ca.com/mainframe2 >

Troy Coleman

CA, Inc.

Principal Product Manager

Office: +1630 505 6025

Mobile: +1224 343 0073

Blog: http://ibmsystemsmag.blogs.com/db2utor/

Twitter: http://twitter.com/DB2utor

Twitter: http://twitter.com/troycoleman

Linkedin: http://www.linkedin.com/in/troylcoleman



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of David Simpson
Sent: Tuesday, December 01, 2009 8:45 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing



Another thing to keep in mind for this....



People like me hammer home the need to use GET DIAGNOSTICS when doing multi-row fetch & insert. This is true if you want good messages for any errors encountered. HOWEVER, I've seen a few people substitute GET DIAGNOSTICS for the normal SQLCODE check at the end of a multi-row operation. This could KILL your performance and drive your break-even point up in the stratosphere. If you perform a multi-row operation and receive an SQLCODE of 0 then you generally have no need for GET DIAGNOSTICS, which is VERY expensive. So, check SQLCODE first and only go to the DIAGNOSTICS when errors are encountered.



My 2¢ (with a nod to Faz for the ¢ sign that I cut and pasted)...



cid:[login to unmask email] < https://mail.themisinc.com/exchange/dsimpson/Drafts/RE:%[login to unmask email] > ______________________________________________________________________________
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email] <mailto:[login to unmask email]>

For more information about Themis, visit www.themisinc.com < http://www.themisinc.com/ >



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dave Nance
Sent: Tuesday, December 01, 2009 6:08 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing



Dan Lukesetich(?sp?) from YL&A has some slides that show the savings/costs associated with multi-row fetch on the YL&A website. I think there is even a behind the bar podcast where he and Susan discuss it. If I remember correct, the best savings was right around 100 records.


David Nance





________________________________

From: "Joehlin, Gary" <[login to unmask email]>
To: [login to unmask email]
Sent: Mon, November 30, 2009 4:22:04 PM
Subject: [DB2-L] Approximate "break-even" when using Rowset processing

Hello Listers:





Has anyone derived an approximate break-even point, rule of thumb, when considering coding options: a regular cursor (open - fetch loop) and using Rowset processing in a regular batch (read COBOL) program that is not a stored procedure?



I'm sure that there is a 'price' to pay for setting up and calling DB2 using ROWSET processing. But, where (within a range) would that be a better pay off against a regular cursor and fetch-loop logic?



I understand that if you have no clue on the number of rows being returned, you're vulnerable to the variations in the fixed-cost of each method, but you still need to hard-code a number in the program for ROWSET.



I know that it certainly does depend, but I'd like to know that break-even point, there must be some reasonable calculation to consider.



TIA,

Gary Joehlin



________________________________



________________________________

IDUG - The Worldwide DB2 User Community! < 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 >

________________________________

Windows Live: Friends get your Flickr, Yelp, and Digg updates when they e-mail you. < http://www.microsoft.com/middleeast/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:en-xm:SI_SB_3:092010 >



________________________________

IDUG - The Worldwide DB2 User Community! < 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 - The Worldwide DB2 User Community! < 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

Suresh Sane

Re: Approximate "break-even" when using Rowset processing
(in response to Troy L Coleman)

Just for the record, one more fact which could be important.



You get the generic "you had errors" as the first error in GET DIAGNOSTICS also.



If you had multiple errors, they seem to appear in reverse order. e.g. when inserting 5 rows, row no. 2 and row no. 4 had errors. GET DIAGNOSTICS shows three errors (not two!) - first is the generic, 2nd is error for row #4 and third is error for row #2.



Who cares? Mostly no one since you should process all errors anyway. But if my first error is row #4, it does not mean first 3 were successful! You could process backwards also.



As I said, mostly trivia...



Thx

Suresh


Date: Sun, 13 Dec 2009 20:04:50 -0500
From: [login to unmask email]
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing







Thanks. I love this list.









Troy Coleman
CA, Inc.
Principal Product Manager
Office: +1630 505 6025
Mobile: +1224 343 0073
Blog: http://ibmsystemsmag.blogs.com/db2utor/
Twitter: http://twitter.com/DB2utor
Twitter: http://twitter.com/troycoleman
Linkedin: http://www.linkedin.com/in/troylcoleman



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of David Simpson
Sent: Sunday, December 13, 2009 4:47 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing

Indeed… I cannot type. +354 it is.



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Peter Suhner
Sent: Sunday, December 13, 2009 2:32 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing

David, Troy,

looks like a typo: The SQLCODE for multi-row fetch actually is +354.

And - yes - that's the way we do it. Only call DIAGNOSTICS when one of the respective SQLCODEs were encountered.

Regards,
Peter



Date: Sun, 13 Dec 2009 14:26:22 -0500
From: [login to unmask email]
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing


For multi-row fetch you get a +345 SQLCODE which essentially says "one or more errors may have occurred... have a nice day"



For a non atomic multi-row insert you get a -253 if some of the rows fail or a -254 if all of them fail. Again, no details just "an error has occurred".



For an atomic multi-row insert you get the real SQLCODE of the first failure (since atomic means any failure backs out the entire insert), but you still need the diagnostics to determine which row actually tripped the error.





______________________________________________________________________________
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email]

For more information about Themis, visit www.themisinc.com





From: IDUG DB2-L on behalf of Coleman, Troy L
Sent: Sat 12/12/2009 10:24 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing


Hi David,
Sorry I’m just getting back to DB2-L.
This is a great idea. Have you verified this?
Is the SQLCODE set to the highest SQLCODE?
The reason I ask is if you get several different errors but the last row processed is successful what do you get?









Troy Coleman
CA, Inc.
Principal Product Manager
Office: +1630 505 6025
Mobile: +1224 343 0073
Blog: http://ibmsystemsmag.blogs.com/db2utor/
Twitter: http://twitter.com/DB2utor
Twitter: http://twitter.com/troycoleman
Linkedin: http://www.linkedin.com/in/troylcoleman



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of David Simpson
Sent: Tuesday, December 01, 2009 8:45 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing

Another thing to keep in mind for this….

People like me hammer home the need to use GET DIAGNOSTICS when doing multi-row fetch & insert. This is true if you want good messages for any errors encountered. HOWEVER, I’ve seen a few people substitute GET DIAGNOSTICS for the normal SQLCODE check at the end of a multi-row operation. This could KILL your performance and drive your break-even point up in the stratosphere. If you perform a multi-row operation and receive an SQLCODE of 0 then you generally have no need for GET DIAGNOSTICS, which is VERY expensive. So, check SQLCODE first and only go to the DIAGNOSTICS when errors are encountered.

My 2¢ (with a nod to Faz for the ¢ sign that I cut and pasted)...


______________________________________________________________________________
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email]
For more information about Themis, visit www.themisinc.com



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dave Nance
Sent: Tuesday, December 01, 2009 6:08 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing



Dan Lukesetich(?sp?) from YL&A has some slides that show the savings/costs associated with multi-row fetch on the YL&A website. I think there is even a behind the bar podcast where he and Susan discuss it. If I remember correct, the best savings was right around 100 records.


David Nance








From: "Joehlin, Gary" <[login to unmask email]>
To: [login to unmask email]
Sent: Mon, November 30, 2009 4:22:04 PM
Subject: [DB2-L] Approximate "break-even" when using Rowset processing

Hello Listers:


Has anyone derived an approximate break-even point, rule of thumb, when considering coding options: a regular cursor (open – fetch loop) and using Rowset processing in a regular batch (read COBOL) program that is not a stored procedure?

I’m sure that there is a ‘price’ to pay for setting up and calling DB2 using ROWSET processing. But, where (within a range) would that be a better pay off against a regular cursor and fetch-loop logic?

I understand that if you have no clue on the number of rows being returned, you’re vulnerable to the variations in the fixed-cost of each method, but you still need to hard-code a number in the program for ROWSET.

I know that it certainly does depend, but I’d like to know that break-even point, there must be some reasonable calculation to consider.

TIA,
Gary Joehlin









The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.



Windows Live: Friends get your Flickr, Yelp, and Digg updates when they e-mail you.





The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.






The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.





The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

_________________________________________________________________
Hotmail: Free, trusted and rich email service.
http://clk.atdmt.com/GBL/go/171222984/direct/01/
_____________________________________________________________________

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

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

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

David Simpson

Re: Approximate "break-even" when using Rowset processing
(in response to Suresh Sane)
I have always wondered why the errors come out in reverse.. Guess it's a last-in-first-out queue.

-----Original Message-----
From: Suresh Sane <[login to unmask email]>
Sent: Monday, December 14, 2009 7:46 PM
To: [login to unmask email] <[login to unmask email]>
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing


Just for the record, one more fact which could be important.



You get the generic "you had errors" as the first error in GET DIAGNOSTICS also.



If you had multiple errors, they seem to appear in reverse order. e.g. when inserting 5 rows, row no. 2 and row no. 4 had errors. GET DIAGNOSTICS shows three errors (not two!) - first is the generic, 2nd is error for row #4 and third is error for row #2.



Who cares? Mostly no one since you should process all errors anyway. But if my first error is row #4, it does not mean first 3 were successful! You could process backwards also.



As I said, mostly trivia...



Thx

Suresh


Date: Sun, 13 Dec 2009 20:04:50 -0500
From: [login to unmask email]
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing







Thanks. I love this list.









Troy Coleman
CA, Inc.
Principal Product Manager
Office: +1630 505 6025
Mobile: +1224 343 0073
Blog: http://ibmsystemsmag.blogs.com/db2utor/
Twitter: http://twitter.com/DB2utor
Twitter: http://twitter.com/troycoleman
Linkedin: http://www.linkedin.com/in/troylcoleman



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of David Simpson
Sent: Sunday, December 13, 2009 4:47 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing

Indeed… I cannot type. +354 it is.



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Peter Suhner
Sent: Sunday, December 13, 2009 2:32 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing

David, Troy,

looks like a typo: The SQLCODE for multi-row fetch actually is +354.

And - yes - that's the way we do it. Only call DIAGNOSTICS when one of the respective SQLCODEs were encountered.

Regards,
Peter



Date: Sun, 13 Dec 2009 14:26:22 -0500
From: [login to unmask email]
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing


For multi-row fetch you get a +345 SQLCODE which essentially says "one or more errors may have occurred... have a nice day"



For a non atomic multi-row insert you get a -253 if some of the rows fail or a -254 if all of them fail. Again, no details just "an error has occurred".



For an atomic multi-row insert you get the real SQLCODE of the first failure (since atomic means any failure backs out the entire insert), but you still need the diagnostics to determine which row actually tripped the error.





______________________________________________________________________________
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email]

For more information about Themis, visit www.themisinc.com





From: IDUG DB2-L on behalf of Coleman, Troy L
Sent: Sat 12/12/2009 10:24 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing


Hi David,
Sorry I’m just getting back to DB2-L.
This is a great idea. Have you verified this?
Is the SQLCODE set to the highest SQLCODE?
The reason I ask is if you get several different errors but the last row processed is successful what do you get?









Troy Coleman
CA, Inc.
Principal Product Manager
Office: +1630 505 6025
Mobile: +1224 343 0073
Blog: http://ibmsystemsmag.blogs.com/db2utor/
Twitter: http://twitter.com/DB2utor
Twitter: http://twitter.com/troycoleman
Linkedin: http://www.linkedin.com/in/troylcoleman



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of David Simpson
Sent: Tuesday, December 01, 2009 8:45 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing

Another thing to keep in mind for this….

People like me hammer home the need to use GET DIAGNOSTICS when doing multi-row fetch & insert. This is true if you want good messages for any errors encountered. HOWEVER, I’ve seen a few people substitute GET DIAGNOSTICS for the normal SQLCODE check at the end of a multi-row operation. This could KILL your performance and drive your break-even point up in the stratosphere. If you perform a multi-row operation and receive an SQLCODE of 0 then you generally have no need for GET DIAGNOSTICS, which is VERY expensive. So, check SQLCODE first and only go to the DIAGNOSTICS when errors are encountered.

My 2¢ (with a nod to Faz for the ¢ sign that I cut and pasted)...


______________________________________________________________________________
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email]
For more information about Themis, visit www.themisinc.com



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dave Nance
Sent: Tuesday, December 01, 2009 6:08 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing



Dan Lukesetich(?sp?) from YL&A has some slides that show the savings/costs associated with multi-row fetch on the YL&A website. I think there is even a behind the bar podcast where he and Susan discuss it. If I remember correct, the best savings was right around 100 records.


David Nance








From: "Joehlin, Gary" <[login to unmask email]>
To: [login to unmask email]
Sent: Mon, November 30, 2009 4:22:04 PM
Subject: [DB2-L] Approximate "break-even" when using Rowset processing

Hello Listers:


Has anyone derived an approximate break-even point, rule of thumb, when considering coding options: a regular cursor (open – fetch loop) and using Rowset processing in a regular batch (read COBOL) program that is not a stored procedure?

I’m sure that there is a ‘price’ to pay for setting up and calling DB2 using ROWSET processing. But, where (within a range) would that be a better pay off against a regular cursor and fetch-loop logic?

I understand that if you have no clue on the number of rows being returned, you’re vulnerable to the variations in the fixed-cost of each method, but you still need to hard-code a number in the program for ROWSET.

I know that it certainly does depend, but I’d like to know that break-even point, there must be some reasonable calculation to consider.

TIA,
Gary Joehlin









The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.



Windows Live: Friends get your Flickr, Yelp, and Digg updates when they e-mail you.





The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.






The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.





The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

_________________________________________________________________
Hotmail: Free, trusted and rich email service.
http://clk.atdmt.com/GBL/go/171222984/direct/01/
_____________________________________________________________________

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

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

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

_____________________________________________________________________

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

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

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

Suresh Sane

Re: Approximate "break-even" when using Rowset processing
(in response to David Simpson)

Don't know why. Last-in-first-out queue = stack.



Thx

Suresh

> Date: Mon, 14 Dec 2009 19:49:41 -0600
> From: [login to unmask email]
> To: [login to unmask email]
> Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing
>
> I have always wondered why the errors come out in reverse.. Guess it's a last-in-first-out queue.
>
> -----Original Message-----
> From: Suresh Sane <[login to unmask email]>
> Sent: Monday, December 14, 2009 7:46 PM
> To: [login to unmask email] <[login to unmask email]>
> Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing
>
>
> Just for the record, one more fact which could be important.
>
>
>
> You get the generic "you had errors" as the first error in GET DIAGNOSTICS also.
>
>
>
> If you had multiple errors, they seem to appear in reverse order. e.g. when inserting 5 rows, row no. 2 and row no. 4 had errors. GET DIAGNOSTICS shows three errors (not two!) - first is the generic, 2nd is error for row #4 and third is error for row #2.
>
>
>
> Who cares? Mostly no one since you should process all errors anyway. But if my first error is row #4, it does not mean first 3 were successful! You could process backwards also.
>
>
>
> As I said, mostly trivia...
>
>
>
> Thx
>
> Suresh
>
>
> Date: Sun, 13 Dec 2009 20:04:50 -0500
> From: [login to unmask email]
> To: [login to unmask email]
> Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing
>
>
>
>
>
>
>
> Thanks. I love this list.
>
>
>
>
>
>
>
>
>
> Troy Coleman
> CA, Inc.
> Principal Product Manager
> Office: +1630 505 6025
> Mobile: +1224 343 0073
> Blog: http://ibmsystemsmag.blogs.com/db2utor/
> Twitter: http://twitter.com/DB2utor
> Twitter: http://twitter.com/troycoleman
> Linkedin: http://www.linkedin.com/in/troylcoleman
>
>
>
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of David Simpson
> Sent: Sunday, December 13, 2009 4:47 PM
> To: [login to unmask email]
> Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing
>
> Indeed… I cannot type. +354 it is.
>
>
>
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Peter Suhner
> Sent: Sunday, December 13, 2009 2:32 PM
> To: [login to unmask email]
> Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing
>
> David, Troy,
>
> looks like a typo: The SQLCODE for multi-row fetch actually is +354.
>
> And - yes - that's the way we do it. Only call DIAGNOSTICS when one of the respective SQLCODEs were encountered.
>
> Regards,
> Peter
>
>
>
> Date: Sun, 13 Dec 2009 14:26:22 -0500
> From: [login to unmask email]
> To: [login to unmask email]
> Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing
>
>
> For multi-row fetch you get a +345 SQLCODE which essentially says "one or more errors may have occurred... have a nice day"
>
>
>
> For a non atomic multi-row insert you get a -253 if some of the rows fail or a -254 if all of them fail. Again, no details just "an error has occurred".
>
>
>
> For an atomic multi-row insert you get the real SQLCODE of the first failure (since atomic means any failure backs out the entire insert), but you still need the diagnostics to determine which row actually tripped the error.
>
>
>
>
>
> ______________________________________________________________________________
> David Simpson | Senior Technical Advisor | Themis Education
> 123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email]
>
> For more information about Themis, visit www.themisinc.com
>
>
>
>
>
> From: IDUG DB2-L on behalf of Coleman, Troy L
> Sent: Sat 12/12/2009 10:24 PM
> To: [login to unmask email]
> Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing
>
>
> Hi David,
> Sorry I’m just getting back to DB2-L.
> This is a great idea. Have you verified this?
> Is the SQLCODE set to the highest SQLCODE?
> The reason I ask is if you get several different errors but the last row processed is successful what do you get?
>
>
>
>
>
>
>
>
>
> Troy Coleman
> CA, Inc.
> Principal Product Manager
> Office: +1630 505 6025
> Mobile: +1224 343 0073
> Blog: http://ibmsystemsmag.blogs.com/db2utor/
> Twitter: http://twitter.com/DB2utor
> Twitter: http://twitter.com/troycoleman
> Linkedin: http://www.linkedin.com/in/troylcoleman
>
>
>
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of David Simpson
> Sent: Tuesday, December 01, 2009 8:45 PM
> To: [login to unmask email]
> Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing
>
> Another thing to keep in mind for this….
>
> People like me hammer home the need to use GET DIAGNOSTICS when doing multi-row fetch & insert. This is true if you want good messages for any errors encountered. HOWEVER, I’ve seen a few people substitute GET DIAGNOSTICS for the normal SQLCODE check at the end of a multi-row operation. This could KILL your performance and drive your break-even point up in the stratosphere. If you perform a multi-row operation and receive an SQLCODE of 0 then you generally have no need for GET DIAGNOSTICS, which is VERY expensive. So, check SQLCODE first and only go to the DIAGNOSTICS when errors are encountered.
>
> My 2¢ (with a nod to Faz for the ¢ sign that I cut and pasted)...
>
>
> ______________________________________________________________________________
> David Simpson | Senior Technical Advisor | Themis Education
> 123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email]
> For more information about Themis, visit www.themisinc.com
>
>
>
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dave Nance
> Sent: Tuesday, December 01, 2009 6:08 PM
> To: [login to unmask email]
> Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing
>
>
>
> Dan Lukesetich(?sp?) from YL&A has some slides that show the savings/costs associated with multi-row fetch on the YL&A website. I think there is even a behind the bar podcast where he and Susan discuss it. If I remember correct, the best savings was right around 100 records.
>
>
> David Nance
>
>
>
>
>
>
>
>
> From: "Joehlin, Gary" <[login to unmask email]>
> To: [login to unmask email]
> Sent: Mon, November 30, 2009 4:22:04 PM
> Subject: [DB2-L] Approximate "break-even" when using Rowset processing
>
> Hello Listers:
>
>
> Has anyone derived an approximate break-even point, rule of thumb, when considering coding options: a regular cursor (open – fetch loop) and using Rowset processing in a regular batch (read COBOL) program that is not a stored procedure?
>
> I’m sure that there is a ‘price’ to pay for setting up and calling DB2 using ROWSET processing. But, where (within a range) would that be a better pay off against a regular cursor and fetch-loop logic?
>
> I understand that if you have no clue on the number of rows being returned, you’re vulnerable to the variations in the fixed-cost of each method, but you still need to hard-code a number in the program for ROWSET.
>
> I know that it certainly does depend, but I’d like to know that break-even point, there must be some reasonable calculation to consider.
>
> TIA,
> Gary Joehlin
>
>
>
>
>
>
>
>
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.
>
>
>
> Windows Live: Friends get your Flickr, Yelp, and Digg updates when they e-mail you.
>
>
>
>
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.
>
>
>
>
>
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.
>
>
>
>
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.
>
> _________________________________________________________________
> Hotmail: Free, trusted and rich email service.
> http://clk.atdmt.com/GBL/go/171222984/direct/01/
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
> _____________________________________________________________________
>
> http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
> DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
> many more categories of help waiting for you!
> Whether you are an old hand or a DB2 newbie, we have presentations for every level.
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
> _____________________________________________________________________
>
> http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
> DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
> many more categories of help waiting for you!
> Whether you are an old hand or a DB2 newbie, we have presentations for every level.
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

_________________________________________________________________
Hotmail: Trusted email with Microsoft’s powerful SPAM protection.
http://clk.atdmt.com/GBL/go/177141664/direct/01/
_____________________________________________________________________

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

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

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