Background documentationPerformance Improvement in the IMPORT TABLE Locate this document in the navigation structure

 

You can use the syntax rule usage_spec in the DATA data stream to control whether the data of a table imported in CSV or FWV data format should be imported with a mass INSERT (RECORDS USAGE) or with pages (PAGE WITH ...).

Structure

Syntax Syntax

  1. <usage_spec> ::=
  RECORDS USAGE
| PAGE WITH <unsigned_integer> % USAGE
| PAGE WITH <unsigned_integer> RECORDS USAGE
End of the code.

The default value for usage_spec is: RECORDS USAGE. If a percentage is specified in usage_spec syntax rule PAGE WITH ... , this number must be between 50 and 100 (default value: 80). The length of individual table records is important when you specify the number of rows.

You can considerably speed up the import of tables (IMPORT TABLE command) 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 in 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.

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

  • The target table does not have an index.

  • The target table does not have an LOB column.

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

  • 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 into the target table.

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, 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 you anticipate considerable dynamic growth for the table, you should utilize the occupied memory by less than 80%. If you use syntax rule PAGE WITH ... consider the following:

  • Since no log entries are written when PAGE WITH ... is specified, 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.

Note Note

You can also import and export data from a table in 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).

End of the note.

Example

Loader Tutorial, IMPORT TABLE