The
CREATE TYPE MAPPING statement defines a mapping between data types.
The mapping can be defined between the following data
types:
- The data type of a column in 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:
- A specified data source
- A range of data sources; for example, all data sources of a particular
type and version
A data type mapping must be created only if an existing
one is not adequate.
If multiple type mappings are applicable
when creating a nickname or creating a table (transparent DDL), the
most recent one is applied.
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
>>-CREATE TYPE MAPPING--+-------------------+--●---------------->
'-type-mapping-name-'
(1) .-LOCAL TYPE-.
>--------+-FROM-+--+------------+--| local-data-type |--●------->
'-TO---'
.-REMOTE-.
>--+-TO---+--| remote-server |--+--------+---------------------->
'-FROM-'
>--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
>>-| built-in-type |-------------------------------------------><
built-in-type
|--+-+-SMALLINT----+-------------------------------------------------------------+--|
| +-+-INTEGER-+-+ |
| | '-INT-----' | |
| '-BIGINT------' |
| .-(5,0)-------------------. |
+-+-+-DECIMAL-+-+--+-------------------------+--------------------------------+
| | '-DEC-----' | | .-,0-------. | |
| '-+-NUMERIC-+-' '-(integer-+----------+-)-' |
| '-NUM-----' '-,integer-' |
| .-(53)------. |
+-+-FLOAT--+-----------+--+---------------------------------------------------+
| | '-(integer)-' | |
| +-REAL------------------+ |
| | .-PRECISION-. | |
| '-DOUBLE--+-----------+-' |
| .-(1)-------------------. |
+-+-+-+-CHARACTER-+--+-----------------------+----------+--+--------------+-+-+
| | | '-CHAR------' '-(integer-+--------+-)-' | '-FOR BIT DATA-' | |
| | | '-OCTETS-' | | |
| | '-+-VARCHAR----------------+--(integer-+--------+-)-' | |
| | '-+-CHARACTER-+--VARYING-' '-OCTETS-' | |
| | '-CHAR------' | |
| | .-(1M)------------------------. | |
| '-+-CLOB------------------------+--+-----------------------------+--------' |
| '-+-CHARACTER-+--LARGE OBJECT-' '-(integer-+---+-+--------+-)-' |
| '-CHAR------' +-K-+ '-OCTETS-' |
| +-M-+ |
| '-G-' |
| .-(1)------------------------. |
+-+-GRAPHIC--+----------------------------+------+----------------------------+
| | '-(integer-+-------------+-)-' | |
| | '-CODEUNITS16-' | |
| +-VARGRAPHIC--(integer-+-------------+-)-------+ |
| | '-CODEUNITS16-' | |
| | .-(1M)-----------------------------. | |
| '-DBCLOB--+----------------------------------+-' |
| '-(integer-+---+-+-------------+-)-' |
| +-K-+ '-CODEUNITS16-' |
| +-M-+ |
| '-G-' |
| .-(1M)-------------. |
+-+-BLOB----------------+--+------------------+-------------------------------+
| '-BINARY LARGE OBJECT-' '-(integer-+---+-)-' |
| +-K-+ |
| +-M-+ |
| '-G-' |
+-+-DATE-------------------------+--------------------------------------------+
| +-TIME-------------------------+ |
| | .-(--6--)-------. | |
| '-TIMESTAMP--+---------------+-' |
| '-(--integer--)-' |
'-XML-------------------------------------------------------------------------'
remote-server
|--+-SERVER--server-name------------------------------------------------------------------+--|
'-SERVER TYPE--server-type--+--------------------------------------------------------+-'
'-VERSION--| server-version |--+-----------------------+-'
'-WRAPPER--wrapper-name-'
server-version
|--+-version--+------------------------+-+----------------------|
| '-.--release--+--------+-' |
| '-.--mod-' |
'-version-string-constant-------------'
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 for Linux, UNIX,
and Windows 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.
Examples
- 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
- 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)
- 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()
- 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), DB2 will create the Oracle table 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)
DB2 10.5 for Linux, UNIX, and Windows