The following tables offer examples of the type of data that is logged according to the rules you defined and the behavior of the query.
|IF you…||THEN …|
|try to drop a user or database with DBQL enabled||an error 5780 occurs. You cannot drop a user or database that has DBQL logging enabled. First disable logging for that user or database.|
|log on as the authorized user DBC (or your DBA user) and attempt to DROP, UPDATE or ALTER any DBQL object||the statement fails with an access error:
Failure 3523 (<username> does not have <drop | update | alter> access to <DBQL table name>)
|log on as an unauthorized user (not DBC or an administrative user) and submit a BEGIN QUERY LOGGING or REPLACE QUERY LOGGING statement||the BEGIN QUERY LOGGING or REPLACE QUERY LOGGING statement fails with an access error:
Failure 3523 (username does not have statement permission)
|disable query logging for a user running a session that is being logged||no more rows are cached for that session or that user after the running query completes.|
|abort a session that is being logged||the AbortFlag value is set to T in the DBQLogTbl row for the query.|
|begin query logging for a user and that user runs the same query multiple times during one session||multiple rows are logged in DBQLogTbl.
If the query is in the steps cache when it is executed after the first execution, the CacheFlag is set to T. The second time a query is run, it goes into the steps cache. The third and subsequent times, the CacheFlag is set to T.
|want to view all logged rows for a query||use the QueryID field to join DBQLogTbl rows with (depending on the rules for the user) other DBQL table rows.|
|want to forbid logging for any MULTILOAD job||submit the following statement:
BEGIN QUERY LOGGING WITH NONE ON APPLNAME= 'MULTLOAD';
Valid application names are drawn from the pool of names returned in the LogonSource string, and for MultiLoad, it returns MULTLOAD (no I). For a list of the commonly seen LogonSource strings, see “Application Names” in SQL Data Definition Language Detailed Topics.
|want to start logging queries for a user who already has a WITH NONE rule in place||submit REPLACE QUERY LOGGING.
REPLACE QUERY LOGGING WITH STEPINFO ON user5 ACCOUNT = ‘abc’;
|want to find out if statement errors occurred in the successful multistatement or iterated request||check the ErrorCode column of the DBQL log table. If the ErrorCode column is populated with error 9259, this means that the query succeeded, but contains statement errors.|