How to sync desktop with AS400 DB2 Database, probably using MS SQL?

Sorry for the confusion.

Currently, I access the AS400 directly using IBM's ODBC Adapter and .NET35 ADO.NET.

This doesn't work because DB2 on the AS400 has a very high per-query overhead.

I'm looking for a solution so that I keep a local copy of the 60 tables, perform quick queries on the local copies for my customer, and constantly update it throughout the day from the AS400.  The problem is that my documentation I have only shows how to import/export databases; it does not indicate how MS SQL can best draw from DB2.

I appreciate any help,

Ryan

Slight goal revision:

What is the easiest way to maintain a sync'd desktop copy of 60 tables in DB2 on an AS400?

I'm hoping to find something better than duplicating the 60 databases on the AS400 & then TCP'ing the changes to my desktop.

In my original question I said I don't want to do setup on the AS400 because I'm not familiar with the platform but any straightforward solution would be welcome.

Thanks again,

Ryan

well, easiest thing will be creating triggers on the 60 tables on the as400 which will allow you to identify the new records and then just copy these records

In general, I don't think triggers are the best way to go, for several reasons:

1) Performance.  Trigger programs add synchronous overhead to every single database write, update and delete operation.
2) Risk.  Trigger program failure, delays, or locks can cause other applications that touch these tables to fail unexpectedly.
3) Labor: It is a lot of work to design, code, properly test, and maintain 60 trigger programs, and it is possible that these programs will require additional maintenance if the underlying table changes (depending on design).

Now, there are technical solutions to help mitigate each of the above, but no matter what approach you take, triggers will reduce overall database performance, add more points of failure into multiple processes, and take quite a bit of work to implement and maintain.

Hands down, the best solution that I;ve ever seen is to journal (transaction log) the tables that need to be replicated, and create a journal monitor process (unfortunately this code needs to run on the AS/400) that monitors the database journal, and pumps the data over to the remote machine where it can be synchronized into the replica table.  The process is incredibly lightweight compared to other mechanisms.  I've personally developed replication code that can mirror extremely high volumes of changes with almost no perceptible AS/400 overhead.

Advantages include:

1) Performance.  Almost invisible compared to heavy-handed query-based "pulls".  Much lighter than triggers (especially true if tables are already journaled).

2) Risk.  If the journal monitor process fails, the client's applications are not effected.  While some AS/400 code must be installed and journaling implemented if not currently in use, the application is not directly in the client's application path.

3) Labor.  There is a single program to develop and maintain.  Tables to replicate can be soft-coded, minimizing the need to maintain this program if needs change in the future.

There are third-party data mirroring tools out there that will do this for you, but it is pretty simple to write your own.  They are typically fairly costly.

There is just no good, efficient, performance-friendly way that I know of to do this as a "pull" from the client side.

If you are interested in this approach, post back and I can provide some good sample code and technical references to get you started.

- Gary Patterson

Gary,

     I'm glad to hear you weigh in on this issue.  I buy into your approach for client risk reasons alone.

     The performance issues also make your approach much more compelling.  In early stages of development I would like to avoid database locks and new database writes as much as possible; journaling seems pretty safe.

     I would be very interested in sample code and tech references.  Thinking about programming languages, I understand IBM encourages RPG and Java. I bet a partition running a Windows partition w/Visual RPG & C# would be like a barnacle to the existing native RPG/DB2 code, so I'm guessing RPG & Java are the options, which is fine.  I don't know RPG or Cobol at all, although I've had some experience with IBM languages like PL/I and Fortran
     
     Restating a possibly important constraint: I'm using database members, and don't mind getting updates for an entire table as long as I know which member was affected by the update. My client's tables do not have a column naming the affected database member.

     I was surprised to see that the Q&A re. accessing DB2 from Windows ranked fairly highly on Google's search list leading to more hits than I would have expected. Hopefully journaling will also be of interest to a number of people.

---Ryan


   

Missing link for the example CL that I mentioned in #2.:  http://archive.midrange.com/rpg400-l/200312/msg00143.html

- Gary Patterson (check out my EE profile: http://www.experts-exchange.com/M_4382324.html)

I really appreciate the overview of journaling on the AS400 and how to capture its results in Windows.

One of the nice things about journaling is its (I hope) automatic inclusion of intra-table transactions; if it has a transaction ID marker it would also word for cross-table transactions.  It turns out the system is designed to maintain integrity at all times; the IT guy just didn't know that. I guess communication is never perfect.


I'm glad you mentioned C because I strongly prefer it to Java in most instances. Elegantly designed C API's are rare but wonderful to see. Lucid class taxonomies don't seem to survive Java team programming without losing a set of clear semantics about object creation and the invariants governing their ongoing state. Java isn't enough like Smalltalk to be real OOP, and unlike C and C# it actively hinders functional programming, rule engines, language oriented programming, declarative programming, meta-level programming, or the like.

I will chat with my AS400 IT guy to see if the journaling you describe is acceptable; I suspect he will be very happy. He also knows about putting Windows on the AS400, and at this point the question will just be whether putting in a Windows card is better or whether buying a desktop and putting it on the AS400 LAN is better for the middleware.

Right now the AS400 DB2 data flows like this:

[AS400 Order Entry] =>
      [AS400 Journaling] =>
         [Desktop.Net collect scheduling constraints] =>
             [Desktop display constraints] =>
                    [Desktop let's schedule what-if his way into a schedule by making choices] =>
                            [Desktop has a declarative representation of a plan] =>
                                    [AS400 Master Schedule]
   


But it might be much more efficient to use a middle-ware approach:

[AS400 Order Entry] =>
      [AS400 Journaling] =>
         [AS400 Windows card .Net: collect scheduling constraints] =>
             [Desktop display constraints] =>
                    [Desktop human scheduler does what-if's and makes schedule choices] =>
                            [Desktop updates a declarative representation of a plan] =>
                                    [AS400 Master Schedule]

Thanks again,

Ryan

Post back if you or you AS/400 guy need any help.

- Gary Patterson (check out my EE profile: http://www.experts-exchange.com/M_4382324.html)

Ryan:

>  (The tables are members, but I have aliased all of the members as per IBM's recommendations).

Can you clarify that one small piece? Is that an indication of 'multi-member' files? Or did you simply create an ALIAS over the single members in each file?

If the files are multi-member, what purpose is served by the different members? In some cases, a file might be 'partitioned' into members so that each member serves a different company or different division or even user. Other times, members might be generated for each new year or other calendar period for historical reasons. In your case, multi-members would serve what purpose?

Tom

Sorry. I aliased the databases. They are indeed multi-member, in the sense that (N015) is for one location, (N035) is for another, and so forth, for each table.

Turns out the AS400 mirrors to another AS400, but unless DB2 on a PC can accept mirroring efficiently I still think journaling is the way to go.

Ryan,

What mirroring package is being used?  

- Gary Patterson (check out my EE profile: http://www.experts-exchange.com/M_4382324.html)

Thanks very much. I will find out which mirroring package ASAP...

---Ryan

Mirroring software is called Itera, and it uses journaling, so journaling is already enabled for all of the databases in question.

I asked IT if Itera can replicate to a Windows-based "client".  I imagine DB2 running on Windows would work, but my imagination and IBM's reality rarely coincide... :)

---Ryan Michael

I'm familiar with iTera, and afiak, it does not support remote journaling to anything other than another AS/400.

- Gary Patterson (check out my EE profile: http://www.experts-exchange.com/M_4382324.html)

Hi Ryan,

I've very familiar with iTera, i used to work for the software vendor. iTera will only support mirroring to another AS/400 and will only support remote journaling as the transport mechanism. The vendor (Vision Solutions) has another product called Replicate1 which can mirror using the same journals that iTera is using on your primary AS/400 to a SQL server on windows desktop(or server), it can also mirror to a flat file (.CSV) so you can import the data into excel if required.

It's probably a little overkill for what you need to as it's pretty much an enterprise level product rather then a a simple replication tool.

Other vendors in the iseries world have similar offerings, i'm not personally familiar with it but it's been recommended by others i know is Quick-EDD/DRm (http://www.quick-edd.com/qedddrm/eng/home.php) another is DBMoto (http://www.hitsw.com/products_services/dbmoto/dbmoto.html), i've not personally used either so can't make any recommendation.

I'm not a programmer but i'm told that it's not too difficult to put together something to read the journals and get it into a format ready to import in SQL. Check out Chris Hird's blog (http://www.shield.on.ca/Blog/), Chris writes his own replication product on the AS/400 for High Availability, but he publishes alot of detail on how to read journals using IBM APIs etc, if you know some programming you'll spot what he doing pretty quickly i'm sure.

Best of Luck

Simon