SQL Proceure Questions

[Alan Campin <Alan.Campin@xxxxxxx>]

I agree with what Charles is saying here except that user defined function can 
be any program object type. I have one written as a ILE/RPG service program. 
There are two classes of SQL Procedures, External and SQL. External procedures 
are just programs of any language that get called when you call the stored 
procedure name so you could say that they don't exist except as entry in the 
tables pointing to a program to run. 

The SQL Procedure, which I think you are talking about, is written in SQL and 
compiled to a C Pgm object but again the SQL Procedure name itself does not 
exist except in the tables. The tables points to the actual program name.

message: 6
date: Thu, 2 Jun 2005 07:48:19 -0400
from: "Wilt, Charles" <CWilt@xxxxxxxxxxxx>
subject: RE: SQL Proceure Questions

Mike,

Both SQL procedures and user defined functions exist as an object.

In the case of procedures, the object is a ILE C *PGM object.

For User defined functions, the object is an ILE C *SRVPGM object.

Here are a couple of examples from my system:
Object      Type        Attribute   Text
TESTSP      *PGM        CLE         SQL PROCEDURE TESTSP
HISTO00001  *SRVPGM     CLE         SQL FUNCTION HISTORICALCOST


Take a look at the EXTERNAL_NAME column of SYSPROCS & SYSROUTINES.  Procedures 
show up in both views.  User defined functions are only in SYSROUTINES.  Here's 
then results of a query, where SPECIFIC_SCHEMA = 'CWLIBR', of SYSROUTINES on my 
system:
SPECIFIC_NAME           EXTERNAL_NAME           ROUTINE_NAME            
ROUTINE_TYPE
---------------------   ------------------      --------------------    
------------
CVTCHARMDYTODATE        CWLIBR/CVTCH00001       CVTTODATE                       
FUNCTION
CVTNUMISOTODATE         CWLIBR/CVTNU00001               CVTTODATE               
        FUNCTION
HISTORICALCOST          CWLIBR/HISTO00001               HISTORICALCOST          
        FUNCTION
PARTSGOINGOUTOFSTOCK    CWLIBR/PARTS00001       PARTSGOINGOUTOFSTOCK    FUNCTION
TESTSP                  CWLIBR/TESTSP           TESTSP                          
PROCEDURE


As far as finding the procedure to be invoked.  Depends on rather *SYS or *SQL 
naming is in effect.  Basically, when *SYS naming is in effect, the library 
list is used.  When *SQL naming is in effect, the library list in not used.  
However, there is at v5r1 and higher an SQL Path used to find Stored Procedures 
and UDFs.  Here's a IBM document the covers this stuff:
http://www-912.ibm.com/s_dir/slkbase.NSF/0/2554db904e5a601b862565c2007cc4b8?OpenDocument

or http://tinyurl.com/cx2oz



HTH,

Charles Wilt
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121