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.
midrange.com
