IMPORT TABLE

You can import all data of a table exported from a database using the EXPORT TABLE command to a database. Use the IMPORT TABLE command to do so. It is not possible to select specific column values with the IMPORT TABLE command; you can only do this with the IMPORT COLUMNS command. Only the owner of the table may execute the IMPORT TABLE ...PAGE and IMPORT TABLE ...PAGES WITH ... commands. In all other cases you must have access authorization for the table you want to import the data to.

Prerequisites

You can use the demo databases DEMODB and TESTDB. You have exported the required data from demo database DEMODB. In demo database TESTDB, create the data administrator MONA if you have not already done so. Start the Loader and log onto database TESTDB as database administrator MONA with the password RED. Use the session mode: loadercli -d testdb -u mona,red..

Activities

The data from the selected table is to be imported into the TESTDB database. You can make entries after the keywords CATALOG and/or DATA to select what is to be imported, the database catalog and/or the application data.

Caution Caution

If you import a table, the Loader also creates all the objects and links (views, foreign key dependencies with other tables…) that are defined for the table. If the necessary objects are not present and the import therefore cannot be executed, the Loader notifies you of this fact. Typical error messages include -25451 and -25392. If these errors occur, the import is terminated at this point.

End of the caution.

Test the Loader behavior for different tables. If necessary, enter the required dependent objects into the TESTDB database. The ROOM table, for example, is dependent on the CITY and HOTEL tables.

Importing the Database Catalog of a Table

You can import all the database catalog information for a table.

IMPORT TABLE hotel.room

CATALOG INSTREAM 'room_ddl.catalog'

The database catalog (CATALOG) is imported in the DDL format.

Importing a Table’s Application Data

You can import all the application data of a table.

IMPORT TABLE hotel.room

DATA INSTREAM 'room_csv.data'

The application data (DATA) is imported in the CSV format (default value).

IMPORT TABLE hotel.room

DATA INSTREAM 'room_fwv.data' FWV

type 6-11

free 12-31

price 32-51

The application data (DATA) is imported in the FWV format.

IMPORT TABLE hotel.room

DATA INSTREAM 'room_fwvbinary.data' FWV BINARY

hno 1-5

type 6-11

free 12-31

price 32-51

The application data (DATA) is imported in the FWV BINARY format.

See also: Loader, CSV, FWV, FWV BINARY

IMPORT TABLE hotel.room

DATA INSTREAM 'room_pages.data' PAGES

The application data (DATA) is imported in the PAGES format. A PAGES import uses a database format (based on a page) and does not write log entries, so it is faster than an import that uses one of the other formats. However, the tables imported in the PAGES format are write-protected until you back up the data.

IMPORT TABLE hotel.room

DATA INSTREAM 'room_records.data' RECORDS

The application data (DATA) is imported in the RECORDS format.

Performance Improvement

You can speed up an IMPORT TABLE process considerably even if you are using format CSV, FWV [BINARY] by importing page by page. In this case you should use the PAGE WITH ... option of the usage_spec syntax rule for the DATA INSTREAM data stream, which determines that the table data is imported page by page.

IMPORT TABLE hotel.room

DATA INSTREAM 'room_csv.data'

PAGE WITH 80 % USAGE

The application data is imported into the blank table ROOM. The application data is read from the data stream in the CSV data format and imported into the ROOM table page by page with a fill level of 80%.

See also: Loader, Performance Improvement in the IMPORT TABLE

Importing the Database Catalog and all the Application Data of a Table

You can import all the data in the specified table.

IMPORT TABLE hotel.room

CATALOG INSTREAM 'room_ddl.catalog'

DATA INSTREAM 'room_csv.data'

Note the sequence of the syntax elements. Always enter the syntax element for the import of the database catalog first, and then the syntax element for the import of the application data.

if_condition

You can select individual data records from a DATA data stream in the formats CSV, FWV or RECORDS and then import only these records to the specified table. The IF condition is ignored if a data stream is imported in the PAGES format as the complete page is always imported. Note the sequence of the syntax elements. Enter the IF condition first.

IMPORT TABLE hotel.room

IF POS 4 < '200'

CATALOG INSTREAM 'room_ddl.catalog'

DATA INSTREAM 'room_csv.data'

The rows that have a value below 200 in the PRICE column are imported to the ROOM table.

See also: Loader, Selecting Data Records

DUPLICATES Clause

Use the DUPLICATES clause to control the behavior if duplicate table rows occur. Note the sequence of the syntax elements. Enter the DUPLICATES clause after the IF condition.

IMPORT TABLE hotel.room

UPDATE DUPLICATES

CATALOG INSTREAM 'room_ddl.catalog'

DATA INSTREAM 'room_csv.data'

The application data of table ROOM is imported and an existing row is overwritten with the imported data record.

See also: Loader, IMPORT TABLE command, Duplicates clause

Additional Log Functions

If you specify a data stream after the keyword PACKAGE, the command entries from the SYSLOADER.TRANSFORMATIONMODEL table are exported to the specified data stream. This is an additional logging option. Always enter the PACKAGE syntax element as the last syntax element.

IMPORT TABLE hotel.room

CATALOG INSTREAM 'room_ddl.catalog'

PACKAGE OUTSTREAM 'room_csv.package'

See also: Loader, Additional Log Functions (package_spec)

Handling LOB Values

If you use the CSV and FWV [BINARY] formats, extra data streams are created for the LOB data during export. The data of the HOTEL table in the HOTEL schema is to be imported. The HOTEL has the LOB column INFO, the contents of which were exported in a separate data stream. Enter the LOB syntax element after the DATA syntax element.

IMPORT TABLE hotel.hotel

DATA INSTREAM 'hotel_csv.data'

LOB INSTREAM INFO 'hotel_info_csv.lob'

All the columns in the HOTEL table are imported. The LOB values are stored in the data stream hotel_info_csv.lob.

If you use the PAGES and RECORDS formats, all data from the table, including LOB data, is exported to a data stream during export. A LOB syntax element is therefore not necessary for the import.

IMPORT TABLE hotel.hotel

INSTREAM 'hotel_records.data' RECORDS

All columns of the HOTEL table, including the LOB column INFO, are imported from the hotel_records.data data stream.

See also: Loader, Importing LOB Values

Complete Syntax of the IMPORT TABLE Command

Combining the syntax options can result in correspondingly complex commands. Note the sequence of the syntax elements.

IMPORT TABLE hotel.room

IF POS 4 < '200'

UPDATE DUPLICATES

CATALOG INSTREAM FILE 'room_ddl.catalog' DDL

DATA INSTREAM FILE 'room_csv.data' CSV

PAGE WITH 80 % USAGE

PACKAGE OUTSTREAM FILE 'room_csv.package' CSV

For a complete syntax description, see IMPORT TABLE Command in the Loader documentation.

If you want to import only selected column values instead of all the table data, select the IMPORT COLUMNS command.

More Information

Other commands for Importing

Session Mode