Wednesday, June 29, 2022

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

stop table delete with restrict on drop

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.

2 comments:

  1. S.E. Yarbrough JrJune 29, 2022 at 6:37 AM

    Simon, great read and examples, this DDL function is very welcome into the tools of the IBMi / DB2 family of tools.

    ReplyDelete
  2. Gustavo Valdes ChavanaJune 30, 2022 at 5:26 AM

    Outstanding benefits, i recommed give alter table for restrict drop for some important tables and avoid accidents

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.