Topic
8 replies Latest Post - ‏2013-03-11T21:37:37Z by markevans
SystemAdmin
SystemAdmin
6195 Posts
ACCEPTED ANSWER

Pinned topic How to set transaction isolation=none

‏2013-02-20T08:55:28Z |
Hi
when i run my test application i receive the error "EGL0504E REPLACE: SQL7008 QCUSTCDT "; i think the problem is that my file is not under journal.
Question: how can i set "transaction isolation=none" .
I saw differents solutions :
  • in the connection URL
  • set the sqlCommitControl to NOCOMMIT in the EGL build descriptor

which is the solution suggested ?

Thanks in advance

p.s. in the ERL build descriptor i'mk not able to find where set tha value
Updated on 2013-03-11T21:37:37Z at 2013-03-11T21:37:37Z by markevans
  • markevans
    markevans
    2759 Posts
    ACCEPTED ANSWER

    Re: How to set transaction isolation=none

    ‏2013-02-20T19:19:23Z  in response to SystemAdmin
    Hey,

    Yes, the problem is it is trying to do journaling.

    So, if you are using a connection URL, then you can just add it to the end of the URL in the sqlDB build descriptor (manually). If I remember right, you use a ";" (semi-colon) after the end of the URL string.

    If you are using WAS or a datasource in the EAR, then you need to add this (or set this) in the custom properties for the connection. For Tomcat, it would be the URL that is defined in its URL.

    Also, how you set it (or specifically which property) to use is based on whether you are using the Jt400 toolkit or the DB2 JDBC driver to access your iSeries database.

    Finally, for WAS based access, at one time there was not a way to set it to *NONE..please see this link for more information and the addition of a property call webSphereDefaultIsolationLevel

    http://www-01.ibm.com/support/docview.wss?uid=nas173b7334d203940828625759e005a0186

    Of course, the other way to solve this is to turn on the journaling for the table you are trying access.
    • SystemAdmin
      SystemAdmin
      6195 Posts
      ACCEPTED ANSWER

      Re: How to set transaction isolation=none

      ‏2013-02-22T23:22:45Z  in response to markevans
      Hi Mark

      i'm tryng to set "transaction isoltaion=none", but i have some problem:

      first i tryed to set in sqlDB build descriptor see img01, but i receive the same error

      second i have tryed to change it opening "data source Explorer" and the editing "driver details" see img02, but if i go in "Sql database connection" i don't see the "transaction isoltaion=none" setted see img03 ; also have tryed to restore default and reselect "New DB2 i5/OS" connection in "Sql database connection" but that value not came displayed.

      at the end i have applied the journal in table and it works !!!

      why the first two option don't solve the problem ?? (i have also stopped and restared Appl. Server)

      Thanks in advance
      • markevans
        markevans
        2759 Posts
        ACCEPTED ANSWER

        Re: How to set transaction isolation=none

        ‏2013-02-27T17:20:40Z  in response to SystemAdmin
        Hi,

        When you use J2EE=yes and sqlJNDIName in your build descriptor, the connection properties are defined in the datasource info. This is where you would need to add it.

        I am not sure if you have defined your JDBC provider/Datasource within the EAR (way the "EGL Runtime Data Source" creates it) or if you have defined the JDBC provider/Datasource within it. Either way, the datasource will have properties and this is the place for it.

        Finally, please see my input in the previous post regarding the isolation level specifically within WAS and the additional properties that is needed. I seem to remember at one time without the additional property, WAS will just ignore transaction isolation = none.

        Of course, one option is the one you used which is to turn on journaling.
        • SystemAdmin
          SystemAdmin
          6195 Posts
          ACCEPTED ANSWER

          Re: How to set transaction isolation=none

          ‏2013-03-07T17:41:15Z  in response to markevans
          Hi Mark

          here again , but still with the problem; please be patient but i have done all the test and problem is not solved:

          1- if i go in "data source explorer's property" i i see in the connection url the "transation isolation= none" (see img01)

          2- if i go in EGL runtime data source i see recalled the "resource reference name" of my point 1 (see img02)

          so in thi situation i expeceted that configuration would be OK, but unfortunately this is not true .
          where i'm wrong ? can you suggest me some documentation that show step by step how configure this situation ?

          is there some log/debug that show me which configuration run and url came read when my project run ?

          am becoming crazy for a banal thing !!!
          thanks in advance
          • markevans
            markevans
            2759 Posts
            ACCEPTED ANSWER

            Re: How to set transaction isolation=none

            ‏2013-03-07T23:03:01Z  in response to SystemAdmin
            Thanks for the pictures.

            I did not get a chance to try it or get pictures..., but if I remember right, the properties from the driver properties (your image one) are not carried over to the datasource created for you in the EAR file. That is because the driver name and the way the properties are specified are different.

            You will need to go look at deployment descriptor of the EAR project and add/change this property within the datasource properties. The EGL Runtime DataSource Wizard creates this JDBC provider and Datasource within the EAR. So, you need to edit the application.xml or otherwise known as the EAR project deployment descriptor. Depending on the level of RBD and WAS you are using, then you may have to select the deployment descriptor and under J2EE in the context menu, choose WebSphere Extensions.

            See if you find this and can add/change this property there.

            And please remember you still may need to set the additional property I mentioned (or provided the link to ) when accessing this datasource with WAS.
            • SystemAdmin
              SystemAdmin
              6195 Posts
              ACCEPTED ANSWER

              Re: How to set transaction isolation=none

              ‏2013-03-09T22:52:46Z  in response to markevans
              Hi Dan

              i have tried to do this:
              1- i have opened the deployment descriptor in EAR project (see attached application.xml and img01) but i don't see any data source properties ??
              2- i selected the EAR project and then jEE contect menù and i obtained that is in my img02 ??
              3- i opened the file rglbld : see the img03
              I am losing all my hopes to resolve this problem.. possible don't exist any tutorial that explain how do this step by step ? possible don't exist any debug / log that say where is the problem ?

              i'm using RBD 8.0.1.2 and WAS v7.0

              thanks in advance
              • SystemAdmin
                SystemAdmin
                6195 Posts
                ACCEPTED ANSWER

                Re: How to set transaction isolation=none

                ‏2013-03-09T22:55:33Z  in response to SystemAdmin
                Sorry but i'm not able to attach files ..

                it says me that file files contains virus !!
                • markevans
                  markevans
                  2759 Posts
                  ACCEPTED ANSWER

                  Re: How to set transaction isolation=none

                  ‏2013-03-11T21:37:37Z  in response to SystemAdmin
                  Hi,

                  I got your files offline. In your images, you had correctly opened up the dialog in the EAR files deployment Descriptor which allows you to see/change the JDBC Provider, the datasource, and the properties for that datasource.

                  I tried this out and the ONLY thing that had to be done was to follow the instructions in the link I had provided before related to the websphereDefaultIsolationLevel property.

                  In attached screenshot,you will see that I have "added" a new property to the end of the list of properties

                  websphereDefaultIsolationLevel with a value of 0. The type when you add it is java.lang.string

                  NOTEs.
                  - I did not have to set Transaction Isolation at all. Only this websphereDefaultIsolationLevel has any effect (in WAS).
                  - I restarted WAS after the changes were published. I just like to start everything clean

                  Also:
                  • you may be wondering but any additional property you put on the connectionURL is not carried over to the datasource definition. Only things like the server name.
                  • When you are working with a web project, the default in the build descriptor is J2EE=NO. This means that the data access will be through a JNDI type connection (Datasource). So, the only information that EGL uses out of the build descriptor for execution is the sqlJNDIName build descriptor. The sqlDB has no meaning for web projects, so even though you had put transaction isolation=none there, it is not used (and as I stated above, it was the wrong option for the datasource under WAS).