Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

DB2 Exception - SQL0904N Unsuccessful execution caused by an unavailable resource

 
Mak Kumar
Greenhorn
Posts: 2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have written a code which is giving this exception

SQL0904N Unsuccessful execution caused by an unavailable resource. Reason code: "00D70027", type of resource: "00000220", and resource name: "DB2B.DSNDBC.DSNDB07.DSN4K05.I0001.A001". SQLSTATE=57011

at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(Unknown Source)
at COM.ibm.db2.jdbc.app.DB2Statement.execute2(Unknown Source)
at COM.ibm.db2.jdbc.app.DB2Statement.executeQuery(Unknown Source)
at com.ibm.divest.vo.CurrentFile.processLine(CurrentFile.java:465)

Scenario:
Database: DB2 9.2
Java: 5
JDBC Driver - Type 2

Executing a SELECT query again and again for n number of times. I am using this SELECT query with - "FOR READ ONLY WITH UR".
I am using same Connection again and again but closing ResultSet & Statement Object after each query and creating new one each time.

I am getting this exception at execute statement().

Code:
Statement psGetRdc=null;
ResultSet rsKna1=null;
psGetRdc = getCon().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
rsKna1 = psGetRdc.executeQuery(fetchDataFromRDCSQL);

When other application run at the same time and use the same table then only I get this exception.
This is related to Database - DataSet.
I do not know how to resolve it.
The only way I am able to think is close the connection after N number of records. Because the code is so fast that JAVA is releasing memory but Database is not able to release POOL.

I am not able to find the solution any where.
Can you please help.

Thanks
 
K. Tsang
Bartender
Posts: 3583
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to the Ranch.

You are using connection pool right? Since you close the result set and statement, why not also the connection for each query ... so the connection ideally gets back to the pool.

About the exception, can you pinpoint if the "unavailable resource" refers to the connection or something else? If connection, fine tune the connection pool settings.
 
Mak Kumar
Greenhorn
Posts: 2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for quick reply

Yes you are right that we can close the connection after PrepareStatement . But consider that we have 300K records and that number of times closing the connection. This will sure effect the performance.

I was thinking that I have already applied the commit count that commits the connection after 1000 record and that can be controlled by property.
We can close the connection when its doing commit. This may solve the problem.

And here "unavailable resource" is the DataSets in the DB2 database. I have no idea of datasets.
Can we do something of these DataSets ?
 
K. Tsang
Bartender
Posts: 3583
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Datasets huh, hmm. That's the table itself or temp table DB uses internal, not sure.

What do your query do? select or insert/update? I can only think of if data set unavailable is when say first query does something to table A and query 2 uses this table A but table A's state isn't consistent or something like that.

If query just plain select then this "unavailable resource" shouldn't happen.

Using transactions (commit/rollback) manually may be a good idea.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic