AS400/iSeries replicating to SQL Server 2005

Posted on 2008-11-06
Medium Priority
Last Modified: 2013-12-06
I need to replicate data from an AS400/iSeries to SQL Server 2005. How can we do this? Can anyone recommend a product that might be able to do this?
Question by:stanforda
  • 2
  • 2
  • 2
  • +1
LVL 37

Expert Comment

ID: 22893202
if you need a permenant replication (not a one time thing) you can use ibm websphere federation server
it's a product by ibm that is able to connect to many data sources including db2 and sql server and oracle etc...
and you can use it to perform replication to sql server
LVL 18

Expert Comment

by:Dave Ford
ID: 22896732

I like SQL Server's own DTS. It's extremely effective, and (best of all) you already have it.

-- DaveSlash
LVL 36

Expert Comment

by:Gary Patterson
ID: 22897294

DTS is an SQL Server 2000 technology that is replaced in SQL Server 2005 by SQL Server Integration Services (SSIS).  Websphere Federation Server and SSIS are both options for replication, and there are many other tools and techniques, as well.  One particularly cool product is HiT Software's DBMoto product:  It provides shapshot, one-way mirroring, and two-way mirroring capabilities:


There are numerous other tools, too, and costs vary dramatically.  There really isn't, in my opinion, a single "one-size-fits-all" solution.  When we design these sorts of replication solutions for our clients, there are a number of factors that we consider:

1) Application requirements.  Is this a development or QA system that just needs an occasional update to refresh test data?  IS it a data warehouse that needs to be updated with monthly or weekly sales figures?  Is it a customer order-tracking system that needs to be updated instantaneously?  Perhaps you have a mix of these requirements.
2) Scheduling of updates.
3) Volume of data.
4) Data security.
5) Type and speed of connection between DB servers.
6) Snapshot, one-way morroring, or two-way mirroring of changes?
7) Reliability.
8) Ability to quickly verify that databases are in sync.

The "best tool" depends several of the above factors.  The key factor is how up-to-date you need the replica to be.  Do you need every update immediately reflected in the replica (mirroring), or do you plan to reload or push updates less frequently (snapshots)?

Do you need to replicate in one direction only (iSeries to SQL server), or do you need to be able to do two-way mirroring (updates to either system get pushed to the replication partner)?

If you would like to give us a little more background on your requirements, we might be able to give more specific guidance.

- Gary Patterson
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.


Author Comment

ID: 22899560
Sorry... I should have also noted that we need replicate one way only with iSeries as a source and SQL Server 2005 as destination and that we need changes on the iSeries to be visible on SQL Server in near real time... so about a 30-60 second delay would be OK. We were going to get DB Moto, but they were really messing us around, so we are looking at alternatives. I had wondered about SSIS/DTS, but could not get my head around how to get it running in near real-time so it only downloaded the changes as they occur on the iSeries. It seems to me that what ever we do it is going to need to be monitoring the journal on the iSeries to see the changes as they happen. We are hoping to get some good feedback from the experts exchange. In the meantime I am looking at something called SQL DRPLus from a company called Starquest. Thank you for the input so far.
LVL 37

Assisted Solution

momi_sabag earned 800 total points
ID: 22903571
you could use triggers in the iseries db2 in order to capture the changes to the replicated tables and write these changes to "change tables", that is, tables that will allow you to replicate the change to sql server
then you can have an ssis package that is constantly running and polling these change tables and apply the changes (and keep track of which changes were applied already)
LVL 36

Accepted Solution

Gary Patterson earned 1200 total points
ID: 22924137
I have written and maintained homegrown "data replication" applications in the past, using triggers as momi_sabaq has suggested, and by writing AS/400 journal monitors.

Inevitably, something happens and replication gets out of sync.  If it is important that the databases stay in sync, you really want to get a reliable commercial solution that can verify database synchronization, and restore sync if an error occurs.  That is a lot of code to write and test in a homegrown application, and testing failure scenarios is very complicated.

Look at DBMoto from HiT software.  It does exactly what you need, plus offers additional capabilities such as two-way mirroring for future applications.

- Gary Patterson

Author Closing Comment

ID: 31513817
Thank you for your input

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question