AS400/iSeries replicating to SQL Server 2005

Posted on 2008-11-06
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
    LVL 37

    Expert Comment

    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


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

    -- DaveSlash
    LVL 34

    Expert Comment

    by:Gary Patterson

    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

    Author Comment

    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

    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 34

    Accepted Solution

    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

    Thank you for your input

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    I use more than 1 computer in my office for various reasons. Multiple keyboards and mice take up more than just extra space, they make working a little more complicated. Using one mouse and keyboard for all of my computers makes life easier. This co…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how the fundamental information of how to create a table.

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now