I'm not sure if you are talking about the program name (title of your post) or the program library (body of your post). I can perhaps explain how OS/400 (IBM i) searches for a stored procedure definition and then corresponding the external *PGM or *SRVPGM object. Maybe you can find a solution in here.

First, run these 2 SQL statements:
SELECT * FROM SYSPROCS
SELECT * FROM SYSPARMS

SYSPROCS is system catalog where your stored proc definitions are kept.
SYSPARMS are the corresponding parm definitions (for both stored procs and UDFs/UDTFs).

I only use *SYS naming not *SQL. This allows SQL to use the *LIBL in 2 ways. So I call stored procs like this: CALL MYPROC(PARM1 PARM2 PARM3). You could also make a qualified call like this: CALL MYLIB/MYPROC(PARM1 PARM2 PARM3).

instead of CALL MYLIB.MYPROC(...) which is for *SQL naming.

1. For the unqualified CALL MYPROC(), OS/400 uses your stored procedure name and your job's *LIBL to look for the matching stored procedure schema ("MYLIB" above) and stored proc name.

So CALL MYPROC looks for a matching schema name in the SYSPROCS catalog by examining each LIB in your *LIBL. This means you could have multiple definitions of the same stored proc in many schemas.

The one used depends on where the match is found in your job *LIBL. Nothing is actually in that schema library, it's just a placeholder (a key) in SYSPROCS. I normally think of the *LIBL as looking for a physical object but here it's looking for a row in a SYSPROCS table.

CREATE PROCEDURE MYLIB1/MYPROC ...
CREATE PROCEDURE MYLIB2/MYPROC ...
CREATE PROCEDURE MYLIB3/MYPROC ...

*LIBL = 'MYLIB2 MYLIB3 MYLIB1' so CALL MYPROC would use the MYLIB2 definition.

2. A stored proc can be overloaded. This means the same stored proc name could have many different parameter signatures. The system matches by the number of parms and each parm type.

CALL MYPROC(INTEGER1 CHAR2 DECIMAL3 NUMERIC4)
CALL MYPROC(INTEGER1 VARCHAR2)
CALL MYPROC(INTEGER1 VARCHAR2 INTEGER3)

These have the same stored proc name but different parm signatures. They could all 3 call different RPG *PGMs. The 2nd and 3rd could call the same RPG *PGM with the 3rd parm as OPTION(*NOPASS). SQL calling a *PGM sets %PARMS in RPG, no problem. SQL calling a *SRVPGM sub-procedure does *not* set %PARMS, big problem.

The unique key in this case in SYSPROCS in the SPECIFIC_NAME column. IBM i detects a duplicate stored procedure name and assigns a unique name here (unless you assign it yourself) on the CREATE PROCEDURE. This unique name is alway the link to SYSPARMS. Most of the time, your stored procedure name column is the same as the unique name column.

If the system does not find that MYPROC defined with the exact parm definitions in SYSPARMS, you will get an error, "could not be found".

3. The external object *PGM/*SRVPGM can be found in the *LIBL by simply leaving off the leading Exernal Name 'LIBRARY/'. You have do this from the SQL command line, not while F4 prompting. Prompting does not allow a value of *LIBL, I don't know why.

EXTERNAL NAME MYPGM
EXTERNAL NAME MYSRVPGM(MYSUBPROC)

You will see '*LIBL/MYPGM' and '*LIBL/MYSRVPGM(MYSUBPROC)' in SYSPROCS column EXTERNAL_NAME. Or the library name must be filled in when you do the CREATE PROCEDURE statement. You can't 'softcode' this library name as far as I know.

4. Also know that a system restore of such registered programs can automagically create duplicate entries of your stored procs defintions. It's not pretty. I've had to drop them all for a given name sometimes and recreate them. Would be nice if the restore command had a parm that said UPDSTRPRC(*NO).

http://www.itjungle.com/mgo/mgo082003-story02.html

Last thing, SQL does not support unsigned numbers, so don't define any of your external name RPG parms as unsigned: 3u0 5u0 10u0 20u0. It will crash.

I hope that helps some...

Chris Ringer

Hi Chris. Thanks for the long explanation, I appreciate it. I think the biggest thing is, having to qualify the external program name in the sproc because as I promote it through turnover from development up thru production, the enviroments program library changes.

Here's an example:

CREATE

PROCEDURE UpdateGiftCard (

in p_ordno dec(8,0), -- SODA order number

in p_lineno dec(4,0), -- SODA line number

in p_shipamt dec(9,2), -- shipping amount

in p_shpmeth char(20), -- shipping method

in p_shipdt dec(8,0), -- shipping date

in p_cardno char(25), -- card number

in p_cardamt char(6), -- card amount

in p_trackno char(20) -- tracking number

)external

name mm610dvp/sod565 <--- the library has to change to mm610itg for QA, then mm610pgm for production

language

RPGLE

parameter

styleSQL

I'm trying to figure out if somehow, I can softcode this. Also, since BizTalk calling the sproc does not have a *libl, it just has the data library, I need a way of figuring out where the program has to be called from based on the data library that they are calling the sproc from.

Does this make sense?

Thanks again!

Chris

Could you create 3 or 4 stored proc definitions?

1. mm610dvp/UpdateGiftCard-> mm610dvp/sod565

2. mm610itg/UpdateGiftCard-> mm610itg/sod565

3. mm610pgm/UpdateGiftCard-> mm610pgm/sod565

4. datalib/UpdateGiftCard-> mm610pgm/sod565

If you stack your dev *LIBL like this 'mm610dvp mm610itg mm610pgm' then the first 3 definitions could be exactly the same  mm610pgm/UpdateGiftCard-> *LIBL/sod565 .

Chris Ringer

Can you put the stored proc in the data library? That's where we put our trigger programs (though they were RPG, not native SQL). You need to override the target object library on your promotion forms, which is probably a locked field, so you'll need cooperation / permission from the TurnOver administrator.