Database Query Logging works from a hierarchical foundation that allows BEGIN QUERY LOGGING requests to be submitted for individual users even if a rule exists for ALL users. However, if a rule exists for a specific account:user pair, you must submit an appropriate END QUERY LOGGING request to delete the rule before you can issue a new rule for that account:user pair.
The database applies the rules in the following order.
| Order in Hierarchy | Type of Rule |
|---|---|
| 1 | A rule based on an application name. |
| 2 | A rule for this specific user and specific account. |
| 3 | A rule for this specific user and any account. |
| 4 | A rule for all users and this specific account. |
| 5 | A rule for all users and any account. |
As you can see from this table, DBQL first searches for a rule based on an application name. If no such rule exists, DBQL then looks for a rule specific to the user and account, and so on down the hierarchy. You can submit a SHOW QUERY LOGGING request to determine which rules the database applies. See SHOW QUERY LOGGING.
The rules cache contains rules either for an application or for a specific account:user combination. As each user logs on, DBQL first searches the rules cache in hierarchical order for a rule. If there are no specific rules in the rules cache for level 1 or 2, DBQL searches DBC.DBQLRuleTbl in hierarchical order for the best fit. DBQL makes an entry in the rules cache for the account:user pair: either a rule that DBQL is not enabled for the account:user or the DBQL rule that applies with its options. If a match is made on the rules table at level 1, DBQL makes an application name entry in the rules cache.
The hierarchical scheme permits you to invoke a DBQL rule for a specific account:user pair, and a different rule for that specific user for all other accounts. Similarly, you might establish a rule for all users with a specific account, and a different rule for all other users, such as an ALL users ALL accounts rule.
For example, you can submit a BEGIN QUERY LOGGING request for default logging on ALL users, and DBQL can also be enabled for user1 with objects and steps. If user1 logs on, DBQL collects objects and steps. When users other than user1 log on, DBQL only logs default information for them.
Similarly, if there is an ALL users rule to log information for objects, you can implement a rule for a specific user so that DBQL logs does default logging for that user.
You can also selectively exclude users, applications, and so on from logging through the use of the WITH NONE option. For example, if there is a rule that specifies default logging for ALL users, and you want to exclude logging for busy_user1, you can issue the following request.
BEGIN QUERY LOGGING WITH NONE ON busy_user1;
Once a set of query logging rules is created, it applies to the current session and all subsequent sessions to which an active DBQL rule applies.
Note that DBQL does not allow multiple rules for any named instance of one of the five query logging rule types. For example, if a rule currently exists for ALL users under a specific account name, the system does not accept a duplicate rule.