Performance Improvement in the IMPORT TABLE 

You can use the usage_spec syntax rule in the DATA data stream to control whether a table’s data is imported in the CSV or FWV data formats and via a mass INSERT (RECORDS USAGE) or by pages (PAGE WITH …).

Syntax

<usage_spec> ::= RECORDS USAGE
               | PAGE WITH <unsigned_integer> % USAGE
               | PAGE WITH <unsigned_integer> RECORDS USAGE

Default value: RECORDS USAGE

<unsigned_integer>

Unsigned integer See the SQL Reference Manual, Unsigned Integer (unsigned_integer) If a percentage is specified, this number must be between 50 and 100. Default value: 80 When specifying the number of rows, the length of individual table records is important.

Examples

Loader Tutorial, IMPORT TABLE

Explanation

Importing tables (IMPORT TABLE command) can be significantly speeded up by specifying PAGE WITH … (importing as pages). This is considerably faster than using RECORDS USAGE (importing via a mass INSERT).

If you specify the syntax rule PAGE WITH ... for the import, the data is entered directly into the pages of the database and no log entries are written.

When you specify the rule RECORDS USAGE, the data is entered into the database as a mass INSERT and log entries are written so that entries can be cancelled if errors occur.

You can only use the syntax rule PAGE WITH… if the following prerequisites are met:

●     The target table exists in the database instance.

●     You have logged on to the Loader with the database user who is the owner of the target table.

●     The target table does not have an index.

●     The target table does not have a LONG column.

●     The application data in the data stream to be imported is sorted in ascending order in the sequence of the primary key of the target table.

●     If the target table already contains application data, only those data records whose key values are greater than the largest key value in the target table can be inserted.

You can use the syntax rule PAGE WITH to define the extent to which a page is filled with data records. To do so, you can specify a percentage between 50 and 100 or define the number of rows (data records, records) that you want to import to each page. If you specify a number of rows that exceeds the actual number possible, the Loader displays an error message during the import operation, indicating the maximum number of rows permitted for each page.

If the table is not modified at all, or only slightly, it is a good idea to utilize the occupied memory by more than 80%.

If considerable dynamic growth is anticipated for the table, it is a good idea to utilize the occupied memory by less than 80%.

If you use syntax rule PAGE WITH … please consider the following:

●     When PAGE WITH … is specified no log entries are written, so you must back up the new table data after you have imported it. You can either back up the appropriate pages (incremental data backup) or all the data (complete data backup).

●     The table is write-protected until you back up the new application data.

See also:

Concepts of the Database System, Backing Up Data

You can also import and export data from a table in the PAGES format, so that the information is stored in the data stream as pages. In this case, the usage_spec syntax rule is neither possible nor necessary.

EXPORT TABLE Command, IMPORT TABLE Command