Unable to create DB2 Procedure through command line

I am trying to create a procedure to do a blanket revoking of executeauth for procedures from a schema. This is in line with trying to secure a non-restricted database.

CREATE PROCEDURE PROC_REV
    LANGUAGE SQL
    MODIFIES SQL DATA
    BEGIN
        DECLARE v_NAME VARCHAR(400);
        FOR v1 AS
        c1 CURSOR FOR
        select specificname from SYSCAT.ROUTINEAUTH where grantee='PUBLIC' and schema='SYSPROC' and routinetype='P'
        DO
            SET v_NAME = specificname;
            SET v_GrantQuery = 'revoke execute on specific procedure '|| v_NAME ||' from PUBLIC';
            EXECUTE IMMEDIATE v_GrantQuery;
        END FOR;
    END#

and this is the command I run to process the file with the code above.

db2 -td# -svf RoutineAuthRevoke.db2

However, I keep running into this error

SQL0553N An object cannot be created with the schema name "SYSFUN  ". LINE NUMBER=1. SQLSTATE 42939

I’m fairly new to DB2 and this is my first foray into writing db2 procedure scripts. Would anyone be able to spot the “SYSFUN ” because I sure as hell can’t. The only other way I can revoke 310 entries from SYSPROC for PUBLIC is through a batch file and I figured, procedures might be a cleaner way of achieving this. I would really appreciate any help with either this error or with the code itself.

Answer

A number of problems.

You can’t create a routine in the SYSFUN schema as the error message shows. You get this message because the statement VALUES CURRENT SCHEMA returns SYSFUN in your session. You must either run the SET SCHEMA SOME_VALID_SCHEMA_NAME statement before CREATE or use fully qualified routine name like SOME_VALID_SCHEMA_NAME.PROC_REV.

Variable v_GrantQuery is not defined in the routine.

According to the syntax of REVOKE (routine privileges) statement, you should generate the REVOKE statement using fully qualified routine name and with RESTRICT clause at the end. The easiest way to do it with a compound statement (you don’t need to create a routine for that):

BEGIN
    FOR v1 AS
        select 'REVOKE EXECUTE ON SPECIFIC PROCEDURE "' || schema ||'"."'|| specificname || '" FROM PUBLIC RESTRICT' AS STMT
        from SYSCAT.ROUTINEAUTH 
        where grantee='PUBLIC' and schema='SYSPROC' and routinetype='P'
    DO
        EXECUTE IMMEDIATE v1.STMT;
    END FOR;
END#