Maintaining Rules and Procedures

• Granting and Revoking Procedure Authorities

• Examining the Inventory of Rules and Procedures

• Dropping and Recreating Rules and Procedures

• Validating Procedures and Procedure Sections

Granting and Revoking Procedure Authorities

You must grant EXECUTE authority to users who need to execute procedures with the EXECUTE PROCEDURE statement. The creator of a rule that executes a procedure must also have EXECUTE authority for the procedure at the time the CREATE RULE statement is issued. The user of a statement that fires the procedure through a rule need not have EXECUTE authority for the procedure; EXECUTE authority is implicit in tying the procedure to an INSERT, UPDATE, or DELETE, for which the user must have authority.

The following example grants EXECUTE authority to the Managers group:

   isql=> grant execute on PurchDB.ReportMonitor to Managers;

You can use the REVOKE statement to remove EXECUTE authority, as in the following:

   isql=> revoke execute on PurchDB.ReportMonitor from      Managers;

In addition, when you create a procedure, you can specify the IN DBEFileSetName clause to indicate where the procedure's sections are to be stored. Any user storing sections in the specified DBEFileSet must have been granted SECTIONSPACE authority to do so, as in the following grant to a group:

  isql=> grant sectionspace on dbefileset DBEFileSet1 to    Group1;

You can use the REVOKE statement to remove SECTIONSPACE authority, as in the following:

   sql=> revoke sectionspace on dbefileset DBEFileSet1 from    Group1;

Examining the Inventory of Rules and Procedures

Rules, procedures, and their definitions are stored in the system catalog. You can display the current list of procedures with the following query:

   isql=> select * from system.procedure;

   isql=>

Use a query like the following to display the definition of a particular procedure (use the real procedure name in place of PROCNAME and the real owner name in place of OWNER):

   isql=> select segnum, definestring

   > from system.proceduredef

   > where name = 'PROCNAME' and

   > owner = 'OWNER' order by segnum;

To display the current list of rules in the DBEnvironment, use the following query:

   isql=> select * from system.rule;

To display the definition of a particular rule, use the following query (use the real rule name in place of RULENAME and the real owner name in place of OWNER):

   isql=> select segnum, rulestring

   > from system.ruledef

   > where rulename = 'RULENAME' and

   > owner = 'OWNER' order by segnum;

   isql=>

Similar queries can be performed on the SYSTEM.RULECOLUMN, SYSTEM.PARAMETER, SYSTEM.PARAMDEFAULT, and SYSTEM.PROCAUTH views to obtain more detailed information.

Dropping and Recreating Rules and Procedures

As business rules change, the rules and procedures defined in the DBEnvironment can be modified. Use the DROP RULE statement to remove a rule that is no longer needed, and use the DROP PROCEDURE statement to remove a procedure that is no longer needed. If you use the PRESERVE option with the DROP PROCEDURE statement, the EXECUTE authorities associated with the procedure remain in the system catalog. Rules that invoke a particular procedure are not dropped when the procedure is dropped. However, stored sections that depend on rules which invoke the procedure are marked invalid when you drop the procedure. Creating or dropping rules has the effect of invalidating all sections that depend on the table on which the rule is based.

Validating Procedure Sections

When you create a procedure, a section is created for each SQL statement in the procedure except:

   BEGIN WORK                  OPEN CURSOR           WHENEVER

   CLOSE CURSOR                ROLLBACK WORK         

   COMMIT WORK                 SAVEPOINT            

When procedure sections become invalid, ALLBASE/SQL will attempt to revalidate each section as it executes. You can also use the VALIDATE statement to revalidate all the sections in a procedure at one time. When a procedure is dropped, recreated, and reinvoked, ALLBASE/SQL must revalidate any invalid sections that execute the procedure or invalid sections containing rules that may invoke the procedure.