SQL Interface Trace Option

The SQL trace option is used to trace the internal SQL code. This includes information such as the generated SELECT / UPDATE / etc. statements. This option is typically used along with the General Trace option TRACEFILE, which shows the higher level code (such as the contents of the current record being read / written). This trace option is actually one of the General Trace options, called TRACESQL.

There are currently problems with the level settings for TRACESQL. At present, this violates the standard used in all other trace areas, in which a higher level of trace causes more information to be displayed. For TRACESQL, some information displays only if you set level = 2, or level = 9. Other information is only displayed for level = 3, or level = 8 or greater. Most of the Microsoft SQL Server trace is only displayed for level = 8 exactly. So currently the recommended trace levels are:

Generic SQL layers (common to all SQL databases). Level = 2 or 3 for some specific SQL code areas; 15 for all.
Oracle Driver. Level = 8 or greater for most messages, though some are only displayed for level 3 exactly.
DB2 Driver. Level = 3 for main DB2 trace code, 8 or 9 for most / all.
MS SQL Server Driver. Level = 8 exactly. This displays all information.

In general, level = 3 produces a lot of trace information, and should be tried first. If this is not enough, try level = 8 or 9, depending on which database driver you are using. However, with Oracle you will lose a lot a trace information if you do not use level 3.

Comment on this topic

Topic ID: 750053