Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Use SQL to retrieve the QUSRSYS/QSRSAV21 *DTAARA and transform the detail therewithin into consumable history.
-- Author: Scott Forstie
-- Date : June 28, 2019
-- Email : forstie@us.ibm.com
--
-- ==============
-- Basic Version
-- ==============
--
--
-- Details for the previous GO SAVE operations are found
-- within the QUSRSYS/QSRSAV21 *DTAARA
--
-- https://www-01.ibm.com/support/docview.wss?uid=nas8N1010666
--
select data_area_value
from qsys2.data_area_info
where data_area_library = 'QUSRSYS'
and data_area_name = 'QSRSAV21';
stop;
--
-- ==================
-- Advanced Version
-- ==================
--
-- category: IBM i Services
-- 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)
)
begin
declare pos int default 1;
declare step int default 1;
declare step_count int;
declare dtaara_guts varchar(2000) ccsid 37;
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
pipe (
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;
return;
end;
stop;
select * from table(systools.go_save_info()) order by step_time;
stop;
--
-- description: Study elapsed time of GO SAVE option 21
--
with SAVSYS(step_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 = 'SAVSYS'
),
ENDSAV(step_time, row_num) as (
select step_time,
row_number() over (order by step_time) as row_num
from table(systools.go_save_info())
where step_name = 'GO SAVE Complete'
)
select s.row_num, device, s.step_time, e.step_time,
timestampdiff(8, cast(e.step_time - s.step_time as char(22)))
as GOSAVE_hours,
timestampdiff(4, cast(e.step_time - s.step_time as char(22)))
as GOSAVE_minutes
from SAVSYS s inner join ENDSAV e on s.row_num = e.row_num;
stop;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.