IMPORT TABLE 

You can use the IMPORT TABLE command to import all the data of a table.

Prerequisites

You require the TESTDB demo database. Create the database user MONA if not set up already. Create the HOTEL schema as user MONA if it does not already exist.

You have exported the required data using the EXPORT TABLE Command.

It is not possible to select specific column values with the IMPORT TABLE command; you can only do this with the IMPORT COLUMNS command.

The commands IMPORT TABLE ... PAGE and IMPORT TABLE ... PAGES WITH ... may only be executed by the owner of the table. In all other cases you must have access authorization for the table you want to import the data to.

Start the Loader as database administrator MONA and log on to the demo database TESTDB. Use the session mode.

loadercli –d testdb –u mona,red –s hotel

Examples

The data from the selected table is to be imported into the TESTDB database.

You can make entries after the keyword CATALOG and/or DATA to select what is to be imported, the database catalog and/or the application data.

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 the fact. Typical error messages include: -25451 and -25392. If these errors occur, the import is terminated at this point.

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 of a table.

IMPORT TABLE hotel.room
  CATALOG INSTREAM 'room_ddl.catalog'

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

See also:

Loader, Data Stream

Importing the Application Data of a Table

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
    hno    1-5
    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  06-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.

IMPORT TABLE hotel.room
  DATA INSTREAM 'room_records.data' RECORDS

The application data (DATA) is imported in the RECORDS 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.

See also:

Loader, PAGES, RECORDS

Performance Improvement

You can speed up an IMPORT TABLE process considerably even if you are using format CSV or 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'

Observe 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. Observe 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. Observe 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, Duplicates Clause (IMPORT TABLE Command)

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 log 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'

Loader, Additional Log Functions (package_spec)

Handling LONG Values

If you use the CSV and FWV [BINARY] formats, extra data streams are created for the LONG data during export.

The data of the HOTEL table in the HOTEL schema is to be imported. The HOTEL table has the LONG column INFO, the contents of which were exported in a separate data stream. Enter the LOB syntax elements after the DATA syntax element.

IMPORT TABLE hotel.hotel
  DATA INSTREAM 'hotel_csv.data'
  LOB INSTREAM INFO 'hotel_info_csv.long'

All the columns in the HOTEL table are imported. The LONG values are stored in the data stream hotel_info_csv.long.

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

IMPORT TABLE hotel.hotel.
  DATA INSTREAM 'hotel_records.data' RECORDS

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

See also:

Loader, Exporting and Importing LONG Values

Complete Syntax of the IMPORT TABLE Command

Combining the syntax options can result in correspondingly complex commands. Observe 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.

See also:

Other commands for Importing