Re: implied CRTSQLPKG weirdness - uses creator of program as RDB user in creating SQLPKG?

On 16-Sep-2015 12:33 -0600, Needles,Stephen J wrote:

We are at V7R2M0 on all referenced IBMi's.

I've a process (RPG with imbedded SQL) that will read data from a
remote IBMi DB, process it, persist it to the local IBMi DB, delete
the remote row and move to the next in a loop.

This thing works just fine in the development environments. When the
need for an SQLPKG arises, the package is automatically created on
the remote as needed. The program object is created using a profile
that is available on both the remote and local machines. As weird as
this seems to me, this will become important.

As we move to the next level of testing, the creator is that of the
move coordinator, who does not have a valid user profile on the
remote machine. This seems to be causing an issue.

FWiW: The SQL maintains the SQL Package Creator (column DBXPCRT of QADBPKG) separately from the SQL Package Owner (column DBXPOWN of QADBPKG); the latter is used to effect ownership assignment, and the former would seem likely to be used to effect creation.

The information for the QADBPKG data comes from the package-specific details stored in the Program Associated Space (PAS) of the program. Some subset of the PAS data is visible via Print SQL Information (PRTSQLINF) corresponds, though neither of the owner nor the creator; the SQLPKG owner is the object-owner, but the SQLPKG creator is likely visible in the PAS, by viewing output from the request either to Dump Object (DMPOBJ) of the program to Dump System Object (DMPSYSOBJ) [optionally used to explicitly dump just the PAS using SPACE(0 *)].

As the programs runs, the need to create the SQLPKG on the remote
arises. Rather than use the profile that is running the process as
the RDB user ID, it is retrieving the move coordinator profile that
created the program to create the SQLPKG. Because this profile does
not exist on the remote machine, the package creation fails. Which
causes the process to fail.

Likely the SQL is using the /creator/ information from the PAS as the user to create the package.

5770SS1 V7R2M0 140418 -Create SQL package- 09/16/15 09:25:05 Page 1
<<SNIP>>

MSG ID ... TEXT
SQL0204 ... THE_MOVE_COORDINATOR in QSYS type *USRPRF not found.
SQL5056 ... SQL package creation for module PROGRAM_NAME failed.
Package name was to be PROGRAM_NAME in QTEMP at REMOTE_SYSTEM.

Not sure how to proceed.

The SQL package could be explicitly created, avoiding the need for the implicit creation. Create SQL Package (CRTSQLPKG).

While it appears that the SQL0204 error is the killer, I can't even
find SQL5056 on IBM's website.

The SQL5056 is just describing the overall effect, that the creation failed; the -204 is the condition that led to that overall failure.

The following request [to Work With Message Description (WRKMSGD)] provides the ability to see the additional details for the message; e.g. using 5=Display:
WRKMSGD SQL5056 QSQLMSG

Notably, the second level text suggests the recovery is to "Review the messages on the listing and correct the problems. Try the request again." and the prior SQL0204 is the issue requiring a correction.

I expect that I'll have to ask IBM about it, but thought I would
share this with you all.