AW: Stored Procedure library list

Hi,

your SQL procedure will only use the library list if it's written with
System-Naming.
If it's written with SQL naming you only can access data within a single
library with unqualified file access.

We already used SQL stored procedures called from outside, written with SQL
Naming, where we need to set the library list.
To set the library list we use a CL-Program, that is registered as Stored
procedure. The easiest way to register an external procedure (a program,
that is written in an language other than SQL) is to use the iSeries
Navigator. You also can register an external procedure by executing the SQL
command CREATE PROCEDURE.
A stored procedure (SQL or external) can be called and executed with the
SQL-Command CALL. CALL can be specified in your SQL procedure.

To set the data library when SQL Naming is used, you need to execute the SQL
Command SET CURRENT SCHEMA.
SET PATH is only used for SQL Naming and only searched to find called stored
procedures and user defined functions.
But it is not used to find tables/physical files, views, indexes or logical
files.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"


-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von DLee@xxxxxxxx
Gesendet: Wednesday, 20. May 2009 20:13
An: rpg400-l@xxxxxxxxxxxx
Betreff: Re: Stored Procedure library list

I have an sql stored procedure that runs against some as400 files and
returns a result set,
it looks like it's working the way I would like, but I also have a need to
run this against different companies with different library lists

I have an as400 clp I can run that will set the library lists for me, but
I don't know how to call the program from a sql stored procedure, and
haven't been able to find an answer searching thru the internet, manuals,
forums.

This is what it looks like as created from generate sql in ops nav

By knowing the company as the IN parm, I can determine the library list by
calling program SETCO1CL with the company as a parameter. I just don't
know how to call it, or where to fit it into the procedure without
generating errors with runsqlstm.

DROP SPECIFIC PROCEDURE CRPRDLIBTS/STR005SP ;

SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM","DRLPGMR" ;

CREATE PROCEDURE CRPRDLIBTS/STR005SP (
IN COCD CHAR(2) ,
IN LNAM CHAR(35) )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC CRPRDLIBTS/STR005SP
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
LANGUAGE SQL
SPECIFIC CRPRDLIBTS/STR005SP
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
CLOSQLCSR = *ENDMOD ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DLYPRP = *NO ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
RDBCNNMTH = *RUW ,
SRTSEQ = *HEX

BEGIN

DECLARE WRKDAT NUMERIC (8,0);
SET WRKDAT = (YEAR(CURRENT DATE)*10000) + (MONTH(CURRENT
DATE)*100) + (DAY(CURRENT DATE));
BEGIN
DECLARE C1 CURSOR FOR

SELECT PCCOCD , PCAGNT , PCPOLM ,PCCOVC, PCSDES, PCEFDT ,PCEXDT
,INLNAM , INFNAM , AGNAME

FROM SPFILES/COVMST
INNER JOIN SPFILES/INSNAM ON INCOCD = PCCOCD AND INAGNT = PCAGNT
AND INPOLM = PCPOLM
AND ININS# = PCINS#
JOIN SPFILES/ACCTMST ON INCOCD = AGCOCD AND INAGNT = AGACCT

WHERE PCCOCD = COCD AND INLNAM = LNAM
AND PCPOLM <> ' '
AND PCCOVC >= 100 AND PCCOVC < 300 AND PCCNDT = 00000000
AND PCEXDT >= WRKDAT
ORDER BY PCCOCD , PCPOLM , INLNAM ;

OPEN C1 ;

END;
END;

COMMENT ON SPECIFIC PROCEDURE CRPRDLIBTS/STR005SP
IS 'SELECT INSURED BY LAST NAME';

Darrell Lee
Information Technology
Extension 17127