- SAP Managed Tags:
This article provides step-by-step instructions for setting up a real-time replication environment from SAP Adaptive Server Enterprise (ASE) On Premise to SAP HANA Cloud data lake Relational Engine (HDLRE) in a BTP subaccount, SAP Replication Server (SRS) and Cloud Connector. Subscriptions are initialized using the bulk materialization capability to HDLRE introduced in SAP Replication Server 16.1 (released 2025-02-28).
Using bulk materialization allows existing data in the source ASE database to be materialized to HDLRE as part of the subscription definition, eliminating the need for a separate extraction and load step or starting with empty source tables.
Other than bulk materialization, the steps described are similar to those documented in Replicating from SAP Adaptive Server Enterprise to SAP HANA Cloud Datalake Relational Engine except as follows:
- The target HDLRE will be defined in non CF-enabled subaccount (including the srs proxy connection service required).
- A non-administrative HDLRE account will be defined as the table owner HDLRE and DSI connection from SRS.
Full database replication is not possible as this is heterogenous replication. Table level replication is used to replicate required tables. Table level replication is described in documentation (Create an SAP Replication Server On-Premise Remote Server | SAP Help Portal).
Using table level replication allows finer control over database replication as not all tables may need to be replicated.
1. Install, create and configure an ASE On Premise
ASE 16.1 is installed in the $SYBASE folder (Adaptive Server Enterprise/16.1 SP00 PL00/EBF 30672 SMP/P/x86_64/Linux 5.14.21-150400.24.147-default/ase161sp00pl00/3343/64-bit/FBO/Thu Feb 6 02:47:09 2025). Instance SAMPLE_ASE is running on port 5000.
An empty 'tpcc' database (~6GB) will be used as the starting point on the ASE.
2. Install, create and configure an SRS On Premise.
SRS 16.1 is installed in its own $SYBASE path (Replication Server/16.1/EBF 30761 SP00 PL00 rs161sp00pl00/Linux AMD64/Linux 5.14.21-150400.24.136-default x86_64/3251/OPT64/Sun Dec 29 10:05:13 2024).
Create a SAMPLE_RS instance using $SYBASE/$SYBASE_REP/install/rs_init with a partition size of 4GB.
Once the SRS instance is created, run the following configuration commands:
configure replication server set dsi_bulk_copy to "off"
configure replication server set dynamic_sql to "off"
configure replication server set memory_limit to "16384Parallel DSI's cannot be used for HDLRE as loading must be via a single connection, but catch up queues will be automatically created for bulk materialization.
3. Install and Start Cloud Connector On Premise
Download and install the latest cloud connector from https://tools.hana.ondemand.com/#cloud. As of this writing, the current version is 2.18.0. For this blog, the Linux portable version will be used but the daemon version can be installed and run instead, if required.
Cloud connector requires a newer JRE. SAP JRE can be downloaded from https://sap.github.io/SapMachine/. For this writing, SAP JVM 21 (21.0.7) will be used and JAVA_HOME set accordingly.
Once installed, start the Cloud Connector.
Cloud Connector documentation can be found at: https://help.sap.com/docs/connectivity/sap-btp-connectivity-cf/cloud-connector. If this is the first time connecting to Cloud Connector, several proscribed steps must be taken to connect as 'Administrator' and change default passwords.
4. Connect to BTP Cockpit
Connect to SAP BTP Cockpit (e.g. https://amer.cockpit.btp.cloud.sap) and access the sub account from the 'Overview' where the HDLRE instance will be created.
Locate and copy the BTP subaccount id (see screenshot below):
5. Configure Cloud Connector
Connect to Cloud Connector (e.g. http://<cloudconnectorhost>:8443 ) and configure a new subaccount using the subaccount number from above. Fill in the proxy details if necessary and choose 'Configure manually'. Fill in the dialog similar to the following using a valid BTP subaccount userid and password:
Note: if using MFA via an authenticator app for the login, append the time-generated code to the end of the password.
Once added, select 'Cloud To On Premise' on the left menu under 'Replication Demo' and fill in the prompts of the dialogs as follows to add the Virtual to Internal System mapping:
- Backend Type: Other SAP System
- Protocol: TCP
- Internal host: <name of CC host from 'hostname' shell command>
- Port or Port Range: <port on which SRS is listening - from $SYBASE/interfaces file> (e.g. 11752)
- Virtual Host: <SRS name from interfaces file> (e.g. SAMPLE_RS)
- Virtual Port: <same port as above>
On the 'Summary' dialog, ensure that 'Check Internal Host' is selected before pressing 'Finish'. If there are any issues, corrections can be made from the presented list (example below):
6. Create Proxy Service in BTP
Before creating the HDLRE instance, a proxy service must be created in the BTP sub account.
Connect to the BTP subaccount, navigate to 'Serivces' -> 'Instances and Subscriptions'. Create a new service instance using the below dialog example and select 'Create'.
Once created, select the '...' (Actions) menu and 'Create Service Binding'.
Use 'sample_rs' for the Binding Name and create the binding.
When the binding has been created, click on the link in the 'Credentials' column of the service and copy the JSON to use in the next step.
7. Create HDLRE instance in BTP
SAP HANA Cloud Central must be used to create the HDLRE instance. From BTP Cockpit, install (if necessary) 'SAP HANA Cloud' application from the 'tools' plan and 'Go to Application'.
Once connected and running SAP HANA Cloud Central, create a new HDLRE instance of type 'SAP HANA Cloud, Data Lake' in the 'Other Environments' runtime environment.
- Instance Name: HDLRE
- Allowed connections: Allow all IP addresses
- Enable data lake Relational Engine: yes
- Set and confirm HDLADMIN password
- Size: 2 VCPUs for each of the coordinator and workers
- Enable Cloud Connector: yes
- Service key: (paste the previously copied service binding JSON into the text box)
- Allowed connections: Allow all IP addresses
- Mode: Configure to be most compatible with SAP IQ
Leave the remaining details and press 'Create'.
Once created, copy the coordinator SQL Endpoint for this instance to the clipboard using the '...' (Actions) -> 'Copy Other' menu for the instance:
This endpoint contains the FQDN and port (usually 443) of the instance coordinator. This information is used in several of the next steps.
8. Add the HDLRE and ASE endpoints to the SRS interfaces file.
Edit the $SYBASE/interfaces file for the SRS instance and add details for connections to the ASE and HDLRE. Linux example:
SAMPLE_RS
master tcp ether <SRS instance host> 11752
query tcp ether <SRS instance host> 11752
SAMPLE_RS_ERSSD
master tcp ether localhost 11751
query tcp ether localhost 11751
SAMPLE_ASE
master tcp ether <ASE instance host> 5000
query tcp ether <ASE instance host> 5000
HDLRE
master tcp ether <FQDN from coordinator SQL Endpoint> 443 ssl="CN=hanacloud.ondemand.com"
query tcp ether <FQDN from coordinator SQL Endpoint> 443 ssl="CN=hanacloud.ondemand.com"
9. Add the SRS endpoint to the ASE interfaces file.
Edit the $SYBASE/interfaces file for the ASE instance and add details for connections to the SRS.
Example:
SAMPLE_ASE
master tcp ether <ASE instance host> 5000
query tcp ether <ASE instance host> 5000
SAMPLE_ASE_BS
master tcp ether <ASE instance host> 5001
query tcp ether <ASE instance host> 5001
SAMPLE_ASE_JSAGENT
master tcp ether <ASE instance host> 4900
query tcp ether <ASE instance host> 4900
SAMPLE_ASE_XP
master tcp ether <ASE instance host>5002
query tcp ether <ASE instance host>5002
SAMPLE_RS
master tcp ether <SRS instance host> 11752
query tcp ether <SRS instance host> 11752
10. Create the database and tables in ASE.
Connect to the ASE database using isql or DBISQL. After creating the database, run the script below in the database to create the tables, columns, indexes and RI contraints:
set quoted_identifier on
go
/*==============================================================*/
/* Table: CUSTOMER */
/*==============================================================*/
create table CUSTOMER (
C_ID int default '0' not null,
C_D_ID smallint default '0' not null,
C_W_ID smallint default '0' not null,
C_FIRST varchar(16) null,
C_MIDDLE char(2) null,
C_LAST varchar(16) null,
C_STREET_1 varchar(20) null,
C_STREET_2 varchar(20) null,
C_CITY varchar(20) null,
C_STATE char(2) null,
C_ZIP char(9) null,
C_PHONE char(16) null,
C_SINCE DATETIME default getdate() null,
C_CREDIT char(2) null,
C_CREDIT_LIM decimal(12,2) null,
C_DISCOUNT decimal(4,4) null,
C_BALANCE decimal(12,2) null,
C_YTD_PAYMENT decimal(12,2) null,
C_PAYMENT_CNT int null,
C_DELIVERY_CNT int null,
C_DATA varchar(500) null,
constraint C_PRIMARY primary key (C_W_ID, C_D_ID, C_ID)
)
lock datarows
go
/*==============================================================*/
/* Index: C_C_ID */
/*==============================================================*/
create index C_C_ID on CUSTOMER (
C_ID ASC
)
go
/*==============================================================*/
/* Index: C_D_ID */
/*==============================================================*/
create index C_D_ID on CUSTOMER (
C_D_ID ASC
)
go
/*==============================================================*/
/* Index: C_W_ID */
/*==============================================================*/
create index C_W_ID on CUSTOMER (
C_W_ID ASC
)
go
/*==============================================================*/
/* Table: DISTRICT */
/*==============================================================*/
create table DISTRICT (
D_ID smallint default '0' not null,
D_W_ID smallint default '0' not null,
D_NAME varchar(10) null,
D_STREET_1 varchar(20) null,
D_STREET_2 varchar(20) null,
D_CITY varchar(20) null,
D_STATE char(2) null,
D_ZIP char(9) null,
D_TAX decimal(4,4) null,
D_YTD decimal(12,2) null,
D_NEXT_O_ID int null,
constraint D_PRIMARY primary key (D_W_ID, D_ID)
)
lock datarows
go
/*==============================================================*/
/* Index: D_D_ID */
/*==============================================================*/
create index D_D_ID on DISTRICT (
D_ID ASC
)
go
/*==============================================================*/
/* Index: D_W_ID */
/*==============================================================*/
create index D_W_ID on DISTRICT (
D_W_ID ASC
)
go
/*==============================================================*/
/* Table: HISTORY */
/*==============================================================*/
create table HISTORY (
H_C_ID int null,
H_C_D_ID smallint null,
H_C_W_ID smallint null,
H_D_ID smallint null,
H_W_ID smallint default '0' null,
H_DATE DATETIME default getdate() null,
H_AMOUNT decimal(6,2) null,
H_DATA varchar(24) null
)
lock datarows
go
/*==============================================================*/
/* Table: ITEM */
/*==============================================================*/
create table ITEM (
I_ID int default '0' not null,
I_IM_ID int null,
I_NAME varchar(24) null,
I_PRICE decimal(5,2) null,
I_DATA varchar(50) null,
constraint PK_ITEM primary key (I_ID)
)
lock datarows
go
/*==============================================================*/
/* Table: NEW_ORDER */
/*==============================================================*/
create table NEW_ORDER (
NO_O_ID int default '0' not null,
NO_D_ID smallint default '0' not null,
NO_W_ID smallint default '0' not null,
constraint NO_PRIMARY primary key (NO_W_ID, NO_D_ID, NO_O_ID)
)
lock datarows
go
/*==============================================================*/
/* Table: ORDER */
/*==============================================================*/
create table "ORDER" (
O_ID int default '0' not null,
O_D_ID smallint default '0' not null,
O_W_ID smallint default '0' not null,
O_C_ID int null,
O_ENTRY_D DATETIME default getdate() null,
O_CARRIER_ID int null,
O_OL_CNT smallint null,
O_ALL_LOCAL smallint null,
constraint O_PRIMARY primary key (O_W_ID, O_D_ID, O_ID)
)
lock datarows
go
/*==============================================================*/
/* Index: O_O_ID */
/*==============================================================*/
create index O_O_ID on "ORDER" (
O_ID ASC
)
go
/*==============================================================*/
/* Index: O_D_ID */
/*==============================================================*/
create index O_D_ID on "ORDER" (
O_D_ID ASC
)
go
/*==============================================================*/
/* Index: O_W_ID */
/*==============================================================*/
create index O_W_ID on "ORDER" (
O_W_ID ASC
)
go
/*==============================================================*/
/* Index: O_C_ID */
/*==============================================================*/
create index O_C_ID on "ORDER" (
O_C_ID ASC
)
go
/*==============================================================*/
/* Table: ORDER_LINE */
/*==============================================================*/
create table ORDER_LINE (
OL_O_ID int default '0' not null,
OL_D_ID smallint default '0' not null,
OL_W_ID smallint default '0' not null,
OL_NUMBER int default '0' not null,
OL_I_ID int null,
OL_SUPPLY_W_ID smallint null,
OL_DELIVERY_D DATETIME null,
OL_QUANTITY smallint null,
OL_AMOUNT decimal(6,2) null,
OL_DIST_INFO char(24) null,
constraint OL_PRIMARY primary key (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER)
)
lock datarows
go
/*==============================================================*/
/* Index: IDX1 */
/*==============================================================*/
create index IDX1 on ORDER_LINE (
OL_W_ID ASC,
OL_I_ID ASC,
OL_D_ID ASC,
OL_O_ID ASC
)
go
/*==============================================================*/
/* Index: IDX2 */
/*==============================================================*/
create index IDX2 on ORDER_LINE (
OL_O_ID ASC,
OL_D_ID ASC,
OL_W_ID ASC
)
go
/*==============================================================*/
/* Index: OL_O_ID */
/*==============================================================*/
create index OL_O_ID on ORDER_LINE (
OL_O_ID ASC
)
go
/*==============================================================*/
/* Index: OL_D_ID */
/*==============================================================*/
create index OL_D_ID on ORDER_LINE (
OL_D_ID ASC
)
go
/*==============================================================*/
/* Index: OL_W_ID */
/*==============================================================*/
create index OL_W_ID on ORDER_LINE (
OL_W_ID ASC
)
go
/*==============================================================*/
/* Index: OL_I_ID */
/*==============================================================*/
create index OL_I_ID on ORDER_LINE (
OL_I_ID ASC
)
go
/*==============================================================*/
/* Index: OL_SUPPLY_W_ID */
/*==============================================================*/
create index OL_SUPPLY_W_ID on ORDER_LINE (
OL_SUPPLY_W_ID ASC
)
go
/*==============================================================*/
/* Table: STOCK */
/*==============================================================*/
create table STOCK (
S_I_ID int default '0' not null,
S_W_ID smallint default '0' not null,
S_QUANTITY int default '0' not null,
S_DIST_01 char(24) null,
S_DIST_02 char(24) null,
S_DIST_03 char(24) null,
S_DIST_04 char(24) null,
S_DIST_05 char(24) null,
S_DIST_06 char(24) null,
S_DIST_07 char(24) null,
S_DIST_08 char(24) null,
S_DIST_09 char(24) null,
S_DIST_10 char(24) null,
S_YTD int null,
S_ORDER_CNT int null,
S_REMOTE_CNT int null,
S_DATA varchar(50) null,
constraint S_PRIMARY primary key (S_W_ID, S_I_ID)
)
lock datarows
go
/*==============================================================*/
/* Index: S_I_ID */
/*==============================================================*/
create index S_I_ID on STOCK (
S_I_ID ASC
)
go
/*==============================================================*/
/* Index: S_W_ID */
/*==============================================================*/
create index S_W_ID on STOCK (
S_W_ID ASC
)
go
/*==============================================================*/
/* Table: WAREHOUSE */
/*==============================================================*/
create table WAREHOUSE (
W_ID smallint default '0' not null,
W_NAME varchar(10) null,
W_STREET_1 varchar(20) null,
W_STREET_2 varchar(20) null,
W_CITY varchar(20) null,
W_STATE char(2) null,
W_ZIP char(9) null,
W_TAX decimal(4,4) null,
W_YTD decimal(12,2) null,
constraint W_PRIMARY primary key (W_ID)
)
lock datarows
go
alter table CUSTOMER
add constraint C_FKEY_D foreign key (C_W_ID, C_D_ID)
references DISTRICT (D_W_ID, D_ID)
go
alter table DISTRICT
add constraint D_FKEY_W foreign key (D_W_ID)
references WAREHOUSE (W_ID)
go
alter table HISTORY
add constraint H_FKEY_C foreign key (H_C_W_ID, H_C_D_ID, H_C_ID)
references CUSTOMER (C_W_ID, C_D_ID, C_ID)
go
alter table HISTORY
add constraint H_FKEY_D foreign key (H_W_ID, H_D_ID)
references DISTRICT (D_W_ID, D_ID)
go
alter table NEW_ORDER
add constraint NO_FKEY_O foreign key (NO_W_ID, NO_D_ID, NO_O_ID)
references "ORDER" (O_W_ID, O_D_ID, O_ID)
go
alter table "ORDER"
add constraint O_FKEY_C foreign key (O_W_ID, O_D_ID, O_C_ID)
references CUSTOMER (C_W_ID, C_D_ID, C_ID)
go
alter table ORDER_LINE
add constraint OL_FKEY_O foreign key (OL_W_ID, OL_D_ID, OL_O_ID)
references "ORDER" (O_W_ID, O_D_ID, O_ID)
go
alter table ORDER_LINE
add constraint OL_FKEY_S foreign key (OL_SUPPLY_W_ID, OL_I_ID)
references STOCK (S_W_ID, S_I_ID)
go
alter table STOCK
add constraint S_FKEY_I foreign key (S_I_ID)
references ITEM (I_ID)
go
alter table STOCK
add constraint S_FKEY_W foreign key (S_W_ID)
references WAREHOUSE (W_ID)
go
11. Create remote connection to SRS from HDLRE
Connect to the HDLRE endpoint using the HDLRE client using the Identification parameters:
- Authentication: Database
- User ID: HDLADMIN
- Password: <password created in step 7>
- Action: Connect to Data Lake IQ
- Instance ID: <Instance ID from coordinator SQL Endpoint FQDN>
- Landscape: <remainder of FQDN without Instance ID>
or use the SQL Console of SAP HANA Cloud Central directly from the HDLRE instance.
(the following steps are documented at Create an SAP Replication Server On-Premise Remote Server | SAP Help Portal. Where differences exist, please follow the documentation).
Run the following SQL in the HDLRE console to create the connection to SRS required for replication to HDLRE:
CREATE SERVER SAMPLE_RS class 'ASEODBC'
USING 'Host=sample_rs:11752;
LocationID=repdemo;
UseCloudConnector=ON';
(The 'LocationID' is the value created in step 5. The 'Host' is the virtual host and port defined in step 5).
Create a new HDLRE login:
create user tpcc identified by <password>;
grant create table to tpcc;
grant create procedure to tpcc;Map the login for HDLRE to use with SRS:
create externlogin tpcc to SAMPLE_RS
remote login sa identified by '<SRS sa login password>';
12. Create the tables in HDLRE
Reconnect to the HDLRE SQL console using the new login created above. Run the following script to create the tables, columns and indexes (no RI is defined in this script to allow only specific tables to be replicated if desired):
/*==============================================================*/
/* Table: CUSTOMER */
/*==============================================================*/
create table CUSTOMER (
C_ID integer not null default '0',
C_D_ID smallint not null default '0',
C_W_ID smallint not null default '0',
C_FIRST varchar(16) null,
C_MIDDLE char(2) null,
C_LAST varchar(16) null,
C_STREET_1 varchar(20) null,
C_STREET_2 varchar(20) null,
C_CITY varchar(20) null,
C_STATE char(2) null,
C_ZIP char(9) null,
C_PHONE char(16) null,
C_SINCE timestamp null default 'getdate()',
C_CREDIT char(2) null,
C_CREDIT_LIM decimal(12,2) null,
C_DISCOUNT decimal(4,4) null,
C_BALANCE decimal(12,2) null,
C_YTD_PAYMENT decimal(12,2) null,
C_PAYMENT_CNT integer null,
C_DELIVERY_CNT integer null,
C_DATA varchar(500) null,
constraint C_PRIMARY primary key (C_W_ID, C_D_ID, C_ID) );
/*==============================================================*/
/* Index: C_C_ID */
/*==============================================================*/
create HG index C_C_ID on CUSTOMER (
C_ID
);
/*==============================================================*/
/* Index: C_D_ID */
/*==============================================================*/
create HG index C_D_ID on CUSTOMER (
C_D_ID
);
/*==============================================================*/
/* Index: C_W_ID */
/*==============================================================*/
create HG index C_W_ID on CUSTOMER (
C_W_ID
);
/*==============================================================*/
/* Table: DISTRICT */
/*==============================================================*/
create table DISTRICT (
D_ID smallint not null default '0',
D_W_ID smallint not null default '0',
D_NAME varchar(10) null,
D_STREET_1 varchar(20) null,
D_STREET_2 varchar(20) null,
D_CITY varchar(20) null,
D_STATE char(2) null,
D_ZIP char(9) null,
D_TAX decimal(4,4) null,
D_YTD decimal(12,2) null,
D_NEXT_O_ID integer null,
constraint D_PRIMARY primary key (D_W_ID, D_ID) );
/*==============================================================*/
/* Index: D_D_ID */
/*==============================================================*/
create HG index D_D_ID on DISTRICT (
D_ID
);
/*==============================================================*/
/* Table: HISTORY */
/*==============================================================*/
create table HISTORY (
H_C_ID integer null,
H_C_D_ID smallint null,
H_C_W_ID smallint null,
H_D_ID smallint null,
H_W_ID smallint null default '0',
H_DATE timestamp null default 'getdate()',
H_AMOUNT decimal(6,2) null,
H_DATA varchar(24) null );
/*==============================================================*/
/* Table: ITEM */
/*==============================================================*/
create table ITEM (
I_ID integer not null default '0',
I_IM_ID integer null,
I_NAME varchar(24) null,
I_PRICE decimal(5,2) null,
I_DATA varchar(50) null,
constraint PK_ITEM primary key (I_ID) );
/*==============================================================*/
/* Table: NEW_ORDER */
/*==============================================================*/
create table NEW_ORDER (
NO_O_ID integer not null default '0',
NO_D_ID smallint not null default '0',
NO_W_ID smallint not null default '0',
constraint NO_PRIMARY primary key (NO_W_ID, NO_D_ID, NO_O_ID) );
/*==============================================================*/
/* Table: "ORDER" */
/*==============================================================*/
create table "ORDER" (
O_ID integer not null default '0',
O_D_ID smallint not null default '0',
O_W_ID smallint not null default '0',
O_C_ID integer null,
O_ENTRY_D timestamp null default 'getdate()',
O_CARRIER_ID integer null,
O_OL_CNT smallint null,
O_ALL_LOCAL smallint null,
constraint O_PRIMARY primary key (O_W_ID, O_D_ID, O_ID) );
/*==============================================================*/
/* Index: O_O_ID */
/*==============================================================*/
create HG index O_O_ID on "ORDER" (
O_ID
);
/*==============================================================*/
/* Index: O_D_ID */
/*==============================================================*/
create HG index O_D_ID on "ORDER" (
O_D_ID
);
/*==============================================================*/
/* Index: O_W_ID */
/*==============================================================*/
create HG index O_W_ID on "ORDER" (
O_W_ID
);
/*==============================================================*/
/* Index: O_C_ID */
/*==============================================================*/
create HG index O_C_ID on "ORDER" (
O_C_ID
);
/*==============================================================*/
/* Table: ORDER_LINE */
/*==============================================================*/
create table ORDER_LINE (
OL_O_ID integer not null default '0',
OL_D_ID smallint not null default '0',
OL_W_ID smallint not null default '0',
OL_NUMBER integer not null default '0',
OL_I_ID integer null,
OL_SUPPLY_W_ID smallint null,
OL_DELIVERY_D timestamp null,
OL_QUANTITY smallint null,
OL_AMOUNT decimal(6,2) null,
OL_DIST_INFO char(24) null,
constraint OL_PRIMARY primary key (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER) );
/*==============================================================*/
/* Index: IDX1 */
/*==============================================================*/
create HG index IDX1 on ORDER_LINE (
OL_W_ID,
OL_I_ID,
OL_D_ID,
OL_O_ID
);
/*==============================================================*/
/* Index: IDX2 */
/*==============================================================*/
create HG index IDX2 on ORDER_LINE (
OL_O_ID,
OL_D_ID,
OL_W_ID
);
/*==============================================================*/
/* Index: OL_O_ID */
/*==============================================================*/
create HG index OL_O_ID on ORDER_LINE (
OL_O_ID
);
/*==============================================================*/
/* Index: OL_D_ID */
/*==============================================================*/
create HG index OL_D_ID on ORDER_LINE (
OL_D_ID
);
/*==============================================================*/
/* Index: OL_W_ID */
/*==============================================================*/
create HG index OL_W_ID on ORDER_LINE (
OL_W_ID
);
/*==============================================================*/
/* Index: OL_I_ID */
/*==============================================================*/
create HG index OL_I_ID on ORDER_LINE (
OL_I_ID
);
/*==============================================================*/
/* Index: OL_SUPPLY_W_ID */
/*==============================================================*/
create HG index OL_SUPPLY_W_ID on ORDER_LINE (
OL_SUPPLY_W_ID
);
/*==============================================================*/
/* Table: STOCK */
/*==============================================================*/
create table STOCK (
S_I_ID integer not null default '0',
S_W_ID smallint not null default '0',
S_QUANTITY integer not null default '0',
S_DIST_01 char(24) null,
S_DIST_02 char(24) null,
S_DIST_03 char(24) null,
S_DIST_04 char(24) null,
S_DIST_05 char(24) null,
S_DIST_06 char(24) null,
S_DIST_07 char(24) null,
S_DIST_08 char(24) null,
S_DIST_09 char(24) null,
S_DIST_10 char(24) null,
S_YTD integer null,
S_ORDER_CNT integer null,
S_REMOTE_CNT integer null,
S_DATA varchar(50) null,
constraint S_PRIMARY primary key (S_W_ID, S_I_ID) );
/*==============================================================*/
/* Table: WAREHOUSE */
/*==============================================================*/
create table WAREHOUSE (
W_ID smallint not null default '0',
W_NAME varchar(10) null,
W_STREET_1 varchar(20) null,
W_STREET_2 varchar(20) null,
W_CITY varchar(20) null,
W_STATE char(2) null,
W_ZIP char(9) null,
W_TAX decimal(4,4) null,
W_YTD decimal(12,2) null,
constraint W_PRIMARY primary key (W_ID) );
13. Create connection from SRS to ASE
As ASE holds the source database, the connection must be created using rs_init. The rs_init resource file to use will be:
sybinit.release_directory: <$SYBASE from SRS installation>
sybinit.product: rs
rs.rs_operation: rs_setup_db
#
rs.rs_name: SAMPLE_RS
rs.rs_rs_sa_user: sa
rs.rs_rs_sa_pass: <SRS sa password>
#
rs.rs_ds_name: SAMPLE_ASE
# sa login for this Adaptive Server
rs.rs_ds_sa_user: sa
rs.rs_ds_sa_password: <ASE sa password>
# name of the database that you are adding to the replicated data system
rs.rs_db_name: tpcc
rs.rs_needs_repagent: yes
# name of the user who will update replicated data
# default <rs_db_name>_maint
# rs.rs_db_maint_user: USE_DEFAULT
rs.rs_db_maint_user: tpcc_maint
rs.rs_db_maint_password: <create a pwd>
# rep agent login
rs.rs_ltm_rs_user: SAMPLE_RS_ra
rs.rs_ltm_rs_pass: <rs.rs_ltm_rs_pass from SAMPLE_RS rs_init creation resource file>(User tpcc_maint will not actually be used as there will be no replication into the tpcc database.)
Create the resource file on the SRS server and submit to the SRS instance using the SRS host shell:
$SYBASE/$SYBASE_REP/install/rs_init -r <resource file>14. Configure the ASE replication agent
Connect to the ASE instance as 'sa' with isql/DBISQL and issue the following:
use tpcc
go
exec sp_reptostandby tpcc, 'all'
go
exec sp_config_rep_agent tpcc,send_warm_standby_xacts,true
go
exec sp_stop_rep_agent tpcc
go
exec sp_start_rep_agent tpcc
go
15. Mark tables for replication
exec sp_setreptable ITEM, 'true', owner_on
exec sp_setreptable WAREHOUSE, 'true', owner_on
exec sp_setreptable DISTRICT, 'true', owner_on
exec sp_setreptable STOCK, 'true', owner_on
exec sp_setreptable CUSTOMER, 'true', owner_on
exec sp_setreptable 'ORDER', 'true', owner_on
exec sp_setreptable HISTORY, 'true', owner_on
exec sp_setreptable ORDER_LINE, 'true', owner_on
exec sp_setreptable NEW_ORDER, 'true', owner_on
go
16. Create connection from SRS to HDLRE
Connect to SAMPLE_RS as 'sa' using isql/DBISQL and issue the following RCL:
create connection to HDLRE.iqaas using profile rs_ase_to_iq;standard
set username to tpcc
set password to "<password>"
set dsi_quoted_identifier to "always"
set dsi_compile_enable to "on"
set ddl_dup_detect to "off"
set dsi_compile_retry_threshold to "0"
17. Create database replication definitions
Remain connected to the SRS and issue the following RCL commands to create the table replication definitions:
create replication definition ITEM_rep
with primary at SAMPLE_ASE.tpcc
with primary table named dbo.'ITEM'
with replicate table named 'ITEM'
(
I_ID int,
I_IM_ID int,
I_NAME varchar(24),
I_PRICE decimal,
I_DATA varchar(50)
)
primary key (I_ID)
go
create replication definition WAREHOUSE_rep
with primary at SAMPLE_ASE.tpcc
with primary table named dbo.'WAREHOUSE'
with replicate table named 'WAREHOUSE'
(
W_ID int,
W_NAME varchar(10),
W_STREET_1 varchar(20),
W_STREET_2 varchar(20),
W_CITY varchar(20),
W_STATE char(2),
W_ZIP char(9),
W_TAX decimal,
W_YTD decimal
)
primary key (W_ID)
go
create replication definition DISTRICT_rep
with primary at SAMPLE_ASE.tpcc
with primary table named dbo.'DISTRICT'
with replicate table named 'DISTRICT'
(
D_ID int,
D_W_ID int,
D_NAME varchar(10),
D_STREET_1 varchar(20),
D_STREET_2 varchar(20),
D_CITY varchar(20),
D_STATE char(2),
D_ZIP char(9),
D_TAX decimal,
D_YTD decimal,
D_NEXT_O_ID int
)
primary key (D_W_ID, D_ID)
go
create replication definition STOCK_rep with
primary at SAMPLE_ASE.tpcc
with primary table named dbo.'STOCK'
with replicate table named 'STOCK'
(
S_I_ID int,
S_W_ID int,
S_QUANTITY int,
S_DIST_01 char(24),
S_DIST_02 char(24),
S_DIST_03 char(24),
S_DIST_04 char(24),
S_DIST_05 char(24),
S_DIST_06 char(24),
S_DIST_07 char(24),
S_DIST_08 char(24),
S_DIST_09 char(24),
S_DIST_10 char(24),
S_YTD int,
S_ORDER_CNT int,
S_REMOTE_CNT int,
S_DATA varchar(50)
)
primary key (S_W_ID, S_I_ID)
go
create replication definition CUSTOMER_rep
with primary at SAMPLE_ASE.tpcc
with primary table named dbo.'CUSTOMER'
with replicate table named 'CUSTOMER'
(
C_ID int,
C_D_ID int,
C_W_ID int,
C_FIRST varchar(16),
C_MIDDLE char(2),
C_LAST varchar(16),
C_STREET_1 varchar(20),
C_STREET_2 varchar(20),
C_CITY varchar(20),
C_STATE char(2),
C_ZIP char(9),
C_PHONE char(16),
C_SINCE datetime,
C_CREDIT char(2),
C_CREDIT_LIM decimal,
C_DISCOUNT decimal,
C_BALANCE decimal,
C_YTD_PAYMENT decimal,
C_PAYMENT_CNT integer,
C_DELIVERY_CNT integer,
C_DATA varchar(500)
)
primary key (C_W_ID, C_D_ID, C_ID)
go
create replication definition ORDER_rep
with primary at SAMPLE_ASE.tpcc
with primary table named dbo.'ORDER'
with replicate table named 'ORDER' quoted
(
O_ID int,
O_D_ID int,
O_W_ID int,
O_C_ID int,
O_ENTRY_D datetime,
O_CARRIER_ID int,
O_OL_CNT int,
O_ALL_LOCAL int
)
primary key (O_W_ID, O_D_ID, O_ID)
go
create replication definition HISTORY_rep
with primary at SAMPLE_ASE.tpcc
with primary table named dbo.'HISTORY'
with replicate table named 'HISTORY'
(
H_C_ID int,
H_C_D_ID int,
H_C_W_ID int,
H_D_ID int,
H_W_ID int,
H_DATE datetime,
H_AMOUNT decimal,
H_DATA varchar(24)
)
primary key (H_C_ID, H_C_D_ID, H_W_ID, H_D_ID, H_W_ID)
go
create replication definition ORDER_LINE_rep
with primary at SAMPLE_ASE.tpcc
with primary table named dbo.'ORDER_LINE'
with replicate table named 'ORDER_LINE'
(
OL_O_ID int,
OL_D_ID int,
OL_W_ID int,
OL_NUMBER int,
OL_I_ID int,
OL_SUPPLY_W_ID int,
OL_DELIVERY_D datetime,
OL_QUANTITY int,
OL_AMOUNT decimal,
OL_DIST_INFO char(24)
)
primary key (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER)
go
create replication definition NEW_ORDER_rep
with primary at SAMPLE_ASE.tpcc
with primary table named dbo.'NEW_ORDER'
with replicate table named 'NEW_ORDER'
(
NO_O_ID int,
NO_D_ID int,
NO_W_ID int
)
primary key (NO_W_ID, NO_D_ID, NO_O_ID)
go
18. Load data into ASE database
In this example, the application used to load the data performs the loading of data using java.sql.PreparedStatement.executeBatch() and java.sql.PreparedStatement.executeUpdate().
After loading the ASE database contains the following record counts:
tablename records
-------------------- --------------------
ITEM 100000
DISTRICT 10
WAREHOUSE 1
STOCK 100000
CUSTOMER 30000
ORDER 30000
ORDER_LINE 300271
HISTORY 30000
NEW_ORDER 9000
19. Create and validate the table subscriptions for HDLRE
(Do not apply any transactions to the source database while performing the next steps)
Subscription creation:
create subscription HDLRE_ITEM_sub for ITEM_rep
with replicate at HDLRE.iqaas
without holdlock
direct_load
user tpcc password <password>
subscribe to truncate table
go
create subscription HDLRE_WAREHOUSE_sub for WAREHOUSE_rep
with replicate at HDLRE.iqaas
without holdlock
direct_load
user tpcc password <password>
subscribe to truncate table
go
create subscription HDLRE_DISTRICT_sub for DISTRICT_rep
with replicate at HDLRE.iqaas
without holdlock
direct_load
user tpcc password <password>
subscribe to truncate table
go
create subscription HDLRE_STOCK_sub for STOCK_rep
with replicate at HDLRE.iqaas
without holdlock
direct_load
user tpcc password <password>
subscribe to truncate table
go
create subscription HDLRE_CUSTOMER_sub for CUSTOMER_rep
with replicate at HDLRE.iqaas
without holdlock
direct_load
user tpcc password <password>
subscribe to truncate table
go
create subscription HDLRE_ORDER_sub for ORDER_rep
with replicate at HDLRE.iqaas
without holdlock
direct_load
user tpcc password <password>
subscribe to truncate table
go
create subscription HDLRE_HISTORY_sub for HISTORY_rep
with replicate at HDLRE.iqaas
without holdlock
direct_load
user tpcc password <password>
subscribe to truncate table
go
create subscription HDLRE_ORDER_LINE_sub for ORDER_LINE_rep
with replicate at HDLRE.iqaas
without holdlock
direct_load
user tpcc password <password>
subscribe to truncate table
go
create subscription HDLRE_NEW_ORDER_sub for NEW_ORDER_rep
with replicate at HDLRE.iqaas
without holdlock
direct_load
user tpcc password <password>
subscribe to truncate table
go
A separate subscription thread is spawned in the SRS server for each table. Setting a larger memory configuration in Step 2 allows the subscritions to materialize in parallel (see example 'admin who' output below):
1> admin who
2> go
Spid Name State Info
---- --------------- ------------------------------ ------------------------------------------------------------
28 DSI EXEC Awaiting Command 101(1) SAMPLE_RS_ERSSD.SAMPLE_RS_ERSSD
18 DSI Awaiting SQM writer 101 SAMPLE_RS_ERSSD.SAMPLE_RS_ERSSD
14 SQM Awaiting Message 101:0 SAMPLE_RS_ERSSD.SAMPLE_RS_ERSSD
DSI EXEC Suspended 102(1) SAMPLE_ASE.tpcc
DSI Suspended 102 SAMPLE_ASE.tpcc
22 DIST Awaiting Message 102 SAMPLE_ASE.tpcc
27 SQT Awaiting Wakeup 102:1 DIST SAMPLE_ASE.tpcc
16 SQM Awaiting Message 102:1 SAMPLE_ASE.tpcc
15 SQM Awaiting Message 102:0 SAMPLE_ASE.tpcc
31 REP AGENT Awaiting Command SAMPLE_ASE.tpcc
101 DSI EXEC Awaiting Command 103(1) HDLRE.iqaas
99 DSI Awaiting SQM writer 103 HDLRE.iqaas
17 SQM Awaiting Message 103:0 HDLRE.iqaas
20 dSUB Active
6 dCM Awaiting Message
12 dAIO Awaiting Message
24 dREC Sleeping dREC
13 dDELSEG Awaiting Message
44 USER Active sa
172 USER Awaiting Command sa
173 USER Awaiting Command sa
174 USER Awaiting Command sa
175 USER Awaiting Command sa
102 SUB Awaiting Wakeup HDLRE_ITEM_sub
112 SUB Active HDLRE_DISTRICT_sub
115 SUB Awaiting Wakeup HDLRE_STOCK_sub
122 SUB Active HDLRE_CUSTOMER_sub
127 SUB Active HDLRE_ORDER_sub
139 SUB Awaiting Wakeup HDLRE_ORDER_LINE_sub
5 dALARM Awaiting Wakeup
25 dSYSAM Sleeping
Each subscription can be validated as follows:
check subscription HDLRE_ITEM_sub for ITEM_rep
with replicate at HDLRE.iqaas
go
check subscription HDLRE_WAREHOUSE_sub for WAREHOUSE_rep
with replicate at HDLRE.iqaas
go
check subscription HDLRE_DISTRICT_sub for DISTRICT_rep
with replicate at HDLRE.iqaas
go
check subscription HDLRE_STOCK_sub for STOCK_rep
with replicate at HDLRE.iqaas
go
check subscription HDLRE_CUSTOMER_sub for CUSTOMER_rep
with replicate at HDLRE.iqaas
go
check subscription HDLRE_ORDER_sub for ORDER_rep
with replicate at HDLRE.iqaas
go
check subscription HDLRE_HISTORY_sub for HISTORY_rep
with replicate at HDLRE.iqaas
go
check subscription HDLRE_ORDER_LINE_sub for ORDER_LINE_rep
with replicate at HDLRE.iqaas
go
check subscription HDLRE_NEW_ORDER_sub for NEW_ORDER_rep
with replicate at HDLRE.iqaas
go
While a subscription is being materialized, an additional line of output showing the progress of materialization will be output when checked. e.g.:
Subscription HDLRE_ORDER_LINE_sub is ACTIVE at the replicate.
Subscription HDLRE_ORDER_LINE_sub is ACTIVE at the primary.
Subscriptions HDLRE_ORDER_LINE_sub progress: initial loading, 94% done, 15923 commands remaining.When materialization is complete, the 'SUB' thread will not show in 'admin who' output and the additional progress output will be removed from the 'check subscription' output.
The record counts in HDLRE will match:
ITEM 100000
DISTRICT 10
WAREHOUSE 1
STOCK 100000
CUSTOMER 30000
ORDER 30000
ORDER_LINE 300271
HISTORY 30000
NEW_ORDER 9000
20. Run transactional load on ASE database
When materialization is complete for all subscriptions, transactions can be started on the source ASE database.
Replication will continue as more transactions are applied at the source database.
Finally
That's it!
Thanks to Rob Waywell, Ashutosh Singh, Jim Summers, John Smirnios, Mark Mumy and many others for their work on contributing to this blog.
