Restoring libraries that begin with the letter E

Restoring libraries that begin with the letter E.sql

-- =================================================
-- author: Scott Forstie
-- date  : May 29, 2019
-- email : [email protected]
-- disclaimer - no implied warranties, yada yada
-- =================================================

--
-- Super Fast retrieval of library and schema name
--
select objname as library_name,
       objlongname as schema_name
  from table (
      qsys2.object_statistics('*ALLSIMPLE', 'LIB')
    ) z
  order by 1 asc;
stop;

--
-- description: Find libraries that begin with E
--
select objname as library_name,
       objlongname as schema_name,
       z.*
  from table (
      qsys2.object_statistics('*ALLSIMPLE', 'LIB')
    ) z
  where objname like 'E%'
  order by 1 asc;
stop;

--
-- description: Create a printf to joblog tool
--
cl:addlible qsysinc;
cl:crtsrcpf qtemp/qcsrc;
cl:addpfm file(qtemp/qcsrc) mbr(LPRINTF);
insert into qtemp.qcsrc values
 (1,010101,'{'),                                                  
 (2,010101,'extern int Qp0zLprintf (char *format, ...);'),      
 (3,010101,'Qp0zLprintf("%.*s\n", LPRINTF.PRINT_STRING.LEN, LPRINTF.PRINT_STRING.DAT);'),                                     
 (4,010101,'}');

CREATE OR REPLACE PROCEDURE systools.LPRINTF(Print_string VARCHAR(1000) ccsid 37)
SET OPTION BINDOPT = 'BNDSRVPGM(QSYS/QP0ZCPA)'
BEGIN
  IF Print_string IS NOT NULL THEN 
    INCLUDE QTEMP/QCSRC(LPRINTF); 
  END IF;
END;             
stop;

--
-- description: restore libraries that begin with E
--
create or replace procedure systools.lib_restore_E()
begin
declare libname varchar(10);
declare command_string varchar(1000) ccsid 37;
DECLARE NOT_FOUND CONDITION FOR '02000' ; 
DECLARE AT_END INTEGER DEFAULT 0 ; 

declare libs_that_begin_with_e cursor for 
  select objname as library_name
  from table (
      qsys2.object_statistics('*ALLSIMPLE', 'LIB')
    ) z
  where objname like 'E%'
  order by 1 asc;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET AT_END = 1 ; 
DECLARE CONTINUE HANDLER FOR NOT_FOUND SET AT_END = 1 ; 

open libs_that_begin_with_e;
fetch from libs_that_begin_with_e into libname;
WHILE ( AT_END = 0 ) DO 
  set command_string =  'RSTLIB SAVLIB(' concat libname concat ') DEV(*SAVF) SAVF(QGPL/' concat libname concat ') RSTLIB(' concat libname concat ')';
  call systools.LPRINTF(command_string);
  begin 
    declare verror integer;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET verror = 1 ; 
    call qsys2.qcmdexc(command_string);
  end;
  fetch from libs_that_begin_with_e into libname;
END WHILE ; 
close libs_that_begin_with_e;

end;
stop;

--
-- description: Restore libraries begin with an input search string
--
create or replace procedure systools.lib_restore_flex(p_search_criteria varchar(10) default 'E%')
set option output = *print
begin
declare libname varchar(10);
declare command_string varchar(1000) ccsid 37;
DECLARE NOT_FOUND CONDITION FOR '02000' ; 
DECLARE AT_END INTEGER DEFAULT 0 ; 
declare libs_cursor_stmt_text clob(2K) ccsid 37;
declare libs_cursor cursor for libs_cursor_stmt;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET AT_END = 1 ; 
DECLARE CONTINUE HANDLER FOR NOT_FOUND SET AT_END = 1 ; 

set libs_cursor_stmt_text = 'select objname as library_name ' 
  concat ' from table(qsys2.object_statistics(''*ALLSIMPLE'', ''LIB'')) z '
  concat ' where objname like ?  order by 1 asc ';
prepare libs_cursor_stmt from libs_cursor_stmt_text;
open libs_cursor using p_search_criteria;
fetch from libs_cursor into libname;
WHILE ( AT_END = 0 ) DO 
  set command_string =  'RSTLIB SAVLIB(' concat libname concat ') DEV(*SAVF) SAVF(QGPL/' concat libname concat ') RSTLIB(' concat libname concat ')';
  call systools.LPRINTF(command_string);
  begin 
    declare verror integer;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET verror = 1 ; 
    call qsys2.qcmdexc(command_string);
  end;
  fetch from libs_cursor into libname;
END WHILE ; 
close libs_cursor;

end;
stop;

Very interrest SQL code to integrate with Tape Library but i think that Its not possible to save more than one library to *SAVF (error CPF3789 on SAVLIB with DEV=*SAVF ); and then its not possible to restore a group of libraries from *SAVF device type