Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

DB2 CallableStatement [max. number of statement exceeded issue]

 
iamiqbal.m Mohiuddin
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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,


 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic