JCMT image here


Previous: Isql Up: TMS Utilities Next: Sqsh

Sybase SQL Server(TM)Utility Programs for UNIX (Chapter 3)

Using the isql Utility

This chapter introduces the interactive SQL utility isql and discusses some isql topics: changing the command terminator, the interaction of the performance option and command terminator values, setting the network packet size, and input and output files.

How to Use Transact-SQL with the isql Utility

You can use SQL directly from the operating system with the standalone utility program isql . You must have an account, or login, on SQL Server. To access the account, enter this command at your operating system prompt:


The following prompt appears:


Type your password at the prompt and press the Return key. The password does not appear on the screen as you type. The isql prompt appears, as follows:


You can now start issuing Transact-SQL commands. The isql program sends the commands to SQL Server, formatting the results and printing them to standard output. There is no maximum size for an isql statement.

Terminate a command by typing the default command terminator go on a new line. For example:


1> select *
2> from authors
3> where city = "Oakland"
4> go

To exit isql , type "quit" or "exit" on a line by itself.

Formatting isql Output

Table 3-1 describes the command line options that change the format of isql output:

Table 3-1: Format options for isql
Option Default Meaning
-h headers 1 Number of rows to print between column headings
-s colseparator Single space Changes the column separator character
-w linewidth 80 characters Changes the line width

To include each command issued to isql in the output, use the -e option; use the -n option to remove numbering and prompt symbols. For example:

 isql -e -n -o output 
 select * 
from authors
where city = "Oakland"
 cat output 
 select * 
from authors
where city = "Oakland"
 au_id       au_lname                             au_fname
 phone        address                                 
 city               state  country      postalcode 
 ----------- ------------------ -------------------------- 
------------ ---------------------------------------- 
-------------------- ----- ------------ ---------- 
 213-46-8915 Green                                 Marjorie
 415 986-7020 309 63rd St. #411                       
 Oakland              CA    USA          94618      
 274-80-9391 Straight                               Dick        
 415 834-2919 5420 College Av.                        
 Oakland              CA    USA          94609      
 724-08-9931 Stringer                             Dirk
 415 843-2991 5420 Telegraph Av.                      
 Oakland              CA    USA          94609      
 724-80-9391 MacFeather                           Stearns
 415 354-7128 44 Upland Hts.                          
 Oakland              CA    USA          94612      
 756-30-7391 Karsen                               Livia
 415 534-9219 5720 McAuley St.                        
 Oakland              CA    USA          94609      
(5 rows affected)

Note that the output file does not include the command terminator.

Correcting Input

If you make an error when typing a Transact-SQL command, you can:

set Options That Affect Output

Table 3-2 lists the set options that affect Transact-SQL output. For more information, refer to the set command in the SQL Server Reference Manual .

Table 3-2: set options that affect Transact-SQL output
set Option Default Meaning
char_convert Off Turns character set conversion off and on between SQL Server and a client; it also starts a conversion between the server character set and a different client character set
fipsflagger Off Warns when any Transact-SQL extensions to entry level SQL92 are used
flushmessage Off Sends messages as they are generated
language us_english Sets the language for system messages
nocount Off Turns off report of number of rows affected
noexec Off Compiles each query but does not execute it; often used with showplan
parseonly Off Checks the syntax of queries and returns error messages without compiling or executing the queries
showplan Off Generates a description of the processing plan for a query; does not print results when used inside a stored procedure or trigger
statistics io
statistics time
Off Displays performance statistics after each execution
statistics subquerycache Off Displays the number of cache hits, misses, and rows in the subquery cache for each subquery
textsize 32KB Controls the number of bytes of text or image data returned

Changing the Command Terminator

If you include the command terminator argument ( -c ), you can choose your own terminator symbol; "go" is the default value for this option. You must always enter the command terminator without blanks or tabs in front of it.

For example, to use a period as the command terminator, invoke isql as follows:

 isql -c. 

A sample isql session with this command terminator looks like this:

 1> select name from sysusers 
2> .
(3 rows affected) 

Using the isql command terminator option with scripts requires advance planning:

Performance Statistics Interaction with Command Terminator Values

isql provides a performance statistics option ( -p ). For example:

   isql -p 
 1> select * from
2> go

returns the following statistics:

 1 xact: 
Clock Time (ms.): total = 2000 avg = 2000 (0.50 xacts per sec.)

This means that a single transaction took 2,000 milliseconds, so the average is one transaction per 2,000 milliseconds. The clock time value reflects the entire transaction, which starts when Client-Library builds the query and ends when Client-Library returns the information from SQL Server.

You can gather performance statistics based on executing one or more transactions. To gather statistics on more than one transaction, specify a number after the command terminator ( go , by default). For example:

 isql -p 
 1> select * from
2> go 3

instructs SQL Server to execute three select * transactions and report the performance statistics. SQL Server returns:

Clock Time (ms.): total = 1000 avg = 333 (3.00 xacts per sec.)

Setting the Network Packet Size

The -A size option specifies the network packet size to use for this isql session For example:

 isql -A 2048

sets the packet size to 2,048 bytes for this isql session. To check, type:

 select * from sysprocesses 

The value appears under the network_pktsz heading.

size must be between the values of the default network packet size and max network packet size configuration parameters, one-third the size of the additional network memory configuration parameter, and must be a multiple of 512. SQL Server uses the closest available packet size that is a multiple of 512 if there is not enough memory available.

Use packet sizes larger than the defaults to perform I/O-intensive operations, such as readtext or writetext operations.

Setting or changing SQL Server's packet size does not affect the remote procedure call's packet size.

Input and Output Files

You can specify input and output files on the command line with the -i and -o options.

isql does not provide formatting options for the output. However, you can use the -n option to eliminate the isql prompts, and use other tools to reformat the output.

If you use the -e option, isql echoes the input to output. The resulting output file contains both the queries and their results.

UNIX Command Line Redirection

The UNIX redirection symbols, "<" and ">", provide a similar mechanism to the -i and -o options, as follows:

 isql -Usa -P  password  < input > output 

You can direct isql to take input from the terminal, as in the following example:

 isql -Usa -P  password  <<
EOF > output 
 select * from table 

"<<EOF" instructs isql to take input from the terminal until the string "EOF." You can replace "EOF" with any character string.

Similarly, the following example signals the end of input with Ctrl-d:

 isql -Usa -P  password  << > output 

Previous: Isql Up: TMS Utilities Next: Sqsh

JACH | JCMT | UKIRT | Computer Services | JCMT TMS
[ JACH | JCMT | UKIRT | Computer Services | JCMT TMS ]

Please address any comments, suggestions or requests to:

Remo Tilanus

Last modified: Fri Jan 7 12:52:57 HST 2000