Announcement Announcement Module
Collapse
No announcement yet.
'commitment control with spring-hibernate and AS400' Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 'commitment control with spring-hibernate and AS400'

    I am using spring and hibernate with DB2/AS400. I am using programmatic transaction to update database.

    The following is the method to execute transaction.

    public void saveOrUpdate(final TestDTO testDto){

    this.getTransactionTemplate().setIsolationLevel(-1);

    transactionTemplate.execute(
    new TransactionCallback(){
    public Object doInTransaction(TransactionStatus ts){
    try{
    hibernateTemplate.saveOrUpdate(testDto);

    }
    catch(Exception e){
    ts.setRollbackOnly();
    }
    return null;


    }
    });
    }

    When I call this method, the following error comes.

    Caused by: java.sql.SQLException: [SQL7008] APAP0400 in TRUGER not valid for operation.

    The same error is coming for whatever isolatio level i set.

    When using Hibernate alone (without Spring) to access AS400, the same error comes with isolation level higher than 0.

    Using journaling solves the problem, but our requirement does not allow it because of performance issue.

    Please suggest if there is any way out to have commitment control without use of journaling.

  • #2
    Please use [ code][/code ] tags when posting code that way it is more readable.

    Why programmatic control why not declarative control?! Saves you a lot of coding.

    All the googling I just did regarding this issue with DB2 on a AS400 indicates that the only solution is to disable commit control or enable journaling. I suggest contacting IBM or ask at there support forums.

    Comment


    • #3
      As I have already explained in other thread (http://forum.springframework.org/sho...890#post198890) transaction on AS400 is not possible without journaling. Journal is the place where information needed for rollback is stored: no journal -> no possibility for rollback -> no atomicity (no A in ACID) -> no transaction. So your requirments are senseless, sorry.

      If builtin journaling does not satisfy your needs (which I really doubt) there are some 3rd-party products that may replace it and promise higher performance, but I do not have their names on hand, make some search. But be sure that these products are way expensive!

      Regards,
      Oleksandr

      Originally posted by saurabhjain View Post
      I am using spring and hibernate with DB2/AS400. I am using programmatic transaction to update database.

      The following is the method to execute transaction.

      public void saveOrUpdate(final TestDTO testDto){

      this.getTransactionTemplate().setIsolationLevel(-1);

      transactionTemplate.execute(
      new TransactionCallback(){
      public Object doInTransaction(TransactionStatus ts){
      try{
      hibernateTemplate.saveOrUpdate(testDto);

      }
      catch(Exception e){
      ts.setRollbackOnly();
      }
      return null;


      }
      });
      }

      When I call this method, the following error comes.

      Caused by: java.sql.SQLException: [SQL7008] APAP0400 in TRUGER not valid for operation.

      The same error is coming for whatever isolatio level i set.

      When using Hibernate alone (without Spring) to access AS400, the same error comes with isolation level higher than 0.

      Using journaling solves the problem, but our requirement does not allow it because of performance issue.

      Please suggest if there is any way out to have commitment control without use of journaling.

      Comment


      • #4
        Originally posted by saurabhjain View Post

        Please suggest if there is any way out to have commitment control without use of journaling.
        No there isn't, and I am surprised you think having journalling on is a such a performance overhead that you don't want to use it.

        The AS/400/iSeries/System I or whatever IBM like to call it nowadays is highly optimized for transactional IO, in fact this is really what it was designed for.

        Just do a CREATE COLLECTION to create an AS/400 library. Any tables then created within this will automatically be journalled, then just annotate any methods that need to be transactional with @transactional.

        From looking at your code example and your desire not to use journalling, I respectfully suggest you are over complicating something that is actually very easy.

        If you do have a valid performance problem, can you explain that in greater detail. If you are doing huge amounts of commits in batch you might want to look at Spring Batch, which is highly optimized for high throughput batch transactions. You can also configure the AS/400 quite cleverly, for example putting the journals on one set of disks and the tables on another, to spread the load (and makes it more secure in case of a disk crash too, you will either lose the journals or the tables, not both, allowing recovery in both cases).

        Comment


        • #5
          Originally posted by Marten Deinum View Post
          All the googling I just did regarding this issue with DB2 on a AS400 indicates that the only solution is to disable commit control or enable journaling. I suggest contacting IBM or ask at there support forums.
          Journalling works as follows:

          You set up a journal that is essentially an object that records information about all the tables that are journalled to it. Attached to the journal are journal receivers, that are storage areas that contain journal entries, such as before and after images of updates, or inserts, deletes and so on.

          When you do an update for example, an uncommited update is written to the database, and a before and an after image of the row (record in AS/400 parlance) is written to the journal receiver. When a commit takes place a commit entry is also written to the journal receiver, and the update becomes permanent. In the case of the rollback, the DBMS looks at the before entry, and replaces the uncommited row with the before entry.

          I serverly doubt any third party solution would be better or faster than IBM's. A lot of people tend to turn off commitment control for large batch work. In this case you have no commits,. and if your batch job fails you can manually roll back to where the job started from. Journalling is still happening here. You can also turn off journalling all together, but in the event of a job failure or whatever, you have no starting point to go back to, other than your last save.

          Performance hits on journalling are often over-stated. It is very typical to use one set of disks to handle journal i/o, and another for database i/o. There is a hardware overhead for this, but not a performance one.

          When someone suggests turning off journalling all together I guess it depends on how much you value your data.

          Comment


          • #6
            Anyway, any transctional database that I'm aware of uses some kind of journaling (often it is named "logging" as by Oracle, but it is only terminological difference).

            Anyway, Paul is 100% right (with minor correction - it is possible to configure joiurnaling in such a way that only "after" image is written).

            And once more - before any attempt to part with journaling the real problem has to be formulated and it existence reliable proven, as well as impossibility to find some other soultion (the most likely problem is not in journaling but in bad application design and/or bad database design).

            Unlike other AS400 parts DB is quite effective (no big surprise as it was a primary goal by creation of this platform ).

            Regards,
            Oleksandr

            Originally posted by Paul Newport View Post
            Journalling works as follows:

            You set up a journal that is essentially an object that records information about all the tables that are journalled to it. Attached to the journal are journal receivers, that are storage areas that contain journal entries, such as before and after images of updates, or inserts, deletes and so on.

            When you do an update for example, an uncommited update is written to the database, and a before and an after image of the row (record in AS/400 parlance) is written to the journal receiver. When a commit takes place a commit entry is also written to the journal receiver, and the update becomes permanent. In the case of the rollback, the DBMS looks at the before entry, and replaces the uncommited row with the before entry.

            I serverly doubt any third party solution would be better or faster than IBM's. A lot of people tend to turn off commitment control for large batch work. In this case you have no commits,. and if your batch job fails you can manually roll back to where the job started from. Journalling is still happening here. You can also turn off journalling all together, but in the event of a job failure or whatever, you have no starting point to go back to, other than your last save.

            Performance hits on journalling are often over-stated. It is very typical to use one set of disks to handle journal i/o, and another for database i/o. There is a hardware overhead for this, but not a performance one.

            When someone suggests turning off journalling all together I guess it depends on how much you value your data.

            Comment


            • #7
              Originally posted by al0 View Post
              Anyway, Paul is 100% right (with minor correction - it is possible to configure joiurnaling in such a way that only "after" image is written).
              I left out that detail just not too over complicate the issue (just as you can omit open and closes and so on).

              Exactly the point though, if you don't journal or log, how else can you do it ? You have to persist at least the after image to disk (you can't hold it in memory in case the power turns off), so you will always get extra io's if you want transactions.

              I think this is case of trying to have one's cake and eat it !

              Comment


              • #8
                Sure, and it is quite well documented in the IBM documentation,
                Here is quotation from "How commitment control works with objects" topic of AS400 documentation
                Journaled files and commitment control
                You must journal (log) a database file (resource type FILE or DDM) before it can be opened for output under commitment control or referenced by an SQL application that uses an isolation level other than No Commit. A file does not need to be journaled in order to open it for input only under commitment control.
                See http://publib.boulder.ibm.com/infoce...v5r4/index.jsp, topic "Database/Commitment Control/Commitment Control Concepts" (the parent topic of above mentioned one) for further information.


                Originally posted by Paul Newport View Post
                I left out that detail just not too over complicate the issue (just as you can omit open and closes and so on).

                Exactly the point though, if you don't journal or log, how else can you do it ? You have to persist at least the after image to disk (you can't hold it in memory in case the power turns off), so you will always get extra io's if you want transactions.

                I think this is case of trying to have one's cake and eat it !

                Comment

                Working...
                X