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)
.
