2. Using iLoader#

This chapter explains how to use iLoader.

The various options provided by Altibase can be used with iLoader. iLoader can run in batch mode or in interactive mode. Command-line options are used when running iLoader in batch mode.

Command-Line Options#

The command-line options supported by Altibase include general options and options for improving performance.

General Options
Peformance Options

The general options and performance options are all listed below. They are explained in the next section.

iloader [-h]
    [-s server_name] [-u user_name] [-p password]
    [-port port_no] [-silent] [-nst] [-displayquery]
    [-NLS_USE nls_name]
    [-prefer_ipv6]
    [-geom WKB]
    [-ssl_ca CA_file_path | -ssl_capath CA_dir_path]
    [-ssl_cert certificate_file_path]
    [-ssl_key key_file_path]
    [-ssl_verify]
    [-ssl_cipher cipher_list]
    [{ in | out | formout | structout | help }
        [-d datafile or datafiles] [-f formatfile]
        [-T table_name] [-F firstrow] [-L lastrow]
        [-t field_term] [-r row_term] [-mode mode_type]
        [-commit commit_unit] [-bad badfile]
        [-log logfile] [-e enclosing] [-array count]
        [-replication true/false] [-split number]
        [-readsize size] [-errors count]
        [-lob lob_option_string] [-atomic]
        [-parallel count] [-direct [log|nolog]]
        [-rule csv]
        [-partition]
        [-dry-run]
        [-prefetch_rows]
        [-async_prefetch off|on|auto]
        [-geom WKB]
        [-lightmode]
        [-verbose]]

Note#

Double quotation marks should be used if the user name contains special characters or spaces.

$ iloader -U "user name"

General Options#

iLoader is run with the following options. Where applicable, default values are shown.

Factor	Description
-S\|-s servername	The host name of the server. This option specifies the name(or IP address) of the computer on which the Altibase server is running. If connection is attempted while the ISQL CONNECTION environment variable is set to IPC or UNIX, and the remote server is specified for this option, iSQL ignores the ISQL CONNECTION specification and connects to the remote server via TCP, and outputs a warning message that the ISQL CONNECTION specification has been ignored. It can be a host name, an IPv4 address, or an IPv6 address. An IPv6 address must be enclosed by a left square bracket([) and a right square bracket(]). For example, in the case of localhost (meaning this computer), localhost can be specified as the host name, 127.0.0.1 as the IPv4 address, or [::1] as the IPv6 address. For more information about the IPv6 address notation, please refer to the Altibase Administrator's Manual.
-U\|-u login_id	The user ID
-P\|-p password	The user password
-PORT port_no	Specifies the port number for connecting via TCP/IP or IPC. However, when connecting in a Unix environment via IPC, this option must not be specified. On specification, a warning message is output. After a warning message is output, connection to the server is made. To connect via TCP, first set 'ISQL_CONNECTION=TCP' on the client and then enter the PORT_NO. If the environment variable ISQL_CONNECTION is not set to IPC and the -PORT option is omitted, the port number will be checked for first in the environment variable ALTIBASE_PORT_NO and then in the PORT_NO property in altibase.properties, and if it is not set in either of those places, a prompt to enter it will be raised.
-NLS_USE charset	This is the character set displayed to the user when selecting data. Specifies the encoding on the terminal from which iSQL is run. US7ASCII KO16KSC5601 MS949 BIG5 GB231280 MS936 UTF8 SHIFTJIS MS932 EUCJP On omission, the environment variables, ALTIBASE_NLS_USE and altibase.properties are referenced in turn. If no value is set, the default character set US7ASCII is used.
-prefer_ipv6	This option determines the IP address to be connected first when a host name is given for the -s option. If this option is specified and a host name is given for the -s option, this means that resolving the host name to the IPv6 address is prefered. If this option is omitted, iloader connects to the IPv4 address by default. If it fails to connect to the prefered IP version address, an attempt is made to connect using the other IP version address. For example, when localhost is given for the -s option and this option is specified, iloder first tries to connect to the [::1] IPv6 address. If this attempt fails, iloder proceeds to connect to the 127.0.0.1 IPv4 address.
-ssl_ca CA_file_path	Specifies the location of the certification authority (CA) certificate in which the public key of the Altibase server to be connected to is incorporated.
-ssl_capath CA_dir_path	Specifies the directory under which the certification authority (CA) certificate in which the public key of the Altibase server to be connected is incorporated.
-ssl_cert certificate_file_path	Specifies the location of the client authentication file.
-ssl_key key_file_path	Specifies the location of the client private key file.
-ssl_verify	Verifies the certificate the client receives from the server.
-ssl_cipher cipher_list	Specifies a cipher list for SSL encryption. Please refer to the SSL_CIPHER_LIST property in the General Reference.
in \| out \| formout \| structout \| help	Sets the direction in which to copy data (it is essential that only one is set) • in: Copy from a file to a database table. • out: Copy from a database table to a file. • formout: Make a table format file (FORM file). • structout: Performs a function similar to that of formout. Used to create a structure matching that of a given table. (Used when creating a client application.) • help: Explain how to use help.
-T table_name	The name of the table to be copied. If you use double quotation marks to enclose this, this is case-sensitive when you execute iLoader in interactive mode. Since the table name is already written in the FORM file, this option is ignored when uploading or downloading data.
-d datafile(datafiles)	The full path of the data file used when copying data from the database to the file or vice versa. This is not used with `formout`. `in` Mode: - Up to 32 file names can be specified, and files are uploaded in the listed order. - Each file can upload a maximum of 2,147,483,647 records. If this limit is exceeded, the excess records are ignored, and the upload completes. However, the record count in the log file may not be accurate. `out` Mode: - Each file can normally contain up to 2,147,483,647 records for download. If this limit is exceeded, the data can still be downloaded, but the log file may show inaccurate record counts. - With the parallel option, at least as many files as the number specified in the parallel option are created.
-rule csv	This applies CSV format rules to the data to be uploaded or downloaded. These options cannot be used together with delimiter-related options such as -f, -t, -r, and -e.
-f formatfile	The full path of formatfile, created by a previous call to iLoader.
-F firstrow	The line number of the first row to be copied (default is 1). This option is meaningful only when uploading data.
-L lastrow	The line number of the last row to be copied (default is the line number of the last row). This option is meaningful only when uploading data.
-t field_term	The delimiter between fields. The default is '^'. %t signifies a tab character, %n signifies a newline character and %r signifies a carriage return. The -t, -r and -e options must be different from each other. e.g., -t ^%t It is inadvisable to use characters that are interpreted on the command line, such as: ' " / & as delimiters. When using delimiters (for example "#") at the iLoader prompt, they can be entered as -t # or "#". At a general command prompt, they can be entered as -t #, '#' or "#".
-r row_term	row terminator (default is '\n '.) The details are the same as for the -t option.
-e enclosing	Block delimiter for enclosing fields. The details are the same as for the -t option.
-lob lob_option_string	Because the maximum allowable size of LOB data is 4GB, problems may arise when LOB data larger than 2GB are saved on 32-bit operating systems. Therefore, this option can be used to specify how to handle LOB type data. If this option is not specified, LOB columns will be handled just like other columns.
-replication true/false	Option to turn off replication and load data. (If omitted, true applies.)
-mode mode_type	APPEND: This adds data to existing tables. (default) REPLACE: This uses the DELETE statement to delete all data from the existing table and then populate the table with the new records. TRUNCATE: Similar to REPLACE, this deletes all data from the existing table and then populates the table with the new records, however it uses the TRUNCATE statement to do so.
-bad badfile	If one or more errors occur during an upload operation, this option saves rows that were not uploaded in badfile. Not saved if not specified. If stdout or sterr (must be in lowercase) is specified for this option, no file is created. Instead, the rows are output to stdout (standard out) or stderr (standard error).
-log logfile	Maintains a record of iLoader execution while iLoader is running. The start time, end time, number of target rows, number of processed rows, number of erroneous rows, and error details are recorded here. It will not be saved unless specified. If stdout or sterr (must be in lowercase) is specified for this option, no file is created. Instead, the log information is output to stdout (standard out) or stderr (standard error). This option can be used with the -verbose option.
-split n	Specifies the number of records to copy to each file (only meaningful when used with the -out option). After the command is executed, multiple backup files, each storing a number of records equal to n and having the names datefile.dat0, datafile.dat1, etc... will have been created.
-errors count	This specifies the maximum number of allowable errors when iLoader is executed with the -in option. If the number of errors exceeds the number specified here, execution terminates. If this option is omitted, the default is 50. If this value is set to 0, execution continues regardless of the number of errors. The number of errors occurring during the uploading operation may exceed the number specified here. When this option is used in conjunction with the -parallel option, if the number of errors exceeds the specified value for one of multiple threads executing in parallel, all threads are terminated.
-partition	If the table specified using the -T option is a partitioned table, a number of FORM files equal to the number of partitions in the table will be generated. The name of each FORM file will have this structure: [formfile_name.partition_name]. If the specified table is not a partitioned table, one FORM file, named formfile_name, will be generated.
-verbose	In case of an upload error, the position of the column(COLUMN_ORDER) where the error occurred is recorded in the log file specified by the -log option. However, if the position cannot be determined, it is not recorded. To use this option, the -log option must be set.

If the -S, -U and -P command-line options are omitted, the user will be prompted to enter the values of these options manually at the time of execution.

iSQL> CREATE TABLE test (i1 INTEGER);
Create success.

$ iloader formout -T test -f test.fmt
-------------------------------------------------------
     Altibase Data Load/Download utility.
     Release Version 7.1.0.1
     Copyright 2000, Altibase Corporation or its subsidiaries.
     All Rights Reserved.
-------------------------------------------------------
Write Server Name (default:localhost) : 
Write UserID : sys
Write Password : 
ISQL_CONNECTION : TCP

If "iloader" is entered without any options at a shell prompt, the iLoader> prompt appears, and work can be conducted in interactive mode.
All of the options described above are case-sensitive except for -S, -U, and -P.
The -in | -out | -formout option must be entered before any of the other command options.
For more detailed information about SSL connection, please refer to Chapter 2. Connecting and Disconnecting in the iSQL User's Manual.

CSV Format Rules and -rule csv Option

iLoader processes data according to the CSV (comma-separated values) format rules when the delimiter option is not specified. The CSV format is a text-based data format where fields or columns are separated by commas and rows are separated by line breaks. Data formatted according to CSV format rules undergoes the following transformation:
Before: John 027551234 Seoul
After: "John",027551234,"Seoul"
Following CSV format rules, data is correctly formatted even if it contains commas or double quotation marks. Therefore, users do not need to consider the content of the data. However, it's important to note that when using the -rule csv option, the options to specify delimiters manually (-f, -t, -r, and -e) cannot be used.

Limitations of Options#

Field delimiters, row delimiters, and block delimiters cannot be subsets of each other. In other words, the field delimiter must not contain the row delimiter, and the row delimiter must not contain the field delimiter. Moreover, column values cannot contain these delimiters.
For example, if the field delimiter is '-' and the row delimiter is '-#', a row having 3 columns would be displayed as follows:

Honggildong-027551234-Seoul-#

In this case, the third '-' would be recognized as another field delimiter, so the row would be handled as though it had 4 columns and no row delimiter.

In the case below, although the field delimiter, which is '-', is not a subset of the row delimiter, which is '##', the second and third dashes ('-'), which are part of the data in the second column, are erroneously recognized as field delimiters.

Honggildong-02-755-1234-Seoul##

Therefore, the delimiters must be selected carefully because of the possibility that they will be incorrectly handled.

Note#

Double quotation marks should be used if the table name contains special characters or spaces.

iloader> -T "table name"
$ -T "table name"

Use double quotation marks if the file name contains special characters or spaces.

iloader> -f "fmt file.fmt" -d "dat file.dat"
$ -f "fmt file.fmt" -d "dat file.dat"

LOB Option#

The user can specify how LOB columns are handled by entering lob_option_string after the - lob option. To specify multiple keywords (see below), use the -lob option repeatedly on the same line, each -lob option being followed by its own lob_option_string, enclosed in double quotation marks.

The keywords for lob_option_string are as follows:

use_lob_file, lob_file_size, use_separate_files, lob_indicator

use_lob_file#

use_lob_file = {yes|no}

Determines whether LOB data expressions refer to external files (LOB files).

Either value can be used when iLoader is copying data in either direction ("in" or "out").

For information on LOB file naming conventions, please refer to the description of the lob_file_size option.

lob_file_size#

lob_file_size = file_size

Specifies the maximum size of a LOB file. If the use_lob_file option is set to yes and this option is not specified, the size of the external file is only limited by the operating system's file size limit. When the file size is limited by the operating system or the platform, it is recommended that this option be set to 2 GB or less on platforms on which the long data type is 4 bytes.

This option is applicable only when the iLoader data copy direction is "out". iLoader ignores this option when the data copy direction is "in". When the data copy direction is "in", the size of the first LOB data file is taken as lob_file_size.

If this option is set without the use_lob_file option being specified, it is inferred that use_lob_file = yes.

If the size of LOB data to be dumped exceeds the size specified in this option, a new file is created, and the data dump continues.

The file size is represented in 'number + unit' format. The number can be represented to one decimal place. The number cannot be 0.

The units that can be used for the file size are "T" and "G". "T" indicates terabytes and "G" indicates gigabytes. If no units are specified, gigabytes are used by default.

The name of each LOB file is determined by removing the filename extension from the original data file name and appending _serialNumber.lob thereto. The serial number has 9 digits. If the number of digits in the serial number is less than 9, the leftmost positions of the serial number are filled with 0's (zeroes). The serial numbers start at 1. If a data file is split into several files using the -split option, a data file number is appended to each LOB file name.

For example, if the option -d dump.dat -lob "lob_file_size-uG". is specified, the following LOB files are created:

Total amount of LOB data: T GB

Size of each resulting file: u GB

dump_000000001.lob (u Gbytes)
dump_000000002.lob (u Gbytes)
...
dump_n.lob (T - un GBytes)
where n = int(T / u), preceded by 0

Use_separate_files#

use_separate_files = {yes | no}

When processing LOB files, this option determines whether to save each LOB data item (i.e. contents of one cell) in a separate file. This option assumes that use_lob_file is set to yes.

If this option is set to yes and lob_file_size is also specified, an error occurs.

When this option is set, folders having the same names as tables and columns are created and LOB data from these columns are saved in data files in numeric order according to row. The row numbers start from 1 and have 9 digits. If the number of digits in the row number is less than 9, the leftmost positions of the row number are filled with 0's (zeroes).

For example, if columns C1 and C3 of table t1, which has 3 columns, are LOB type columns, and -d d.dat -lob "use_separate_files=yes" is specified, the following LOB files are created.

t1/C1/000000001.lob
t1/C1/000000002.lob
t1/C3/000000002.lob
t1/C3/000000002.lob

When this option is set, the path of the LOB file is written in the corresponding data cell in the LOB column in the data file. However, if the LOB data are NULL, the address of the LOB file isn't written, and no LOB file is created.

Note#

When downloading data using -lob "use_separate_files=yes", the directory, in which LOB files is located, which is written in a data file is represented relative to the path given with -d option. But, if you change the relative path to the absolute path manually in the data file and upload it, the file path set specified by -d option will be ignored.

In other words, if an absolute path is represented for the directory in which LOB files is located, iLoader utility uses the absolute path. On the contrary, if a relative path is represented for the directory, iLoader utility uses the path made by attaching the relative path in the data file to the path given with -d option.

lob_indicator#

lob_indicator = char_sequence

This option specifies a block delimiter that denotes a pointer for indicating the offset and size of a LOB file.

It can be used regardless of whether the data copy direction in iLoader is in or out.

The default value is %%. Up to 10 characters can be used.

However, %t, %n and %r are each regarded as one character. %t indicates a tab, %n indicates a newline character and %r indicates a carriage return.

This option cannot have the same value specified for the -t, -r or -e options.

It is recommended that characters that are interpreted on the command line, such as: ' " & not be used as delimiters

It is assumed that use_lob_file = yes if it is not specified.

When the data copy direction in iLoader is in and use_lob_file = yes, if a LOB column in a data file does not start with the character sequence specified by lob_indicator, the data row containing that LOB column is treated as an erroneous row.

Examples#

To dump LOB data to external files, such as t1_000000001.lob, t1_000000002.lob, etc. which are 1.5 GB in size:

$ iloader out ... -f t1.fmt -d t1.dat -lob " lob_file_size=1.5G "

To dump LOB data to a single file named t1.lob:

$ iloader out ... -f t1.fmt -d t1.dat -lob "use_lob_file=yes"
$ iloader out ... -f t1.fmt -d t1.dat -lob "use_lob_file=yes" -lob "lob_file_size=1G"
$ iloader out ... -f t1.fmt -d t1.dat -lob "lob_indicator=%$LOB$%"

Data File Format#

When downloading a column containing LOB data, an offset:size combination which points to the location of LOB data in an external file is written to the data file.

If the offset:size combination, which points to LOB data, is not expressly specified, then iLoader looks in the data file for the pointer to the position of the LOB data in the LOB file after a %% delimiter.

Example)
$ cat t1.fmt
table t1
{
I1 integer;
I2 blob;
I3 char (10);
}
DATA_NLS_USE=US7ASCII
$ iloader out ... -f t1.fmt -d t1.dat -lob "lob_file_size=3G"
$ ls
t1_000000001.lob t1_000000002.lob t1.dat t1.fmt
$ cat t1.dat
1,"%%0:2000000000", "Terminator3"
2,"%%2000000000:1800000000", "SixSense"

The actual LOB data are downloaded to a LOB file, regardless of whether LOB offset and size information is provided between field delimiters (or between a pair of block delimiters, if using block delimiters).

When null LOB data are downloaded to a data file, there will be no data between the field separators (or between the pair of block delimiters, if appropriate) at the position of the corresponding LOB data within the data file. LOB data of size 0 are also stored in the same way. (Altibase internally handles LOB data of size 0 as NULL.)

If the use_separate_files option is specified, the data files are organized as follows:

t1/I1/000000001.lob,t1/I2/000000001.lob,t1/I3/000000001.lob
t1/I1/000000002.lob,t1/I2/000000002.lob,t1/I3/000000002.lob
t1/I1/000000003.lob,t1/I2/000000003.lob,t1/I3/000000003.lob
,,,

In the above example, all of the columns contain LOB data.

If the LOB data to be stored in a LOB file are NULL, no characters are stored at the corresponding LOB data position in the data file, as can be seen in the fourth row above.

Performance Options#

IN#

The following options can realize considerable performance gains when running iLoader.

Factor	Description
-array array_size	To increase the speed of a data uploading ("in") operation, data read from a file are first organized into an array before being sent to the server. Performance is increased because the number of times that communication with the server must be established is decreased. However, if this value is set excessively high, it can have the opposite effect. If a LOB column exists, -array is ignored.
-commit commit_unit	When uploading data, this option determines how many records are committed at one time after being inserted. By default, 1000 records are committed after being inserted. If commit_unit is set to 0, the application runs in NONAUTOCOMMIT mode, in which the commit operation takes place only after all data have been inserted. If commit_unit is set to 1, the application runs in AUTOCOMMIT mode, in which a commit operation takes place for every record at the time that it is inserted. When this option is used together with the array option, the commit operation takes place after a number of records equal to array_size * commit_unit have been inserted.
-atomic	This option is set to use the Atomic Array INSERT option. Atomic Array INSERT realizes better performance than Array Insert because Atomic Array INSERT handles a number of Insert statements (up to the size of the array) as a single transaction. This option must be used together with the -array option. Additionally, tables that contain LOB type columns cannot be handled using this option. Furthermore, this option is useful only when uploading data.
-direct [log\|nolog] (Direct-Path INSERT)	This option is for use with the Direct-Path INSERT when uploading data to a disk table. If neither log nor nolog is specified, log is the default. If using nolog mode, it is essential to back up the table in question. If execution fails in nolog mode, recovery to a normal state will be impossible. If loading tables for which there are restrictions (Restrictions for Direct-Path INSERT), execution will automatically switch to the atomic option. If the -array option has not been set, the size of the array will automatically be set to the maximum possible size (=USHRT_MAX-1, or 65535). If the -commit option is omitted, its value will be set to 1.
-parallel count	This specifies the number of threads that can operate at the same time. A number of threads can be created and executed in parallel up to the specified value, the maximum of which is 32. When downloading, a number of files equal to the specified value is created, and the data are saved therein. When downloading with only the -parallel option set, the performance of repeated bind and fetch operations may be reduced. Therefore, when downloading data, the -parallel and -array options should be used together. If a LOB column is present, this option is ignored. When uploading with the -parallel option, iloader creates count + 1 connections, and when downloading with this option, iloader always creates two connections. Therefore, when uploading or downloading with this option via IPC, the value in the IPC_CHANNEL_COUNT property in altibase.properties must be equal to or greater than the number of these connections. Default value: 1, Max value: 32
-readsize n	This is used as an option for the IN mode. This option specifies the amount of data that are read from a file at one time. The size must be greater than 0. The default is 1048576 bytes.

LOB column constraints#

If there is a LOB colum in the table to be uploaded, the value speicifed by the user is ignored for the following options and internally set as shown in the table below.

Option	Setting Value
-array	1
-atomic	Ignored
-commit	1
-direct	Ignored
-parallel	1

Atomic Array INSERT#

The -atomic option is used to execute Atomic Array INSERT, which is useful only when uploading data. Atomic Array INSERT can realize faster performance than Array INSERT because Atomic Array INSERT handles a number of Insert statements (up to the size of the array) as a single transaction.

When uploading records using Atomic Array INSERT, if one of the records is not successfully uploaded, the records will instead be uploaded using Array INSERT. In other words, the result of Atomic Array INSERT is the same as Array INSERT. Moreover, Atomic Array INSERT provides much better performance than Array INSERT. For this reason, the use of Atomic Array INSERT is recommended.

The Difference between Atomic Array INSERT and Array INSERT#

Comparing Atomic Array INSERT and Array INSERT is as follows.

Array INSERT executes individual statements separately, whereas Atomic Array INSERT can combine several statements and handle them as though they were a single statement.

While Array INSERT's original advantage is low communication overhead, Atomic Array INSERT realizes even faster performance because it reduces the number of statements.

Array INSERT and Atomic Array INSERT are compared in the following table.

Characteristic	Array INSERT	Atomic Array INSERT
Number of Statements	Number of Array Elements	One
Number of Results	Number of Array Elements	One
Number of Results (when an error occurs)	Number of Array Elements	Number of Array Elements
Transmission Speed	Fast	Very Fast

[Table 2-1] Differences between Array INSERT and Atomic Array INSERT

Atomic Array INSERT Limitations#

The following limitations govern the use of Atomic Array INSERT:

	Array Insert	Atomic Array Insert
Foreign Key	Operates Normally	Operates Normally
Unique Key	Operates Normally	Operates Normally
Not null	Operates Normally	Operates Normally
Check	Operates Normally	Operates Normally
Trigger Each Row	Executed N times	Executed N times
Trigger Each Statement	Executed N times	Executing Once
Partitioned Table	Operates Normally	Operates Normally
Sequence	Executed N times	Executed N times
SYSDATE	Executed N times	Executing Once
LOB Column	Operates Normally	Atomic attribute is not guaranteed when errors occur.
Procedure	Operates Normally	Operates Normally
SubQuery	A new view is observed every time	The first executed view is observed

[Table 2-2] Atomic Array INSERT Limitations

Atomic Array INSERT: Warning#

The -atomic option must be used with [-array array_size], and must not be used on tables that include LOB columns. Furthermore, this option is only useful when uploading data.

Direct-Path INSERT#

The so-called Direct-Path INSERT has been provided to support the uploading of data into a disk table. Direct-Path INSERT is a method of writing data directly into a data file using a Direct-Path INSERT buffer and a flush manager, bypassing the buffer manager. Moreover, free space in the existing extents is not used. Instead, new extents are allocated for a Direct-Path INSERT operation. Consequently, performance is improved, but additional space is required.

You can query the V$DIRECT_PATH_INSERT performance view to manage statistics for Direct-Path INSERT.

Restrictions for Direct-Path INSERT#

The following restrictions apply when uploading data using the Direct-Path INSERT. If the data to be uploaded do not satisfy all of these restrictions, data can be uploaded automatically using Atomic Array INSERT (-atomic option) instead of Direct-Path INSERT (- direct option).

the table can't have an index (or a Primary Key).
the table can't be associated with a trigger.
the table can't have a LOB column.
the table cannot have CHECK constraints.
the table cannot have referential integrity constraints.
replication cannot be performed on the table.
the table must exist in disk tablespace.

Warnings#

When processing data using the Direct-Path INSERT, please abide by the following:

If data upload fails while Direct-Path INSERT operation is being executed in nologging mode ("-direct nolog"), it will be impossible to restore the database normally. Therefore, it is critical to back up the relevant table(s) before using nologging mode.
The Direct-Path INSERT realizes excellent performance when uploading large amounts of data. However, when not handling large amounts of data, the improvement in performance will not be notable.
Data can be loaded using the Direct-Path INSERT only if the table exists in disk tablespace. Therefore, when using this method to load data into memory tablespaces, the result will be the same as if the -atomic option were used.

Examples#

To execute direct-path uploading in logging mode with the maximum array size:

il in t1.form -d t1.dat -direct [log]

To execute direct-path uploading in nologging mode with the maximum array size:

il in t1.form -d t1.dat -direct nolog

To execute direct-path uploading in logging mode with a specified array size:

il in t1.form -d t1.dat -array 1000 -direct

OUT#

When executing OUT operation with iLoader, the following options can be used to increase performance.

Factor	Description
-array array_size	Option to specify the number of rows to fetch at one time. Default: 1
-parallel count	This option specifies the number of threads to work simultaneously. As many threads as specified are created and processed in parallel, as many data files as threads are created. When downloading using the -parallel option alone, bind and fetch are repeatedly performed, which causes performance degradation. Therefore, the -array option must be used together. In case of IN, there are always 2 connections to the server. Default: 1, Max: 32
-prefetch_rows n	When executing a select query, the user can specify the number of records fetched from the database at one time. The range of possible values is 0 to 214783647. 0 means the maximum size that can be contained in a network packet. Default: 0
-async_prefetch [on\|off\|auto]	To improve fetch performance, asynchronous prefetch can be configured. The values that can be set for this option are as follows. - off: Do not perform asynchronous prefetch. (Default) - on: Perform asynchronous prefetch. - auto: Perform auto tuning for asynchronous prefetch. (Linux only) For detailed information on asynchronous prefetch, please refer to ALTIBASE_PREFETCH_ASYNC, ALTIBASE_PREFETCH_AUTO_TUNING, ALTIBASE_SOCK_RCVBUF_BLOCK_RATIO in CLI User's Manual.

LOB Column contraints#

If there is a LOB column in the download target table, the value specified by the user is ignored for the next option and is set internally as follows.

Option	Setting Value
-array	1
-parallel	1

Example#

Download to Array 1000.

iLoader> out -f t1.form -d t1.dat -array 1000

Using iLoader in Batch Mode#

iLoader can run in interactive mode or in batch mode. When running iLoader in batch mode, command-line options are used.

Creating FORM Files#

FORM files provide iLoader with information on the attributes of target tables when data are uploaded or downloaded. This information covers all aspects of table schema except constraints. In order to use iLoader to load data, FORM files containing information about the target tables(s) must first be created.

The syntax for creating FORM files is shown below.

iloader  formout  -S servername  -U user_id  -P password  -T table_name -f form_file

In the following example, table T1 and a corresponding FORM file are created.

iSQL> CREATE TABLE t1 (
name VARCHAR(30),
age INTEGER,
gender CHAR(1),
etc NVARCHAR(30));
Create success.
iSQL> INSERT INTO t1 VALUES ( 'John Doe', 20, 'M', N'Group A');
1 row inserted.
iSQL> INSERT INTO t1 VALUES ('Pham Tan Sang', 30, 'M', N'Group C');
1 row inserted.
iSQL> INSERT INTO t1 VALUES ('Alisee Dupoint', 24, 'F', N'Group D');
1 row inserted.
iSQL> SELECT * FROM t1;
NAME AGE GENDER ETC
---------------------------------------------------
John Doe 20 M Group A
Pham Tan Sang 30 M Group C
Alisee Dupoint 24 F Group D
3 rows selected.
iSQL> EXIT
$ iloader formout -s 127.0.0.1 -u sys -p manager -T T1 -f T1.fmt
-------------------------------------------------------
 Altibase Data Load/Download utility.
 Release Version 7.1
 Copyright 2000, AltibaseCorporation or its subsidiaries.
 All Rights Reserved.
-------------------------------------------------------
ISQL_CONNECTION : TCP
$ cat T1.fmt
table T1
{
"NAME" varchar (30);
"AGE" integer;
"GENDER" char (1);
"ETC" nvarchar (100);
}
DATA_NLS_USE=US7ASCII
NCHAR_UTF16=YES

DATA_NLS_USE can be used to show how downloaded data will be encoded. Normally, when a FORM file is created, the client character set is used.

If the table to be downloaded contains a national character type column, NCHAR_UTF16 will be used. The encoding method that is used when data are saved is UTF-16BE (Big Endian) when NCHAR_UTF16 is set to YES. If set to NO, data will be encoded as CHAR type, and could be lost. The default value is YES.

Downloading Data#

Records in the database table that satisfy the conditions specified in the FORM file are saved in text file form.

Downloaded data will be saved according to the character set specified using the ALTIBASE_NLS_USE environment variable.

If the database character set is different from that specified in ALTIBASE_NLS_USE, the data will be converted. When downloading data, Altibase displays the table name every 5,000 records, and also displays the total number of records after all records have been downloaded.

The following syntax is used to download data.

iloader  out  -S servername -U user_id   -P password          -f  form_file  -d backup_files  -split n   -array count      -parallel count

In the following example, the T1.fmt file is used to download data to the T1.dat file.

$ iloader out -s 127.0.0.1 -u sys -p manager -f T1.fmt -d T1.dat
-------------------------------------------------------
     Altibase Data Load/Download utility.
     Release Version 7.1.0.1
     Copyright 2000, Altibase Corporation or its subsidiaries.
     All Rights Reserved.
-------------------------------------------------------
ISQL_CONNECTION : TCP
DATA_NLS_USE: US7ASCII
NCHAR_UTF16 : YES

     Total 3 record download(T1)

     DOWNLOAD : 11.2320 msec
$ cat T1.dat
"John Doe",20,"M","Group A"
"Pham Tan Sang",30,"M","Group C"
"Alisee Dupoint",24,"F","Group D"

Uploading Data#

iLoader can be used to upload backup files or files containing edited data to the database.

The data character set must be specified using ALTIBASE_NLS_USE when uploading data. iLoader converts data saved in the character set specified in ALTIBASE_NLS_USE to the database character set and then saves the data. If the database character set and the character set specified using ALTIBASE_NLS_USE are different, the data are converted. If the character set specified in ALTIBASE_NLS_USE is different from the character set in which the data were actually saved, the data will not be uploaded properly.

When uploading data, the -atomic option can be used to perform Atomic Array Insert to realize improved performance. The -atomic option must be used with the -array option, and it is additionally recommended that the -commit option also be used.

When uploading data, iLoader displays the table name every 5,000 records, and also displays the total number of records after all records have been uploaded.

The following syntax is used to upload data.

iloader  in  -S servername -U user_id -P password
-f form_file      -d backup_files 
-mode mode_type  -F firstrow  
-commit commit_unit  -L lastrow   
-array count     -atomic   -parallel count

In the following example, the T1.fmt file and the T1.dat file are used to upload data.

$ iloader in -s 127.0.0.1 -u sys -p manager -f T1.fmt -d T1.dat -F 1 -L 2
-------------------------------------------------------
     Altibase Data Load/Download utility.
     Release Version 7.1.0.1
     Copyright 2000, Altibase Corporation or its subsidiaries.
     All Rights Reserved.
-------------------------------------------------------
ISQL_CONNECTION : TCP
DATA_NLS_USE: US7ASCII
NCHAR_UTF16 : YES

     UPLOAD : 24.4040 msec

     Load Count  : 2(T1)
$ isql -s 127.0.0.1 -u sys -p manager -silent
iSQL> select * from t1;
select * from t1;
iSQL> SELECT * FROM t1;
NAME AGE GENDER ETC
-------------------------------------------------------
John Doe 20 M Group A
Pham Tan Sang 30 M Group C
Alisee Dupoint 24 F Group D
Yuvraj Kohli 20 M Group A
Hok Chan 30 M Group C
5 rows selected.

The table information in the previously created T1.fmt file is referenced to upload the data in the T1.dat file to table T1 of the current database.
Since the default value of the -mode option is APPEND, data are appended to the T1 table. (The REPLACE option uploads data after first deleting all of the data in table T1 of the current database.)
The 1st and 2nd records are uploaded from the T1.dat file. If 100 and 1000 are specified for the first row and the last row respectively, data in the file from the 100th record to the 1000th record are uploaded.

$ iloader in -s 127.0.0.1 -u sys -p manager -f T1.fmt -d T1.dat -array 1000 -commit 1 -atomic

The table information in the T1.fmt file is referenced to upload 1000 records at a time from the T1.data file to table T1 of the current database using Atomic Array Insert.

Execution Result Code#

Altibase displays one of the following execution result codes after using iLoader to download or upload data.

0: Success
-1: General Error
-2: One or more upload errors have occured.

0 or -1 is displayed to indicate success or failure, respectively, of a data upload or download operation. -2 is displayed to indicate that the overall upload operation succeeded but that one or more errors occurred.

Using iLoader for Remote Database Access#

You can use iLoader to access a remote server. To execute iLoader, the Altibase server must have been started. The options are as follows, and are case-insensitive.

Command line options for running iLoader remotely#

iloader [-H] [-S server_name] [-U user_id] [-P password]
[-PORT port_no] [-SILENT] [-NST] [-DISPLAYQUERY]

-H: provides help on how to execute iLoader.
-S server_name: specifies the name (or IP address) of the computer on which the Altibase server is running.
-U user_name: specifies the user ID with which to access the database.
-P password: specifies the password corresponding to the user ID.
-PORT port_no: specifies the port number for communication with the remote server.
-SILENT: specifies execution in silent mode. In silent mode, nonessential information, such as the copyright notice etc., is not displayed.
-NST: specifies execution in nst mode. In nst mode, elapsed times are not displayed.
-DISPLAYQUERY: The user can check hints and conditional clauses specified in a FORM file when executing statements. They are displayed in query format.
-NLS_USE: specifies the character set to be used when downloading or uploading data. If omitted, the client's environment variable ALTIBASE_NLS_USE and altibase.properties are referred to in sequence, and if still not set, the basic character set (US7ASCII) is used.

The options -S server_name, -U user_name, -P password, and -PORT port_no must be directly input at a command prompt when connecting to the remote server.

Example#

Remote Server IP: 192.168.1.71, PORT_NO: 20594
Communication protocol: TCP/IP (ISQL_CONNECTION=TCP/IP)

Remote Server Data#

Executing on remote server

iSQL> CREATE TABLE department (
dno            SMALLINT     PRIMARY KEY,
dname          CHAR(30)     NOT NULL,
dep_location   CHAR(9),
mgr_no         INTEGER );
Create success.
iSQL> INSERT INTO department VALUES (1001, 'technical', 'Mapo', 1);
1 row inserted.
iSQL> INSERT INTO department VALUES (1002, 'engine devt', 'Yeouido', 10);
1 row inserted.
iSQL> INSERT INTO department VALUES (1003, 'marketing', 'Gangnam', 9);
1 row inserted.
iSQL> INSERT INTO department VALUES (2001, 'planning', 'Gangnam', 15);
1 row inserted.
iSQL> INSERT INTO department VALUES (3001, 'operations', 'Sinchon', 9);
1 row inserted.

Remote Data Download#

Excuting on local server

$ iloader formout -s 192.168.1.71 -u sys -p manager -port 20594 -T department -f dept.fmt
-------------------------------------------------------     
Altibase Data Load/Download utility.
    Release Version 7.1.0.1
    Copyright 2000, Altibase Corporation or its subsidiaries.
    All Rights Reserved.
-------------------------------------------------------
ISQL_CONNECTION : TCP
$ ls
dept.fmt
$ iloader out -s 192.168.1.71 -u sys -p manager -port 20594 -f dept.fmt -d dept.dat
-------------------------------------------------------
     Altibase Data Load/Download utility.
     Release Version 7.1.0.1
     Copyright 2000, Altibase Corporation or its subsidiaries.
     All Rights Reserved.
-------------------------------------------------------
ISQL_CONNECTION : TCP
DATA_NLS_USE: US7ASCII

     Total 5 record download(department)

     DOWNLOAD : 5.9540 msec
$ ls
dept.dat   dept.fmt

Remote Data Upload#

Executing on local server

$ iloader in -s 192.168.1.71 -u sys -p manager -port 20594 -f dept.fmt -d dept.dat -mode replace
-------------------------------------------------------
     Altibase Data Load/Download utility.
     Release Version 7.1.0.1
     Copyright 2000, Altibase Corporation or its subsidiaries.
     All Rights Reserved.
-------------------------------------------------------
ISQL_CONNECTION : TCP
DATA_NLS_USE: US7ASCII

     UPLOAD : 70.6630 msec

     Load Count  : 5(department)

Result on remote server#

Executing on local server

iSQL> select * from department ;
DNO         DNAME                           DEP_LOCATION  MGR_NO      
---------------------------------------------------------------------------
1001        technical                       Mapo       1           
1002        engine devt                     Yeouido    10          
1003        marketing                       Gangnam    9           
2001        planning                        Gangnam    15          
3001        operations                      Sinchon    9           
5 rows selected.

Using iLoader in Interactive Mode#

If the user does not enter any of the mandatory inputs for iLoader execution, i.e. formout / out / in, the iLoader> prompt will be displayed, and iLoader will be executed in interactive mode. In interactive mode, command-line options can be used the same way as in batch mode

Starting iLoader#

The user must first be logged in before entering interactive mode. Connection information can be entered either on the command line or via iLoader. Additional information required to establish a connection with the server includes the server name (-S), user name (-U) and password (-P). The password is not case-sensitive, but the user name is if you use double quotation marks to enclose this.

How to Enter Connection Information on the Command line

$ iloader -s 127.0.0.1 -u sys -p manager
-------------------------------------------------------
     Altibase Data Load/Download utility.
     Release Version 7.1.0.1
     Copyright 2000, Altibase Corporation or its subsidiaries.
     All Rights Reserved.
-------------------------------------------------------
ISQL_CONNECTION : TCP
iLoader>            ->  When the iLoader prompt appears, enter a command to accomplish a desired task in interactive mode.

How to Enter Connection Information in iLoader#

$ iloader
-------------------------------------------------------
     Altibase Data Load/Download utility.
     Release Version 7.1.0.1
     Copyright 2000, Altibase Corporation or its subsidiaries.
     All Rights Reserved.
-------------------------------------------------------
Write Server Name (enter:127.0.0.1) :
Write UserID : sys
Write Password : manager    -> The password is not output to the screen.
ISQL_CONNECTION : TCP
iLoader>            -> When the iLoader prompt appears, enter a command to accomplish a desired task in interactive mode.

Exiting Interactive Mode#

iLoader> exit

Creating FORM Files#

iLoader> formout -T employees -f employees.fmt

iLoader> exit
$ ls
employees.fmt

Downloading Data#

iLoader> out -f employees.fmt -d employees.dat
DATE FORMAT : YYYY/MM/DD HH:MI:SS
     ..
    Total 20 record download (EMPLOYEES)
    DOWNLOAD: 56936

iLoader> exit
$ ls
employees.dat   employees.fmt

Uploading Data#

iLoader> in -f employees.fmt -d employees.dat -mode replace
DATE FORMAT : YYYY/MM/DD HH:MI:SS
     UPLOAD: 13880

     Load Count  : 20 (EMPLOYEES)

Execution Result Code#

Altibase displays one of the following execution result codes after downloading or uploading data using iLoader.

0 : Success
-1 : General Error -2 : One or more upload errors have occurred.

0 or -1 is displayed to indicate success or failure, respectively, of a data upload or download operation. -2 is displayed to indicate that the overall upload operation succeeded but that one or more errors occurred.

Remote Access in Interactive Mode#

When iLoader is executed remotely, if none of the command-line options (formout / out / in) are entered, and only the port number is entered, the user will be prompted to enter the option values.

If access is successful, an iLoader prompt will appear, and a command can be entered to perform a desired task interactively.

In the following example, after the port number of a remote server is used to access the remote server from a local server, the iLoader utility is used to download the data from the MANAGER table on the remote server to a file, and then to upload the data back to the MANAGER table.

Remote server (IP: 192.168.1.10, PORT_NO: 21300)
Communication protocol: TCP/IP (ISQL_CONNECTION=TCP/IP)

Remote Server Data#

iSQL> CREATE TABLE manager(
mgr_no INTEGER,
mname VARCHAR(20),
address VARCHAR(60));
Create success.
iSQL> INSERT INTO manager VALUES (7, 'HJMIN', '44-25 Youido-dong Youngdungpo-gu Seoul, Korea');
1 row inserted.
iSQL> INSERT INTO manager VALUES(8, 'JDLEE', '3101 N. Wabash Ave. Brooklyn, NY');
1 row inserted.
iSQL> INSERT INTO manager VALUES(12, 'MYLEE', '130 Gongpyeongno Jung-gu Daegu, Korea');
1 row inserted.
iSQL> SELECT * FROM manager;
MGR_NO      MNAME      ADDRESS
--------------------------------------------------------------------
7           HJMIN      44-25 Youido-dong Youngdungpo-gu Seoul, Korea
8           JDLEE      3101 N. Wabash Ave. Brooklyn, NY
12          MYLEE      130 Gongpyeongno Jung-gu Daegu, Korea
3 rows selected.

Remote Data Download and Upload (Local Server)#

$ iloader -s 192.168.1.10 -u sys -p manager -port 21300
-------------------------------------------------------
     Altibase Data Load/Download utility.
     Release Version 7.1.0.1
     Copyright 2000, Altibase Corporation or its subsidiaries.
     All Rights Reserved.
-------------------------------------------------------
ISQL_CONNECTION : TCP
iLoader>        -> iLoader is connected to the server, and in this mode commands can be entered to interactively perform a desired task.

or

$ iloader -port 21300
-------------------------------------------------------
     Altibase Data Load/Download utility.
     Release Version 7.1.0.1
     Copyright 2000, Altibase Corporation or its subsidiaries.
     All Rights Reserved.
-------------------------------------------------------
Write Server Name (enter:127.0.0.1) : 192.168.1.10
Write UserID : sys
Write Password : manager            -> The password is not displayed on the screen.
ISQL_CONNECTION : TCP

iLoader> formout -T MANAGER -f MANAGER.fmt

iLoader> out -f MANAGER.fmt -d MANAGER.dat
DATA_NLS_USE: US7ASCII
     Total 3 record download(MANAGER)
     DOWNLOAD : 16.2420 msec

iLoader> in -f MANAGER.fmt -d MANAGER.dat
DATA_NLS_USE: US7ASCII

     UPLOAD : 32.5190 msec
     Load Count  : 3(MANAGER)

Result (Remote Server)#

iSQL> SELECT * FROM manager;
MGR_NO   MNAME     ADRESS
-----------------------------------------------------------------
7        HJMIN     44-25 Youido-dong Youngdungpo-gu Seoul, Korea 
8        JDLEE     3101 N. Wabash Ave. Brooklyn, NY 
12       MYLEE     130 Gongpyeongno Jung-gu Daegu, Korea 
7        HJMIN     44-25 Youido-dong Youngdungpo-gu Seoul, Korea 
8        JDLEE     3101 N. Wabash Ave. Brooklyn, NY 
12       MYLEE     130 Gongpyeongno Jung-gu Daegu, Korea              
6 rows selected.      <- The data have been appended to the table MANAGER.

Online Help and Other Options#

Help#

Help is available for the commands provided with iLoader. The HELP command displays all help information. For help on a specific command, enter the HELP command followed by the information for which help is needed.

Example#

iLoader> help
Usage : { in | out | formout | structout | help }
        [-d datafile or datafiles] [-f formatfile]
        [-T table_name] [-F firstrow] [-L lastrow]
        [-t field_term] [-r row_term] [-mode mode_type]
        [-commit commit_unit] [-bad badfile]
        [-log logfile] [-e enclosing] [-array count]
        [-replication true/false] [-split number]
        [-readsize size] [-errors count]
        [-lob lob_option_string] [-atomic]
        [-parallel count] [-direct [log|nolog]]
        [-rule csv]
        [-partition]
        [-dry-run]
        [-prefetch_rows]
        [-async_prefetch off|on|auto]
        [-geom WKB]
        [-lightmode]
        [-verbose]
iLoader> help help
Ex) help [ in | out | formout | structout | exit | help ]

iLoader> help in
Ex) in -f $formatfile -d $datafile -bad $badfile -log $logfile -e $enclosing

iLoader> help out
Ex) out -f $formatfile -d $datafile -split $number

iLoader> help exit
Ex> exit (or quit)

or

$ iloader help
===========================================================
                         ILOADER HELP Screen
===========================================================
  Usage   : iloader [-h]
                    [-s server_name] [-u user_name] [-p password]
                    [-port port_no] [-silent] [-nst] [-displayquery]
                    [-NLS_USE nls_name]
                    [-prefer_ipv6]
                    [-geom WKB]
                    [-ssl_ca CA_file_path | -ssl_capath CA_dir_path]
                    [-ssl_cert certificate_file_path]
                    [-ssl_key key_file_path]
                    [-ssl_verify]
                    [-ssl_cipher cipher_list]
                    [{ in | out | formout | structout | help }
                     [-d datafile or datafiles] [-f formatfile]
                     [-T table_name] [-F firstrow] [-L lastrow]
                     [-t field_term] [-r row_term] [-mode mode_type]
                     [-commit commit_unit] [-bad badfile]
                     [-log logfile] [-e enclosing] [-array count]
                     [-replication true/false] [-split number]
                     [-readsize size] [-errors count]
                     [-lob lob_option_string] [-atomic]
                     [-parallel count] [-direct [log|nolog]]
                     [-rule csv]
                     [-partition]
                     [-dry-run]
                     [-prefetch_rows]
                     [-async_prefetch off|on|auto]
                     [-geom WKB]
                     [-lightmode]
                     [-verbose]]
            -h            : This screen
            -s            : Specify server name to connect
            -u            : Specify user name to connect
            -p            : Specify password of specify user name
            -port         : Specify port number to communication
            -silent       : No display Copyright
            -nst          : No display Elapsed Time
            -displayquery : display query string
            -NLS_USE      : Specify NLS
            -prefer_ipv6  : Prefer resolving server_name to IPv6 Address
            -geom         : Specify geometry format such as WKB
            -ssl_ca       : The path to a CA certificate file
            -ssl_cpath    : The path to a directory that contains CA certificates
            -ssl_cert     : The path to the client certificate
            -ssl_key      : The path to the client private key file
            -ssl_verify   : Whether the client is to check certificates
                            that are sent by the server to the client
            -ssl_cipher   : A list of SSL ciphers
===========================================================

Other Options#

log: iLoader records task results, execution progress and errors that occurred during execution.
bad: rows that could not be uploaded due to the occurrence of errors during loading are recorded.

Example#

$ isql -s localhost -u sys -p manager -silent
iSQL> CREATE TABLE test1 (i1 INTEGER);
Create success.
iSQL> CREATE TABLE test2 (c1 CHAR(1));
Create success.
iSQL> INSERT INTO test2 VALUES(1);
1 row inserted.
iSQL> INSERT INTO test2 VALUES('A');
1 row inserted.
iSQL> INSERT INTO test2 VALUES(2);
1 row inserted.
iSQL> exit

$ iloader -s localhost -u sys -p manager -silent
iLoader> formout -T test1 -f test1.fmt

iLoader> formout -T test2 -f test2.fmt

iLoader> out -f test2.fmt -d test2.dat
DATA_NLS_USE: US7ASCII
NCHAR_UTF16 : YES

     Total 3 record download(TEST2)

     DOWNLOAD : 1.0420 msec

iLoader> in -f test1.fmt -d test2.dat -log test.log -bad test.bad
DATA_NLS_USE: US7ASCII
NCHAR_UTF16 : YES

UPLOAD : 6.6670 msec

     Load Count  : 2(TEST1)
     Error Count : 1

iLoader> exit

$ cat test.bad 
A

$ cat test.log 
<DataLoad>
TableName : TEST1
Start Time : Thu Sep 30 10:39:46 2010
Record 2 : A
[ERR-21011 : Invalid literal]
End Time : Thu Sep 30 10:39:46 2010
Total Row Count : 3
Load Row Count  : 2
Error Row Count : 1