Bulk Copy utility (iq_bcp)

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.

Syntax

iq_bcp [ [ database_name. ] owner. ] table_name { in | out } datafile

Parameters

Table 3-1 lists the available parameters for iq_bcp.

**Table 3-1: iq_bcp parameters**
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.

Usage

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.

**Table 3-2: Numeric data types**
	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	—	—

Table 3-3: Character data types*
	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.

Table 3-4: Binary data types*
	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.

**Table 3-5: Date data types**
	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.

**Table 3-6: IQ data types**
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.

Options

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.

Constraints

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.