Re: Impact of SQL Packages

On 09-Jan-2015 13:03 -0600, Thomas Garvey wrote:

Unfortunately, I can't run the delivered programs in debug on the
current system as they were not delivered with that option.

The additional messaging available from the optimizer and the SQL, comes with /debug/ being _active_ within the job; or the QAQQINI option requesting debug messages. Just Start Debug (STRDBG) without any program specified; no need to start debug on the SQL program itself:
STRDBG PGM(*NONE) UPDPROD(_as_appropriate)

I also see no SQL error messages in the joblog.

Because SQL is return-code based rather than using messaging to communicate to the invoker, some "error messages" may never appear in a joblog; e.g. msg SQL0100 is a valid but also possibly error condition, as alluded in the OP, if data was expected but apparently the program thinks none was received. As well, the logging-level may be relevant, as some SQL messages that are issued may be filtered; actual errors however, do generally appear in the joblog, in my experience, .

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

As noted initially, just STRDBG; the SQL programs need not be debug capable, because the /debug messaging/ is merely additional information being logged because the _job_ is running with the debug environment activated.

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.

As alluded in my prior reply, I do believe the SQLPKG() is unrelated; unless the program had specified a target RDB() and thus the package object was generated on the target RDB\system, that SQLPKG() attribute plays no role because the connection is implicitly *LOCAL.

Again, if the program can not operate when restored to the other system [into whatever library], then any of trace, debug, or the query\SQL debug messaging\feedback feature may be used in hopes of identifying an issue. In general, a program that was transported and then no longer functions on the new system is suspect for how the OS [language run-time], SQL run-time, and query\database features are operating with the restored object; the program generally should not require a recompile unless the source or run-time environment knowingly is incompatible with the expectations and actual effects of the original environment in which the program was successfully performing.