how to log/trace sql statements

[Former Member]

Hi,

I have an application that performs quite some sql statements to a client sql anywhere database and sometimes it's hard to figure out what exactly is happening and would like to do some tracing on DB level.

I've tried a few methods to log/trace sql statements but only with moderate or no success.

I've tried the following things.

1. Trace (Sybase central) , the newly created tracing-database seems to be empty ... or I just don't know where to look

2. Application profiling. for some reason It shows a few sql statements but not the ones generated by my appliation, and even when I go to interactive sql and type a sql statement it does not appear in application profiling. What am I doing wrong?

3. startup the server with "-zr sql" and save to a logfile. This has moderate success... I see sql statements and a whole lot more, sql statements look weird, with lots of parameters and hard to decode.

I would have like to use application profiling since it looks really nice, but it starts up and doesnt seem to update. I can see my connections though (in application profiling), and when I perform some sql statements in my application I see that the amount of memory used by that connection/application changes.

PS: Other things I've tried. Using "The monitor": gives nothing away of sql commands using third party profilers in my application, but unfortunately these fail as well.

some help would be appreciated.

[VolkerBarth]

What API are you using? For example, on Windows ODBC applications can be traced via the builtin ODBC tracing facility.

[Former Member]

It's a .net application that uses a dll: iAnywhere.Data.SQLAnywhere

So, it's no odbc I'm afraid.

[VolkerBarth]

I do rarely use the tracing facility but Breck Carter has given a very detailed instruction list here:

Database Tracing In 50 Easy Steps

[Former Member]

I was looking in to that, thanks, but unfortunately, new queries just don't appear for some reason in the application profiling details...I can see my connections though (whenever I perform something in my application the Connection ID changes, so I know that Application profiling is aware of my app)... really weird, I'm probably missing something...

I've been carefull not to check 'only trace queries that take longer than 1 second'

[MCMartin]

Use the two SQL commands to enable logging:

call sa_server_option  ('Request_level_log_file', 'c:\\\\temp\\\\sql.log');
call sa_server_option  ('Request_level_logging', 'SQL+hostvars')

you should find the sql commands in the created log file
Disable the logging again with:

call sa_server_option  ('Request_level_logging', 'NONE')