RE: SQL0805 in SQL RPG but no issue in STRSQL or ACS Run SQL Script -- RPG400-L

Adding below SQL solved the SQL0805 issue, but now throws SQL0818 because of the OS release difference
Exec SQL Set Option Naming=*SYS, UsrPrf=*USER;

Thanks Craig,

-----Original Message-----
From: Francois Lavoie
Sent: Monday, November 11, 2019 11:01
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SQL0805 in SQL RPG but no issue in STRSQL or ACS Run SQL Script

That's why I use slashes instead of dots in the path name.
No matter what Naming convention used by any systems *SYS or *SQL, they will not throw an error based on file naming

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Craig Richards
Sent: Monday, November 11, 2019 10:01
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL0805 in SQL RPG but no issue in STRSQL or ACS Run SQL Script

Are you using the same SQL Naming convention in all scenarios (System Naming vs SQL Naming) because there are differences with regard to owners and authority depending on which you use.

Birgitta provides some details here if you scroll down:

https://www.ibm.com/developerworks/ibmi/library/i-sqlnaming/index.html

regards,
Craig

On Mon, 11 Nov 2019 at 14:52, Francois Lavoie < Francois.Lavoie@xxxxxxxxxxxxxxxxxxxx> wrote:

Why below command work fine in Interactive SQL (STRSQL) or ACS Run SQL
Script, but fails in SQL RPG ?

The issuing system is at v7.3, remote systems are at v5.4, 6.1, 7.1
and 7.2. When both systems are at v7.3, no issue, only when remote is
at lower release Looks like the CRTSQLPKG is looking for the OWNER
user profile but program is compiled with USER All latest PTFs are
installed on all systems including similar problem from APAR SE70306

Any help would be much appreciated

Thanks in advance

[cid:image001.png@01D59871.8347E0F0]

Runtime error message in joblog
Additional Message Information

Message ID . . . . . . : SQL0805
Date sent . . . . . . : 11/11/19 Time sent . . . . . . :
08:48:00

Message . . . . : SQL package RSDFM1000A in QGPL not found at DRDA
Server.

Cause . . . . . : A remote request was attempted to ISPEED for SQL
package
RSDFM1000A in QGPL. The SQL package was not found. If you are using
Interactive SQL or Query Manager, an attempt to create a package on the
remote system failed and the package requested does not exist.
Recovery . . . : The most common cause of this problem in an
Interactive
SQL session to a server that is not DB2 for IBM i is that the
connection is
not updateable. In that case the package cannot be automatically
created.
To ensure the connection is updateable, do a RELEASE ALL command
followed by
a COMMIT before connecting to the relational database.
In other cases, the SQL package can be created by using the CRTSQLPKG
command. Also, precompiling an SQL program with the RDB parameter
specified
will create an SQL package on the system. Create or restore the SQL
package.
Run the application again.
If you are using Interactive SQL or Query Manager or SQL Call Level
Interface, exit the product or application and enter a CL COMMIT or
ROLLBACK
command. This will enable you to continue processing at the local
system.
Determine why the package creation failed by examining the job log.
Correct
the problem and attempt the Interactive SQL or Query Manager
session again.

Runtime SQL generated Spool file
5770SS1 V7R3M0 160422 Create SQL package
11/11/19 08:48:01 Page 1
Record *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6
...+... 7 ...+... 8 SEQNBR Last change
Program name..............FDEV/RSDFM100 Relational
database.......ISPEED User .....................QSECOFR
Replace...................*YES Default collection........*PGM
Generation level..........31 Printer file..............*LIBL/QSYSPRT
Object type...............*PGM Module list...............*ALL
Text......................*PGMTXT Source file...............FDEV/QRS
Member....................RSDFM1000A
5770SS1 V7R3M0 160422 Create SQL package
11/11/19 08:48:01 Page 2
Record *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6
...+... 7 ...+... 8 SEQNBR Last change
700 DELETE FROM UPDFF01 WHERE UPSYST = : H
DIAGNOSTIC MESSAGES MSG
ID SEV RECORD TEXT
SQL0204 10 700 Position 13 UPDFF01 in *LIBL type *FILE not found.
SQL0204 30 FPGMR in QSYS type *USRPRF not found.
SQL5056 SQL package creation for module RSDFM1000A failed.
Package name was to be RSDFM1000A in QGPL at ISPEED.
Message Summary
Total Info Warning Error Severe Terminal
2 0 1 0 1 0
30 level severity errors found in source
* * * * * E N D O F L I S T I N G *
* *
* *

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com