Sybase ASE as Source

Capture

This section describes the configuration requirements for capturing changes from Sybase ASE. Except for LTL and RTL capture (see Capture Methods), Fivetran HVR only supports capturing 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 changes from Sybase ASE:

• Direct Transaction Log Access: Reads the transaction log directly from the database log device. Supports reading from transaction dump archives.
• Archive Only: Reads changes from transaction dump (log backup) files. Supports reading from transaction dump archives.
• SQL Access: Queries transaction log records using the ASE utility dbcc logtransfer. Does not support reading from transaction dump archives.
• LTL: Integrates with SAP RepAgent/ASE using the Log Transfer Language protocol. Does not support reading from transaction dump archives.
• RTL: Integrates with a Primary Replication Server using the Replication Transfer Language protocol. Does not support reading from transaction dump archives.

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.

SQL Access

Since v6.3.5/6

In this capture method (Capture_Method=SQL), HVR queries transaction log records using the ASE utility dbcc logtransfer. This method does not require direct access to the transaction log device or archive files. SQL Access does not support reading from transaction dump archives.

LTL

Since v6.3.5/6

In this capture method (Capture_Method=LTL), HVR hosts an LTL Server, an Open Server implementation, within the Capture job. The LTL Server implements the Log Transfer Interface (LTI), which is used by the SAP RepAgent/ASE thread to deliver transaction log data using the Log Transfer Language (LTL) protocol. Because HVR communicates with Sybase ASE through RepAgent rather than reading the log directly, the HVR Capture job can run on any machine with TCP/IP connectivity to the Sybase ASE server.

Only one RepAgent/ASE is supported by a given LTL Server or HVR Capture job configured for LTL Capture.

Runtime Environment

The HVR Capture job must use the Sybase runtime environment from the SAP Replication Server installation. Before starting the HVR process that runs the Capture job, source the appropriate Sybase initialization script (SYBASE.sh or SYBASE.csh) from the Replication Server installation.

For the complete runtime environment requirements, see SAP Open Server SDK.

If the Capture job terminates when the SAP replication component attempts to connect using encrypted password authentication, see LTL or RTL Capture Crashes During Encrypted Password Login.

Prerequisites:

• The Sybase interfaces file on the machine running the HVR Capture job and on the machine running ASE must contain an entry for the LTL Server. The server name in this entry must match the value of the Sybase_CapJob_Server_Name source location property.
• RepAgent/ASE must be configured for the source database using sp_configure to enable RepAgent and sp_config_rep_agent to direct it to the LTL Server hosted by HVR.

Configuring RepAgent/ASE:

All RepAgent/ASE configuration, other than adding the LTL Server entry to the interfaces file, is performed within the ASE process using stored procedures. Log in to the source ASE server and execute the following:

1. Allow RepAgent threads to be started in ASE:

   sp_configure 'enable rep agent threads', 1
   

2. Register the source database with RepAgent and direct it to the HVR LTL Server. The database name, LTL Server name, username, and password must match the values configured in the HVR location:

   sp_config_rep_agent <database>, 'enable', '<LTL server name>', '<RepAgent user name>', '<RepAgent password>'
   

   This does not start the RepAgent; it notifies ASE that the database will be monitored by a RepAgent. It also enables the Secondary Truncation Point on the database's transaction log, which RepAgent requires to operate.

3. Allow RepAgent to interoperate with the HVR LTL Server despite any LTL version mismatch. The HVR LTL Server supports up to LTL version 730, introduced with ASE 15.0.4; later versions of LTL introduce features the HVR LTL Server does not support:

   sp_config_rep_agent <database>, 'skip unsupported features', true
   

4. Enable the following performance options, which reduce LTL size and improve parsing efficiency:

   sp_config_rep_agent <database>, 'short ltl keywords', true
   sp_config_rep_agent <database>, 'send structured oqids', true
   sp_config_rep_agent <database>, 'ltl metadata reduction', true
   

5. Configure the source database to send DDL changes through RepAgent. These settings are required for DDL replication - used by action AdaptDDL - to propagate from the source database through RepServer to HVR:

   sp_reptostandby <database>, 'all'
   go
   

   sp_config_rep_agent <database>, 'send warm standby xacts', true
   go
   

6. Encrypt the password that RepAgent uses to log in to the LTL Server, if desired for security:

   sp_config_rep_agent <database>, 'net password encryption', true
   

7. Start the RepAgent:

   sp_start_rep_agent <database>
   

   To stop the RepAgent:

   sp_stop_rep_agent <database>
   

   If RepAgent/ASE auto start configuration is set to true, the RepAgent starts automatically when ASE is started. Otherwise, if the RepAgent fails to connect to the LTL Server, it retries according to its retry timeout configuration, which is specified in seconds.

To review the RepAgent configuration for a database, use sp_config_rep_agent database without a configuration option.

Secondary Truncation Point

The secondary truncation point for the source database is enabled automatically when RepAgent is configured via sp_config_rep_agent 'enable'. The Log_Truncater source location property does not apply to the LTL capture method.

Maintenance User

RepAgent/ASE ignores transactions from the user specified by the Sybase_Maint_User source location property by default to prevent loopback. Although this username will most likely match the database username provided with the Database_User location property, it does not have to.

RepAgent Configuration and HVR Location Properties

The following table maps each Sybase ASE RepAgent configuration attribute (set on the ASE server via sp_config_rep_agent) to its corresponding label in the HVR UI and its underlying HVR location property. Because RepAgent and HVR each store this information independently, the values configured in RepAgent and in HVR must match for LTL capture to connect.

Database_Name is not itself a RepAgent configuration attribute. It must match the database argument passed to sp_config_rep_agent. Sybase_Maint_User has no corresponding RepAgent attribute; RepAgent instead uses it only to identify and ignore loopback transactions, as described above.

RTL

Since v6.3.5/6

In this capture method (Capture_Method=RTL), HVR acts as a replicate Replication Server, receiving transaction data from a Primary Replication Server over the Replication Server Interface (RSI) using the Replication Transfer Language (RTL) protocol. HVR registers with the SAP Replication Domain ID Server to obtain a Site ID, as any RepServer would. The HVR Capture job can run on any machine with TCP/IP connectivity to the source Sybase ASE server, the Primary RepServer, and the SAP Replication Domain ID Server.

Only one ASE primary database (PDB) and one Primary RepServer are supported by a given RTL Server or HVR Capture job configured for RTL Capture. Transactions from other primary databases that may be delivered by the Primary RepServer are rejected.

Runtime Environment

The HVR Capture job must use the Sybase runtime environment from the SAP Replication Server installation. Before starting the HVR process that runs the Capture job, source the appropriate Sybase initialization script (SYBASE.sh or SYBASE.csh) from the Replication Server installation.

For the complete runtime environment requirements, see SAP Open Server SDK.

If the Capture job terminates when the SAP replication component attempts to connect using encrypted password authentication, see LTL or RTL Capture Crashes During Encrypted Password Login.

Prerequisites:

• The Sybase interfaces file on the machine running the HVR Capture job and on the machine running the Primary RepServer must contain an entry for the RTL Server. The server name in this entry must match the value of the Sybase_CapJob_Server_Name source location property.
• The Primary Replication Server must be running and configured to replicate the source database.
• An ID Server (the RepServer designated within the SAP Replication Domain) must be accessible to the HVR Capture job and must appear in its Sybase interface file so that RTL Server can register as a RepServer and obtain a Site ID.

Configuring the Primary Replication Server and RTL Server

Perform the following configuration steps after the HVR Capture job has started. Commands executed in the RTL Server use the admin credentials configured in Sybase_RTLSrv_Admin_Username and Sybase_RTLSrv_Admin_Password:

isql -S<RTL server name> -U<RTL server admin username> -P<RTL server admin password>

1. In the Primary Replication Server: Create a Direct Route from the Primary Replication Server to the RTL Server. The values of Sybase_SAPRepClient_Username and Sybase_SAPRepClient_Password are the credentials the Primary Replication Server uses to authenticate to the RTL Server:

   create route to <RTL server name>
   set username <Primary Replication Server username>
   set password <Primary Replication Server password>
   go
   

2. In the Primary Replication Server: Create a database replication definition for the source database. The replication definition name should follow your SAP replication naming standards. We recommend the convention <ASE server name>$<database name>_repdef. For example, if your ASE server is named PRODASE1 and your database is SALESDB, name the replication definition PRODASE1$SALESDB_repdef. Use your chosen name wherever <repdef name> appears below:

   create database replication definition <repdef name>
   with primary at <ASE server name>.<database name>
   replicate DDL with auto_update_table_list
   with auto_extend_table_list
   replicate functions
   replicate transactions
   replicate system procedures
   replicate tables
   replicate SQLDML
   go
   

   Choose the options to publish in this database replication definition based on the operations and objects you want to replicate.

3. In source ASE database: Configure the source database to send DDL changes through RepAgent. These settings are required for DDL replication used by action AdaptDDL to propagate from the source database through RepServer to HVR:

   sp_reptostandby <database name>, 'all'
   go
   

   sp_config_rep_agent <database name>, 'send warm standby xacts', true
   go
   

4. In the RTL Server: Create a subscription to the database replication definition. The subscription name must be consistent across all subsequent check subscription and drop subscription commands. We recommend the convention <repdef name>_<RTL server name>_sub. For example, if your replication definition is named PRODASE1$SALESDB_repdef and your RTL Server is named RTLSRV1, name the subscription PRODASE1$SALESDB_repdef_RTLSRV1_sub. Use your chosen name wherever <subscription name> appears below:

   create subscription <subscription name>
   for database replication definition <repdef name>
   with primary at <source ASE server name>.<database name>
   with replicate at <target server>.<target database>
   without materialization
   subscribe to truncate table
   go
   

   Both the Primary Replication Server and RTL Server verify that the named primary (source) database and replicate (target) database are known to the ID Server. If the RTL Server discovers that the replicate database is not currently known, it requests an ID for that database so that it is available when the Primary Replication Server checks. Otherwise, the RTL Server does not restrict the replication of transactions to only the named replicate database. Target locations are determined by the HVR channel definition.

5. Verify that the subscription is VALID at both the Primary Replication Server and the RTL Server. Replication will not begin until the subscription is VALID at both:

   check subscription <subscription name>
   for database replication definition <repdef name>
   with primary at <ASE server name>.<database name>
   with replicate at <replicate server>.<replicate database>
   go
   

Source location properties for RTL capture

All of the following properties are required for RTL capture:

• Sybase_CapJob_Server_Name - RTL Server name.
• Sybase_RTLSrv_Admin_Username - RTL Server admin username. Used to log into RTL Server to create or check RepServer subscriptions.
• Sybase_RTLSrv_Admin_Password - RTL Server admin password.
• Sybase_SAPRepClient_Username - Primary Replication Server username. Used by the Primary Replication Server to log into the RTL Server for route and subscription creation and sending RTL.
• Sybase_SAPRepClient_Password - Primary Replication Server password.
• Sybase_PRS_Name - Primary Replication Server name.
• Sybase_PRS_Username - RTL Server username in the Primary Replication Server. Used by RTL Server to log into the Primary Replication Server for subscription creation.
• Sybase_PRS_Password - RTL Server password in the Primary Replication Server.
• Sybase_IDS_Name - ID Server name. May be the same as the Primary Replication Server name if that RepServer is the ID Server for the SAP Replication Domain.
• Sybase_IDS_Username - RTL Server username in the ID Server. Used by RTL Server to log into the ID Server to obtain for itself a Site ID as every RepServer must do.
• Sybase_IDS_Password - RTL Server password in the ID Server.

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>,... to<username>
  
  grant execute on hvr_traceon to <username>
  
  grant execute on hvr_page to <username>
  
  grant execute on hvr_logtail to <username>
  
  grant execute on hvr_log_state 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 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.

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 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 selected during location creation, HVR will automatically define action TableProperties with parameters CoerceErrorPolicy and CoerceErrorType.

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.

Capture After Database Restore

If the Sybase ASE source database is restored to a previous point in time, the HVR Capture job may stop capturing transactions. Capture cycles may complete without errors but capture no rows, because the saved log position references transaction log records that no longer exist in the restored database. In some cases, the ltm trunc state must also be cleared by truncating the transaction log before capture can resume. For the required recovery steps, see Maintaining HVR Capture After Restoring a Sybase ASE Database.

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>