CREATE/DROP SCHEMAMAP Statement

A schema map is a mapping of the data from one schema to another schema.

You can create a schema map using the CREATE SCHEMAMAP statement. You can delete a schema map using the DROP SCHEMAMAP statement.

Syntax

<create_schema_map_statement> ::=
  CREATE SCHEMAMAP <schema_map_name> MAP SCHEMA <schema_to_schema>,...

<drop_schema_map_statement>   ::= DROP SCHEMAMAP <schema_map_name>

<schema_map_name>             ::= <identifier>
<schema_to_schema>            ::= <schema_name> TO <schema_name>
<schema_name>                 ::= <identifier>

<schema_map_name>

Name of the schema map

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

See the SQL Reference Manual, Identifier (identifier).

<schema_name>

Name of the schema

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

See the SQL Reference Manual, Schema Name (schema_name)

Examples

CREATE SCHEMAMAP myschemamap MAP SCHEMA a1 TO b1, a2 TO b2
//
USE SCHEMAMAP myschemamap
//
IMPORT SCHEMA a1 ...
//

The data from schema a1 is mapped onto schema b1. This setting is valid for the entire Loader session (USE SCHEMAMAP), as long as you do not specify a new USE SCHEMAMAP command or specify another schema map in the IMPORT SCHEMA command. You enter the IMPORT SCHEMA command with the usual syntax; in other words, you specify the schema name of the exported data in the import command.

IMPORT SCHEMA a1 ...
  MAP SCHEMA a1 TO b1, a2 TO b2
  CATALOG INSTREAM 'a1_csv.catalog'

The schema mapping a1 TO b1, a2 TO b2 is only valid for this IMPORT SCHEMA command. The schema map is created implicitly and then immediately deleted after the command is executed. A CREATE SCHEMAP statement is not necessary.

IMPORT SCHEMA a1 ...
  MAP SCHEMA myschemamap
  CATALOG INSTREAM 'a1_csv.catalog'

The schema mapping is only valid for this IMPORT SCHEMA command. A schema map (myschemamap) that was predefined by CREATE SCHEMAMAP is used here.

Explanation

If you create a schema map with the CREATE SCHEMAMAP statement, the schema map name and the associated schema names are stored in system table MAPSCHEMANAMES. The associated schemas need to exist.

For each schema in a database instance, there is a DEFAULT schema map with the name ID that this schema maps onto itself.

The DROP SCHEMAMAP statement deletes the entries of the schema map specified in the statement from the MAPSCHEMANAMES table.

You can use the schema map of your choice by means of the USE SCHEMAMAP command or the IMPORT SCHEMA command.

See also:

MAPSCHEMANAMES

USE SCHEMAMAP Command

IMPORT SCHEMA Command