Enabling DBTrace in PB

Enabling DB Trace in PB

Powerbuilder has a nice feature to enable trace on a database connection. In a typical PB application this can be enabled by adding a "TRA" (for TRACE) before the DBMS name while setting connection parameters before making connection to the database.

SQLCA.DBMS = "TRACE DBMS_name"
 SQLCA.ServerName="<Oracle Server>"
 SQLCA.LogPass="<Oracle ID>"
 SQLCA.LogPass=<Oracle Password>"

Trace can be abbreviated as TRA. In this example, the prefixing is done in Powerscript. SQLCA is the transaction object that is used in Powerscript to connect to database. In a typical application, however, these settings are read from an ini file or the Windows Registry. So depending on the design, you may have to add TRA to an .ini file or the Registry setting for the Application.

To disable the Trace, just remove TRA and restart application.

Enabling DB Trace in EA Server

I currently work with an n-tier PB application deployed in EA Server. The database connection happens only on the Server side. (It's still in PB code, but the PB code runs inside EA Server). So, it is slightly different from the traditional 2-tier PB application.

Typically in any EA Server application, connections to the database are made through Connection Caches (DB Pool). So, we can set the DB trace while connecting to the Connection Cache.

In our application, we have a package called its_systemservices that has some global service objects, including a Properties Manager component. We use this component to store and get all the system level settings, including the settings required by the connection Cache(s). The actual settings are stored as properties of the its_systemservices package. One of these settings is the DBMS property which is set to O10 (for Oracle 10g driver). By prefixing this with TRA(ce), we can enable the DB Trace.

In EA Server, every entity has a set of properties which are stored in a properties file for the entity. We can edit these properties in Jaguar Manager.



The above change gets saved in the properties file for the package (its_systemservices.props). Alternatively, you can also edit the file directly. But, be careful not to change any other settings.



To disable, just remove TRA and restart EA Server.

DB Trace File

By default, the trace output will be stored in a file called, dbtrace.log in C:\Windows. It has lots of valuable information including the SQLs that were executed. To capture all the SQLs from a PB screen in our application, I run EA Server in local mode and thus I will be able to capture SQLs from all the Server components.

PB appends to this file for every DB call and thus file becomes fairly large quickly. Do not forget to turn off this setting as soon as you got the trace needed, as the trace files are typically large. The file can be deleted at any time (provided it's not locked by the app) and PB will create a new file (If TRA option is still set).

DB Trace file name, location and what is being captured can be controlled. Previously, you can control the trace file name and location by setting it in PB.ini. With newer versions of PB (since PB 10.2), you can edit the registry to do this. For e.g., in PB 10.5, the following registry entry controls DB Trace.

HKEY_CURRENT_USER\Software\Sybase\PowerBuilder\10.5\DBTrace

Apart from being able to change file name and location, you can also turn tracing on/off dynamically using these settings. We can also control how much trace information is captured by setting appropriate trace settings under DBTrace in the registry.

Veteran PB developer, Bruce Armstrong, has an article on this nice feature here.

Note: This setting is only for capturing SQLs coming from PB objects (Datawindows, Embedded SQL etc) Obviously, SQLs in Database (such as those in Stored procedures etc) will not be in this trace file, but the calls to Stored procedures will be.