Created
June 15, 2019 05:58
-
-
Save forstie/0bafbde6fbab5daf7639113403da580f to your computer and use it in GitHub Desktop.
Restoring libraries that begin with the letter E
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- ================================================= | |
-- 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; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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