Re: Consume Web service

[Jon Paris <jon.paris@xxxxxxxxxxxxxx>]

OOppsss - never mind.


> On Jan 19, 2016, at 8:05 AM, rob@xxxxxxxxx wrote:
>
> Hey, does anyone know what web service IBM uses in the 
> systools.group_ptf_currency view?
> Maybe I could create my own to select groups there that I don't currently 
> have (a current limitation of this IBM view).
>
> IBM Navigator for i's "Generate SQL" to the rescue.
>
> --  Generate SQL 
> --  Version:                    V7R2M0 140418 
> --  Generated on:               01/19/16 07:36:00 
> --  Relational Database:        GDISYS 
> --  Standards Option:           DB2 for i 
> SET CURRENT DECFLOAT ROUNDING MODE  ROUND_HALF_EVEN ; 
>
> CREATE VIEW SYSTOOLS.GROUP_PTF_CURRENCY FOR SYSTEM NAME GRPPTFCUR ( 
>        PTF_GROUP_CURRENCY FOR COLUMN GRP_CRNCY  , 
>        PTF_GROUP_ID FOR COLUMN GRP_ID     , 
>        PTF_GROUP_TITLE FOR COLUMN GRP_TITLE  , 
>        PTF_GROUP_LEVEL_INSTALLED FOR COLUMN GRP_LVL    , 
>        PTF_GROUP_LEVEL_AVAILABLE FOR COLUMN GRP_IBMLVL , 
>        PTF_GROUP_LAST_UPDATED_BY_IBM FOR COLUMN GRP_LSTUPD , 
>        PTF_GROUP_RELEASE FOR COLUMN GRP_RLS    , 
>        PTF_GROUP_STATUS_ON_SYSTEM FOR COLUMN GRP_SYSSTS ) 
>        AS 
>        SELECT 
>  CASE WHEN ACTUAL.GRPPTF IS NULL THEN 'PTF GROUP DOES NOT EXIST ON ' 
> CONCAT CURRENT SERVER 
>       WHEN PSPS.PSP_NUMBER IS NULL THEN 'PSP INFORMATION NOT AVAILABLE' 
>       WHEN ACTUAL.GRPPTF = PSPS.PSP_NUMBER AND ACTUAL.PTF_GROUP_LEVEL = 
> PSPS.PSP_LEVEL THEN 'INSTALLED LEVEL IS CURRENT' 
>       WHEN ACTUAL.GRPPTF = PSPS.PSP_NUMBER AND ACTUAL.PTF_GROUP_LEVEL < 
> PSPS.PSP_LEVEL THEN 'UPDATE AVAILABLE' 
>       WHEN ACTUAL.GRPPTF = PSPS.PSP_NUMBER AND ACTUAL.PTF_GROUP_LEVEL > 
> PSPS.PSP_LEVEL THEN 'PSP IS DOWNLEVEL -  ' CONCAT ACTUAL.PTF_GROUP_STATUS 
> END
>  PTF_GROUP_CURRENCY, COALESCE(PSPS.PSP_NUMBER, ACTUAL.GRPPTF) 
> PTF_GROUP_ID, COALESCE(PSPS.PSP_TITLE, 
>  ACTUAL.PTF_GROUP_DESCRIPTION) PTF_GROUP_TITLE, ACTUAL.PTF_GROUP_LEVEL 
> PTF_GROUP_LEVEL_INSTALLED, PSPS.PSP_LEVEL 
>  PTF_GROUP_LEVEL_AVAILABLE, PSPS.PSP_DATE AS 
> PTF_GROUP_LAST_UPDATED_BY_IBM,   COALESCE(PSPS.PSP_RELEASE,
>  ACTUAL.PTF_GROUP_TARGET_RELEASE) PTF_GROUP_RELEASE, 
> ACTUAL.PTF_GROUP_STATUS PTF_GROUP_STATUS_ON_SYSTEM 
> FROM XMLTABLE('/all_psps/psp' PASSING XMLPARSE(DOCUMENT 
> SYSTOOLS.HTTPGETBLOB('http://www-912.ibm.com/s_dir/sline003.nsf/PSPbyNumL.xml?OpenView&count=500' 
> , ''))
> COLUMNS PSP_RELEASE CHAR(5) PATH 'release', PSP_NUMBER CHAR(7) PATH 
> 'number', PSP_TITLE VARCHAR(1000) PATH 'title',
>  PSP_LEVEL INTEGER PATH 'level', PSP_DATE CHAR(10) PATH 'date' ) PSPS
> RIGHT OUTER JOIN ( SELECT SUBSTR(PTF_GROUP_NAME, 1,7) AS GRPPTF, 
> PTF_GROUP_LEVEL, PTF_GROUP_STATUS, PTF_GROUP_DESCRIPTION,
>  PTF_GROUP_TARGET_RELEASE 
> FROM     ( SELECT PTF_GROUP_NAME,PTF_GROUP_LEVEL, PTF_GROUP_STATUS, 
> PTF_GROUP_DESCRIPTION,   PTF_GROUP_TARGET_RELEASE, 
>         RANK() OVER (PARTITION BY PTF_GROUP_NAME 
>     ORDER BY PTF_GROUP_LEVEL DESC) AS INSTALLED_NUMBER 
>     FROM QSYS2.GRPPTFINFO 
>     WHERE PTF_GROUP_STATUS = 'INSTALLED') A 
> WHERE A.INSTALLED_NUMBER = 1 ) ACTUAL 
> ON (ACTUAL.GRPPTF = PSPS.PSP_NUMBER) 
>        RCDFMT GRPPTFCUR  ; 
>
> COMMENT ON TABLE SYSTOOLS.GROUP_PTF_CURRENCY 
>        IS 'DB2 FOR IBM i SUPPLIED OBJECT VERSION 07100310001' ; 
>
> GRANT SELECT 
> ON SYSTOOLS.GROUP_PTF_CURRENCY TO PUBLIC ; 
>
> GRANT ALTER , REFERENCES , SELECT 
> ON SYSTOOLS.GROUP_PTF_CURRENCY TO QSYS WITH GRANT OPTION ; 
>
>
> Now I just need to play with this.  Hurts that I don't know jack about XML 
> and stuff
> I go to 
> http://www-912.ibm.com/s_dir/sline003.nsf/PSPbyNumL.xml?OpenView&count=500
> and I see some xml.
> I see that the sql above has FROM XMLTABLE('all_psps/psp' and I this at 
> that site:
> <all_psps>
> <psp>
> <release>R540</release>
> <number>SF99114</number>
> <title>540 IBM HTTP Server for i</title>
> <level>36</level>
> <date>07/09/2013</date>
> </psp>
> <psp>
> <release>R540</release>
> <number>SF99143</number>
> <title>540 Performance Tools</title>
> <level>8</level>
> <date>06/06/2013</date>
> </psp>
> ...
> </all_psps>
>
> So I can tie that together.
>
> Now I can see how they get
> COLUMNS PSP_RELEASE CHAR(5) PATH 'release'
>
> And the 
> RIGHT OUTER JOIN
> Explains why I don't get the group ptf numbers that IBM has that I do not.
>
> I suspect the reason IBM does a RIGHT OUTER JOIN is because they don't 
> select by "release".  Even the software products that have their own 
> release (DB2 web query comes to mind) displays a release of V7R2M0 in 
> WRKPTFGRP when you do a 5 to display, and a format of R720 in the xml and 
> not some product specific release.
>
> Rob Berendt
> -- 
> IBM Certified System Administrator - IBM i 6.1
> Group Dekko
> Dept 1600
> Mail to:  2505 Dekko Drive
>          Garrett, IN 46738
> Ship to:  Dock 108
>          6928N 400E
>          Kendallville, IN 46755
> http://www.dekko.com
>
> -- 
> 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.
>
> Please contact support@xxxxxxxxxxxx for any subscription related questions.

Jon Paris

www.partner400.com
www.SystemiDeveloper.com