Finding the actual Job number for a particular JDBC SQL connection to iSeries?

I am using the JTOpen JDBC driver to connect to the iSeries (aka AS/400, IBM System-i, IBMi, WTH?!...). I am having problems with a particular statement and it appears I need to go 开发者_Go百科back to the actual SQL job QSQSRVR (or QZDASOINIT?) to find more details. Only problem is that there are hundreds of these on the system. Is there an easy way to determine the job which is actually handling my SQL connection or a particular statement?

---

From the JT400 javadoc of the class AS400JDBCConnectionHandle :

getServerJobIdentifier

public String getServerJobIdentifier() throws SQLException

Returns the job identifier of the host server job corresponding to this

connection. Every JDBC connection is associated with a host server job on the system. The format is:

    * 10 character job name
    * 10 character user name
    * 6 character job number 

Note: Since this method is not defined in the JDBC Connection

interface, you typically need to cast a Connection object returned from PooledConnection.getConnection() to an AS400JDBCConnectionHandle in order to call this method:

      String serverJobIdentifier = ((AS400JDBCConnectionHandle)connection).getServerJobIdentifier();


Returns:
    The server job identifier, or null if not known. 
Throws:
    SQLException - If the connection is not open.

---

If the remote app can't be modified, there are a couple possibilities on the server side. The most common method is to search for jobs that have a lock on the user profile (*USRPRF) that logged on through a connection. The system won't allow a user profile to be deleted while it's active in a job, so the lock can be handy:

WRKOBJLCK logonuser *USRPRF

The connection itself can also be useful. The NETSTAT command can list connections:

NETSTAT *CNN

The remote IP address can be checked against the listed services to determine the particular connection. The matching system job can be accessed from there.

---

The following approach is safer in case you are not using connection pooling (no idea why you wouldn't pool your connections :), but...)

public static String getQualifiedJobNumber(Connection connection) throws SQLException, CustomException {
    if (connection != null && !connection.isClosed()) {
        String jobName = null;
        try {
            AS400JDBCConnectionHandle handle = ((AS400JDBCConnectionHandle) connection);
            if (handle != null) {
                jobName = handle.getServerJobIdentifier();
            }
        } 
        catch (ClassCastException e) {
            try {
                AS400JDBCConnection as400Connection = ((AS400JDBCConnection) connection);
                if (as400Connection != null) {
                    jobName = as400Connection.getServerJobIdentifier();
                }
            }
            catch (ClassCastException e2) {
                throw new CustomException("Attempting to retrieve an AS400 qualified job number from a non-AS400 connection");
            }
        }
        if (jobName != null && jobName.length() == 26) {
            return jobName.substring(20) + "/" + jobName.substring(10, 20).trim() + "/" + jobName.substring(0, 10).trim();
        }
    }
    return null;
}