Sybase EAServer: Taking Control – Enabling DB Trace
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 package (its_systemservices) to store and get all the system level settings, including the settings required by the connection Cache(s). 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.
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 Trace, just remove TRA and restart EA Server.
Note: We use package/component to store the systemwide properties in our EAServer application. Your application may be using an INI or even a properties file. Find out where the DBMS settings are change the value and prefix with TRA(ce) to turn on the DB Trace.
Also note that, this TRA setting is only for capturing SQLs coming from PB code. Obviously, SQLs run in Database (such as those in Stored procedures etc) will not be in this trace file, but the calls to Stored procedures will be. And if you are running n-tier app in EAServer like we do, then your DB connections will only be in the Server code. In this case, you will have to run the application server locally (localhost) to be able to capture all SQLs.
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). Here is sample output:
/* 05/30/2012 11:48 */
(75b226c): LOGIN: (3.529 MS / 3.529 MS)
(75b226c): CONNECT TO TRA O10:
(75b226c): DBPARM=UseContextObject=’Yes’,Disablebind=1,CacheName=’DEV2-ITS’ (0.000 MS / 3.529 MS)
(75b226c): PREPARE: (0.000 MS / 3.529 MS)
(75b226c): OPEN CURSOR: (0.001 MS / 3.530 MS)
(73e9ba8): DUMMY CURSOR CONNECTION :
(73e9ba8): DBPARM=UseContextObject=’Yes’,Disablebind=1,CacheName=’DEV2-ITS’ (0.000 MS / 0.000 MS)
(73e9ba8): SELECT PARM_VALUE FROM SYSPARM WHERE PARM_NAME = ‘BATCH_LOG_LOCATION’ (0.150 MS / 0.150 MS)
(73e9ba8): GET AFFECTED ROWS: (0.005 MS / 0.155 MS)
(73e9ba8): ^ 0 Rows Affected
(73e9ba8): GET NUMBER OF COLUMNS SELECTED: (0.916 MS / 1.071 MS)
(73e9ba8): ^ 1 Columns
(73e9ba8): DESCRIBE SELECT: (0.001 MS / 1.072 MS)
(73e9ba8): 1 Bind Columns
(73e9ba8): CHAR Length=302
(73e9ba8): EXECUTE: (0.000 MS / 1.072 MS)
(73e9ba8): BIND SELECT OUTPUT BUFFER (PowerScript): (0.010 MS / 1.082 MS)
(73e9ba8): 1 Bind Columns
(73e9ba8): CHAR Length=302
(73e9ba8): FETCH NEXT: (0.017 MS / 1.099 MS)
(73e9ba8): GET AFFECTED ROWS: (0.002 MS / 1.101 MS)
(73e9ba8): ^ 1 Rows Affected
/* 05/30/2012 11:50 */
Changing the parameters for DBTrace
DB Trace file name typically gets saved in C:\Windows. The location of the file 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 (>PB 10.2), you can edit the registry to do this. For e.g., in PB 12.5, the following registry entry controls DB Trace.
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. Here is a full list of settings in the Registry:
Veteran PB developer, Bruce Armstrong, has an article on this nice feature here.
I’ve also developed a small utility to extract SQLs from the trace file thus created. See here for more details.