Sybase ASE as Source

Capture

This section describes the configuration requirements for capturing changes from Sybase ASE. Fivetran HVR only supports capture changes from a Sybase ASE database on Linux. For the list of supported Sybase ASE versions, from which HVR can capture changes, see Capture changes from location in Capabilities.

Table Types

HVR does not support capture from compressed tables in Sybase ASE.

Capture Methods

HVR allows the following methods for capturing (Capture) changes from Sybase ASE:

Direct Transaction Log Access

In this capture method (Capture_Method=DIRECT), HVR reads transaction log records directly from the database log device using the file I/O. This capture method is faster and less resource-intensive when capturing changes from database locations, especially for highly loaded databases. The DIRECT capture method and pipelined execution ensure optimum efficiency to keep up with the database log writers. As a result, when capture runs continuously, it will be capturing from the tail end of the log where the log writer(s) are writing.

The DIRECT capture method requires the following:

• HVR Agent must be installed on the Sybase ASE source database server. For the steps to install HVR Agent, see Installing HVR Agent.
• For Sybase ASE databases where the log is stored on a separate log device, an OS user who runs HVR must have direct read access to log device files. For 'mixed data and log' Sybase ASE databases, the OS user must have direct read access to data device files.

Archive Only

In this capture method (Capture_Method=ARCHIVE_ONLY), HVR reads/captures changes from Sybase ASE transaction dump (log backup) files available in the directory specified in the location property TRANSACTION DUMP DIRECTORY (Archive_Log_Path). Optionally, HVR can be configured to consider only the files that match the format defined in the location property FILENAME FORMAT (Archive_Log_Format). For more information, see section Accessing Transaction Dumps.

Grants for Capture

This section lists the grants/permissions required for capturing changes from Sybase ASE. The grants are applicable for all the capture methods listed above.

Based on your requirement, either of the permissions listed in this section can be used. For simplicity, we have categorized the required permissions into the following two models:

SysAdmin

In this permission model, the HVR database User must be granted the sa_role and sybase_ts_role roles. The benefit of this permission model is that it is easy to set up, there is no need for operators to install the stored procedures and perform any special SQL statements manually.

Minimal

In this permission model, the HVR database User is not granted or does not require sa_role and sybase_ts_role roles at runtime. The benefit of this permission model is the ability to run HVR with minimal database privileges.

Following are the configuration requirements that must be met/performed for this permission model:

• A user with sa_role and sybase_ts_role roles must install special stored procedures in the capture database using the script file hvrsybaseprocs.sql available in the HVR_HOME/dbms/sybase/ directory.

• The HVR database User must be granted the following privileges:

  grant select on table1, table2,... tousername
  
  grant execute on hvr_traceon to username
  
  grant execute on hvr_page to username
  
  grant execute on hvr_logtail to username
  
  grant role replication_role to username
  

Supplemental Logging

Transaction logs are generally used for database recovery and normally Sybase ASE only logs data that is needed for recovery. For HVR to perform log-based capture, Sybase ASE needs to write some additional information into the transaction log, which is referred to as "Supplemental Logging" in HVR.

To enable Supplemental Logging, it is required to enable replication for the table(s) on Sybase ASE. HVR will automatically enable/disable replication for tables during Activate Replication/Deactivate Replication respectively. Also, this is the recommended method for enabling/disabling replication for tables on Sybase ASE.

Secondary Log Truncation Point

The location property Use Secondary Truncation Point (Log_Truncater) specifies who advances the Sybase ASE transaction log truncation point (truncates the log).

Supported log truncater method

• CAP_JOB_RETAIN: This method ensures Sybase ASE transactions are always available for HVR. Log device usage will grow if the capture job is idle. Not using truncation points allows multiple systems to capture from the same database, but the user must ensure that older transaction files are kept available as transaction dumps until they are replicated.

Accessing Transaction Dumps

Transaction dumps are the incremental backups of the transaction log. They can be very useful as an alternate to full database dumps.

• When the Capture_Method is set to ARCHIVE_ONLY, HVR reads/captures changes from Sybase ASE transaction dump.
• When the Capture_Method is set to DIRECT or SQL and if this location property TRANSACTION DUMP DIRECTORY (Archive_Log_Path) is defined, HVR will search for backups in this directory if the online log is truncated and no longer contains the log records needed for reading.

Transaction Dump Directory

HVR will search for the log backups in the directory in addition to the default log backup location for the source database. For versions prior to 6.1.1/0 and 6.1.0/1, HVR will search for the log backups only in the specified directory instead of the default log backup location for the source database.

FileName Format

This field/property describes the filename format (template) of the transaction log archive files stored in the TRANSACTION DUMP DIRECTORY (Archive_Log_Path). HVR will scan/consider only the files that match the format defined in this field/property.

This field accepts the following format variables:

• * - wildcard, matches zero or more characters
• ? - matches any single character

This field is optional. When this field is not defined, by default HVR will scan all files available in the TRANSACTION DUMP DIRECTORY.

Capturing from SAP Source

HVR allows you to capture changes from a Sybase ASE database which is used by an SAP ECC system. To enable capture using SAP dictionary, the location property SAP Source (SAP_Source_Schema) must be defined while creating a location or by editing the existing location's source and target properties. Then while adding tables to a channel, the Table Selection dialog will display the SAP tables defined in the SAP dictionaries.

For Usage-based Subscription, an additional SAP Unpack license is required to unpack the cluster and pool tables from the SAP database. Contact Fivetran Technical Support to obtain the necessary SAP Unpack license. For the Consumption-based model, a separate license is NOT required.

When SAP pool, cluster, and long text (STXL) tables are added to a channel using the Table Selection dialog, the following actions are automatically defined:

• TableProperties with parameters PackedInside, CoerceErrorPolicy, and CoerceErrorType

  • For each container (pool/cluster) table a separate action TableProperties is defined.
  • This action is not defined for long text (STXL) tables.

• Transform with parameter SapUnpack

  Irrespective of the number of tables, only a single action Transform is defined.

SAP columns are non-nullable by default. They will be described as nullable in the hub's repository and thus as nullable in the target. This is valid for the non-key columns. Key columns will remain non-nullable.

SAP Data Types Conversion

Since v6.1.0/7

This option enables conversion/mapping of SAP specific data types (available in SAP dictionary meta-data) in source location to corresponding data type in the target location. The SAP specific data type will be localized with the source DB's data type and then mapped to HVR's Repository data type. For example, if you have an SAP system on Sybase ASE, the DATS data type will be localized as Sybase ASE's Date type, and then it is mapped to HVR Repository type date(sybase).

This feature is supported for the following SAP specific data types:

• DATS

If the SAP Data Types Conversion option is NOT selected, SAP specific data types are mapped to various other HVR Repositry data types. For more information, see Data Type Mapping for SAP NetWeaver (or SAP dictionary).

If the SAP Data Types Conversion option is selected during location creation, HVR will automatically define action TableProperties with parameters CoerceErrorPolicy and CoerceErrorType.

If the SAP Source (SAP_Source_Schema) location property/option is selected during location creation, by default, the SAP Data Types Conversion option also gets selected.

However, to enable SAP data type conversion for an existing location, select the SAP Data Types Conversion option by editing the location's source and target properties and then manually define action TableProperties with parameters CoerceErrorPolicy and CoerceErrorType.

Compare and Refresh Source

HVR allows you to perform only Compare and Refresh from Sybase ASE database (without using Capture). This section describes the configuration requirements for performing only Compare and Refresh from Sybase ASE database.

Grants for Compare and Refresh from Sybase ASE

This section lists the grants required for performing only Compare and Refresh from Sybase ASE database.

• The HVR database User must be granted the following privilege to read from the Sybase ASE database:

  grant select on tbl to username