Last active
August 25, 2023 15:36
-
-
Save forstie/16ac3d3ea0234988e5dce7e3f0876b12 to your computer and use it in GitHub Desktop.
The request... show how SQL can be used to narrow the field down to those PTFs that have special instructions, and only return the special instructions. The following example focuses on what an admin might do AFTER loading PTFs, but BEFORE applying them.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Subject: PTF Cover Letters | |
-- Author: Scott Forstie | |
-- Date : April, 2023 | |
-- Features Used : This Gist uses qsys2.ptf_info, SQL Alias, ltrim, rtrim(), dynamic SQL, SQL PL, PIPE | |
-- | |
-- Note: | |
-- 1) Whether PTF Cover Letters exist on your IBM i is up to whomever ordered the PTFs | |
-- Use the Copy PTF Cover Letter (CPYPTFCVR) command to bring in PTF cover letters | |
-- https://www.ibm.com/docs/en/i/7.5?topic=ssw_ibm_i_75/cl/cpyptfcvr.html | |
-- 2) The file QGPL/QAPZCOVER *FILE contains one member for each PTF Cover Letter | |
-- | |
-- Reference: https://www.ibm.com/docs/en/i/7.4?topic=services-ptf-info-view | |
stop; | |
-- | |
-- Which PTFs are loaded, but not yet applied? | |
-- | |
select p.* | |
from qsys2.ptf_info p | |
where ptf_loaded_status = 'LOADED'; | |
stop; | |
-- | |
-- Which PTFs are loaded, but not yet applied, and there is a PTF cover letter? | |
-- | |
select PTF_IDENTIFIER | |
from qsys2.ptf_info p | |
where ptf_loaded_status = 'LOADED' and PTF_COVER_LETTER = 'YES'; | |
stop; | |
-- | |
-- Can SQL be used to read PTF cover letters? | |
-- | |
create or replace alias qtemp.ptf_cover_letter for qgpl.qapzcover(QSI82567); | |
select * from qtemp.ptf_cover_letter; | |
stop; | |
-- | |
-- Read the PTF Cover Letters of PTFs that are loaded, but not yet applied | |
-- | |
create or replace function coolstuff.ptf_cover_letters(ptf_predicate varchar(200) for sbcs data default 'LOADED-ONLY') | |
returns table ( | |
cover_text char(80) for sbcs data | |
) | |
not deterministic | |
external action | |
modifies sql data | |
fenced | |
set option COMMIT = *NONE | |
begin | |
declare local_sqlcode integer; | |
declare local_sqlstate char(5) for sbcs data; | |
declare v_message_text varchar(70) for sbcs data; | |
declare not_found condition for '02000'; | |
declare at_end integer default 0; | |
declare v_ptf_id varchar(7) for sbcs data; | |
declare v_ptf_cover_letter_text char(80) for sbcs data; | |
declare loaded_ptfs_cursor_stmttext varchar(10000) for sbcs data; | |
declare ptf_cover_cursor_stmttext varchar(10000) for sbcs data; | |
declare ptf_cover_cursor cursor for ptf_cover_cursor_statement; | |
declare loaded_ptfs_cursor cursor for loaded_ptfs_cursor_statement; | |
declare exit handler for sqlexception | |
begin | |
declare local_sqlcode integer; | |
declare local_sqlstate char(5) for sbcs data; | |
declare v_message_text varchar(200) for sbcs data; | |
get diagnostics condition 1 | |
local_sqlcode = db2_returned_sqlcode, local_sqlstate = returned_sqlstate, | |
v_message_text = message_text; | |
call systools.lprintf('coolstuff.loaded_ptfss() FAILED WITH SQLCODE=' concat local_sqlcode | |
concat ' SQLSTATE=' concat local_sqlstate concat ' MESSAGE= ' concat v_message_text); | |
return; | |
end; | |
declare continue handler for not_found set at_end = 1; | |
if (ptf_predicate = 'LOADED-ONLY') then | |
set loaded_ptfs_cursor_stmttext ='select PTF_IDENTIFIER from qsys2.ptf_info p where ptf_loaded_status = ''LOADED'' and PTF_COVER_LETTER = ''YES'' '; | |
else | |
set loaded_ptfs_cursor_stmttext ='select PTF_IDENTIFIER from qsys2.ptf_info p ' concat ptf_predicate; | |
end if; | |
prepare loaded_ptfs_cursor_statement from loaded_ptfs_cursor_stmttext; | |
open loaded_ptfs_cursor; | |
set at_end = 0; | |
-- Fetch a PTF identifier | |
fetch from loaded_ptfs_cursor into v_ptf_id; | |
ptfs: while (at_end = 0) do | |
call systools.lprintf('PTF: ' concat v_ptf_id); | |
execute immediate 'create or replace alias qtemp.cover_letter for qgpl.qapzcover(Q' | |
concat v_ptf_id concat ')'; | |
set ptf_cover_cursor_stmttext = 'select qapzcover from qtemp.cover_letter'; | |
prepare ptf_cover_cursor_statement from ptf_cover_cursor_stmttext; | |
open ptf_cover_cursor; | |
set at_end = 0; | |
-- Access the PTF Cover letter | |
fetch from ptf_cover_cursor into v_ptf_cover_letter_text; | |
cover_letters: while (at_end = 0) do | |
-- Project the text as a row returned by this UDTF | |
pipe (v_ptf_cover_letter_text); | |
set at_end = 0; | |
fetch from ptf_cover_cursor into v_ptf_cover_letter_text; | |
end while; | |
close ptf_cover_cursor; | |
set at_end = 0; | |
fetch from loaded_ptfs_cursor into v_ptf_id; | |
end while; | |
close loaded_ptfs_cursor; | |
return; | |
end; | |
stop; | |
-- | |
-- Read the PTF Cover letters of loaded, but not applied PTFs | |
-- | |
select * | |
from table ( | |
coolstuff.ptf_cover_letters() | |
); | |
stop; | |
-- | |
-- Confirm that we have 4 PTF cover letters, for the 4 loaded, but not yet applied PTFs | |
-- | |
select * | |
from table ( | |
coolstuff.ptf_cover_letters() | |
) where cover_text like 'PTF/FIX #%'; | |
stop; | |
-- | |
-- Read the PTF Cover Letters of PTFs that are loaded, but not yet applied | |
-- Include the PTF # as part of every row returned | |
-- | |
create or replace function coolstuff.ptf_special_instructions(ptf_predicate varchar(200) for sbcs data default 'LOADED-ONLY') | |
returns table ( | |
cover_text varchar(100) for sbcs data | |
) | |
not deterministic | |
external action | |
modifies sql data | |
fenced | |
set option COMMIT = *NONE | |
begin | |
declare local_sqlcode integer; | |
declare local_sqlstate char(5) for sbcs data; | |
declare v_message_text varchar(70) for sbcs data; | |
declare not_found condition for '02000'; | |
declare at_end integer default 0; | |
declare v_ptf_id varchar(7) for sbcs data; | |
declare v_special_instr_rrn integer; | |
declare v_default_instr_rrn integer; | |
declare v_ptf_cover_letter_text char(80) for sbcs data; | |
declare loaded_ptfs_cursor_stmttext varchar(10000) for sbcs data; | |
declare ptf_cover_cursor_stmttext varchar(10000) for sbcs data; | |
declare ptf_cover_cursor cursor for ptf_cover_cursor_statement; | |
declare loaded_ptfs_cursor cursor for loaded_ptfs_cursor_statement; | |
declare exit handler for sqlexception | |
begin | |
declare local_sqlcode integer; | |
declare local_sqlstate char(5) for sbcs data; | |
declare v_message_text varchar(200) for sbcs data; | |
get diagnostics condition 1 | |
local_sqlcode = db2_returned_sqlcode, local_sqlstate = returned_sqlstate, | |
v_message_text = message_text; | |
call systools.lprintf('coolstuff.loaded_ptfss() FAILED WITH SQLCODE=' concat local_sqlcode | |
concat ' SQLSTATE=' concat local_sqlstate concat ' MESSAGE= ' concat v_message_text); | |
return; | |
end; | |
declare continue handler for not_found set at_end = 1; | |
if (ptf_predicate = 'LOADED-ONLY') then | |
set loaded_ptfs_cursor_stmttext ='select PTF_IDENTIFIER from qsys2.ptf_info p where ptf_loaded_status = ''LOADED'' and PTF_COVER_LETTER = ''YES'' '; | |
else | |
set loaded_ptfs_cursor_stmttext ='select PTF_IDENTIFIER from qsys2.ptf_info p ' concat ptf_predicate; | |
end if; | |
prepare loaded_ptfs_cursor_statement from loaded_ptfs_cursor_stmttext; | |
open loaded_ptfs_cursor; | |
set at_end = 0; | |
-- Fetch a PTF identifier | |
fetch from loaded_ptfs_cursor into v_ptf_id; | |
ptfs: while (at_end = 0) do | |
call systools.lprintf('PTF: ' concat v_ptf_id); | |
execute immediate 'create or replace alias qtemp.cover_letter for qgpl.qapzcover(Q' | |
concat v_ptf_id concat ')'; | |
select rrn(ptfcl) into v_special_instr_rrn | |
from qtemp.cover_letter ptfcl where qapzcover like 'SPECIAL INSTRUCTIONS :%'; | |
select rrn(ptfcl) into v_default_instr_rrn | |
from qtemp.cover_letter ptfcl where qapzcover like 'DEFAULT INSTRUCTIONS :%'; | |
set ptf_cover_cursor_stmttext = 'select qapzcover from qtemp.cover_letter cl where rrn(cl) > ? and rrn(cl) < ? and | |
ltrim(rtrim(qapzcover)) not in (''----------------------'', | |
''********************************************************************'', | |
''THE FOLLOWING ARE SUPERSEDED SPECIAL INSTRUCTIONS. IF THE SUPERSEDED'', | |
''PTF HAS ALREADY BEEN APPLIED AND ITS SPECIAL INSTRUCTION FOLLOWED,'', | |
''IT IS NOT NECESSARY TO FOLLOW THAT SPECIAL INSTRUCTION AGAIN.'', | |
''None.'') | |
and length(rtrim(qapzcover)) > 0'; | |
prepare ptf_cover_cursor_statement from ptf_cover_cursor_stmttext; | |
open ptf_cover_cursor using v_special_instr_rrn, v_default_instr_rrn ; | |
set at_end = 0; | |
-- Access the PTF Cover letter | |
fetch from ptf_cover_cursor into v_ptf_cover_letter_text; | |
cover_letters: while (at_end = 0) do | |
-- Project the text as a row returned by this UDTF | |
pipe ('PTF ' concat v_ptf_id concat ':' concat v_ptf_cover_letter_text); | |
set at_end = 0; | |
fetch from ptf_cover_cursor into v_ptf_cover_letter_text; | |
end while; | |
close ptf_cover_cursor; | |
set at_end = 0; | |
fetch from loaded_ptfs_cursor into v_ptf_id; | |
end while; | |
close loaded_ptfs_cursor; | |
return; | |
end; | |
stop; | |
-- | |
-- Read only the Special Instructions of PTF Cover letters, for PTFs that are loaded, but not applied | |
-- | |
select * | |
from table ( | |
coolstuff.ptf_special_instructions() | |
); | |
stop; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
A good client asked me to enhance the final UDTF, to include the PTF #'s in the ptf_special_instructions() output.
ptf_special_instructions() is updated to include this improvement.