Trace JDBC connection to Java Thread / local socket port

* Remark*
Resent this message from June 23rd as it did not arrive at the list somehow- please excuse if I was just too dumb to see the message.

Dear JDBC list,

we are currently trying to pinpoint some performance issues on our database.
We can identify queries that cause high load on our servers but cannot pinpoint the origin of this query - at the end we only know the output of ps which gives us the client socket port of the query in question, like:

<procpid> postgres: <user> <database> <client ip (client socket port)> CMD

With a netstat -p | grep <client socket port> we are able to identify the PID of the JVM opening the socket, however in order to further pinpoint this issue we would need to get inside the JVM and determine the Java Thread currently running the query over this socket connection (in our case the java process is a JBoss AS and we would like to get the specific worker(s) issueing the query in question).

Is there any way to get to the Socket.getLocalPort() of a connection opened, i.e. establish a mapping between a LocalPort and a SQLConnection instance?

Basically what we want to do is output something like:

SQLConnection con= pool.getConnection(); // Get connection from pool, includes con.open()
Logger.debug(Thread.currentThread().getName() " has acquired a connection on "+ Mapping.getLocalPortForConnection(con));

As we have a central DB management layer we would be able to debug which thread currently occupies a connection on a specific port, thus enabling us to trace the connection to specific thread or at least give us pointers.

Looking at the source code it seems that the actual Socket connection is done within PGStream.
This socket is declared private and I don't see any debug statements which could give us hints on the local port used.

Current idea would be now to patch PGStream to add this Mapping.
Perhaps you have a different idea on how to identify the local port or -even better- trace a connection to a specific Java thread. I am certain we are not the only ones with this kind of problem.

Best regards,
Ludwig

petaFuel GmbH
Ludwig Adam
Geschäftsführer

Münchner Strasse 4
85354 Freising

Tel.: +49 (0) 8161 4060 - 202               Mobil: +49 (0) 179-22 911 02
Fax: +49 (0) 8161 4060 - 401               eMail: ludwig(dot)adam(at)petafuel(dot)de

Geschäftsführer: Ludwig Adam, Dr. Peter Schönweitz Amtsgericht München, HRB 133773 www.petaFuel.de