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;
@thebeardedgeek

This comment has been minimized.

Copy link

@thebeardedgeek thebeardedgeek commented Sep 18, 2019

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

This comment has been minimized.

Copy link

@farios2504 farios2504 commented Sep 18, 2019

@forstie

This comment has been minimized.

Copy link
Owner Author

@forstie 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

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