RE: Stored Procedures vs. SQL Statements vs. CHAIN

["Brian Piotrowski" <bpiotrowski@xxxxxxxxxxxxxxx>]

Excellent.  Thanks for the advice, Vern.

Brian.

-----Original Message-----
From: Vernon Hamberg [mailto:vhamberg@xxxxxxxxxxx] 
Sent: Friday, May 05, 2006 8:39 AM
To: Midrange Systems Technical Discussion
Subject: Re: Stored Procedures vs. SQL Statements vs. CHAIN

Hi Brian

This strikes me as another "it depends". If you can do multiple 
updates with a single SQL statement, it is probably faster. You 
mention CHAINs, so you must have some useful indexes (LFs) over the 
data that SQL could take advantage of. It's hard to know where the 
break-even point is - generally SQL is for multiple-row, mass 
changes. Native IO  is still ideal for one-at-a-time, for the most 
part. But a good set of indexes can swing it either way.

Another consideration is ease of maintenance - not a daily 
performance issue, however.

You can find IBM's stuff on SQL any of the recent Infocenters - 
www.iseries.ibm.com/infocenter - pick the V5R4 version, eg., click on 
the "Database" link, find the "Related information" link, and there 
you find the relevant Redbooks, among other things. One of them is 
entitled "Stored Procedures, Triggers and User Defined Functions on 
DB2 Universal Database for iSeries" - link is

http://www.redbooks.ibm.com/abstracts/sg246503.html

You can also find stuff at www.iseries.ibm.com/db2 - there under the 
support tab are code samples, tech articles, etc. That link is on the 
same "Related information" page, as well as lots of other good stuff. 
Even a link to porting to iSeries from other systems - it's the link 
near the bottom with "enable" in it, I think.

HTH
Vern

At 07:17 AM 5/5/2006, you wrote:

>Hi All,
>
>
>
>Can someone tell me in terms of speed which method would be best when
>working with multiple records?  Right now my code uses a series of SQL
>statements to update several hundred records in one shot.  I'm thinking
>about moving these statements to a stored procedure instead, but I
>wanted to weigh my choices against a good old CHAIN command as well.
>
>
>
>Anyone have any thoughts or comments?
>
>
>
>Also, can someone please recommend a good Redbook that discusses Stored
>Procedures on the i5?
>
>
>
>Thanks!
>
>
>
>Brian.
>
>
>
>-=-=-=-=-=-=-=-=-=-=-=-=-=-
>
>Brian Piotrowski
>
>Specialist - I.T.
>
>Simcoe Parts Service, Inc.
>
>PH: 705-435-7814
>
>FX: 705-435-6746
>
>-=-=-=-=-=-=-=-=-=-=-=-=-=-
>
>
>
>--
>This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
>To post a message email: MIDRANGE-L@xxxxxxxxxxxx
>To subscribe, unsubscribe, or change list options,
>visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>or email: MIDRANGE-L-request@xxxxxxxxxxxx
>Before posting, please take a moment to review the archives
>at http://archive.midrange.com/midrange-l.