ttBulkCp
ttBulkCp has two modes:
-
In copy-in mode (
ttBulkCp -i), rows are copied into an existing TimesTen table from one or more ASCII files (orstdin). -
In copy-out mode (
ttBulkCp -o), an entire TimesTen table is copied to a single ASCII output file (orstdout).
On UNIX and Linux systems, this utility is supported for TimesTen Data Manager DSNs. For Client DSNs, use the utility ttBulkCpCS.
Note:
Although cross-release compatibility over client/server protocol is supported in TimesTen, the tool ttBulkCpCS is not backward and forward release compatible; hence it can be used only for the same version client/server connections.
This utility only copies out the objects owned 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.
Usage in TimesTen Scaleout and TimesTen Classic
This utility is supported in both TimesTen Classic and TimesTen Scaleout.Syntax
ttBulkCp {-h | -help | -? | -helpfull}
ttBulkCp {-V | -version}
ttBulkCp -i [-cp numTrans | final] [-d errLevel]
[-e errorFile] [-m maxErrs] [-s c] [-t errLevel]
[-u errLevel] [-v 0|1] [-xp numRows | rollback]
[-Cc | -Cnone] [-tformat timeFormat] [-tsformat timeStampFormat]
[-dformat | -D dateFormat] [-F firstRow] [-L lastRow]
[-N ncharEncoding] [-Q 0|1] [-S errLevel] [-dateMode dateMode][-numThreads numthreads][-[no]tblLock] [-localOnly] {-connStr connection_string | DSN}
[owner.]tableName [dataFile ...]
ttBulkCp -directLoad [-cp numTrans|final] [-d errLevel] [-e errorFile]
[-m maxErrs] [-s c] [-t errLevel] [-u errLevel]
[-v 0|1] [-xp numRows|rollback] [-Cc | -Cnone]
[-dformat formatStr] [-tformat formatStr]
[-tsformat formatStr] [-F firstRow] [-L lastRow]
[-N ncharEncoding] [-Q 0|1] [-S errLevel] [-dateMode mode]
{DSN | [-connstr] connection_string}
[owner.]tblName [dataFile ...]
ttBulkCp -o [-s c] [-v 0|1] [-A 0|1] [-Cc | -Cnone]
[-nullFormat formatStr] [-localOnly]
[-tformat timeFormat] [-tsformat timeStampFormat]
[-dateMode dateMode] [-dformat | -D dateFormat]
[-N ncharEncoding] [-noForceSerializable | -forceSerializable]
[-tsprec precision] [-Q 0|1] [-localOnly]
{-connStr connection_string | DSN} [owner.]tblName
[dataFile]Options
ttBulkCp has the options:
| Option | Description |
|---|---|
|
|
|
|
|
An ODBC connection string that specifies a database location, driver, and optionally other connection attribute settings. |
|
|
Specifies an ODBC data source name of the database to be copied. |
|
|
Sets the date format. For a list of supported fixed values, see Fixed Date, Time and Timestamp Formats. This option overrides the See also: |
|
|
For copy-in mode, specifies the path name(s) of one or more For copy-out mode, specifies the path name of the file into which rows should be copied. If no file is given, the standard output is used. A single hyphen ( |
|
|
Specifies whether For copy-in mode, the default behavior for input is date. For copy-out mode, the default behavior for output is timestamp. TimesTen truncates the data and issues a warning if you select This option overrides the |
|
|
Selects copy-in mode that copies data from an ASCII file into a database table, but can only be used by a client using a direct connection. Avoids some of the overhead required when using a client/server connection, which provides better performance than the |
|
|
Prints a short usage message and exits. |
|
|
Prints a longer usage message and exits. |
|
|
Selects copy-in mode that copies data from an ASCII file into a database table. Can be used by a client using either a direct connection or a client/server connection. |
|
|
This option only loads rows from a specific instance. Load a specific instance in the grid and use this option. When you use this option, This option is only supported in TimesTen Scaleout. The default value is |
|
|
Specifies the input and output character encoding for |
|
|
Selects copy-out mode. |
|
|
Specifies the owner of the table to be saved or loaded. If owner is omitted, TimesTen looks for the table under the user's name and then under the user name |
|
|
Indicates whether character-string values should be enclosed in double quotes.
|
|
|
Sets the default field-separator character to |
|
|
Specifies the name of the table to be saved or loaded. This parameter is case-insensitive. |
|
|
Sets the time format. For a list of supported fixed values, see Fixed Date, Time and Timestamp Formats. The default value is See also: |
|
|
Sets the timestamp format. For a list of supported fixed values, see Fixed Date, Time and Timestamp Formats. The default value is See also: |
|
|
Prints the release number of |
|
|
Sets the verbosity level.
|
Use the following options in copy-out (-o) mode only. You must have SELECT privileges on the specified tables.
| Option | Description |
|---|---|
|
|
Indicates whether
|
|
|
The
If you specify the Warning: This output was produced using a non-serializable isolation level. It may therefore not reflect a transaction-consistent state of the table. For more information on isolation modes, see Transaction Isolation Levels in Oracle TimesTen In-Memory Database Operations Guide. |
|
|
Specifies the format in which
An empty LOB is printed as |
|
|
When used with the |
Use the following options in copy-in (-i) and directload (-directload) modes only. You must have INSERT privileges on the specified tables.
| Option | Description |
|---|---|
|
|
Sets the checkpoint policy for the copy in. A value of A nonzero value indicates that A value of The default value is Periodic checkpoints can only be enabled if periodic commits are also enabled. See the NOTE: This option is not supported in TimesTen Scaleout. |
|
|
By default,
Regardless of the setting of |
|
|
Indicates the name of the file in which |
|
|
Indicates the number of the first row that should be copied. Use this option (optionally with |
|
|
Indicates the number of the last row that should be copied. See the description of |
|
|
Specifies the maximum number of errors to report. The default is If set to |
|
|
By default,
|
|
|
By default,
|
|
|
Specifies whether to use table-level or row-level locking, when copying rows into a TimesTen table.
For a single input stream into a table, using |
|
|
By default,
|
|
|
Sets the transaction policy for the load. A value of A value of A nonzero value indicates that The default value is Use the |
Data File Format
This section describes the format the dataFile parameter.
Each line of a ttBulkCp input file is either 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
-Ccommand-line option or theCOMMENTCHARfile attribute (see File Attribute Line Format). The comment character must be the first character on the line. Comment lines are ignored byttBulkCp. Comments at the end of data lines are not supported. -
File attribute lines are used for setting file attributes that control the formatting of the data file. Attribute lines begin with the ten-character sequence
##ttBulkCp. The section File Attribute Line Format describes the full syntax for attribute lines. Attribute lines can appear anywhere in the data file. -
Data lines contain the rows of the table being copied. Data lines in the data file 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
-scommand-line option or theFSEPfile attribute. The section Data Line Format describes the full syntax for data lines.
File 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.
File 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.
Most command line options take precedence over the values in the file attributes that are supported by ttBulkCp. The CHARACTERSET attribute is the only file attribute that overrides command line options.
The file attributes are:
-
CHARACTERSET: Specifies the character set to be used to interpret the data file. If the file attribute is not set, the character set used to interpret the file is the one specified in theConnectionCharacterSetconnection attribute. For best performance, the value of theDatabaseCharacterSetconnection attribute should match either theConnectionCharacterSetconnection attribute or this file attribute. If the character set supplied inConnectionCharacterSetconnection attribute or in this file attribute is different than the actual character set of the file,ttBulkCpmay interpret data incorrectly. -
VERSION: Specifies the version of the file format used in the file, expressed asmajor.minor. The only supported version is 1.0. -
DATEMODE: Specifies whether an Oracle databaseDATEtype is specified as 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-Qoption. -
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 data file.
-
DFORMAT: Sets the date format. For a list of supported values, see Fixed Date, Time and Timestamp Formats. When a custom format is used, it should be enclosed in single quotes. This value can be overridden with the-D/-dformatcommand-line option. See also:TFORMATandTSFORMAT. -
NCHARENCODING: Indicates the encoding to be used for theNCHARandNVARCHAR2data types. The value may be eitherASCIIorUTF-8. -
TFORMAT: Indicates the time format. For a list of supported values, see Fixed Date, Time and Timestamp Formats. When a custom format is used, it should be enclosed in single quotes. This value can be overridden with the-tformatcommand-line option. See also:DFORMATandTSFORMAT. -
TSFORMAT: Sets the timestamp format. For a list of supported values, see Fixed Date, Time and Timestamp Formats. When a custom format is used, it should be enclosed in single quotes. This value can be overridden with the-tsformatcommand-line option. See also:DFORMATandTFORMAT.
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, CLOB, NCLOB: 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. -
\tThe tab character. -
\nThe newline character. -
\rThe carriage return character. -
\\The backslash character. -
\xyz(CHARandVARCHAR2only) The character whoseASCIIvalue isxyz, wherexyzis a three-character octal number, as in\033. -
\
uxyzw(NCHARandNVARCHAR2only) The character whose unicode value isxyzw, wherexyzwis a four-digit hexadecimal number, as in\ufe4a. The\uxyzwnotation is supported in bothUTF-8andASCIIencoding modes.
In addition, any of the ~ ! @ # $ % ^ & * ( ) = : ; | < > ? , / characters can be escaped with a backslash. Although it is unnecessary to escape these characters usually, 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 data file. Trailing blanks are neither added nor removed.
Binary Values
BINARY, VARBINARY, BLOB: 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 character-string quoting is enabled or disabled, 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 hexadecimal string is converted to a single binary byte, the hexadecimal 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 data file.
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):
| String | Value |
|---|---|
|
|
|
|
|
|
|
|
|
TimesTen outputs the values as: NAN, INF and -Inf.
Fixed Date, Time and Timestamp Formats
For date values, the fixed formats are:
| Format | Description |
|---|---|
|
ODBC |
Example: (default value) |
|
Oracle |
Example: |
|
SYBASE1 |
Example: |
|
SYBASE2 |
Example: |
|
SYBASE3 |
Example: |
For time values, the only fixed format is ODBC:
| Format | Description |
|---|---|
|
ODBC |
Example: |
For timestamp values, the fixed formats are:
| Format | Description |
|---|---|
|
ODBC |
Example: |
|
Oracle |
Example: |
|
SYBASE1 |
Example: |
|
SYBASE2 |
Example: |
|
SYBASE3 |
Example: J |
The default timestamp value is: 'DF*TF+FF'
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.
| Specifier | Descriptions and restrictions |
|---|---|
|
|
Quarter. Cannot be used in copy-in mode. |
|
|
Year (four digits). |
|
|
Year (with comma as shown). |
|
|
Year (last three digits). Cannot be used in copy-in mode. |
|
|
Year (last digit). Cannot be used in copy-in mode. |
|
|
Month (full name, blank-padded to 9 characters, case-insensitive). |
|
|
Month (three character prefix, case-insensitive). |
|
|
Month (01 through 12). |
|
|
Day of the month (01 through 31). |
|
|
Hour (00 through 23). |
|
|
Hour (01 through 12). Must be used with AM/PM for copy-in mode. |
|
|
Hour (01 through 12). Must be used with AM/PM for copy-in mode. |
|
|
Minute (00 through 59). |
|
|
Second (00 through 59). |
|
|
Fractional seconds.Six digits, unless overridden with the |
|
|
Fractional seconds (number of digits specified by n). |
|
|
In copy-in mode, matches, optional decimal point plus one or more fractional seconds. In copy-out mode, same as |
|
|
In copy-in mode, same as |
|
|
Meridian indicator without dots. In copy-in mode, this must be used with |
|
A.M. P.M. |
Meridian indicator with dots. In copy-in mode, this must be used with |
|
|
Current date format (can only be used in timestamp format). |
|
|
Current time format (can only be used in timestamp format). |
|
|
Punctuation that are matched in copy-in mode or output in copy-out mode. |
|
" |
Text that is matched in input mode or output in copy-out mode. |
|
|
Matches 0 or more whitespace characters (space or tab) in copy-in mode or outputs 1 space in copy-out mode. |
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 database mystore into a file called mytbl.dump.
% ttBulkCp -o mystore mytbl mytbl.dump
The following command loads the rows listed in file mytbl.dump into a table called mytbl on database mystore, placing any error messages into the file mytbl.err.
% ttBulkCp -i -e mytbl.err 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 irrecoverable error), use -m 0, as in:
% ttBulkCp -i -e mytbl.err -m 0 mystore mytbl mytbl.dump
To ignore errors caused by constraint violations, use -d ignore, as follows.
% ttBulkCp -i -e mytbl.err -d ignore mystore mytbl mytbl.dump
Notes
ttBulkCp explicitly sets the Overwrite connection attribute to 0, to prevent accidental destruction of a database. For more information, see Overwrite.
Real, float or double values may be rounded to zero when the floating point number is small.
The connection attribute PassThrough with a nonzero value is not supported in this utility and returns an error.
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 data file:
-
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. If there are no actual comments in the file, set the comment character to
noneto avoid characters from being misread as comment characters.
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.
This utility is for use specifically with TimesTen tables. It is not supported with passthrough to an Oracle database.
On Windows, this utility is supported for all TimesTen Data Manager and Client DSNs.
It is recommended that you do not run DDL SQL commands while running ttBulkCp to avoid lock contention issues for your application.