Setting V$SESSION.program property on Glassfish JD

Question

my Java EE App is deployed on Glassfish 3.0.1, and uses a JDBC Connection Pool to Connect to an Oracle 9i database. I am using JPA to read/write data to the database, which is working fine. However, to get better reporting regarding the load this app is putting on the database, I want to set the V$SESSION.program column for use by oracle.

From various google searches (eg. http://forums.oracle.com/forums/thread.jspa?messageID=3271623) it looks like I should just be able to add this as a property, the same as you would set any other property. So I have tried this by changing the domain.xml file for Glassfish (see below), and the V$SESSION.program property is now set on the JDBC connection pool when I view it using the Glassfish Admin Console (When I start up the glassfish server, go to the admin page and browse to Resources->JDBC->Connection Pools->MyConnectionPool->Additional Properties I can see an entry for V$SESSION.program set appropriately).

However, when I query the Oracle Database for the connections (SELECT * FROM V$SESSION), they have the same V$SESSION.program as before (which is "JDBC Thin Client"), rather than the one I set in the domain.xml and which I can see as a property of the JDBC Connection Pool on the Glassfish Admin Page.

The resources section of my domain.xml is below:

<resources>
  <jdbc-connection-pool pool-resize-quantity="4" max-pool-size="16" datasource-classname="oracle.jdbc.pool.OracleDataSource" res-type="javax.sql.DataSource" steady-pool-size="4" name="mydatabasename">
    <property name="datasourceName" value="OracleConnectionPoolDataSource" />
    <property name="databaseName" value="mydatabasename" />
    <property name="password" value="mypassword" />
    <property name="portNumber" value="1521" />
    <property name="serverName" value="myservername" />
    <property name="url" value="jdbc:oracle:thin:@myservername:1521:mydatabasename" />
    <property name="user" value="myuser" />
    <property name="v$session.program" value="MyGlassfishApp" />
  </jdbc-connection-pool>
  <jdbc-resource pool-name="mydatabasename" jndi-name="jdbc/mydatabasename" />
</resources>

And my Persistence.xml is as below (I've deleted all the <class>...</class> lines):

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">

  <persistence-unit name="Persistence-ejb" transaction-type="RESOURCE_LOCAL">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <jta-data-source>jdbc/mydatabasename</jta-data-source>
    <exclude-unlisted-classes>false</exclude-unlisted-classes>
    <properties>
      <property name="javax.persistence.query.timeout" value="60"/>
      <property name="javax.persistence.lock.timeout" value="60"/>
      <property name="javax.persistence.target-server" value="SunAS9"/>

      <!-- Disable caching so we always use the DB directly -->
      <property name="eclipselink.cache.shared.default" value="false"/>
      <property name="eclipselink.query-results-cache" value="false"/>

    </properties>
  </persistence-unit>

</persistence>

EDIT: I have tried v$session.program, V$SESSION.PROGRAM and V$SESSION.program - none have worked though... I have also tried setting the V$SESSION.program property on the EntityManager, but that didn't work (I didn't really expect it to since from what I understand the EntityManager properties are only to do with the JPA layer, not the underlying connection to the Database - but I'm trying stuff out of desperation...)

I also tried using a SessionCustomizer as suggested here. The SessionCustomizer code I used is below:

public class ProgramSessionCustomizer implements SessionCustomizer {
    private static Logger logger = Logger.getLogger(ProgramSessionCustomizer.class);

    @Override
    public void customize(Session s) throws Exception {
        logger.error("ProgramSessionCustomizer setting v$session.program");
        s.getDatasourceLogin().setProperty("v$session.program", "MYprogramTEST");
        logger.error("ProgramSessionCustomizer has set v$session.program");
    }
}

I then set the SessionCustomizer in the persistence.xml by adding the property:

<property name="eclipselink.session.customizer" value="persistence.config.ProgramSessionCustomizer"/>

I can see the log lines, so the Customizer is definitely being called. However, I don't see the program set in the database, and I don't even see it set when I browse to the JDBC connection pool properties on the Glassfish Admin Console (which, as I said above, I do see when I set the property using the glassfish domain.xml)

Again, any more suggestions would be very welcome as I'm at a loss!

Answer 1

This works for me using Spring. It should works for yours Java EE app as well. The idea is to set the v$session param indirectly via properties passed to connectionProperties property

<bean class="oracle.jdbc.pool.OracleDataSource" destroy-method="close">
    <property name="URL" value="${configDb.url}"/>
    <property name="user" value="${configDb.user}"/>
    <property name="password" value ="${configDb.password}"/>
    <property name="connectionCachingEnabled" value="true"/>
    <property name="connectionProperties">
       <value>v$session.program:PUT_YOUR_NAME_HERE</value>
    </property>
</bean>

Answer 2

I finally found out how to set this connection property in WebSphere 8 too for a XA datasource. It can be set in the WebSphere admin console: Resources > JDBC providers > [Oracle JDBC Driver (XA)] > Data Sources > [data source name] > Custom properties

Just add there a property with a key: connectionProperties and its value: v$session.program:PUT_YOUR_NAME_HERE