Sybase ASE 15.x版本中bcp和isql工具的新特性
本博客中关于isql和bcp这2个工具的文章有：
ISQL：
BCP：
本文介绍Sybase ASE V15.x版本中isql和bcp这两个工具的新特性。在前面的博文：ASE中的命令行实用工具isql的几个参数的使用方法 中介绍了isql的几个新特性。
BCP
Skip number of rows: --skiprows (SDK 12.5.1 ESD #19)
The new --skiprows parameter cannot co-exist with the -F option.
In the following example, BCP will ignore the first two rows of the input file titles.txt and start to copy from the third row.
bcp pubs2..titles in titles.txt -U username -P password --skiprows 2
Partition support for multiple-partition, multiple-file operations (ASE 15.0)
Computed columns: --hide-vcc / --show-fi (ASE 15.0)
Two new parameters are included to support BCP computed columns:
--hide-vcc: Instructs BCP not to copy VCCs.
--show-fi: Instructs BCP to copy FIs.
Encrypted columns: -C (ASE 15.0 ESD #1 / SDK 12.5.1 ESD #5)
Currently, data is retrieved in plain text when BCP is running against a table that supports encrypted columns, and the user has permission to view the data in the columns.
The new BCP command line option (-C) allows bulk movement of cipher-text data for authorized users. For this to occur, you must use the -C option, and the table located on the server must support encrypted columns. This results in a SQL command, set ciphertext=on, to be sent to the server before initiating any bulk library routines to produce cipher-text.
Initialization strings: --initstring (ASE 15.0 ESD #5)
In the following example, replication is disabled when titles.txt data is transferred into the pubs2 titles table:
bcp pubs2..titles in titles.txt –-initstring “set replication off”
Discard file support for rejected rows: -d (ASE 15.0 ESD #8)
bcp pubs2..titles in titlesfile.txt -d reject_If you use multiple input files, one discard file is created for every input file that has an erroneous row. If there are no rejected rows, no discard file is created.
If bcp reaches the maximum errors allowed and stops the operation, the bcp logs all the rows from the beginning of the batch until the failed row.
Currently, the bcp option -e errorfile logs the rows rejected due to conversion or format errors into an error file. ESD #8 extends the functionality of this option to log all rejected rows including those resulting from inserting duplicate rows when unique constraint is present, inserting into a table-partition that doesn’t fit the partition criteria, and truncation of data.
Alternate Sybase home: -y (New Features in 15.0.2 ESD#5 / SDK 15.0 ESD#2)
bcp64 for 64-bit products on UNIX platforms (New Features in 15.5 ESD #9)
Usage
bcp [[database_name.]owner.]table_name [: [ partition_id | slice_number ] |
partition partition_name] {in | out} datafile
[-f formatfile]
[-e errfile]
[-d discardfileprefix]
[-F firstrow]
[-L lastrow]
[-b batchsize]
[-m maxerrors]
[-n]
[-c]
[-t field_terminator]
[-r row_terminator]
[-U username]
[-P password]
[-I interfaces_file]
[-S server]
[-a display_charset]
[-z language]
[-A packet_size]
[-J client_charset]
[-T text_or_image_size]
[-E]
[-g id_start_value]
[-N]
[-W]
[-X]
[-M LabelName LabelValue]
[-labeled]
[-K keytab_file]
[-R remote_server_principal]
[-C]
[-V [security_options]]
[-Z security_mechanism]
[-Q]
[-Y]
[-y sybase directory]
[-x trusted.txt_file]
[--maxconn maximum_connections
[--show-fi]
[--hide-vcc]
[--colpasswd [[[database_name.[owner].table_name.]column_name
[password]]]
[--keypasswd [[database_name.[owner].]key_name [password]]]
isql external error status return: --retserverror (ASE 15.0.2 ESD#4 / SDK 15.0 ESD#10)
$ isql -Uguest -Pguestpwd -SmyASE --retserverror 2> isql.stderr
1> select no_column from sysobjects
2> go
Msg 207, Level 16, State 4:
Server 'myASE', Line 1:
Invalid column name 'no_column'.
$ echo $?
2
$ cat isql.stderr
Msg 207
Redirects/Append the output of the T-SQL command to file_name (ASE 15.0.2 ESD#5 / SDK 15.0 ESD#12)
Example 1:
1> select @@servername2> go > myserver.txt
Example 2:
1> select @@version2> go >> myserver.txt Example:
Pipes the output of the sp_who command to grep and returns the lines that contain the string 'sa':
1> sp_who
2> go | grep sa
isql --conceal [':?' | 'wildcard']$ isql -Uguest -Pguest -Smyase --conceal1> sp_password2> :? old3> ,4> 
? new5> gooldnewConfirm newPassword correctly set.(return status = 0)
Example 2:
$ isql -Uguest -Pguest -Smyase --conceal1> sp_password2> :?3> ,4> ?5> go:?:?Confirm :?Password correctly set.(return status = 0)$ isql -UmyAccount --conceal '*'Password:1> set role2> * role3> with passwd4> ** password5> on6> gorolepasswordConfirm password1>
Verbose usage: --help (ASE 15.0 ESD #14)
Application name: --appname (ASE 15.0.3 ESD#4 / OCS 15.0 ESD#17 / OCS 15.5)
The new isql option --appname allows you to change the default application name isql to the isql client application name.
isql -Usa -P… --appname my_app_name
The specified value appears in sysprocesses.program_name
– Various ASE features are related to this application name:
- Resource limits (sp_add_resource_limit; since ASE 11.5)
- Temporary database bindings (sp_tempdb)
- Workload Manager (Logical Clusters) in ASE CE
– Testing these features can be problematic if you cannot set the application name to an arbitrary value; this was not always easy to do (ocs.cfg)
- Testing of Adaptive Server cluster routing rules for incoming client connections based on the client application name.
- Switching between alternative settings for isql in $SYBASE/$SYBASE_OCS/config/ocs.cfg, such as between debugging and normal sessions.
- Identification of the script that started a particular isql session from within Adaptive Server.
Example 1:
$ isql -UmyAccount -SmyServer --appname "isql Session 01"Password:1>select program_name from sysprocesses2>where spid=@@spid3>goprogram_name-------------------isql Session 01Example 2:
$ isql --appname $0 ;Sample ocs.cfg file
[DEFAULT]
;place holder
[isql]
;place holder
[isql_dbg_net]
CS_DEBUG = CS_DBG_NETWORK
;CS_APPNAME = "isql"
isql -Uguestisql -Uguest --appname isql_dbg_netCommand history: --history (ASE 15.0.3 ESD#4 / OCS 15.0 ESD#18 / OCS 15.5)
Command history
Syntax:
isql [--history [p]history_length [--history_file history_filename]]- p – 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.
- history_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.
--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:
For UNIX: $HOME/.sybase/isql/isqlCmdHistory.log
For Windows: %APPDATA%\Sybase\isql\isqlCmdHistory.log
Example 1:
isql -Uguest -Ppassword -Smyase --history 1024 --history_file myaseHistory.logisql -Uguest -Ppassword -Smyase --history p1024The command history feature is available only in command mode. Also, only commands that are issued interactively in isql are included in the command history.
If you do not specify an alternate log file, and if the $HOME or %APPDATA% environment variable used by the default log file is not defined, an error message appears and the command history log is not saved.
Listing command history
Syntax:
h [n]
n – indicates the number of commands to appear. If n is positive, the commands that appear start from the oldest command in the history. If n is negative, the n most recent commands appear.
Example 1:
1> h[1] select @@version[2] select db_name()[3] select @@servername1>Example 2:
1> h -2[2] select db_name()[3] select @@servername1>
Recalling and reissuing commands
Syntax:
? n | ??
n – when n is positive, isql looks for the command labeled with the number n and loads this to the command buffer. When n is negative, isql loads the nth most recent command issued.
?? – recalls the latest command issued and is equivalent to ? -1.
Examples:
In this sample command history used for examples 1 and 2, [1] tags the oldest command issued and [3] tags the most recent command issued:
[1] select @@version
[2] select db_name
[3] select @@servername
1> ? 11> select @@version2>Example 2:
1> ? -11> select @@servername2>