Symptom
Oracle "DATE" datatypes that are replicated to HANA "timestamp"
datatypes are not showing the correct time in HANA. In the replication
definition the "DATE" column is defined as an "rs_oracle_datetime"
datatype.
For example: From Oracle when "May 5, 2014 10:34:12" is replicated to HANA, the datetime value changed to "May 5, 2014 12:00:00".
For example: From Oracle when "May 5, 2014 10:34:12" is replicated to HANA, the datetime value changed to "May 5, 2014 12:00:00".
Environment
- SAP Replication Server RS 15.7.1 SP100 and UP
- SAP Replication Server Option for Oracle 15.7 and UP
Cause
Root Cause: Missing the following translation information from
"rs_oracle_datetime" to "rs_hanadb_timestamp" in the RSSD rs_translation
table.
select classid, type, source_dtid, target_dtid from rs_translation where source_dtid=0x0000000000010200
classid type source_dtid target_dtid
------------------ ---- ------------------ ------------------
0x000000000100001e D 0x0000000000010200 0x0000000000010a00
where
classid 0x000000000100001e is rs_hanadb_function_class
source_dtid 0x0000000000010200 is rs_oracle_datetime
target_dtid 0x0000000000010a00 is rs_hanadb_timestamp
Issue the following query from RSSD to verify the datatype:
select dtid,dtname from rs_datatype where dtid in (0x0000000000010200, 0x0000000000010a00)
dtid dtname
------------------ ------------------------------
0x0000000000010a00 rs_hanadb_timestamp
0x0000000000010200 rs_oracle_datetime
Background: This heterogeneous replication system has two primaries (MSSQL and Oracle) and one target (HanaDB). Replication was setup from MSSQL to HANA first "using profile rs_msss_to_hanadb", successfully loaded datatype translation information from MSSQL to HANA. Since HANA connection has already created, when setting up replication from Oracle to HanaDB, there is no option to just load translation information without creating HANA connection "using profile rs_oracle_to_hanadb".
select classid, type, source_dtid, target_dtid from rs_translation where source_dtid=0x0000000000010200
classid type source_dtid target_dtid
------------------ ---- ------------------ ------------------
0x000000000100001e D 0x0000000000010200 0x0000000000010a00
where
classid 0x000000000100001e is rs_hanadb_function_class
source_dtid 0x0000000000010200 is rs_oracle_datetime
target_dtid 0x0000000000010a00 is rs_hanadb_timestamp
Issue the following query from RSSD to verify the datatype:
select dtid,dtname from rs_datatype where dtid in (0x0000000000010200, 0x0000000000010a00)
dtid dtname
------------------ ------------------------------
0x0000000000010a00 rs_hanadb_timestamp
0x0000000000010200 rs_oracle_datetime
Background: This heterogeneous replication system has two primaries (MSSQL and Oracle) and one target (HanaDB). Replication was setup from MSSQL to HANA first "using profile rs_msss_to_hanadb", successfully loaded datatype translation information from MSSQL to HANA. Since HANA connection has already created, when setting up replication from Oracle to HanaDB, there is no option to just load translation information without creating HANA connection "using profile rs_oracle_to_hanadb".
Resolution
New feature request CR 763862 (Add "add profile" option on alter
connection) was opened with engineering to add a "add profile" option to
the "alter connection" command.
This option allows add additional profile to the existing replicate connection. For example, if an existing replication is from MSSQL to HANA, customer should be able to setup replication from Oracle to HANA by the following alter command:
alter connection to HANADB.hanadb add profile rs_oracle_to_hanadb;ech
Current Solution, when setting up heterogeneous replication from different primaries to the same replicate the best practice is to create a ‘dummy’ connection for the additional profiles.
Here is an example:
1) Setup MSSQL to HANA using normal MS to HANA connection profile.
create connection to HANADB.hanadb
using profile rs_msss_to_hanadb;ech
set username to "<user>"
set password to "<password>"
2) Next, create a dummy connection from Oracle to HANA.
create connection to dummy.dummy
using profile rs_oracle_to_hanadb;ech
set username to "<dummy>"
The connection creation will fail (dummy is not a valid user), however, the Class Level Translations, as well as anything else the profile may wish to do in the RSSD will be done.
Then, drop the dummy connection
drop connection to dummy.dummy
Suspend and resume the real connection, so that the changes to the RSSD are re-loaded.
3) Alternatively, use the ‘display_only’ syntax, to see and apply the RSSD changes manually.
create connection to dummy.dummy
using profile rs_oracle_to_hanadb;ech
set user name to dummy
display_only
With the "display_only" option, RepServer will show (to the client session and its error log) all of the
commands it would have executed and where they would have been executed (RSSD, Replicate Data Server,
or RepServer itself). Manually apply all insert rs_translation command to the RSSD.
This option allows add additional profile to the existing replicate connection. For example, if an existing replication is from MSSQL to HANA, customer should be able to setup replication from Oracle to HANA by the following alter command:
alter connection to HANADB.hanadb add profile rs_oracle_to_hanadb;ech
Current Solution, when setting up heterogeneous replication from different primaries to the same replicate the best practice is to create a ‘dummy’ connection for the additional profiles.
Here is an example:
1) Setup MSSQL to HANA using normal MS to HANA connection profile.
create connection to HANADB.hanadb
using profile rs_msss_to_hanadb;ech
set username to "<user>"
set password to "<password>"
2) Next, create a dummy connection from Oracle to HANA.
create connection to dummy.dummy
using profile rs_oracle_to_hanadb;ech
set username to "<dummy>"
The connection creation will fail (dummy is not a valid user), however, the Class Level Translations, as well as anything else the profile may wish to do in the RSSD will be done.
Then, drop the dummy connection
drop connection to dummy.dummy
Suspend and resume the real connection, so that the changes to the RSSD are re-loaded.
3) Alternatively, use the ‘display_only’ syntax, to see and apply the RSSD changes manually.
create connection to dummy.dummy
using profile rs_oracle_to_hanadb;ech
set user name to dummy
display_only
With the "display_only" option, RepServer will show (to the client session and its error log) all of the
commands it would have executed and where they would have been executed (RSSD, Replicate Data Server,
or RepServer itself). Manually apply all insert rs_translation command to the RSSD.
Keywords
HANA timestamp, Oracle date, ExpressConnect for SAP HANA Database(ech)
Header Data
| Released On | 02.09.2015 16:01:19 |
| Release Status | Released to Customer |
| Component | BC-SYB-REP-RSO Rep Server Options (RSO) |
| Priority | Normal |
| Category | Problem |
No comments:
Post a Comment