Sybase Interview Question:

How do I capture a process s SQL in Sybase?

Submitted by: Administrator

This is a bit of a wide question, and there are many answers to it. Primarily, it depends on why you are trying to capture it. If you are trying to debug a troublesome stored procedure that is behaving differently in production to how it did in testing, then you might look at the DBCC method. Alternatively, if you wanted to do some longer term profiling, then auditing or one of the third party tools might be the way forward. If you know of methods that are not included here, please let me know.

DBCCs
If you want to look at the SQL a particular process is running at the moment, one of the following should work. Not sure which versions of ASE these work with. Remember to issue dbcc traceon(3604) before running any of the dbcc's so that you can see the output at your terminal.
* dbcc sqltext(spid)
* dbcc pss(0, spid, 0)

The first of the commands issues the SQL of the spid only a bit like this:
[27] BISCAY.master.1> dbcc sqltext(9)
[27] BISCAY.master.2> go
SQL Text: select spid, status, suser_name(suid), hostname,
db_name(dbid), cmd, cpu, physical_io, memusage,
convert(char(5),blocked) from master..sysprocesses
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
[28] BISCAY.master.1>
Submitted by: Administrator