Infrastructure at your Service

Stéphane Haby

Attunity Replicate: Replicate numeric data types from Oracle to SQL Server is easy

After the good article “A short glance at Attunity replicate” from Franck Pachot, I will continue to explain our test on Attunity Replicate with datatype and how this tool do the mapping between Oracle and SQL Server for numeric datatypes.

Introduction

The first step is to find the mapping between Oracle and SQL Server.
I summarize this mapping in this table:
attunity_replicate02

As you known (or not), in SQL Server, you have a replication with Oracle directly but this functionality is deprecated
attunity_replicate01

In the msdn, you have a default data type mapping here.
For numeric datatype, the mapping is approximatively the same as above.
To complete this part, you can also specify alternative data type mapping here.

This functionally is deprecated and I decide to find an alternative. I find that Attunity replicate is a good way to replicate from Oracle to SQL Server near real time.
Attunity Replicate has a self-datatype table for the mapping.
In the user guide from Attunity Replicate, you have the mapping between Oracle and the Attunity Data types.
If I summarize my previous mapping, I have this mapping:
attunity_replicate03
Then next step is to realize the mapping between the Attunity data type and the SQL Server data type, as explained in the user guide.
The mapping will give us this mapping:
attunity_replicate04
Oops, I have some “no match” like BIT, TINYINT, BIGINT, DECIMAL, MONEY or SMALLMONEY due to the intermediate Attunity data type mapping
attunity_replicate05

This is the theory, and now let’s go to the practice…

Create the source & destination connections

To create the task, follow the blog by Frank here.
In my case, I just change the destination database with a SQL Server connection
Add SQL Server image
One thing to not forget is to test the connection with the button on the bottom-left of the connection window
attunity_replicate07.png

Create the source table and destination table

With SQL Developer, I create in Oracle a table NUMERIC_DT in the schema OE to have all numerical data types from my example

CREATE TABLE OE.NUMERIC_DT 
(
 NUMBER_BIT NUMBER(1),
NUMBER_TINYINT NUMBER(3),
NUMBER_SMALLINT NUMBER(5),
NUMBER_INTEGER NUMBER(10),
NUMBER_BIGINT NUMBER(19),
BINARYFLOAT_REAL BINARY_FLOAT,
BINARYDOUBLE_FLOAT BINARY_DOUBLE,
FLOAT_FLOAT FLOAT,
REAL_FLOAT REAL,
NUMBER_NUMERIC NUMBER(30,20),
NUMBER_DECIMAL NUMBER(20,10),
NUMBER_MONEY NUMBER(19,4),
NUMBER_SMALLMONEY NUMBER(10,4)
)

attunity_01

With Attunity Replicate, I use the console to replicate my table through the web browser.
Then Select the schema OE in Oracle and now the table NUMERIC_DT.
attunity_replicate10
Now, I start the replication with the Run button
attunity_replicate11
The first action when you start the replication is a Full load.
In my case I just create the table without data so you have “Transferred count” value set to 0 in the monitoring
attunity_replicate12
I start SSMS to see the table that Attunity created in the SQL Server destination
attunity_replicate13
The result matches with my theorical table above.
To have a comparison, my colleague Vincent Matthey do the same test with the tool SQL Server Migration Assistant (SSMA) for Oracle
With SSMS, we can see the result
STH_SQL_Server

The result is a little strange… As you can see, all defined SQL Server integer datatype like TINYINT, SMALLINT, … are NUMERIC with a scale of 0 to have integer! For example, the NUMBER(1) becomes a numeric(1,0). It is not false but also not right at all!

Insert Data

I create a script to put some data in this table, to see if the replication works:

BEGIN
  for i in 1..15000
  loop
 insert into OE.NUMERIC_DT
(NUMBER_BIT,NUMBER_TINYINT,NUMBER_SMALLINT,NUMBER_INTEGER,NUMBER_BIGINT
,BINARYFLOAT_REAL,BINARYDOUBLE_FLOAT,FLOAT_FLOAT,REAL_FLOAT,NUMBER_NUMERIC
,NUMBER_DECIMAL,NUMBER_MONEY,NUMBER_SMALLMONEY)
VALUES( TRUNC(dbms_random.value(0,1)), 
TRUNC(dbms_random.value(1,255)),
TRUNC(dbms_random.value(1,32767)),
TRUNC(dbms_random.value(1,2147483647)),
TRUNC(dbms_random.value(1,9223372036854775807)),
dbms_random.value(1,9999999999),
dbms_random.value(1,9999999999),
dbms_random.value(1,9223372036854775807),
dbms_random.value(1,999999999999999999999999999999999999999999999999999999999999),
dbms_random.value(1,9999999999),
dbms_random.value(1,9999999999),
dbms_random.value(1,999999999999999),
dbms_random.value(1,999999));
end loop;
  commit;
  END;

attunity_replicate14
In the monitoring tool from Attunity, you have a powerful graphical monitoring to see all operations during the replication
attunity_replicate15
To be sure that the replication has correctly replicated the 15000 rows, I do a select in the SQL Server destination database and I see that I have all rows.
attunity_replicate16

Update data

Just to see the power of this interface, I run an update on Oracle side of all rows with NUMBER_BIT equals 0:

UPDATE OE.NUMERIC_DT
SET NUMBER_BIT=TRUNC(dbms_random.value(0,1)),
NUMBER_TINYINT=TRUNC(dbms_random.value(0,255)),
NUMBER_SMALLINT=TRUNC(dbms_random.value(1,32767)),
NUMBER_INTEGER=TRUNC(dbms_random.value(1,2147483647)),
NUMBER_BIGINT=TRUNC(dbms_random.value(1,9223372036854775807)),
BINARYFLOAT_REAL=TRUNC(dbms_random.value(1,9223372036854775807)),
BINARYDOUBLE_FLOAT=dbms_random.value(1,9999999999),
FLOAT_FLOAT=dbms_random.value(1,9999999999),
REAL_FLOAT=dbms_random.value(1,999999999999999999999999999999999999999999999999999999999999),
NUMBER_NUMERIC=dbms_random.value(1,9999999999),
NUMBER_DECIMAL=dbms_random.value(1,9999999999),
NUMBER_MONEY=dbms_random.value(1,9999999999),
NUMBER_SMALLMONEY=dbms_random.value(1,999999)
WHERE NUMBER_BIT=0;
COMMIT;

You can see that I have in the Oracle source 9986 rows
attunity_replicate17
And the same on SQL Server destination
attunity_replicate18
I apply the update…
attunity_replicate19
In the Attunity monitoring, you can see in the changing Processing that I have 1 transaction in the incoming changes and I can see the latency. In my case, my lab is not set to have good performance…
attunity_replicate20
When the transaction is applied on the destination, you can see that the incoming changes has 0 transaction and that the Applied Changes pie has now the update stats.
attunity_replicate21

As you can see, the monitoring interface is very powerful!
And in live, it is even better ;-)

Delete data

The last command to test is the delete command

DELETE FROM OE.NUMERIC_DT WHERE NUMBER_BIT=0;
COMMIT; 

After this command, with the monitoring interface, you can see the 3 commands:
attunity_replicate23
And in the destination database, you can see that the delete was applied.

Conclusion

This tool is very good for a replication between heterogeneous database systems.
The creation of a staging database on the SQL Server destination from an Oracle source is very simple to deploy and use, as you can see.
Finally, if you are interested by this blog and see more, I invite you to come in our free Event “Replication road show” who Vincent Matthey, David Barbarin and I, will present the replication between Oracle and SQL Server with Attunity Replicate and complete this demo with also change on DDL command like change a data type on a column or add a calculated column, etc.

 

Leave a Reply


two × = 18

Stéphane Haby
Stéphane Haby

Delivery Manager