Sybase ASE, Replication Server
all versions
Last updated: 07 August 2014
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 tool product originally designed and developed by Scott Gray (who received the ISUG award for his work a few years ago). In 2004, support has been taken over by Michael Peppler (another ISUG award winner). Today it's being maintained and supported by Martin Wesdorp.
sqsh can be downloaded from http://sourceforge.net/projects/sqsh. NB: the old site www.sqsh.org is no longer related to 'sqsh' (as you'll see when you visit)

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.8 Copyright (C) 1995-2001 Scott C. Gray
Portions Copyright (C) 2004-2012 Michael Peppler and Martin Wesdorp
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:
[103] X150.sa.master.1> REPX126
[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.

(Update September 2012: there is actually a better way dynamically reconnect when you're working in a RepServer context: see the quiz question for September 2012).

Downloading and building 'sqsh'
To get sqsh running, you'll have to build it yourself. That's usually easy: go to the sqsh page at Sourceforge, and download the latest version of the sqsh sources (currently -August 2014- the latest version is 2.5). Then see the INSTALL file for instructions how to build.

'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.


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