Power to Build

Home » Programming » Powerbuilder » PowerBuilder DB Trace – SQL Extraction Utility

PowerBuilder DB Trace – SQL Extraction Utility

If you ever have to debug database scripts in PowerBuilder, the DB Trace option comes in handy. As I mentioned in my earlier post on Enabling DB Trace, you just need to prefix the DBMS name with Trace. For e.g., if your application works with Oracle, you will have say,

SQLCA.DBMS=Trace ORA

A typical DB Trace file gets created in C:\Windows and contains a lot of noise. Some of these can be changed, but I was trying to get all the SQLs while the user was doing data entry – sort of reverse engineering the SQLs. The file grew really large and was not easy to extract SQLs manually.

Here is an excerpt with 2 SQLs from the original dbtrace.log:

/*—————————————————*/
/*                 10/08/2010  16:17                 */
/*—————————————————*/
(ad2ca34): LOGIN: (90.315 MS / 90.315 MS)
(ad2ca34): CONNECT TO TRA O10:
(ad2ca34): LOGID=latax
(ad2ca34): DBPARM=UseContextObject=’Yes’,Disablebind=1,CacheName=’DEV2-ITS’ (0.000 MS / 90.315 MS)
(ad2ca34): PREPARE: (0.000 MS / 90.315 MS)
(ad2ca34): PREPARE:
(ad2ca34): SELECT COUNT ( *) FROM SYSPARM WHERE PARM_NAME =’ENFORCE_CLIENT_VERSION_MATCH’  (0.123 MS / 90.438 MS)
(ad2ca34): GET AFFECTED ROWS: (0.003 MS / 90.441 MS)
(ad2ca34): ^ 0 Rows Affected
(ad2ca34): GET NUMBER OF COLUMNS SELECTED: (7.282 MS / 97.723 MS)
(ad2ca34): ^ 1 Columns
(ad2ca34): DESCRIBE SELECT: (0.000 MS / 97.723 MS)
(ad2ca34): 1 Bind Columns
(ad2ca34): DECIMAL Length=80
(ad2ca34): EXECUTE: (0.000 MS / 97.723 MS)
(ad2ca34): BIND SELECT OUTPUT BUFFER (PowerScript): (0.014 MS / 97.737 MS)
(ad2ca34): 1 Bind Columns
(ad2ca34): INT Length=80
(ad2ca34): FETCH NEXT: (1.186 MS / 98.923 MS)
(ad2ca34): GET AFFECTED ROWS: (0.002 MS / 98.925 MS)
(ad2ca34): ^ 1 Rows Affected
(ad2ca34): FETCH NEXT: (0.024 MS / 98.949 MS)
(ad2ca34): *** DBI_FETCHEND *** (rc 100)
(ad2ca34): CANCEL: (0.001 MS / 98.950 MS)
(ad2ca34): PREPARE:
(ad2ca34): SELECT PARM_VALUE FROM SYSPARM WHERE PARM_NAME =’ENFORCE_CLIENT_VERSION_MATCH’  (0.109 MS / 99.059 MS)
(ad2ca34): GET AFFECTED ROWS: (0.002 MS / 99.061 MS)
(ad2ca34): ^ 1 Rows Affected
(ad2ca34): GET NUMBER OF COLUMNS SELECTED: (2.139 MS / 101.200 MS)
(ad2ca34): ^ 1 Columns
(ad2ca34): DESCRIBE SELECT: (0.000 MS / 101.200 MS)
(ad2ca34): 1 Bind Columns
(ad2ca34): CHAR Length=302
(ad2ca34): EXECUTE: (0.000 MS / 101.200 MS)
(ad2ca34): BIND SELECT OUTPUT BUFFER (PowerScript): (0.020 MS / 101.220 MS)
(ad2ca34): 1 Bind Columns
(ad2ca34): CHAR Length=302
(ad2ca34): FETCH NEXT: (1.218 MS / 102.438 MS)
(ad2ca34): GET AFFECTED ROWS: (0.002 MS / 102.440 MS)
(ad2ca34): ^ 1 Rows Affected
(ad2ca34): FETCH NEXT: (0.039 MS / 102.479 MS)
(ad2ca34): *** DBI_FETCHEND *** (rc 100)
(ad2ca34): CANCEL: (0.000 MS / 102.479 MS)
(ad2ca34): DISCONNECT: (0.031 MS / 102.510 MS)
(ad2ca34): SHUTDOWN DATABASE INTERFACE: (0.000 MS / 102.510 MS)
/*—————————————————*/
/*                 10/08/2010  16:17                 */
/*—————————————————*/

In comes PERL to the rescue. Attached script in pastebin is the one I wrote to do the extract.
I wrote this script in 2010. When I ran the script against the dbtrace, it extracts the SQLs from it, as shown below.
–dbtrace.1008.log
— 10/08/2010  16:17
SELECT COUNT ( *)
FROM SYSPARM
WHERE PARM_NAME =’ENFORCE_CLIENT_VERSION_MATCH’  ;
SELECT PARM_VALUE
FROM SYSPARM
WHERE PARM_NAME =’ENFORCE_CLIENT_VERSION_MATCH’  ;
To run it, you simply pass in the trace file and reroute the output to a file.
pb_sqls.pl -f dbtrace.1008.log > dbtrace.1008.sql
Years later

Script:

Perl Version:

http://pastebin.com/FZW1TRcp

Python Version:

http://pastebin.com/bHf2NfdV


1 Comment

  1. […] needle in a haystack. That’s why I created a script to extract out only SQLs. See my post here. You can find my Python Script to do this in Pastebin. The output can be redirected to a file with […]

Comments, please?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: