MySQL datatype support - aws_mainframe_modernization_service - connect_cdc_mimix_share - Latest

AWS Mainframe Modernization - Data Replication for IBM i
Product type
Software
Portfolio
Integrate
Product family
Connect
Product
AWS Mainframe Modernization > AWS Mainframe Modernization Service
Version
Latest
Language
English
Product name
AWS Mainframe Modernization
Title
AWS Mainframe Modernization - Data Replication for IBM i
Copyright
2024
First publish date
2003
Last updated
2024-02-01
Published on
2024-02-01T23:02:31.099696

The following tables show the MySQL (as target-only) datatype support.

MySQL Target-only Datatype

Supported Sources

BIGINT

(8 bytes)

DB2 (includes IBM i and UDB)

  • BIGINT

  • DECIMAL*

  • FLOAT*

  • INTEGER

  • NUMERIC*

  • REAL*

  • SMALLINT

MS SQL Server

  • BIGINT

  • BIT

  • DECIMAL*

  • DOUBLE PRECISION*

  • FLOAT*

  • INTEGER

  • MONEY*

  • NUMERIC*

  • REAL*

  • SMALLINT

  • SMALLMONEY*

  • TINYINT

Oracle

  • DECIMAL*

  • DOUBLE PRECISION*

  • FLOAT*

  • INTEGER

  • NUMBER*

  • NUMERIC*

  • REAL*

  • SMALLINT

*These mappings are allowed with a warning in the Director; even though values for these data types may cause an overflow or may be incompatible when applying to the target, resulting in an error. When an incorrect value is encountered the behavior depends on the SQL_MODE setting currently in effect for the Applier’s session.

MySQL Target-only Datatype

Supported Sources

BINARY (m)

(M = number of bytes, max 255)

Db2 (includes IBM i and UDB)

  • CHAR FOR BIT

  • BLOB (IBM i) *

MS SQL Server

  • BINARY*

  • VARBINARY(MAX)*

 

MySQL Target-only Datatype

Supported Sources

BIT (m)

M = number of bits, max 64

 

Db2 (includes IBM i and UDB)

  • BIGINT*

  • DECIMAL*

  • DOUBLE*

  • FLOAT*

  • INTEGER*

  • NUMERIC*

  • REAL*

  • SMALLINT*

MS SQL Server

  • BIGINT*

  • BIT

  • DECIMAL*

  • DOUBLE PRECISION*

  • FLOAT*

  • INTEGER*

  • MONEY*

  • NUMERIC*

  • REAL*

  • SMALLINT*

  • SMALLMONEY*

  • TINYINT

Oracle

  • DECIMAL*

  • DOUBLE PRECISION*

  • FLOAT*

  • INTEGER*

  • NUMBER*

  • NUMERIC*

  • REAL*

  • SMALLINT.

*These mappings are allowed with a warning in the Director; even though values for these data types may cause an overflow or may be incompatible when applying to the target, resulting in an error. When an incorrect value is encountered the behavior depends on the SQL_MODE setting currently in effect for the Applier’s session.

MySQL Target-only Datatype

Supported Sources

BLOB 65536 bytes

Db2 (includes IBM i and UDB)

  • CHAR FOR BIT

  • BLOB (IBM i)*

MS SQL Server

  • BINARY

  • VARBINARY

Oracle

  • BLOB*

*These mappings are allowed with a warning in the Director; even though values for these data types may cause an overflow or may be incompatible when applying to the target, resulting in an error. When an incorrect value is encountered the behavior depends on the SQL_MODE setting currently in effect for the Applier’s session.

MySQL Target-only Datatype

Supported Sources

CHAR and VARCHAR TEXT 65536 bytes, TINYTEXT 256 bytes

Db2 (includes IBM i and UDB)

  •    BIGINT

  •    CHAR

  •    CHAR FOR BIT. (Not valid for UDB).

  •    CLOB. (Not valid for UDB).

  •    DATE

  •    DECIMAL

  •    DOUBLE

  •    FLOAT

  •    INTEGER

  •    NUMERIC

  •    REAL

  •    SMALLINT

  •    TIME

  •    TIMESTAMP

  •    VARCHAR

  •    VARCHAR FOR BIT. (Not valid for UDB).

CHAR and VARCHAR

 

MS SQL Server

  •    BIGINT

  •    BIT

  •       CHAR

  •    DATE

  •    DATETIME

  •    DATETIME2(P)

  •    DATETIMEOFFSET(P)

  •    DECIMAL

  •    DOUBLE PRECISION

  •    FLOAT

  •    INTEGER

  •    MONEY

  •    NUMERIC

  •    REAL

  •    SMALLDATETIME

  •    SMALLINT

  •    SMALLMONEY

  •    TIME

  •    TINYINT

  •    VARCHAR

  •    UNIQUEIDENTIFIER

Oracle

  •       CHAR

  •    DATE

  •    DECIMAL

  •    DOUBLE PRECISION

  •    FLOAT

  •    INTEGER

  •    NUMBER

  •    NUMERIC

  •    REAL

  •    SMALLINT

  •    TIMESTAMP

  •    VARCHAR

  •    VARCHAR2

MySQL Target-only Datatype

Supported Sources

CHAR (Unicode), VARCHAR (Unicode), NCHAR, NVARCHAR

 

Db2 (includes IBM i and UDB)

  • BIGINT

  • CHAR

  • CHAR (Unicode)

  • CHAR FOR BIT (Treat as CHARACTER)

  • CLOB. (Not valid for IBM i).

  • DBCLOB. (Not valid for UDB).

  • DBCLOB (Unicode)

  • DATE

  • DECIMAL

  • DOUBLE

  • FLOAT

  • GRAPHIC

  • GRAPHIC (Unicode)

  • INTEGER

  • NUMERIC

  • REAL

  • SMALLINT

  • TIME

  • TIMESTAMP

MS SQL Server

  •    BIGINT

  •    BIT

  •    CHAR

  •    DATE

  •    DATETIME

  •    DATETIME2(P)

  •    DATETIMEOFFSET(P)

  •    DECIMAL

  •    DOUBLE PRECISION

  •    FLOAT

  •    INTEGER

  •    MONEY

  •    NCHAR

  •    NTEXT

  •    NVARCHAR

  •    NVARCHAR(MAX)

  •    NUMERIC

  •    REAL

  •    SMALLDATETIME

  •    SMALLINT

  •    SMALLMONEY

  •    TIME

  •    TINYINT

  •    VARCHAR

  •    UNIQUEIDENTIFIER

Oracle

  •    CHAR

  •    DATE

  •    DECIMAL

  •    DOUBLE PRECISION

  •    FLOAT

  •    INTEGER

  •    NCHAR

  •    NCLOB

  •    NVARCHAR2

  •    NUMBER

  •    NUMERIC

  •    REAL

  •    SMALLINT

  •    TIMESTAMP

  •    VARCHAR

  •    VARCHAR2

MySQL Target-only Datatype

Supported Sources

DATE

DB2 (includes IBM i and UDB)

  • DATE

  • TIMESTAMP

MS SQL Server

  • DATE

  • DATETIME

  • DATETIME2(P)

  • DATETIMEOFFSET(P)

  • SMALLDATETIME
Oracle

  • DATE

  • TIMESTAMP
Note: The standard display format for the MySQL DATE datatype is YYYY-MM-DD. Data is accepted without the embedded hyphens.

MySQL Target-only Datatype

Supported Sources

DATETIME (p)

(Maximum of 6 fractional

seconds)

Db2 (includes IBM i and UDB)

  • DATE

  • TIMESTAMP

DATETIME (p)

MS SQL Server

  • DATE

  • DATETIME

  • DATETIME2(P)

  • DATETIMEOFFSET(P)

  • SMALLDATETIME. Time generated as all zero's.

  • TIME(P)

Oracle

  • DATE

  • TIMESTAMP
Note: The format for the DATETIME column is YYYY-MM-DD HH:MM:SS[.fraction]. Unlike the TIMESTAMP column, the value is not stored in UTC.

MySQL Target-only Datatype

Supported Sources

DECIMAL (p,s) and NUMERIC(p,s)

Db2 (includes IBM i and UDB)

  • BIGINT

  • DECIMAL

  • DOUBLE

  • FLOAT

  • INTEGER

  • NUMERIC (p,s)

  • REAL

  • SMALLINT.

MS SQL Server

  • BIGINT

  • BIT

  • DECIMAL

  • DOUBLE PRECISION

  • FLOAT

  • INTEGER

  • MONEY

  • NUMERIC

  • REAL

  • SMALLINT

  • SMALLMONEY

  • TINYINT

Oracle

  • DECIMAL

  • DOUBLE PRECISION

  • FLOAT

  • INTEGER

  • NUMBER

  • NUMERIC

  • REAL

  • SMALLINT
Note: The data types DEC and NUMERIC are aliases for DECIMAL. All of the supported source datatypes may result in run time errors, depending on the precision and scale specified on the target decimal columns. When an incorrect value is encountered, the behavior depends on the SQL_MODE setting currently in effect for the Applier's session. The Director should use the precision and scale to determine when a warning should be issued.

MySQL Target-only Datatype

Supported Sources

DOUBLE (p,s), FLOAT (p,s), REAL (p,s)

Db2 (includes IBM i and UDB)

  • BIGINT

  • DECIMAL

  • DOUBLE

  • FLOAT

  • INTEGER

  • NUMERIC

  • REAL

  • SMALLINT

MS SQL Server

  • BIGINT

  • BIT

  • DECIMAL

  • DOUBLE PRECISION

  • FLOAT

  • INTEGER

  • MONEY

  • NUMERIC

  • REAL

  • SMALLMONEY

  • SMALLINT

  • TINYINT

Oracle

  • DECIMAL

  • DOUBLE PRECISION

  • FLOAT

  • INTEGER

  • NUMBER

  • NUMERIC

  • REAL

  • SMALLINT

MySQL Target-only Datatype

Supported Sources

INT

 

Db2 (includes IBM i and UDB)

  • BIGINT *

  • DOUBLE *

  • FLOAT*

  • INTEGER

  • NUMERIC*

  • REAL*

  • SMALLINT

MS SQL Server

  • BIGINT*

  • BIT

  • DECIMAL*

  • DOUBLE PRECISION*

  • FLOAT*

  • INTEGER

  • MONEY*

  • NUMERIC*

  • REAL*

  • SMALLMONEY*

  • SMALLINT

  • TINYINT

Oracle

  • DECIMAL*

  • DOUBLE PRECISION*

  • FLOAT*

  • INTEGER

  • NUMBER*

  • NUMERIC*

  • REAL*

  • SMALLINT

*These mappings are allowed with a warning in the Director; even though values for these data types may cause an overflow or may be incompatible when applying to the target, resulting in an error. When an incorrect value is encountered the behavior depends on the SQL_MODE setting currently in effect for the Applier’s session.

 

MySQL Target-only Datatype

Supported Sources

LONGBLOB

(4,294,967,296 bytes)

Db2 (includes IBM i and UDB)

  • CHAR FOR BIT

  • BLOB (IBM i)

MS SQL Server

  • BINARY

  • IMAGE

  • VARBINARY

  • VARBINARY(MAX)

Oracle

  • BLOB

MySQL Target-only Datatype

Supported Sources

LONGTEXT 4,294,967,296 bytes, MEDIUMTEXT   16,777,216 bytes TINYTEXT

 

 

Db2 (includes IBM i and UDB)

  • CHAR

  • CHAR (Unicode)

  • CHAR FOR BIT (Treat as CHARACTER). (Not valid for UDB).

  • CLOB. (Not valid for UDB). (Not valid for TINYTEXT for IBM i).

  • DBCLOB. (Not valid for UDB)

  • DBCLOB (Unicode)

  • GRAPHIC

  • GRAPHIC (Unicode)

  • VARCHAR

  • VARGRAPHIC

  • VARCHAR FOR BIT. (Not valid for UDB).

MS SQL Server

  • CHAR

  • NCHAR

  • NTEXT

  • NVARCHAR

  • NVARCHAR(MAX)

  • TEXT

  • VARCHAR

  • VARCHAR (MAX).

Oracle

  • CHAR

  • CLOB. (Not valid for TINYTEXT).

  • NCHAR

  • NCLOB

  • NVARCHAR2

  • VARCHAR

  • VARCHAR2
Note: MySQL has four different TEXT types for character-based large objects, where each type differs only in size. A TEXT type holds a variable size character string that is defined with a character set. When applying to one of the BLOB types, if the target value is larger than the target column allows, the value is truncated to fit the target column.

MySQL Target-only Datatype

Supported Sources

LONGTEXT (Unicode) and MEDIUMTEXT (Unicode) TINYTINYTEXT

 

Db2 (includes IBM i and UDB)

  • CHAR a

  • DBCLOB ((Unicode for IBM i only)

  • GRAPHIC a

  • VARCHAR a

  • VARGRAPHIC a

MS SQL Server

  • NCHAR a

  • NTEXT

  • NVARCHAR a

  • NVARCHAR(MAX)

Oracle

  • NCHAR a

  • NVARCHAR2 a 
aIndicates that Connect CDC Director will issue a warning of possible trun­cation of data at the target.

MySQL Target-only Datatype

Supported Sources

MEDIUMBLOB

16,777,216 bytes

Db2 (includes IBM i and UDB)

  • CHAR FOR BIT

  • BLOB (IBM i)*

MS SQL Server

  • BINARY

  • IMAGE*

  • VARBINARY

  • VARBINARY(MAX)*

Oracle

  • BLOB*

*These mappings are allowed with a warning in the Director; even though values for these data types may cause an overflow or may be incompatible when applying to the target, resulting in an error. When an incorrect value is encountered the behavior depends on the SQL_MODE setting currently in effect for the Applier’s session.

MySQL Target-only Datatype

Supported Sources

MEDIUMINT

(3 bytes)

Db2 (includes IBM i and UDB)

  • BIGINT *

  • DECIMAL *

  • DOUBLE *

  • FLOAT*

  • INTEGER

  • NUMERIC*

  • REAL*

  • SMALLINT

MS SQL Server

  • BIGINT*

  • BIT

  • DECIMAL*

  • DOUBLE PRECISION*

  • FLOAT*

  • INTEGER

  • MONEY*

  • NUMERIC*

  • REAL*

  • SMALLMONEY*

  • SMALLINT

  • TINYINT

Oracle

  • DECIMAL*

  • DOUBLE PRECISION*

  • FLOAT*

  • INTEGER

  • NUMBER*

  • NUMERIC*

  • REAL*

  • SMALLINT

*These mappings are allowed with a warning in the Director; even though values for these data types may cause an overflow or may be incompatible when applying to the target, resulting in an error. When an incorrect value is encountered the behavior depends on the SQL_MODE setting currently in effect for the Applier’s session.

MySQL Target-only Datatype

Supported Sources

SMALLINT

(2 bytes)

Db2 (includes IBM i and UDB)

  • BIGINT *

  • DECIMAL *

  • DOUBLE *

  • FLOAT*

  • INTEGER

  • NUMERIC*

  • REAL*

  • SMALLINT

SMALLINT

(3 bytes)

MS SQL Server

  • BIGINT*

  • BIT

  • DECIMAL*

  • DOUBLE PRECISION*

  • FLOAT*

  • INTEGER

  • MONEY*

  • NUMERIC*

  • REAL*

  • SMALLMONEY*

  • SMALLINT

  • TINYINT

Oracle

  • DECIMAL*

  • DOUBLE PRECISION*

  • FLOAT*

  • INTEGER

  • NUMBER*

  • NUMERIC*

  • REAL*

  • SMALLINT

*These mappings are allowed with a warning in the Director; even though values for these data types may cause an overflow or may be incompatible when applying to the target, resulting in an error. When an incorrect value is encountered the behavior depends on the SQL_MODE setting currently in effect for the Applier’s session.

 

MySQL Target-only Datatype

Supported Sources

TIME(P)

(Maximum of 6 fractional seconds)

Db2 (includes IBM i and UDB)

  • TIME

  • TIMESTAMP

MS SQL Server

  • DATETIME

  • DATETIME2(P)

  • DATETIMEOFFSET(P)

  • SMALLDATETIME

  • TIME

  • TIME(P)

Oracle

  • DATE

  • TIMESTAMP

MySQL Target-only Datatype

Supported Sources

TIMESTAMP

(Maximum of 6 fractional seconds)

Db2 (includes IBM i and UDB)

  • DATE

  • TIMESTAMP

MS SQL Server

  • DATE

  • DATETIME

  • DATETIME2(P)

  • DATETIMEOFFSET(P)

  • SMALLDATETIME

  • TIME(P)

Oracle

  • DATE

  • TIMESTAMP

MySQL Target-only Datatype

Supported Sources

TIMESTAMP (P)

Db2 (includes IBM i and UDB)

  • DATE

  • TIME

  • TIMESTAMP

MS SQL Server

  • DATE

  • DATETIME

  • DATETIME2(P)

  • DATETIMEOFFSET(P)

  • SMALLDATETIME

  • TIME

  • TIME(P)

Oracle

  • DATE

  • TIMESTAMP

  • TIMESTAMP (P)

MySQL Target-only Datatype

Supported Sources

TINYBLOB

(256 bytes)

Db2 (includes IBM i and UDB)

  • CHAR FOR BIT

  • BLOB (IBM i)*

MS SQL Server

  • BINARY

  • VARBINARY

*These mappings are allowed with a warning in the Director; even though values for these data types may cause an overflow or may be incompatible when applying to the target, resulting in an error. When an incorrect value is encountered the behavior depends on the SQL_MODE setting currently in effect for the Applier’s session.

MySQL Target-only Datatype

Supported Sources

TINYINT

(1 byte)

Db2 (includes IBM i and UDB)

  • BIGINT *

  • DOUBLE *

  • FLOAT*

  • INTEGER

  • NUMERIC*

  • REAL*

  • SMALLINT

MS SQL Server

  • BIGINT*

  • BIT

  • DECIMAL*

  • DOUBLE PRECISION*

  • FLOAT*

  • INTEGER*

  • MONEY*

  • NUMERIC*

  • REAL*

  • SMALL MONEY*

  • SMALLINT*

  • TINYINT

Oracle

  • DECIMAL*

  • DOUBLE PRECISION*

  • FLOAT*

  • INTEGER

  • NUMBER*

  • NUMERIC*

  • REAL*

  • SMALLINT

*These mappings are allowed with a warning in the Director; even though values for these data types may cause an overflow or may be incompatible when applying to the target, resulting in an error. When an incorrect value is encountered the behavior depends on the SQL_MODE setting currently in effect for the Applier’s session.

MySQL Target-only Datatype

Supported Sources

VARBINARY (m)

M = number of bytes, max 65,535

Db2 (includes IBM i and UDB)

  • CHAR FOR BIT

  • BLOB ( IBM i ) *

MS SQL Server

  • BINARY

*These mappings are allowed with a warning in the Director; even though values for these data types may cause an overflow or may be incompatible when applying to the target, resulting in an error. When an incorrect value is encountered the behavior depends on the SQL_MODE setting currently in effect for the Applier’s session.

MySQL Target-only Datatype

Supported Sources

YEAR

(Precision indicates number of digits. Can be either 2 or 4)

Db2 (includes IBM i and UDB)

  • DATE

  • TIMESTAMP

MS SQL Server

  • DATE

  • DATETIME

  • DATETIME2(P)

  • DATETIMEOFFSET(P)

  • SMALLDATETIME

Oracle

  • DATE

  • TIMESTAMP