SQL-Debugger - DBA usage

Sybase's sql-debugger (sqldbgr) is a tool included with the ASE installation and can be used to debug stored procedures and triggers. This might give you the impression that this tool is mainly for developers and not for DBA's. However, the debugger also allows you to attach to an already running process (another spid), examine or change local variables of that other session and even inspect their temporary tables. For production environments the tool provides just that extra information where Monitoring Tables fall short.

Example of SQL code that needs to be debugged

As an example the following code is created into a database. Please note that for readability things like transaction handling, error checking and compilation requirements have been left out. Description of functionality is given below.

create procedure myProc @a int, @b int
as

declare @c int

select @c = c_value
from myTable
where a_value = @a

select d_value
into #work
from myOtherTable
where b_value = @b

execute myOtherProc @c
go

create procedure myOtherProc @c int
as

select *
from BigTable
join #work on (BigTable.d_value = #work.d_value)
where c_column = @c
go

When the myProc procedure is executed it retrieves a value from a table, populates a worktable and executes the stored procedure myOtherProc. The stored procedure myOtherProc selects data from the worktable #work and a very big table. Once this procedure is running it is impossible to determine the content of the temporary table at run-time (or a cumbersome procedure must be followed). Any inspection of local variables is not possible. Now this is where SQL-debugger kicks in.

Starting the debugger

You can start the debugger like this:

$SYBASE/$SYBASE_ASE/bin/sqldbgr -U <username> -P <password> -S <host:portnumber>

The parameters username and password speak for themselves, host:portnumber should be substituted with the hostname (or IP-address) and the portnumber where your ASE server is listening on.

Make sure to put a space between the command switch and the argument, so put a space after -U, -P and the -S. Check that the SYBASE_JRE or JAVA_HOME environment variable has been set and points to a Java runtime environment.

Once you are logged on a prompt will be shown:

(sqldbg)

To exit the debugger, just type 'quit'

Attaching to a session

To connect to another session you must know its spid. Within the debugger you can execute normal sql commands like sp_who, but you must put "mysql" in front of the statement. Remember that you are working with a debugger, so the output of e.g. sp_who is not very nicely formatted and you can only type your command on a single line. No "go" is needed to execute the command within the debugger. Just press 'Enter'.

(sqldbg) mysql sp_who

When you have determined the spid, you can connect to the session with the attach command:

(sqldbg) attach <spid>
(sqldbg)

When you get "You cannot debug a task that is not owned by you" the session is using another username than you. See below how to resolve this. With the detach command you can detach from an attached session.

(sqldbg) detach <spid>

Retrieving the call stack

When you have been attached successfully to the other spid you can see the call-stack from that session with the "where" command

(sqldbg) where
(dbo.myOtherProc::5::@c = 2)
(dbo.myProc::17::@a = 1,@b = 3)
(ADHOC::1::null)
(sqldbg)

From this we can learn quite a lot:

• At the first line (dbo.myOtherProc::5::@c = 2) it is shown that the procedure dbo.myOtherProc is currently running, it is at line 5 within that stored procedure and a value of 2 has been passed into the @c parameter. Since this is the first line from the "where" output, the level is numbered as 0.
• At the second line (dbo.myProc::17::@a = 1,@b = 3) the procedure dbo.myProc is shown. This means that myProc called myOtherProc at line 17. Two variables were passed to the myProc procedure. Now we are at level 1.
• Finally, (ADHOC::1::null) tells us that the myProc procedure was called from a command line tool (like isql / SQL-Advantage), at line 1 of the batch. This is level 2.

Viewing local variables

Local variables can be viewed with the "show variables" command.

(sqldbg) show variables
(sqldbg)

In this example no output is shown. This is because "show variables" works default at level 0 and a distinction is made between variables declared within the stored procedure, and parameters that were declared in the "create procedure" statement. Indeed, no local variables were declared within the myOtherProc stored procedure and therefore "show variables" shows nothing.

(sqldbg) show variables at level 1
int @c 2
(sqldbg)

With "show variables at level 1" we can actually see the declared local variables within the myProc procedure, their datatype and value. In this case @c was declared as an int and a value of 2 has been put into it.

Note: Global variables cannot be retrieved for an attached session. For instance, when the running procedure has changed the transaction isolation level this will be reflected in the @@isolation global variable. This change will not be seen within the debugger that has been attached to that particular session.

Viewing temporary tables

To view temporary tables use the "sql" command (not the mysql command), like this:

(sqldbg) sql select * from #work
d_value
4
(sqldbg)

Now we know that the attached session created a temporary table with just one row. The column d_value has a value of 4. To select data from a temporary table you must know the name of the table as it is created within the stored procedure. In practice this means that you should have access to the source code of the stored procedure. Alternatively you can run a select on sysobjects in the temporary database and query the name column as in this example:

select name from tempdb..sysobjects

The output will show the first few characters of the name of the table, followed by the spid.

There is a small problem when the attached session is using a temporary database and that database is not accessible by you. Such a situation can occur when the login/application of the attached session is bound to a specific tempdb. In that case you need to impersonate the other login with the 'setuser' command.

Modifying local variables or temporary tables

Local variables (as viewed with the "show variables" command) can be modified with the "set" command. This is only possible for variables at level 0. Example:

(sqldbg) set @z = 2

Temporary tables can be modified with the "sql" command. Example:

(sqldbg) sql delete from #work where d_value = 3

Although you are attached to a session this does not mean that you have taken over that session. Any locks set by the session will also affect the debugger. When a temporary table is locked exclusively it cannot be modified through the debugger. Selection of data is always possible, so these locks can indeed by bypassed.

Resolving "You cannot debug a task that is not owned by you"

When you try to attach to a session and the error "You cannot debug a task that is not owned by you" is raised, the session is running with another username than yourself. By default you can only attach to a session running with the same username as yourself. Using the T-sql command "set session authorization" you can impersonate another user and then use the attach command. Before you can execute "set session authorization" you should have been granted privilege to it, even when you have already sa_role or sso_role. To get this privilege, a dba should add you to the master database as a user and then execute "grant set proxy to <your-username>" (preferably with the ‘restricted’ option). Improper usage of "grant set proxy to" can introduce security issues so please read and understand the Sybase documentation with regard to this before using it. When all requirements have been met you can execute "set session authorization" within the debugger. Example:

(sqldbg) attach 18
You cannot debug a task that is not owned by you
(sqldbg) mysql set session authorization 'joe'
(sqldbg) attach 18
(sqldbg)

Further reading

Sybase documentation for sqldbgr is here http://infocenter.sybase.com/help/topic/com.sybase.help.ase_15.0.utility/html/utility/utility216.htm

Sybase documentation for "grant" statement is here http://infocenter.sybase.com/help/topic/com.sybase.help.ase_15.0.commands/html/commands/commands59.htm