Monday, 25 February 2008

Cancelling Long Running Queries with 11g JDBC Thin Driver

With the introduction of the 11g JDBC driver calling Statement.cancel() will actually cancel a long running query where in 10g JDBC Driver this would not work. Now with the 11g JDBC driver the cancel request is sent out of band. The out-of-band implementation may allow the database server to process the request immediately and terminate the system call that would otherwise block the query. This database feature is OS dependent and may not work on all platforms. I tested on Linux x86-32bit system with a 11.1.0 Database and it worked fine.

Java Code:

CancelQuery11G.java


package pas.jdbc.cancel;

import java.io.BufferedReader;

import java.io.IOException;
import java.io.InputStreamReader;

import java.sql.Connection;

import java.sql.ResultSet;
import java.sql.SQLException;

import java.sql.Statement;

import oracle.jdbc.pool.OracleDataSource;

public class CancelQuery11G
{
private OracleDataSource ods = null;
public final String userId = "scott";
public final String password = "tiger";
public final String url =
"jdbc:oracle:thin:@myhost:1521:linux11g";

public CancelQuery11G() throws SQLException
{
ods = new OracleDataSource();
ods.setUser(userId);
ods.setPassword(password);
ods.setURL(url);
}

public void run () throws SQLException, IOException
{
Connection conn = null;
Statement stmt = null;

ResultSet rset = null;

try
{
conn = getConnection();
getDriverDetails(conn);
stmt = conn.createStatement();

CancelThread ct = new CancelThread(stmt);
Thread th = new Thread(ct);
th.start();
Thread.sleep(1000);
System.out.println("Hit enter to cancel:");
BufferedReader stdin = new BufferedReader( new InputStreamReader( System.in ) );
String input = stdin.readLine();

System.out.println("Cancelling Query");
stmt.cancel();
System.out.println("Query Cancelled");

System.out.println("Hit enter to finish:");
stdin.readLine();

}
catch (SQLException e)
{
System.out.println("SQLException in run() : ");
e.printStackTrace();
}
catch (IOException ioe)
{
ioe.printStackTrace();
}
catch (InterruptedException ie)
{
ie.printStackTrace();
}
finally
{
if (ods != null)
ods.close();

if (conn != null)
conn.close();
}

}

private Connection getConnection() throws SQLException
{
return ods.getConnection();
}

private void getDriverDetails (Connection conn) throws SQLException
{
java.sql.DatabaseMetaData meta = conn.getMetaData ();

// gets driver info:

System.out.println("=============\nDatabase Product Name is ... " +
meta.getDatabaseProductName());
System.out.println("Database Product Version is " +
meta.getDatabaseProductVersion());
System.out.println("=============JDBC Driver Name is ........ " +
meta.getDriverName());
System.out.println("JDBC Driver Version is ..... " +
meta.getDriverVersion());
System.out.println("JDBC Driver Major Version is " +
meta.getDriverMajorVersion());
System.out.println("JDBC Driver Minor Version is " + meta.getDriverMinorVersion());
System.out.println("=============");
}
public static void main(String[] args) throws Exception
{
CancelQuery11G test = new CancelQuery11G();
test.run();
System.out.println("Completed");
}
}




CancelThread.java


package pas.jdbc.cancel;

import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;

public class CancelThread implements Runnable
{
private Statement stmt = null;
String longRunningSQL =
"select * from all_objects";
private int i = 0;

public CancelThread(Statement oracleStmt)
{
stmt = oracleStmt;
}

public void run()
{
ResultSet rset = null;

try
{
System.out.println("Executing statement....");
rset = stmt.executeQuery(longRunningSQL);
System.out.println("Processing resultset now..");
while (rset.next())
{
i++;
}
System.out.println("Finished with resultset and statement now..");
}
catch (SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if (rset != null)
rset.close();

if (stmt != null)
stmt.close();
}
catch (SQLException e)
{

}
}

}
}



Output:

=============
Database Product Name is ... Oracle
Database Product Version is Oracle Database 11g Release 11.1.0.0.0 - Production
=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 11.1.0.6.0-Production
JDBC Driver Major Version is 11
JDBC Driver Minor Version is 1
=============
Executing statement....
Processing resultset now..
Hit enter to cancel:

Cancelling Query

java.sql.SQLException: ORA-01013: user requested cancel of current operation

at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:119)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:115)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:221)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:467)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:417)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1084)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:187)
at oracle.jdbc.driver.T4CStatement.fetch(T4CStatement.java:1312)
at oracle.jdbc.driver.OracleResultSetImpl.close_or_fetch_from_next(OracleResultSetImpl.java:606)
at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:493)
at pas.jdbc.cancel.CancelThread.run(CancelThread.java:28)
at java.lang.Thread.run(Thread.java:595)

Query Cancelled
Hit enter to finish:

Completed

1 comment:

Anonymous said...

very good!