Unanswered: Logging all SQL statements (optimization)
We are running Sybase Adaptive Server 126.96.36.199.
We use it rather heavily and sometimes it is a bottleneck. Therefore, we want to optimize it. The configuration of Sybase itself seems ok. In fact, it is amazing what it can do. The SQL statements passed by our many Java clients to the Sybase server might not be very effficient, though.
So, what we want to do is log the SQL statements passed to the Sybase server along with the execution times (start and end times).
How is this done?
We hava a lot of clients accessing the Sybase server. Augmenting these clients with logging capabilities is a difficult task. Also, it seems naturnal to add the logging centrally at the Sybase server.
Any help would be appreciated - and, I think, of general use to those wanting to optimize their use of Sybase.
Check out the auditing feature available. BUt if you are looking for per tuning client code, then auditing might be a bit too overwhelming. In addition the java clients connecting through JDBC mostly send stored proc to the server into the tempdb.
The keyword "auditing" helped me find the info I needed.
For other readers, Sybase server contains an entire Audit System (assuming you have installed it...) that allows you to audit (monitor) pretty much all activity in the server. Nice. For more information, see Appendix C of "Sybase SQL Server 11 Unleashed" by Ray Pankins etc.
Set "enable cis" to 1 then enable traceflag 11202. This will cause all commands for all sessions to be written to the errorlog. Neat, but watch your disk space as your errorlog can seriously grow if lots of clients doing loads of stuff...