Thanks All!
The consensus is to create the SQLPKG object and deploy it using the change management software.
Now the problem is that the Change Management product doesn't have an *SQLPKG object type. I guess that I'll have to invent it.
Steve Needles
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, September 16, 2015 2:56 PM
To: midrange-l@xxxxxxxxxxxx
Subject: 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.
--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
________________________________
This communication, including attachments, is confidential, may be subject to legal privileges, and is intended for the sole use of the addressee. Any use, duplication, disclosure or dissemination of this communication, other than by the addressee, is prohibited. If you have received this communication in error, please notify the sender immediately and delete or destroy this communication and all copies.
TRVDiscDefault::1201
midrange.com
