Three-part names and QXQCLIPKGx SQL packages

[Jonathan Ball]

Three-part naming is a great DRDA feature. Unfortunately, it doesn't
work right out of the box.


select authorization_name, maximum_allowed_storage, storage_used,
text_description
from remotesys.qsys2.user_info
where maximum_allowed_storage is not null
and storage_used / maximum_allowed_storage >= .75

SQL State: 51002
Vendor Code: -805
Message: [SQL0805] SQL package QSQCLIPKGN in QGPL not found at DRDA Server.


I know how to create the packages using the DB2PackageCreator JAVA class
(http://www-01.ibm.com/support/docview.wss?uid=nas8N1017528), but I
don't know why those packages wouldn't already exist

[Jonathan Ball]

Sorry, I fat-fingered the package name in the subject line the first
time; corrected.

[Dieter Bender]

"Jonathan Ball" <jonb...@gmail.com> schrieb im Newsbeitrag
news:Yblvz.282$6d....@fx26.iad...

> Three-part naming is a great DRDA feature.

... it's nothing rally new or even great!!! it's simply the same than
redirecting interactive STRSQL or QMQRY to an outfile, working since SQL is
on the AS/400. TFM says: "If the alias-name is qualified, the name can be a
two-part or three-part name.
The schema name should not be a system schema."

My knowledge of english language is limited and I don't exactly understand
isf "should not" is meaning "must not" or "it would be nice, if it doesn't"

D*B

[CRPence]

On 24-Aug-2016 10:55 -0700, Jonathan Ball wrote:
> Three-part naming is a great DRDA feature. Unfortunately, it doesn't
> work right out of the box.
>
> select authorization_name, maximum_allowed_storage, storage_used
> , text_description
> from remotesys.qsys2.user_info
> where maximum_allowed_storage is not null
> and storage_used / maximum_allowed_storage >= .75
>
> SQL State: 51002
> Vendor Code: -805
> Message: [SQL0805] SQL package QSQCLIPKGN in QGPL not found at DRDA
> Server.

AFaIK, the three-part-naming feature functions fine without any of
those packages, when that query originates on the server [with a truly
*LOCAL connection] vs when that query is requested from a client; that
has been my experience anyhow, as none of the systems on which I have
access, currently have those SQL CLI packages, and testing verifies that
there were no errors, plus the expected result-set was returned.

>
> I know how to create the packages using the DB2PackageCreator JAVA

> class (http://www.ibm.com/support/docview.wss?uid=nas8N1017528),

> but I don't know why those packages wouldn't already exist

Perhaps seemingly odd, that some product\option installation does not
just effect the creation of those packages, but there is a legitimate
reason. That is, SQL packages are recorded locally for the creation on
the remote DRDA server. Not only must the remote server have been
registered as an entry at the local system [in the Relational DataBase
Directory Entry (RDBDIRE)], and each server properly defined with a
*LOCAL entry, but the connection to the remote servers must be available
[for which normal system ops is required at the requester, thus
post-install, and also normal system ops at the remote server], on which
the packages are created.

--
Regards, Chuck

[Jonathan Ball]

On 8/24/2016 3:27 PM, CRPence wrote:
> On 24-Aug-2016 10:55 -0700, Jonathan Ball wrote:
>> Three-part naming is a great DRDA feature. Unfortunately, it doesn't
>> work right out of the box.
>>
>> select authorization_name, maximum_allowed_storage, storage_used
>> , text_description
>> from remotesys.qsys2.user_info
>> where maximum_allowed_storage is not null
>> and storage_used / maximum_allowed_storage >= .75
>>
>> SQL State: 51002
>> Vendor Code: -805
>> Message: [SQL0805] SQL package QSQCLIPKGN in QGPL not found at DRDA
>> Server.
>
> AFaIK, the three-part-naming feature functions fine without any of
> those packages, when that query originates on the server [with a truly
> *LOCAL connection] vs when that query is requested from a client; that
> has been my experience anyhow, as none of the systems on which I have
> access, currently have those SQL CLI packages, and testing verifies that
> there were no errors, plus the expected result-set was returned.

You are right: when I try to use three-part naming using the SQL Script
window (cwbundbs.exe) that is served by a QZDASSINIT job on the local
system, the query fails if the QSQCLIPKGN package is not found on the
remote system. If I run the same query in a STRSQL session on the local
system, it executes and returns the expected result set.

I'd love to know why there is a difference.

[Jonathan Ball]

Now there is even more, and worse. I learned that when I tried to use
three-part naming in a SQL scripts window and it failed due to the
missing QSQCLIPKGx packages on the remote system, the failure caused a
required QSQLPKG2 package in QSYS to be deleted and then recreated.
However, the "created by user" value in the object description now shows
my profile, and it is supposed to be *IBM. This, according to IBM, is
why our SAVSYS operations are failing on those systems.

[CRPence]

On 29-Aug-2016 19:34 -0500, Jonathan Ball wrote:
> Now there is even more, and worse. I learned that when I tried to
> use three-part naming in a SQL scripts window and it failed due to
> the missing QSQCLIPKGx packages on the remote system, the failure
> caused a required QSQLPKG2 package in QSYS to be deleted and then
> recreated. However, the "created by user" value in the object
> description now shows my profile, and it is supposed to be *IBM.
> This, according to IBM, is why our SAVSYS operations are failing on
> those systems.

FWiW, I suspect the actual Service OIR information [i.e. DSPOBJD
QSQLPKG2 *SQLPKG *SERVICE] that is incorrect, for which there is an
error, is the LPP detail; i.e. the object is not registered as being
part of the OS. The "created by user" is unlikely tested; being part of
the *FULL OIR details rather than *SERVICE.

Tie-back to another topic:
[7.3 CHKPRDOPT fails after latest set of group
ptf's](http://archive.midrange.com/midrange-l/201609/msg00613.html)

In a followup to that message, now in a prior month archive, I ask
[in (http://archive.midrange.com/midrange-l/201610/msg00056.html)] "Any
word on preventive for the improper Service OIR information when the
package(s) are created on the server rather than restored from a backup
or restored for an install?" Same question here. Note: I also give
direction to effect recovery without re-install or [if supported,
directly] restoring from a SAVSYS backup; a save file with the QSQLPKG2
and QSQXDPKG SQL Package objects -- if the system used is still a v7r2
[i.e. IBM i 7.2], as I can find no mention in this message thread of
what OS level or maintenance.

--
Regards, Chuck