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

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

On 8/24/2016 10:55 AM, 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.
>
>
> 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" <jonball52@gmail.com> schrieb im Newsbeitrag 
news:Yblvz.282$6d.166@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
 

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

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.

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

On 8/26/2016 11:11 AM, Jonathan Ball wrote:
> 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.

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.

>
> I'd love to know why there is a difference.
>
>>>
>>> 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.
>>
>

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