Topic
  • 8 replies
  • Latest Post - ‏2013-03-07T18:13:50Z by markevans
MacJansen
MacJansen
20 Posts

Pinned topic Database transaction over 2 db's

‏2013-01-29T06:45:43Z |
I have the following challenge.

We had 2 systems.
classic : a RPG system start building since 1995.
old : a EGL based system currently being build.

The challenge now is to keep the 2 system synchronized.
Currently when are trying to write to the classic system from the new system. (with EGL - Call)
Writing is not the problem but the transaction is. The Commit of the RPG based writing is always done in RPG and I can get it to the EGL level.

We try to do it with WS-Transaction but our Project structure doesnot allow us to put policy sets to our client WSDL's
Is anyone who can help me with this?
Updated on 2013-03-07T18:13:50Z at 2013-03-07T18:13:50Z by markevans
  • Ortwin
    Ortwin
    208 Posts

    Re: Database transaction over 2 db's

    ‏2013-01-29T21:09:18Z  
    Hi,

    I'm trying to create a picture of what you try to accomplish, but I'm not managing.

    Of what does this 'EGL based system' exist? EGL JSF, or EGL RUI, or maybe EGL generated Cobol? And/or EGL webservices? And does it have it's own database? Do you have two databases (for the title of the post is 'Database transaction over 2 db's')? Do you have two user interfaces (RPG and EGL), and/or do you want to maintain those, or do you plan a migration from the RPG interface to an EGL interface?

    What is the most preferable situation you want to end up with? Please, give examples.

    Ortwin
  • MacJansen
    MacJansen
    20 Posts

    Re: Database transaction over 2 db's

    ‏2013-01-30T06:27:08Z  
    • Ortwin
    • ‏2013-01-29T21:09:18Z
    Hi,

    I'm trying to create a picture of what you try to accomplish, but I'm not managing.

    Of what does this 'EGL based system' exist? EGL JSF, or EGL RUI, or maybe EGL generated Cobol? And/or EGL webservices? And does it have it's own database? Do you have two databases (for the title of the post is 'Database transaction over 2 db's')? Do you have two user interfaces (RPG and EGL), and/or do you want to maintain those, or do you plan a migration from the RPG interface to an EGL interface?

    What is the most preferable situation you want to end up with? Please, give examples.

    Ortwin
    Ortwin,

    The new system is in EGL-RUI (frontend) and Webservices written in EGL (backend). There is No COBOL.
    Thru the backend we want to maintain the classic Database and the new database both are exist on the iseries.

    The situation is the following:

    The product is changed thru the RUI-frontend and sent to the backend (EGL-Java).
    In the backend we write the changed record to the classic system thru a RPG program. This is needed because the business rules are in that program. After that we write it to the new system.
    The problem lies in the fact that when in the new system for any reason the record is not correct and is Rollbacked. The record in the classic system is already committed and cannot be rollbacked anymore.
    We want to bring both commintment cycles in the same transaction so if we rollback both the changes are rollbacked.

    Hope this make sence now.
  • Ortwin
    Ortwin
    208 Posts

    Re: Database transaction over 2 db's

    ‏2013-01-31T20:26:47Z  
    • MacJansen
    • ‏2013-01-30T06:27:08Z
    Ortwin,

    The new system is in EGL-RUI (frontend) and Webservices written in EGL (backend). There is No COBOL.
    Thru the backend we want to maintain the classic Database and the new database both are exist on the iseries.

    The situation is the following:

    The product is changed thru the RUI-frontend and sent to the backend (EGL-Java).
    In the backend we write the changed record to the classic system thru a RPG program. This is needed because the business rules are in that program. After that we write it to the new system.
    The problem lies in the fact that when in the new system for any reason the record is not correct and is Rollbacked. The record in the classic system is already committed and cannot be rollbacked anymore.
    We want to bring both commintment cycles in the same transaction so if we rollback both the changes are rollbacked.

    Hope this make sence now.
    Ah, I see.
    There's one thing that's not complete clear and that is from where the transaction to the new system is triggered. I picture for each database update you have one EGL service that first calls an RPG program to update the old database and then, from the same service, the new database is updated. Is this right?

    Here's a thought on how to handle it.
    You can call the RPG program first to perform the database update, but not to commit it yet. Then update the new system and when it succeeds commit it and after that commit the old system. This way you can still rollback the old system when the update to the new system fails.

    You can do this by creating your RPG program as a service program and calling it stateful so it stays open after performing the update. When it returns you update and commit the new system. Then you call the first RPG service program again, but a different subprocedure, to commit the first transaction.

    Does this make sense?

    Ortwin
  • MacJansen
    MacJansen
    20 Posts

    Re: Database transaction over 2 db's

    ‏2013-02-04T08:55:37Z  
    • Ortwin
    • ‏2013-01-31T20:26:47Z
    Ah, I see.
    There's one thing that's not complete clear and that is from where the transaction to the new system is triggered. I picture for each database update you have one EGL service that first calls an RPG program to update the old database and then, from the same service, the new database is updated. Is this right?

    Here's a thought on how to handle it.
    You can call the RPG program first to perform the database update, but not to commit it yet. Then update the new system and when it succeeds commit it and after that commit the old system. This way you can still rollback the old system when the update to the new system fails.

    You can do this by creating your RPG program as a service program and calling it stateful so it stays open after performing the update. When it returns you update and commit the new system. Then you call the first RPG service program again, but a different subprocedure, to commit the first transaction.

    Does this make sense?

    Ortwin
    Ortwin,

    I think I understand what you mean but not completely.

    Is it possible that you have some examples for me?
  • Ortwin
    Ortwin
    208 Posts

    Re: Database transaction over 2 db's

    ‏2013-02-05T20:19:50Z  
    • MacJansen
    • ‏2013-02-04T08:55:37Z
    Ortwin,

    I think I understand what you mean but not completely.

    Is it possible that you have some examples for me?
    One transaction would look like this:
    • EGL RUI calls EGL service 'putCustomerRec'
    • putCustomerRec calls main line of RPG Service Program 'PUTCUSTREC' in a stateful way. In PUTCUSTREC the file CUSTOMER is updated but not yet committed. PUTCUSTREC ends with a return statement, without setting *INLR so PUTCUSTREC is not closed.
    • putCustomerRec executes an SQL command to update file customerTable in the new system and when succeeded commits it.
    • putCustomerRec calls RPG Service Program 'PUTCUSTREC' again, but this time not the main line routine but subprocedure 'commitMyRec' to commit the update to file CUSTOMER which was done in the previous call. PUTCUSTREC ends with *INLR on so the program is closed.
    • putCustomerRec returns to the RUI application.

    Ortwin
  • dan_darnell
    dan_darnell
    973 Posts

    Re: Database transaction over 2 db's

    ‏2013-02-06T04:41:26Z  
    • Ortwin
    • ‏2013-02-05T20:19:50Z
    One transaction would look like this:
    • EGL RUI calls EGL service 'putCustomerRec'
    • putCustomerRec calls main line of RPG Service Program 'PUTCUSTREC' in a stateful way. In PUTCUSTREC the file CUSTOMER is updated but not yet committed. PUTCUSTREC ends with a return statement, without setting *INLR so PUTCUSTREC is not closed.
    • putCustomerRec executes an SQL command to update file customerTable in the new system and when succeeded commits it.
    • putCustomerRec calls RPG Service Program 'PUTCUSTREC' again, but this time not the main line routine but subprocedure 'commitMyRec' to commit the update to file CUSTOMER which was done in the previous call. PUTCUSTREC ends with *INLR on so the program is closed.
    • putCustomerRec returns to the RUI application.

    Ortwin
    If you need to completely guarantee with 100% certainty that the systems remain in sync then you have to look into true database replication (an HA solution) or a two-phase commit at the DB2 level through DRDA.

    --Dan
  • MacJansen
    MacJansen
    20 Posts

    Re: Database transaction over 2 db's

    ‏2013-03-07T13:58:42Z  
    If you need to completely guarantee with 100% certainty that the systems remain in sync then you have to look into true database replication (an HA solution) or a two-phase commit at the DB2 level through DRDA.

    --Dan
    Problem is solves.

    We now using local EGL services instead of Soap Calls. An dnow the transaction is committed over the 2 db's . Both iseries and egl db is committed.

    Thanks for all the suggestions.
  • markevans
    markevans
    3025 Posts

    Re: Database transaction over 2 db's

    ‏2013-03-07T18:13:50Z  
    • MacJansen
    • ‏2013-03-07T13:58:42Z
    Problem is solves.

    We now using local EGL services instead of Soap Calls. An dnow the transaction is committed over the 2 db's . Both iseries and egl db is committed.

    Thanks for all the suggestions.
    Glad to hear you got it working.

    take care.