Pages

Tuesday, June 28, 2022

Restrict on Drop/Delete table - IBM i

Database files are key for any application. One can never imagine the impact of a database file being accidentally deleted. 

There are ways to recover the data if the file is journaled. But, this would take lot of effort and business disruption. 

With the introduction of RESTRICT ON DROP attribute in IBM i 7.5, we can restrict a file from being deleted accidentally. 

Add Restriction on Drop

To prevent a file/table from being deleted, ADD RESTRICTION ON DROP can be used with ALTER TABLE. 

By adding this restriction a file cannot be deleted both by using DLTF (Delete File) from command line/CL program or by using DROP TABLE from SQL. 

ALTER TABLE <Library>/<File> ADD RESTRICT ON DROP 

E.g.: 

RESTRICT ON DROP



This would add a restriction on REDDYP1/TEST and this file cannot be deleted. Once added, the file cannot be deleted. 

Below error would appear if we try to delete the file from command line. Same would happen with DROP TABLE from SQL. 

RESTRICT ON DROP








Delete file with Restrict drop












Drop Table









DSPFD (Display File Description) command can be used to verify if a file has restriction on drop. DSPFD would contain the below if the restriction is present. 

Restrict on drop  . . . . . . . . . . . . . :            NO

Remove Restriction on Drop 

If the file can/need to be deleted, the restriction need to be removed using DROP RESTRICT ON DROP with ALTER TABLE. 

ALTER TABLE <Library>/<File> DROP RESTRICT ON DROP 

E.g.: 

Drop Restriction on Drop




*This attribute is only available since IBM i 7.5. 
 
If you have any Suggestions or Feedback, Please leave a comment below or use Contact Form. 

No comments:

Post a Comment

OOPs.. Is Java a pure Object Oriented Language?

Object Oriented Programming Object Oriented Programming or OOPs is a programming model which is based on the Objects.  We probably have to t...