RE: sql continue handler

What is the goal? To ensure that not only is MYLIB in the library list, but it's the last entry in the library list?
If it's already in the library list remove it and ensure that it's at the end?

Normally the logic in an all CL situation is I just want to be able to use the objects in the library so I:
Try to add the library to the library list
Do my process
If I added the library to the library list just to do my process then remove it now. If it was already there then leave it there.

RMVLIBLE may not always remove a library from your library list. For example if it is your CURRENT library.

But let's talk about your continue handler. I'm a newbie but I think it's all wrong.
BEGIN is used at the beginning of the stored procedure. Not at the beginning of a continue handler.

So I find this confusing:

BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '38501'
CALL QSYS2.QCMDEXC('RMVLIBLE LIB(myLib)');
END;
CALL QSYS2.QCMDEXC('ADDLIBLE LIB(myLib) POSITION(*LAST)');

So let's reformat it to:
Myproc: BEGIN

DECLARE CONTINUE HANDLER FOR SQLSTATE '38501'
CALL QSYS2.QCMDEXC('RMVLIBLE LIB(myLib)');

CALL QSYS2.QCMDEXC('ADDLIBLE LIB(myLib) POSITION(*LAST)');

END Myproc;

Now what this is saying is Don't execute the RMVLIBLE unless the addlible fails, and ONLY if the addlible fails.
So if MYLIB was already in your library list at the beginning it will no longer be.
If MYLIB was not in your library list at the beginning it will now be.

See an example of a continue handler below

Sample program:
CREATE PROCEDURE Change_IFS_Owner
(IN CURRENT_OWNER VARCHAR(10),
IN NEW_OWNER VARCHAR(10),
IN START_PATH VARCHAR(50) )
LANGUAGE SQL MODIFIES SQL DATA
SET OPTION DATFMT = *ISO
P1: BEGIN
DECLARE WORK_PATH_NAME VARCHAR(500);
DECLARE COMMAND CHAR(500); -- Ensure this is large enough.
DECLARE END_TABLE INT DEFAULT 0;
DECLARE C1 CURSOR FOR
select path_name
-- 'CHGOWN OBJ(''' concat path_name concat ''') NEWOWN(' concat new_owner concat ')'
--, QCMDEXC('CHGOWN OBJ(''' concat path_name concat ''') NEWOWN(' concat new_owner concat ')')
FROM TABLE (QSYS2.IFS_OBJECT_STATISTICS(START_PATH_NAME => START_PATH,
SUBTREE_DIRECTORIES => 'YES'))
WHERE object_owner=CURRENT_OWNER
and path_name not like '%' concat x'7D' concat '%';

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET END_TABLE = 1;
-- DECLARE EXIT HANDLER FqsysOR SQLEXCEPTION
-- SET DEPT_SALARY = NULL;
OPEN C1;
FETCH C1 INTO WORK_PATH_NAME;
WHILE END_TABLE = 0 DO
SET COMMAND = 'CHGOWN OBJ(''' concat work_path_name concat ''') NEWOWN(' concat new_owner concat ')';
CALL QSYS2.QCMDEXC(COMMAND);
FETCH C1 INTO WORK_PATH_NAME;
END WHILE;
CLOSE C1;
END P1


Rob Berendt