Skip to content

Instantly share code, notes, and snippets.

@forstie
Last active August 25, 2023 15:36
Show Gist options
  • Save forstie/16ac3d3ea0234988e5dce7e3f0876b12 to your computer and use it in GitHub Desktop.
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.
-- 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;
@forstie
Copy link
Author

forstie commented Apr 28, 2023

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment