Re: Impact of SQL Packages

Unfortunately, I can't run the delivered programs in debug on the current system as they were not delivered with that option.
I also see no SQL error messages in the joblog.

The only way I can get debug is by recompiling them from source I had to also move there, and that's when the problem disappears (when it's recompiled).
The embedded SQL is all for local data, no remote databases. Also, no library specific references in the SQL statements.
I don't see anything in the PRTSQLINF report that would lead me to see a failure.

Maybe the SQL Package stuff isn't the problem? I just jumped on it because I see no other attributes change from the delivered object to the recompiled object.



Best Regards,,

Thomas Garvey



On 1/9/2015 12:42 PM, CRPence wrote:

On 09-Jan-2015 11:52 -0600, Thomas Garvey wrote:

I have a number of RPGLE programs with embedded SQL that were
created and compiled on one system, then moved to another system.

Might be of some value to collect a Print SQL Information (PRTSQLINF) for at least one object [described later in this message as failing] to see what is recorded there; minimally, for a program not yet run, the access plan should be invalid. For the first run of a program that is not functioning [perhaps restore anew for a repeated test], run the program in debug to see what additional optimizer and SQL messages are logged to show what transpires.

The original object library on the original system and the
destination library on the destination system are not named the
same.

The programs were created-into and saved from a library [e.g. LIBSRC] and then restored to another system; the programs were either restored or moved into a library of a different name [e.g. LIBTGT].

The 'DB2 for IBM i module attributes:' section has a reference
to SQL Package Name which includes the original library name.

The SQLPKG object is, like a JRN object, tied to the original library; those object types can not be relocated to a library of a different name. The PGM can be (re)located, effectively without any restriction by the OS, to any library; only the dependencies coded in the program source would pose an issue.

The default for the CRTSQLxxx is to generate that name, identical to the object name being created; i.e. SQLPKG(*OBJLIB/*OBJ)

Though the Display Program (DSPPGM) details for the modules implies the value for the SQL Package name may be *NONE, for the programs created a CRTSQLxxx command for which there is the SQL Package (SQLPKG) parameter, for the lack of the single\special-value of *NONE, that attribute will always be set.... irrespective the lack of conspicuous meaning.

This library does not exist on the current system.

That should not matter for the program. Relevance for the existence is limited to the target system for a CONNECT statement. If the program runs only *LOCAL, the value appearing in the SQL Package is AFaIK moot.

I am trying to track down why the program does not work correctly on
the current system (The SQL statements are not selecting any records
from the data file).

And the effect being sqlcode=0100 was verified for those statements? I have seen many times that the return codes for SQL statements are not tested, and the lack of results is nothing to do with records not being selected, but instead that [sometimes all of] the statements failed; e.g. if the program can not achieve a /connected state/ then no statements will run.

When I simply recompile the program on the current system, the only
thing that's different is the SQL Package Name Library reference (it
now points to the object's library), the program works fine.

The recompile almost surely has the default SQLPKG(*OBJLIB/*OBJ), so that difference is expected [albeit since seeing that this issue was raised, perhaps there should be the capability to specify SQLPKG(*NONE)]. The fact that there is a new compiled program object is almost surely what was the /recovery/; almost surely unrelated to the noted /difference/.

I know nothing about SQL Packages, how or why they are created, or
why my program has a reference to one (which is not found anywhere on
either system, at least using an object type of *SQLPKG).

Normally the SQL Package is created to /package/ the statements that are stored in the [associated space of the] program, for access to those statements at a target system\RDB. IIRC, when the program issues a statement while connected to a remote database, the package is referenced for the equivalent statement, and that statement [stored on the remote system] is executed on the remote system; the *SQLPKG object is created on the remote system.

Could this difference be my problem? If so, why?

Given the SQLPKG is seemingly an unknown, and as already alluded, I think that difference is unlikely to have any bearing.

Thanks for any advice.

If a program that exhibited the problem can be restored and then called to exhibit the problem anew, then I infer likely the situation as described is a defect. A [SQL] program compiled on one system and then restored to another system should function [generally speaking] when called on that other system.