Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save forstie/0bafbde6fbab5daf7639113403da580f to your computer and use it in GitHub Desktop.
Save forstie/0bafbde6fbab5daf7639113403da580f to your computer and use it in GitHub Desktop.
Restoring libraries that begin with the letter E
-- =================================================
-- author: Scott Forstie
-- date : May 29, 2019
-- email : forstie@us.ibm.com
-- 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;
@ajut400
Copy link

ajut400 commented Jul 1, 2019

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

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