CREATE TYPE MAPPING statement

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include DBADM authority.

Syntax

local-data-type

built-in-type

remote-server

server-version

Notes:

• ¹ Both a TO and a FROM keyword must be present in the CREATE TYPE MAPPING statement.

Description

type-mapping-name

Names the data type mapping. The name must not identify a data type mapping that is already described in the catalog. A unique name is generated if type-mapping-name is not specified.

FROM or TO

Specifies a reverse or forward type mapping.

FROM

Specifies a forward type mapping when followed by local-data-type or a reverse type mapping when followed by remote-server.

TO

Specifies a forward type mapping when followed by remote-server or a reverse type mapping when followed by local-data-type.

local-data-type

Identifies a data type that is defined to a federated database. If local-data-type is specified without a schema name, the type name is resolved by searching the schemas in the SQL path.

Empty parentheses can be used for the parameterized data types. A parameterized data type is any one of the data types that can be defined with a specific length, scale, or precision. If empty parentheses are specified in a forward type mapping, such as, for example, CHAR(), the length is determined from the column length on the remote table. If empty parentheses are specified in a reverse type mapping, the type mapping is applied to the data type with any length. If you omit parentheses altogether, the default length for the data type is used.

FLOAT() cannot be used (SQLSTATE 42601), because the parameter value indicates different data types (REAL or DOUBLE). NUMBER() cannot be used (SQLSTATE 42601), because the parameter value indicates different data types (DECFLOAT or DECIMAL).

DECFLOAT can be accepted only as the local-data-type by Oracle wrapper, Db2® wrapper for IBM® Db2 Version 9.5 or later.

The local-data-type cannot be a user-defined type (SQLSTATE 42611).

built-in-type

See "CREATE TABLE" for the description of built-in data types.

SERVER server-name

Names the data source to which data-source-data-type is defined.

SERVER TYPE server-type

Identifies the type of data source to which data-source-data-type is defined.

VERSION

Identifies the version of the data source to which data-source-data-type is defined.

version

Specifies the version number. The value must be an integer.

release

Specifies the number of the release of the version denoted by version. The value must be an integer.

mod

Specifies the number of the modification of the release denoted by release. The value must be an integer.

version-string-constant

Specifies the complete designation of the version. The version-string-constant can be a single value (for example, '8i'); or it can be the concatenated values of version, release and, if applicable, mod (for example, '8.0.3').

WRAPPER wrapper-name

Specifies the name of the wrapper that the federated server uses to interact with data sources of the type and version denoted by server-type and server-version.

TYPE data-source-data-type

Specifies the data source data type that is being mapped to or from the local data type.

Empty parentheses can be used for the parameterized data types. If empty parentheses are specified in a forward type mapping, such as, for example, CHAR(), the type mapping is applied to the data type with any length. If empty parentheses are specified in a reverse type mapping, the length is determined from the column length specified in the transparent DDL. If you omit parentheses altogether, the default length for the data type is used.

The data-source-data-type must be a built-in data type. User-defined types are not allowed.

If server-name is specified with a type mapping, or existing servers are affected by the type mapping, data-source-data-type, p, and s are verified when creating the type mapping (SQLSTATE 42611).

p

If p is specified, only the data type whose length or precision equals p is affected by the type mapping.

[p1..p2]

For forward type mapping only. For a decimal data type, p1 and p2 specify the minimum and maximum number of digits that a value can have. For string data types, p1 and p2 specify the minimum and maximum number of characters that a value can have. In all cases, the maximum must equal or exceed the minimum; and both numbers must be valid with respect to the data type.

s

If s is specified, only the data type whose scale equals s is affected by the type mapping.

[s1..s2]

For forward type mapping only. For a decimal data type, s1 and s2 specify the minimum and maximum number of digits allowed to the right of the decimal point. The maximum must equal or exceed the minimum, and both numbers must be valid with respect to the data type.

P [operand] S

For a decimal data type, P [operand] S specifies a comparison between the precision and the number of digits allowed to the right of the decimal point. For example, the operand = indicates that the type mapping is applied if the precision and the number of digits allowed in the decimal fraction are the same.

FOR BIT DATA

Indicates whether data-source-data-type is for bit data. These keywords are required if the data source type column contains binary values. The database manager will determine this attribute if it is not specified for a character data type.

Notes

• A CREATE TYPE MAPPING statement within a given unit of work (UOW) cannot be processed (SQLSTATE 55007) under either of the following conditions:
  • The statement references a single data source, and the UOW already includes one of the following:
    • A SELECT statement that references a nickname for a table or view within this data source
    • An open cursor on a nickname for a table or view within this data source
    • Either an INSERT, DELETE, or UPDATE statement issued against a nickname for a table or view within this data source
  • The statement references a category of data sources (for example, all data sources of a specific type and version), and the UOW already includes one of the following:
    • A SELECT statement that references a nickname for a table or view within one of these data sources
    • An open cursor on a nickname for a table or view within one of these data sources
    • Either an INSERT, DELETE, or UPDATE statement issued against a nickname for a table or view within one of these data sources
• When multiple type mappings are applicable, the most recent one will be used. You can retrieve the creation time for a type mapping by querying the CREATE_TIME column of the SYSCAT.TYPEMAPPINGS catalog view.
• BINARY and VARBINARY types are not supported in a Federated system.

Examples

1. Create a forward type mapping between the Oracle data type DATE and the data type SYSIBM.DATE. For all of the nicknames that are created after this mapping is defined, Oracle columns of data type DATE will map to Db2 columns of data type DATE.

      CREATE TYPE MAPPING MY_ORACLE_DATE
        FROM LOCAL TYPE SYSIBM.DATE
        TO SERVER TYPE ORACLE
        REMOTE TYPE DATE

2. Create a forward type mapping between data type SYSIBM.DECIMAL(10,2) and the Oracle data type NUMBER([10..38],2) at data source ORACLE1. If there is a column in the Oracle table of data type NUMBER(11,2), it will be mapped to a column of data type DECIMAL(10,2), because 11 is between 10 and 38.

      CREATE TYPE MAPPING MY_ORACLE_DEC
        FROM LOCAL TYPE SYSIBM.DECIMAL(10,2)
        TO SERVER ORACLE1
        REMOTE TYPE NUMBER([10..38],2)

3. Create a forward type mapping between data type SYSIBM.VARCHAR(p) and the Oracle data type CHAR(p) at data source ORACLE1 (p is any length). If there is a column in the Oracle table of data type CHAR(10), it will be mapped to a column of data type VARCHAR(10).

      CREATE TYPE MAPPING MY_ORACLE_CHAR
        FROM LOCAL TYPE SYSIBM.VARCHAR()
        TO SERVER ORACLE1
        REMOTE TYPE CHAR()

4. Create a reverse type mapping between the Oracle data type NUMBER(10,2) at data source ORACLE2 and data type SYSIBM.DECIMAL(10,2). If you use transparent DDL to create an Oracle table and specify a column of data type DECIMAL(10,2), the Oracle table will be created with a column of data type NUMBER(10,2).

      CREATE TYPE MAPPING MY_ORACLE_DEC
        TO LOCAL TYPE SYSIBM.DECIMAL(10,2)
        FROM SERVER ORACLE2
        REMOTE TYPE NUMBER(10,2)