replication of DB2 to MS SQL

Dear all,

right now one old DB2 is getting old but we do not have DB2 DR site in case the old hardware dead, we should have another hardware there to take up the usage ASAP.

any tools to replication the whole DB2 content to MS SQL ?

I heard that there are Sybase Replication server can replicate everything from any DB to other DB, is that right? but any cheaper tools ?

MS migration wizard can copy everything from DB2 to MS SQL in a regularily to server as DR purpose ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

marrowyungSenior Technical architecture (Data)Author Commented:
can this keep sync data from DB2 to MS SQL?

http://www.microsoft.com/en-eg/download/details.aspx?id=29100

use mainly MS SQL replication and the tools install locally on that SQL server box and create replication ?
0
HuaMin ChenProblem resolverCommented:
0
lcohanDatabase AnalystCommented:
Assuming you use SQL 2012 yes, you can use the "Microsoft® OLEDB Provider for DB2 v4.0 for Microsoft® SQL Server® 2012" from link you posted above to create a linked server ON your SQL Server then "pull" your database(s) in SQL via that OLEDB provider.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

marrowyungSenior Technical architecture (Data)Author Commented:
lcohan,

we are using SQL server 2008 R2, but seems V4.0 can support this one, right?

What I am worrying about is DB2 V8.02 don't get support, it only list 8.1/8.2, agree?

" create a linked server ON your SQL Server then "pull" your database(s) in SQL via that OLEDB provider."

on the target SQL server I want data from DB2 replicate to , I create a linked server on it and point to the DB2 server, then I can do select from the DB2 you mean ?

HuaMinChen,

but that one depends on if I installed that OLEDB provide for DB2, right?
0
marrowyungSenior Technical architecture (Data)Author Commented:
we are using DB2 V8.02 on Solaris 9, we have SQL 2005/2008 R2 , please assume we are going to use SQL server 2008 R2.


but one think, that Microsoft® OLEDB Provider for DB2 v4.0 for Microsoft® SQL Server® 2012 is only for SQL distributed query but not for replication data to DR site for DR purpose?

it is very diff concept, right ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
so for SQL server 2008 R2, we have to use Microsoft OLE DB Provider for DB2 V3.0 ?

http://blogs.msdn.com/b/sjackson/archive/2010/06/11/microsoft-ole-db-provider-for-db2-v3-0-is-available.aspx
0
marrowyungSenior Technical architecture (Data)Author Commented:
I have read this one :

http://searchsqlserver.techtarget.com/tip/How-to-create-a-SQL-Server-linked-server-to-DB2

this one seems only added a linked server and query doable! how about the replication and DR concern?

how MS SQL detect this DB2 and do the replication ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you think about SQL Server Integration Services (SSIS)? It's a powerful ETL tool that let you build any Import/Export process from whatever source and target.
0
marrowyungSenior Technical architecture (Data)Author Commented:
Vitor Montalvão,

Yes, I read an article about that! so with this, but most of them only mention it as the link server to DB2 and do basic query.  no OLEDB driver 3.0/4.0 is needed?

that one is replication or just import/export? no OLEDB driver 3.0/4.0 is needed?

We need a tools to replication everything in real time from DB2 to SQL server in case the primary one failed. but problem should be it is not possible to sync back to db2 from MS SQL ?

or I just suggest from Db2 to MS SQL is not supposed to happen and the product is not designed for it? then why not DB2 to DB2, right?

any article/source tell me how to setup using SSIS for real time replication for it and therefore serve as DR purpose?
0
lcohanDatabase AnalystCommented:
0
marrowyungSenior Technical architecture (Data)Author Commented:
Vitor Montalvão,

you mean once setup the OLEDB 3.0/4.0 on the SQL server box, then we use SSIS for it or the SSIS already provide the connectivity to other diff DB system ?

can SQL server 2008 R2/2012/2014 standard edition can already do it instead of enterprise edition ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
I just launch the SSIS for SSDT for Visual studio to try greating a SSIS packge.

When I try to define the OLEDB destination to see if our DB2 is connectable and I create the connection from connection Manager.

I can only find data provider in my PC as:

when defining OLEDB for DB2
and I test the connection by username and password I got:

error on doing this.

how come and it seems that from the SSDT or SSIS tools, I can't find a OLEDB source but destination.. what is the reason for this?

am I really need to install the OLEDB provider 3.0/4.0 from MS and try the same thing again ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
it seems that i keep finding post from the internet like this:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4242afa6-0056-48d4-a22a-6fa5eb3c1bb6/automated-replication-using-ssis?forum=sqlintegrationservices

that replication can't be manage using SSIS and SSIS is strong on data transform but it don't have change track feature SQL replication feature included.

what should be the real one? I just want to setup a DR process, which can switchback from MS SQL back to DB2 V8.02.
0
marrowyungSenior Technical architecture (Data)Author Commented:
it seems that we can't replicate DB2 V 8.02 database to MS SQL, right? I am using SQL server 2008 R2 but I don't see I can select Db2 subscription but MS SQL and Oracle subscription only, right?

how can it be done ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You need the correct client driver.
You should have DB2 V8.02 client for install it in the SQL Server machine so it will allow to create the right connection.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"You should have DB2 V8.02 client for install it in the SQL Server machine so it will allow to create the right connection. "

ok, you mean once the DB2 V8.02 client has been installed on that MS SQL box and the SSIS will show OLEDB source as well as OLEDB destination?

So basically either you or your friend has tried that before?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Which my friend?
Anyway, is how it works and that's why clients are necessary. For my experience I did create a process in SSIS to replicate data from an Oracle database to a DB2 database and I needed to install both native client drivers in SSIS server so it could connect to both databases.
Even if you want to connect to a SQL Server database you'ld always need a SQL Server client driver. The difference is that when you install SQL Server the client is also installed and maybe because of that you never realized that you need it.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Looks like DB2 v8.x isn't supported anymore. EOS was on 2009 (!).
Information from IBM website.

So, unless you have the client with you on a CD or DVD, you might not be able to perform the replication that you want to.
0
marrowyungSenior Technical architecture (Data)Author Commented:
EOS was on 2009 (!). ? end of service?

"Which my friend?"

no, I just guess you/ your fiend has tired that already and that's why you are so sure about that.

"Anyway, is how it works and that's why clients are necessary. For my experience I did create a process in SSIS to replicate data from an Oracle database to a DB2 database and I needed to install both native client drivers in SSIS server so it could connect to both databases."

basically for SQL replication it also works in that way an no need SSIS as there are oracle driver from the replication UI when we need to find a Oracle publisher, am I right?

"Even if you want to connect to a SQL Server database you'ld always need a SQL Server client driver. The difference is that when you install SQL Server the client is also installed and maybe because of that you never realized that you need it. "

you are right on this as during SQL server installation the Wizard will tell you that SQL client SW is installing.

"So, unless you have the client with you on a CD or DVD, you might not be able to perform the replication that you want to. :

so if I have connect from my workstation (using some client connectivity driver inside Toad for DB2)  and I can use that driver on the SQL server box ? but once it is installed, how can I verify that I can connect to DB2 by that ? what task/item I can drag and drop into the import flow control ? what will be added in the SSIS project ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
forget about one thing Vitor, any step by steps instruction on how to setup replication from DB2 in SSIS til deployment ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Looks like DB2 v8.x isn't supported anymore. EOS was on 2009 (!).
Information from IBM website."

btw, what version of  the DB2 support can the Db2 client driver support ?

I am sorry that I cant' find a client driver for DB2 which say it don't support V8.02, I have one here called "IBM Data Server Client (Windows 32-bit AMD and Intel x86)"

I am trying to install other client and create a connection manger so that, it seems can connect to the DB2 insider the data flow control box, what is next to successfully create replication from DB2 to SQL server?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm not a DB2 expert. I just went to IBM website and found that DB2 V8 isn't supported any more.
I made some searches and found the step-by-step instructions that might help you. Please give a look here.
Also found this MSDN article about Replication between MSSQL and DB2 databases.
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks so much about this victor, hard to believe that replication doesn't work between DB2 and MSSQL as how can we define DB2 as the publisher, it just didn't allow but Oracle, agree?

so right now only once choice, MS SSIS or other party party tools like data mirror or Sybase replication server, which support to replication database of any vendor, agree?

just one funny question to confirm, once define the connection manager to point to that DB2 and I can see table inside, but later on I delete the connection manager it will be no hurt to any of the data insider that DB2 box, right?

it will be xxxxing scare if it will delete any data in DB2 by deleting the new created DB2 connection manager, right?

ok, right? I can handle the failover and switch backup of replication from SQL server to DB2 insider the same SSIS package?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Like I said, I don't know DB2. Oracle also has a replication mechanism and maybe that's why it works fine with SQL Server. Maybe DB2 don't has it and so it's more hard to set a replication with SQL Server.

Deleting the connection manager doesn't affect data at all. It only makes you unable to connect to DB2, nothing else.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Like I said, I don't know DB2. Oracle also has a replication mechanism and maybe that's why it works fine with SQL Server. Maybe DB2 don't has it and so it's more hard to set a replication with SQL Server."

yes understood.

"Deleting the connection manager doesn't affect data at all. It only makes you unable to connect to DB2, nothing else."

yes! I believe

but one very important point, by using SSIS, can we implement the DR switch back operation from MS SQL back to DB2 ?

if answer is no, then I might need to announce that there is nothing we can do between DB2 and MS SQL to make the whole replication works !
0
marrowyungSenior Technical architecture (Data)Author Commented:
I just tested the IBM Data Server Client x86  and x64 edition on my PC and SSIS connection manager can make use of this and I can connect to a test DB2.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
but one very important point, by using SSIS, can we implement the DR switch back operation from MS SQL back to DB2 ?
You can implement that but will be a very complex package. You may need to write so .NET code to be used in the package.

I just tested the IBM Data Server Client x86  and x64 edition on my PC and SSIS connection manager can make use of this and I can connect to a test DB2.
Great. That's a big step :)
0
marrowyungSenior Technical architecture (Data)Author Commented:
"You can implement that but will be a very complex package. You may need to write so .NET code to be used in the package."

I believe so, the application is a very old one and the application is called remedy.

can we setup 2 x Package that one to replicate from DB2 to MS SQL and other one from MS SQL to DB2 ?

but another problems here is how DB2 knows it is a DR Database and accept connection from MS SQL ? role switch I mean?

so it seems in this case a separate replication tools is necessary, right? what kind of tools you can suggest ? Sybase replication server?

"Great. That's a big step :)"

yeah. tks.

so for thsi replication to happen, only SSIS from SQL server standard edition can already do the job or must be enterprise ? standard should be enough as it doesn't involve any audit and encryption feature, right?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, Standard Edition should be enough.
About the way you going to implement it's only depends on you. How do you think it's more easier and simple to understand.

I also don't know about Sybase replication but since SQL Server born from Sybase I believe that there should be similar behavior with SQL Server Replication.

Which version of Remedy do you have? Maybe the database can be migrated if you can get a newer version of the Remedy.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"I also don't know about Sybase replication but since SQL Server born from Sybase I believe that there should be similar behavior with SQL Server Replication."

you are very right on this, we have a very new sybase here and you know what, the structure and command between sybase and MS SQL is so close !

e.g. select @@version.

"Which version of Remedy do you have? Maybe the database can be migrated if you can get a newer version of the Remedy."

yeah, this is what we are planning, but suddenly the hardware has problem and we need this working ASAP and this replication part is just a minor part of it.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
So, you may have all this trouble for a temporary solution?
I would put all my focus in the hardware issue so it can be solved fast and then go for the Remedy upgrade and in consequence the database upgrade/migration as well.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"I would put all my focus in the hardware issue so it can be solved fast and then go for the Remedy upgrade and in consequence the database upgrade/migration as well. "

yeah, I believe that and I will tell my boss that there are no native support on replication between DB2 and MS SQL but Oracle to MS SQL.

I am checking a product called SQLWay and they only good on one-off DB migration from Db2 to MS SQL, but in terms of replication they are totally failed and they tried to implement a solution only for us, I am very scare on this!

and their so called replication is based only by trigger and only replicate updated data, this is the same as the data audit project I done for MySQL ( do you recalled that I post similar post in EE ?)

this kind of trigger can slow down everything if we define trigger for all tables !

and trigger is operating in sync mode, so if the DR database has problem and primary one can't replicate, problem comes !!

"So, you may have all this trouble for a temporary solution?"

not temporary , my boss just rise this concern and he just think there are no problem on doing replication between Db2 and MS SQL!

he really want to have a MSSQL server here to keep a copy of DB2 database in case sth wrong happened.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"I also don't know about Sybase replication but since SQL Server born from Sybase I believe that there should be similar behavior with SQL Server Replication."

some studies by my in EE as suggested by someone here in EE that Sybase replication server can replicate all type of thing, including DB2,sybase, oracle and MS SQL.

so this is the only thing I can think about.

tried data mirror before? how is it ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
lcohan,

can the DB replication as SQL publisher can be setup on DB2 V8.02 ? or since what version of DB2 can do this ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
marrowyung, everything that you want to do will need always a DB2 V8 client. If you don't have it will be useless.
0
marrowyungSenior Technical architecture (Data)Author Commented:
Vitor, no.... I don't mean that. what I focusing now is the DB replication stuff as, if DB2 can't be define as the MS SQL replication publisher, how can MS SQL work with DB2 on this, and I am thinking it might only support some version of DB2 but not V8.02, the one we are having .

that's why I ask Icohan.
0
marrowyungSenior Technical architecture (Data)Author Commented:
Icohan,

this link:

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.swg.im.iis.db.repl.sqlrepl.doc/topics/iiyrsplnoverview.html

seems only about replication from other DB to DB2, not from DB2 to MS SQL.
0
marrowyungSenior Technical architecture (Data)Author Commented:
Victor,

this link: http://blog.stevienova.com/2009/05/20/etl-method-fastest-way-to-get-data-from-db2-to-microsoft-sql-server/

seems telling me that the SSIS can only replication one table at a time, am I right?

also in the article:

"After you wrap your head around everything I just went over, then stop to think about this. The whole DTF/Data Transfer/etc is all exposed in a COM API for “Data Transfer Automation Objects
http://www-912.ibm.com/s_dir/slkbase.NSF/643d2723f2907f0b8625661300765a2a/0c637d6b03f927ff86256a710076ab22?OpenDocument
"

yeah, very hard to understand what he mean as the picture has go through a lot of step itself and he don't tell you step by step on how that SSIS diagram create.

and that link show me no where to download the DTA object.

so by that link it seem hard for me to create replication from DB2 to MS SQL.

any better idea ? or I just suggest tools for that. IBM inforsphere replication server or Sybase replication server, they are design for that.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Do you have this?
Install the IBM Client Access tools. There is a tool called “Data Transfer From iSeries Server” which the actual exe is "C:Program FilesIBMClient Accesscwbtf.exe"

The author only explained with one but I think you can transfer more than a table.
Did you read this article?

and that link show me no where to download the DTA object.
Use the IBM search engine. There are plenty of information there. I even found an article talking about alternatives.
0
marrowyungSenior Technical architecture (Data)Author Commented:
ok, tks.

I found no where that the link say SSIS can import more than one table automatically ( when new table added in DB2 side, even less likely, the SSIS package can detect it and transfer it automatically), at a time.

IBM's link is funny, easy to get tired when reading them and get lost !
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I found no where that the link say SSIS can import more than one table automatically
For that you need to know more of SSIS. There are some components that can make it for you, like containers or loops. But I'm not specialist in SSIS. You may need to open a new question to see if some Expert can help you better in that one.

IBM's link is funny, easy to get tired when reading them and get lost !
Ahahaha. True :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
Victor,

"For that you need to know more of SSIS. There are some components that can make it for you, like containers or loops. But I'm not specialist in SSIS. You may need to open a new question to see if some Expert can help you better in that one.
"

tks for it anyway ! you already helped me a lot ! but I don't think SSIS is for this purpose but I will create another post when I really need.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.