• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

DB2 Exception - SQL0904N Unsuccessful execution caused by an unavailable resource

 
Greenhorn
Posts: 2
Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Bartender
Posts: 3648
16
Android Mac OS X Firefox Browser Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 3648
16
Android Mac OS X Firefox Browser Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic