Enabling ODBC tracing on IBM i

With the native ODBC driver for IBM i being used in mission-critical applications, developers may be interested in the driver manager’s tracing feature. This tracing capability can help troubleshoot issues with ODBC and reveal details of how ODBC works.

Note: Use tracing only when necessary, because it will slow your queries. Turn off when done.

To enable tracing, edit /QOpenSys/etc/odbcinst.ini, adding the [ODBC] key and these two lines beneath it:

[ODBC]
Trace = Yes
TraceFile = /tmp/odbc.log

The resulting log, in the TraceFile you specified (/tmp/odbc.log), will look something like this:

[ODBC][1172450][1583089933.299842][SQLPrepare.c][196]
Entry: Statement = 18003bd50
SQL = [select * from qiws.qcustcdt ][length = 28]

[ODBC][1172450][1583089933.301607][SQLPrepare.c][377]
Exit: [SQL_SUCCESS]

[ODBC][1172450][1583089933.301646][SQLExecute.c][187]
Entry: Statement = 18003bd50

[ODBC][1172450][1583089933.303731][SQLExecute.c][357]
Exit: [SQL_SUCCESS]

[ODBC][1172450][1583089933.303770][SQLNumResultCols.c][156]
Entry: Statement = 18003bd50
Column Count = ffffffffffff6f0

[ODBC][1172450][1583089933.303801][SQLNumResultCols.c][251]
Exit: [SQL_SUCCESS]
Count = ffffffffffff6f0 -> 11

[ODBC][1172450][1583089933.303839][SQLNumResultCols.c][156]
Entry: Statement = 18003bd50
Column Count = ffffffffffff5f0

[ODBC][1172450][1583089933.303868][SQLNumResultCols.c][251]
Exit: [SQL_SUCCESS]
Count = ffffffffffff5f0 -> 11

The trace above contains several interesting elements, including the SQL, the number of columns, the success flag, the names of ODBC APIs such as SQLPrepare, SQLExecute, and SQLNumResultCols, as well as time stamps (1583089933.299842, 1583089933.301607) that may help you measure performance.

To disable tracing when you are done, set
Trace = No

For more information on using ODBC for IBM i, check out our Q&A about ODBC on IBM i.