7 Replies Latest reply on Apr 26, 2010 11:50 PM by vickyk

    How to define a read-only connection ?

    rmazzola

      Hi

      I'm trying to define a readonly connection to a IBM DB2 (9.7) database

      I try several way but without success.

       

      I read also http://community.jboss.org/message/22945

       

      this is my definition

       

      {code}

      <?xml version="1.0" encoding="UTF-8"?>
      <datasources>
      <local-tx-datasource>
      <jndi-name>nowreadonly</jndi-name>
      <connection-url>jdbc:db2:nownwgui</connection-url>
      <driver-class>com.ibm.db2.jcc.DB2Driver</driver-class>
      <user-name>db2admin</user-name>
      <password>db2admin</password>
      <connection-property name="readOnly">true</connection-property>
      <min-pool-size>1</min-pool-size>
      <max-pool-size>20</max-pool-size>
      <idle-timeout-minutes>15</idle-timeout-minutes>
      <transaction-isolation>TRANSACTION_READ_UNCOMMITTED</transaction-isolation>
      </local-tx-datasource>
      </datasources>

      {code}

       

       

      I try also try using  new-connection-sql to "SET TRANSACTION READ ONLY" but it is not allowed on db2 9.7

       

      To test it I have a simple JSP doing ..

       

      {code}

      ...

      public Connection getConnection(String aName)
      {
      try
      {
           InitialContext vContext = new InitialContext();
        DataSource ds = (DataSource)vContext.lookup(aName);
        Connection vConnection = ds.getConnection();
        return vConnection;
      }
      catch (Exception ex)
      {
        ex.printStackTrace();
      }
      return null;
      }

      .....

      Connection vConnection = getConnection("java:/nowreadonly");
      System.out.println(" *** vConnection : " +vConnection );

      System.out.println("*** vConnection.isReadOnly() : " + vConnection.isReadOnly());

      ...

      {code}

       

      But the isReadOnly return always false...


      Any idea?

       

      Thanks You!

        • 1. Re: How to define a read-only connection ?
          vickyk

          Can you try setting the readOnly properties via the simple java standalone jdbc program and see if it works first, we can then take a look at why this does not work in JCA if you got it working in standalone jdbc program.

          • 2. Re: How to define a read-only connection ?

            Roberto Mazzola wrote:


            <connection-property name="readOnly">true</connection-property>

            You're missing the xml attribute type="java.lang.Boolean".

            See http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad/rjvdsprp.htm

             

            It will be a java.lang.String by default which it probably doesn't recognise?

            • 3. Re: How to define a read-only connection ?
              rmazzola

              I try it but it doesn't works.

               

              I change my DS to

               

              <connection-property name="readOnly" type="java.lang.Boolean">true</connection-property>

               

              It reply always "false".

               

              I try different way.. also using java standalone class but the only  way is to invoke directly the setReadOnly method on the connection

               

              I opened also a post of IBM DB2 Forum....

               

              I hope to find a solution...

               

              ty

              • 4. Re: How to define a read-only connection ?

                Roberto Mazzola wrote:

                 

                I try it but it doesn't works.

                 

                I change my DS to

                 

                &lt;connection-property name="readOnly" type="java.lang.Boolean"&gt;true&lt;/connection-property&gt;

                 

                 

                It reply always "false".

                 

                I try different way.. also using java standalone class but the only  way is to invoke directly the setReadOnly method on the connection

                 

                I opened also a post of IBM DB2 Forum....

                 

                I hope to find a solution...

                 

                ty

                Actually that won't work.

                 

                I checked the dtd and there is no type="" attribute for ConnectionPropertys in -ds.xml,

                only for ConfigPropertys in ra.xml

                 

                You might want to raise a feature request for it in JIRA?

                 

                If you can make your datasource XA, then you will be able to use

                <xa-datasource-property name="readOnly">true</xa-datasource-property>

                and it will work out the type from the setReadOnly(boolean) method.

                 

                For local datasources we don't know the types since we use

                java.sql.Driver.connect(String, Properties);

                and like I said above, there is no way to specify it.

                • 5. Re: How to define a read-only connection ?
                  rmazzola

                  Unfortunatelly I try all ways... but doens't works..

                  I try also XA using type 2 and type 4.. nothing

                   

                   

                  <?xml version="1.0" encoding="UTF-8"?>
                  <datasources>
                     <!--
                         XADatasource for DB2 v8.x (app driver)
                     -->
                     <xa-datasource>
                       <jndi-name>nowreadonly</jndi-name>
                       <xa-datasource-class>com.ibm.db2.jcc.DB2XADataSource</xa-datasource-class>
                       <!--
                       <xa-datasource-property name="ServerName">localhost</xa-datasource-property>
                       <xa-datasource-property name="PortNumber">50000</xa-datasource-property>
                       -->
                       <xa-datasource-property name="DatabaseName">nownwgui</xa-datasource-property>
                       <xa-datasource-property name="ReadOnly">true</xa-datasource-property>
                       <!--
                       <xa-datasource-property name="readOnly">true</xa-datasource-property>
                       -->
                       <!-- DriverType can be either 2 or 4, but you most likely want to use the JDBC type 4 as it doesn't require a DB" client -->
                       <xa-datasource-property name="DriverType">2</xa-datasource-property>
                       <!-- If driverType 4 is used, the following two tags are needed -->     
                       <track-connection-by-tx></track-connection-by-tx>
                       <isSameRM-override-value>false</isSameRM-override-value>
                       <xa-datasource-property name="User">db2admin</xa-datasource-property>
                       <xa-datasource-property name="Password">db2admin</xa-datasource-property>
                        <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
                        <metadata>
                           <type-mapping>DB2</type-mapping>
                        </metadata>
                     </xa-datasource>
                  </datasources>
                  
                  • 6. Re: How to define a read-only connection ?
                    vickyk
                    Can you make sure that com.ibm.db2.jcc.DB2XADataSource::setReadOnly()/getReadOnly() exists?
                    The IBM docs shows that it exists but can you confirm if there are setter getter methods for it too ?
                    http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad/rjvdsprp.htm

                    Also you could try setting this in xa-datasource/local-tx-datasource?
                    <connection-property name="connectionProperties">readOnly=true</connection-property>

                    Let us know if this works.
                    • 7. Re: How to define a read-only connection ?
                      vickyk

                      Adrian Brock wrote:

                      For local datasources we don't know the types since we use

                      java.sql.Driver.connect(String, Properties);

                      and like I said above, there is no way to specify it.

                      I think we should be able to set the readOnly property above as readOnly=true.

                      I am right now feeling lazy to check the source code