ODBC trace

You can use various methods to trace for ODBC activity, depending on the operating system that you are using.

About this task

Windows platformFor Windows, use the Tracing tab of the ODBC function:
  1. Click Start > Settings > Control Panel > Administrative Tools.
  2. Double-click Data Sources.
  3. Click the Tracing tab.
  4. Click Start Tracing Now.
  5. Click OK.

To stop ODBC tracing, on the Tracing tab, click Stop Tracing Now, then OK.

Linux platformUNIX platformFor Linux® and UNIX operating systems using IBM® Integration ODBC Database Extender drivers:
  • To initiate trace for ODBC activity, edit the [ODBC] stanza in the odbcinst.ini file in the directory pointed to by your ODBCSYSINI environment variable as follows:
    1. Change Trace=no to Trace=yes.
    2. Specify a path and file name for TraceFile
    3. Ensure that the TraceFile entry points to a file system that has enough space to receive the trace output
z/OS platformFor z/OS®, to initiate application trace for ODBC activity:
  1. Edit the BIPDSNAO file in the component dataset and under the stanza entry [COMMON], change APPLTRACE=0 to APPLTRACE=1
  2. If the [COMMON] stanza in the BIPDSNAO member does not include the TRACEPIDTID parameter, then set TRACEPIDTID=1 to enable the process/thread IDs in the ODBC trace.
  3. Remove the comment from the COMPDIR variable declaration and the APPLTRC DD from the steps EGNOENV and EGENV, in the IBM Integration Bus started task JCL.
  4. Stop and restart the integration node after you have made all the changes to the BIPDSNAO file and the started task JCL.

    By default, the trace output file is written to <component_HFS>/output/, into a file called db2appltrace.. Each address space has a unique number, and the eight character integration server label appended to the end of db2appltrace.

    This unique number, appended to the ODBC file, is the SE number in the integration server address space JOBLOG.

    If the eight character integration server label is not unique across multiple integration servers, look for the value of SE in the JOBLOG for which you want to view the ODBC trace, and find the file that specifies this value.

Results

Example

IBM DB2® on IBM Integration Bus for z/OS

The following sample ODBC trace files show the layout of a trace file, together with some examples of successful and error returns. The general layout of each group in an ODBC file is that:
  • Each line is preceded by a process/thread ID and time stamp.
  • The first line displays what the call does.
  • The second line displays the return.
  • The third line displays the result.
The first trace file shows a trace where a call fails because an object does not have the correct authority to perform an action:
[000207A9 0000000000000021] [2008-09-24 15:49:20.544123] SQLAllocStmt( hDbc=2, phStmt=&1c7f9554 )
[000207A9 0000000000000021] [2008-09-24 15:49:20.544156] SQLAllocStmt( phStmt=1 )
[000207A9 0000000000000021] [2008-09-24 15:49:20.544163]     ---> SQL_SUCCESS

[000207A9 0000000000000021] [2008-09-24 15:49:20.544179] SQLFreeStmt( hStmt=1, fOption=SQL_CLOSE )
[000207A9 0000000000000021] [2008-09-24 15:49:20.544189] SQLFreeStmt( )
[000207A9 0000000000000021] [2008-09-24 15:49:20.544194]     ---> SQL_SUCCESS

[000207A9 0000000000000021] [2008-09-24 15:49:20.544205] SQLPrepare( hStmt=1 )
[000207A9 0000000000000021] [2008-09-24 15:49:20.544212] ( pszSqlStr="SELECT TESTTABLE.ID FROM 
WMQI77.TESTTABLE TESTTABLE", cbSqlStr=-3 )
[000207A9 0000000000000021] [2008-09-24 15:49:20.587083] SQLPrepare( )
[000207A9 0000000000000021] [2008-09-24 15:49:20.587101]     ---> SQL_ERROR

[000207A9 0000000000000021] [2008-09-24 15:49:20.587157] SQLError( hEnv=0, hDbc=0, hStmt=1, 
pszSqlState=&3902af28, pfNativeError=&3902af24, pszErrorMsg=&1b88b0b0, 
cbErrorMsgMax=1024, pcbErrorMsg=&3902aefc )
[000207A9 0000000000000021] [2008-09-24 15:49:20.587190] SQLError( pszSqlState="42501", pfNativeError=-551, 
pszErrorMsg="{DB2 FOR OS/390}{ODBC DRIVER}{DSN09015}
 DSNT408I SQLCODE = -551, ERROR:  WMQI83 DOES NOT HAVE THE PRIVILEGE TO PERFORM
          OPERATION SELECT ON OBJECT WMQI77.TESTTABLE
 DSNT418I SQLSTATE   = 42501 SQLSTATE RETURN CODE
 DSNT415I SQLERRP    = DSNXOSC SQL PROCEDURE DETECTING ERROR
 DSNT416I SQLERRD    = -100  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION
 DSNT416I SQLERRD    = X'FFFFFF9C'  X'00000000'  X'00000000'  X'FFFFFFFF'
          X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION
  ERRLOC=1:13:2", pcbErrorMsg=623 )
[000207A9 0000000000000021] [2008-09-24 15:49:20.587666]     ---> SQL_SUCCESS

[000207A9 0000000000000021] [2008-09-24 15:49:20.587725] SQLError( hEnv=0, hDbc=0, hStmt=1, 
pszSqlState=&3902af28, pfNativeError=&3902af24, pszErrorMsg=&1b88b0b0, 
cbErrorMsgMax=1024, pcbErrorMsg=&3902aefc )
[000207A9 0000000000000021] [2008-09-24 15:49:20.587752] SQLError( )
[000207A9 0000000000000021] [2008-09-24 15:49:20.587757]     ---> SQL_NO_DATA_FOUND

[000207A9 0000000000000021] [2008-09-24 15:49:20.588049] SQLFreeStmt( hStmt=1, fOption=SQL_DROP )
[000207A9 0000000000000021] [2008-09-24 15:49:20.588075] SQLFreeStmt( )
[000207A9 0000000000000021] [2008-09-24 15:49:20.588080]     ---> SQL_SUCCESS

[000207A9 0000000000000021] [2008-09-24 15:49:20.593800] SQLTransact( hEnv=1, hDbc=0, fType=SQL_COMMIT )
[000207A9 0000000000000021] [2008-09-24 15:49:20.593887] SQLTransact( )
[000207A9 0000000000000021] [2008-09-24 15:49:20.593893]     ---> SQL_SUCCESS
The second trace file shows the same trace file with the operation working:
[000207A9 0000000000000021] [2008-09-24 16:00:25.287052] SQLAllocStmt( hDbc=1, phStmt=&1c7f8e54 )
[000207A9 0000000000000021] [2008-09-24 16:00:25.287068] SQLAllocStmt( phStmt=1 )
[000207A9 0000000000000021] [2008-09-24 16:00:25.287075]     ---> SQL_SUCCESS

[000207A9 0000000000000021] [2008-09-24 16:00:25.287088] SQLFreeStmt( hStmt=1, fOption=SQL_CLOSE )
[000207A9 0000000000000021] [2008-09-24 16:00:25.287098] SQLFreeStmt( )
[000207A9 0000000000000021] [2008-09-24 16:00:25.287104]     ---> SQL_SUCCESS

[000207A9 0000000000000021] [2008-09-24 16:00:25.287114] SQLPrepare( hStmt=1 )
[000207A9 0000000000000021] [2008-09-24 16:00:25.287121] ( pszSqlStr="SELECT TESTTABLE.ID FROM 
WMQI77.TESTTABLE TESTTABLE", cbSqlStr=-3 )
[000207A9 0000000000000021] [2008-09-24 16:00:25.302484] SQLPrepare( )
[000207A9 0000000000000021] [2008-09-24 16:00:25.302510]     ---> SQL_SUCCESS

[000207A9 0000000000000021] [2008-09-24 16:00:25.302539] SQLFreeStmt( hStmt=1, 
fOption=SQL_CLOSE )
[000207A9 0000000000000021] [2008-09-24 16:00:25.302555] SQLFreeStmt( )
[000207A9 0000000000000021] [2008-09-24 16:00:25.302560]     ---> SQL_SUCCESS

[000207A9 0000000000000021] [2008-09-24 16:00:25.302573] SQLExecute( hStmt=1 )
[000207A9 0000000000000021] [2008-09-24 16:00:25.302622] SQLExecute( )
[000207A9 0000000000000021] [2008-09-24 16:00:25.302628]     ---> SQL_SUCCESS

[000207A9 0000000000000021] [2008-09-24 16:00:25.302660] SQLNumResultCols( hStmt=1, 
pcCol=&3902c7fa )
[000207A9 0000000000000021] [2008-09-24 16:00:25.302672] SQLNumResultCols( pcCol=1 )
[000207A9 0000000000000021] [2008-09-24 16:00:25.302679]     ---> SQL_SUCCESS

[000207A9 0000000000000021] [2008-09-24 16:00:25.302697] SQLDescribeCol( hStmt=1, iCol=1, 
pszColName=&3902cb10, cbColNameMax=200, pcbColName=&3902c804, 
pfSQLType=&3902c802, pcbColDef=&3902c858, pibScale=&3902c800, 
pfNullable=&3902c7fe )
[000207A9 0000000000000021] [2008-09-24 16:00:25.302733] SQLDescribeCol( pszColName="ID", 
pcbColName=2, pfSQLType=SQL_CHAR, pcbColDef=10, pibScale=0, 
pfNullable=SQL_NULLABLE )
[000207A9 0000000000000021] [2008-09-24 16:00:25.302819]     ---> SQL_SUCCESS

[000207A9 0000000000000021] [2008-09-24 16:00:25.302826] SQLColAttribute( hStmt=1, iCol=1, 
fDescType=SQL_DESC_OCTET_LENGTH, rgbDesc=NULL, cbDescMax=0, 
pcbDesc=NULL, pfDesc=&3902c864 )
[000207A9 0000000000000021] [2008-09-24 16:00:25.302850] SQLColAttribute( pfDesc=10 )
[000207A9 0000000000000021] [2008-09-24 16:00:25.302857]     ---> SQL_SUCCESS

[000207A9 0000000000000021] [2008-09-24 16:00:25.302866] SQLBindCol( hStmt=1, iCol=1, 
fCType=SQL_C_CHAR, rgbValue=&1b48829c, cbValueMax=12, 
pcbValue=&1b488298 )
[000207A9 0000000000000021] [2008-09-24 16:00:25.302888] SQLBindCol( )
[000207A9 0000000000000021] [2008-09-24 16:00:25.302894]     ---> SQL_SUCCESS

[000207A9 0000000000000021] [2008-09-24 16:00:25.302901] SQLSetStmtAttr( hStmt=1, 
fAttribute=SQL_ATTR_ROW_BIND_TYPE, pvParam=&10, iStrLen=0 )
[000207A9 0000000000000021] [2008-09-24 16:00:25.302917] SQLSetStmtAttr( )
[000207A9 0000000000000021] [2008-09-24 16:00:25.302922]     ---> SQL_SUCCESS

[000207A9 0000000000000021] [2008-09-24 16:00:25.302928] SQLSetStmtAttr( hStmt=1, 
fAttribute=Unknown value 9, pvParam=&20, iStrLen=0 )
[000207A9 0000000000000021] [2008-09-24 16:00:25.302943] SQLSetStmtAttr( )
[000207A9 0000000000000021] [2008-09-24 16:00:25.302949]     ---> SQL_SUCCESS

[000207A9 0000000000000021] [2008-09-24 16:00:25.302956] SQLExtendedFetch( hStmt=1, 
fFetchType=SQL_FETCH_NEXT,iRow=0, pcRow=&1c7f6894, 
rgfRowStatus=&1bca17d0 )
[000207A9 0000000000000021] [2008-09-24 16:00:25.317947] ( Row=1, iCol=1, fCType=SQL_C_CHAR, 
rgbValue="TABLG     ", pcbValue=10 )
[000207A9 0000000000000021] [2008-09-24 16:00:25.317980] ( Row=2, iCol=1, fCType=SQL_C_CHAR, 
rgbValue="TABLF     ", pcbValue=10 )
[000207A9 0000000000000021] [2008-09-24 16:00:25.318001] ( Row=3, iCol=1, fCType=SQL_C_CHAR, r
gbValue="TABLE     ", pcbValue=10 )
[000207A9 0000000000000021] [2008-09-24 16:00:25.318022] ( Row=4, iCol=1, fCType=SQL_C_CHAR, 
rgbValue="TABLD     ", pcbValue=10 )
[000207A9 0000000000000021] [2008-09-24 16:00:25.318044] ( Row=5, iCol=1, fCType=SQL_C_CHAR, 
rgbValue="TABLC     ", pcbValue=10 )
[000207A9 0000000000000021] [2008-09-24 16:00:25.318065] ( Row=6, iCol=1, fCType=SQL_C_CHAR, 
rgbValue="TABLB     ", pcbValue=10 )
[000207A9 0000000000000021] [2008-09-24 16:00:25.318087] ( Row=7, iCol=1, fCType=SQL_C_CHAR, 
rgbValue="TABLA     ", pcbValue=10 )
[000207A9 0000000000000021] [2008-09-24 16:00:25.318109] SQLExtendedFetch( pcRow=7 )