wood burning stoves 2.0*
The moose likes JDBC and the fly likes DB2 CallableStatement [max. number of statement exceeded issue] Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "DB2 CallableStatement [max. number of statement exceeded issue]" Watch "DB2 CallableStatement [max. number of statement exceeded issue]" New topic
Author

DB2 CallableStatement [max. number of statement exceeded issue]

iamiqbal.m Mohiuddin
Greenhorn

Joined: May 11, 2006
Posts: 12
Preview
DB2 callable statement close.
All:

I am running into very strange situation.

I am getting error maximum number of statements exceeded. which is known that the statement / callable statement is not closed somewhere in the code.

which is not the case here in my code. I am closing callable statement.

I have ran two scenarios one worked and another failed.

Scenario one which worked:
I have multithreaded application if i create the connection after i spawn a new thread it works without any error.

pseudo code is as follows
_________________________________________________________________
public class Thread1 implements Runnable{

private DBMgrImpl dbMgrImpl = null;

public Thread1(){
this.dbMgrImpl = new DBMgrImpl(); //creates DB connection
}

public void run(){

dbLoader.insertRecord(dbMgrImpl ); // inserts record by creating callable statement. I have finally block in insertRecord which closes callable statement.

}

}

public class Test{

for(int i<100){
Thread1 thread = new Thread1();
new Thread(thread).start();
}
}

_______________________________________________________________________________________

This above approache works fine with no errors. Though iam not closing connection anywhere in the above. Its just callable statement being closed.

Scenario two which fails:

public class Thread1 implements Runnable{

private DBMgrImpl dbMgrImpl = null;

public Thread1(DBMgrImpl dbMgrImpl ){
this.dbMgrImpl = dbMgrImpl ;
}

public void run(){

dbLoader.insertRecord(dbMgrImpl ); // inserts record by creating callable statement. I have finally block in insertRecord which closes callable statement.

}

}

public class Test{

DBMgrImpl dbMgrImpl = new DBMgrImpl(100); // create 100 connections before spawning any thread

for(int i<100){
Thread1 thread = new Thread1(dbMgrImpl );
new Thread(thread).start();
}
}

This above approache blows with maximum number of statements exceeded.
_____________________________________________________________________

My understanding of what might be happening is when iam creating DBMgrImpl inside the thread as soon as thread completes its getting garbage collected. So connection / callable statements are not available.
whereas in other scenario since DBMgrImpl is created before spawning the thread and is passed to threads each one uses it and DBMgrImpl remains in memory even after thread is completed.

Does anyone of you guys got into this kind of situation. I am using jt400.jar [type 4 driver]. I have read in few forums that we have to explicitly invoke garbage collection for this kind of errors. Wondering is this driver [jt400] issue?

I would appreciate any pointers in this direction.

Thanks,


 
Consider Paul's rocket mass heater.
 
subject: DB2 CallableStatement [max. number of statement exceeded issue]
 
Similar Threads
Closing Statement object prior to committing
Thread example, dont know why is it a deadlock condition
locking??
Regarding Thread's join()
"Limit No of Statements Exceded"