DBQL is flexible enough to log information on the variety of SQL requests that run on Database Engine 20, from short transactions to longer-running analysis and mining queries.
Options enable you to control the volume and detail of the logged data. You can define rules, for example, that log the first 5,000 characters of any query that runs during a session invoked by a specific user under a specific account. Or you can define a rule to log queries that take more time to complete than the specified time threshold. You can even log queries that exceed a specific CPU time specified in hundredths of a second. For example, specify “THRESHOLD=500 CPUTime” for queries that take longer than five seconds of AMP CPU time to complete.
- Default logging reports for each query with at least the leading SQL characters, the time of receipt, the number of processing steps completed, the time the first step was dispatched, the times the packets were returned to the host, and the number of rows updated, inserted, and deleted.
- Summary logging reports the count of all queries that completed processing time within specified time intervals, I/O criteria, or CPU usage criteria.
- Threshold logging can log a combination of default and summary data:
- Default data for each query that ran beyond the threshold limit
- Summary counts of all queries that ran within the threshold time
Use elapsed time, I/O counts, or CPU time as a criterion for threshold logging of details or summary
- Detailed logging, which includes:
- Default data
- Step level activity, including parallel steps
- Object usage per query
- Full SQL text
- Explain text
- XML query plan
- Preventing logging for a specific user, user/account pair, user/account list, or application name.