Thursday, 20 September 2007

How to populate v$session.program from mid tier data source connections

I am constantly asked how to distinguish the different JDBC connections that come from the middle tier. His an example on how to do this OAS 10.1.3.x.

1. Create a data source as follows which is using the JDBC driver as the factory class (oracle.jdbc.driver.OracleDriver)


<managed-data-source
connection-pool-name="ScottConnectionPool"
jndi-name="jdbc/scottDS"
name="jdbc/scottDS"/>
<connection-pool
name="ScottConnectionPool"
initial-limit="5"
min-connections="5">
<connection-factory
factory-class="oracle.jdbc.driver.OracleDriver"
user="scott"
password="tiger"
url="jdbc:oracle:thin:@//papicell-au2.au.oracle.com:1521/lnx102">
<property name="v$session.program"
value="OAS10132-apple-scottpool"/>
</connection-factory>
</connection-pool>


2. In order to populate the PROGRAM column you need to set the property as shown below.

<property name="v$session.program"
value="OAS10132-apple-scottpool">

3. When the data source is started it will create 5 connections so we can then run the following SQL to see if it worked once the connection pool has been started.

set head on feedback on

set pages 999
set linesize 120

prompt
prompt SCOTT sessions

col machine format a25
col username format a15
col username format a8
col program format a25

select
username,
program,
status,
last_call_et seconds_since_active,
to_char(logon_time, 'dd-MON-yyyy HH24:MI:SS') "Logon"
from v$session
where username = 'SCOTT'
/


The result is as follows which clearly shows that the PROGRAM field has been populated by our mid tier data source:


2 comments:

Jayesh said...

Pas,
I am trying to the same thing using JNDI but not achieving any success.
It works when you try using DriverManager.getConnection, but fails when you want to use DataSource ds = (DataSource)ctx.lookup(datasourceName);

Here is my little code, which is not giving me any value for v$session.program.

-----------------------------------
java.util.Properties params = new java.util.Properties();
params.put("v$session.program", "MyApp");
Context ctx = new InitialContext(params);

DataSource ds = (DataSource)ctx.lookup(datasourceName);

-----------------------------------
Any insights ??

Thanks,

Jayesh

Pas Apicella said...

Jayesh,

Those properties need to be set at the data source level in the data-sources.xml and this is only supported in OAS 10.1.3.x. The Context is used to provide authentication details for example prior to obtaining a resource from the server and cannot be used to set JDBC properties as you have tried, so it's ignored when you try that.

If your using OAS 10.1.2 this solution won't help but if your using OAS 10.1.3.x it will work as I have shown.

Thanks
Pas