SQL events

A SQL rule runs before the SQL source is prepared. If a SQL source is modified, it is prepared or passed to run immediately after the SQL rule runs. Use SQL rules for the following purposes:

Modify a SQL source

To modify a SQL source, add or modify a WHERE clause.

Reject a SQL statement

To reject a SQL statement, use the REJECT return value. You can also use the SQL.MESSAGE to send a message to the client. If the SQL statement is rejected, set the SQL.CODE variable to a negative value. Otherwise, the value -1 is used as the SQL code.

Accept a SQL statement

To accept a SQL statement, set the return value to ACCEPT. If the SQL statement is accepted, Db2® does not run it. Instead, the rule processes the statement. To send a warning or error message to the client, use the SQL.MESSAGE variable. For warnings, a positive value. For failures, use a negative value. If the return code is ACCEPT and a non-zero value is set for the SQL.CODE variable, a message is sent to the client. If a message is not provided, a default message is constructed and sent.

When a SQL event occurs, the system extracts information about the event and creates the following variables. These variables are instantiated when the SQL rule is scheduled to run. You can write a SQL rule that accesses the following variables:

Criterion	Variable	Contents	Data type
ALL	SQL.CODE	The code to return to the client	Character, read-write
ALL	SQL.MESSAGE	The message to return to the client	Character, read-write
ALL	SQL.SEARCHID	The SQL verb that is extracted from the current SQL string	Character, read-only
ALL	SQL.TEXT	The actual SQL source	Character, read-only
ALL	SQL.USER	The user area that is passed among all rules	Character, read-write