Design Guide

10.5 HotFix 3
- 10.5 HotFix 2
- 10.5 HotFix 1
- 10.5
- 10.2 HotFix 1
- 10.2
- 10.1
- 10.0 HotFix 1
- 10.0

Back Next

Source Data Types

The following tables list the supported source data types and what they are converted to for storage in the IDT and IDX.

The first column shows the native data types that can be read from User Source Tables.

The second column shows the equivalent MDM-RE data type documented in the

Files & Views

chapter, Format & Data Types
section of this manual). Data read from USTs are converted to a common data type to enable combining source data from heterogeneous source database types. For example, an IDT stored on Oracle can be merged from source data read from UDB and MS-SQL Server.

When loading data from flat files instead of USTs, the File and View definitions are specified using these MDM-RE data types. When loading from a UST, files and views representing the UST and IDT are generated automatically.

The third column shows the mapping from MDM-RE data types back to native host DBMS data types. The IDT is stored as a native table on the target DBMS so that it may be queried using SQL. The IDX holds compressed keys and data (MDM-RE data types) and is not user accessible.

Oracle

UST Data Type	Data Type (IDX)	IDT Data Type
	F	VARCHAR2
CHAR VARCHAR2 CLOB NUMBER (scale > 0) DATE ² TIMESTAMP	C	VARCHAR2 /VARCHAR2 CLOB ¹
	V	VARCHAR2
RAW BLOB	B	RAW / BLOB³
	I	NUMBER(10) / RAW ⁴
	G	NUMBER / RAW ⁵
NUMBER (scale = 0) INT SMALLINT	N	NUMBER
	R	NUMBER
NCHAR NVARCHAR2 NCLOB	W	NVARCHAR2 /NCLOB ⁶
	X	CHAR
	Z	CHAR

¹VARCHAR2 for column lengths <= 4000, otherwise CLOB.

² DATEs and TIMESTAMPs are converted to C(64) fields by default. The length may be overridden. The default installation date mask determines the date format. This is specified either explicitly with the initialization parameter

NLS_DATE_FORMAT

or implicitly with the initialization parameter

NLS_TERRITORY

. It can also be set for a session with the ALTER SESSION command via a logon trigger defined for the SSA user.

³ RAW for column lengths <= 2000, otherwise BLOB.

⁴ NUMBER(10) if the length is 2 or 4, otherwise RAW.

⁵ NUMBER if the length is 2 or 4, otherwise RAW.

⁶ NVARCHAR2 for column lengths <= 4000, otherwise NCLOB.

UDB

UST Data Type	Data Type (IDX)	IDT Data Type
	F	VARCHAR
CHAR VARCHAR DATE⁸ TIMESTAMP ⁸ NUMBER (scale > 0)	C	VARCHAR / CLOB⁷
	V	VARCHAR
	B	VARCHAR FOR BIT DATA / BLOB ⁹
	I	INTEGER
	G	INTEGER
NUMBER (scale=0) DECIMAL INTEGER SMALLINT BIGINT ¹⁰	N	NUM
	R	NUM
	W	VARCHAR / DBCLOB ¹¹
	X	CHAR
	Z	CHAR

⁷VARCHAR for column lengths <= 32000, otherwise CLOB.

⁸DATEs and TIMESTAMPs are converted to C(64) fields by default.

⁹VARCHAR FOR BIT DATA for column lengths <= 32000, otherwise BLOB.

¹⁰Negative BIGINTs are not supported for use as a PK and/or join column.

¹¹VARCHAR for column lengths <= 32000, otherwise DBCLOB.

Microsoft SQL Server

UST Data Type	Data Type (IDX)	IDT Data Type
SQL_CHAR	F	VARCHAR / TEXT ¹²
SQL_VARCHAR SQL_DATE SQL_TIME SQL_TIMESTAMP SQL_TYPE_DATE SQL_TYPE_TIME SQL_TYPE_TIMESTAMP SQL_NUMERIC ¹³ SQL_DECIMAL¹³ SQL_FLOAT SQL_REAL SQL_DOUBLE SQL_GUID	C	VARCHAR / TEXT ¹²
SQL_BINARY SQL_VARBINARY SQL_BIT	B	VARBINARY / IMAGE¹⁴
SQL_NUMERIC ¹⁵ SQL_DECIMAL ¹⁵ SQL_INTEGER SQL_SMALLINT SQL_TINYINT SQL_BIGINT	N	DECIMAL
SQL_WCHAR	W	NVARCHAR / NTEXT ¹⁶
SQL_WVARCHAR