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

On 22-Sep-2015 12:31 -0600, Needles,Stephen J wrote:

Ok... The consensus solution was to explicitly create an *SQLPKG on
the remote LPAR/server. This won't work because the program is never
installed to the remote LPAR/server and an *SQLPKG object apparently
cannot exist unless it is. I need new ideas.

I am unsure I understand that description. The /program/ need not exist on the remote RDB, only the *SQLPKG object; only an effective copy of the SQL statements need exist there, such that the Print SQL Information (PRTSQLINF) of both the local program and the remote SQL Package are effectively the same.

The Create SQL Package (CRTSQLPKG) command can be run where the program-object was created, specifying the RDB where the package otherwise would be implicitly created; the creator being a user profile that exists on both the system where the CRTSQLPKG command was issued. Or instead of compiling the program and then issuing a separate CRTSQLPKG, just specifying the Package information on the compile request should suffice to create the local program and the remote package [i.e. each of the Relational Database (RDB), RDB Connect Method (RDBCNNMTH), and SQL Package Name (SQLPKG): RDB(named_vs_*LOCAL) RDBCNNMTH(*as_appropriate) SQLPKG(Lib/Obj)]; again, the user issuing the compile, being one that exists on the target.

Here's the deal:

I've a process that will run on a local LPAR/server that will consume
data from a remote LPAR/server. The program will only exist and run
on the local LPAR/server, never on the remote.

The program uses 3-part naming in its SQL to identify the remote
server.schema.table. Because of this, the program will normally
implicitly create the *SQLPKG on the remote LPAR/server. In the
compile command (CRTSQLRPGI) of the local program object, this
*SQLPKG is directed to be created in QTEMP. No reason for QTEMP
beyond ease of testing. This will allow the local program to process
remote data.

As Charles indicted earlier in the thread, the implicitly created
remote *SQLPKG object is created using the object creator, not the
object owner or job current user. It doesn't use the profile used to
establish the CONNECTION either. Because the object creator doesn't
exist on the remote LPAR/server, the implicit creation of the
*SQLPKG fails.

Ideas?

Seems reasonable to open an issue with the service provider to ask for clarification on the failure of the implicit creation. Perhaps even searching the symptoms seen for the failure, might identify an existing preventive PTF.? The target processing by the SQL might just be deemed reasonable to have coded to recover from the failure, by retrying using the current-user of the job; maybe there is no security exception for using something other than the original package creator.?