The following table documents the SQLTEXT option for BEGIN QUERY LOGGING.
| Option | Definition |
|---|---|
| SQLTEXT[=n] | Sets the maximum number of SQL text characters to log in the default row. The default value is 200. The value specified cannot be negative. If you specify 0, then no characters are logged. If you specify SQLTEXT without specifying a numeric value to limit the number of characters logged, then the entire SQL request is logged up to a maximum of 10,000 characters in DBC.DBQLogTbl. If the request exceeds 10,000 characters, then the excessive characters are not logged. If you specify either ALL or SQL, SQLTEXT is logged redundantly in both DBC.DBQLogTbl and DBC.DBQLSqlTbl. You should not specify both ALL and SQLTEXT. If you specify ALL, then you should set the value for SQLTEXT to 0. Otherwise, the SQL text is redundantly logged in both the DBC.DBQLogTbl and DBC.DBQLSqlTbl tables. |
The following tables document the SUMMARY option for BEGIN QUERY LOGGING.
| Option | Definition |
|---|---|
| SUMMARY=n1,n2,n3 | Designed for use with short, OLTP-like, queries. You can specify SUMMARY to reduce collection overhead for short running, high-volume queries. You can further specify the units of ELAPSEDTIME, CPUUSAGE, or IOCOUNT. Counts the number of requests for a session that fall into each of four time intervals or count ranges. Interval values can be specified in CPU time, normalized CPU time, elapsed seconds, elapsed hundredths of seconds, or I/O counts. If you do not specify a summary unit for the intervals, the default is expressed in elapsed seconds. If you specify SUMMARY, then you cannot specify any other options. You must specify the first three intervals explicitly. The fourth interval is created by default.
Counts for each interval are stored in the QueryCount column of four separate rows in DBC.DBQLSummaryTbl. SUMMARY is the only option whose cache is flushed at regular, system-defined, intervals. |
The following table documents the valid SUMMARY option modifiers.
| SUMMARY Option Modifier | Description |
|---|---|
| CPUTIME | Use this option to set ranges and to summarize counts of the number of requests that fall into a CPU time interval. The SUMMARY value is expressed in units of 0.01 second. For example, if you specify 500 for one of the intervals, then the value used to make the determination is 5 CPU seconds. |
| CPUTIMENORM | Use this option to set ranges and to summarize counts of the number of requests that fall into a normalized CPU time interval. This option is designed for use with coexistence systems to aid in managing mixed nodes more efficiently, but it can be used with any system. The SUMMARY value is expressed in units of 0.01 second. |
| ELAPSEDSEC | Use this option to set ranges and to summarize counts of the number of requests that fall into an elapsed time interval. The SUMMARY value is expressed in units of 1.00 seconds. This is the default. |
| ELAPSEDTIME | Use this option to set ranges and to summarize counts of the number of requests that fall into an elapsed time interval. The SUMMARY value is expressed in units of 0.01 second, so it provides finer granularity for elapsed time than ELAPSEDSEC. |
| IOCOUNT | Use this option to set ranges and to summarize counts of the number of requests that fall into an I/O interval. |
The following tables document the THRESHOLD option for BEGIN QUERY LOGGING.
| Option | Definition |
|---|---|
| THRESHOLD[=n] | Sets a threshold (the optional value n specifies the number of units of either seconds or I/O counts for the threshold) that determines whether a query is to be logged fully or just counted. Queries that exceed the threshold are logged, while those that do not meet the threshold are not logged. You can specify THRESHOLD to reduce collection overhead for short running, high-volume queries. You can further specify the units of ELAPSEDTIME, CPUUSAGE, or IOCOUNT. You can specify the THRESHOLD limit option with the SQL, STEPINFO, and OBJECTS logging options. You cannot specify the THRESHOLD limit option with the ALL, EXPLAIN, STATSUSAGE, or XMLPLAN logging options. If you do not specify a threshold option modifier to specify the logging units, the default is expressed in elapsed seconds. If you do not specify a THRESHOLD limit or SUMMARY, then all queries are logged fully. The following restrictions apply to time thresholds only.
The following restriction applies to I/O count thresholds only.
See the list of valid THRESHOLD option modifiers and their descriptions on the next page. |
The following table lists the valid THRESHOLD option modifiers.
| THRESHOLD Option Modifier | Description |
|---|---|
| CPUTIME | If you do not specify a threshold value for n, then the system uses the default CPUTIME value of 0.05 CPU seconds. The THRESHOLD value is expressed in units of 0.01 second. For example, if you specify 500, then the value used to make the determination is 5 CPU seconds. |
| CPUTIMENORM | This option is designed for use with coexistence systems to aid in managing mixed nodes more efficiently. The THRESHOLD value is expressed in units of 0.01 second. |
| ELAPSEDSEC | The THRESHOLD value is expressed in units of 1.00 seconds. This is the default. |
| ELAPSEDTIME | The THRESHOLD value is expressed in units of 0.01 second, so it provides finer granularity for elapsed time than ELAPSEDSEC. |
| IOCOUNT | If you do not specify a THRESHOLD value for n, then the system uses the default IOCOUNT value of 5. |