
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.
Simon, great read and examples, this DDL function is very welcome into the tools of the IBMi / DB2 family of tools.
ReplyDeleteOutstanding benefits, i recommed give alter table for restrict drop for some important tables and avoid accidents
ReplyDelete