ttBulkCp

Description

Copies data between TimesTen tables and ASCII files. ttBulkCp has two modes:

• In copy-in mode rows are copied into an existing TimesTen table from one or more ASCII files (or stdin).

• In copy-out mode an entire TimesTen table is copied to a single ASCII output file (or stdout).

On UNIX, this utility is supported for TimesTen Data Manager DSNs. For Client DSNs, use the utility ttBulkCpCS.This utility only copies out the objects owed by the user executing the utility, and those objects for which the owner has SELECT privileges. If the owner executing the utility has the ADMIN privilege, ttBulkCP copies out all objects.

Required privilege

This utility requires the INSERT privilege on the tables it copies information into. It requires the SELECT privilege on the tables it copies information from. If authentication information is not supplied in the connection string or DSN, this utility prompts for a user ID and password before continuing.

Syntax

ttBulkCp {-h | -help | -? | -helpfull}

ttBulkCp {-V | -version}

ttBulkCp -i [-cp numTrans | final] [-d errLevel] 
[-e errorFile] [-m maxErrs] [-sc] [-t errLevel]
[-u errLevel] [-v 0|1] [-xp numRows | rollback] 
[-Cc | -Cnone] [-tformat timeFormat] [-dateMode dateMode] 
[-tsformat timeStampFormat] [-dformat | -D dateFormat] 
[-F firstRow] [-L lastRow] [-N ncharEncoding] [-Q 0|1] 
[-S errLevel] {-connStr connection_string | DSN} 
[owner.]tableName [dataFile ...]

ttBulkCp -o [-sc] [-v 0|1] [-A 0|1] [-Cc | -Cnone] 
[-tformat timeFormat] [-tsformat timeStampFormat] 
[-dateMode dateMode] [-dformat | -D dateFormat]
[-N ncharEncoding] [-noForceSerializable | -forceSerializable]
[-tsprec precision] [-Q 0|1] 
{-connStr connection_string | DSN} [owner.]tblName 
[dataFile]

Options

ttBulkCp has the options:

Warning: This output was produced using a non-serializable isolation level. It may therefore not reflect a transaction-consistent state of the table.

The following options can be used in copy-out (-o) mode only. You must have SELECT privileges on the specified tables.

The following options can be used in copy-in (-i) mode only. You must have INSERT privileges on the specified tables.

datafile format

Every line of a ttBulkCp input file is one of the following: a blank line, a comment line, an attribute line or a data line.

• Blank lines are lines with no characters at all, including whitespace characters (space and tab). Blank lines are ignored by ttBulkCp.

• Comment lines begin with the comment character. The default comment character is #; this default can be overridden with the -C command-line option or the COMMENTCHAR file attribute (see "Attribute line format"). The comment character must be the first character on the line. Comment lines are ignored by ttBulkCp. Comments at the end of data lines are not supported.

• Attribute lines are used for setting file attributes, which control the formatting of the datafile. Attribute lines begin with the ten-character sequence ##ttBulkCp. The full syntax for attribute lines is described in "Attribute line format". Attribute lines can appear anywhere in the datafile.

• Data lines contain the rows of the table being copied. Data lines in the datafile and rows of the table correspond one-to-one; that is, each data line completely describes exactly one row. Each data line consists of a list of column values separated by the field separator character. The default field separator is a comma (,). This default can be overridden by the -s command-line option or the FSEP file attribute. The full syntax for data lines is described in "Data line format".

Attribute line format

The format of an attribute line is:

##ttBulkCp[:attribute=value]...

Attribute lines always begin with the ten-character sequence ##ttBulkCp, even if the comment character is not #. This sequence is followed by zero or more file attribute settings, each preceded by a colon.

Attribute settings remain in effect until the end of the input file or until they are changed by another attribute line in the same input file. The values of any file attributes that are omitted in an attribute line are left unchanged.

Command line options take precedence over the values in the file attributes that are supported by ttBulkCp. Those file attributes are:

• VERSION: Specifies the version of the file format used in the file, expressed as major.minor. The only supported version is 1.0.

• DATEMODE: Specifies whether an Oracle DATE type is specified as simple date or as timestamp.

• FSEP: Specifies the field separator character used in the file. The field separator can be set to \t (tab) or any of the characters: ~ ! @ # $ % ^ & * ( ) = : ; | < > ? , /

• QUOTES: Indicates whether character string values in the file are enclosed in double quotes. The value can be 0, to indicate that strings are not quoted, or 1, to indicate that strings are quoted. This value can be overridden with the -Q option.

• COMMENTCHAR: Specifies the comment character used in the file. The comment character can be set to \t (tab) or any of the characters: ~ ! @ # $ % ^ & * ( ) = : ; | < > ? , /

The comment character can also be set to the value none, which disables the use of comments in the datafile.

• DFORMAT: Sets the date format. Legal values are described in "Date, time and timestamp values". When a custom format is used, it should be enclosed in single quotes. This value can be overridden with the -D/-dformat command-line option. See also TFORMAT and TSFORMAT.

• NCHARENCODING: Indicates the encoding to be used for the NCHAR and NVARCHAR2 data types. The value may be either ASCII or UTF-8.

• TFORMAT: Indicates the time format. Legal values are described in "Date, time and timestamp values". When a custom format is used, it should be enclosed in single quotes. This value can be overridden with the -tformat command-line option. See also DFORMAT and TSFORMAT.

• TSFORMAT: Sets the timestamp format. Legal values are described in "Date, time and timestamp values". When a custom format is used, it should be enclosed in single quotes. This value can be overridden with the -tsformat command-line option. See also DFORMAT and TFORMAT.

Examples

The following header line sets the field separator character to $ and disables quoting of character strings:

##ttBulkCp:FSEP=$:QUOTES=0

The following header line disables comments and sets the date format to the Oracle format:

##ttBulkCp:COMMENTCHAR=none:DFORMAT=Oracle

The following header line set the date format to a custom format:

##ttBulkCp:DFORMAT='Mon DD, YYYY'

Data line format

Data lines contain the row data of the table being copied. Each data line corresponds to a row of the table; rows cannot span input-file lines. A data line consists of a list of column values separated by the field separator character. Unnecessary whitespace characters should not be placed either before or after the field separator. The format of each value is determined by its type.

NULL values

NULL values can either be expressed as NULL (all capitals, no quotes) or as empty fields.

Character and unicode strings

CHAR, VARCHAR2, NCHAR, NVARCHAR2: If quoting of character strings is enabled (the default), then strings and characters must be enclosed in double quotes. If quoting of character strings is disabled, then any double-quote characters in the string are considered to be part of the string itself. ttBulkCp recognizes the following backslash-escapes inside a character string, regardless of whether quoting of strings is enabled:

• \" The double-quote character. If character-string quoting is enabled, then all double quote characters in the string must be escaped with a backslash. If character-string quoting is disabled, then it is permissible, but not necessary, to use the backslash.

• \t The tab character.

• \n The newline character.

• \r The carriage return character.

• \\ The backslash character.

• \xyz (CHAR and VARCHAR2 only) The character whose ASCII value is xyz, where xyz is a three-character octal number, as in \033.

• \uxyzw (NCHAR and NVARCHAR2 only) The character whose unicode value is xyzw, where xyzw is a four-digit hexadecimal number, as in\ufe4a. The \uxyzw notation is supported in both UTF-8 and ASCII encoding modes.

In addition, any of the ~ ! @ # $ % ^ & * ( ) = : ; | < > ? , / characters can be escaped with a backslash. Although it is unnecessary to escape these characters in most cases, doing so prevents them from being mistaken for a comment character or a field separator when character-string quoting is disabled.

If character-string quoting is enabled, the empty string (represented as " ") is distinct from NULL. If character-string quoting is disabled, then empty strings cannot be represented, as they cannot be distinguished from NULL.

For unicode strings, unicode characters encoded using UTF-8 multibyte sequences are supported in the UTF-8 encoding mode only. If these sequences are used with the ASCII encoding mode, ttBulkCp interprets each byte in the sequence as a separate character.

For fixed-length CHAR and NCHAR fields, strings that are shorter than the field length are padded with blanks. For VARCHAR2 and NVARCHAR2 fields, the string is entered into TimesTen exactly as given in the datafile. Trailing blanks are neither added nor removed.

Binary values

BINARY, VARBINARY: If quoting of character strings is enabled (the default), binary values are delimited by curly braces ({...}). If quoting of character strings is disabled, then curly braces should not be used. Whether or not character-string quoting is enabled, binary values may start with an optional 0x or 0X.

Each byte of binary data is expressed as two hexadecimal digits. For example, the four-byte binary string:

01101000 11001010 01001001 11101111

would be expressed as the eight-character hexadecimal string:

68CA49EF

Digits represented by the letters A through F can either be upper- or lower-case. The hexadecimal string cannot contain white spaces. Because each pair of characters in the hex string is converted to a single binary byte, the hex string must contain an even number of characters. For fixed-length binary fields, if the given value is shorter than the column length, the value is padded with zeros on the right. For varbinary values, the binary value is inserted into TimesTen exactly as given in the datafile.

If character-string quoting is enabled, a zero-length binary value (represented as { }) is distinct from NULL. If character-string quoting is disabled, then zero-length binary values cannot be represented, as they cannot be distinguished from NULL.

Integer values

TINYINT, SMALLINT, INTEGER, BIGINT: Integer values consist of an optional sign followed by one or more digits. Integer values may not use E-notation. Examples:

-14 98765 +186

Floating-point values

REAL, FLOAT, DOUBLE: Floating-point values can be expressed with or without decimal points and may use E-notation. Examples:

3.1415
-0.00004
1.1e-3
5e3
.56
-682
-.62E-4
170.

Fixed-point values

DECIMAL, NUMERIC: Decimal values can be expressed with or without decimal points. Decimal values may not use E-notation. Examples:

5
-19.5
-11
000
-.1234
45.
-57.0
0.8888

Inf, -Inf and NaN values

Inf, -Inf and Nan values: Infinity and Not a Number values can be represented as strings to represent the corresponding constant value (all are case insensitive):

TimesTen outputs the values as: NAN, INF and -Inf.

Date, time and timestamp values

Formats for date, time and timestamp values can be specified either by selecting a fixed datetime format or by defining a custom datetime format. The custom datetime formats are defined using format specifiers similar to those used by the TO_DATE and TO_CHAR SQL functions, as described in the following table.

In many cases, it is not necessary to define the timestamp format, even when a custom date or time format is used, because the default TimesTen format (DF*TF+FF) is defined in terms of the date and time formats. Therefore, setting the date format sets not only the format for date values, but also for the date portion of timestamp values. Similarly, setting the timestamp format affects both time values and the time portion of the timestamp values.

Fixed, date, time and timestamp formats

For date values, the fixed formats are

For time values, the only fixed format is ODBC:

For timestamp values, the fixed formats are:

The default timestamp value is: 'DF*TF+FF'

Examples

The following input file is for a table with five columns: two char columns, a double column, an integer column and a varbinary column. In the "Mountain View" line, the last three columns have NULL values.

##ttBulkCp
# This is a comment.
###### So is this.
# The following line is a blank line.

"New York","New York",-345.09,12,{12EF87A4E5}
"Milan","Italy",0,0,{0x458F}
"Paris","France",1.4E12,NULL,{F009}
"Tokyo","Japan",-4.5E-18,26,{0x00}
"Mountain View","California",,,

Here is an equivalent input file in which quotes are disabled, the comment character is '$' and the field separator is '|':

##ttBulkCp:QUOTES=0:COMMENTCHAR=$:FSEP=|
$ This is a comment.
$$$$$$ So is this.
$ The following line is a blank line.

New York|New York|-345.09|12|12EF87A4E5
Milan|Italy|0|0|0x458F
Paris|France|1.4E12|NULL|F009
Tokyo|Japan|-4.5E-18|26|0x00
Mountain View|California|||

The following command dumps the contents of table mytbl from data store mystore into a file called mytbl.dump.

ttBulkCp -o DSN=mystore mytbl mytbl.dump

The following command loads the rows listed in file mytbl.dump into a table called mytbl on data store mystore, placing any error messages into the file mytbl.err.

ttBulkCp -i -e mytbl.err DSN=mystore mytbl mytbl.dump

The above command terminates after the first error occurs. To force the copy to continue until the end of the input file (or a fatal error), use -m 0, as in:

ttBulkCp -i -e mytbl.err -m 0 DSN=mystore mytbl mytbl.dump

To ignore errors caused by constraint violations, use -d ignore:

ttBulkCp -i -e mytbl.err -d ignore DSN=mystore mytbl mytbl.dump

Notes

ttBulkCp explicitly sets the Overwrite data store attribute to 0, to prevent accidental destruction of a data store. For more information, see "Overwrite".

Real, float or double values may be rounded to zero when the floating point number is small.

When specifying date, time and timestamp formats, incomplete or redundant formats are not allowed in input mode. Specifiers that reference fields that are not present in the data type (for example a minute specifier in a date format) return errors in copy-out mode. In copy-in mode, the values of those specifiers are ignored.

The following caveats apply when disabling quoted strings in the ttBulkCp datafile:

• Empty strings and zero-length binary values cannot be expressed, as they cannot be distinguished from NULL.

• If the field separator character appears inside a character string, it must be escaped with a backslash or else it is treated as an actual field separator.

• If a data line begins with a character string and that string begins with the comment character, that character must be escaped with a backslash or else the line is treated as a comment. Setting the comment character to none can prevent this, as long as there are no actual comments in the file.

For UTF-8, NCHAR are converted to UTF-8 encoding and then output. UTF-8 input is converted to NCHAR.

For ASCII, those NCHAR values that correspond to ASCII characters are output as ASCII. For those NCHAR values outside of the ASCII range, the escaped Unicode format is used.

On Windows, this utility is supported for all TimesTen Data Manager and Client DSNs.

See also