Kill SQL query execution if it exceeds allotted time in DB2

Question

I'm creating a program in .NET that uses the IBM DB2 .NET provider(IBM.Data.DB2) to connect to a DB2 database(v9.7) and run select queries.

The program should terminate the SQL execution, if a submitted select query takes more than 5 seconds to return the data.

To implement this as per IBM's documentation I could use the QueryTimeout parameter in the connection.

string connStr = "Server=server:12345;Database=db;UID=user;PWD=pass; QueryTimeout = 5;";

            DB2Connection conn = null;
            DB2Command cmd = null;

            conn = new DB2Connection(connStr);
            conn.Open();
            Console.WriteLine("IBM DB2: " + conn.IsOpen);
            if (conn.IsOpen)
            {
                Console.WriteLine(conn.ConnectionTimeout);
                cmd = conn.CreateCommand();
                cmd.CommandText = "select * from user.orders";                    
                DB2DataReader reader = cmd.ExecuteReader();
                int counter = 0;
                while (reader.Read())
                {
                    counter += 1;
                    Console.WriteLine(reader.GetDB2Int64(0));
                }                    
                reader.Close();                    
            }
            conn.Close();

When I ran the query in winSQL, the query took about 20 seconds to execute. But when I execute it in this program, it took me the same 20 seconds. As per the documentation the query should have terminated in 5 seconds.

Why has the execution not stopped?

PS: I've also tried setting the cmd.CommandTimeout to 5 and it still would not stop the execution.

Answer 1

The problem with the above example is that ExecuteReader() creates a cursor to the database that pulls the data as an when its read, so the query never times out.

But using a DataAdapter to a dataset, pulls the data in a single go. So the below seems to kill the execution.

string connStr = "Server=server:12345;Database=db;UID=user;PWD=pass;";
conn = new DB2Connection(connStr);
conn.Open();

if (conn.IsOpen)
{
    Console.WriteLine(conn.ConnectionTimeout);
    cmd = conn.CreateCommand();
    cmd.CommandText = "select * from orders";
    cmd.CommandTimeout = 5;

    DB2DataAdapter adp = new DB2DataAdapter(cmd);
    DataSet ds = new DataSet();
    adp.Fill(ds);
    foreach (DataRow row in ds.Tables[0].Rows){
        Console.WriteLine(row[0]);
    }
}
conn.Close();