SQL Tracing

Using ttTraceMon with the SQL component provides information about the SQL being prepared or executed by the TimesTen engine. Table 2-1 describes the levels for SQL tracing. Each level with a '+' sign includes the trace information described for that level, plus all levels preceding it.

Table 2-1 SQL tracing levels

Level	Output
2	SQL commands being prepared
3	+ SQL commands being executed
4	+ The effect of command pooling (prepares not being done because the prepared command already exists in the pool), the need for reprepares (for example, because an index was created), and commands being destroyed At this level, ttTraceMon also shows when a query command is being opened, fetched, and closed.
5	+ Some internal data, such as command numbers, which are not generally useful for customer-level debugging

Note:

TimesTen recommends tracing SQL at level 3 or 4. SQL tracing does not show any information about the optimizer. Optimizer tracing is managed by a separate component (OPT) at level 4 only, and is not designed for customer use.

In this example, execute ttTraceMon to do a SQL trace at level 4 on the database1 database. Direct the output from the SQL trace to the SQLtrace.txt file. Then flush the buffer so that the trace does not report past SQL statements.

% ttTraceMon database1
Trace monitor; empty line to exit
Trace > outfile SQLtrace.txt
Trace > level sql 4
Trace > flush

At this point, execute an application that includes the following SQL statement:

SELECT * FROM departments WHERE department_id = 10;

The trace information is written to the SQLtrace.txt file:

12:19:36.582     269 SQL      2L    3C  29570P Preparing: select * from 
departments where department_id = 10
12:19:36.583     270 SQL      4L    3C  29570P sbSqlCmdCompile ()(E): (Found 
already compiled version: refCount:01, bucket:28) cmdType:100, cmdNum:1000146.
12:19:36.583     271 SQL      4L    3C  29570P Opening: select * from departments
where department_id = 10;
12:19:36.583     272 SQL      4L    3C  29570P Fetching: select * from 
departments where department_id = 10;
12:19:36.583     273 SQL      4L    3C  29570P Closing: select * from departments
where department_id = 10;
5 records dumped

When the application has completed, turn off SQL tracing and exit ttTraceMon.

Trace > level sql 0
Trace > {press ENTER – blank line}