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 below as follows:
|
DBMS |
Abbreviation |
|---|---|
|
DB2(IBM i and UDB) |
DB2 |
|
MS SQL Server |
MSS |
|
MySQL |
MySQL |
|
Oracle |
ORA |
|
PostgreSQL |
PG |
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 |
|
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 INTERVAL • 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 fractional 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 fractional 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. |