Change to Create Table statement to stop accidental deletion of the Table

It is always a danger that someone will accidentally drop (delete) the wrong file, not maliciously just accidentally.

01 CREATE TABLE MYLIB.TABLE1 02 (COLUMN1 CHAR(1)) ; 03 DROP TABLE MYLIB.TABLE1 ; Statement ran successfully

Within the new release of IBM i 7.5 is an addition to the Create Table statement that can prevent this. This does appear to be only in 7.5, I cannot find a mention of this in the documentation for IBM i 7.4 TR6.

This addition to the Create Table statement is: WITH RESTRICT ON DROP

01 CREATE TABLE MYLIB.TABLE1 02 (COLUMN1 CHAR(1)) 03 WITH RESTRICT ON DROP ;

When I use the Drop statement to delete the file I get:

04 DROP TABLE MYLIB.TABLE1 ; SQL State: 55035 Vendor Code: -672 Message: [SQL0672] DROP not allowed for TABLE1 in MYLIB type *FILE. Cause . . . . . : TABLE1 in MYLIB type *FILE has the RESTRICT ON DROP attribute and cannot be dropped

If I try to delete the table using the Delete File command, DLTF, in a 5250 session:

DLTF FILE(MYLIB/TABLE1) Operation not successful for TABLE1 in MYLIB type *FILE.

When I look in the job log I see two messages related to this failed delete:

Operation not successful for TABLE1 in MYLIB type *FILE. Object TABLE1 in MYLIB type *FILE not allowed to be deleted.

If I press F1 on the second message I can the full text for the message:

Additional Message Information Message ID . . . . . : CPF32BF Severity . . . . . : 40 Message type . . . . : Diagnostic Date sent . . . . . : DD/DD/DD Time sent . . . . : TT:TT:TT Message . . . . : Object TABLE1 in MYLIB type *FILE not allowed to be deleted. Cause . . . . . : The object was either created or altered with the SQL RESTRICT ON DROP clause. Recovery . . . : Remove the RESTRICT ON DROP attribute with the SQL ALTER statement or CREATE OR REPLACE statement for the object.

If I needed to drop/delete this Table I would have to "remove" the restriction from it. I do this by using the Alter Table statement:

01 ALTER TABLE MYLIB.TABLE1 02 DROP RESTRICT ON DROP ; 03 DROP TABLE MYLIB.TABLE1 ; Statement ran successfully

Line 2: The DROP RESTRICTION removes this restriction.

Line 3: When the Drop Table statement is executed the table is successfully dropped/deleted.

What do I do if I want to add this restriction to an existing table. I use the Alter Table again:

01 ALTER TABLE MYLIB.TABLE2 02 ADD RESTRICT ON DROP ; 03 DROP TABLE MYLIB.TABLE2 ; SQL State: 55035 Vendor Code: -672 Message: [SQL0672] DROP not allowed for TABLE2 in MYLIB type *FILE. Cause . . . . . : TABLE2 in MYLIB type *FILE has the RESTRICT ON DROP attribute and cannot be dropped

Line 2: The ADD RESTRICT adds the restriction to the existing table.

Line 3: The Drop Table fails.

One of the recent enhancements I really like in the Create Table expression has been the OR REPLACE. And this does give us a gotcha with using this restriction. Let me start with creating a new table:

01 CREATE OR REPLACE TABLE MYLIB.TABLE1 02 (COLUMN1 CHAR(1)) 03 WITH RESTRICT ON DROP ; 04 DROP TABLE MYLIB.TABLE1 ;

Lines 1 – 3: Create or replace statement used to create the Table, with the restriction on line 3.

Line 4: When this line is executed it errors as the table cannot be dropped/deleted due to restriction on the Table.

Let me use the Create or Replace Table statement again to replace the existing Table:

05 CREATE OR REPLACE TABLE MYLIB.TABLE1 06 (COLUMN1 CHAR(1)) ; 07 DROP TABLE MYLIB.TABLE1 ;

Lines 5 and 6: This Create or Replace Table does not have the restriction clause, therefore, when it is executed a new Table is generated without the restriction.

Line 7: Which means when this Drop is executed the Table is successfully dropped/deleted.

The same is true if the restriction is added to an existing Table, that had been created with Create or Replace.

01 CREATE OR REPLACE TABLE MYLIB.TABLE1 02 (COLUMN1 CHAR(1)) ; 03 ALTER TABLE MYLIB.TABLE1 04 ADD RESTRICT ON DROP ; 05 DROP TABLE MYLIB.TABLE2 ; 06 CREATE OR REPLACE TABLE MYLIB.TABLE1 07 (COLUMN1 CHAR(1)) ; 08 DROP TABLE MYLIB.TABLE2 ;

Lines 1 and 2: Table is created with the Create or Replace, and without the restriction.

Lines 3 and 4: The restriction is added using the Alter Table statement.

Line 5: This Drop statement is unsuccessful due to the restriction.

Lines 6 and 7: The Table is replaced with the Create or Replace.

Line 8: This Drop statement does drop/delete the Table.

The opposite is also true.

01 CREATE OR REPLACE TABLE RPGPGM1.TABLE1 02 (COLUMN1 CHAR(1)) ; 03 CREATE OR REPLACE TABLE RPGPGM1.TABLE1 04 (COLUMN1 CHAR(1)) 05 WITH RESTRICT ON DROP ; 06 DROP TABLE RPGPGM1.TABLE1 ;

Lines 1 and 2: I create the Table using the Create or Replace and without the restriction.

Lines 3 – 5: Using the Create or Replace again replaces the existing Table, and its replacement has the restriction.

Line 6: The Drop statement is unsuccessful.

I can also do this to DDS files. Here I am using this with my test DDS file TESTFILE:

01 ALTER TABLE MYLIB.TESTFILE 02 ADD RESTRICT ON DROP ; 03 CL: DLTF MYLIB/TESTFILE ; 04 ALTER TABLE MYLIB.TESTFILE 05 DROP RESTRICT ON DROP ; 06 CL: DLTF MYLIB/TESTFILE ;

Lines 1 and 2: I use the Alter Table statement to add the restriction to the DDS file.

Line 3: Rather than a Drop statement I am using the CL command DLTF. This fails as the file is restricted.

Lines 4 and 5: I use the Alter Table statement again to drop the restriction.

Line 6: This time the DLTF is successful.

IMHO this is good addition to the Create Table and Alter Table statements. When my employer's production partition is updated to IBM i 7.5 this will become a project to add this to all the Tables and Physical files.

 

You can learn more about RESTRICT ON DROP from the IBM website here.

 

This article was written for IBM i 7.5 only.