One of the "new" features in PowerBuilder 10.5 was actually initially introduced in the PowerBuilder 10.2 maintenance release. The folks at Sybase finally addressed a number of fundamental issues with database tracing for deployed applications.

Trace File Location
Users are often running under accounts that don't have Administrative or Power User privileges. As a result, they may not have sufficient rights to create a file in the WINDOWS directory, the very directory where a deployed application was hard coded to store the trace file. We've been able to specify an alternative location for applications run out of the IDE since PowerBuilder 6.0 by setting the DBTraceFile parameter in the database section of the PB.INI file. It wasn't until these new database tracing features were added that we gained this ability for deployed applications.

Database Trace Prompt
Until these new features were introduced, PowerBuilder would display a message box indicating the location of the database trace file (see Figure 1). With the new database tracing features, the message box has been replaced with a dialog that allows the end user to modify many characteristics of the database trace (see Figure 2). More important, the developer now has the option to suppress the dialog completely (see Controlling Tracing below). The dialog may have been, at worst, annoying for a deployed application that has a user operating it. However, the dialog actually prevented database trace capability for deployed components or applications that were installed as services. Worse, since there wasn't a user to respond to the database trace message box, the application would hang when a database trace was attempted.

Level of Trace Detail
The amount of information that PowerBuilder collects in a database trace is extensive and often overwhelming. For example, if we are simply trying to gather timing information in order to determine which statements are slowing down our application, we simply don't have a need to see each record as it's retrieved from the database. Until this new functionality was added, we got it whether we wanted it or not. Now we have much greater control over what is actually recorded to the database trace file, so we can focus on just the information we actually need to see.

Dynamic Tracing
Prior to the introduction of these enhancements, we needed to enable tracing before connecting to the database and then record every database interaction the application was involved with up through the area we were actually interested in, and then on further until the application disconnected from the database. Depending on how much we needed to do before we reached the area we were interested in, we might record a lot more tracing data than we actually needed. With these enhancements, we can turn on database tracing dynamically, so we can focus on just those interactions we are actually interested in. In addition, the enhancements include a separate ability to do a statement-level trace.

Controlling Tracing
Unlike the DBTraceLog setting for the IDE, the control over the new database tracing functions is handled through the registry under the "HKEY_CURRENT_USER\Software\Sybase\PowerBuilder\10.5\DBTrace" key (see Figure 3). The LogFileName registry entry takes a string value that points to the location of the trace file. The SQLTraceFile takes a string value that points to the location of a trace file for recording statement-level tracing. The remaining entries take string values of either "1" or "0" to indicate that the particular feature is enabled or disabled.

The ShowDialog option determines whether the application attempts to show the tracing dialog (see Figure 2) to the user. If it is shown to the user, the option values shown in the dialog will be based on the current values of the registry entries. If the user changes those options, the values he or she selected are recorded back out to the system registry.

To compare the functionality provided by these various options, consider, first, a somewhat typical trace file from PowerBuilder prior to these modifications (see Listing 1). You see the statement being prepared, the binding performed, the statement executed, and then the fetching of every record along with the data from every record. Finally, the previous version gives you the execution time for each individual action.

Compare that with the trace output when only DBI Names is selected (see Listing 2). Now you see the type of function being performed (PREPARE, BIND, EXECUTE, FETCH) and the SQL statement. In addition, the only other thing you see is the database interface functions being called. This is actually a new piece of information that was unavailable with the older tracing capability. What you don't see, however, is all of the binding information, the data being returned, or timing information.

The results from selecting just the Timing option (see Listing 3) or the Summary Timing option (see Listing 4) are not significantly different. Instead of the database interface functions, you see the timing for each operation or (another new capability) the total time since tracing was enabled. Once again, you've significantly reduced a great deal of information that you may not be interested in.

If you are only interested in seeing how PowerBuilder is binding to the dataset being returned (see Listing 5), you can turn on just the Bindings option. Alternatively, if you want to see the data as it's being returned (see Listing 6), you can enable the Data Buffers option.

Of course, these detail level options are not mutually exclusive; they can be used in combination to record just the specific information you need to see. If you enable them all, you essentially get the same effect as the previous database tracing functionality (with the addition of DBI Names and Summary Timing info). However, all of those options control only the level of detail for the standard tracing option. The statement-level tracing option records a fixed amount of information (see Listing 7) regardless of how those options are set.

Implementing Tracing
To do a standard trace, prefix the transaction object DBMS attribute with "TRA," just as we always have. However, there is a new PBTrace DBParm parameter that determines whether the tracing begins when the connection is made or if it's delayed until later. If the PBTrace parameter is set to 0 on the initial connection, tracing isn't performed until that parameter is changed to 1. It can then be set back to 0 to turn tracing off. What is a bit unusual, though, is that even if the parameter is set to 0 on the initial connection, if the ShowDialog option is set to 1, the database tracing dialog (see Figure 2) will be shown to the user when the initial connection is made. I would have expected the dialog to have been delayed until database tracing was actually initiated.

To do a statement trace, prefix the transaction object DBMS attribute with "TRS." The same PBTrace DBParm parameter can be used to dynamically determine when tracing begins and ends.

Conclusion
The latest enhancements to the database tracing functionality address some long-standing concerns. They allow us to control the detail and scope of the tracing, and finally make it possible for us to perform tracing on components or services that cannot interact with the user.

--This article was originally published on PBDJ.