Sybase ASE 15.x版本中bcp和isql工具的新特性

发表评论

本博客中关于isql和bcp这2个工具的文章有：

ISQL：

  1. Sybase ASE中用isql连接数据库失败时的错误信息所反馈的信息
  2. ASE中的命令行实用工具isql的几个参数的使用方法

BCP：

  1. 关于BCP工具的使用，介绍三个方面的东西
  2. 关于Sybase中的快bcp
  3. ASE15.x的bcp能够实现按照条件导出表内数据
  4. bcp导出数据时禁止字符集转化
  5. 快速生成bcp in导表命令行的方法

本文介绍Sybase ASE V15.x版本中isql和bcp这两个工具的新特性。在前面的博文：ASE中的命令行实用工具isql的几个参数的使用方法 中介绍了isql的几个新特性。

1 BCP

1.1 Skip number of rows: --skiprows (SDK 12.5.1 ESD #19)

1.2 Partition support for multiple-partition, multiple-file operations (ASE 15.0)

1.3 Computed columns: --hide-vcc / --show-fi (ASE 15.0)

1.4 Encrypted columns: -C (ASE 15.0 ESD #1 / SDK 12.5.1 ESD #5)

1.5 Initialization strings: --initstring (ASE 15.0 ESD #5)

1.6 Discard file support for rejected rows: -d (ASE 15.0 ESD #8)

1.7 Alternate Sybase home: -y (New Features in 15.0.2 ESD#5 / SDK 15.0 ESD#2)

1.8 bcp64 for 64-bit products on UNIX platforms (New Features in 15.5 ESD #9)

1.9 Usage

2 ISQL

2.1 isql external error status return: --retserverror (ASE 15.0.2 ESD#4 / SDK 15.0 ESD#10)

2.2 Alternate Sybase home: -y (ASE 15.0.2 ESD#5 / SDK 15.0 ESD#2)

2.3 Redirects/Append the output of the T-SQL command to file_name (ASE 15.0.2 ESD#5 / SDK 15.0 ESD#12)

2.4 Pipes the output of the T-SQL command to an external command (ASE 15.0.2 ESD#5 / SDK 15.0 ESD#12)

2.5 Obfuscated input: --conceal (ASE 15.0 ESD #13)

2.6 Verbose usage: --help (ASE 15.0 ESD #14)

2.7 Application name: --appname (ASE 15.0.3 ESD#4 / OCS 15.0 ESD#17 / OCS 15.5)

2.8 Command history: --history (ASE 15.0.3 ESD#4 / OCS 15.0 ESD#18 / OCS 15.5)

2.8.1 Command history

2.8.2 Listing command history

2.8.3 Recalling and reissuing commands

2.9 isql64 for 64-bit products on UNIX platforms (New Features 15.5 ESD #9)

2.10 Usage

3 Sources

3.1 New Features Open Server™ 15.5 and SDK 15.5 for Windows, Linux, UNIX, and Mac OS X

3.2 New Features Open Server™ 15.0 and SDK 15.0 for Microsoft Windows, Linux, UNIX, and Mac OS X

 

BCP

 

Skip number of rows: --skiprows (SDK 12.5.1 ESD #19)

You can now use the new --skiprows parameter to skip the number of rows starting from the first row of an input file. This new feature provides BCP the ability to skip a specified number of rows before starting to copy from an input file. The valid range for --skiprows is between 0 and the actual number of rows in the input file. Providing an invalid value will display an error message.

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)

In the following example, BCP creates the discard file reject_titlesfile.txt:
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)

You can now set an alternate Sybase home directory using the new isql and bcp option -y.

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

 
 

isql external error status return: --retserverror (ASE 15.0.2 ESD#4 / SDK 15.0 ESD#10)

In this example, isql encounters a server error of severity 16. Since the --retserverror option is specified, isql returns “2” to the calling shell, prints “Msg 207” to stderr, and exits. As before, isql prints the full server error message to stdout. The same behavior applies to Windows, where you will find the return value of “2” in %ERRORLEVEL%:

$ 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

 

Alternate Sybase home: -y (ASE 15.0.2 ESD#5 / SDK 15.0 ESD#2)

You can now set an alternate Sybase home directory using the new isql and bcp option -y.
 

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:

Writes the output of the select @@servername command to the file myserver.txt, or overwrites that file if it already exists:
1> select @@servername
2> go > myserver.txt

Example 2:

Writes the output of the select @@version command to the new file myserver.txt, or appends it to that file if it already exists:
1> select @@version
2> go >> myserver.txt


Pipes the output of the T-SQL command to an external command (ASE 15.0.2 ESD#5 / SDK 15.0 ESD#12)

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

Obfuscated input: --conceal (ASE 15.0 ESD #13)

 
With the new --conceal command line option, you can hide your input during an isql session. The --conceal option is useful when entering sensitive information, such as passwords. The syntax for the --conceal option is:
isql --conceal [':?' | 'wildcard']
Example 1:
Changes password without displaying the password entered. This example uses “old” and “new” as prompt labels:
$ isql -Uguest -Pguest -Smyase --conceal
1> sp_password
2> :? old
3> ,
4> :?:? new
5> go
old
new
Confirm new
Password correctly set.
(return status = 0)

Example 2:

Changes password without displaying the password entered. This example uses the default wildcard as the prompt label:
$ isql -Uguest -Pguest -Smyase --conceal
1> sp_password
2> :?
3> ,
4> :?:?
5> go
:?
:?
Confirm :?
Password correctly set.
(return status = 0)
 
Example 3:
Activates a role for the current user. This example uses a custom wildcard and the prompt labels “role” and “password:”
$ isql -UmyAccount --conceal '*'
Password:
1> set role
2> * role
3> with passwd
4> ** password
5> on
6> go
role
password
Confirm password
1>

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

Why is this useful ?
– 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)

 
– This feature simplifies:

  • 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:

Sets the application name to “isql Session 01”:
$ isql -UmyAccount -SmyServer --appname "isql Session 01"
Password:
1>select program_name from sysprocesses
2>where spid=@@spid
3>go
program_name
-------------------
isql Session 01

Example 2:
Sets the application name to the name of the script that started the isql session:
$ isql --appname $0
Example 3:
The following 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:

;Sample ocs.cfg file
[DEFAULT]
;place holder

[isql]
;place holder

[isql_dbg_net]
CS_DEBUG = CS_DBG_NETWORK
;CS_APPNAME = "isql"

To run isql normally:
isql -Uguest
To run isql with network debug information:
isql -Uguest --appname isql_dbg_net
 
 

Command history: --history (ASE 15.0.3 ESD#4 / OCS 15.0 ESD#18 / OCS 15.5)

 

Command history

The isql command history feature enables you to list, recall, and reissue past commands. The command history is loaded into memory when you start isql, and is updated whenever a new command is issued. If specified, isql saves the in-memory history to disk when isql shuts down.

Syntax:

isql [--history [p]history_length [--history_file history_filename]]
Parameters:
--history

  • 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:

Deletes myaseHistory.log after loading its contents to memory. The session’s command history is not stored:
isql -Uguest -Ppassword -Smyase --history 1024 --history_file myaseHistory.log
 
Example 2:
Loads and saves the command history using the default log file:
isql -Uguest -Ppassword -Smyase --history p1024
 
Usage
The command history feature is available only in command mode. Also, only commands that are issued interactively in isql are included in the command history.
Command history contains the most recent commands issued in an isql session. When history_length is reached, isql drops the oldest command from the history and adds the newest command issued.
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

In an isql session, use the h command to display the command history. A page can display up to 24 lines of commands. If the command history contains more than 24 lines, press Enter to display the next set of commands or enter “a” to display all commands in one page. Enter “q” to return to isql.

Syntax:
h [n]

Parameter:
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:

Lists all the commands stored in the command history:
1> h
[1] select @@version
[2] select db_name()
[3] select @@servername
1>

Example 2:
Lists the two most recent commands issued:
1> h -2
[2] select db_name()
[3] select @@servername
1>

Recalling and reissuing commands

Use the ? command to recall and reissue a command from the command history.

Syntax:
? n | ??

Parameter:
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

Example 1:
Recalls the command labeled 1 from the command history:
1> ? 1
1> select @@version
2>

Example 2:
Recalls the latest issued command from the command history:
1> ? -1
1> select @@servername
2>
Usage:
When a command is recalled from history, the recalled command overwrites the command in the command buffer.
You can edit a recalled command before resubmitting the command to the server.


isql64 for 64-bit products on UNIX platforms (New Features 15.5 ESD #9)

Usage

isql [option1] [option2] ... where [options] are ...
  -b                   Disables the display of the table headers output.
  -e                   Echoes input.
  -F                   Enables the FIPS flagger.
  -p                   Prints performance statistics.
  -n                   Removes numbering and prompt symbol when used with -e.
  -v                   Prints the version number and copyright message.
  -W                   Turn off extended password encryption on
                       connection retries.
  -X                   Initiates the login connection to the server with
                       client-side password encryption.
  -Y                   Tells the Adaptive Server to use chained transactions.
  -Q                   Enables the HAFAILOVER property.
  -a display_charset   Used in conjunction 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.
  -A packet_size       Specifies the network packet size to use for
                       this isql session.
  -c cmdend            Changes the command terminator.
  -D database          Selects the database in which the isql session begins.
  -E editor            Specifies an editor other than the default editor vi.
  -h header            Specifies the number of rows to print between
                       column headings.
  -H hostname          Sets the client host name.
  -i inputfile         Specifies the name of the operating system file
                       to use for input to isql.
  -I interfaces_file   Specifies the name and location of the interfaces file.
  -J client_charset    Specifies the character set to use on the client.
  -K keytab_file       Specifies the path to the keytab file used
                       for authentication in DCE.
  -l login_timeout     Specifies the number of seconds to wait for the server
                       to respond to a login attempt.
  -m errorlevel        Customizes the error message display.
  -M labelname labelvalue
                       Used for security labels. See CS_SEC_NEGOTIATE
                       for more details.
  -o outputfile        Specifies the name of an operating system file to
                       store the output from isql.
  -P password          Specifies your Adaptive Server password.
  -R remote_server_principal
                       Specifies the principal name for the server as defined
                       to the security mechanism.
  -s col_separator     Resets the column separator character, which is
                       blank by default.
  -S server_name       Specifies the name of the Adaptive Server to which.
                       to connect to.
  -t timeout           Specifies the number of seconds before a SQL command
                       times out.
  -U username          Specifies a login name. Login names are case sensitive.
  -V [security_options]
                       Specifies network-based user authentication.
                       Valid [security_options]:
                       c - Enable data confidentiality service.
                       i - Enable data integrity service.
                       m - Enable mutual authentication for connection
                           establishment.
                       o - Enable data origin stamping service.
                       q - Enable out-of-sequence detection.
                       r - Enable data replay detection.
                       d - Requests credential delegation and forwards
                           client credentials.
  -w column_width      Sets the screen width for output.
  -y sybase_directory  Sets an alternate location for the Sybase home directory.
  -z localename        Sets the official name of an alternate language to
                       display isql prompts and messages.
  -Z security_mechanism
                       Specifies the name of a security mechanism to use
                       on the connection.
  -x trusted.txt_file  Specifies an alternate trusted.txt file location.
  --retserverror       Forces isql to terminate and return a failure code when
                       it encounters a server error of severity greater than 10.
  --conceal [wildcard] Obfuscates input in an ISQL session. The optional
                       wildcard will be used as a prompt.
  --appname application_name
                       Replaces the default 'isql' application name in
                       the server with 'application_name'.
  --history [p]length [--history_file file_name]
                       Activates command history of <length> commands in isql.
                       With 'p' history will be saved at the end of the session.
                       --history_file file_name  Path to, and including name of
                                                 the command history file.
 

Sources

New Features Open Server™ 15.5 and SDK 15.5 for Windows, Linux, UNIX, and Mac OS X

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc20155.1550/html/newfesd/title.htm

New Features Open Server™ 15.0 and SDK 15.0 for Microsoft Windows, Linux, UNIX, and Mac OS X

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc20155.1500/html/newfesd/title.htm


  • 本文链接地址：http://www.sybrepair.com/isqlbcp_news.htm；
  • 本文为dbainfo个人原创，请在尊重作者劳动成果的前提下进行转载；
  • 转载务必注明原始出处 : Sybase数据库技术，数据库恢复专家；
  • 对《Sybase ASE 15.x版本中bcp和isql工具的新特性》有何疑问或见解，请在本文下方发表；
  • 对网站还有其他问题或建议，请提交在留言板，谢谢！
    • 标签: , ,
    目前还没有任何评论.
    :wink: :twisted: :roll: :oops: :mrgreen: :lol: :idea: :evil: :cry: :arrow: :?: :-| :-x :-o :-P :-D :-? :) :( :!: 8-O 8)