SQL stored procedures question

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

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.        
____________________________________________________________