Last active
October 6, 2023 11:42
-
-
Save forstie/561094b33eb7adcdf8822e5651277ac5 to your computer and use it in GitHub Desktop.
Use SQL to retrieve the QUSRSYS/QSRSAV21 *DTAARA and transform the detail therewithin into consumable history.
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
-- | |
-- ======================================================== | |
-- iSee - GO SAVE Option 21 ( Save Entire system ) deetz | |
-- ======================================================== | |
-- | |
-- Author: Scott Forstie | |
-- Date : September, 2023 | |
-- Email : forstie@us.ibm.com | |
-- | |
-- Details for the previous GO SAVE Option 21 operations are found | |
-- within the QUSRSYS/QSRSAV21 *DTAARA | |
-- | |
-- Documentation: | |
-- https://www.ibm.com/support/pages/estimating-how-long-go-save-option-21-takes | |
-- | |
select data_area_value, DATA_AREA_LIBRARY | |
from qsys2.data_area_info | |
where data_area_library = 'QUSRSYS' and | |
data_area_name = 'QSRSAV21'; | |
stop; | |
-- | |
-- description: Review previous GO SAVE option 21 steps | |
-- | |
create or replace function systools.go_save_info () | |
returns table ( | |
step_time timestamp(0), step_name varchar(16), device varchar(10) | |
) | |
not deterministic | |
modifies sql data | |
external action | |
set option usrprf = *user, dynusrprf = *user | |
begin | |
declare pos int default 1; | |
declare step int default 1; | |
declare meter int; | |
declare step_count int; | |
declare at_end int; | |
declare dtaara_guts varchar(2000) ccsid 37; | |
declare v_step_name varchar(16) for sbcs data; | |
declare v_step_time timestamp(0); | |
declare v_start_of_savsys timestamp(0); | |
declare found_a_savsys integer; | |
declare sav21 cursor for | |
select step_name, step_time | |
from SESSION.SAV21_INFO | |
order by step_time asc; | |
declare global temporary table SESSION.SAV21_INFO ( | |
step_time timestamp(0), step_name varchar(16), device varchar(10)) | |
with REPLACE; | |
set dtaara_guts = (select data_area_value | |
from qsys2.data_area_info | |
where data_area_library = 'QUSRSYS' and | |
data_area_name = 'QSRSAV21'); | |
set step_count = length(rtrim(dtaara_guts)) / 50 + 1; | |
while (step <= step_count) do | |
insert into SESSION.SAV21_INFO | |
values (timestamp_format(substr(dtaara_guts, pos, 14), 'YYYYMMDDHH24MISS'), | |
case | |
when rtrim(substr(dtaara_guts, pos + 15, 10)) = '********' then 'GO SAVE Complete' | |
else rtrim(substr(dtaara_guts, pos + 15, 10)) | |
end, substr(dtaara_guts, pos + 26, 10)); | |
set pos = pos + 50; | |
set step = step + 1; | |
end while; | |
-- Remove any SAVSYS's that do not reach GO SAVE complete! | |
begin | |
declare continue handler for not found set at_end = 1; | |
open sav21; | |
set at_end = 0; | |
set meter = 1; | |
set found_a_savsys = 0; | |
fetch from sav21 | |
into v_step_name, v_step_time; | |
call systools.lprintf('Fetched step: ' concat v_step_name); | |
while (at_end = 0 and meter < 50) do | |
set meter = meter + 1; | |
if (v_step_name = 'SAVSYS') then | |
if (found_a_savsys = 1) then | |
delete from SESSION.SAV21_INFO where step_time >= v_start_of_savsys and | |
step_time < v_step_time; | |
call systools.lprintf('Deleting rows'); | |
else | |
set v_start_of_savsys = v_step_time; | |
set found_a_savsys = 1; | |
end if; | |
end if; | |
if (v_step_name = 'GO SAVE Complete') then | |
if (found_a_savsys = 1) then | |
set v_start_of_savsys = null; | |
set found_a_savsys = 0; | |
end if; | |
end if; | |
set at_end = 0; | |
fetch from sav21 | |
into v_step_name, v_step_time; | |
call systools.lprintf('Fetched step: ' concat v_step_name); | |
end while; | |
close sav21; | |
end; | |
return select step_time, step_name, device | |
from SESSION.SAV21_INFO; | |
end; | |
stop; | |
-- | |
-- Save steps | |
-- | |
select * | |
from table ( | |
systools.go_save_info() | |
) | |
order by step_time asc; | |
stop; | |
-- | |
-- Add in OLAP | |
-- | |
select step_time, device, step_name, row_number() over ( | |
order by step_time | |
) as row_num | |
from table ( | |
systools.go_save_info() | |
); | |
stop; | |
-- | |
-- description: Study elapsed time of GO SAVE option 21 | |
-- | |
with SAVE_Start (step_start_time, device, row_num) as ( | |
select step_time, device, row_number() over ( | |
order by step_time | |
) as row_num | |
from table ( | |
systools.go_save_info() | |
) | |
where step_name in ('SAVSYS') | |
), | |
Earliest_Save (step_time) as ( | |
select min(step_start_time) | |
from SAVE_Start | |
), | |
SAVE_End (step_end_time, row_num) as ( | |
select e.step_time, row_number() over ( | |
order by e.step_time | |
) as row_num | |
from Earliest_Save b, table ( | |
systools.go_save_info() | |
) e | |
where step_name = 'GO SAVE Complete' and | |
e.step_time > b.step_time | |
) | |
select s.row_num, device, s.step_start_time, e.step_end_time, | |
timestampdiff(8, cast(e.step_end_time - s.step_start_time as char(22))) | |
as GOSAVE_hours, | |
timestampdiff(4, cast(e.step_end_time - s.step_start_time as char(22))) | |
as GOSAVE_minutes | |
from SAVE_Start s inner join SAVE_End e on s.row_num = e.row_num | |
where timestampdiff(4, cast(e.step_end_time - s.step_start_time as char(22))) > 0; | |
stop; | |
-- | |
-- description: Study elapsed time of GO SAVE option 21 | |
-- | |
create or replace view systools.go_save21_info as | |
with SAVE_Start (step_start_time, device, row_num) as ( | |
select step_time, device, row_number() over ( | |
order by step_time | |
) as row_num | |
from table ( | |
systools.go_save_info() | |
) | |
where step_name in ('SAVSYS') | |
), | |
Earliest_Save (step_time) as ( | |
select min(step_start_time) | |
from SAVE_Start | |
), | |
SAVE_End (step_end_time, row_num) as ( | |
select e.step_time, row_number() over ( | |
order by e.step_time | |
) as row_num | |
from Earliest_Save b, table ( | |
systools.go_save_info() | |
) e | |
where step_name = 'GO SAVE Complete' and | |
e.step_time > b.step_time | |
) | |
select s.row_num as save_num, device, s.step_start_time, e.step_end_time, | |
timestampdiff(8, cast(e.step_end_time - s.step_start_time as char(22))) | |
as GOSAVE_hours, | |
timestampdiff(4, cast(e.step_end_time - s.step_start_time as char(22))) | |
as GOSAVE_minutes | |
from SAVE_Start s inner join SAVE_End e on s.row_num = e.row_num | |
where timestampdiff(4, cast(e.step_end_time - s.step_start_time as char(22))) > 0; | |
stop; | |
-- | |
-- description: GO SAVE option 21 info | |
-- | |
select * from systools.go_save21_info; | |
stop; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hi,
DATA_AREA_INFO only exists on IBM i 7.3 and higher.
You can find release and Db2 Fix pack level support detail here: http://ibm.biz/Db2foriServices
Scott