|
|
|
Commands are available to trace such things as disk file access, locks, user interface-to-DBMS server communications, query plans of the Ingres Query Optimizer and various types of memory usage. For more information on tracing, see the Database Administrator Guide.
DBMS server tracing is generally enabled by some form of the set command, although some forms of tracing make use of Ingres environment variables and logicals. Set commands used specifically for debugging and troubleshooting are discussed in this document. More general set commands are documented in the SQL Reference Guide.
|
|
|
The Ingres ODBC Call-level Interface (CLI) follows the existing convention of ODBC tracing as performed by the UNIX ODBC Driver Manager and Microsoft ODBC Driver Manager. Registry or configuration files are scanned for trace and trace log settings. Tracing output is similar to what is currently provided by the Ingres ODBC tracing DLL on Windows. Optionally, an application can set standard ODBC tracing using the following Ingres environment variables:
A string indicating the path and file name of an ODBC trace file. For example, the path and file name of an ODBC trace file for UNIX and Linux is \tmp\odbc.log and for Windows is c:\temp\odbc.log.
A positive integer with a value of 1, 3, or 5. A setting of 1 provides standard ODBC tracing and is the most useful for debugging ODBC CLI applications. A setting of 3 includes ODBC function entry calls in the Ingres ODBC driver (as opposed to the ODBC CLI driver manager). A setting of 5 displays information about internal function entries in the Ingres ODBC driver.
If an ODBC application does not use the Ingres ODBC CLI, the ODBC trace settings of 3 and 5 are still recognized, but the setting of 1, which belongs to the driver manager component, is not. However, the existing tracing capability for third-party driver managers is unaffected. In this case, the driver manager tracing is written to the ODBC trace log as specified in the registry (Windows) or odbcinst.ini file (UNIX and Linux), but the detailed tracing information is written to the log file as specified by the II_ODBC_LOG environment variable.
|
|
|
The set statement is used to specify a number of runtime options and has the general format:
set [no]option [additional parameters];
The option is set on or active by set option or set option on (depending on the option). It is turned off by a corresponding set nooption or set option off. Additional parameters are required, depending on the option. For a detailed syntax description of the set statement, see the SQL Reference Guide.
|
|
|
Set statements are executed as part of query language startup procedures at selected levels of scope. A set statement is entered directly at a terminal for temporary settings or executed using an Ingres environment variable/logical to establish a default option setting for all users. Several levels are shown in the following table:
Where Specified |
Scope of Effect |
|---|---|
ING_SYSTEM_SET |
Executed whenever Ingres is started up, affecting all users |
ING_SET |
Executed whenever any Ingres tool connects to a server |
ING_SET_DBNAME |
Affects only Ingres tools starting up on the specified database |
II_EMBED_SET |
Similar to ING_SET, but diagnostic output is to a file |
set statement entered directly |
In effect until changed by a subsequent set statement for the option. This occurs by another direct set statement or using any of the Ingres environment variables and logicals above reestablishing the default. |
|
|
|
One or more set options can be specified using the _SET environment Ingres environment variables and logicals. They can be assigned directly from the operating system shell. Separate multiple set statements by semicolons (up to a limit of 64 characters), as shown in the following examples:
Windows:
SET ING_SET=set nojournaling;set printqry
UNIX:
C Shell:
setenv ING_SET "set nojournaling; set printqry"
Bourne Shell:
ING_SET = "set nojournaling; set printqry" export ING_SET
VMS:
DEFINE ING_SET "SET NOJOURNALING; SET PRINTQRY"
|
|
|
Set statements can also be implemented by means of an include statement. The include statement allows you to place the set statements in a file and specify the file name in the setenv (or equivalent) command. Use of the include option also avoids the 64-character line limit.
For example:
Windows:
set ING_SET=include c:\extra\ingres\set.ing
UNIX:
C Shell:
setenv ING_SET "include /extra/ingres/set.ing"
Bourne Shell:
ING_SET = "include /extra/ingres/set.ing"
export ING_SET
VMS:
DEFINE ING_SET -"INCLUDE DUA0:[EXTRA.INGRES]SET.ING"
The file set.ing includes the following statements:
set autocommit on;
set lockmode on mastertable
where level = page,
maxlocks = 10;
|
|
|
Set statements are automatically executed as part of query processor start up. Various startup defaults are enabled either by setting an Ingres environment variable/logical (such as ING_SET or II_EMBED_SET described above) or by including these set statements in one of the query processor startup files.
For an alphabetical listing of Ingres environment variables/logicals, see the appendix "Environment Variables and Logicals."
|
|
|
Set options of special interest in query troubleshooting include:
|
|
|
The command set printqry prints out the query before it is optimized and executed. This is especially useful when evaluating queries for performance tuning or troubleshooting.
|
|
|
The following command turns query display on:
Windows:
SET ING_SET=set printqry
UNIX:
C Shell:
setenv ING_SET "set printqry"
Bourne Shell:
ING_SET = "set printqry"
export ING_SET
VMS:
DEFINE ING_SET "SET PRINTQRY"
As an example, assume that QBF is started up. The following is displayed:
Query Buffer:
set Autocommit on
Query Parameters:
Query Buffer:
select cap_value, cap_capability from iidbcapabilities
Query Parameters:
Query Buffer:
select user_name, dba_name from iidbconstants
Query Parameters:
|
|
|
The following command turns query display on through II_EMBED_SET. This is similar to the ING_SET environment variable/logical. All information is gathered in the client application and printed to the log by the library routines linked to the client application:
Windows:
SET II_EMBED_SET=printqry
UNIX:
C Shell:
setenv II_EMBED_SET "printqry"
Bourne Shell:
II_EMBED_SET = "printqry"
export II_EMBED_SET
VMS:
DEFINE II_EMBED_SET "PRINTQRY"
The query output with query timings is placed into a file called iiprtqry.log. Here is a sample output:
Query text:
select * from iirelation
Query Send Time: Thu Mar 26 17:20:43 2001
Query Response Time: Thu Mar 26 17:20:44 2001
Response CPU Time: 2630
Qry End CPU Time: 3370
This type of query monitoring can be useful for spotting slow-running queries in applications.
|
|
|
The set qep option provides a display of the optimizer's query execution plan (QEP) for the query after it has been optimized but before it is executed.
The following command turns QEP display on as the default ING_SET level:
Windows:
SET ING_SET=set qep
UNIX:
C Shell:
setenv ING_SET "set qep"
Bourne Shell:
ING_SET = "set qep"
export ING_SET
VMS:
DEFINE ING_SET "SET QEP"
The Ingres Query Optimizer and QEPs are described in detail in the Database Administrator Guide.
|
|
|
The set joinop notimeout option can be used in tracing query performance. This statement turns the optimizer timeout feature off. With timeout on, the optimizer stops checking for further query execution plans when it believes that the best plan it has found takes less time to execute than the amount of time already spent searching for a plan. If you issue a set joinop notimeout statement, the optimizer continues searching query plans. This option is often used with the set qep option to ensure that the optimizer is picking the best possible query plan.
|
|
|
To cancel any of these options that have been set, you issue the opposite set statement (set nooption to turn an option off or set option in the case of joinop notimeout to restore the default behavior).
|
|
|
The following set statement enables you to specify how transaction errors are handled in the current session:
set session with on_error = rollback
statement| transaction
To direct Ingres to roll back the effects of the entire current transaction if an error occurs, specify rollback transaction. To direct Ingres to roll back only the current statement (the default), specify rollback statement. To determine the current status of transaction error handling, issue the select dbmsinfo('on_error_state') statement.
Specifying rollback transaction reduces logging overhead, and help performance; the performance gain is offset by the fact that, if an error occurs, the entire transaction is rolled back, not the single statement that caused the error.
The following errors always roll back the current transaction, regardless of the current transaction error-handling setting:
To determine if a transaction was aborted as the result of a database statement error, issue the statement select dbmsinfo('on_error_state'). If the error aborted the transaction, this statement returns 0, indicating that the application is currently not in a transaction.
You cannot issue the set session with on_error statement from within a database procedure or multi-statement transaction.
|
|
|
The set option io_trace prints out information about disk I/O during the life of each query.
The following command turns I/O trace on as the default ING_SET level:
Windows:
SET ING_SET=set io_trace
UNIX:
C Shell:
setenv ING_SET "set io_trace"
Bourne Shell:
ING_SET = "set io_trace"
export ING_SET
VMS:
DEFINE ING_SET "SET IO_TRACE"
For example, given the query:
select * from iirelation;
Here is a sample output from the I/O trace. The counts are the number of pages read/written:
***************************************************************
I/O READ File: aaaaaaac.t00 (iidbdb, iirel_idx, 13) count:1
***************************************************************
***************************************************************
I/O READ File: aaaaaaab.t00 (iidbdb, iirelation, 0) count:8
***************************************************************
***************************************************************
I/O READ File: aaaaaaab.t00 (iidbdb, iirelation, 8) count:8
***************************************************************
I/O READ File: aaaaaaab.t00 (iidbdb, iirelation, 16) count:7
Note: When tracing the I/O or the locks of a parallel query (using set io_trace or set lock_trace with set parallel n), the trace messages from child threads of the QEP are logged to the II_DBMS_LOG. The trace messages for the main thread are sent to the user session in the normal manner.
|
|
|
Set lock_trace prints out information about locks within a transaction.
The following command turns lock tracing on as the default ING_SET level:
Windows:
set ING_SET=set lock_trace
UNIX:
C Shell:
setenv ING_SET "set lock_trace"
Bourne Shell:
ING_SET = "set lock_trace"export ING_SET
VMS:
DEFINE ING_SET "SET LOCK_TRACE"
|
|
|
The following features enable your application to display and trace events:
set [no]printdbevents
set [no]logdbevents
set_sql(dbeventdisplay = 1 | 0)
Specify a value of 1 to enable the display of received events, or 0 to disable the display of received events. You can also enable this feature using II_EMBED_SET.
You can create a routine that traps all events returned to an embedded SQL application. To enable/disable an event-handling routine or function, your embedded SQL application must issue the following set_sql statement:
exec sql set_sql(dbeventhandler = event_routine | 0)
To trap events to your event-handling routine, specify event_routine as a pointer to your error-handling function. For information about specifying pointers to functions, see your host language companion guide. In addition to issuing the set_sql statement, you must create the event-handling routine, declare it, and link it with your application.
|
|
|
The General Communication Facility (GCF) is composed of the GCA protocol and three communications programs: Name Server, Communications Server, and Data Access Server. These are separate processes. You can listen in on the communications between these GCF programs using the following Ingres environment variables and logicals:
|
|
|
You can trace communications occurring in the GCA with the following command:
Windows:
set II_EMBED_SET=printgca
UNIX:
C Shell:
setenv II_EMBED_SET "printgca"
Bourne Shell:
II_EMBED_SET = "printgca"
export II_EMBED_SET
VMS:
DEFINE II_EMBED_SET "PRINTGCA"
This traces all GCA messages passed between Ingres tools and the server. The output is placed into a file called iiprtgca.log.
|
|
|
Here is a sample output:
printgca = on session -2 (Thu Mar 26 15:50:32 2001)
GCA Service GCA_SEND
gca_association_id: 0
gca_message_type: GCA_QUERY
gca_data_length: 49
gca_end_of_data: TRUE
gca_data_area: GCA_Q_DATA
gca_language_id: 2
gca_query_modifier: 0x01
gca_qddata [0]:
gca_type: 51: DB_QTXT_TYPE
gca_precision: 0
gca_1_value: 29
gca_value: select name, gid from iiuser
End of GCA Message
GCA Service: GCA_RECEIVE
gca_association_id: 0
gca_message_type: GCA_TDESCR
gca_data_length: 55
gca_end_of_data: TRUE
gca_data_area: GCA_TD_DATA
gca_tsize: 26
gca_result_modifier: 0x01
gca_id_tdscr: 8
gca_1_col_desc: 2
gca_col_desc[0]:
gca_attdbv:
db_data: 0x00
db_length: 24
db_datatype: 20: DB_CHA_TYPE
db_prec: 0
gca_1_attname: 4
gca_attname: (4) name
gca_col_desc[1]
gca_attdbv:
db_data: 0x00
db_length: 2
db_datatype: 30: DB_INT_TYPE
db_prec: 0
gca_1_attname: 3
gca_attname: (3) gid
End of GCA Message
GCA Service: GCA_RECEIVE
gca_association_id: 0
gca_message_type: GCA_TUPLES
gca_data_length: 338
gca_end_of_data: TRUE
gca_data_area: GCA_TU_DATA not traced
End of GCA Message
|
|
|
The syntax of GCA trace utility are as follows:
define II_GCA_TRACE [n]
define II_GCA_LOG [filename]
II_GCx_TRACE defines the level of GCA tracing, with n = 1 (lowest) through 4 (most detailed). II_GCA_LOG names the destination file of the trace output. If not specified, it defaults to standard output.
Here is an example of level 2 tracing:
!GCcm: target_id: 9062
!GCrequest 0: connecting on 9062
!GC_exchange 0: status 00000000
!GC_exchange_sm 0: status 00000000 state 0
!GCsend 0: send 528
!GC_send_comp 0: sent 528 status 00000000
!GC_exchange_sm 0: status 00000000 state 1
!GCreceive 0: want 528
!GC_recv_colmp 0: status 000000 state 0
!GCsend 0: send 65
!GC_send_comp 0: sent 65 status 00000000
!GCreceive 0: want 24