Using the Database Trace tool – PB Docs 70

  2. Powerbuilder Documentation
  3. Powerbuilder Documentation Older
  4. Powerbuilder Documentation 7.0
  5. Using the Database Trace tool – PB Docs 70

Using the Database Trace tool

The section describes how to use the Database Trace tool.

About the DatabaseTrace tool

The Database Trace tool records the internal commands that PowerBuilder executes
while accessing a database. You can trace a database connection
in the development environment or in a PowerBuilder application
that connects to a database.

PowerBuilder 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, PowerBuilder creates
the log file on your computer. Tracing continues until you disconnect
from the database.

note.gif Using the Database Trace tool with one connection You can only use the Database Trace tool for 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 PowerBuilder is doing internally
when
you work with your database. Examining the information in the log
file can help you:

  • Understand how PowerBuilder 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 PowerBuilder 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 DatabaseTrace log on different platforms

PBTRACE.LOG

By default, PowerBuilder writes output of the Database Trace
tool to a file named PBTRACE.LOG.

The location of PBTRACE.LOG depends on the platform you are
using:

Platform Location
Windows PBTRACE.LOG in your Windows directory
UNIX $HOME/pbtrace.log

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 contents 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:

Parameter Description
COMMAND The internal command that PowerBuilder executes to
perform the database operation
time The number of milliseconds it takes PowerBuilder to
perform the database operation. The precision used depends on your
operating system’s timing mechanismFor example, on Windows, the Database Trace timer rounds down
to the nearest 55-millisecond increment. If an operation takes 54
milliseconds or less to perform, the log displays the time as 0 milliseconds.
If an operation takes between 55 and 109 milliseconds, the time
displays as 55 milliseconds
additional_information (Optional) Additional information about
the command. The information provided depends on the database operation

Example

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

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

Starting the DatabaseTrace tool

By default, the Database Trace tool is turned off in PowerBuilder.
You can start it in the PowerBuilder development environment or in
a PowerBuilder application to trace your database connection.

To start Database Trace in Do this
The PowerBuilder development environment Edit your database profile
A PowerBuilder application Edit your application script

Starting Database Tracein the development environment

To start the Database Trace tool in the PowerBuilder development environment,
edit the database profile for the connection you want to trace,
as described in the following procedure.

proc.gif To start the Database Trace tool by editing a
database profile:

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

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

    cn8dbtr.gif

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

    For example, here is the relevant portion of the database
    profile entry for Sybase System 11 Test. The setting that starts
    Database Trace is shown in bold.

  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 PowerBuilder will write the output. (By default, PowerBuilder 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.

    PowerBuilder connects to the database and starts tracing the
    connection.

Starting Database Trace in a PowerBuilder application

In a PowerBuilder application that connects to a database, you
must specify the required connection parameters in the appropriate
script. For example, you might specify them in the script that opens
the application.

To trace a database connection in a PowerBuilder script, you
specify the name of the DBMS preceded by the word trace
and
a single space. You can do this by:

  • Copying the PowerScript DBMS trace syntax from the Preview
    tab in the Database Profile Setup dialog box into your script
  • Coding PowerScript to set a value for the DBMS property
    of the Transaction object
  • Reading the DBMS value from an external text file

For more about using Transaction objects to
communicate with a database in a PowerBuilder application, see Application
Techniques
.

Copying DBMS trace syntax from the Preview tab

One way to start Database Trace in a PowerBuilder application
script is to copy the PowerScript DBMS trace syntax from the Preview
tab in the Database Profile Setup dialog box into your script, modifying
the default Transaction object name (SQLCA) if necessary.

As you complete the Database Profile Setup dialog box in the
development environment, PowerBuilder generates the correct connection
syntax on the Preview tab for each selected option, including Generate
Trace. Therefore, copying the syntax directly from the Preview tab
ensures that it is accurate in your script.

proc.gif To copy DBMS trace syntax from the Preview tab
into your script:

  1. On the Connection tab (or System tab in
    the case of OLE DB) in the Database Profile Setup dialog box for
    your connection, select the Generate Trace checkbox to turn on Database
    Trace.

    For instructions, see “Starting Database Trace
    in the development environment”    .

  2. Click Apply to save your changes to the Connection
    tab without closing the Database Profile Setup dialog box.

  3. Click the Preview tab.

    The correct PowerScript connection syntax for the Generate
    Trace and other selected options displays in the Database Connection
    Syntax box. For example:

    cn8trpr.gif

  4. Select the SQLCA.DBMS line and any other syntax
    you want to copy to your script and click Copy.

    PowerBuilder copies the selected text to the clipboard.

  5. Click OK to close the Database Profile Setup dialog
    box.

  6. Paste the selected text from the Preview tab into
    your script, modifying the default Transaction object name (SQLCA)
    if necessary.

Coding PowerScript to set a value for the DBMS property

Another way to start the Database Trace tool in a PowerBuilder script
is to specify it as part of the DBMS property of the Transaction
object. The Transaction object is a special
nonvisual object that PowerBuilder uses to communicate with the database.
The default Transaction object is named SQLCA, which stands for
SQL Communications Area.

SQLCA has 15 properties, 10 of which are used to connect to
your database. One of the 10 connection properties is DBMS. The
DBMS property contains the name of the database to which you want
to connect.

proc.gif To start the Database Trace tool by specifying
the DBMS property:

  1. Use the following PowerScript syntax to
    specify the DBMS property. (This syntax assumes you are using the
    default Transaction object SQLCA, but you can also define your own
    Transaction object.)

    For example, the following statements in a PowerBuilder script
    set the SQLCA properties required to connect to a SQL Server 4.x
    database named Test. The keyword trace
    in the
    DBMS property indicates that you want to trace the database connection.

Reading the DBMS value from an external text file

As an
alternative to setting the DBMS property in your PowerBuilder application
script, you can use the PowerScript ProfileString function to read the
DBMS value from a specified section of an external text file, such
as an application-specific initialization file.

The following procedure assumes that the DBMS value read from
your initialization file uses the following syntax to enable database
tracing:

proc.gif To start the Database Trace tool by reading the
DBMS value from an external text file:

  1. Use the following PowerScript syntax to
    specify the ProfileString function with the DBMS property:

    For example, the following statement in a PowerBuilder script
    reads the DBMS value from the [Database] section
    of the APP.INI file:

Stopping the DatabaseTrace tool

Once you start tracing a particular database connection, PowerBuilder 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

Stopping Database Trace in the development environment

proc.gif To stop the Database Trace tool by editing a database
profile:

  1. In the Database Profile Setup dialog box
    for the database you are tracing, clear the Generate Trace checkbox
    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. Click Connect in the Database Profiles dialog
    box.

    PowerBuilder connects to the database and stops tracing the
    connection.

Stopping Database Trace in a PowerBuilder application

To stop Database Trace in a PowerBuilder application script,
you must delete the word trace
from the DBMS
property. You can do this by:

  • Editing
    the value of the DBMS property of the Transaction object
  • Reading the DBMS value from an external text file

Editing the DBMS property

proc.gif To stop Database Trace by editing the DBMS value
in a PowerBuilder script:

  1. Delete the word trace
    from
    the DBMS connection property in your application script.

    For example, here is the DBMS connection property in a PowerBuilder script
    that enables the Database Trace. (This syntax assumes you are using the
    default Transaction object SQLCA, but you can also define your own Transaction
    object.)

    Here is how the same DBMS connection property should look
    after you edit it to stop tracing:

Reading the DBMS value from an external text file

As an alternative to editing the DBMS property in your PowerBuilder application
script, you can use the PowerScript profile function to read the DBMS
value from a specified section of an external text file, such as
an application-specific initialization file.

This assumes that the DBMS value read from your initialization
file does not include
the word trace
,
as shown in the preceding example in “Editing the DBMS property.”

Specifying a nondefaultDatabase Trace log

In the PowerBuilder 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 PowerBuilder 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, PowerBuilder writes Database Trace output to a log
file named PBTRACE.LOG located in your Windows directory or $HOME
directory (on UNIX).

You can override this default in the development environment
by editing your PowerBuilder initialization file. The name and location
of the initialization file depends on your platform:

Platform Location
Windows PB.INI in PowerBuilder product directory
UNIX $HOME/.pb.ini

How to do it

proc.gif To specify a nondefault Database Trace log file:

  1. Open the PowerBuilder initialization file for editing.

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

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

    For example:

  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, PowerBuilder 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 Database Trace
    in the development environment”    .

Using the DatabaseTrace log

PowerBuilder 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
on different platforms, see “Location of the Database
Trace log on different platforms”.

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
a PowerBuilder session.

proc.gif To view the contents of the log file:

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

    • Use the File Editor
      in PowerBuilder. (For instructions, see the PowerBuilder
      User’s Guide


      .)
    • Use any text editor outside PowerBuilder.

note.gif Leaving the log file open If you leave the log file open as you work in PowerBuilder,
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 may 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.

proc.gif To annotate the log file:

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

    • Use the File
      Editor in PowerBuilder. (For instructions, see the PowerBuilder
      User’s Guide
      .)
    • Use any text editor outside PowerBuilder.
  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, PowerBuilder 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.

proc.gif To keep the size of the log file manageable:

  1. Do either of the following periodically:

    • Open the log file,
      clear its contents, and save the empty file.
      Provided that you use the default PBTRACE.LOG or the same nondefault
      file the next time you connect to a database with tracing enabled, PowerBuilder will
      write to this empty file.
    • Delete the log file.
      PowerBuilder will automatically create a new log file the next
      time you connect to a database with tracing enabled.

Sample Database Traceoutput

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 a Sybase System 11 database
named Sample. The output was generated while running a PowerBuilder 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.

note.gif Similar format and content on all platforms The basic format and content of the Database Trace log is
the same on all
supported PowerBuilder platforms.

However, 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 may vary from this example.

Connect to database

Prepare SELECT statement

Get column descriptions

Bind memory buffers to columns

Execute SELECT statement

Fetch rows from result set

Commit database changes

Disconnect from database

Shut down database interface

Document get from Powerbuilder help
Thank you for watching.
Powerbuilder Documentation 7.0
PB Docs 70 Powerbuilder Connecting To Your Database Powerbuilder Documentation Powerbuilder Documentation 7.0 Powerbuilder Documentation Older
August 3, 2023
Was this article helpful?
2
1
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x