Tracing iSeries Access for Windows ODBC Jobs

---

  Following is the process for capturing the traces commonly required for ODBC problem determination. It starts a job trace, a debug job log, a communications trace, and a database monitor trace on IBM i. On the PC, it starts an ODBC trace and an IBM i Access cwbcotrc trace.

Note: The user profile used for the ODBC connection must be authorized to the job trace and DBMON commands.

To begin, do the following:

Step 1. Start the ODBC trace.

In order to be sure that Windows authorities do not prevent the trace file from being created, we recommend that you change the log file path for the trace to point to the public documents folder. In Windows XP and older systems, the path is typically "C:\Documents And Settings\All Users\Documents".  Note, Windows Explorer often displays this folder as "Shared Documents". On Windows Vista and newer operating systems, the equivalent path is "C:\Users\Public\Documents". In the following example, the log file is set to "C:\Users\Public\Documents\SQL.LOG". Click the Apply button after you set the path, then click Start Tracing Now. For more information about collecting client-side traces, see Gathering an ODBC Trace.




Step 2a: Start Diagnostics.

Continuing in ODBC Administration, select the data source being used and press the Configure button. Select the Diagnostic tab and set the options specified by IBM support (tracing, database monitor, debug job log, and trace job). The user profile being traced must be authorized to the trace commands and to the QUSRSYS library. Press the Help button or the F1 key for further information.




Step 2b: Start the cwbcotrc trace with detail information.

Use the cwbcotrc to capture detail information as described in document, Gathering an ODBC Trace.

The ODBC trace might fail to start for applications that run as a Windows service. To circumvent the problem, see Gathering an ODBC Trace

Step 3: Start a TCP/IP connection trace.

From a different PC, start a PC5250 session and start a TCP/IP connection trace. We recommend starting the trace from a different PC to minimize the size of the trace.

To trace a specific PC TCP/IP address, on the i5/OS command line, type the following:

TRCCNN SET(*ON) TRCTYPE(*IP) TRCTBL(MYTRCTBL) SIZE(128000) TCPDTA(*TCP () () *N 'nnn.nnn.nnn.nnn')

Note: The minimum recommended trace size is 128000 KB/128 MB.
Replace nnn.nnn.nnn.nnn with the IP address of the PC or client used to re-create the problem.

To trace all TCP/IP addresses, on the i5/OS command line, type the following:

TRCCNN SET(*ON) TRCTYPE(*IP) TRCTBL(MYTRCTBL) SIZE(128000) TCPDTA(*TCP () () )

Step 4: Re-create the error on the test PC. Then, end the ODBC connection.

Step 5: End and format the TCP/IP connection trace.

To stop the trace, on the operating system command line, type the following:

TRCCNN SET(*OFF) TRCTBL(MYTRCTBL) FMTDTA(*CALC)

This command produces two spooled files. You can delete the one named QSYSPRT.

Step 6: Locate the spooled files generated by the debug job log, the TRCJOB, and the connection trace and copy them to your PC.
 

a.	Open the system in Navigator for i.
b.	Expand your system name, Basic Operations, and select Printer Output. The spooled files created are QPJOBLOG (debug job log), QPSRVTRC (job trace), and QPCSMPRT (connection trace). If the trace connection was run under a different user ID, select 'Include...' from the 'Actions' menu and change the user ID to the user ID that was used to collect the trace or make the ODBC connection.
c.	Select the spooled files and click 'Export as' then 'ASCII text' from the 'Actions' menu.  Click the 'OK' button to confirm the download.
d.	Send the files to the IBM i Global Support Center in step 9

Step 7: Copy the DBMON output to your PC.

The database monitor creates an output file QUSRSYS/QODBnnnnnn where nnnnnn is the job number of the ODBC job (the job number is in the QPJOBLOG file). Create a save file, save the database monitor report to the save file, and binary FTP it to your PC. From there, the file can be attached to the case or it can be uploaded to the Support Center FTP site. Run the following IBM i command to create a save file to store the DBMON output:

CRTSAVF FILE(YOURLIB/DBMONSAVF)

Replace YOURLIB with the name of the library where you want to create the SAVF. Then, run the following command to save the DBMON output into the new save file:

SAVOBJ OBJ(QODBnnnnnn) LIB(QUSRSYS) DEV(*SAVF) SAVF(YOURLIB/DBMONSAVF)

Download the save file to your PC by using FTP in binary format. From the PC DOS prompt, type:

1.	FTP YOURAS400 Replace YOURAS400 with the name of your system.
2.	Enter your IBM i user profile when prompted for it.
3.	Enter your IBM i password when prompted for it.
4.	Enter the BIN command at the FTP prompt to set the transfer mode to binary.
5.	Enter the command CD YOURLIB at the FTP prompt where YOURLIB is the library that you created the save file in at the beginning of this step.
6.	Enter the command GET DBMONSAVF at the FTP prompt to copy the save file to your PC.
7.	Enter the command QUIT at the FTP prompt to end the FTP connection.

Step 8: End the PC traces and disable Diagnostics.
 

1.	Clear all the trace options on the ODBC data source diagnostic tab.
2.	Disable the ODBC trace by using the ODBC Tracing tab.
3.	End the cwbcotrc trace by using the command: cwbcotrc off.  For more information about the cwbcotrc, see Gathering an ODBC Trace.

Step 9: Submit the data to IBM

There are several files collected on your PC by this time so we recommend that you compress them into a single, compressed file before you send the data to IBM's secure data repository. You can do that with the built-in windows support (Select all of the files in windows explorer, right-click, and select Send To > Compressed Folder) or you can use a 3rd party utility such as 7Zip. Be sure to include the ODBC trace (Step 2a), the cwbcotrc trace (Step 2b), the 3 spooled files (Step 6), and the database monitor save file (Step 7). If the resulting compressed file is small enough for your mail server to allow it to be sent as an attachment, you can email the compressed file to ibmi_support@ecurep.ibm.com. Set the subject to your case number. Alternatively, you can use the web-based upload tool at http://www.ecurep.ibm.com or use one of the alternative tools or methods linked to and documented here: http://www.ibm.com/support/pages/node/739401