It’s most likely that you have added an extra lock on your front door, preventing it for being opened from the outside. In most cases we do not think about having this extra lock, it is just there, and we use it.

However, at some stage someone came up with this idea and it spread around the world. For sure there are exceptions, but nonetheless it is considered as common sense to have an extra lock in place.

What about adding something similar to our database files? Just an extra precaution to secure our data in a better way. If this could be implemented easily and turned on and off in a simple way, then it is worth considering, I guess.

In today’s world it is hard to keep up with the latest technology, IBM i is piling up new functionality and, in most cases, it takes a while before the market is adopting and embracing that technology.

In the past I dedicated an article to executing a Reorganise on-the-fly after the IBM i was changed and records were actually moved and not deleted first and inserted immediately afterwards, when reorganising A screen a story – What does “move active records” mean?

I still see customers not using that possibility, because their ISV is not ready to use that technology.

In this article I would like to introduce a feature of IBM i 7.5 enhanced twice according to this link RESTRICT ON DROP.

Using the command ALTER TABLE will allow us to add or remove the RESTRICT ON DROP to a file, by using the ADD RESTRICT ON DROP or DROP RESTRICT ON DROP. By adding the RESTRICT ON DROP attribute to a file, it prevents a file from being deleted, even if the user trying to delete the file has the right authority to do so.

With a simple command any defined DDS file or SQL defined table can be changed to use this extra protection. There is only one exception, it cannot be used against a file defined in QTEMP.

Below a simple SQL script, you can use to give RESTRICT ON DROP a try:

Create Table Qrplobj.Mycustcdt As
      (Select *
          From Qiws.Qcustcdt)
      With Data;

Select *
  From Qrplobj.Mycustcdt;

Alter Table Qrplobj.Mycustcdt
  Add Restrict On Drop;

Drop Table Qrplobj.Mycustcdt;

The last statement will result in a message:

[SQL0672] DROP not allowed for MYCUSTCDT in QRPLOBJ type *FILE.

The SQL statement:

ALTER TABLE QRPLOBJ.MYCUSTCDT
   REMOVE RESTRICT ON DROP;
   

Will allow you to delete the file again.

For the command ALTER TABLE to add or remove the RESTRICT ON DROP attribute, the file cannot be in use. Please be aware of this.

The SQL statement below will tell you if you are already using RESTRICT ON DROP when running the statement below:

Select Table_Name,
       Table_Owner,
       Table_Text,
       System_Table_Name,
       System_Table_Schema,
       Restrict_On_Drop
  From Qsys2.Systables
  Where Restrict_On_Drop = 'YES';
  

Creating a procedure to ADD or REMOVE RESTRICT ON DROP will save you the time having to do this manually.

That is precisely what the SQL procedure below will do.

It only requires two parameters.

-- Create Schema for all your SQL scripts
-- create schema SQLSCRIPTS

/* Creating SQLSCRIPTS.ADD_REMOVE_RESTRICTONDROP_4_SCHEMA [Procedure] */
Create Or Replace Procedure Sqlscripts.Add_Remove_Restrictondrop_4_Schema (
        In Schema Varchar(30),
        In Action Char(4) Default 'ADD'
      )
    Language Sql
    Not Deterministic
    Modifies Sql Data
    Called On Null Input
    System_Time Sensitive No
    Set Option Alwblk = *Allread,
               Alwcpydta = *Optimize,
               Commit = *None,
               Dlyprp = *Yes,
               Dyndftcol = *No,
               Dynusrprf = *User,
               Usrprf = *User
Begin
  Declare Needed Char(3);
  Declare Sqlstatement Varchar(500);
  If Action = 'ADD' Then
    Set Needed = 'NO'; /* Avoiding adding what is already in place */
  End If;
  If Action = 'DROP' Then
    Set Needed = 'YES'; /* Avoiding removing what is not there */
  End If;
  For Add_Remove_Action Cursor For
    Select Table_Name,
           Table_Schema,
           Restrict_On_Drop
      From Qsys2.Systables
      Where Table_Schema = Schema
            And Restrict_On_Drop = Needed
    Do
      Set Sqlstatement = 'alter table ' Concat Table_Schema Concat '. ' Concat Table_Name Concat ' '
            Concat Action Concat ' RESTRICT ON DROP';
      Call Systools.Lprintf(
        '' Concat Sqlstatement Concat ''
      ); /* So the joblog reflects what is done under the bonnet */
      Execute Immediate Sqlstatement;
  End For;
End; 
  

It might be a good idea to create a special schema/library for this SQL procedure. I have included that statement as comments. Placing your cursor on that line and pressing Ctrl+7 will toggle the comments.

Once you have created the procedure, executing some additional statements will improve the quality of your procedure:

/* Setting label text for SQLSCRIPTS.EMAIL_PROBLEM_REPORT */
Label On Routine Sqlscripts.Add_Remove_Restrictondrop_4_Schema(Varchar(), Char(4))
  Is 'ADD or DROP RESTRICT ON DROP FOR SCHEMA TABLES';

/* Setting comment text for SQLSCRIPTS.EMAIL_PROBLEM_REPORT */
Comment On Parameter Routine Sqlscripts.Add_Remove_Restrictondrop_4_Schema (Varchar(), Char(4))
  (Schema Is 'Schema name', Action Is 'Enter ADD or REMOVE');
  

Finally, it is time to give it a try:

-- Examples how to run this procedure

-- Example 1 - Run with option ADD
Call Sqlscripts.Add_Remove_Restrictondrop_4_Schema(
  Schema => 'QRPLOBJ',
  "ACTION" => 'DROP'
);
Stop;

-- Example 2 - Run with option DROP
Call Sqlscripts.Add_Remove_Restrictondrop_4_Schema(
  Schema => 'QRPLOBJ',
  "ACTION" => 'ADD'
);

stop;

Currently this procedure is limited to adding RESTRICT ON DROP only to physical files and tables.

After all, you have to start somewhere with IBM i modernisation. Adding some extra layer of security to your database is, in my humble opinion, a good start.

The whole idea behind this procedure is to add flexibility, in that way you can run the procedure should your ISV come along wanting to make changes to your database. The only limitation, as already mentioned, is the fact that for adding the RESTRICT ON DROP attribute the file/table cannot be in use.

Currently this procedure does not pay attention to that aspect, but just let me know if you want me to add that in.

It might be an idea for a new article!

  Please Note

For all coding examples on the PowerWire platform, see our disclaimer below.