25 Understanding What's Supported for Sybase

This chapter contains information on database and table features supported by Oracle GoldenGate for Sybase.

Topics:

• Supported Sybase Data Types
  
• Non-Supported Sybase Data Types
  
• Supported Operations and Objects for Sybase
  
• Non-Supported Operations and Objects for Sybase
  

Supported Sybase Data Types

This section lists the Sybase data types that Oracle GoldenGate supports and any limitations of this support.

• Integers
  
• Floating-Point Numbers
  
• Character Data
  
• Dates and Timestamps
  
• Large Objects
  
• Money Types
  
• IDENTITY Type
  
• text, image, and unitext Data Types
  
• User-Defined Data Types
  

Integers

• BIGINT

• BIT

• DECIMAL

• INT (signed and unsigned)

• TINYINT (signed and unsigned)

• NUMERIC

• SMALLINT (signed and unsigned)

Limitations of Support

• NUMERIC and DECIMAL (fixed-point) are supported with no integrity loss when moving data to a target column of the same data type without involving calculations or transformation. When calculations or transformation must be performed, Oracle GoldenGate supports a maximum value of a signed long integer (32-bits).

• BIT is supported for automatic mapping between Sybase databases. To move BIT data between Sybase and another database type, Oracle GoldenGate treats BIT data as binary. In this case, the following are required:

  • The BIT column must be mapped to the corresponding source or target column with a COLMAP clause in a TABLE or MAP statement.

• For the Sybase 157 GA release, these data types cannot be replicated:

  • BIGINT (as a key column)

  • BIGDATETIME

  • BIGTIME

• When replicating TINYINT and Extract is not in the same version of Replicat, you will need to create a sourcedef and/or targetdef file even if you are replicating between identical Sybase versions.

• See also Non-Supported Sybase Data Types.

Floating-Point Numbers

• DOUBLE

• FLOAT

• REAL

Limitations of Support

The support of range and precision for floating-point numbers depends on the host machine. In general, the precision is accurate to 16 significant digits, but you should review the database documentation to determine the expected approximations. Oracle GoldenGate rounds or truncates values that exceed the supported precision.

Character Data

• CHAR

• NCHAR

• NVARCHAR

• VARCHAR

• UNICHAR

• UNIVARCHAR

Limitations of Support

• These data types are supported to the maximum length supported by the database, this being the maximum page size.

• Fetching NVARCHAR replication results using the Sybase char_length or datalength functions when a Sybase database is the target and the source is a heterogenous database and you replicate from the source to the target may result in a data integrity issue. This occurs when you use a Sybase release earlier than Adaptive Server Enterprise 15.5 for Windows x64 platform EBF 21262: 15.5 ESD #5.3.

Dates and Timestamps

• BIGDATETIME

• BIGTIME

• DATE

• DATETIME

• SMALLDATETIME

• TIME

Limitations of Support

• Oracle GoldenGate supports timestamp data from 0001/01/03:00:00:00 to 9999/12/31:23:59:59. If a timestamp is converted from GMT to local time, these limits also apply to the resulting timestamp. Depending on the time zone, conversion may add or subtract hours, which can cause the timestamp to exceed the lower or upper supported limit.

• Oracle GoldenGate does not support negative dates.

Large Objects

• BINARY

• IMAGE

• TEXT

• UNITEXT

• VARBINARY

Limitations of Support

• TEXT, UNITEXT and IMAGE are supported up to 2 GB in length.

• Large objects that are replicated from other databases (such as Oracle BLOB and CLOB) can be mapped to Sybase CHAR, VARCHAR, BINARY, and VARBINARY columns. To prevent Replicat from abending if the replicated large object is bigger than the size of the target column, use the DBOPTIONS parameter with the ALLOWLOBDATATRUNCATE option in the Replicat parameter file. For more information, see Reference for Oracle GoldenGate for Windows and UNIX.

• To move data to a Sybase target from a source database that permits empty LOB columns, use the DBOPTIONS parameter with the EMPTYLOBSTRING option in the Replicat parameter file. This parameter accepts a string value and prevents Replicat from setting the target column to NULL, which is not permitted by Sybase. For more information, see Reference for Oracle GoldenGate for Windows and UNIX.

• When a source table contains multiple identical rows, it can cause LOB inconsistencies in the target table. This occurs when the source table lacks a primary key or other unique row identifier. The rows are inserted by Replicat on the target, but if the LOB data is updated in a subsequent source operation, it will only be replicated to the first row that was inserted on the target.

• Do not use NOT NULL constraints on the in-row LOB column. If you want to use NOT NULL constraints, use them on the off-row LOB column.

• If you need to fetch the in-row LOB data directly from the table you must use FETCHCOLS/FETCHMODCOLS.

• Oracle GoldenGate for Sybase 15.7 does not support the in-row LOB column replication (however, it can still push the data into the in-row LOB column at in the Replicat database). This means tables included in the replication cannot have any in-row LOB columns. Oracle GoldenGate will abend if any replication table includes an in-row LOB column. If you need in-row LOB support, contact Oracle Support for further information.

Money Types

• MONEY

• SMALLMONEY

Limitations of Support

Money data types are supported with no integrity loss when moving data to a target column of the same data type without involving calculations or transformation. When calculations or transformation must be performed, Oracle GoldenGate supports a maximum value of a signed long integer (32-bits).

IDENTITY Type

The IDENTITY data type is supported for replication in one direction only, but not for a bi-directional configuration.

text, image, and unitext Data Types

With the Sybase 15.7 version, the LOB text, image, and unitext data types are now supported in BATCHSQL mode. The data length of the LOB is confined to 4K. If the records that contain LOB columns and the size exceeds more than 4K, then those records are excluded from the batches and are executed one at a time. The LOB columns in are now bound, while in previous Sybase version (15.5 or 15.0) the LOBs were not bound. You can use thee older behavior by using the DBPOTIONS LEGACYLOBREPLICATION parameter. This support is only applicable to Replicat running on Sybase version 15.7 and later.

User-Defined Data Types

User-defined data types are fully supported.

Non-Supported Sybase Data Types

This section lists the Sybase data types that Oracle GoldenGate does not support.

• The TIMESTAMP data is not supported. Timestamp columns data is captured though the data cannot be applied to the Sybase timestamp column due to a database limitation. The database populates this column automatically once that corresponding row is inserted or updated. To exclude timestamp columns from being captured by Oracle GoldenGate, use the COLSEXCEPT option of the TABLE parameter. Because the system generates the timestamps, the source and target values will be different.

• The Java rowobject data type is not supported.

Supported Operations and Objects for Sybase

This section lists the data operations and database objects that Oracle GoldenGate supports.

• The extraction and replication of insert, update, and delete operations on Sybase tables that contain rows of up to 512 KB in length.

• The maximum number of columns and the maximum column size per table that is supported by the database.

• Deferred inserts, deferred indirect inserts, deferred updates, and deferred deletes. It is possible that the use of deferred updates could cause primary key constraint violations for the affected SQL on the target. If these errors occur, use the Replicat parameter HANDLECOLLISIONS .

• TRUNCATE TABLE if the names of the affected tables are unique across all schemas. If the table names are not unique across all schemas, use the IGNORETRUNCATES parameter for those tables to prevent Replicat from abending.

• GETTRUNCATES and IGNORETRUNCATES by Extract and Replicat.

• Data that is encrypted with a system-encrypted password.

• Array fetching during initial loads, as controlled by the FETCHBATCHSIZE parameter.

• The BATCHSQL Replicat feature on ASE 15.7 SP110 and later on the following platforms:

  • AIX

  • Linux x64

  • Sun Solaris SPARC

  • Sun Solaris x64

  • Windows x64

  In certain scenarios, the CS_NUMERIC and CS_DECIMAL data types are not supported by BatchSQL because of a bug in the Sybase specific CT Library. LOB replication is supported in BatchSql mode for Sybase database version 157 SP110 onward. This will improve the LOB replication performance. It is restricted to 16384 bytes of LOB data that means if LOB data is more than 16384 bytes, the data would not be processed through BATCHSQL mode instead the mode switched to Normal.

• Limitations on Computed Columns support are as follows:

  • Fully supports persisted computed columns. The change values are present in the transaction log and can be captured to the trail.

  • You cannot use NOT NULL constraints on in-row LOB columns. If you need to use NOT NULL constraints, do so only with off-row LOB columns.

  • Tables with non-persisted computed columns, but does not capture change data for these columns because the database does not write it to the transaction log. To replicate data for non-persisted computed columns, use the FETCHCOLS or FETCHMODCOLS option of the TABLE parameter to fetch the column data from the table. Keep in mind that there can be discrepancies caused by differences in data values between when the column was changed in the database and when Extract fetches the data for the transaction record that is being processed.

  • Replicat does not apply DML to any computed column, even if the data for that column is in the trail, because the database does not permit DML on that type of column. Data from a source persisted computed column, or from a fetched non-persisted column, can be applied to a target column that is not a computed column.

  • In an initial load, all of the data is selected directly from the source tables, not the transaction log. Therefore, in an initial load, data values for all columns, including non-persisted computed columns, gets written to the trail or sent to the target, depending on the method that is being used. As when applying change data, however, Replicat does not apply initial load data to computed columns, because the database does not permit DML on that type of column.

  • Persisted computed column that is defined as a key column, an index column, or that is part of a KEYCOLS clause in a TABLE or MAP statement are not used. If a unique key or index includes a computed column and Oracle GoldenGate must use that key, the computed column will be ignored. Additionally, if a unique key or index contains a computed column and is the only unique identifier on the table, all of the columns are used except the computed column as an identifier to find the target row. Thus, the presence of a computed column in a key or index affects data integrity if the remaining columns do not enforce uniqueness. Sybase does not support non-persisted computed columns as part of a key so neither does Oracle GoldenGate.

  • To support TRUNCATE TABLE, all table names should be unique across all schemas within a database. This rule applies to Extract and Replicat.

• Limitations on Automatic Heartbeat Table support are as follows:

  • Heartbeat frequency should an integer that is divisible by 60. Oracle GoldenGate heartbeat parameter frequency is accepted in minutes, although you can use in seconds. The Sybase job scheduler uses the minutes in integer not in decimal so it is converted internally to set the frequency in minutes to the nearest possible value. For example, setting this value to 65 seconds results in the frequency being set to 1 minute; 140 seconds results in the value set to 2 minutes.

  • Data truncation occurs with a Replicat abend when it exceeds more than 1500 characters for the incoming_routing_path and outgoing_routing_path of the GG_HEARTBEAT_SEED, GG_HEARTBEAT, and GG_HEARTBEAT_HISTORY tables. The incoming_routing_path and outgoing_routing_path size of these table is set to 1500 characters in ASCII and is a 500 max bytes in multibyte characters. Ensure that the incoming and outgoing routing path strings are within the specified limit.

  • Sybase job scheduler must be configured on the ASE server prior to running Oracle GoldenGate heartbeat functionality.

  • For heartbeat table functionality to operate correctly, the login user must have the replication_role, js_admin_role, js_user_role roles.

Non-Supported Operations and Objects for Sybase

This section lists the data operations and database objects that Oracle GoldenGate does not support.

• Data that is encrypted with a user-defined password.

• Extraction or replication of DDL (data definition language) operations.

• Multi-Extract configuration. Only one Extract can reserve a context to read the Sybase transaction logs.

• Because SHOWSYNTAX is supported in the DYNSQL mode, NODYNSQL is deprecated.

• Table names that contain data with an underscore followed by some characters then a space (for example, 'zzz_j') is not supported. Oracle GoldenGate cannot process records containing this type of character string with GGSCI, DEFGEN, EXTRACT, or REPLICAT. Additionally, this type of data cannot be used with Oracle GoldenGate wildcard (*). If you do have this type of data in your table name, you must drop this kind of table name from your database, and then they restart the application to process and respect Oracle GoldenGate wildcard.