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