Problems accessing a DB2 database
What kind of problem are you having accessing your DB2 database?
- SQL0567N "DB2ADMIN" is not a valid authorization ID. SQLSTATE=42602.
- SQL0805N Package "package name" was not found.
- SQL0805N Package "NULLID.SQLLC300" was not found. SQLSTATE=51002.
- SQLException, with ErrorCode -99,999 and SQLState 58004, with Java "StaleConnectionException: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0119E Unexpected system failure. SQLSTATE=58004" using WAS40-type data source.
- Error message java.lang.reflect.InvocationTargetException: com.ibm.ws.exception.WsException: DSRA0023E: The DataSource implementation class "COM.ibm.db2.jdbc.DB2XADataSource" could not be found. when trying to access a DB2 database
- CLI0119E System error. SQLSTATE=58004 - DSRA8100 : Unable to get a XAconnection, or DSRA0011E: Exception: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0119E Unexpected system failure. SQLSTATE=58004.
- COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "2". SQLSTATE=40001.
- "COM.ibm.db2.jdbc.DB2ConnectionPoolDataSource" could not be found for data source (data_source).
- A NullPointerException error is thrown if the serverName property is not provided when running type 4 DB2 Universal JDBC driver
SQL0567N "DB2ADMIN " is not a valid authorization ID. SQLSTATE=42602
If you encounter this error when attempting to access a DB2 Universal Database (UDB):
- Verify that your user name and password in the data source properties page in the administrative console are correct.
- Ensure that the user ID and password do not contain blank characters before, in between, or after.
SQL0805N Package package-name was not found
Possible reasons for these exceptions:
- If the package name is NULLID.SQLLC300, see SQL0805N Package "NULLID.SQLLC300" was not found. SQLSTATE=51002. for the reason.
- You are attempting to use an XA-enabled JDBC driver on a DB2 database that is not XA-ready.
To correct the problem on a DB2 Universal Database (UDB), run this one-time procedure, using the db2cmd interface while connected to the database in question:
- DB2 bind @db2ubind.lst blocking all grant public
- DB2 bind @db2cli.lst blocking all grant public
The db2ubind.lst and db2cli.lst files are in the bnd directory of your DB2 installation root. Run the commands from that directory.
SQL0805N Package "NULLID.SQLLC300" was not found. SQLSTATE=51002
This error can occur because:
- The underlying database was dropped and recreated.
- DB2 was ugpraded and its packages are not rebound correctly.
To resolve this problem, rebind the DB2 packages by running the db2cli.lst script found in the bnd directory. For example: db2>@db2cli.lst.
SQLException, with ErrorCode -99,999 and SQLState 58004, with Java "StaleConnectionException: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0119E Unexpected system failure. SQLSTATE=58004", when using WAS40-type data source
An unexpected system failure usually occurs when running in XA mode (two-phase commit). Among the many possible causes are:
- An invalid username or password was provided.
- The database name is incorrect.
- Some DB2 packages are corrupted.
To determine whether you have a user name or password problem, look in the db2diag.log file to view the actual error message and SQL code. A message like the following example, with an SQLCODE of -1403, indicates an invalid user ID or password:
2002-07-26-14.19.32.762905 Instance:db2inst1 Node:000 PID:9086(java) Appid:*LOCAL.db2inst1.020726191932 XA DTP Support sqlxa_open Probe:101 DIA4701E Database "POLICY2" could not be opened for distributed transaction processing. String Title: XA Interface SQLCA PID:9086 Node:000 SQLCODE = -1403
To resolve these problems:
- Correct your user name and password. If you specify your password on the GUI for the data source, ensure that the user name and password you specify on the bean are correct. The user name and password you specify on the bean overwrite whatever you specify when creating the data source.
- Use the correct database name.
- Rebind the packages (in the bnd directory) as follows:
db2connect to dbname c:\SQLLIB\bnd>DB2 bind @db2ubind.lst blocking all grant public c:\SQLLIB\bnd>DB2 bind @db2cli.lst blocking all grant public
- Ensure that the \WebSphere\AppServer\properties\wsj2cdpm.properties file has the right user ID and password.
Error message java.lang.reflect.InvocationTargetException: com.ibm.ws.exception.WsException: DSRA0023E: The DataSource implementation class "COM.ibm.db2.jdbc.DB2XADataSource" could not be found. when trying to access a DB2 database
One possible reason for this exception is that a user is attempting to use a JDBC 2.0 DataSource, but DB2 is not JDBC 2.0-enabled. This situation frequently happens with new installations of DB2 because DB2 provides separate drivers for JDBC 1.X and 2.0, with the same physical file name. By default, the JDBC 1.X driver is on the class path.
To confirm this problem:
- On Windows systems, look for the inuse file in the java12 directory in your DB2 installation root. If the file missing, you are using the JDBC 1.x driver.
- On UNIX systems, check the class path for your data source. If the class path does not point to the db2java.zip file in the java12 directory, you are using the JDBC 1.x driver.
To correct this problem:
- On Windows systems, stop DB2. Run the usejdbc2.bat file from the java12 directory in your DB2 instalation root. Run this file from a command line to verify that it completes successfully.
- On UNIX systems, change the class path for your data source to point to the db2java.zip file in the java12 directory of your DB2 installation root.
CLI0119E System error. SQLSTATE=58004 - DSRA8100 : Unable to get a XAconnection or DSRA0011E: Exception: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0119E Unexpected system failure. SQLSTATE=5800
If you encounter this error when attempting to access a DB2 Universal Database (UDB) data source:
- Check your user name and password custom properties in the data source properties page in the administrative console. Verify that they are correct.
- Ensure the user ID and password do not contain any blank characters, before, in between, or after.
- Check that the WAS.policy file exists for the application, for example, D:\WebSphere\AppServer\installedApps\markSection.ear\META-INF\was.policy.
- View the entire exception listing for an underlying SQL error, and look it up using the DBM vendor message reference.
If you encounter this error while running DB2 on Red Hat Linux, the max queues system wide parameter is too low to support DB2 while it acquires the necessary resources to complete the transaction. When this problem exists, the exceptions J2CA0046E and DSRA0010E can precede the exception DSRA8100E.
To correct this problem, edit the /proc/sys/kernal/msgmni file to increase the value of the max queues system wide parameter to a value greater than 128.
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "2". SQLSTATE=40001
This problem is probably an application-caused DB2 deadlock, particularly if you see an error similar to the following when accessing a DB2 data source:
ERROR CODE: -911 COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "2". SQLSTATE=40001
To diagnose the problem:
- Execute these DB2 commands:
The directory_name\lock_snapshot.log now has the DB2 lock information.
- db2 update monitor switches using LOCK ON
- db2 get snapshot for LOCKS on dbName >
- Turn off the lock monitor by executing: db2 update monitor switches using LOCK OFF
To verify that you have a deadlock:
- Look for an application handle that has a lock-wait status, and then look for the ID of the agent holding lock to verify the ID of the agent.
- Go to that handle to verify it has a lock-wait status, and the ID of the agent holding the lock for it. If it is the same agent ID as the previous one, then you know that you have a circular lock (deadlock).
To resolve the problem:
- Examine your application and use a less restrictive isolation level if no concurrency access is needed.
- Use caution when changing the accessIntent value to move to a lower isolation level. This change can result in data integrity problems.
- For DB2/UDB V7.2 and earlier releases, you can set the DB2_RR_TO_RS flag from the DB2 command line window to eliminate unnecessary deadlocks, such as when the accessIntent defined on the bean method is too restrictive, for example, PessimisticUpdate. The DB@_RR_TO_RS setting has two impacts:
- If RR is your chosen isolation level, it is effectively downgraded to RS.
- If you choose another isolation level, and the DB2_RR_TO_RS setting is on, a scan skips over rows that are deleted but not committed, even though the row might qualify for the scan. The skipping behavior affects the RR, Read Stability (RS), and Cursor Stability (CS) isolation levels.
For example, consider the scenario where transaction A deletes the row with column1=10 and transaction B does a scan where column1>8 and column1<12. With DB2_RR_TO_RS off, transaction B waits for transaction A to commit or rollback. If transaction A rolls back, the row with column1=10 is included in the result set of the transaction B query. With DB2_RR_TO_RS on, transaction B does not wait for transaction A to commit or rollback. Transaction B immediately receives query results that do not include the deleted row. Setting DB2_RR_TO_RS effectively changes locking behavior, thus avoiding deadlocks.
"COM.ibm.db2.jdbc.DB2ConnectionPoolDataSource" could not be found for data source ([data-source-name])"
This error is denoted by message DSRA8040I: Failed to connect to the DataSource.
This error usually occurs when the class path of the DB2 JDBC driver is set correctly to ${DB2_JDBC_DRIVER_PATH}/db2java.zip but the environment variable DB2_JDBC_DRIVER_PATH is not set.
This error can also occur if you are using DB2 V7.1 or 7.2 and you have not yet run usejdbc2. This might be the problem if your path is correct but you still receive this error.
To confirm this problem:
- Go to the Manage WebSphere Variables panel.
- Select Environment to verify that there is no entry for the variable DB2_JDBC_DRIVER_PATH.
To correct this problem: Add the variable DB2_JDBC_DRIVER_PATH with value equal to the directory path containing the db2java.zip file.
If the path is correct and you need to run usejdbc2, refer to Configuring WebSphere Application Server for DB2 access.
A NullPointerException error is thrown if the serverName property is not provided when running type 4 DB2 Universal JDBC driver
For more information about configuring the DB2 Universal JDBC Driver Provider, refer to the DB2 section of the topic Vendor-specific data sources minimum required settings.
Problem Instead of an exception with a meaningful message, DB2 is throwing a NullPointerException error. Recommended response If you are using the Universal JDBC Type 4 driver, verify that you have provided a value for the serverName property. Setting the driverType property to 4 requires that the serverName property be provided. Check the IBM support page for information on problems that occur when using connection pooling with DB2.
Unexpected errors, which might involve "Database is read-only" messages, occur inconsistently when a J2EE 1.2 application component uses a V4 data source with a DB2 JDBC provider
Symptom You encounter unexpected errors on a sporadic basis when using a V4 data source with a DB2 JDBC provider in a J2EE-1.2 level application component. Although your application component does not set the read-only property of connections to true (that is, does not perform a Connection.setReadOnly(true) method call), your trace data might contain a "Database is read-only" message. Problem An application component that previously used the connection set the read-only property to true, but returned the connection to the connection pool without resetting the read-only value to false. (Note that the other application component could be a CMP bean, with code generated by the EJB container. This automatically generated code might have set the read-only property of connections to true). The following message text is an example of data that you might find in an exception stack trace that corresponds to this problem:
java.sql.SQLException: DB2JDBCSQLCompiler Received Error in Method compileSQL -> Insert/Update/Delete not honored. Database is read Only at COM.ibm.db2os390.sqlj.jdbc.DB2JDBCSQLCompiler.compileSQL(DB2JDBCSQLCompiler.java:205) at COM.ibm.db2os390.sqlj.jdbc.DB2SQLJStatement.executeUpdate(DB2SQLJStatement.java:726) at com.ibm.ejs.cm.proxy.StatementProxy.executeUpdateCommon(StatementProxy.java:440) at com.ibm.ejs.cm.proxy.StatementProxy.executeUpdate(StatementProxy.java:417) at ejb.C2Bean.run(C2Bean.java:70) at ejb.EJSRemoteStatelessC2_63bc8377.run(EJSRemoteStatelessC2_63bc8377.java:30) at ejb._EJSRemoteStatelessC2_63bc8377_Tie.run(_EJSRemoteStatelessC2_63bc8377_Tie.java:150) at ejb._EJSRemoteStatelessC2_63bc8377_Tie._invoke(_EJSRemoteStatelessC2_63bc8377_Tie.java:76) at com.ibm.ws390.orb.ServerRegionBridge.invoke(ServerRegionBridge.java:538) at com.ibm.ws390.orb.ORBEJSBridge.invoke(ORBEJSBridge.java:170)
Recommended response For V4 data sources that are configured with a DB2 JDBC provider, set a custom property that directs Application Server to reset the read-only property of connections before returning them to the connection pool. You can set this custom property through the administrative console: click Resources > JDBC Providers > JDBC Provider > Data Sources (Version 4) > Data source > Custom Properties. Define a new custom property named resetReadOnly with a value of true.
Troubleshooting by task: What are you trying to do?
Troubleshooting by component: What is not working?
Example: Accessing data using IBM extended APIs to share connections between container-managed and bean-managed persistence beans
Cannot access a data source
Problems accessing an Oracle data source
Problems accessing a SQL server data source
Problems accessing a Cloudscape database
Problems accessing a Sybase data source
Data access application programming interface support