SAP Sybase ASE to SAP HANA Data Replication

SAP Sybase Replication Server version 15.7.1 SP 100 is the first edition which supports data replication to SAP HANA. Source can be any SAP Sybase Replication Server primary supported RDBMS:

• Adaptive Server
• Oracle
• Microsoft SQL Server
• DB2 UDB on Linux, UNIX, and Windows

RepServer provides a new connector called ECH (ExpressConnect for HANA) which has been implemented similar to the Oracle (ECO). ExpressConnect for Oracle (ECO) is a library that is loaded by Replication Server 15.5 or later for Oracle replication.

More info regarding ECO/ECH -->  The advantages of ECO/ECH include:

• It does not require a separate server process for starting up, monitoring, or administering.
• Since Replication Server and ECO/ECH run within the same process, no SSL is needed between them.
• Server connectivity is configured via Replication Server using the “create connection” and “alter connection” commands, thus there is no need to separately configure the equivalent to the ECDA for Oracle connect_string setting.
• ECH consists in 2 dynamic libraries shipped with SAP Sybase Replication Server (libsybhdb and libsybhdbodbc) that are linked with SAP HANA odbc driver (libodbcHDB)
• Direct load materialization support for HANA, no need to use materialization queues (this requires Rep Agent 15.7.1 SP100)

Using ExpressConnect for HANA DB

A Replication Server database connection to HANA DB can be:

• secure, in which the connection uses the hdbuserstore key specified in the database connection, or
• standard, in which the connection uses an entry in the interfaces file for the host and port number for HANA DB.

Replication Server provides new a function string class, new connection profiles, and replicate database objects to support HANA DB.

New function strings have been added to the Replication Server rs_hanadb_function_class. These function strings are designed to communicate with a HANA DB data server and access the tables and procedures.

Replication Server provides new connection profiles for replicating into HANA DB:

• rs_ase_to_hanadb – installs Adaptive Server-to-HANA DB class-level translations.
• rs_oracle_to_hanadb – installs Oracle-to-HANA DB class-level translations.
• rs_udb_to_hanadb – installs DB2 UDB-to-HANA DB class-level translations.
• rs_msss_to_hanadb – installs Microsoft SQL Server-to-HANA DB class-level translations.

Direct Load Materialization

Use direct load materialization to materialize data between different kinds of primary databases and HANA DB.

Direct load materialization can be used to materialize data:

• from Adaptive Server to HANA DB
• from Microsoft SQL Server to HANA DB
• from Oracle to HANA DB
• from DB2 UDB to HANA DB

Note: Direct load materialization is not supported for materializing data into an Adaptive Server database.

Direct load materialization is enabled through the direct_load option of the create subscription command.

When using direct load materialization, note these restrictions for create subscription:

• When the direct_load option is used, no other subscription can be created or defined at the same time for the same replicate table.
• The direct_load option is for subscriptions to table replication definitions only and is used withwithout holdlock. It cannot be used with the without materialization or incrementally options.
• The user and password options are used only with direct_load.
• You cannot use the direct_load option against a logical or alternate connection. The primary connection in the replication definition and the replicate connection in the subscription must be physical connections.
• The maintenance user of the primary database cannot be used in the user and password options to create subscriptions.
• You cannot use other automatic materialization methods if the primary database is not Adaptive Server. The only automatic materialization option for Oracle or other databases is direct load materialization. You cannot drop a subscription with the with purge option if the replicate database is not Adaptive Server.
• The direct_load option is available only if the replicate Replication Server site version and route version are 1571100 or later.
• You can use row filtering, name mapping, customized function strings and datatype mapping with subscriptions created using the direct_load option.
• Replication Server rejects any attempt to create a subscription with the direct_load option if the number of subscriptions being created has reached or exceeded num_concurrent_subs.

Primary Database Considerations

In directly materializing data from a primary database, Replication Server connects to Replication Agent for non-Adaptive Server databases, and directly to the primary database for Adaptive Server.

You must have Replication Agent version 15.7.1 SP100 or later to materialize data from a non-Adaptive Server primary database using direct load materialization.

When invoking the create subscription command, Replication Server connects to Replication Agent using the Replication Agent administrator login name.

rs_init sets default configuration parameters after you install your Replication Server. 

======================================================================

Configuring/ Setup replication from ASE to HANA

6 Steps required for establishing a replication system from ASE to HANA using SAP Sybase Replication Server.

1. Create the connection to primary ASE database (easy using rs_init)

create connection

Adds a database to the replication system and sets configuration parameters for the connection. To create a connection for an Adaptive Server database, use Sybase Central or rs_init. To create a connection for a non-Adaptive Server database, see create connection using profile command.

         create connection to ASE_PS.pubs2

         set error class ansi_error
         set function string class sqlserver_derived_class
         set username pubs2_maint
         set password pubs2_maint_pw

Syntax

         create connection to data_server.database

         set error class [to] error_class

         set function string class [to] function_class

         set username [to] user

         [set password [to] passwd]

         [set dsi_connector_sec_mech [to] hdbuserstore]

         [set replication server error class [to] rs_error_class]

         [set database_param [to] 'value' [set database_param [to] 'value']...]

         [set security_param [to] 'value' [set security_param [to] 'value']...]

         [with {log transfer on, dsi_suspended}]

         [as active for logical_ds.logical_db |

         as standby for logical_ds.logical_db

         [use dump marker]]

Parameters

data_server – The data server that holds the database to be added to the replication system.

database – The database to be added to the replication system.

error_class – The error class that is to handle errors for the database.

function_class – The function string class to be used for operations in the database.

user – The login name of the Replication Server maintenance user for the database. Replication Server uses this login name to maintain replicated data. You must specify a user name if network-based security is not enabled.

passwd – The password for the maintenance user login name. You must specify a password unless a network-based security mechanism is enabled.

dsi_connector_sec_mech – Specifies the DSI connector security mechanism.

rs_error_class – The error class that handles Replication Server errors for a database. The default is rs_repserver_error_class.

database_param – A parameter that affects database connections from the Replication Server.

value – A character string that contains a value for the option.

security_param – A parameter that affects network-based security. See "Parameters Affecting network-Based Security" table for a list and description of security parameters that you can set with create connection. This parameter does not apply to non-ASE, non-IQ connectors.

log transfer on – Indicates that the connection may be a primary data source or the source of replicated functions. When the clause is present, Replication Server creates an inbound queue and is prepared to accept a RepAgent connection for the database. If you omit this option, the connection cannot accept input from a RepAgent.

dsi_suspended – Starts the connection with the DSI thread suspended. You can resume the DSI later. This option is useful if you are connecting to a non-Sybase data server that does not support Replication Server connections.

as active for – Indicates that the connection is a physical connection to the active database for a logical connection.

as standby for – Indicates that the connection is a physical connection to the standby database for a logical connection.

logical_ds – The data server name for the logical connection.

logical_db – The database name for the logical connection.

use dump marker – Tells Replication Server to apply transactions to a standby database after it receives the first dump marker after the enable replication marker in the transaction stream from the active database. Without this option, Replication Server applies transactions it receives after the enable replication marker.

2. Create the connection to the HANA database (only can do manually with create connection command).  

         create connection to

         <hana_server>.<hana_db>

         using profile rs_ase_to_hanadb;ech

         set username <userid>

         set password <password>

HANA DB Replicate Database Permissions

Replication Server requires a maintenance user ID that you specify using the Replication Server create connection command to apply transactions in a replicate database.

The maintenance user ID must be defined at the HANA DB data server and granted authority to apply transactions in the replicate database. The maintenance user ID must have these schema privileges:

• CREATE ANY – allows the user to create tables, views, sequences, synonyms, SQL script functions, or database procedures in a schema.
• DELETE, DROP, EXECUTE, INDEX, INSERT, SELECT, and UPDATE – granted on every object stored in the specified schema.

Connection has to be created using profile rs_ase_to_hanadb;ech

Additional Settings

Command Batching : HANA DB does not support command batching. Do not turn on command batching for the HANA DB database connection.

Dynamic SQL: The dynamic_sql configuration parameter is set to 'on' by default, and this setting is recommended for all HANA DB connection profiles.

HVAR : If you have a Replication Server license for Advanced Service Options, you can use Replication Server High-Volume Adaptive Replication (HVAR) in replicating to HANA DB. 

Direct Load Materialization

Use direct load materialization to materialize data between different kinds of primary and replicate databases.

• Direct load materialization is only for use with subscriptions to table replication definitions.
• Direct load materialization differs from other automatic materialization methods:
• No materialization queue is used with direct load materialization. Data is loaded directly from a primary table into a replicate table.
• Replication to other tables is not suspended during direct load materialization. DML operations on a primary table being materialized are stored in a catch-up queue and applied to the replicate table after the initial materialization phase. DML operations on a primary table that is not being materialized are replicated into the replicate table as the DSI receives them. Multiple tables can be concurrently materialized with direct load materialization.
• When subscription materialization stops due to an error, regular replication to other tables is not suspended.
• Multiple parallel threads can be used to load data from one primary table to its corresponding replicate table. You can tune this multi-threaded behavior with max_mat_load_threads.
• The atomic and nonatomic materialization methods described here are only supported for an Adaptive Server primary. For an explanation of the different types and methods of materialization, see the Replication Server Heterogeneous Replication Guide > Materialization > Types of Materialization and the Replication Server Administration Guide: Volume 1 > Manage Subscriptions > Subscription Materialization Methods.

Direct load materialization can be used to materialize data:

• from Adaptive Server to HANA DB
• from Microsoft SQL Server to HANA DB
• from Oracle to HANA DB
• from DB2 UDB to HANA DB.

 Note: Direct load materialization is not supported for materializing data into an Adaptive Server database

Restrictions and Limitations for create subscription

• When the direct_load option is used, no other subscription can be created or defined at the same time for the same replicate table.
• The direct_load option is for subscriptions to table replication definitions only and is used withwithout holdlock. It cannot be used with without materialization or incrementally.
• The user and password options are used only with direct_load.
• You can only use the direct_load option against a physical database connection, not an alternate or logical connection. This is the case for both the primary connection—the connection specified in the replication definition—and the replicate connection—the connection specified in the subscription.
• The maintenance user of the primary database cannot be used in the user and password options to create subscriptions.
• You cannot use atomic materialization if the primary database is not Adaptive Server. For a primary database other than Adaptive Server, the only automatic materialization option supported is direct load.You cannot drop a subscription with the with purge option if the replicate database is not Adaptive Server.
• The direct_load option is available only if the replicate Replication Server site version and route version are 1571100 or later.
• You can use row filtering, name mapping, customized function strings and datatype mapping with subscriptions created using the direct_load option.
• Replication Server rejects any attempt to create a subscription with the direct_load option if the number of subscriptions being created has reached or exceeded num_concurrent_subs.

3. Create replication definition at primary ASE database and mark it

         create replication definition authors_rep

         with primary at ASE_PS.pubs2

         with all tables named 'authors'

         (au_id varchar(11), au_lname varchar(40),

         au_fname varchar(20), phone char(12),

         address varchar(12), city varchar(20),

         state char(2), country varchar(12), postalcode char(10))

         primary key (au_id)

         searchable columns (au_id, au_lname)

         replicate minimal columns

4. Create table at replicate HANA database similar to Primary Table 

using SAP HANA Studio tools:

• Select your Schema and Right click on and select "New Table" 
• Enter the Table Name
• Choose the Table Type, e.g. "Row Store"
• Enter the table columns/fields, data types, Key characteristics, etc. by clicking on the "+" sign similar to Sybase ASE Table, and click on the Create table icon (or F8) when it’s ready. 

using standard SQL

• Select your Schema. Right click and select "SQL Console". Otherwise you can also click on “SQL" button on top panel.
• Type the SQL statement in the SQL editor. 

          CREATE TABLE REP_TABLE1 ( UID INTEGER, UNAME VARCHAR(10), 

          UREMARKS VARCHAR(100), PRIMARY KEY (ID) ); 

         

         click on the Execute (or F8)

5. Create subscription and verify.

At the replicate Replication Server, create the subscription:

         Create subscription sub_hana_authors_rep

         for authors_rep

         with Replication at HanaDB.TestDB

         without holdlock

         direct_load

         go

Syntax

         create subscription subscription_name

         for replication_definition

         with replicate at dataserver.database

         [where search_conditions]

         without holdlock

         direct_load

         go

         check subscription subscription_name for repdef_name

         with replicate at replicate_dataserver.replicate_database

         go

When materialization is complete, Replication Server returns a message similar to:

Subscription <subscription_name> has been MATERIALIZED at the replicate.

When the subscription process is complete, Replication Server returns a message similar to:

Subscription <subscription_name> is VALID at the replicate.

If there is an error in the subscription process, the check subscription command returns a message similar to:

Subscription <subscription_name> encountered ERROR.

6. Start repagent and replication is ready

(Thanks much to the SAP Sybase Documentation and Many SAP geeks)

.