forstie/GO SAVE Option 21 history via SQL.sql

## GO SAVE Option 21 history via SQL.sql
--
-- ========================================================
-- 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;
	--
	-- ========================================================
	-- 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;