How to trace user activity to a file in SAP Sybase ASE

Today, I’ve been checking if there are any videos on YouTube on Sybase ASE’s dbcc traceon usage and I’ve found this interesting video by Lester Leonares about a nice functionality to log user activity to a file:


(This video is a part of the SAP D&T Academy, definitely worth checking out.)

Googling further on the “set tracefile”, I’ve found a White Paper by Sybase entitled “Application Tracing in Adaptive Server Enterprise (ASE) 15” (opens pdf file in new window).

Below are some notes from the video and the white paper.

To enable tracing:

set tracefile "<file-path>" [for <spid>]

To disable tracing:

set tracefile off [for <spid>]

The “set” commands in ASE whose output can be traced under application tracing context are:

set show_sqltext <on/off>
set showplan <on/off>
set statistics io <on/off>
set statistics time <on/off>
set statistics plancost <on/off>
-- and all ‘set option’ subcommands

Obviously, as you could imagine, there are some documents in Sybase Infocenter about Finding Slow Running Queries where you can read about tracefile and see some examples. This link is an excerpt from a Sybase book on Performance and Tuning Series: Query Processing and Abstract Plans (opens a 3.9MB pdf file in new window).

Sybase : How to remove leading zeros from a column?

This is how I do it:

UPDATE <tablename>
SET <column> = SUBSTRING(<column>, PATINDEX('%[^0]%', <column>), CHAR_LENGTH(<column>))
WHERE <column> LIKE '0%'

Obviously, the most interesting part for me is the:

'%[^0]%'

construct that finds the position of the first non-ZERO character in the <column> to start substring from.

For detailed info about used functions, please visit Sybase’s documentation:

patindex

char_length