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!

[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.

[adrian.brock]

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?

[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

[adrian.brock]

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.

[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>

[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.

[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