× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.