Open-client based utility that copies a database table to or from an operating system file in a user-specified format. iq_bcp is located in $SYBASE\ASIQ-12_7\bin.
iq_bcp [ [ database_name. ] owner. ] table_name { in | out } datafile
Table 3-1 lists the available parameters for iq_bcp.
Option |
Description |
---|---|
-S |
Specifies the Sybase IQ server name to which iq_bcp connects. |
-I |
Specifies the name and location of the interface file to search when connecting to Sybase IQ. If you do not specify -I, iq_bcp looks for an interface file (sql.ini in Windows) located in the directory specified by the SYBASE environment variable; for example, ini directory in Windows. |
-U |
Specifies a Sybase IQ login name. |
-P |
Specifies a Sybase IQ password. If you do not specify -Ppassword, iq_bcp prompts for a password. You can omit the -P flag if the password is NULL. |
-c |
Performs the copy operation. Use this cross-platform file format if you are sharing data between platforms. This parameter does not prompt for each field; it uses CHAR as the default storage type of all columns in the data file, no prefixes, \t (tab) as the default field terminator, and \n (new line) as the default row terminator. |
-J |
Specifies the client character set. The iq_bcp utility supports multi-byte character data. When inserting or extracting data, the -J flag converts data from server character set to client character set. Omitting -J sets the client charset to that platform’s default, potentially causing incorrect data conversion because the default charset might not be what the client is using. For more information on character sets, see Chapter 11, “International Languages and Character Sets” in the Sybase IQ System Administration Guide. |
-v |
Displays the version number and copyright message of iq_bcp and returns to the operating system. |
-t |
Specifies the default field terminator. |
-r |
Specifies the default row terminator. When specifying terminators from the command line with the -t or -r parameter, you must escape characters that have special significance to the UNIX operating system (or the command prompt shell for Windows). Either place a backslash in front of the special character or enclose it in quotes. This is not necessary when iq_bcp prompts you (interactive mode). WARNING! Do not use -t or -r parameters with iq_bcp in native format. Results are unpredictable and data may become corrupted. |
-A |
Specifies the network packet size to use for this iq_bcp session. The following example sets the packet size to 4096 bytes: iq_bcp pubs2..titles out table_out -A 4096 packet_size must be between the values of the default network packet size and maximum network packet size configuration variables, and it must be a multiple of 512. Use network packet sizes larger than the default to improve the performance of large bulk-copy operations. |
iq_bcp filters out unsupported options by BCP and supports additional options listed in Table 3-1. For all unsupported options, iq_bcp displays a message.
You can view this information by issuing the following command without parameters:
iq_bcp[.exe]
iq_bcp offers two modes, iq_bcp in and iq_bcp out.
When executing a bulk copy in or out option, iq_bcp offers two formats for saving or reading the file: native and cross-platform. Sybase IQ supports cross-platform file format only. Native file format is not supported by Sybase IQ in any bulk copy operation.
iq_bcp in Inserts data into a Sybase IQ table from a physical file.
Data to be inserted into Sybase IQ tables via iq_bcp must be imported in cross-platform file format using the -c option.
The following tables illustrate the supported ASE data types for the iq_bcp in operation.
IQ data types |
|||||||||
TinyInt |
SmallInt |
Int |
Unsigned Int |
Big Int |
Unsigned Big Int |
Decimal |
Real |
Double |
|
ASE data types |
|||||||||
TinyInt |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
SmallInt |
— |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Int |
— |
— |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Unsigned Int |
— |
— |
— |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Big Int |
— |
— |
— |
— |
Yes |
Yes |
Yes |
Yes |
Yes |
Unsigned Big Int |
— |
— |
— |
— |
— |
Yes |
Yes |
Yes |
Yes |
Decimal |
— |
— |
— |
— |
— |
— |
Yes |
Yes |
Yes |
Real |
— |
— |
— |
— |
— |
— |
Yes |
Yes |
Yes |
Double |
— |
— |
— |
— |
— |
— |
Yes |
— |
— |
IQ data types |
|||
Char |
VarChar |
Long VarChar |
|
ASE data types |
|||
Char |
Yes |
Yes |
Yes |
VarChar |
Yes |
Yes |
Yes |
Text |
Yes |
Yes |
Yes |
NChar |
Yes |
Yes |
Yes |
NVarChar |
Yes |
Yes |
Yes |
UniChar |
— |
— |
— |
UniVarChar |
— |
— |
— |
*The support for character data types is limited to input values of size less than 32KB.
IQ data types |
|||
Binary |
VarBinary |
Long Binary |
|
ASE data types |
|||
Binary |
Yes* |
Yes* |
Yes* |
VarBinary |
Yes* |
Yes* |
Yes* |
Long Binary |
Yes* |
Yes* |
Yes* |
*For binary data type support to work, a database option, ASE_Binary_Display, must be set to OFF in IQ server. The default value of this option is ON. See “ASE_BINARY_DISPLAY option,” “Database Options,” in the Sybase IQ Reference Manual.
For BINARY data type, support is limited to column size of less than 256 bytes.
For VARBINARY and LONG BINARY data types, support is limited to column size of less than 32KB.
The ASE data type BIT is supported by the corresponding Sybase IQ data type BIT.
IQ data types |
|||||
Date |
Time |
Timestamp |
SmallDatetime |
Datetime |
|
ASE data types |
|||||
SmallDatetime |
— |
— |
Yes |
Yes |
Yes |
Datetime |
— |
— |
Yes |
Yes |
Yes |
All supported data types listed in Table 3-2 through Table 3-5 are supported for conversion to any Sybase IQ data type, as long as the value can be implicitly converted. Table 7-8 in Chapter 7, “Moving Data In and Out of Databases” of the Sybase IQ System Administration Guide describes the supported implicit data conversions used by Sybase IQ during an INSERT operation.
Sybase IQ supports additional data types, which are not directly compatible with any ASE data type. Table 3-6, illustrates all the Sybase IQ data types along with its equivalent data types in ASE, BCP and ASA. Table 3-6 does not list aliases or domains defined in Sybase IQ.
IQ data types |
ASE data types |
BCP data types |
ASA data types |
---|---|---|---|
Numeric data types |
|||
Tiny Int |
Tiny Int |
CS_TINYINT |
Unsigned Tiny Int |
Small Int |
Small Int |
CS_SMALLINT |
Signed Small Int |
Int |
Int |
CS_INT |
Signed Int |
Unsigned Int |
Unsigned Int |
CS_UINT_TYPE |
Unsigned Int |
Big Int |
Big Int |
CS_BIGINT_TYPE |
Big Int |
Unsigned Big Int |
Unsigned Big Int |
CS_UBIGINT_TYPE |
Unsigned Big Int |
Decimal |
Decimal |
CS_DECIMAL |
Decimal |
Real |
Real |
CS_REAL |
Real |
Double |
Double Precision |
CS_FLOAT |
Double |
Character data types |
|||
Char |
Char |
CS_CHAR |
Char |
VarChar |
VarChar |
CS_VARCHAR |
VarChar |
Long VarChar |
Text |
CS_TEXT |
Long VarChar |
Binary data types |
|||
Binary |
Binary |
CS_BINARY |
Binary |
VarBinary |
VarBinary |
CS_VARBINARY |
VarBinary |
Long Binary |
Image |
CS_IMAGE |
Long Binary |
Bit data types |
|||
Bit |
Bit |
CS_BIT |
Bit |
Bit allowing null |
— |
CS_BIT |
Bit allowing null |
Date data types |
|||
Date |
— |
— |
Date |
Time |
— |
— |
Time |
Timestamp |
Datetime |
CS_DATETIME |
Timestamp |
The only non-ASE data type supported for the iq_bcp in and iq_bcp out operation is nullable BIT data type.
iq_bcp out Extracts data from a Sybase IQ table to a physical file.
Data to be extracted from Sybase IQ tables, including data from ASE and ASA tables meant for import back in Sybase IQ tables, must be exported in cross-platform file format using the -c option.
database_name Is optional if the table being copied is in your default database or in master. Otherwise, you must specify a database name.
owner Is optional if you or the Database Owner own the table being copied. If you do not specify an owner, iq_bcp looks first for a table of that name that you own; then it looks for one owned by the Database Owner. If another user owns the table, you must specify the owner name or the command fails.
table_name Is the name of the database table to copy. The table name cannot be a Transact-SQL reserved word.
in | out Is the direction of the copy. in indicates a copy from a file into the database table, which must be imported in cross-platform file format using the -c option. out indicates a copy to a file from the database table or view, which must be exported in cross-platform file format using the -c option.
datafile Specifies a set of one or more unique data files, separated by commas. It is supported for both iq_bcp in and iq_bcp out. The path name can be from 1 to 255 characters in length.
If a table contains a decimal column, iq_bcp out is supported on that table only if the precision of the decimal column is less than or equal to 38 digits.
The precision specifies the maximum number of decimal digits that can be stored in the column. It includes all digits, both to the right and left of the decimal point. You can specify precisions ranging from 1 digit to 38 digits or use the default precision of 18 digits.
See Numeric data types, Chapter 4, “SQL Data Types,” in the Sybase IQ Reference Manual.
If any character column value in the input contains a space or spaces alone, a zero-length string is inserted in the table’s corresponding column.
Non-default values for server options, except TEXTSIZE, do not take effect for the iq_bcp client.
Table 7-8 in Chapter 7, “Moving Data In and Out of Databases” of the Sybase IQ System Administration Guide
“ASE_BINARY_DISPLAY option,” Chapter 2, “Database Options,” in the Sybase IQ Reference Manual
Chapter 4, “SQL Data Types” in the Sybase IQ Reference Manual