Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved DB trace to 2 files

  1. How-to
0
Votes
Undo
  1. Arcady Abramov
    Arcady Abramov
  2. PowerBuilder
  3. Wednesday, 27 April 2022 10:50 AM UTC

Hello, all

I have a very big application, which connects to multiple DBs via ODBC.

I would like to use DBTrace in order to see which selects takes too much time or being repeated more than it needs to.

My question is - how can I set up tracing to several different files based on transaction objects?

Is it at all possible?

Thank you

  1. .EXE
  2. Logging
Acceleration of Editmask spin control
Problem passing XML to Web API using HttpClient
Responses (4)
Andreas Mykonios
Andreas Mykonios Accepted Answer Pending Moderation
  1. Wednesday, 27 April 2022 10:57 AM UTC
  2. PowerBuilder
  3. # 1

Hi.

I don't know what database you are using, but I wonder if it provides some monitoring tools to do that. This has more sense as you can see that information from server side. Some database do have a profiler (example: sql anywhere does have one). With profiler you don't see only which statement gets to long but you may also get hints about missing indexes e.t.c.

Andreas.

Comment
Arcady Abramov
  1. Arcady Abramov
  2. Wednesday, 27 April 2022 11:05 AM UTC
Thanks, but I need to know what specific users in specific applications are doing that takes time.

So, it has to be client-side trace. Multiple applications and services connect to DB, the DB itself is not the problem.
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 27 April 2022 15:57 PM UTC
  2. PowerBuilder
  3. # 2

Hi Arcady;

  Unfortunately,  the SQLCA.DBMS = "TRACE XXX" command outputs to only one SQL trace file no matter how many Transaction Objects you have this feature enabled on.

  However, ODBC (via each of its DSN) has it's own SQL tracing feature. So you could trace multiple DBMS interactions simultaneously via the ODBC trace feature.  HTH

Regards ... Chris

 

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Wednesday, 27 April 2022 16:47 PM UTC
Hi Chris,

Not my experience. If I have 2 transaction objects, I'm being prompt twice! So the second prompt allows me to save to a different log file.

regards.
  1. Helpful 1
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Wednesday, 27 April 2022 16:54 PM UTC
Yes, as long as you catch the prompt for the log file, you can redirect it's location & name manually each time.
  1. Helpful 2
There are no comments made yet.
Brad Mettee
Brad Mettee Accepted Answer Pending Moderation
  1. Wednesday, 27 April 2022 16:48 PM UTC
  2. PowerBuilder
  3. # 3

I've recently been debugging a very slow (2-3minutes) retrieval and started doing some tracing of my own. 3 DB transaction objects, TRACE XXX on all 3 of them, also using SQL tracing features too.

Each Transaction object can have a different file assigned, and when using TRACE in the DBMS, it should prompt for the filename for each connection (it does for me), so you can have each trace end up in a different file.

Here's how I connect to the DB:

if ib_dbtrace then
	sqlca.DbParm += "ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT;SQL_OPT_TRACE,SQL_OPT_TRACE_ON;SQL_OPT_TRACEFILE,c:\temp\odbctrace_sqlca.log'"
	sqlca.dbms="TRACE " + GetSettingString(gs_IniFile, "Database", "DBMS", "ODBC")
else
	sqlca.DbParm += "ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'"
	sqlca.dbms=GetSettingString(gs_IniFile, "Database", "DBMS", "ODBC")
end if

 

Further on in the code we replace _sqlca with the appropriate transaction name in the DBParm to differentiate the trace files.

trans_main.DbParm = srchrepl(sqlca.dbparm, "_sqlca", "_trans_main")

 

From this, I've been able to see what's going on through each part of the system, and can compare retrieval times quite easily. (though it still hasn't helped me solve the speed issue yet)

Comment
There are no comments made yet.
Tobias Roth
Tobias Roth Accepted Answer Pending Moderation
  1. Friday, 29 April 2022 04:34 AM UTC
  2. PowerBuilder
  3. # 4

Hi Arcady,

Maybe the PB tracing and profiling will help you.
John Strano explained it very well in this video:

Catching the Runtime Error in the Act - Tracing and Profiling Your Application at Runtime

There are different views in which you can even display the execution times of individual lines of code (e.g. retrieve or embedded SQL lines).

I hope that helps you.

Regards Tobi

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.