Skip to content

Instantly share code, notes, and snippets.

@forstie
Last active October 6, 2023 11:42
Show Gist options
  • Save forstie/561094b33eb7adcdf8822e5651277ac5 to your computer and use it in GitHub Desktop.
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.
--
-- ========================================================
-- 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;
@thebeardedgeek
Copy link

Scott, when was data_area_info in QSYS2 introduced? When I tried to run the first statement in this gist I received this error: Message: [SQL0204] DATA_AREA_INFO in QSYS2 type *FILE not found. I am running V7R2.

@farios2504
Copy link

farios2504 commented Sep 18, 2019 via email

@forstie
Copy link
Author

forstie commented Sep 19, 2019

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

@forstie
Copy link
Author

forstie commented Jun 14, 2022

Just improved this gist

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