TY2me3nH

1Adaptive Server Enterprise 15.7 SP100 > Utility Guide 15.7 SP100 > Utility Commands Reference
2
3isql
4Interactive SQL parser to Adaptive Server.
5
6The utility is located in:
7(UNIX) $SYBASE/$SYBASE_OCS/bin.
8(Windows) %SYBASE%\%SYBASE_OCS%\bin, as isql.exe.
9Syntax
10isql [-b] [-e] [-F] [-n] [-p] [-v] [-W] [-X] [-Y] [-Q]
11	[-a display_charset]
12	[-A packet_size]
13	[-c cmdend]
14	[-D database]
15	[-E editor]
16	[-h header]
17	[-H hostname]
18	[-i inputfile]
19	[-I interfaces_file]
20	[-J client_charset]
21	[-K keytab_file]
22	[-l login_timeout]
23	[-m errorlevel]
24	[-M LabelName LabelValue]
25	[-o outputfile]
26	[-P password]
27	[-R remote_server_principal]
28	[-s col_separator]
29	[-S server_name]
30	[-t timeout]
31	[-U username]	[-v]
32	[-V [security_options]]
33	[-w column-width]
34	[-x trusted.txt_file]
35	[-y sybase_directory]
36	[-z localename]
37	[-Z security_mechanism]
38	[--appname "application_name”]
39	[--conceal [':?' | 'wildcard']]
40	[--help]
41	[--history [p]history_length [--history_file history_filename]]
42	[--retserverror]
43	[--URP remotepassword
44Parameters
45-a display_charset – allows you to run isql from a terminal where the character set differs from that of the machine on which isql is running. Use -a with -J to specify the character set translation file (.xlt file) required for the conversion. Use -a without -J only if the client character set is the same as the default character set.
46Note: The ascii_7 character set is compatible with all character sets. If either the Adaptive Server character set or the client character set is set to ascii_7, any 7-bit ASCII character can pass unaltered between client and server. Other characters produce conversion errors. For more information on character set conversion, see the System Administration Guide.
47-A packet_size – specifies the network packet size to use for this isql session. For example, to set the packet size to 4096 bytes for the isql session, use:
48isql -A 4096
49To check your network packet size, use:
50select * from sysprocesses
51The value appears under the network_pktsz heading.
52
53packet_size must be between the values of the default network packet size and maximum network packet size configuration variables, and must be a multiple of 512. The default value is 2048.
54
55Use larger-than-default packet sizes to perform I/O-intensive operations, such as readtext or writetext operations. Setting or changing the Adaptive Server packet size does not affect remote procedure calls’ packet size.
56-b – disables the display of the table headers output.
57-c cmdend – changes the command terminator. By default, you terminate commands and send them to the server by typing “go” on a line by itself. When you change the command terminator, do not use SQL reserved words or control characters.
58-D database – selects the database in which the isql session begins.
59-e – echoes input.
60-E editor – specifies an editor other than the default editor vi. To invoke the editor, enter its name as the first word of a line in isql.
61-F – enables the FIPS flagger. When you specify the -F parameter, the server returns a message when it encounters a nonstandard SQL command. This option does not disable SQL extensions. Processing completes when you issue the non-ANSI SQL command.
62-h headers – specifies the number of rows to print between column headings. The default prints headings only once for each set of query results.
63-H hostname – sets the client host name.
64-i inputfile – specifies the name of the operating system file to use for input to isql. The file must contain command terminators (the default is “go”).
65Specifying the parameter is equivalent to < inputfile:
66-i inputfile
67If you use -i and do not specify your password on the command line, isql prompts you for it.
68If you use < inputfile and do not specify your password on the command line, specify your password as the first line of the input file.
69-I interfaces_file – specifies the name and location of the interfaces file to search when connecting to Adaptive Server. If you do not specify -I, isql looks for a file named interfaces in the directory specified by your SYBASE environment variable.
70-J client_charset – specifies the character set to use on the client. The parameter requests that Adaptive Server convert to and from client_charset, the character set used on the client. A filter converts input between client_charset and the Adaptive Server character set.
71-J with no argument sets character set conversion to NULL. No conversion takes place. Use this if the client and server use the same character set.
72
73Omitting -J sets the character set to a default for the platform. The default may not necessarily be the character set that the client is using. For more information about character sets and the associated flags, see Configuring Client/Server Character Set Conversions, in the System Administration Guide, Volume One.
74-K keytab_file – specifies the path to the keytab file used for authentication in DCE. keytab_file specifies a DCE keytab that contains the security key for the user name you specify in -U. Create keytab files using the DCE dcecp utility. See your DCE documentation.
75If you do not specify -K, the isql user must be logged in to DCE with the same username specified in -U.
76-l login_timeout – specifies the maximum timeout value allowed when connecting to Adaptive Server. The default is 60 seconds. This value affects only the time that isql waits for the server to respond to a login attempt. To specify a timeout period for command processing, use the -ttimeout parameter.
77-m errorlevel – customizes error message appearance. For errors of the severity level specified or higher, only the message number, state, and error level appear; no error text appears. For error levels lower than the specified level, nothing appears.
78-M LabelName LabelValue – (Secure SQL Server only) enables multilevel users to set the session labels for the this isql session. Valid values for LabelName are:
79curread (current read level) – is the initial level of data that you can read during this session. curread must dominate curwrite.
80curwrite (current write level) – is the initial sensitivity level that is applied to any data that you write during this session.
81maxread (maximum read level) – is the maximum level at which you can read data. This is the upper bound to which you as a multilevel user can set curread during the session. maxread must dominate maxwrite.
82maxwrite (maximum write level) – is the maximum level at which you can write data. This is the upper bound to which you as a multilevel user can set curwrite during a session. maxwrite must dominate minwrite and curwrite.
83minwrite (minimum write level) – is the minimum level at which you can write data. This is the lower bound to which you as a multilevel user can set curwrite during a session. minwrite must be dominated by maxwrite and curwrite.
84LabelValue is the actual value of the label, expressed in the human-readable format used on your system (for example, “Company Confidential Personnel”).
85-n – removes numbering and the prompt symbol (>) from the echoed input lines in the output file when used with -e.
86-o outputfile – specifies the name of an operating system file to store the output from isql. Specifying the parameter as -o outputfile is similar to > outputfile
87-p – prints performance statistics.
88-P password – specifies your Adaptive Server password. If you do not specify the -P flag, isql prompts for a password. If your password is NULL, use the-P flag without any password.
89-Q – provides clients with failover property. See Using Sybase Failover in a High Availability System.
90-R remote_server_principal – specifies the principal name for the server as defined to the security mechanism. By default, a server’s principal name matches the server’s network name (which is specified with the -S parameter or the DSQUERY environment variable). Use the -R parameter when the server’s principal name and network name are not the same.
91-s colseparator – resets the column separator character, which is blank by default. To use characters that have special meaning to the operating system (for example, “|”, “;”, “&”, “<”, “>”), enclose them in quotes or precede them with a backslash.
92The column separator appears at the beginning and the end of each column of each row.
93-S server_name – specifies the name of the Adaptive Server to which to connect. isql looks this name up in the interfaces file. If you specify -S without server_name, isql looks for a server named SYBASE. If you do not specify -S, isql looks for the server specified by your DSQUERY environment variable.
94-t timeout – specifies the number of seconds before a SQL command times out. If you do not specify a timeout, the command runs indefinitely. This affects commands issued from within isql, not the connection time. The default timeout for logging into isql is 60 seconds.
95-U username – specifies a login name. Login names are case-sensitive.
96-v – prints the version and copyright message of isql and then exits.
97isql is available in both 32-bit and 64-bit versions. They both reside in the same directory and are differentiated by their executable file names (isql and isql64). Enter isql -v or isql64 -v to see the detailed version string of the isql you are using.
98-V security_options – specifies network-based user authentication. With this option, the user must log in to the network’s security system before running the utility, and users must supply the network user name with the -U option; any password supplied with the -P option is ignored.
99Follow -V with a security_options string of key-letter options to enable additional security services. These key letters are:
100c – enables data confidentiality service.
101d – enables credential delegation and forwards the client credentials to the gateway application.
102i – enables data integrity service.
103m – enables mutual authentication for connection establishment.
104o – enables data origin stamping servic.e
105q – enables out-of-sequence detection
106r – enables data replay detection
107-W – disables both extended password and password encrypted negotiations.
108-w columnwidth – sets the screen width for output. The default is 80 characters. When an output line reaches its maximum screen width, it breaks into multiple lines.
109-x trusted.txt_file – specifies an alternate trusted.txt file.
110-X – initiates the login connection to the server with client-side password encryption. isql (the client) specifies to the server that password encryption is desired. The server sends back an encryption key, which isql uses to encrypt your password, and the server uses the key to authenticate your password when it arrives.
111This option can result in normal or extended password encryption, depending on connection property settings at the server. If CS_SEC_ENCRYPTION is set to CS_TRUE, normal password encryption is used. If CS_SEC_EXTENDED_ENCRYPTION is set to CS_TRUE, extended password encryption is used. If both CS_SEC_ENCRYPTION and CS_SEC_EXTENDED_ENCRYPTION are set to CS_TRUE, extended password encryption takes precedence.
112
113If isql fails, the system creates a core file that contains your password. If you did not use the encryption option, the password appears in plain text in the file. If you used the encryption option, your password is not readable.
114
115For details on encrypted passwords, see the user documentation for the Open Client Client-Library.
116-y sybase_directory – sets an alternate Sybase home directory.
117-Y – tells the Adaptive Server to use chained transactions.
118-z locale_name – specifies the official name of an alternate language to display isql prompts and messages. Without -z, isql uses the server’s default language. Add languages to an Adaptive Server during installation or afterward, using the langinstall utility (langinst in Windows) or the sp_addlanguage stored procedure.
119-Z security_mechanism – specifies the name of a security mechanism to use on the connection.
120Security mechanism names are defined in the libtcl.cfg configuration file located in the ini subdirectory below the Sybase installation directory. If no security_mechanism name is supplied, the default mechanism is used. For more information on security mechanism names, see the description of the libtcl.cfg file in the Open Client and Open Server Configuration Guide.
121--appname "application_name" – allows you to change the default application name isql to the isql client application name. This simplifies:
122Testing of Adaptive Server cluster routing rules for incoming client connections based on the client application name.
123Switching between alternative settings for isql in $SYBASE/$SYBASE_OCS/config/ocs.cfg, such as between debugging and normal sessions.
124Identification of the script that started a particular isql session from within Adaptive Server.
125application_name:
126Is the client application name. You can retrieve the client application name from sysprocesses.program_name after connecting to your host server.
127Has a maximum length of 30 characters. You must enclose the entire application name in single quote or double quote characters if it contains any white spaces that do not use the backslash escape character. You can set the application_name to an empty string.
128Note: You can also set the client application name in ocs.cfg using the CS_APPNAME property.
129--conceal [':?' | 'wildcard'] – hides your input during an isql session. The --conceal option is useful when entering sensitive information, such as passwords.
130wildcard, a 32-byte variable, specifies the character string that triggers isql to prompt you for input during an isql session. For every wildcard that isql reads, isql displays a prompt that accepts your input but does not echo the input to the screen. The default wildcard is :?.
131
132Note: --conceal is silently ignored in batch mode.
133--help – displays a brief description of syntax and usage for the isql utility consisting of a list of available arguments.
134--history [p]history_length [--history_file history_filename] – Loads the contents of the command history log file, if it exists, when isql starts. By default, the command history feature is off. Use the --history command line option to activate it.
135p – indicates command history persistence; in-memory command history is saved to disk when isql shuts down. If you do not use the p option, the command history log is deleted after its contents are loaded into memory.
136history_length – this parameter, which is required if you use --history, is the number of commands that isql can store in the command history log. The maximum value of history_length is 1024; if a larger value is specified, isql silently truncates it to 1024.
137-history_file history_filename – indicates that isql must retrieve the command history log from history_filename. If p is specified, isql also uses history_filename to store the current session’s command history. history_filename can include an absolute or a relative path to the log file. A relative path is based on the current directory. If you do not indicate a path, the history log is saved in the current directory. When --history_file is not specified, isql uses the default log file in $HOME/.sybase/isql/isqlCmdHistory.log.
138--retserverror – forces isql to terminate and return a failure code when it encounters a server error with a severity greater than 10. When isql encounters this type of abnormal termination, it writes the label “Msg” together with the actual Adaptive Server error number to stderr, and returns a value of 2 to the calling program. isql prints the full server error message to stdout.
139--URP remotepassword – enables setting the universal remote password remotepassword for clients accessing Adaptive Server.
140Examples
141Query edit – Opens a text editor where you can edit the query. When you write and save the file, you are returned to isql. The query appears; type “go” on a line by itself to execute it:
142isql -Ujoe -Pabracadabra
1431> select *
1442> from authors
1453> where city = "Oakland"
1464> vi
147Clearing and quitting – reset clears the query buffer, and quit returns you to the operating system:
148isql -Ualma
149Password:
1501> select *
1512> from authors
1523> where city = "Oakland"
1534> reset
1541> quit
155Column separators – Creates column separators using the “#” character in the output in the pubs2 database for store ID 7896:
156isql -Usa -P -s#
1571> use pubs2
1582> go
1591> select * from sales where stor_id = "7896"
160#stor_id#ord_num             #date                      #
161#-------#--------------------#--------------------------#
162#7896   #124152              #       Aug 14 1986 12:00AM#
163#7896   #234518              #       Feb 14 1991 12:00AM#
164
165(2 rows affected)
166Credentials – (MIT Kerberos) Requests credential delegation and forwards the client credentials to MY_GATEWAY:
167isql -Vd -SMY_GATEWAY
168Passwords – Changes password without displaying the password entered. This example uses “old” and “new” as prompt labels:
169$ isql -Uguest -Pguest -Smyase --conceal
170sp_password
171:? old
172,
173:?:? new
174----------------
175old
176new
177Confirm new
178Password correctly set.
179
180(Return status 0)
181Hide input – In this example of --conceal, the password is modified without displaying the password entered. This example uses “old” and “new” as prompt labels:
182$ isql -Uguest -Pguest -Smyase --conceal
1831> sp_password
1842> :? old
1853> ,
1864> :?:? new
1875> go
188
189old
190new
191Confirm new
192Password correctly set.
193(return status = 0)
194In this example of --conceal, the password is modified without displaying the password entered. This example uses the default wildcard as the prompt label:
195$ isql -Uguest -Pguest -Smyase --conceal
1961> sp_password
1972> :?
1983> ,
1994> :?:?
2005> go
201
202:?
203:?
204Confirm :?
205Password correctly set.
206(return status = 0)
207This example of --conceal uses a custom wildcard, and the prompt labels "role" and "password" to activate a role for the current user:
208$ isql -UmyAccount --conceal '*'
209Password:
2101> set role
2112> * role
2123> with passwd
2134> ** password
2145> on
2156> go
216
217role
218password
219Confirm password
2201>
221Return server error – returns 2 to the calling shell, prints “Msg 207” to stderr, and exits, when it encountered a server error of severity 16:
222guest> isql -Uguest -Pguestpwd -SmyASE --retserverror
223    2> isql.stderr
2241> select no_column from sysobjects
2252> go
226
227Msg 207, Level 16, State 4:
228Server 'myASE', Line 1:
229Invalid column name 'no_column'.
230
231guest> echo $?
2322
233guest> cat isql.stderr
234Msg 207
235guest >
236Application name – Sets the application name to the name of the script that started the isql session:
237isql --appname $0
238History – Loads and saves the command history using the default log file:
239isql -Uguest -Ppassword -Smyase --history p1024
240Run isql with configuration file – This sample ocs.cfg file allows you to run isql normally or with network debug information. Because the configuration file is read and interpreted after the command line parameters are read and interpreted, setting CS_APPNAME to isql sets the application name back to isql:
241;Sample ocs.cfg file 
242[DEFAULT] 
243;place holder 
244
245[isql] 
246;place holder 
247
248[isql_dbg_net] 
249CS_DEBUG = CS_DBG_NETWORK 
250CS_APPNAME = "isql"
251To run isql normally:
252isql -Uguest
253To run isql with network debug information:
254isql -Uguest --appname isql_dbg_net
255Load and save command history – Loads and saves the command history using the default log file:
256isql -Uguest -Ppassword -Smyase --history p1024
257Delete log – Deletes myaseHistory.log after loading its contents to memory. The session’s command history is not stored.
258isql -Uguest -Ppassword -Smyase --history 1024 
259   --history_file myaseHistory.log
260All commands in command history – Lists all the commands stored in the command history:
261isql -Uguest -Ppassword -Smyase --history p1024
2621> h
263
264[1] select @@version
265[2] select db_name()
266[3] select @@servername
267
2681>
269Most recent commands – Lists the two most recent commands issued:
270isql -Uguest -Ppassword -Smyase --history p1024
2711> h -2
272
273[2] select db_name()
274[3] select @@servername
275
2761>
277Recall labeled command from history – Recalls the command labeled 1 from the command history:
278isql -Uguest -Ppassword -Smyase --history p1024
2791> ? 1
280
2811> select @@version
2822>
283Recall last-issued command from history – Recalls the latest issued command from the command history:
284isql -Uguest -Ppassword -Smyase --history p1024
2851> ? -1
286
2871> select @@servername
2882> 
289Set directory – Sets an alternate Sybase home directory using the -y option:
290isql -y/work/NewSybase -Uuser1 -Psecret -SMYSERVER
291Roles – Activates a role for the current user. This example uses a custom wildcard and the prompt labels “role” and “password”:
292$ isql -UmyAccount --conceal '*'Password:
293set role
294* role
295with passwd
296** password
297on
298go
299
300role
301password
302Confirm password
303Application name – Sets the application name to “isql Session 01”:
304isql -UmyAccount -SmyServer --appname "isql Session 01"
305Password:
3061>select program_name from sysprocesses
3072>where spid=@@spid
3083>go
309
310program_name
311-------------------
312isql Session 01
313Deleting history – Deletes myaseHistory.log after loading its contents to memory. The session’s command history is not stored:
314isql -Uguest -Ppassword -Smyase --history 1024
315  --history_file myaseHistory.log
316Usage for isql
317Additional information for using isql.
318Parent topic: Utility Commands Reference
319Related concepts
320Interactive isql Commands
321Command History in isql
322Using Interactive isql from the Command Line
323Created October 1, 2013. Send feedback on this help topic to Technical Publications: pubs@sap.com