Oracle spatial datatype limitations - 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 Oracle spatial datatypes have the following limitations:

  • The prepared statement option for a table is not allowed when one or more of the spatial datatype columns are mapped.

  • Spatial datatypes may not be specified as a distribution key column in a table mapping.

  • Spatial datatypes cannot be referenced in expressions.

  • Oracle spatial columns may only be replicated to target columns that are defined as either Geography or Geometry in MS SQL Server.

  • Connect CDC does not support spatial datatypes defined as SOLID or MULTISOLID

  • Only trigger based capture is supported for Oracle 11g.

  • Using ST_GEOMETRY objects after running the Oracle database upgrade script, sdoupggeom.sql, generates a size overflow exception. The ST_GEOMETRY overflow limitation is inherent to Oracle JDBC spatial support and occurs in all Oracle databases with an expanded ordinates array. This limitation does not apply to SDO_GEOMETRY objects.

Oracle Source Datatype

Supported Targets

SMALLINT

Db2 (includes IBM i and UDB)

  • BIGINT

  • DECIMAL(p,s)

  • FLOAT

  • INTEGER

  • SMALLINT

MS SQL Server/SQL Azure

  • BIGINT

  • BIT

  • DECIMAL(p,s)

  • FLOAT

  • INTEGER

  • SMALLINT

  • TINYINT

Oracle

  • NUMBER

  • NUMBER(38)

  • NUMBER (p,s). Connect CDC Director warns.

  • VARCHAR2 (L), where L>= 6.

All Platforms

  • CHAR (L), where L>= 6.

  • VARCHAR (L), where L>= 6.

 

Oracle Source Datatype

Supported Targets

TIMESTAMP

Note:  Oracle supports the TIMESTAMP datatype with a fractional second precision up to 9 digits. Connect CDC only a supports a fractional second precision up to a maximum of 6 digits, the default.

Db2 (includes IBM i and UDB)

  • TIMESTAMP

MS SQL Server/SQL Azure

  • DATETIME

  • DATETIME2(P)

  • DATETIMEOFFSET(P)

  • SMALLDATETIME

Oracle

  • TIMESTAMP

  • VARCHAR2 (L), where L >= 20. Format of target value is YYYYMMDDHHMMSSFFFFFF.

PostgreSQL

  • DATE

  • TIME

  • TIMESTAMP

All Platforms

  • CHAR (L)

  • VARCHAR (L)

where L >= 20. Format of target value is YYYYMMDDHHMMSSFFFFFF.

Note:

For the following datatypes, seconds and fractions of a second of time data are truncated.

  • DATETIME

  • DATETIME2(P)

  • DATETIMEOFFSET(P)

  • SMALLDATETIME

Oracle Source Datatype

Supported Targets

TIMESTAMP(P)

Db2 (includes IBM i and UDB)

  • TIMESTAMP

MS SQL Server/SQL Azure

  • DATETIME.

  • DATETIME2(P)

  • DATETIMEOFFSET(P)

  • SMALLDATETIME.

Oracle

  • TIMESTAMP

  • TIMESTAMP(P)

  • VARCHAR2 (L), where L >= 20. Format of target value is YYYYMMDDHHMMSSFFFFFF.

PostgreSQL

  • TIME

  • TIMESTAMP

All Platforms

  • CHAR (L)

  • VARCHAR (L)

where L >= 20. Format of target value is YYYYMMDDHHMMSSFFFFFF.

Note:

For the following datatypes, seconds and fractions of a second of time data are truncated.

  • DATETIME.

  • DATETIME2(P)

  • DATETIMEOFFSET(P)

  • SMALLDATETIME

Oracle Source Datatype

Supported Targets

VARCHAR

Db2 (includes IBM i and UDB)

  • CHAR (Unicode)

  • CLOB (IBM i only)

  • DBCLOB (Unicode IBM i only)

  • GRAPHIC (Unicode)

  • VARGRAPHIC (Unicode)
MS SQL Server/SQL Azure

Oracle

  • CLOB

  • LONG

  • VARCHAR2

All Platforms

  • CHAR

  • VARCHAR

Oracle Source Datatype

Supported Targets

VARCHAR2

Db2 (includes IBM i and UDB)

  • CHAR (Unicode)

  • CLOB (IBM i only)

  • DBCLOB (Unicode IBM i only)

  • GRAPHIC (Unicode)

  • VARGRAPHIC (Unicode)
MS SQL Server/SQL Azure

Oracle

  • CLOB

  • LONG

  • VARCHAR2

All Platforms

  • CHAR

  • VARCHAR

Oracle Source Datatype

Supported Targets

XMLTYPE

Db2 (includes IBM i and UDB)

  • CLOB

  • DBCLOB

MS SQL Server/SQL Azure

  • NTEXT

  • TEXT

  • NVARCHAR(MAX)

  • VARCHAR(MAX)

  • XML

Oracle

  • CLOB

  • NCCLOB

  • XMLTYPE