Answer

There is no session information stored on the Rational Portfolio Manager as to who is connected and when they connect. This is managed by application server and it does not collect such information.

There is a way to derive the information using the Oracle table V$SESSION, if you are using the Rational Portfolio manager schema to query this table you need to assure that this schema as the rights to the SELECT_CATALOG_ROLE.

What follows is a script and the results from a test system:

<SCRIPT>

set linesize 130
set pagesize 3000

column sid format 99999
column SQL_ID format 999999
column machine format a32
column OSUSER format a15

SELECT S.SID,
       S.SERIAL#,
       S.USERNAME,
       S.OSUSER,
       S.MACHINE,
       S.COMMAND,
       S.STATUS
FROM V$SESSION S;

<SCRIPT/>

<RESLT Example>

SID    SERIAL# USERNAME     OSUSER       MACHINE     COMMAND    STATUS  
------ ---------- --------- ----- -------------- ---------- --------
140      39836           oracle          rpmcsaix02        0 ACTIVE
141      22686 OTHER                     rpmdev31          0 INACTIVE
142      62386 RPM                       rpmdev31          0 INACTIVE
143      63960 RPM       oracle          rpmcsaix02        3 ACTIVE
145       6818 OTHER                     rpmdev31          0 INACTIVE
146          2           oracle          rpmcsaix02        0 ACTIVE
149      61492 RPM                       rpmdev19          0 INACTIVE
151      47563 RPM                       rpmdev19          0 INACTIVE
152          3           oracle          rpmcsaix02        0 ACTIVE
153          3           oracle          rpmcsaix02        0 ACTIVE
154      29127 RPM       db2admin        rpmdev19          0 INACTIVE
159      56879 RPM                       rpmdev31          0 INACTIVE
160          1           oracle          rpmcsaix02        0 ACTIVE
161          1           oracle          rpmcsaix02        0 ACTIVE
162          1           oracle          rpmcsaix02        0 ACTIVE
163          1           oracle          rpmcsaix02        0 ACTIVE
164          1           oracle          rpmcsaix02        0 ACTIVE
165          1           oracle          rpmcsaix02        0 ACTIVE
166          1           oracle          rpmcsaix02        0 ACTIVE
167          1           oracle          rpmcsaix02        0 ACTIVE
168          1           oracle          rpmcsaix02        0 ACTIVE
169          1           oracle          rpmcsaix02        0 ACTIVE
170          1           oracle          rpmcsaix02        0 ACTIVE

23 rows selected.

<RESLT/>

The query above does not filter any information at this time, but you can see that you can add a WHERE clauses to the Query to filter out condition that you may require. For example, the USERNAME defines the schema that is being used to connect to the database and the MACHINE value will indicate from what machine the connection is coming from. Also, based on the status field you can determined if the connection is active or inactive and waiting for an action.

Rational Portfolio Manager can have inactive connection on the database. This will depend on the configuration in the IBMRPM.xml file of the Tomcat application server, or the WebSphere Application server's or BEA WebLogic application server's connection pool definitions.

See Max Active and Max Idle properties. What follows in an example of these settings in Tomcat:

<SNAPSHOT IBMRPM.xml>

    <Resource
    name="jdbc/RPMDATASOURCE"
    type="javax.sql.DataSource"
    driverClassName="oracle.jdbc.OracleDriver"
    password="proc"
    maxIdle="10"
    maxWait="5000"
    username="proc"
    url="jdbc:oracle:thin:@//192.168.222.197:1521/IBMRPM"
    maxActive="4"/>

<SNAPSHOT/>

With the configuration above, you should have a minimum 4 and maximum of 10 active or inactive connections, based on the status column of the query. Once you have this information you can poll the database at regular intervals and get the information required.