Restrict on drop

To prevent an unintended drop of a table, function, or procedure, the RESTRICT ON DROP attribute can be added to the definition of the object.

Table, physical file, or source physical file

RESTRICT ON DROP can be added to tables, physical files, or source physical files. It is enforced for all methods of deleting a table, physical file, or source physical file, including library level operations. It does not prevent using the OR REPLACE option to redefine the table. Since it is a file level attribute, it does not apply to requests to delete rows or remove a member.

When creating a table, it can be established like this:

CREATE TABLE TEST_TABLE (ID INT, NAME VARCHAR(50), ADDRESS VARCHAR(50)) WITH RESTRICT ON DROP;

It can be added to an existing table, database physical file, or source physical file by using the ALTER TABLE statement:

ALTER TABLE TEST_TABLE ADD RESTRICT ON DROP;

Once the attribute has been added to the table, the table cannot be dropped or deleted, regardless of the user's authority. When RESTRICT ON DROP prevents a database file from being deleted, SQLCODE -672 (SQLSTATE 55035) is returned by the SQL statement, or a CPF32BF escape message is returned to the CL command.

Before the table or file can be dropped, the attribute must be removed using an ALTER TABLE statement.

ALTER TABLE TEST_TABLE DROP RESTRICT ON DROP;

RESTRICT ON DROP cannot be added to a table in QTEMP.

The CRTDUPOBJ CL command propagates the RESTRICT ON DROP attribute to the new file. A database file created any other way will not have the RESTRICT ON DROP attribute unless it is explicitly specified on the CREATE TABLE statement.

The MOVOBJ, RNMOBJ, and RSTOBJ CL commands maintain the RESTRICT ON DROP attribute.

Functions and procedures

The RESTRICT ON DROP attribute can be added to both SQL routines and external routines.

SQL routines

For SQL routines, RESTRICT ON DROP prevents dropping the routine and deleting the associated program or service program object.

CREATE FUNCTION SQUARE (P1 INT)
     RETURNS INT
     WITH RESTRICT ON DROP
   RETURN P1 * P1;

It can be added to an existing SQL routine with an ALTER statement.

ALTER PROCEDURE MYLIB.PROC1 
     WITH RESTRICT ON DROP;

Once the attribute is associated with an SQL routine, the routine cannot be dropped and the associated program or service program cannot be deleted, regardless of the user's authority.

Before the procedure or function can be dropped, the attribute must be removed using an ALTER FUNCTION or ALTER PROCEDURE statement.

ALTER PROCEDURE MYLIB.PROC1 
     WITHOUT RESTRICT ON DROP;

When RESTRICT ON DROP prevents an SQL routine from being dropped or deleted, SQLCODE -672 (SQLSTATE 55035) is returned by the SQL statement, or a CPF32BF escape message is returned to the CL command.

The MOVOBJ, RNMOBJ, and RSTOBJ CL commands maintain the RESTRICT ON DROP attribute. The CRTDUPOBJ CL command propagates the RESTRICT ON DROP attribute to the new program or service program.

External routines

For external routines, the RESTRICT ON DROP attribute can apply to the routine definition or it can include the program or service program object as well.

To prevent a procedure from being dropped, use the following statement.

CREATE PROCEDURE MYLIB.PROC2 (P1 INT)
     EXTERNAL NAME APPLIB.PGM1
     WITH RESTRICT ON DROP;

To add the attribute to an existing function and prevent both the function and the service program it uses from being dropped or deleted, use the following ALTER FUNCTION statement.

ALTER FUNCTION MYLIB.FUNC3 
     WITH RESTRICT ON DROP INCLUDING EXTERNAL PROGRAM;

Before a procedure or function can be dropped, the attribute must be removed. Use the ALTER PROCEDURE statement to remove it from a procedure.

ALTER PROCEDURE MYLIB.PROC2 
     WITHOUT RESTRICT ON DROP;

When the INCLUDING EXTERNAL PROGRAM clause is used, it is only enforced when the routine's definition is successfully recorded in the program or service program object. Message SQL7909 is issued when an external routine is created or altered and the *PGM or *SRVPGM could not be modified. The RESTRICT ON DROP will prevent the routine from being dropped, but the INCLUDING EXTERNAL PROGRAM will not be enforced.

When RESTRICT ON DROP prevents an external routine from being dropped, SQLCODE -672 (SQLSTATE 55035) is returned by the SQL statement. When RESTRICT ON DROP prevents an external routine's program from being deleted, a CPF32BF escape message is returned to the CL command. If multiple routines are registered for the same program or service program, the INCLUDING EXTERNAL PROGRAM clause must be removed from all the routines before the object can be dropped.

The MOVOBJ, RNMOBJ, and RSTOBJ CL commands maintain the RESTRICT ON DROP attribute. The CRTDUPOBJ CL command propagates the RESTRICT ON DROP attribute to the new program or service program.