SQL Reference

CREATE TYPE MAPPING

The CREATE TYPE MAPPING statement creates a mapping between these data types:

A data type of a column of a data source table or view that is going to be defined to a federated database
A corresponding data type that is already defined to the federated database.

The mapping can associate the federated database data type with a data type at either (1) a specified data source or (2) a range of data sources; for example, all data sources of a particular type and version.

A data type mapping has to be created only if an existing one is not adequate.

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. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must have SYSADM or DBADM authority.

Syntax

>>-CREATE TYPE MAPPING--+-------------------+---FROM--| local-data-type |-->
                        '-type-mapping-name-'
 
>---TO----| remote-server |--TYPE--data-source-data-type-------->
 
>-----+--------------------------------------------+-----------><
      +-FOR BIT DATA-------------------------------+
      '-(--+-p-------+---+---------+---)--+------+-'
           '-[p..p]--'   +-,s------+      +-P=S--+
                         '-,[s..s]-'      +-P>S--+
                                          +-P<S--+
                                          +-P>=S-+
                                          +-P<=S-+
                                          '-P<>S-'
 
local-data-type
 
|---+-SMALLINT----------------------------------------------------------------+->
    +-+-INTEGER-+-------------------------------------------------------------+
    | '-INT-----'                                                             |
    +-BIGINT------------------------------------------------------------------+
    +-+-FLOAT--+----------------+-+-------------------------------------------+
    | |        '-(--integer--)--' |                                           |
    | +-REAL----------------------+                                           |
    | |        .-PRECISION-.      |                                           |
    | '-DOUBLE-+-----------+------'                                           |
    +--+-DECIMAL-+---+--------------------------------+-----------------------+
    |  +-DEC-----+   '-(--integer--+-----------+---)--'                       |
    |  +-NUMERIC-+                 '-,integer--'                              |
    |  '-NUM-----'                                                            |
    +--+--+-CHARACTER-+---+------------+----------------+---+---------------+-+
    |  |  '-CHAR------'   '-(integer)--'                |   '-FOR BIT DATA--' |
    |  '--+-VARCHAR-------------------+--(--integer--)--'                     |
    |     '--+-CHARACTER-+---VARYING--'                                       |
    |        '-CHAR------'                                                    |
    +-GRAPHIC--+------------+-------------------------------------------------+
    |          '-(integer)--'                                                 |
    +-VARGRAPHIC--(integer)---------------------------------------------------+
    +-DATE--------------------------------------------------------------------+
    +-TIME--------------------------------------------------------------------+
    '-TIMESTAMP---------------------------------------------------------------'
 
>---------------------------------------------------------------|
 
remote-server
 
|---+-SERVER--server-name-------------------------------------------------------------------------+->
    '-SERVER TYPE--server-type----+-----------------------------------------------------------+---'
                                  '-VERSION--| server-version |--+-------------------------+--'
                                                                 '-WRAPPER--wrapper-name---'
 
>---------------------------------------------------------------|
 
server-version
 
|---+-version--+--------------------------+-+-------------------|
    |          '-.--release--+---------+--' |
    |                        '-.--mod--'    |
    '-version-string-constant---------------'

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.

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 on the SQL path (defined by the FUNCPATH preprocessing option for static SQL and by the CURRENT PATH register for dynamic SQL).

The local-data-type cannot be LONG VARCHAR, LONG VARGRAPHIC, DATALINK, a large object (LOB) type, or a user-defined type.

SERVER server-name

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

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. version must be an integer.
release: Specifies the number of the release of the version denoted by version. release must be an integer.
mod: Specifies the number of the modification of the release denoted by release. mod 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.

data-source-data-type

Specifies the data source data type that is being mapped to local-data-type. If data-source-data-type is qualified by a schema name at the data source, it is allowable, but not required, to specify this qualifier.

The data-source-data-type must be a built-in data type. If the type has a short and long form (for example, CHAR and CHARACTER), the short form should be specified.

p

For a decimal data type, p specifies the maximum number of digits that a value can have. For all other data types for character data, p specifies the maximum number of characters that a value can have. The p must be valid with respect to the data type (SQLSTATE 42611). If p is not specified and the data type requires it, the system will determine the best match.

[p..p]

For a decimal data type, [p..p] specifies the minimum and maximum number of digits that a value can have. For all other data types for character data, [p..p] specifies 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 (SQLSTATE 42611).

s

For a decimal data type, s specifies the allowable maximum number of digits to the right of the decimal point. This number must be valid with respect to the data type (SQLSTATE 42611). If a number is not specified and the data type requires one, the system will determine the best match.

[s..s]

For a decimal data type, [s..s] specifies 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 (SQLSTATE 42611).

P [operand] S

For a decimal data type, P [operand] S specifies a comparison between the maximum allowable precision and the maximum number of digits allowed to the right of the decimal point. For example, the operand = indicates that the maximum allowable precision and the maximum number digits allowed in the decimal fraction are the same. Specify P [operand] S only if the level of checking that it enforces is required.

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 on a character data type.

Notes

A CREATE TYPE MAPPING statement within a given unit of work (UOW) cannot be processed under either of the following conditions:

The statement references a single data source, and the UOW already includes a SELECT statement that references 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 a SELECT statement that references a nickname for a table or view within one of these data sources.

Examples

Example 1: Create a mapping between SYSIBM.DATE and the Oracle data type DATE at all Oracle data sources.

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

Example 2: Create a mapping between SYSIBM.DECIMAL(10,2) and the Oracle data type NUMBER([10..38],2) at data source ORACLE1.

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

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]