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