HP 3000 Manuals HP 3000 Manuals
Maintaining Rules and Procedures
The following tasks are used for the maintenance of rules and procedures
in a DBEnvironment:
* 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.