IMPORT SCHEMA Command

You can use the Loader command IMPORT SCHEMA to import the data belonging to the application of the specified schema.

Prerequisites

Only the owner of the schema can execute the IMPORT SCHEMA command. You have exported the required data using the corresponding EXPORT command.

Structure

Syntax Syntax

<import_schema_command> ::=
  IMPORT SCHEMA <schema_name>
    [<duplicates_clause>]
    [<map_schema_spec>]
    [<configuration_spec>]
    <catalog_data_instream_part_spec>
    [<package_spec>]
    [<restart_spec>]

<duplicates_clause> ::=
  REJECT DUPLICATES
| IGNORE DUPLICATES
| UPDATE DUPLICATES

<map_schema_spec> ::=
  MAP SCHEMA [<schema_map_name> | <schema_to_schema>,...]
<schema_map_name> ::=
  <identifier>
<schema_to_schema> ::=
  <schema_name> TO <schema_name>

<configuration_spec> ::=
  CONFIGURATION <instream_csv_spec>

<catalog_data_instream_part_spec> ::=
  <catalog_instream_spec>
| <data_instream_part_spec>
| <catalog_instream_spec> <data_instream_part_spec>
<catalog_instream_spec> ::=
  CATALOG <instream_ddl_spec>
<data_instream_part_spec> ::=
  DATA <instream_tableformat_spec>

<package_spec> ::=
  PACKAGE <oustream_csv_spec>

<restart_spec> ::=
  RESTART <package_guid>
<package_guid> ::=
  <unsigned_integer>

End of the code.

<catalog_instream_spec>, <data_instream_part_spec>

Depending on whether database catalog and/or application data is imported, the following data streams are generated during import:

Data stream for database catalog definitions CATALOG <instream_ddl_spec>
Data stream for application data DATA <instream_tableformat_spec>

Only one of the formats PAGES or RECORDS is possible for the data stream for the instream_tableformat_spec application data.

See: Data Stream, PAGES, RECORDS

If the name of the schema is identical to a keyword or if upper and lowercase notation is relevant, the name must be placed in double quotation marks.

Syntax Syntax

IMPORT SCHEMA <schema_name>
  [<duplicates_clause>]
  [<map_schema_spec>]
  [<configuration_spec>]
  <catalog_instream_spec> <data_instream_part_spec>
  [<package_spec>]
  [<restart_spec>]

End of the code.

This IMPORT SCHEMA command imports the data belonging to the application of the specified schema (database catalog and application data).

Syntax Syntax

IMPORT SCHEMA <schema_name>
  [<map_schema_spec>]
  <catalog_instream_spec>
  [<package_spec>]
  [<restart_spec>]

End of the code.

This IMPORT SCHEMA command imports the database catalog information belonging to the application of the specified schema.

Syntax Syntax

IMPORT SCHEMA <schema_name>
  [<duplicates_clause>]
  [<map_schema_spec>]
  [<configuration_spec>]
  <data_instream_part_spec>
  [<package_spec>]
  [<restart_spec>]

End of the code.

This IMPORT SCHEMA command imports the application data of the specified schema.

<duplicates_clause>

You use the DUPLICATES clause to specify how to proceed when importing data from a data stream if a row with the same key as the new row already exists in the table. This clause is not evaluated if the data is imported into the database in PAGES format.

`REJECT DUPLICATES`	The new line is rejected with an error message (default value).
`IGNORE DUPLICATES`	The new line is not inserted.
`UPDATE DUPLICATES`	The new line overwrites the existing line.

<map_schema_spec>

If data has been exported in one schema, you can import it to another. To do this, you can use the USE SCHEMAMAP command to determine a schema map for a Loader session. However, you can also use the map_schema_spec syntax element in the IMPORT SCHEMA command, either to specify an existing schema map (schema_map_name) containing the schema mapping you require or to determine the schema mapping directly using <schema_name> TO <schema_name>.

See: CREATE/DROP SCHEMAMAP Statement

<configuration_spec>

You can exclude tables from the import.

If you do not specify a data stream using CONFIGURATION <instream_csv_spec>, all the application data is imported.
If you specify a data stream using CONFIGURATION <instream_csv_spec>, you can exclude the application data of selected tables from the import of the instream_tableformat_spec application data. The tables must be identified in the data stream by specifying their schema and table name. The information about which tables are not exported is stored in the system tables. One way of displaying this information is to display the system table SYSLOADER.TRANSFORMATIONMODEL. The tables excluded from the export of the application data have the value TRUE in the EXCLUDE column.

The database catalog entries CATALOG <instream_ddl_spec> of all tables are always imported, regardless of the restrictions set using CONFIGURATION <instream_csv_spec>.

<package_spec>

You can export system table SYSLOADER.TRANSFORMATIONMODEL.

If you specify a data stream using PACKAGE <outstream_csv_spec>, all entries in the SYSLOADER.TRANSFORMATIONMODEL table are exported to this data stream. We recommend you use this option to create a log of the export or import process.
If you do not specify a data stream using PACKAGE <outstream_csv_spec>, the system table SYSLOADER.TRANSFORMATIONMODEL is not exported.

See: Additional Log Functions (package_spec)

<restart_spec>

You can restart the export or import using the package_guid (ID created by Loader for every command at runtime. This integer is always 24 bytes long). The user can determine the package_guid of the corresponding export or import commands from the SYSLOADER.TRANSFORMATIONMODEL table or the log file. You can restart the command with the corresponding package_guid using the restart_spec syntax element.

See: Restarting

Result

The application data and/or database catalog are imported to the data_instream_part_spec and catalog_instream_spec data streams. If necessary, all entries in the SYSLOADER.TRANSFORMATIONMODEL table are exported to the package_spec data stream.

Errors: The import cannot be completed successfully. In this case, Loader logs the cause of the error in the log file. The values PACKAGEGUID and CLIENTNODE are also written to the log file. The PACKAGEGUID is required if you want to restart the export.

Example

Loader Tutorial, IMPORT SCHEMA

More Information

Importing: Loader Commands

EXPORT SCHEMA Command

Loader Log File

Loader System Tables