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

Have you read the enhances text for SQL0818?

Message ID . . . . . . . . . : SQL0818

Message file . . . . . . . . : QSQLMSG

Library . . . . . . . . . : QSYS

Message . . . . : Consistency tokens do not match.

Cause . . . . . : Package &3 in &4 on application server &5 cannot be run

with the current application program because either the application
program
has been recompiled without rebuilding the package or the package has been

restored from a back level version.

Recovery . . . : Rebuild the package by using the CRTSQLPKG command or by

using a CRTSQLxxx command specifying the correct relational database.

Otherwise, restore the package from a version which matches the
application
program being run.

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!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Francois
Lavoie
Sent: Montag, 11. November 2019 19:44
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

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