tbLoader

This chapter describes the tbLoader utility and its usage.

Overview

tbLoader is a utility that loads a large amount of data to Tibero database at once. Instead of inserting each data to a database by writing SQL statements, you can load column data at once after converting it to a general text file. Therefore, it is useful when loading a large amount of data to Tibero database.

tbLoader allows users to easily create data files and reduces data loading time.

---

Quick Start

tbLoader is automatically installed and uninstalled along with Tibero.

The following shows how to execute tbLoader at a command prompt.

$ tbloader [options]

For information about the options, refer to “tbLoader Parameters”.

The following executes tbLoader.

Executing tbLoader

$ tbloader userid=db_user/db_password@default
control=sample.ctl data=sample.data direct=Y

---

I/O Files

tbLoader receives control and data files and outputs log and error files. Control and data files are written by users as input files, while log and error files are automatically created by tbLoader as output files. Since the input and output files in tbLoader are all text files, users can easily create input files.

This section describes the input and output files needed in tbLoader.

Control File

A control file defines parameters used to execute tbLoader. In this file, a user specifies the location of data to read, how to read the data, and the actual location of the data.

For information about control file options, refer to “Control File Options”.

Data File

A data file is a text file that contains database table data. Users can create and modify a data file by using a general text editor and save SQL query results in the file by using the SPOOL command in tbSQL.

User-specified data files are in either fixed or delimited record format.

Fixed Record Format

Fixed record format is used when a user specifies position information for all columns in the control file. Instead of using a delimiter, data values are read from user-specified positions. A column position is determined by the byte length. This format is less flexible than the delimited record format but it has better performance.

The record size or the end of line (EOL) character can be specified to separate each record. To improve performance, the system reads data from the column position specified in the control file, ignoring the ESCAPED BY and LINE STARTER parameter values.

A fixed record can only be used with the POSITION clause, not the FIELDS or LINE STARTED BY clause.

For information about clauses, refer to “Control File Options”.

• When using the LINES FIX clause The LINES FIX clause is used for fixed (in bytes) records. The record length must be specified in the control file (for example, LINES FIX 12). The LINES TERMINATED BY clause is ignored. The following is an example of fixed records.

example.ctl:
        LOAD DATA
        INFILE 'example.dat' 
        LOGFILE 'example.log' 
        BADFILE 'example.bad' 
        APPEND
        INTO TABLE EMP 
        LINES FIX 12
        (
                empno position (01:04),
                ename position (06:12)
        )
        
        example.dat:
        7922 MILLER 7777 KKS        7839 KING 7934 MILLER 7566 JONES

• When not using the LINES FIX clause When the record size is not specified in the control file, the EOL character ('\n') is used as a line separator. The following is an example of using the EOL character as a line separator

example.ctl:
    LOAD DATA
    INFILE 'example.dat' 
    LOGFILE 'example.log' 
    BADFILE 'example.bad' 
    APPEND
    INTO TABLE EMP 
    TRAILING NULLCOLS 
    (
        empno position (01:04),
        ename position (06:15),
        job   position (17:25),
        mgr   position (27:30),
        sal   position (32:39)
    )

example.dat:
    7922 MILLER	    CLERK	7782	920.00
    7777 KKS	    CHAIRMAN		
    7839 KING	    PRESIDENT		5500.0
    7934 MILLER	    CLERK	7782	920.00
    7566 JONES	    MANAGER	7839	3123.75
    7658 CHAN	    ANALYST	7566	3450.00
    7654 MARTIN	    SALESMAN	7698	1312.50

In the example.dat file, the second line does not have values in the last two columns. In this case, an error occurs if TRAILING NULLCOLS is not specified.

For more information about TRAILING NULLCOLS, refer to “TRAILING NULLCOLS”

Delimited Record Format

Delimited record format is used when position information is not specified for all columns in the control file. Each field is separated by FIELD TERMINATOR (field delimiter) and each record is separated by LINE TERMINATOR (record delimiter). This format has lower performance than the fixed record format but it is more flexible.

The following is an example of delimited records.

example.ctl: 
    LOAD DATA
    INFILE 'example.dat' 
    LOGFILE 'example.log' 
    BADFILE 'example.bad' 
    APPEND
    INTO TABLE emp
    FIELDS TERMINATED BY ',' 
            OPTIONALLY ENCLOSED BY '"' 
            ESCAPED BY '\\'
    LINES TERMINATED BY '\n' 
    (
        empno, 
        ename, 
        job, 
        mgr,
        hiredate, 
        sal, 
        comm, 
        deptno
    )

example.dat:
    7654, "Martin", "Sales",7698,1981/10/28,1312.50,3,10
    7782, "\,Clark","Manager" ,7839, 1981/01/11 ,2572.50,10,20
    7839, "King",President,,1981/11/17,5500.00,,10
    7934,"Miller","Clerk",7782 ,1977/10/12,920.00,,10
    7566, "Jones",Manager" ,7839, 1981/04/02,3123.75,,20
    7658, "Chan", Ana lyst, 7566,1982/05/03,3450,,20

Log File

A log file writes tbLoader execution process. This file provides users with basic metadata of the input columns and statistics about successful and failed input records. It also provides the cause of failed records analyzed by tbLoader.

Error File

An error file writes records that are failed to be loaded by tbLoader. Users can modify the records in this file and reload them.

---

Data Loading Methods

tbLoader loads data by using the following two methods.

• Conventional Path Load Default data loading method provided by tbLoader. It reads column data from user-specified data files, saves them in a column array, and loads them into the database server using batch update. It has lower performance than the direct path load but it can be used without any constraints.

• Direct Path Load Reads data from user-specified data files and loads the data to a column array so that the data matches the column data type. The column array data is converted to Tibero database blocks through a block formatter, and the converted blocks are directly written to Tibero database.

  It has better performance than the conventional path load but it has the following constraints.

  • The CHECK and referential key constraints are not checked.

  • The primary key, unique key, and NOT NULL constraints are checked.

  • The insert trigger does not work during data loading.

---

Constraints

The section describes the constraints of tbLoader.

When Delimiter Values Are Duplicate

If any of the FIELD TERMINATED, ENCLOSED BY, ESCAPED BY, and LINE TERMINATED BY parameter values are duplicate, data files cannot be read normally.

For example, if the FIELD TERMINATED BY and ENCLOSED BY parameters have the same value as follows, an error occurs.

FIELDS  TERMINATED BY '"' 
        OPTIONALLY ENCLOSED BY '"'

When ESCAPED BY Option Is Not Specified

If a character specified with ENCLOSED BY, FIELD TERMINATED BY, or LINE TERMINATED BY is used as an input field value in a data file as follows, ESCAPED BY must be specified.

For more information, refer to “FIELDS ESCAPED BY”.

example.ctl:
    FIELDS TERMINATED BY ',' 
    (ID, NAME)
    
example.dat:
    7654,Martin, Kim

When Table Owner and User Are Different

If the table owner and the user who is trying to load the table data are different, the user must have the LOCK ANY TABLE privilege to access the table in the DPL mode. The server policy is getting a table lock before lading data to prevent another session from executing DML on the table, which ensures high performance of DPL.

Use the following statement to grant the privilege.

grant lock any table to <user>;

If the user does not have the privilege, the following error occurs.

-17004: Permission denied

---

Whitespace Handling

This section describes how tbLoader handles whitespaces.

When a Field Value Contains Only Whitespace(s)

An input file's field value that corresponds to a column value of a record has only whitespace(s), tbLoader loads 0 or NULL depending on the data type of the input table column.

When a Field Value Contains Whitespace(s)

tbLoader treats blank space (' '), tab ('\t'), and EOL ('\n') characters as a whitespace unless the character is specified with the FIELD TERMINATED BY or LINE TERMINATED BY clause. Whitespaces can exist at the start and end of a field. Whitespaces in the middle of a field are treated as data.

tbLoader treats whitespace differently depending on the data file type.

Fixed Records

Whitespaces preceding a field value are treated as actual data, and whitespace following a field value are regarded as unnecessary and are truncated. This is because users can delete the leading whitespace but may add trailing whitespace for formatting purposes.

The following shows how whitespace are handled for a fixed record

" aaa \t" -> " aaa"

Delimited Records

All whitespaces preceding or following a field value are truncated because they are regarded as unnecessary. To add a whitespace as data, enclose the whitespace with the ENCLOSED BY string.

The following shows how whitespace are handled for a delimited record.

/" aaa \t" -> "aaa"

Specifying Whitespaces As Data

To specify whitespaces preceding or following a field value as data, use the “PRESERVE BLANKS” clause.

---

Advanced Functions

This section describes advanced functions of tbLoader.

Parallel DPL

If direct path load is used to transfer data, the target table is locked to prevent concurrent data loading into the same table. Parallel DPL can be used for concurrent data loading.

When using Parallel DPL, the Parallel DPL flag is set in data to load. A server receives the data in parallel and then merges and saves the data. To use Parallel DPL, set "direct" to Y and "parallel" to a value greater than or equal to 2 in tbloader.

The following example shows how to use Parallel DPL.

Using Parallel DPL in tbLoader

$ tbloader userid=db_user/db_password@default 
control=sample.ctl data=sample.data direct=Y parallel=2

Encrypting Access Information

tbLoader allows to encrypt database connection information (connect_string) by using an encryption file (wallet). To use this function, create an encryption file (wallet) that saves connection information first. For more information, refer to "Creating an Encryption File".

The following example shows how use an encryption file.

Using Encryption File (wallet) to Execute tbLoader

$ export LR_WALLET_PATH=./wallet_file.dat

$ tbloader control=sample.ctl data=sample.data

Currently, this function is supported only in UNIX with Open SSL installed.

Memory Protection

tbLoader provides memory protection. This function is enabled by default. To disable this function, set the following environment variable to N.

Disabling Memory Protection

$ export LD_MEM_PROTECT=N

Compressed Data Transmission

tbLoader provides data compression when transmitting data by using direct path load. To use this function, set the TBCLI_DPL_COMPRESSION_LEVEL environment variable to the compression level on the client where tbLoader executes.

The following describes how to set TBCLI_DPL_COMPRESSION_LEVEL.

Enabling Compressed Data Transmission on the Client

$ export TBCLI_DPL_COMPRESSION_LEVEL= -1

Compression Level	Description
0	No compression. (Default value)
1	Fast data compression.
9	High data compression.
-1	Recommended data compression.

To transmit compressed data to a server, _USE_DPL_MSG_COMPRESSION must be set in the tip file of the server.

Enabling Compressed Data Reception on the Server

_USE_DPL_MSG_COMPRESSION=Y (Default value: N)

---

Control File Options

This section describes how to specify control file options.

Users can specify the following options in a control file.

• Character set included in data files

• Byte ordering of data files

• Data file that contains data to load

• Log file that records events during data loading

• Error file that records data failed to be loaded

• Error number to exclude from the error file

• How to process existing data in a table (APPEND|REPLACE|TRUNCATE|MERGE)

• Field terminator and other options (TERMINATOR, ENCLOSED BY STRING, ESCAPED BY STRING)

• Start and end strings of a line

• Number of lines to ignore from a data file

• Options for a specific table column

The following is the control file format. The order of the options must be preserved. Items enclosed in square brackets ([ ]) are optional.

LOAD DATA
    [CHARACTERSET characterset_name] 
    [BYTEORDER endian_type]
    [INFILE data_file_name]
    [LOGFILE log_file_name] 
    [BADFILE bad_file_name] 
    [DISCARDFILE discard_file_name] 
    [SKIP_ERRORS error_number, ...]
    [APPEND|REPLACE|TRUNCATE|MERGE(column_name,	)]
    [PRESERVE BLANKS]
    INTO TABLE table_name
    [WHEN filter_expression [AND filter_expression	]]
    [MULTI INSERT INDEXES|FAST BUILD INDEXES]
    [FIELDS [TERMINATED BY field_terminator]
            [OPTIONALLY ENCLOSED BY enclosed_by_start_string
                                [AND enclosed_by_end_string]] 
            [ESCAPED BY escaped_by_string]]
    [LINES [FIX number]
        [STARTED BY line_start_string] 
        [TERMINATED BY line_terminator_string]]
    [TRAILING NULLCOLS]
    [IGNORE number LINES] 
    (column_name [FILLER]
                 [POSITION(from:to)]
                 [INTEGER EXTERNAL(size)	        |
                  FLOAT EXTERNAL(size)	                |
                  DOUBLE EXTERNAL(size)	                |
                  CHAR(size)	                        |
                  RAW(size)	                        |
                  DATE(size) date_fmt_string	        | 
                  TIMESTAMP(size) timestamp_fmt_string  | 
                  TIME(size) time_fmt_string]
                  [OUTFILE]
                  [CONSTANT constant_value] 
                  [NULL TERMINATED] 
                  [PRESERVE BLANKS]
                  [sql_expression]
                  [NULLIF cond_expression], ..........)
-- This line is comment.

The following describes each clause that can be used in the control file.

Clause	Description
CHARACTERSET	Specifies a character set.
BYTEORDER	Specifies the byte order of data.
INFILE	Specifies a data file (text file) that contains actual data.
LOGFILE	Specifies a log file that records logs recorded during data loading.
BADFILE	Specifies an error file that contains records failed to be loaded.
DISCARDFILE	Specifies a file that contains records omitted by a WHEN conditional clause
SKIP_ERRORS	Specifies error numbers that will not be recorded in an error file.
APPEND|REPLACE| TRUNCATE|MERGE	Specifies how to process data when a user-specified table has existing data.
PRESERVE BLANKS	Preserves blanks included in field data when saving the data in a database.
INTO TABLE	Specifies a target table to which data in a data file is loaded.
WHEN	Specifies a condition for fields of a data file to load.
MULTI INSERT INDEXES|FAST BUILD INDEXES	Specifies how to create indexes when loading data to a target table with direct path load.
FIELDS TERMINATED BY	Specifies a field terminator.
FIELDS OPTIONALLY ENCLOSED BY	Specifies a character string that encloses the start and end of a field when reading records from a data file.
FIELDS ESCAPED BY	Specifies a character string that enables to read a special character or character string.
LINES FIX	Specifies the length of a line in a data file.
LINES STARTED BY	Specifies a character string that indicates the start of a line.
LINES TERMINATED BY	Specifies a character string that indicates the end of a line.
TRAILING NULLCOLS	Treats data of the last column that does not exist in a data file as NULL.
IGNORE LINES	Ignores the specified number of lines from the start of a data file to load.
Table Column Attributes	Specifies columns of a table where a user enters data.

CHARACTERSET

Specifies a character set. Multiple character sets of data can be loaded to Tibero server. The specified character set affects the character set of control and data files. If no character set is specified, the TB_NLS_LANG value set in the client environment variable or MSWIN949 is used as the default character set for data files.

The default value for TB_NLS_LANG is as follows:

• Tibero 6 or earlier : MSWIN949

• Tibero 7 or later : UTF8

Syntax

CHARACTERSET characterset_name

Item	Description
characterset_name	Character set that is available for TB_NLS_LANG. (Default value: the default character set for the client)

Example

When a current client character set is KSC5601 (TB_NLS_LANG =EUCKR), add the following to the control file to load an MSWIN949 data file to the server.

CHARACTERSET MSWIN949

BYTEORDER

Specifies the byte order of data. It is required to read binary data.

Syntax

BYTEORDER endian_type

Item	Description
endian_type	Byte order of the data file if it is different from the order of the machine. Set to either BIG ENDIAN or LITTLE ENDIAN.

Example

BYTEORDER BIG ENDIAN

INFILE

Specifies the path and name of a data file (text file) that contains actual data. The path can be an absolute or relative path.

If the path is specified both at a command prompt and in the control file, the value specified at the command prompt is preferred.

Syntax

INFILE data_file_name

Item	Description
data_file_name	Data file path and name.

Example

INFILE '/home/test/data.dat' 
INFILE '../data.dat'

LOGFILE

Specifies the path and name of a log file that contains logs recorded during data loading. The path can be an absolute or relative path.

If the path is specified both at a command prompt and in the control file, the value specified at the command prompt is preferred.

Syntax

LOGFILE log_file_name

Item	Description
log_file_name	Log file path and name. (Default value: control_file_name.log)

Example

LOGFILE '/home/test/control.log' 
LOGFILE '../control.log'

BADFILE

Specifies the path and name of an error file that contains records failed to be loaded. The path can be an absolute or relative path.

If the path is specified both at a command prompt and in the control file, the value specified at the command prompt is preferred.

Syntax

BADFILE bad_file_name

Item	Description
bad_file_name	Error file path and name. (Default value: data_file_name.bad)

Example

BADFILE '/home/test/data.bad' 
BADFILE '../data.bad'

DISCARDFILE

Specifies the path and name of a file that contains records omitted by a WHEN conditional clause. The path can be an absolute or relative path.

If the path is specified both at a command prompt and in the control file, the value specified at the command prompt is preferred.

Syntax

DISCARDFILE discard_file_name

Item	Description
discard_file_name	File path and name.

Example

DISCARDFILE '/home/test/data.dsc' 
DISCARDFILE '../data.dsc'

SKIP_ERRORS

Specifies error numbers that will not be recorded in an error file.

Syntax

SKIP_ERRORS error_number, ...

Item	Description
error_number	Error numbers that will not be recorded in an error file.

Example

SKIP_ERRORS -10007
SKIP_ERRORS -10007, -10005

APPEND|REPLACE|TRUNCATE|MERGE

Specifies how to process data when a user-specified table has existing data.

Since REPLACE and TRUNCATE delete table records and then perform auto-commit, the data cannot be recovered after executing tbLoader.

The following is the details of processing the existing data.

Syntax

APPEND|REPLACE|TRUNCATE|MERGE(column_name, )

Item	Description
APPEND	Appends new data to the existing data. (Default value)
REPLACE	Deletes the existing data by using the DELETE command and adds new data. A user must have the permission for DELETE. If there is a trigger for DELETE, the trigger is executed. If the table has a referential integrity constraint, the constraint must be disabled before using DELETE.
TRUNCATE	Deletes the existing data by using the TRUNCATE command and adds new data. If the table has a referential integrity constraint, the constraint must be disabled before using TRUNCATE.
MERGE(column_name, .....)	Specifies a list of columns to merge. tbLoader uses the user-specified column list as key values. If new data has the same key value as the existing data, the new data overwrites the existing data. If there is no matching key, new data is inserted.

Example

control.ctl:
    LOAD DATA
    ... 
    REPLACE
    ... 
    (...)

control.ctl:
    LOAD DATA
    ...
    MERGE(EMPNO, EMPNM)
    ... 
    (...)

PRESERVE BLANKS

Preserves blanks included in field data when saving the data in a database.

Syntax

PRESERVE BLANKS

Example

control.ctl:
    LOAD DATA
    ...
    PRESERVE BLANKS
    ... 
    (...)

INTO TABLE

Specifies a target table to which data in a data file is loaded.

Syntax

INTO TABLE table_name

Item	Description
table_name	Target table name. PUBLIC synonym can be specified.

Example

control.ctl:
    LOAD DATA
    ...
    INTO TABLE EMP
    ... 
    (...)

WHEN

Specifies a condition for fields of a data file to load.

Syntax

WHEN filter_expression [AND filter_expression...]

filter_expression:
    {column_name|'('column_pos')'} operator {value_string|BLANKS}

Item	Description
column_name	Column name to compare.
column_pos	Column number to compare. The number can be greater than or equal to 1 and must be enclosed in parentheses.
operator	Comparison operator. Equality (=) and inequality (!=, <, >) operators are available.
value_string	Value to compare to. Can be a string enclosed in single quotation marks or the BLANKS keyword.

Example

control.ctl:
    LOAD DATA
    ...
    INTO TABLE EMP 
    WHEN C1 = '1'
    ... 
    (...)

MULTI INSERT INDEXES|FAST BUILD INDEXES

Specifies how to create indexes when loading data to a target table with direct path load. Either one of the following can be used.

• MULTI INSERT INDEXES Creates indexes for multiple records at once.

• FAST BUILD INDEXES Ignores existing indexes. Loads all data of a data file and then recreates indexes.

If the target table has a large amount of data when using tbLoader, use MULTI INSERT INDEXES. If not, use FAST BUILD INDEXES. Note that indexes can be in unusable status due to an issue such as data redundancy when using direct path load.

Syntax

[MULTI INSERT INDEXES|FAST BUILD INDEXES]

Item	Description
MULTI INSERT INDEXES	Creates indexes with the MULTI INSERT method.
FAST BUILD INDEXES	Creates indexes with the FAST BUILD method.

Example

control.ctl:
    LOAD DATA
    ...
    MULTI INSERT INDEXES
    ... 
    (...)

FIELDS TERMINATED BY

Specifies a field terminator. When reading records from a data file, each field is delimited by the specified field terminator.

Syntax

FIELDS TERMINATED BY field_terminator

Item	Description
field_terminator	ASCII character string.

Example

control.ctl:
    LOAD DATA
    ...
    FIELDS TERMINATED BY ','
    ... 
    (...)

FIELDS OPTIONALLY ENCLOSED BY

Specifies a character string that encloses the start and end of a field when reading records from a data file.

tbLoader treats a field value that includes a meta string, such as an empty character (' ', '\t', '\r', '\n'), a field terminator, and a line terminator, as a data value. It treats the ESCAPED BY string as a meta string even if the string is enclosed in the ENCLOSED BY string.

Note that if a field value contains a string that is the same as the ENCLOSED BY string, the ESCAPED BY string must be used as its prefix. Otherwise, the string is treated as the ENCLOSED BY string and the rest of the field value is not recognized.

Syntax

FIELDS OPTIONALLY ENCLOSED BY enclosed_by_start_string

Item	Description
enclosed_by_start_string	ASCII character string.

Example

The following shows when the start and end strings are the same.

control.ctl:
    LOAD DATA
    ...
    FIELDS OPTIONALLY ENCLOSED BY '"'
    ... 
    (...)

The following shows when the start and end strings are different.

control.ctl:
    LOAD DATA
    ...
    FIELDS OPTIONALLY ENCLOSED BY '{$' AND '$}'
    ... 
    (...)

The following shows when a parameter value is specified.

control.ctl:
    LOAD DATA
    ...
    FIELDS OPTIONALLY ENCLOSED BY '"' 
        ESCAPED BY '\\'
    ... 
    (...)

If a field value contains a string that is the same as the ENCLOSED BY string as follows, the ESCAPED BY string must be used as its prefix.

”quotation mark[\”]”,0001→(quotation 
mark["],0001)”quotation mark[”]”,0001→Causes an error 

FIELDS ESCAPED BY

Specifies a character string that enables to read a special character or character string. Use a double backslash (\) to specify a backslash ().

Syntax

FIELDS ESCAPED BY escaped_by_string

Item	Description
escaped_by_string	ASCII character string.

Example

FIELDS ESCAPED BY '\\'
FIELDS ESCAPED BY '$$!'

LINES FIX

Specifies the length of a line in a data file. The FIELDS, LINE TERMINATED BY, and LINE STARTED BY clauses cannot be used together. POSITION clause must be used together to distinguish columns. If POSITION is not specified, the default value is NULL.

Syntax

LINES FIX number

Item	Description
number	Integer. (Unit: bytes)

Example

control.ctl:
    LOAD DATA
    ...
    LINES FIX 5
    ... 
    (...)

Assume that a data file is as follows:

data.dat: 
abcdefghijklmnopqrst

tbLoader reads the data file as follows.

LINE #1: abcde
LINE #2: fghij
LINE #3: klmno
LINE #4: pqrst

LINES STARTED BY

Specifies a character string (prefix) that indicates the start of a line. Only data following the specified prefix are loaded. If a line does not contain the prefix, the entire line is ignored. For performance reasons, a prefix must consist of a sequence of the same character.

Syntax

LINES STARTED BY line_start_string

Item	Description
line_start_string	ASCII character string. Can be up to 30 bytes.

Example

Assume that LINES STARTED BY is set as follows:

control.ctl:
    LOAD DATA
    ...
    LINES STARTED BY '$$$'
    ... 
    (...)

Assume that a data file is as follows:

data.dat:
$$$0001,”SMITH”
…something … $$$0002,”DAVID”

tbLoader reads the data file as follows.

(0001, “SMITH”), (0002, “DAVID”)

LINES TERMINATED BY

Specifies a character string that indicates the end of a line.

Syntax

LINES TERMINATED BY line_terminator_string

Item	Description
line_terminator_string	ASCII character string. (Default value: '\n')

Example

The following example specifies the line_terminator_string as '|\n' consisting of two ASCII characters.

control.ctl:
    LOAD DATA
    ...
    LINES TERMINATED BY '|\n'
    ... 
    (...)

In Windows files, use '|\r\n' as the line terminator.

TRAILING NULLCOLS

Treats data of the last column that does not exist in a data file as NULL. If this clause is not specified, data of NULL column is treated as an error.

Syntax

TRAILING NULLCOLS

Example

The following example treats the job column values as NULL.

control.ctl:
    LOAD DATA
    ...
    TRAILING NULLCOLS
    ... 
    (
    id, 
    name, 
    job
    )

data.dat:
    10 SMITH

IGNORE LINES

Ignores as many lines as the specified number from the start of a data file to load. If a negative value is entered, as many lines as the specified number from the end of a data file are ignored.

Syntax

IGNORE number LINES

Item	Description
number	Integer. (Default value: 0)

Example

The following ignores the first line of a data file. When the first line indicates a column name, typically set to 1.

control.ctl:
    LOAD DATA
    ...
    IGNORE 1 LINES
    ...
    (...)

UNORDERED

Loads data in fixed record format when the data corresponding to each column in the control file is not positioned in ascending order in the data file.

Syntax

UNORDERED

Example

Specify this clause to avoid an error when the numbers entered after position are not in ascending order.

control.ctl: LOAD DATA ...
        UNORDERED ... ( c1 position (03:04), c2 position (01:02)
        )

Table Column Attributes

Specifies columns of a table where a user enters data. Note that the columns must be specified in the order they appear in a data file. Specify the column name of the target table in the column_name parameter.

The following is the details.

(column_name [FILLER]
             [POSITION(from:to)] 
             [INTEGER EXTERNAL(size)                          |
              FLOAT EXTERNAL(size)                            |
              DOUBLE EXTERNAL(size)                           |
              CHAR(size)                                      |
              RAW(size)                                       |
              DATE(size) date_fmt_string                      |
              TIMESTAMP(size) timestamp_fmt_string            | 
              TIME(size) time_fmt_string]
              [OUTFILE]
              [CONSTANT constant_value] 
              [NULL TERMINATED] 
              [PRESERVE BLANKS]
              [sql_expression]
              [NULLIF cond_expression],..........)

Clause	Description
FILLER	Specifies a column to be excluded from the data file.
POSITION	Specifies the start and end positions of a column in a line of a data file.
Data Types	Data types and buffer size.
OUTFILE	Specifies a non-data file from which data is read.
CONSTANT	Specifies the value of a specific column as a constant regardless of a data file value.
PRESERVE BLANKS	Preserves blanks included in a specified column.
sql expression	Specifies SQL expressions for a column value.
NULLIF	Conditional statement to replace a specific column value with NULL.

FILLER

Specifies a column to be excluded from the data file.

Syntax

FILLER

Example

For a fixed record, specify FILLER before the position clause.

control.ctl:
    LOAD DATA
    ... 
    (
        empno         position (01:04),
        ename         position (06:15), 
        job     filler position (17:25), 
        mgr           position (27:30),
        sal           position (32:39),
        comm          position (41:48),
        deptno        position (50:51)
)

For a delimited record, specify FILLER after the column name.

control.ctl:
    LOAD DATA
    ... 
    (
        empno, 
        ename,
        job filler, 
        mgr,
        sal, 
        comm, 
        deptno
    )

POSITION

Specifies the start and end positions of a column in a line of a data file.

Syntax

POSITION(from:to)

Item	Description
from	Start position of the column in the line. The line starts at 1.
to	End position of the column in the line.

Example

For a fixed record, specify the exact position along with the column list as follows:

control.ctl:
    LOAD DATA
    ... 
    (
        empno     position (01:04),
        ename     position (06:15),
        job       position (17:25),
        mgr       position (27:30),
        sal       position (32:39),
        comm      position (41:48),
        deptno    position (50:51)
    )

For a delimited record, the POSITION clause is not required.

control.ctl:
    LOAD DATA
    ... 
    (
        empno, 
        ename, 
        job, 
        mgr, 
        sal, 
        comm, 
        deptno
)

Data Types

tbLoader supports a set of certain data types. Default values and methods of binding column data vary by data type.

tbLoader provides the following data types.

• Numeric

• Character string

• Binary

• Datetime

Numeric

Loads character string data into numeric columns in Tibero. A NULL value is bound to 0 by default and then loaded into the database. To bind a NULL value, the column must be specified as a character string data type.

The following is the details.

Syntax

INTEGER EXTERNAL(size)|FLOAT EXTERNAL(size)|DOUBLE EXTERNAL(size)

Item	Description
INTEGER EXTERNAL(size)	Integer data.
FLOAT EXTERNAL(size)	Real data.
DOUBLE EXTERNAL(size)	Double-precision real data.

For more information about size, refer to "Data Buffer Size".

Character String

Loads character string data into character (CHAR, VARCHAR, CLOB, and NCLOB) columns in Tibero. A NULL value is bound to NULL by default and then loaded into the database. To bind 0 for a NULL value, the column must be specified as a numeric data type.

Syntax

CHAR(size)

Item	Description
CHAR(size)	Character string data. For more information about size, refer to "Data Buffer Size".

Binary

Loads binary data into large object (RAW, BLOB, and LONGRAW) columns in Tibero. A NULL value is bound to NULL by default and then loaded into the database.

Syntax

RAW(size)

Item	Description
RAW(size)	Binary data. For more information about size, refer to "Data Buffer Size".

Datetime

Loads character string data into date (DATE, TIME, and TIMESTAMP) columns in Tibero. A NULL value is bound to NULL by default and then loaded into the database.

Tibero client uses TB_NLS_DATE_FORMAT and TB_NLS_TIMESTAMP_FORMAT to specify DATE and TIMESTAMP type columns. You can also manually specify DATE, TIMESTAMP, and TIME columns in the control file of tbLoader. To manually specify a column, you must enclose the column in double quotation marks (" ").

Syntax

DATE date_fmt_string|TIMESTAMP timestamp_fmt_string|TIME time_fmt_string

Item	Description
date_fmt_string	DATE column
timestamp_fmt_string	TIMESTAMP column
time_fmt_string	TIME column

Example The following is a sample control file of tbLoader. This example specifies the date format, 'YYYYMMDD', for the hiredate column in the data.dat file

control.ctl:
LOAD DATA
.....
INFILE 'data.dat'
..... (
empno, ename,
hiredate DATE "YYYYMMDD"
)

data.dat:
1111, JOHN, 19981112
2222, TOM, 20070802

Data Buffer Size

tbLoader reads data from a data file and saves it in a data buffer. Then it converts the data into a Tibero server data type and loads it into the server.

To read delimited records, tbLoader uses database schema information to allocate an appropriate buffer size. To read fixed-length records, tbLoader uses the start and end values of the POSITION clause to allocate the size.

If the data length is known, you can specify the data buffer size. You can also specify the desired data buffer size in bytes to read large object data such as BLOB, CLOB, LONG, and LONG RAW.

Example The following control file allocates 5-byte data buffer for the empno column and 10-byte data buffer for the hiredate column.

control.ctl:
    LOAD DATA
    ....
    (
        empno INTEGER EXTERNAL(5),
        hiredate DATE(10) "YYYY/MM/DD"
    )

OUTFILE

Specifies a non-data file to read large object data (BLOB, CLOB, LONG, and LONG RAW type) or binary data (RAW). To use OUTFILE, you must specify the file path in a data file.

If OUTFILE is specified, tbLoader internally saves file data in a buffer and loads the data to a server multiple times. If not, tbLoader directly reads large object data from a data file and allocates a 32-KB data buffer by default. To read larger data, you can specify "Data Buffer Size".

Example Assume that a control file is as follows:

control.ctl:
    LOAD DATA
    .....
    INFILE 'data.dat'
    ..... 
    (
        empno, 
        ename,
        resume OUTFILE
    )

If a file path is set in the resume column of the data.dat file, tbLoader reads data from the file.

data.dat
1111, JOHN, ./clobdata.txt
2222, TOM, /home/test/clobdata.txt

CONSTANT

Specifies the value of a specific column as a constant regardless of a data file value. The constant must be enclosed in double quotation marks (" ") because it may include a blank character.

Example

When a control file is as follows, the empno column value is specified as 1234 regardless of the data value in the data.dat file.

control.ctl:
    LOAD DATA
    .....
    INFILE 'data.dat'
    ..... 
    (
        empno constant "1234", 
        ename
    )

Preserve Blanks

Preserves blanks included in a specified column. It is similar to “PRESERVE BLANKS”, but you can select columns.

Example When a control file is as follows, all blanks in the first field of the empno column are preserved.

control.ctl:
    LOAD DATA
    ... 
    (
        empno PRESERVE BLANKS,
        ename
    )

SQL Expression

Specifies SQL expressions for a column value. The SQL expression must be enclosed in double quotation marks (" "). Data can be bound to the column by using a colon (:) and the column name in the expression.

Example When a control file is as follows, the SYSDATE value is converted to a character string with the format of 'YYYYMMDD' by using the TO_CHAR function and then saved in the empno column. The empno column's field value from the data file is bound to the ename column.

control.ctl:
    LOAD DATA
    ...
    (
        empno "TO_CHAR(SYSDATE, 'YYYYMMDD')",
        ename ":empno"
    )

NULLIF

Specifies a conditional statement to replace a specific column value with NULL. The left-hand side of the conditional expression can be a column name or column number, and the right-hand side is enclosed in single quotation marks (' ') or can use the BLANKS keyword.

Syntax

NULLIF {column_name|'('column_pos')'} operator {value_string|BALNKS}

Item	Description
column_name	Column name to compare.
column_pos	Column number to compare. The number can be greater than or equal to 1 and must be enclosed in parentheses.
operator	Comparison operator. Equality (=) and inequality (!=, <, >) operators are available.
value_string	Value to compare to. Can be a string enclosed in single quotation marks or the BLANKS keyword.

Example When a control file is as follows, if the value of the empno column is 0, the ename column is replaced with NULL.

control.ctl:
    LOAD DATA
    ... 
    (
        empno CHAR,
        ename CHAR NULLIF empno = '0'
    )

Comments

Comments out a line of the control file.

Example

-- This is comment for control file.

---

tbLoader Parameters

This section describes tbLoader parameters that can be specified at the command line.

If a parameter is not specified when executing tbLoader, it can be entered at the command line. The following describes available command-line parameters.

$ tbloader 
tbLoader 7
TmaxData Corporation Copyright (c) 2008-. All rights reserved. 

Usage: tbloader [options] [controls]
Options:
    -h|--help        Display the more detailed information.
    -c|--charset     Display usable character sets in this version. 
    -v|--version     Display the version information.

Controls: 
userid              Username/password@dbname
control             The name of the control file 
data                The name of the data file 
log                 The name of the log file
bad                 The name of the bad file
discard             The name of the bad file
skip                The	count of line to skip in data file (Default: 0)
errors              The	count of error to allow	(Default: 50)
rows                The	count of row for each commit
                    (Default: commit after the end)
message             The count of records to process to print out (Default: 0)
readsize            The size of buffer to read from the data file 
                    (Default: 65536)
disable_idx	    Whether to disable indexes before loading (Default: N)
direct	            Whether to use the Direct Path Loading (Default: N)
dpl_log             Whether to log about the Direct Path Loading (Default: N) 
parallel            The count of threads to execute Parallel Direct Path Loading 
                    (Default: 1)
bindsize            The size of buffer to read in the Direct Path Loading 
                    (Default: 65536)
syntax              Control file syntax (Default : tibero)

dpl_parallel        Deprecated                                
multithread         Deprecated 

Example:
   tbloader userid=userid/passwd@dbname control=sample.ctl bindsize=1000000

The following describes each parameter.

Item	Description
userid	Tibero database user name and password and the database name in the following format: userid=userid/passwd@databasename
control	Path and name of the control file that contains parameter information. Both absolute and relative paths are available.
data	Path and name of the text file that contains actual data. Both absolute and relative paths are available. If the path is specified at the command prompt and in the control file, the former is used.
log	Path and name of the log file about data loading process. Both absolute and relative paths are available. (Default value: control_file_name.log) If the path is specified at the command prompt and in the control file, the former is used.
bad	Path and name of the file that records failed to be loaded. Both absolute and relative paths are available. (Default value: data_file_name.bad) If the path is specified at the command prompt and in the control file, the former is used.
discard	Path and name of the file that contains records omitted by a WHEN conditional clause. Both absolute and relative paths are available. If this parameter is not specified, omitted records are not recorded. If the path is specified at the command prompt and in the control file, the former is used.
skip	Number of lines to skip from the first line of a data file. (Default value: 0) Same as the control file option, “IGNORE LINES”.
errors	Maximum number of errors allowed when uploading data. (Default value: 50) tbLoader loads data until the number of errors reaches this number. The range of ERRORS is between -1 and 2147483647 (maximum integer value). The value must be -1, 0, or an integer. If ERRORS is set to 0, no errors are allowed. If ERRORS is set to a positive integer value, as many errors as the value are allowed. If the number of errors exceeds the value, data uploading is stopped. If ERRORS is set to -1, all errors are skipped and only error-free data are uploaded
rows	Number of records to commit when uploading a large amount of data. For the tbLoader performance, the actual number of records sent to the server may not be same as this value.
message	Number of logical records that tbLoader is currently processing. If not specified, the progress will not be displayed. Using a number that is too small may affect performance.
readsize	Size of a read-only buffer that tbLoader uses to read data files. (Unit: bytes, default value: 65536 (64 KB), maximum value: 2,097,152 (2 MB))
disable_idx	Option to disable all indexes of the target table before loading data. Y: Changes all indexes to UNUSABLE status. N: Does not change index status. (Default value)
direct	Option to use the direct path load method for data loading. Y: Uses direct path load. N: Uses conventional path load. (Default value)
dpl_log	Option to record logs in the log file of the server when loading data using direct path load. Y: Records logs. Allows recovery, but lowers loading performance. N: Does not record logs. Does not allow recovery. (Default value)
parallel	Number of threads used when loading data using direct path load. (Default value: 1) tbLoader uses as many thread(s) that read data and thread(s) that load the data to the server as the specified value in parallel.
bindsize	Size of the direct path stream used by a client when loading data using direct path load. The Tibero client does not upload the data to the server until the size of the bound data reaches this value. This parameter increases efficiency of uploading large amounts of data. (Unit: bytes, default value: 65536 (64 KB), maximum value: 10,485,760 (10 MB))
syntax	Control file syntax. Tibero: Uses a control file written with tbLoader. (Default value) Oracle: Uses a control file written with SQL*Loader.
dpl_parallel	No longer used.
multithread	No longer used.

At the command line, users can enter metadata required for a parameter or control file. Note that the userid, control, direct, message, readsize, bindsize, errors, and rows parameters can be specified only at the command line.

The -c option displays character sets that are available in the current version for backward compatibility.

The following shows that tbLoader parameters can be entered at the command line.

userid=userid/passwd@dbname

control=/home/test/control.ctl 
control=../control.ctl

log=/home/test/control.log 
log=../control.log

data=/home/test/data.dat 
data=../data.dat

bad=/home/test/data.bad 
bad=../data.bad

discard=/home/test/data.dsc 
discard=../data.dsc

skip=1 
direct=Y 
dpl_log=Y 
message=10000 
readsize=65536 
bindsize=65536 
errors=100
rows=100 
parallel=2

---

Usage Example

This section describes examples of loading delimited records, fixed records, and large object data (such as BLOB and CLOB).

1. Create a table (performed in all examples).

2. Create a control file.

3. Create a data file.

4. Execute tbLoader.

5. Check log and error files.

The following creates a common table. In this section, the database name is "default" and the table owner is loader/loader_pw.

CREATE TABLE MEMBER 
(
    ID           NUMBER(4) NOT NULL,
    NAME         VARCHAR2(10),
    JOB          VARCHAR2(9), 
    BIRTHDATE DATE,
    CITY         VARCHAR2(10), 
    PICTURE      BLOB,
    AGE          NUMBER(3),
    RESUME       CLOB
);

CREATE TABLE CLUB
(
    ID           NUMBER(6) NOT NULL,
    NAME         VARCHAR2(10),
    MASTERID     NUMBER(4)
);

Delimited Records

Use the convention path load method to load delimited records into Tibero server.

Creating a Control File

The following is the contents of the control file, control.ctl.

LOAD DATA
INFILE './data.dat' 
APPEND
INTO TABLE club
FIELDS TERMINATED BY ',' 
        OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '|\n'
IGNORE 1 LINES 
(
  id integer external, 
  name,
  masterid integer external
)

The control file includes the following information.

• Data file

  The data.dat file in the current directory.

• Log file

  If a log file name is not specified at the command prompt, the control file name is used as the log file name by default. In this example, control.log is created by default.

• Error file

  If an error file name is not specified at the command prompt, the data file name is used as the error file name by default. In this example, data.bad is created by default. However, the error file is not created if there are no failed records.

• Target table

  Data will be loaded into the club table's id, name, and masterid columns.

• FIELDS TERMINATED BY, FIELDS OPTIONALLY ENCLOSED BY, FIELDS ESCAPED BY, and LINES TERMINATED BY character strings

  Use a comma (,) for FIELD TERMINATED BY, double quotation marks (" ") for FIELDS OPTIONALLY ENCLOSED BY, and the newline character (|\n) for LINES TERMINATED BY. Since the FIELDS ESCAPED BY character string is not specified, the default value, a double backslash (\\), is used.

• Data to load

  The first line of the data file is ignored due to the IGNORE LINES clause.

Creating a Data File

The following is the contents of the data file, data.dat.

id        name        masterid|
111111,FC-SNIFER,2345|
dkkkkkkkkkk|
111112,"DOCTOR CLUBE ZZANG",2222|
111113,"ARTLOVE",3333|
111114,FINANCE,1235|
111115,"DANCE MANIA",2456|
111116,"MUHANZILZU",2378|
111117,"INT'L",5555

Executing tbLoader

Execute tbLoader.

tbloader userid=loader/loader_pw@default control=./control.ctl

Checking Log and Error Files

After executing tbLoader, check the log and error files.

The log file contains logs generated while executing tbLoader

tbLoader 7
TmaxData Corporation Copyright (c) 2008-. All rights reserved.

Data File : ./data.dat 
Bad File : ./data.bad

Table 'CLUB' was loaded from the data file.
Column Name        Position     DataType
-----------------  -----------  --------------
ID                      1	NUMERIC EXTERNAL
NAME                    2	CHARACTER
MASTERID                3	NUMERIC EXTERNAL

Record 2 was rejected.
    TBR-80053 : Some relatively positioned columns are not present in the record.
    
Record 3 was rejected.
    TBR-80025 : Column data exceeds data buffer size.
    
Record 6 was rejected.
    TBR-80025 : Column data exceeds data buffer size.
    
Table 'CLUB'
-----------------------------
8 Rows were requested to load.
5 Rows were loaded successfully.
3 Rows were failed to load because of some errors

Elapsed time was: 00:00:00.407089

The error file contains data that tbLoader failed to load. The failed data can be modified and reloaded into Tibero database.

dkkkkkkkkkk|
111112,"DOCTOR CLUBE ZZANG",2222|
111115,"DANCE MANIA",2456|

Fixed Records Terminated by EOL

Use the direct path load method to load fixed records into Tibero server.

Creating a Control File

The following is the contents of the control file, control.ctl.

LOAD DATA
INFILE '/home/test/data.dat' 
APPEND
INTO TABLE MEMBER 
(
    id         position (01:04) integer external,
    name       position (06:15),
    job        position (17:25),
    birthdate  position (27:36),
    city       position (38:47),
    age        position (49:51) integer external
)

The control file includes the following information.

• Data file

  The data.dat file in the /home/test directory.

• Log file

  If a log file name is not specified at the command prompt, the control file name is used as the log file name by default. In this example, control.log is created by default.

• Error file

  If an error file name is not specified at the command prompt, the data file name is used as the error file name by default. In this example, data.bad is created by default. However, the error file is not created if there are no failed records.

• Target table

  Data will be loaded into the member table's id, name, job, birthdate, city, and age columns.

• FIELDS TERMINATED BY, FIELDS OPTIONALLY ENCLOSED BY, FIELDS ESCAPED BY, and LINES TERMINATED BY character strings

  For fixed records, FIELDS TERMINATED BY, FIELDS OPTIONALLY ENCLOSED BY, and FIELDS

  ESCAPED BY character strings are not used. Since LINES FIX is not used, EOL is used for LINES TERMINATED BY.

• Data to load

  Data is loaded from the first line of the data file.

Creating a Data File

The following is the contents of the data file, data.dat.

Since tbLoader reads fixed records, the column data must be accurately positioned in the data file

7777	KKS	CHAIRMAN	1975-11-18	SEOUL	33
7839	KING	PRESIDEN		        DAEGU	45
7934	MILLER	CLERK	        1967-01-24	BUSAN	37
7566	JONES	MANAGER			
7499	ALLEN	SALESMAN	ddddddddd	KYUNG-JU	
aaaa7654 MARTIN SALESMAN
7648 CHAN         ANALYST       1979-10-11      INCHON  28

Executing tbLoader

Execute tbLoader.

tbloader userid=loader/loader_pw@default control=./control.ctl direct=Y

Checking Log and Error Files

After executing tbLoader, check the log and error files.

The log file contains logs generated while executing tbLoader.

tbLoader 7 
TmaxData Corporation Copyright (c) 2008-. All rights reserved.

Data File : ./data.dat 
Bad File : ./data.bad

Table 'MEMBER' was loaded from the data file.

Column Name            Position     DataType
-------------------    ---------    -----------------
ID                             1	NUMERIC EXTERNAL
NAME                           2	CHARACTER
JOB                            3	CHARACTER
BIRTHDATE                      4	DATE
CITY                           5	CHARACTER
AGE                            6	NUMERIC EXTERNAL

Record 4 was rejected.
    TBR-80053 : Some relatively positioned columns are not present in the record.
    
Record 5 was rejected.
    TBR-80053 : Some relatively positioned columns are not present in the record.
    
Record 6 was rejected.
    TBR-80053 : Some relatively positioned columns are not present in the record.
    
Table 'MEMBER'
---------------------
7 Rows were requested to load.
4 Rows were loaded successfully.
3 Rows were failed to load because of some errors

Elapsed time was: 00:00:00.338089

As shown in the previous log file, there are 3 records that failed to be loaded because their last column value does not exist. The last column value can be bound to NULL by using the “TRAILING NULLCOLS” clause.

The error file contains data that tbLoader failed to load. The failed data can be modified and reloaded into Tibero database.

7566     JONES    MANAGER
7499     ALLEN    SALESMAN ddddddddd KYUNG-JU
aaaa7654 MARTIN SALESMAN

Fixed Records

Use the conventional path load method to load fixed records into Tibero server.

Creating a Control File

The following is the contents of the control file, control.ctl.

LOAD DATA
INFILE './data.dat' 
APPEND
INTO TABLE MEMBER 
LINES FIX 51 
TRAILING NULLCOLS
(
    id         position (01:04) integer external,
    name       position (06:15),
    job        position (17:25),
    birthdate  position (27:36),
    city       position (38:47),
    age        position (49:50) integer external
)

The control file includes the following information.

• Data file

  The data.dat file in the current directory.

• Log file

  If a log file name is not specified at the command prompt, the control file name is used as the log file name by default. In this example, control.log is created by default.

• Error file

  If an error file name is not specified at the command prompt, the data file name is used as the error file name by default. In this example, data.bad is created by default. However, the error file is not created if there are no failed records.

• Target table

  Data will be loaded into the member table's id, name, job, birthdate, city, and age columns.

• FIELDS TERMINATED BY, FIELDS OPTIONALLY ENCLOSED BY, FIELDS ESCAPED BY, and LINES TERMINATED BY character strings

  For fixed records, FIELDS TERMINATED BY, FIELDS OPTIONALLY ENCLOSED BY, and FIELDS

  ESCAPED BY character strings are not used. Since LINES FIX is used, LINES TERMINATED BY is not used.

• Data to load

  Data is loaded from the first line of the data file.

• “TRAILING NULLCOLS” If a record value of the data file does not exist, bind the value to NULL.

Creating a Data File

The following is the contents of the data file, data.dat. Since tbLoader reads fixed records, the column data must be accurately positioned in the data file. Note that the following example separates records in multiple lines, but they must be written in a single line in actual use

7777	KKS	CHAIRMAN	1975-11-18	SEOUL	33
7839	KING	PRESIDENT	1963-04-13	DAEGU
7934	MILLER	CLERK	        1967-01-24	BUSAN	41
7566	JONES	MANAGER	        1955-08-20	        53
7499	ALLEN	SALESMAN	1977-12-28	
7648	CHAN	ANALYST	        1979-10-11      ULSAN	28

Executing tbLoader

Execute tbLoader.

tbloader userid=loader/loader_pw@default control=./control.ctl

Checking Log and Error Files

After executing tbLoader, check the log and error files.

The log file contains logs generated while executing tbLoader.

tbLoader 7
TmaxData Corporation Copyright (c) 2008-. All rights reserved.

Data File : ./data.dat 
Bad File : ./data.bad

Table 'MEMBER' was loaded from the data file.

Column Name        Position     DataType
--------------     ---------    ------------
ID                     1	NUMERIC EXTERNAL
NAME                   2	CHARACTER
JOB                    3	CHARACTER
BIRTHDATE              4	DATE
CITY                   5	CHARACTER
AGE                    6	NUMERIC EXTERNAL

Record 2 was rejected.
    TBR-5074: Given string does not represent a number in proper format.

Record 4 was rejected.
    TBR-5045: Only TIME format can be omitted.
    
Table 'MEMBER'
--------------------
6 Rows were requested to load.
4 Rows were loaded successfully.
2 Rows were failed to load because of some errors

Elapsed time was: 00:00:00.022404

The error file contains data that tbLoader failed to load. The failed data can be modified and reloaded into Tibero database.

7839 KING    PRESIDENT  1963-04-13    DAEGU
7566 JONES   MANAGER    1955-08-20    53

Large Object Data

Use the conventional path load method to load delimited records into Tibero server.

The following shows how to create an input file including a BOLB column for saving large binary data. The method of creating an input file including a CLOB column for saving large text data is similar to this method.

Creating a Control File

The following is the contents of the control file, control.ctl.

LOAD DATA
INFILE './data.dat' 
LOGFILE './logfile.log' 
BADFILE './badfile.bad' 
APPEND
INTO TABLE member
FIELDS  TERMINATED BY ',' 
        OPTIONALLY ENCLOSED BY '"' 
        ESCAPED BY '\\'
LINES TERMINATED BY '\n' 
TRAILING NULLCOLS
IGNORE 2 LINES 
(
        id integer external, 
        name,
        job, 
        birthdate, 
        city,
        age integer external, 
        picture outfile
)

The control file includes the following information.

• Data file

  The data.dat file in the current directory.

• Log file

  If a log file name is not specified at the command prompt, logfile.log is created in the current directory by default.

• Error file

  If an error file name is not specified at the command prompt, badfile.bad is created in the current directory by default. However, the error file is not created if there are no failed records.

• Target table

  Data will be loaded into the member table's id, name, job, birthdate, city, age, and picture columns.

• FIELDS TERMINATED BY, FIELDS OPTIONALLY ENCLOSED BY, FIELDS ESCAPED BY, and LINES TERMINATED BY character strings.

  Use a comma (,) for FIELD TERMINATED BY, double quotation marks (" ") for FIELDS OPTIONALLY ENCLOSED BY, a double backslash (\\) for FIELDS ESCAPED BY, and a newline character (\n) for LINES TERMINATED BY.

• Data to load

  The first two lines of the data file are ignored due to the IGNORE LINES clause.

• “TRAILING NULLCOLS” If a record value of the data file does not exist, bind the value to NULL.

Creating a Data File

The following is the contents of the data file, data.dat. To load binary data to a BLOB column, enter the path to the binary file.

Ignores the first line.
7782,"Clark","Manager",1981-01-11 , DAEGU,26,./blob.jpg 
7839,"King",President,1960/11/17,SEOUL,47 
7934,"Miller","Clerk",1977/10/12,BUSAN,30,./blob2.jpg 
7566,"Jones",Manager\, ,1981/04/02,31
7499, "Allen", "Salesman" a,1981:02/20,26 
7654, "Martin", "Sale smn", 1981/10/28,26
7658, "Chan",Ana lyst, 1982/05/03,25,25

Executing tbLoader

Execute tbLoader

tbloader userid=loader/loader_pw@default control=./control.ctl

Checking Log and Error Files

After executing tbLoader, check the log and error files.

The log file contains logs generated while executing tbLoader.

tbLoader 7
TmaxData Corporation Copyright (c) 2008-. All rights reserved.

Data File : ./data.dat 
Bad File : ./badfile.bad

Table 'MEMBER' was loaded from the data file.

Column Name       Position      DataType
--------------    ----------    ----------
ID		     1		NUMERIC EXTERNAL
NAME		     2		CHARACTER
JOB		     3		CHARACTER
BIRTHDATE	     4		DATE
CITY		     5		CHARACTER
AGE		     6		NUMERIC EXTERNAL
PICTURE		     7		RAW

Record 4 was rejected.
    TBR-80025 : Column data exceeds data buffer size.

Table 'MEMBER'
---------------------
6 Rows were requested to load.
5 Rows were loaded successfully.
1 Rows were failed to load because of some errors

Elapsed time was: 00:00:00.055475

The error file contains data that tbLoader failed to load. The failed data can be modified and reloaded into Tibero database

7499, "Allen", "Salesman" a,1981:02/20,26