Formats for source and target data - connect_cdc_mimix_share - 5.8

Connect CDC System Reference Guide

Product type
Software
Portfolio
Integrate
Product family
Connect
Product
Connect > Connect CDC (MIMIX Share)
Version
5.8
Language
English
Product name
Connect CDC
Title
Connect CDC System Reference Guide
Copyright
2024
First publish date
2003
ft:lastEdition
2024-02-12
ft:lastPublication
2024-02-12T15:11:59.486869

The formats used are generic and are not DBMS specific. The valid ranges of values vary by DBMS.

The table below describes the formats that Connect CDC Kernel expects as input and output from the Expression Handler component, and at the target for inserting into the target columns. The table also describes the valid ranges of values by DBMS.

The table below uses Connect CDC datatype categories that are described in Datatype conversion rules and used in the data conversion matrix in Datatype conversion matrix. DBMSs are abbreviated in the table below as follows:

DBMS

Abbreviation

DB2(IBM i and UDB)

DB2

MS SQL Server

MSS

Informix

INF

MySQL

MySQL

Oracle

ORA

PostgreSQL

PG

SQL Azure

AZ

Sybase

SYB

Teradata

TD

The following time and date symbols are used, each letter in the symbol representing a single data character:

Symbol

Description

CCYY

century and year

MM

number of the month

MMM

name of the month

DD

day of the month

HH

hours (24-hour format)

MI

minutes

SS

seconds

FFFFFF

fractional (decimal) parts of the second

Note: Datatypes in the binary category do not have a string format.

Target datatype category

Format of string sent to target
Character The number of characters in the target datatype definition.

Integral Numeric

A number of digits, optionally preceded by a sign (+/-). The integer value must

not exceed the size limitations of the target DBMS:

INTEGER/NUMBER(38)

• DB2: -2,147,438,648 to 2,147,438,647

• TD: -2,147,438,648 to 2,147,438,647

• INF: -2,147,438,648 to 2,147,438,647

• MSS / AZ: -2,147,438,648 to 2,147,438,647

• SYB: -2,147,438,648 to 2,147,438,647

• ORA: -938 to 938

• PG: -2,147,483,648 to +2,147,483,647

SMALLINT

• DB2: -32,768 to 32,767

• TD: -32,768 to 32,767

• INF: -32,768 to 32,767

• MSS / AZ: -32,768 to 32,767

• SYB: -32,768 to 32,767

• PG: -32,768 to +32,767

TINYINT

• MSS / AZ: 0 to 255

• SYB: 0 to 255

BIGINT

• MSS / AZ: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

• DB2 (IBM i): -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

• TD: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

• PG: -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807

BYTEINT

• TD: -128 to 127

Decimal Numeric

(.). The number value must not exceed the size limitations of the target DBMS:

MONEY

• INF (0<=p<=32): -930 to 930

• MSS / AZ: -922,337,203,685,477.5807 to 922,337,203,685,477.5807

• SYB: -922,337,203,685,477.5807 to 922,337,203,685,477.5807

• PG: -92,233,720,368,547,758.08 to +92,233,720,368,547,758.07

SMALLMONEY

• MSS / AZ: -214,748.3647 to 214,748.3647

• SYB: -214,748.3647 to 214,748.3647

DECIMAL/NUMERIC (p,s)/NUMBER (p,s)

• DB2 (0<=p<=31): -931 to 931

• INF (0<=p<=31): -931 to 931

• MSS / AZ (0<=p<=28): -928 to 928

• SYB (0<=p<=28): -928 to 928

• ORA (0<=p<=38): -938 to 938

• TD (0<=p<=38): -938 to 938

• PG: -1038 to 1038-1

Approximate Numeric

All values are sent as exponential notation (for example, +n.nnnnnnE+nn). The

number value must not exceed the size limitations of the target DBMS:

FLOAT/DOUBLE

• DB2 (16<=p<=53): ± 2.23E-308 to ± 1.79E+308

• INF: approximately 16 digits of precision

• MSS / AZ (16<=p<=32): ± 2.23E-308 to ± 1.79E+308

• SYB (16<=p<=32): ± 2.23E-308 to ± 1.79E+308

• PG: 15 digits of precision

FLOAT/DOUBLE PRECISION/REAL

• TD (15<=p<=53): ± 2.23E-308 to ± 1.79E+308

NUMBER

• ORA: ± 1.0E-130 to ± 9.938E75

SMALLFLOAT/REAL

• DB2/400 (1<=p<=15): ± 1.18E-38 to ± 3.4E+38

• INF: approximately 8 digits of precision

• MSS / AZ (16<=p<=32): ± 1.18E-38 to ± 3.4E+38

• SYB (16<=p<=32): ± 1.18E-38 to ± 3.4E+38

• PG: 6 digits of precision

Date/Interval

DATE

• DB2: CCYY-MM-DD ab

• TD: CCYY-MM-DD ab

• INF: CCYYMMDD a

• ORA: CCYY-MM-DD HH:MM:SS.FFFFFF abc

• MSS / AZ: CCYY-MM-DD

• PG: CCYY-MM-DDd

DATETIME

• INF: CCYYMMDDHHMISSFFFFFF ae

• MSS / AZ: CCYY-MM-DD HH:MM:SS.FFFFFF bfg

• SYB: CCYY-MM-DD HH:MM:SS.FFFFFF bfg

DATETIME2

• MSS / AZ: CCYY-MM-DD HH:MM:SS.FFFFFF bhi

DATETIMEOFFSET

• MSS / AZ: CCYY-MM-DD HH:MM:SS.FFFFFF bjk

SMALLDATETIME

• MSS / AZ: CCYY-MM-DD HH:MM:SS.FFFFFFbg1m

• SYB: CCYY-MM-DD HH:MM:SS.FFFFFF bgn

TIME

• DB2: HH:MM:SS b

TIMESTAMP

• DB2: CCYY-MM-DD HH:MM:SS.FFFFFF ab

TIME(P)

• TD: HH:MM:SS.FFFFFF bo

• MSS / AZ: HH:MM:SS.FFFFFFF

• PG: HH:MM:SS.FFF

TIMESTAMP(P)

• TD: CCYY-MM-DD HH:MM:SS.FFFFFF abo

• ORA: CCYY-MM-DD HH:MM:SS.FFFFFF

• PG: CCYY-MM-DD HH:MM:SS.FFF

INTERVALp

• INF: A string of digits formatted to match exactly the length and decimal

point position, if any, of the type of interval defined at the target.

a Supported range: 00010101 to 99991231; null if outside this range.

bAll punctuation removed when the target datatype is a character string.

cAll fractions of seconds are truncated at the target when inserting the value.

dSupported DATE range: 4713 BC to 5874897 AD; null if outside this range.

eIf you map DATETIME to DATE, you cannot specify this column as a Distribution Key.

fSupported DATE range: 17530101 to 99991231; null if outside this range.

gOnly the first three digits of the fractions of seconds are inserted at the target column. The last three digits are truncated.

hOnly the first three digits of the fractions of seconds are inserted at the target column. The last three digits are truncated.

iSupported DATE range: the lowest timestamp value is 0001-01-01 00:00:00.0000000 increasing (inclusively) up to the highest value of 9999-12-31 23:59:59.9999999. The frac­tional part of the seconds value supports up to 7 digits.

jOnly the first three digits of the fractions of seconds are inserted at the target column. The last three digits are truncated.

kSupported DATE range: the lowest timestamp value is 0001-01-01 00:00:00.0000000 increasing (inclusively) up to the highest value of 9999-12-31 23:59:59.9999999. The frac­tional part of the seconds value supports up to 7 digits.

lSupported DATE range: 19000101 to 20790606; null if outside this range.

mDoes not support fractional seconds.

nSupported DATE range: 19000101 to 20790606; null if outside this range.

oOptional precision defaults to six digits.

pSee also Date/Interval Category .