ODBC trace

There are various methods for initiating trace for ODBC activity, depending on the operating system that you are using.

Windows platform For 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.

UNIX platform For Linux® and UNIX® operating systems, edit the ODBC.ini file to initiate trace for ODBC activity (if you are using the default ODBC.ini file, located at /var/wmqi/odbc/.odbc.ini):
  1. Under the stanza entry [ODBC], change Trace=0 to Trace=1
  2. Optionally, modify the TraceFile file path value to a preferred value. All trace records go to one file, which can be confusing if you have multiple flows or execution groups.

Trace information is output to the location that is specified in the .odbc.ini file, or in the ODBC configuration on Windows. By default on non-Windows systems, this is /var/wmqi/odbc/odbctrace.out.

z/OS platform For z/OS®, to initiate application trace for ODBC activity: Start of change
  1. Edit the BIPDSNAO file in the component dataset and under the stanza entry [COMMON], change APPLTRACE=0 to APPLTRACE=1
  2. Remove the comment from the COMPDIR variable declaration and the APPLTRACE DD in the WebSphere® Message Broker started task JCL.
  3. Stop and restart the broker after you have made 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 appended to the end of db2appltrace..

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

    You should look for the value of SE in the JOBLOG for which you want to view the ODBC trace, and find the file that is named db2appltrace.<SE number>.

End of change
Start of change

DB2® on WebSphere Message Broker 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 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:
[2008-09-24 15:49:20.544123] SQLAllocStmt( hDbc=2, phStmt=&1c7f9554 )
[2008-09-24 15:49:20.544156] SQLAllocStmt( phStmt=1 )
[2008-09-24 15:49:20.544163]     ---> SQL_SUCCESS

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

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

[2008-09-24 15:49:20.587157] SQLError( hEnv=0, hDbc=0, hStmt=1, 
pszSqlState=&3902af28, pfNativeError=&3902af24, pszErrorMsg=&1b88b0b0, 
cbErrorMsgMax=1024, pcbErrorMsg=&3902aefc )
[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 )
[2008-09-24 15:49:20.587666]     ---> SQL_SUCCESS

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

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

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

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

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

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

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

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

[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 )
[2008-09-24 16:00:25.302733] SQLDescribeCol( pszColName="ID", 
pcbColName=2, pfSQLType=SQL_CHAR, pcbColDef=10, pibScale=0, 
pfNullable=SQL_NULLABLE )
[2008-09-24 16:00:25.302819]     ---> SQL_SUCCESS

[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 )
[2008-09-24 16:00:25.302850] SQLColAttribute( pfDesc=10 )
[2008-09-24 16:00:25.302857]     ---> SQL_SUCCESS

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

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

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

[2008-09-24 16:00:25.302956] SQLExtendedFetch( hStmt=1, 
fFetchType=SQL_FETCH_NEXT,iRow=0, pcRow=&1c7f6894, 
rgfRowStatus=&1bca17d0 )
[2008-09-24 16:00:25.317947] ( Row=1, iCol=1, fCType=SQL_C_CHAR, 
rgbValue="TABLG     ", pcbValue=10 )
[2008-09-24 16:00:25.317980] ( Row=2, iCol=1, fCType=SQL_C_CHAR, 
rgbValue="TABLF     ", pcbValue=10 )
[2008-09-24 16:00:25.318001] ( Row=3, iCol=1, fCType=SQL_C_CHAR, r
gbValue="TABLE     ", pcbValue=10 )
[2008-09-24 16:00:25.318022] ( Row=4, iCol=1, fCType=SQL_C_CHAR, 
rgbValue="TABLD     ", pcbValue=10 )
[2008-09-24 16:00:25.318044] ( Row=5, iCol=1, fCType=SQL_C_CHAR, 
rgbValue="TABLC     ", pcbValue=10 )
[2008-09-24 16:00:25.318065] ( Row=6, iCol=1, fCType=SQL_C_CHAR, 
rgbValue="TABLB     ", pcbValue=10 )
[2008-09-24 16:00:25.318087] ( Row=7, iCol=1, fCType=SQL_C_CHAR, 
rgbValue="TABLA     ", pcbValue=10 )
[2008-09-24 16:00:25.318109] SQLExtendedFetch( pcRow=7 )
End of change
Related tasks
Troubleshooting and support
Using trace
Related reference
Supported databases
Database facilities
Notices | Trademarks | Downloads | Library | Support | Feedback

Copyright IBM Corporation 1999, 2009Copyright IBM Corporation 1999, 2009.
Last updated : 2009-01-07 15:23:23

au14215_