The SYSTOOLS.CVE_INFO table function does not provide the list of corrective PTFs

Thank you for sharing. I also tried to implement this, but we’re heavily dependent on the HTML page format, which changes. We also think it would be a good idea to provide a view that directly shows what’s installed on our system. By including PTF_INFO at the end, it should give us an overview of what’s currently applicable. 

I created an SQL table function for extracting the PTF's...

CREATE OR REPLACE FUNCTION PAULN.EXTRACT_IBM_PTFS (

P_URL VARCHAR(2048),

P_IBMI_RELEASE VARCHAR(10)

)

RETURNS TABLE (

RELEASE_VER VARCHAR(10),

PTF_NUMBER VARCHAR(10),

GROUP_LEVEL INTEGER

)

LANGUAGE SQL

MODIFIES SQL DATA

BEGIN

RETURN

WITH HTML_DATA AS (

-- 1. Haal de HTML-content op
SELECT CAST(QSYS2.HTTP_GET(P_URL, '') AS CLOB(2M)) AS CONTENT

FROM SYSIBM.SYSDUMMY1

),

-- 2. Splits de HTML op in tabelrijen en CAST naar VARCHAR(4000) voor geheugenveiligheid
TABLE_ROWS (ROW_TEXT, POS) AS (

SELECT

CAST(REGEXP_SUBSTR(CONTENT, '(?i)<tr[^>]*>.*?</tr>', 1, 1, 'n') AS VARCHAR(4000)),

REGEXP_INSTR(CONTENT, '(?i)<tr[^>]*>.*?</tr>', 1, 1, 0, 'n')

FROM HTML_DATA

UNION ALL

SELECT

CAST(REGEXP_SUBSTR(CONTENT, '(?i)<tr[^>]*>.*?</tr>', POS + 1, 1, 'n') AS VARCHAR(4000)),

REGEXP_INSTR(CONTENT, '(?i)<tr[^>]*>.*?</tr>', POS + 1, 1, 0, 'n')

FROM HTML_DATA, TABLE_ROWS

WHERE POS > 0

AND REGEXP_INSTR(CONTENT, '(?i)<tr[^>]*>.*?</tr>', POS + 1, 1, 0, 'n') > 0

),

-- 3. Filter op rijen die de release in de 1e cel hebben
TARGET_ROWS AS (

SELECT ROW_TEXT

FROM TABLE_ROWS

WHERE ROW_TEXT IS NOT NULL

AND REGEXP_LIKE(ROW_TEXT, '([A-Z]{2}[0-9]{5}|SF99[0-9]{3})', 'n')

AND REGEXP_LIKE(

REGEXP_SUBSTR(ROW_TEXT, '(?i)<(td|th)[^>]*>.*?</\1>', 1, 1, 'n'),

P_IBMI_RELEASE,

'n'

)

),

-- 4. Getallenreeks 1 t/m 20
NUMBERS AS (

SELECT ROWNUMBER() OVER() AS N

FROM QSYS2.SYSTABLES

FETCH FIRST 20 ROWS ONLY

),

-- 5. UITGEBREID: Haal PTF-nummer én optioneel Group Level op
EXTRACTED_PTFS AS (

SELECT

P_IBMI_RELEASE AS RELEASE_VER,

-- Haal het N-de PTF-nummer (of SF99 group-nummer) uit de rij
REGEXP_SUBSTR(T.ROW_TEXT, '([A-Z]{2}[0-9]{5}|SF99[0-9]{3})', 1, N.N, 'n') AS PTF_NUMBER,

-- Extra REGEXP_SUBSTR: Haal het getal achter "Level" op als het een Group PTF betreft
CASE

WHEN REGEXP_SUBSTR(T.ROW_TEXT, '([A-Z]{2}[0-9]{5}|SF99[0-9]{3})', 1, N.N, 'n') LIKE 'SF99%' THEN

CAST(VARCHAR_FORMAT(REGEXP_SUBSTR(T.ROW_TEXT, '(?i)Level\s*([0-9]+)', 1, 1, 'n', 1)) AS INTEGER)

ELSE NULL

END AS GROUP_LEVEL

FROM TARGET_ROWS T

CROSS JOIN NUMBERS N

)

-- 6. Uniek resultaat teruggeven
SELECT DISTINCT RELEASE_VER, PTF_NUMBER, GROUP_LEVEL

FROM EXTRACTED_PTFS

WHERE PTF_NUMBER IS NOT NULL;

END;

This allows you to

SELECT RELEASE_VER, PTF_NUMBER, GROUP_LEVEL

FROM TABLE(PAULN.EXTRACT_IBM_PTFS('https://www.ibm.com/support/pages/node/7247402', '7.6'));

So far it seems to work but it would indeed be nice if IBM could provide a webservice for this as well.