Re: SQL Stored Procedure

James,

I guarantee that creating a stored procedure like your trying to do
isn't going to help. In fact, I'd bet a steak dinner it'd hurt.

For a stored procedure to help, what you want to do is move the SQL
statements from the client app into stored procedures in the DB. For
example, suppose the client app is doing:
select fld1, fld2, fld3
from myfile
where fld4 = someVariable

You'd want to create an SP:

CREATE PROCEDURE LIBRARY/GETRESULTS
(IN someVariable)
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE C1 CURSOR
WITH RETURN TO CALLER
FOR select fld1, fld2, fld3
from myfile
where fld4 = someVariable;
OPEN C1;
END


The the client app simply calls the SP.

I like the above method as it adds a layer of abstraction into the
application. The stored procedures function as a well defined
interface into the data.

Since you're probably past the point of rewriting the app to use
stored procedures, you need to make sure that the client app is using
prepared statements instead of dynamic statements. My bet is they're
using dynamic.

If the client app doesn't have any ? or @variable in the statements
and is not calling the DeriveParameters() method, then it probably
means everything the client app is doing is being done dynamically.
This is a bad thing.

I recommend taking a look at the Redbook Integrating DB2 Universal
Database for iSeries with Microsoft ADO .NET
http://www.redbooks.ibm.com/abstracts/sg246440.html

HTH,
Charles



On Tue, Apr 28, 2009 at 10:03 AM, Salter, James <JSalter@xxxxxxxxxx> wrote:

Thanks Charles for the input.

We have a 3rd party designing applications using Visual Studio and are
experiencing some performance issues.

They suggested that creating stored procedures might resolve the
performance issues.

They suggested passing the SQL Statement as a parameter to the
procedure.

If you have a snippet that you could provide, that would be great.

I have looked and continue to look at the redbook for guidance.

Thanks.
James Salter
Systems Programmer
American Cast Iron Pipe Company
phone (205) 325-3033
fax      (205) 307-3833

from: Charles Wilt <charles.wilt@xxxxxxxxx>
subject: Re: SQL Stored Procedure

James,

I won't say it can't be done, but it's going to be very, very hard to
do what you want.

It seems as if you want to provide your own STRSQL type application,
why?  What's the business need, perhaps there is a better way.

If the caller can use SQL to call your procedure, why can't the caller
simply run the statement directly?
For a roll your own STRSQL application, an SQL stored procedure is
probably a poor choice in the first place.

In any event, as you've currently coded it, you're going about the
task completely wrong.

You can't CLOSE a cursor you want returned to a caller.
You also can't FETCH from the cursor you want returned, that's the
caller's job.

HTH,
Charles



_________Confidentiality Notice_______________________
This e-mail and any files transmitted with it is
confidential and is intended solely for the use of
the individual(s) or entity(ies) to whom this e-mail
is addressed.  If you are not the intended recipient
or the person responsible for delivering the e-mail
to the intended recipient, be advised that you have
received this e-mail in error, and that any use,
disclosure, dissemination, forwarding, printing,
retention or copying of this e-mail is strictly
prohibited.  If you have received this e-mail in
error, please immediately return this e-mail to
the sender and delete the e-mail from your system.
Thank you.

--
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.