RE: SQL stored procedures question

["Panayiotis Petrou" <panayiotis.petrou@xxxxxxxxxxxxxxxxxxxx>]

I did this and still get the file not found. Is there an APAR that needs to
be applied for this to work.

Panayiotis (Pete) Petrou
zVSE and Websphere MQ System Administration
Information Technology Division
Bank Of Cyprus
Tel 357-22-12-8473
 
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Thursday, December 10, 2009 9:35 AM
To: 'Midrange Systems Technical Discussion'
Subject: AW: SQL stored procedures question

Hi,

the problem is, you are using *SQL-Naming for your stored procedure. When
using *SQL Naming you only can access data (files/Tables, Views, Indexes)
within a single schema/library without qualifying the objects. The library
where the data is searched is either the library with the name of the
current user profile (Default) or the library set by executing the SQL
Command SET CURRENT SCHEMA. With *SQL Naming the library is NOT searched.

The SQL Path is used with *SQL Naming but only to find Stored Procedures or
User Defined Functions and never to find Data (files/Tables, Views or
Indexes).

As I see, you need to access files/tables located within several libraries.
To search these files within the library list, you need to create your
stored procedure with *SYS-Naming. Per Default iSeries Navigator uses *SQL
Naming. The easiest way to move to *SQL Naming is, to retrieve the Script of
your procedure (GENERATE SQL) and switch to *SYS-Naming (JDBC-Setup). If you
are using qualified access within your procedure, change the .-Separator to
/.

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: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Panayiotis Petrou
Gesendet: Thursday, 10. December 2009 06:54
An: 'Midrange Systems Technical Discussion'
Betreff: SQL stored procedures question

We are new to iseries and we are trying to develop some inhouse apps using
SQL stored procedures and RPG ILE

We have created thru iseries navigator a stored procedure (TESTP) which we
call from an RPG program.

When we perform this call we get error SQL0204 *FILE not found on the file
TIPF.

If we prefix the file name with the library where the file resides it works.

What do we need to do in order to get this to work without prefixing the
file with the library.

 

Thanks

 

 

****************************************************************************
*************************

RPG PROGRAM

****************************************************************************
*************************

 

0001.00 ** h specification


0002.00 hdebug


0006.00


0006.01 d wksql1          s            500a


0006.02 d wksql2          s            500a


0006.03 d wksqlp1         s              7a


0006.05 d wksqlp2         s             10a


0006.06 d wksqlp3         s              3a


0009.00 ***************************************

0010.00  /free


0011.01      wksql1 = 'CALL TESTP(?,?,?)';


0011.02      wksqlp1 = '';


0011.03      wksqlp2 = '';


0011.04      wksqlp3 = ' ';


0011.05      EXEC SQL SET OPTION NAMING =*SYS;


0011.06      EXEC SQL PREPARE wksql2 FROM :wksql1;


0011.07      EXEC SQL EXECUTE WKSQL2 using :wksqlp1,:wksqlp2,:WKSQLP3;


0015.00     //dump;


0016.00     *inlr=*on;


0017.00     return;


0018.00  /end-free


0019.00 ***************************************

0020.00 ** sub-routine definitions


0021.00 c     *inzsr        begsr


 

 

 

****************************************************************************
*************************

SQL STORED PROCEDURE

****************************************************************************
*************************

 

--  Generate SQL 

--  Version:                   V5R4M0 060210 

--  Generated on:                        09/12/09 11:12:29 

--  Relational Database:              S65509EB 

--  Standards Option:                  DB2 UDB iSeries 

 

 

 

 

--SET PATH "QSYS","QSYS2","KFILJON","D681" ; 

 

CREATE PROCEDURE TEST.TESTP ( 

            OUT PO_OUT1 CHAR(7),

            OUT PO_OUT2 CHAR(10), 

            OUT PO_OUT3 CHAR(3) ) 

            LANGUAGE SQL 

            SPECIFIC TEST.TESTP 

            NOT DETERMINISTIC 

            MODIFIES SQL DATA 

            CALLED ON NULL INPUT 

            SET OPTION  ALWBLK = *ALLREAD , 

            ALWCPYDTA = *OPTIMIZE , 

            COMMIT = *CHG , 

            DECRESULT = (31, 31, 00) , 

            DFTRDBCOL = *NONE , 

            DYNDFTCOL = *NO , 

            DYNUSRPRF = *USER , 

            SRTSEQ = *HEX   

            BEGIN 

  

 

-- FILE **** TIPF **** IS LOCATED IN **** KFILJON **** LIBRARY   

 

 

SET PO_OUT1 = (         SELECT SUBSTR ( TISCTL , 135 , 7 ) 

                        FROM TIPF ) ; 

 

 

 

 

 

-- FILE **** YBRBRTPF **** IS LOCATED IN **** CYRATES **** LIBRARY   

 

SET PO_OUT2 = (         SELECT BASE 

                        FROM YBRBRTPF 

                        WHERE ABRCODE = 'KR'

                        AND DATEC = '16032009'

);

  

 

 

 

-- FILE **** YEAFICPF **** IS LOCATED IN **** CYEAS **** LIBRARY   

  

SET PO_OUT3 = (         SELECT POLICYTYPE 

                        FROM YEAGICPF 

                        WHERE POLICYNUM = '1234567890'

);

 

END  ;

 

****************************************************************************
*************************

CONSOLE RESULT WHEN WE CALL THE RPG PROGRAM  ************ COMMAND
SYSTEM/DSPJOB

****************************************************************************
*************************

PREPARE of statement WKSQL2 completed. 

TIPF in D681 type *FILE not found.     

TIPF in D681 type *FILE not found.     

 

****************************************************************************
*************************

LIBRARY LIST                                 ************ COMMAND
EDTLIBL

****************************************************************************
*************************

 

   0                

  10    TEST        

  20    CYRATES     

  30    BOCJONLIB   

  40    CYCOL       

  50    KINPJON     

  60    KFILJON     

  70    KWRKJON     

  80    KLIBJON     

  90    LIBK092Z    

 100    REPE39JON   

 110    REPEQNJON   

 120    KAPBASELIB  

 130    QGPL        

 140    QTEMP       

 

Panayiotis (Pete) Petrou

zVSE and Websphere MQ System Administration

Information Technology Division

Bank Of Cyprus

Tel 357-22-12-8473

 

____________________________________________________________ 
The information contained or attached to this email is            
intended only for the use of the individual or entity to whom     
it is addressed and it may contain confidential or legally        
privileged information. If you are not the intended recipient     
of this email, note that any disclosure, copying, distribution    
or use of its contents is strictly prohibited.  In such case      
you should notify the sender immediately by responding to         
this email and delete this email and any attachment from your     
system. All reasonable precautions have been taken to ensure      
that no viruses are present in this email.  We cannot accept      
any responsibly for any loss or damage arising from the use       
of this email or attachments and we recommend that you subject    
these to your virus checking procedure.                           
Any views expressed in this communication are those of the        
individual sender, except where the sender specifically states    
them to be the views of Bank of Cyprus Public Company Ltd.        
____________________________________________________________