Bookshelf: Administering the Query Log

The Siebel Analytics Server provides a facility for logging query activity at the individual user level. Logging is intended for quality assurance testing, debugging, and for use by Siebel Technical Support. In production mode, query logging is normally disabled.

The query log file is named the NQQuery.log file. This file is in the Log subdirectory in the Siebel Analytics installation folder.

Configuring the Logging System

This section describes the logging system and includes information about setting the size of the query log, choosing a logging level, and enabling query logging for a user.

Because query logging can produce very large log files, the logging system is turned off by default. It is sometimes useful, however, to enable logging to test that your repository is configured properly, to monitor activity on your system, to help solve performance problems, or to assist Siebel Systems Technical Support. You need to enable logging on the system for each user whose queries you want logged.

Controlling the Size of the Log File

The parameter USER_LOG_FILE_SIZE in the User Log section of the NQSConfig.INI file determines the size of the NQQuery.log file. When the log file grows to one-half the size specified by the USER_LOG_FILE_SIZE parameter, the file is renamed to NQQuery.log.old, and a new log file is created automatically. (This helps to make sure that the disk space allocated for the log file does not exceed the size specified in the configuration file.) Only one copy of the old file is kept.

You can set the file size as high as you like, limited only by the amount of space available on the device. If you change the value of the USER_LOG_FILE_SIZE parameter, you need to restart the Siebel Analytics Server for the change to take effect. For the syntax of the USER_LOG_FILE_SIZE parameter, see Siebel Analytics Installation and Configuration Guide.

Setting a Logging Level

You can enable logging level for individual users; you cannot configure a logging level for a group. Set the logging level based on the amount of logging you want to do. In normal operations, logging is generally disabled (the logging level is set to 0). If you decide to enable logging, choose a logging level of 1 or 2. These two levels are designed for use by Siebel Analytics Server administrators.

NOTE: Logging levels greater than 2 should be used only with the assistance of Siebel Technical Support.

Table 24. Logging Levels
Logging Level	Information That Is Logged
Level 0	No logging.
Level 1	Logs the SQL statement issued from the client application. Logs elapsed times for query compilation, query execution, query cache processing, and backend database processing. Logs the query status (success, failure, termination, or timeout). Logs the user ID, session ID, and request ID for each query.
Level 2	Logs everything logged in Level 1. Additionally, for each query, logs the repository name, business model name, presentation catalog name, SQL for the queries issued against physical databases, queries issued against the cache, number of rows returned from each query against a physical database and from queries issued against the cache, and the number of rows returned to the client application.
Level 3	Logs everything logged in Level 2. Additionally, logs the logical query plan. Do not select this level without the assistance of Siebel Technical Support.
Level 4	Logs everything logged in Level 3. Additionally, logs the query execution plan. Do not select this level without the assistance of Siebel Technical Support.
Level 5	Logs everything logged in Level 4. Additionally, logs intermediate row counts at various points in the execution plan. Do not select this level without the assistance of Siebel Technical Support.
Level 6 and 7	Reserved for future use.

Using the Log Viewer

Use the Siebel Analytics log viewer utility nQLogViewer (or a text editor) to view the query log. Each entry in the query log is tagged with the user ID of the user who issued the query, the session ID of the session in which the query was initiated, and the request ID of the individual query.

To run the nQlogViewer utility, open a Command window and type nQlogViewer with any combination of its arguments. The syntax is as follows:

nqlogviewer [-u<user_ID>] [-f<log_input_filename>]
[-o<output_result_filename>]
[-s<session_ID>] [-r<request_ID>]


`user_ID`	The name of a user in the Siebel Analytics Server repository. This limits the scope to entries for a particular user. If not specified, all users for whom query logging is enabled are shown.
`log_input_filename`	The name of an existing log file. If not specified, the active log file is read.
`output_result_filename`	The name of a file in which to store the output of the log viewer. If the file exists, results are appended to the file. If the file does not exist, a new file is created. If not specified, output is sent to the monitor screen.
`session_ID`	The session ID of the user session. The Siebel Analytics Server assigns each session a unique ID when the session is initiated. This limits the scope of the log entries to the specified session ID. If not specified, all session IDs are shown.
`request_ID`	The request ID of an individual query. The Siebel Analytics Server assigns each query a unique ID when the query is initiated. This limits the scope of the log entries to the specified request ID. If not specified, all request IDs are shown.

You can also locate user IDs, session IDs and request IDs through the Session Manager. For more information, see Using the Session Manager.

NOTE: Siebel Analytics Web administrators can view the query log using the Manage Sessions Web page.

Interpreting the Log Records

After you have logged some query information and started the log viewer, you can analyze the log. The log is divided into several sections, some of which are described in the next section. Log entries for levels 1 and 2 are generally self-explanatory. The log entries can provide insights to help DBAs in charge of the underlying databases tune them for optimum query performance. The query log can also help you check the accuracy of applications that use the Siebel Analytics Server.

SQL Request

This section lists the SQL issued from the client application. This can be used to rerun the query from the same application, or from a different application.

General Query Information

This section lists the repository, the business model, and the presentation catalog from which the query was run. You can use this information to provide statistics on query usage, which could be used to set priorities for future application development and system management.

Database Query

This section of the log begins with an entry that reads Sending query to the database named <data_source_name>, where data_source_name is the name of the data source to which the Siebel Analytics Server is connecting. Multiple database queries can be sent to one or more data sources. Each query will have an entry in the log.

The database query section has several uses. It records the SQL sent to the underlying databases; you can then use the logged SQL to run queries directly against the database for performance tuning, results verification, or other testing purposes. It allows you to examine which tables are being queried to verify that aggregate navigation is working as you expect. If you understand the structure of the underlying database, it might also provide some insights into potential performance improvements, such as useful aggregate tables or indexes to build.

Query Status

The query success entry in the log indicates if the query completed successfully or if it failed. You can search through the log for failed queries to determine why they failed. For example, all the queries during a particular time period might have failed due to a database downtime.