Using the Database Trace tool

The section describes how to use the Database Trace tool.

About the Database Trace tool

The Database Trace tool records the internal commands that InfoMaker executes while accessing a database. You can trace a database connection in the development environment.

InfoMaker writes the output of Database Trace to a log file named PBTRACE.LOG (by default) or to a nondefault log file that you specify. When you enable database tracing for the first time, InfoMaker creates the log file on your computer. Tracing continues until you disconnect from the database.

Using the Database Trace tool with one connection

You can use the Database Trace tool for only one DBMS at a time and for one database connection at a time.

For example, if your application connects to both an ODBC data source and an Adaptive Server Enterprise database, you can trace either the ODBC connection or the Adaptive Server Enterprise connection, but not both connections at the same time.

How you can use the Database Trace tool

You can use information from the Database Trace tool to help you understand what InfoMaker is doing internally when you work with your database. Examining the information in the log file can help you:

  • Understand how InfoMaker interacts with your database

  • Identify and resolve problems with your database connection

  • Provide useful information to Technical Support if you call them for help with your database connection

If you are familiar with InfoMaker and your DBMS, you can use the information in the log to help troubleshoot connection problems on your own.

If you are less experienced or need help, run the Database Trace tool before you call Technical Support. You can then report or send the results of the trace to the Technical Support representative who takes your call.

Location of the Database Trace log

PBTRACE.LOG

By default, InfoMaker writes output of the Database Trace tool to a file named PBTRACE.LOG in your Windows directory.

Nondefault log file

If you prefer, you can specify a nondefault name and location for the log file when you use Database Trace. For instructions, see Specifying a nondefault Database Trace log.

Contents of the Database Trace log

The Database Trace tool records the following information in the log file when you trace a database connection:

  • Parameters used to connect to the database

  • Time to perform each database operation (in milliseconds)

  • The internal commands executed to retrieve and display table and column information from your database. Examples include:

    • Preparing and executing SQL statements such as SELECT, INSERT, UPDATE, and DELETE

    • Getting column descriptions

    • Fetching table rows

    • Binding user-supplied values to columns (if your database supports bind variables)

    • Committing and rolling back database changes

    • Disconnecting from the database

    • Shutting down the database interface

Format of the Database Trace log

The specific content of the Database Trace log file depends on the database you are accessing and the operations you are performing. However, the log uses the following basic format to display output:

COMMAND: (time)
   {additional_information}

Parameter

Description

COMMAND

The internal command that InfoMaker executes to perform the database operation.

time

The number of milliseconds it takes InfoMaker to perform the database operation. The precision used depends on your operating system's timing mechanism.

additional_information

(Optional) Additional information about the command. The information provided depends on the database operation.


Example

The following portion of the log file shows the commands InfoMaker executes to fetch two rows from a database table:

FETCH NEXT: (77 MilliSeconds)
   dept_id=   dept_name=Business Services
FETCH NEXT: (4 MilliSeconds)
   dept_id=   dept_name=Corporate Management

For a more complete example of Database Trace output, see Sample Database Trace output.

Starting the Database Trace tool

By default, the Database Trace tool is turned off in InfoMaker. You can start it to trace your database connection.

To start the Database Trace tool:

  1. Open the Database Profile Setup dialog box for the connection you want to trace.

  2. On the Connection tab, select the Generate Trace check box and click OK or Apply. (The Generate Trace check box is located on the System tab in the OLE DB Database Profile Setup dialog box.)

    The Database Profiles dialog box displays with the name of the edited profile highlighted.

    For example, here is the relevant portion of a database profile entry for Adaptive Server 12.5 Test. The setting that starts Database Trace is DBMS:

    [Default]     [value not set]
    AutoCommit    "FALSE"
    Database      "qadata"
    DatabasePassword  "00"
    DBMS          "TRACE SYC Adaptive Server Enterprise"
    DbParm        "Release='12.5'"
    Lock          ""
    LogId         "qalogin"
    LogPassword   "00171717171717"
    Prompt        "FALSE"
    ServerName    "Host125"
    UserID        ""
  3. Click Connect in the Database Profiles dialog box to connect to the database.

    A message box displays stating that database tracing is enabled and indicating where InfoMaker will write the output. (By default, InfoMaker writes Database Trace output to a log file named PBTRACE.LOG.)

    For instructions on specifying your own name and location for the Database Trace log file, see Specifying a nondefault Database Trace log.

  4. Click OK.

    InfoMaker connects to the database and starts tracing the connection.

Stopping the Database Trace tool

Once you start tracing a particular database connection, InfoMaker continues sending trace output to the log until you do one of the following:

  • Reconnect to the same database with tracing stopped

  • Connect to another database for which you have not enabled tracing

To stop the Database Trace tool:

  1. In the Database Profile Setup dialog box for the database you are tracing, clear the Generate Trace check box on the Connection tab.

  2. Click OK in the Database Profile Setup dialog box.

    The Database Profiles dialog box displays with the name of the edited profile highlighted.

  3. Right-click on the connected database and select Re-connect from the dropdown menu in the Database Profiles dialog box.

    InfoMaker connects to the database and stops tracing the connection.

Specifying a nondefault Database Trace log

In the InfoMaker development environment, you can specify a nondefault name and location for the log file when you use Database Trace.

What you can do

By specifying a nondefault Database Trace log to use in the development environment, you can:

  • Control where InfoMaker writes the output of the Database Trace tool

  • Give the log file a name and location that best meets the development needs at your site

By default, InfoMaker writes Database Trace output to a log file named PBTRACE.LOG located in your Windows directory. You can override this default in the development environment by editing your InfoMaker initialization file.

How to do it

To specify a nondefault Database Trace log file:

  1. Open the InfoMaker initialization file for editing.

    You can use the File Editor (in InfoMaker) or any text editor (outside InfoMaker).

  2. Create an entry named DBTraceFile in the [Database] section of the initialization file, using the following syntax to specify a nondefault log file:

    DBTraceFile=log_file_pathname

    For example:

    [Database]
    ...
    DBTraceFile=c:\temp\mydbtrce.log
  3. Save your changes to the initialization file.

    The next time you use the Database Trace tool to trace a connection in the development environment, InfoMaker writes the output to the log file you specified instead of to the default PBTRACE.LOG file.

    For instructions on starting Database Trace, see Starting the Database Trace tool.

Using the Database Trace log

InfoMaker writes the output of the Database Trace tool to a file named PBTRACE.LOG (by default) or to a nondefault log file that you specify. To use the trace log, you can do the following anytime:

  • View the Database Trace log with any text editor

  • Annotate the Database Trace log with your own comments

  • Delete the Database Trace log or clear its contents when it becomes too large

For information about where to find PBTRACE.LOG, see Location of the Database Trace log.

For instructions about specifying your own Database Trace log to use in the development environment, see Specifying a nondefault Database Trace log.

Viewing the Database Trace log

You can display the contents of the log file anytime during an InfoMaker session.

To view the contents of the log file:

  1. Open the log file in one of the following ways:

  2. Use the File Editor in InfoMaker. (For instructions, see the User's Guide.)

  3. Use any text editor outside InfoMaker.

Leaving the log file open

If you leave the log file open as you work in InfoMaker, the Database Trace tool does not update the log.

Annotating the Database Trace log

When you use the Database Trace log as a troubleshooting tool, it might be helpful to add your own comments or notes to the file. For example, you can specify the date and time of a particular connection, the versions of database server and client software you used, or any other useful information.

To annotate the log file:

  1. Open the PBTRACE.LOG file in one of the following ways:

    • Use the File Editor in InfoMaker. (For instructions, see the User's Guide.)

    • Use any text editor outside InfoMaker.

  2. Edit the log file with your comments.

  3. Save your changes to the log file.

Deleting or clearing the Database Trace log

Each time you connect to a database with tracing enabled, InfoMaker appends the trace output of your connection to the existing log. As a result, the log file can become very large over time, especially if you frequently enable tracing when connected to a database.

To keep the size of the log file manageable:

  1. Do either of the following periodically:

  2. Open the log file, clear its contents, and save the empty file.

  3. Provided that you use the default PBTRACE.LOG or the same nondefault file the next time you connect to a database with tracing enabled, InfoMaker will write to this empty file.

  4. Delete the log file.

  5. InfoMaker will automatically create a new log file the next time you connect to a database with tracing enabled.

Sample Database Trace output

This section gives an example of Database Trace output that you might see in the log file and briefly explains each portion of the output.

The example traces a connection to an ODBC database named Sample. The output was generated while running an InfoMaker application that displays information about employees in each department. The SELECT statement shown retrieves information from the Employee table to display the names of employees in department 100.

The precision (for example, milliseconds) used when Database Trace records internal commands depends on your operating system's timing mechanism. Therefore, the timing precision in your Database Trace log might vary from this example.

Connect to database

DIALOG CONNECT TO TRACE SYCAdaptive Server Enterprise:
USERID=
SERVER=HOST12
DATA=QATEST
DPPARM=Release='12'

Prepare SELECT statement

PREPARE: 
SELECT employee.emp_id, employee.emp_lname,
employee.emp_fname, employee.dept_id FROM employee
WHERE ( employee.dept_id = 100 )
ORDER BY employee.emp_lname ASC (94 MilliSeconds)

Get column descriptions

DESCRIBE: (0 MilliSeconds)
name=emp_id,len=4,type=????,pbt5,dbt3,ct0,dec0
name=emp_lname,len=21,type=CHAR,pbt1,dbt1,ct0,dec0
name=emp_fname,len=21,type=CHAR,pbt1,dbt1,ct0,dec0
name=dept_id,len=4,type=????,pbt5,dbt3,ct0,dec0

Bind memory buffers to columns

BIND SELECT OUTPUT BUFFER (DataWindow):
(890 MilliSeconds)
name=emp_id,len=4,type=FLOAT,pbt3,dbt3,ct0,dec0
name=emp_lname,len=21,type=CHAR,pbt1,dbt1,ct0,dec0
name=emp_fname,len=21,type=CHAR,pbt1,dbt1,ct0,dec0
name=dept_id,len=4,type=FLOAT,pbt3,dbt3,ct0,dec0

Execute SELECT statement

EXECUTE: (0 MilliSeconds)

Fetch rows from result set

FETCH NEXT: (156 MilliSeconds)
      emp_id=   emp_lname=Jones   emp_fname=Alan
      dept_id=
FETCH NEXT: (0 MilliSeconds)
      emp_id=   emp_lname=Ciccone   emp_fname=Peter
      dept_id=
FETCH NEXT: (0 MilliSeconds)
      emp_id=   emp_lname=Houston   emp_fname=Mary
      dept_id=
FETCH NEXT: (0 MilliSeconds)
      emp_id=   emp_lname=Smith      emp_fname=Susan
      dept_id=
FETCH NEXT: (0 MilliSeconds)
      emp_id=   emp_lname=Stein      emp_fname=David
      dept_id=
FETCH NEXT: (0 MilliSeconds)
      emp_id=   emp_lname=Watson   emp_fname=Linda
      dept_id=
FETCH NEXT: (0 MilliSeconds)
Error 1 (rc 100)

Commit database changes

COMMIT: (55 MilliSeconds )

Disconnect from database

DISCONNECT: (0 MilliSeconds)

Shut down database interface

SHUTDOWN DATABASE INTERFACE: (203 MilliSeconds)