- In the Navigator, double-click Databases.
- In the Object List Viewer, right-click a database or user, and click Modify Query Logging Rules
-
On the Set Query Logging Rule tab, specify the criteria for the query logging you want Vantage to record.
Option Description Rule Type Application Name Logging is enabled or disabled for any user who logs on under the specified application name set. Application names are the names the system passes in the UtilityName query band.
You can also enter single or multiple application names by separating names with a new line.
All Users Creates a query logging rule that applies to all users. Type one or more Account names, separated by commas. All users are restricted to logging on to only those sessions for the specified accounts. If you specify the USECOUNT option, logging is performed for all databases. Specific User Selects users from the list of all users in the database to create a query logging rule for one or more users. Type one or more Account names, separated by commas. All users are restricted to logging on to only those sessions for the specified accounts. The system logs each request performed for every user on the system.
The maximum number of individual user names you can specify in a single BEGIN QUERY LOGGING request is 100. The more users you specify, the greater the impact on system performance. If you specify a user_name with USECOUNT, the use count information for objects in the user database is logged, regardless of the user accessing the objects. If users access objects in another database, use count information is not logged unless the USECOUNT option is also specified for the containing database.
Specific Database Name of one or more databases for which object use count data is to be logged. Accounts Set of user accounts for which SQL request information is to be logged for the specified user_name. Logging Details Custom User can manually select “What to log” and “ Limits” options for logging. None User can selectively exclude users and applications from logging. All Selecting this option: - Lets you specify the EXPLAIN, OBJECTS, SQL, and STEPINFO options individually.
- Does not include LOCK, PARAMINFO, STATSUSAGE, XMLPLAN, or UTILITYINFO.
- Does not invoke the SUMMARY or THRESHOLD limit options.
- Does not let you specify other logging options.
Mode Mode Value Controls which CPU/IO collection algorithm Vantage uses for this query logging rule.
The CPU and I/O data collection mode to use. Mode = m where m is one of the following:- 0 – Use the default data collection algorithm for this Vantage release (algorithm 1).
- 1 – Use the classic V2R6 algorithm 1 for step adjustments.
- 2 – Use AMP algorithm 2, diagnostic only
- 3 – Use AMP algorithm 3, which includes data aborted and parallel steps.
What to log No Logging Exempts one or more users from logging when these users would otherwise be logged by a broader rule such as All Users or Account. Object Names References the names of the database objects (Tables, Views, and so forth.) in the query to be saved to the DBQLObjTbl table. Database, table, column, and index information is logged.You can specify this option with SUMMARY or THRESHOLD. Full SQL Text Saves the full SQL text to the DBQLSQLTbl table (besides what is specified in the SQL Length field). Step Information Logs the statistics for each execution step within the queries to the DBQLStepTbl table. AMP step-level information is logged.This option can be used with THRESHOLD. Explain Text EXPLAIN text for the request is logged.You cannot specify the EXPLAIN option with the SUMMARY or THRESHOLD options.Saves the Explain text for the queries to the DBQLExplainTbl table.
Usecount Logs the user count for queries. Use count information is logged for a database or user. Collects use count information on the specified databases or users, regardless of who is accessing the database objects.If you specify USECOUNT for a:- user, you can specify any of the other logging options.
- database, you cannot specify any other logging options.
You cannot enable the WITH USECOUNT option on account strings.Parameter Parameter values and metadata are logged in DBQLParamTbl. Utility Info Utility information is logged in DBC.DBQLUtilityTbl. XML Plan Adds XML Plan information to the Query Log for later use by the DBQAT products.
The plan is logged in XML format for DDL, DML, and DCL requests. For DDL statements,XMLPLAN logs basic information such as StatementType and the corresponding StepNames. In addition, the XMLPLAN option logs detailed information for the following DDL statements:- COLLECT STATISTICS
- CREATE INDEX
- CREATE TABLE
- DROP INDEX
- DROP TABLE
Select the Verbose option if you want detailed information entered in the log. VERBOSE EXPLAIN text for a request is logged in XML format. Also includes details on SpoolAsgnList and HashFields that are not available with only EXPLAIN. The VERBOSE keyword is a modifier for the XMLPLAN option.
Statistic Usage Logs query statistics. Optimizer statistics and usage recommendations are logged in an XML document for DML requests. If you also specify XMLPLAN, the data from STATSUSAGE and XMLPLAN is logged in one XML document.
If you want to log detailed statistics, select Detail. The following statistics details for all of the database objects referenced in the plan for a request are logged:- StatTimeStamp
- Version
- OrigVersion
- NumColumns
- NumBValues
- NumEHIntervals
- NumHistoryRecords
- NumNulls
- NumAllNulls
- NumAMPs
- NumPNullDistinctVals
- PNullHighModeFreq
- AvgAmpRPV
- HighModeFreq
- NumDistinctVals
- NumRows
- CPUUsage
- IOUsage
The DETAILED keyword is a modifier for the STATSUSAGE option.
Lock Logs information about query locks. Lock contentions longer than n centiseconds are logged in XML format. Limits SQL Limits Limits the length of the SQL stored in the main query logging table: DBQLLogTbl. The maximum length is 10,000 characters. If you do not select this option, Vantage logs the first 200 characters of the SQL. Summary Limits Logs only summary statistics for the executed queries. Enter 3 threshold values. These values define thresholds (seconds) for query response time. They are used to group queries into 4 'size' categories. A count of the number of queries in each category is logged every 10 minutes. For example:, If the three threshold values entered are 5, 10 and 15, counts for the queries that run between 0 and 5 seconds, 5 to 10 seconds, 10 to 15 seconds and above 15 are logged.
Every 10 minutes, a DBQL object row is written for any count greater than 0. For example, if 1 query ran under 5 seconds, 2 queries each ran 7 seconds, and 3 queries each ran over 15 seconds, 3 rows are written to the DBQL object table. When you select Summary, none of the other dialog options applies.
Threshold Limits Counts those queries that run in less than n seconds (in the summary record written every 10 minutes). Queries that run longer than n seconds are logged using the other settings. Option Modifier Specifies the content of the Summary Limits and Threshold Limits fields. - Elapsed (Sec)
- CPU Time
- IO Count
- Elapsed (1/100)
- CPU (Normalized)
- Click Commit to create your query logging rule and begin logging.
-
Click OK to confirm the logging rule was created.
Query logging using the new rule begins.
You can log queries issued by a specific application, one or more users or databases, or one or more database accounts. Query logging rules can specify what to log (such as object names, SQL text, EXPLAIN text), and you can set parameters to limit the logging and save space.