RE: SQL Stored Procedures

Neill,

Right now we are building the SQL statement inside of a Java class with
the appropriate library and then submitting it.

We are setting 2 - 3 connections to the iSeries and executing 2-3 sql
statements. We thought we could speed things up with the stored
procedures. Doing all the sql selects in one connection with a single
stored procedure.

We just cannot figure out how to change the library inside the stored
procedure for our different companies.

I really didn't want to get into changing the liblist. May have to
though.

Thanks,

Wes Taylor
Senior Programmer/Analyst - Message Processing
Comporium Data Services
(803) 326-4115
wes.taylor@xxxxxxxxxxxxx




"Neill Harper" <neill.harper@xxxxxxxx>
Sent by: java400-l-bounces@xxxxxxxxxxxx
09/22/2008 01:45 PM
Please respond to
Java Programming on and around the iSeries / AS400
<java400-l@xxxxxxxxxxxx>


To
"'Java Programming on and around the iSeries / AS400'"
<java400-l@xxxxxxxxxxxx>
cc

Subject
RE: SQL Stored Procedures






Hi Wes

You could write a second stored procedure (cobol or rpg) that accepts
companyID and adds that library to the library list. Although you should
be
careful changing the library list of pre-started jobs...

Where are you calling these stored procs from, I thought on the Java JDBC
driver for DB2 there was a library list property, could you get round your
problem by setting that?

Thanks

Neill

-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx
[mailto:java400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Wes Taylor
Sent: 22 September 2008 14:42
To: java400-l@xxxxxxxxxxxx
Subject: SQL Stored Procedures

My company and myself are new to this. We currently are creating java
classes that are building sql statments that are executed on the iSeries.
We have read that creating stored procedures on the iSeries will speed the

process up.

We can execute the sql procedure from our java class and return data. The

problem we are having is that we have 3-4 different libraries with the
same files in each. We need to pass a library/schema to the procedure via

a parm.

This is the procedure we created on the iSeries:

CREATE PROCEDURE SECLIB.test4
(IN companyId CHAR(8)) language SQL
DYNAMIC RESULT SETS 1
BEGIN
DECLARE C1 CURSOR FOR
select * from companyId.APYPFM01;

OPEN C1;
RETURN;
END

The companyId is the library/schema we need to be a variable.

Any suggestions would be greatly appreciated.

Thanks,


Wes Taylor
Senior Programmer/Analyst - Message Processing
Comporium Data Services
(803) 326-4115
wes.taylor@xxxxxxxxxxxxx