Tricks with 'sqsh'

What is 'sqsh'?
sqsh is -if you didn't know already- a superior replacement for Sybase's default ASCII query tool isql (sqsh has been described as 'isql the way it should have been').

Among the many virtues of sqsh are:

interactive redirection of query results to an OS file
interactive searching through query results
using the semicolon as a batch-terminator instead of 'go'
defining your own sqsh commands, which can include things like programmable loops and expand to customised SQL
the dynamic-reconnect trick described below
and much, much more...

Even better, sqsh is free. It's an open-source product that was originally designed and developed by Scott Gray (Scott rightly received the ISUG award for his work a few years ago). In 2004, support has recently been taken over by Michael Peppler (another ISUG award winner). sqsh can be downloaded from www.sqsh.org, though you'll find only older versions of sqsh here: the project has been moved to Sourceforge now.

A productivity-boosting 'sqsh' trick: dynamically reconnecting your session
If you're managing multiple ASE servers and/or replication servers, you'll probably often find yourself working with multiple open sqsh (or isql) sessions at the same time, using one session to connect to each server. Especially for RepServer DBAs, all those windows can be a bit too much at times. Personally, I have long wanted a way to dynamically reconnect my sqsh session to a different server, so that you can use only a single session without explicitly logging out and in again.
The good news is that sqsh provides the '\reconnect' command to do just that -- the bad news is that sqsh crashed when using '\reconnect'. Fortunately though, this bug has now been fixed (more information here), meaning that you can dynamically reconnect your sqsh session to another ASE server or RepServer, without exiting sqsh first!.

In the following example, the initial connection is to ASE server 'X1252'. Using the sqsh '\reconnect' command, sqsh then reconnects to server 'X12503' (the original connection to X1252 is automatically closed):

$ sqsh -SX1252 -Usa -Pmysecret
sqsh-2.1.2[one-off 26-Nov-2004]
 (DEBUG) Copyright (C) 1995-2001 Scott C. Gray
Portions Copyright (C) 2004 Michael Peppler
This is free software with ABSOLUTELY NO WARRANTY
For more information type '\warranty'
[101] X1252.sa.master.1> select @@servername;
 ------------------------------
 X1252

(1 row affected)
[102] X1252.sa.master.1> \alias X12503='\reconnect -SX12503 -Usa 
-Pmysecret'
[102] X1252.sa.master.1> X12503
[102] X12503.sa.master.1> select @@servername;
 ------------------------------
 X12503

(1 row affected)

In this example, instead of manually typing the full '\reconnect' command, I've defined an alias named 'X12503' for the entire '\reconnect' command. Typically, you'd add such '\alias' commands to your .sqshrc file so that the aliases are always defined when you start sqsh. Of course, there's a security aspect to putting passwords in the .sqshrc file, so make sure it isn't readable for unauthorised users.
While we're at it, you may also find it useful to define aliases for some of those T-SQL commands you're using frequently. This is a section of my own customised .sqshrc file:

# aliases for T-SQL commands
\alias statson='\loop -e "set statistics io on set statistics time on"'
\alias statsoff='\loop -e "set statistics io off set statistics time 
off"'
\alias planon='\loop -e "set showplan on"'
\alias planoff='\loop -e "set showplan off"'
\alias ton='\loop -e "dbcc traceon(3604)"'
\alias toff='\loop -e "dbcc traceoff(3604)"'
\alias tmp='\loop -e "use tempdb"'
\alias mas='\loop -e "use master"'
\alias ssp='\loop -e "use sybsystemprocs"'

# aliases for sqsh commands
\alias h='\history'
\alias xx='\reset'
\alias X1103='\reconnect -SX1103 -Usa -Pmysecret'
\alias X120='\reconnect -SX120 -Usa -Pmysecret'
\alias X1252='\reconnect -SX12503 -Usa -Pmysecret'
\alias X1251='\reconnect -SX1251 -Usa -Pmysecret'
\alias X1252='\reconnect -SX1252 -Usa -Pmysecret'
\alias X150='\reconnect -SX150 -Usa -Pmysecret'
\alias REPX126='\reconnect -c -SREPX126 -Usa -Pmysecret'
\alias REPX125='\reconnect -c -SREPX125 -Usa -Pmysecret'

Reconnecting to RepServer
You can also use '\reconnect' to connect to a RepServer. Note that you may initially get an error message (which is harmless and can be ignored):

[103] X150.sa.master.1> REPX126
No SRV_OPTION handler installed.
Open Client Message
Layer 1, Origin 1, Severity 1, Number 183
ct_options(): user api layer: external error: An error was returned 
from the server while setting the options, check the server message 
for details.
[103] REPX126.sa.master.1> admin who;
 Spid Name       State                Info
 ---- ---------- -------------------- ------------------------------
   32 DSI EXEC   Awaiting Command     101(1) X1251.RSSD_REPX126
   16 DSI        Awaiting Message     101 X1251.RSSD_REPX126
   23 DIST       Awaiting Wakeup      101 X1251.RSSD_REPX126
   28 SQT        Awaiting Wakeup      101:1  DIST X1251.RSSD_REPX126
    9 SQM        Awaiting Message     101:1 X1251.RSSD_REPX126
    8 SQM        Awaiting Message     101:0 X1251.RSSD_REPX126
   43 REP AGENT  Awaiting Command     X1251.RSSD_REPX126
   35 DSI EXEC   Awaiting Command     102(1) X1251.primdb
   17 DSI        Awaiting Message     102 X1251.primdb
   24 DIST       Awaiting Wakeup      102 X1251.primdb
   29 SQT        Awaiting Wakeup      102:1  DIST X1251.primdb
   11 SQM        Awaiting Message     102:1 X1251.primdb
   10 SQM        Awaiting Message     102:0 X1251.primdb
   31 REP AGENT  Awaiting Command     X1251.primdb
   34 DSI EXEC   Awaiting Command     103(1) X1251.repdb
   18 DSI        Awaiting Message     103 X1251.repdb
   12 SQM        Awaiting Message     103:0 X1251.repdb
   22 dSUB       Sleeping
    6 dCM        Awaiting Message
    7 dAIO       Awaiting Message
   26 dREC       Sleeping             dREC
   27 dSTATS     Sleeping
   44 USER       Active               sa
    5 dALARM     Awaiting Wakeup
[104] REPX126.sa.master.1>

Also note that an additional error message wil occur if the '-c' option of the '\reconnect' command is not used when connecting to a RepServer ('-c' suppresses doing a T-SQL 'use <databasename>' command after it has reconnected; RepServer cannot handle the 'use' command).
Note that the database name 'master' in the sqsh prompt is not relevant for a RepServer session, but is simply carried over from a previous connection to an ASE server.

Downloading and building 'sqsh'
To obtain a version of sqsh with the '\reconnect' fix, a binary version for Windows/CygWin can be downloaded here (215Kb; note that this binary has a version string of 'sqsh-2.1.2[one-off 26-Nov-2004]').

For other platforms, you'll have to build it yourself. Go to Sourceforge, and download the latest version of the sqsh sources (currently -December 2004- the latest version is 2.1.3 -- earlier versions like 2.1.2 do not include the '\reconnect' fix -- except for the one-off version mentioned in the above paragraph).

Note that binary versions for various platforms may also be downloaded from www.sqsh.org, but these will not include '\reconnect' fix (and neither support columns longer than 255 bytes).

'sqsh' documentation
I highly recommend reading the sqsh documentation. There's an excellent manual page in the ./doc subdirectory of your sqsh directory tree and you're pretty much guaranteed to discover a command a feature you didn't know yet.

Acknowledgements
Special thanks to Michael Peppler for fixing the '\reconnect' bug and for figuring out how to build sqsh on CygWin.

This document is located at www.sypron.nl/sqsh.html

Site Map Shop Quiz Mailing List Feedback About this site Copyright/Legal