RE: SQL Proceure Questions

["Wilt, Charles" <CWilt@xxxxxxxxxxxx>]

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
 

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Mike 
> Pantzopoulos -
> (H/O)
> Sent: Wednesday, June 01, 2005 8:48 PM
> To: midrange-l@xxxxxxxxxxxx
> Subject: SQL Proceure Questions
> 
> 
> I'm having a couple of conceptual problems with SQL Procedures. Some
> insight into the following would be greatly appreciated:
> 
> *     There doesn't seem to be an actual procedure 'object' in the
> AS400 sense of the word. 
> *     I presume that it exists purely as an SQ" construct. Just like
> an SQL trigger or a table.
> *     The SQL catalog tables, SYSPROCS & SYSROUTINES seem to contain
> the procedure definition.
> *     The manual seems to imply there's a C version somewhere, but I'm
> unsure of where or what the object type might be.
> *     SQL seems to use current library reference to determine where to
> put the procedure reference.
> *     If the current library isn't set, then QGPL is the default.
> *     I'm uncertain what happens when the procedure needs to be
> invoked. I'm using a trigger as the interface (because I'm trying to
> trap an obscure bug). Does it use the library list to find 
> the procedure
> or does it use the current library reference? 
> Thanks in advance.
> 
> Mike Pantzopoulos
> 
> EIG-Ansvar Limited
> Tel  : 61 (3) 9614 3535
> Fax: 61 (3) 9614 2740
> 
> 
> 
> **************************************************************
> *******************************************
> This email and any files transmitted with it are confidential 
> and intended solely for the use of the individual or entity 
> to whom they are addressed. If you are not the intended 
> recipient, any use, disclosure or copying of this message is 
> unauthorised. If you have received this message in error, 
> please reply using the sender's email address. 
> 
> This footnote confirms that this email message has been 
> scanned for computer viruses. EIG-Ansvar Limited does not 
> accept liability for any loss or damage, whether caused by 
> our own negligence or not, that results from a computer virus 
> or defect in the transmission of this email or any attached file. 
> EIG-Ansvar Limited - Australia (A.B.N. 21 007 216 506)
> Email : insure@xxxxxxxxxxxxxxxx
> 
> Eig-Ansvar Limited - New Zealand
> Email : insure@xxxxxxxxxxxxxxxx
> 
> **************************************************************
> *******************************************
> 
> -- 
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
>