+

Search Tips   |   Advanced Search

Data access problems for DB2 databases

This article provides troubleshooting tips for accessing DB2 databases.


What kind of problem are you having accessing your DB2 database?


Kerberos login error occurs while connecting to the database

If we are using the following list of scenarios, a kerberos log-in error occurs while trying to connect to the database.

  1. The injection statements in an EJB 3.0 bean are at the attribute level.

  2. The persistence.xml file does not call out metadata information regarding the database to which we have a connected.

  3. The component managed alias on the data source is not valid.

  4. The database is set up to use a one-off security mechanism, for example, a mechanism other than the typical user ID and password mechanism, such as kerberos.

During the injection of an EJB 3.0 bean, the Java Persistence API (JPA) persistence.xml file attempts to connect to the database to lookup metadata. To make the connection, a Java 2 Connector (J2C) requests a Subject from the security context. In this scenario, the security collaboration API called to put information into the security context was not called and the subject is returned without containing GSSCredentials.

The call should have taken place in the EJB container, but the EJB container did not call the collaboration API because the security APIs require a fully constructed bean that did not exist. Also, the EJB 3.0 specification explicitly states that the bean construction should not be done in a defined security context. Because of the design of the security API and the direction of the EJB 3.0 specification, the EJB container did not call the security collaboration APIs, therefore, the security context did not know to set up the Subject and there were no GSSCredentials.

The absence of GSSCredentials causes the Relational Resource Adapter (RRA) implementation to use the wrong code path; instead of telling DB2 to connect using the GSSCredential, it uses the identity associated with the component managed alias. The result is that the component managed alias is configured to be an identity that is not known to kerberos, therefore, when the DB2 driver relayed this identity to the database, an error occurred.

The following code is an example of the error: 

Caused by: javax.security.auth.login.FailedLoginException: Login error: com.ibm.security.krb5.KrbException, status code: 6
	message: Client not found in Kerberos database
	at com.ibm.security.jgss.i18n.I18NException.throwFailedLoginException(I18NException.java:25)
	at com.ibm.security.auth.module.Krb5LoginModule.b(Krb5LoginModule.java:733)
	at com.ibm.security.auth.module.Krb5LoginModule.c(Krb5LoginModule.java:610)
	at com.ibm.security.auth.module.Krb5LoginModule.login(Krb5LoginModule.java:433)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:45)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
	at java.lang.reflect.Method.invoke(Method.java:599)
	at javax.security.auth.login.LoginContext.invoke(LoginContext.java:795)
	at javax.security.auth.login.LoginContext.access$000(LoginContext.java:209)
	at javax.security.auth.login.LoginContext$4.run(LoginContext.java:709)
	at java.security.AccessController.doPrivileged(AccessController.java:251)
	at javax.security.auth.login.LoginContext.invokePriv(LoginContext.java:706)
	at javax.security.auth.login.LoginContext.login(LoginContext.java:603)
	at com.ibm.db2.jcc.a.ud.a(ud.java:25)

To prevent this error, follow these recommendations:


SQL0567N "DB2ADMIN" is not a valid authorization ID. SQLSTATE=42602

If we encounter this error when attempting to access a DB2 Universal Database™ (UDB):

  1. Verify that your user name and password in the data source properties page in the administrative console are correct.
  2. Ensure that the user ID and password do not contain blank characters prior to, between, or after.


SQL0805N Package package-name was not found

Possible reasons for these exceptions:

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:

  1. DB2 bind @db2ubind.lst blocking all grant public
  2. DB2 bind @db2cli.lst blocking all grant public

The db2ubind.lst and db2cli.lst files are in the bnd directory of our DB2 installation root. Run the commands from that directory.


SQL0805N Package "NULLID.SQLLC300" was not found. SQLSTATE=51002

This error can occur because:

To resolve this problem, rebind the DB2 packages by running the db2cli.lst script found in the bnd directory. For example: db2>@db2cli.lst.


SQL30082N Attempt to establish connection failed with security reason "17" ("UNSUPPORTED FUNCTION") SQLSTATE=08001

This error can occur when the security mechanism specified by the client is not valid for this server. Some typical examples:

To resolve this problem, ensure that the client and server use the same security mechanism. For example, if this is an error on the data source, verify that we have assigned a user id and password or authentication alias.


SQLException, with ErrorCode -99,999 and SQLState 58004, with Java

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:

To determine whether we 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:

  1. Correct your user name and password. If we specify your password on the GUI for the data source, ensure that the user name and password we specify on the bean are correct. The user name and password we specify on the bean overwrite whatever we specify when creating the data source.

  2. Use the correct database name.
  3. 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
  4. Ensure that the \WebSphere\AppServer\properties\wsj2cdpm.properties file has the correct user ID and password.


Error message java.lang.reflect.InvocationTargetException: com.ibm.ws.exception.WsException: DSRA0023E

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:

To correct this problem:


CLI0119E System error

If we encounter this error when attempting to access a DB2 Universal Database (UDB) data source: 

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






 
    

  1.  On the data source properties page in the administrative console, verify that the correct
database name is specified on the data source.


  2.  On the custom properties page, check your user name and password custom properties. Verify that they are correct. 


  3. Ensure the user ID and password do not contain any blank characters, prior to, between, or after. 


  4.  Check that the WAS.policy file exists for the application, for example,
D:\WebSphere\AppServer\installedApps\markSection.ear\META-INF\was.policy. 


  5.  View the entire exception listing for an underlying SQL error, and look it up using the DBM
vendor message reference. 






 If we 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




 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: 



 
    

  1.  Execute these DB2
commands: 



     
      

    1. db2 update monitor switches using LOCK ON


    2. db2 get snapshot for LOCKS on dbName > 

    
 The directory_name\lock_snapshot.log now has the DB2 lock information. 


  2.  Turn off the lock monitor by running: db2 update monitor switches using LOCK
OFF







 To verify that we have a deadlock: 



 
    

  1.  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. 


  2.  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 we have a circular lock
(deadlock). 







 To resolve the problem:



 
    

  1. Examine the application and use a less restrictive isolation level if no concurrency access is needed.


  2. Use caution when changing the accessIntent value to move to a lower isolation
level. This change can result in data integrity problems.


  3. For DB2/UDB v7.2 and earlier releases, we 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 we 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 we are using DB2 v7.1 or 7.2 and we 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:



 
    

  1. Go to the Manage WebSphere Variables page.


  2. 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.






 (ZOS) Error 10500 (E_Fatal) returns when an attempt is made to either publish or inquire on an entity exceeding 255 characters in one of its fields



 This error usually occurs when we make an attempt to either publish or inquire on an entity
exceeding 255 characters in one of its fields. This is less obvious when using non-English
characters, as the real limit is reached prior to 255 visual characters are displayed.




 To correct this problem: Accept this as a limitation in using DB2 v7 on z/OS . Do not exceed the 255 character
limit.





 
  java.sql.SQLException: Failure in loading T2 native library db2jcct2 DSRA0010E: SQL State =
null, Error Code = -99,999 




 The Failure in loading message indicates one of the following problems:


 






 
 Lock contention exception occurs in database when data source implementation type is XA




 Because a lock contention exception can be caused by many factors, consider the following explanation and recommended response as a strategy for eliminating the possible reasons for our
lock contention problem.




Symptom
    Problem
    Description
    Recommended response



A lock contention exception occurs in a DB2 database that the application accesses through a data source
of implementation type XA.
    The application is trying to access database records that are locked by an XA transaction
that is in ended (e) state, but cannot be prepared by the transaction manager.
    An XA transaction to DB2 that ends, but cannot be prepared, is in ended (e) state. Because it is not
considered to be in doubt, the transaction manager cannot recover this transaction. DB2 does not return it in
the list of in doubt transactions. 

 DB2 also does not roll
the transaction back immediately; it waits until all connections to the database are released.
During this period of inaction, the transaction continues to hold locks on the database. If the application server does not disconnect all connections from the database to allow rollback, the ended transaction persists in locking the same database records. If the application attempts to access these locked records, a lock contention exception occurs in DB2.


    		DB2 Version
8.2 is shipped with a sample application that connects to a defined DB2 server and uses the available DB2 APIs to obtain a list
of these particular ended transactions. The application offers a configuration setting that allows us to designate an amount of time after which the application rolls these transactions back. Locate
the sample application in the sqllib/samples/db2xamon.c directory of DB2 v8.2 and run
it.







 
 "DSRA8050W: Unable to find the DataStoreHelper class specified" exception occurs
when trying to use a DB2 Universal Datasource in a mixed release cell



 This error usually occurs when we are using WAS v6.0 or later in
conjunction with a previous version and attempt to create a DB2 Universal Datasource on the previous
version. 




  This can happen because the DB2 Universal Datasource was not available on v5 and previous versions,
but the v6 administrative console allows us to build one.




 To correct this problem: create the datasource on v6.0 or later.





 
 Error "'SYSTEM' is not a valid authorization ID" when trying to access DB2 on a Windows
machine where WAS is also installed



 Error "'SYSTEM' is not a valid authorization ID" message when trying to access DB2 on a Windows
machine where WAS is also installed





 



Symptom
    Problem
    Description
    Recommended response




For a WAS on Windows installation that uses
DB2 as the backend,
we see the following exception in the JVM
log:

java.sql.SQLException: [IBM][CLI 
Driver] SQL0567N "SYSTEM" is 
not a valid authorization ID.  
SQLSTATE=42602 DSRA0010E: 
SQL State = 42602, Error Code
= -567
  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.
DB2Connection.connect
 (Unknown Source)
  at COM.ibm.db2.jdbc.app.
DB2Connection.<init>
 (Unknown Source)
  at COM.ibm.db2.jdbc.app.
DB2ReusableConnection.<init>
 (Unknown Source)
  at COM.ibm.db2.jdbc.
DB2PooledConnection.
getConnection
 (Unknown Source)
  at com.ibm.ws.rsadapter.spi.
WSRdbDataSource.getConnection
(WSRdbDataSource.java:1035)
  at com.ibm.ws.rsadapter.spi.
WSManagedConnectionFactoryImpl.
createManagedConnection
(WSManagedConnectionFactoryImpl
.java:937)
  at com.ibm.ejs.j2c.
poolmanager.FreePool.
createManagedConnectionWithMC
Wrapper(FreePool.java:1502)



    		This exception occurs for configurations in which WAS is a client to the DB2
server. The underlying problem is an authorization conflict between WAS on
Windows
and DB2 that arises
when an application attempts to connect to DB2 without providing a user ID and a password. 
    When a DB2 client and the DB2 database run on the same machine, DB2 allows the client to connect without
a user ID and password. The connection is made under the credentials of the user that owns the client process: in this case, the application server JVM. However, if WAS runs
as a Windows service, and the "Log on as" option is set to "Local System Account", the application server JVM is categorized as a subcomponent of a special Windows user called SYSTEM.
This user is not allowed to connect to DB2, resulting in the previously shown exception.
    We have two options: 


 
    

  • Modify the WAS service to use a Log on as option of This account, and provide an account with permission to connect to DB2. Or


  • Configure the application server to provide credentials on the DB2 connection using container-managed or component-managed authentication.













 
 XAException: XAER_NOTA on XA prepare call in DB2 Universal JDBC Driver type 4 after one phase
transaction rollback



 Symptom




 For applications that use the DB2 Universal JDBC Driver type 4 XA available with DB2 v8.2, a connection might fail and trigger an XAER_NOTA XAException error. The following code block is an example of
this exception:




J2CA0027E: An exception occurred while invoking prepare on an  XA Resource Adapter from dataSource jdbc/SDOSVT, within
 transaction ID {XidImpl: formatId(57415344), gtrid_length(36), bqual_length(54),
  data(000000ff5191398200000001000000296cac5c42fe3c6838631cbaafc8b5a9253b846544
  000000ff5191398200000001000000296cac5c42fe3c6838631cbaafc8b5a9253b8465440000000
  10000000000000000000000000002)}:
javax.transaction.xa.XAException: XAER_NOTA
at com.ibm.db2.jcc.a.xb.a(xb.java:1682)
at com.ibm.db2.jcc.a.xb.a(xb.java:841)
at com.ibm.db2.jcc.a.xb.prepare(xb.java:812)
at com.ibm.ws.rsadapter.spi.WSRdbXaResourceImpl.prepare(WSRdbXaResourceImpl.java:837)
...





 Problem




 If a DB2 Universal
JDBC Driver type 4 XA connection is used in a single-phase transaction, such as a local transaction
with autocommit set to false, and that single-phase transaction is rolled back, the next use of the connection in a two-phase transaction fails on the prepare call. 




 A problem in the DB2 Universal JDBC Driver type 4 XA support causes the XA prepare call to fail. This problem
does not occur if the single-phase transaction is committed, and it does not occur when using the DB2 Universal JDBC
Driver in type 2 mode.




 Solution




 Upgrade to DB2
v8.2 Fix Pack 1, which is equivalent to v8.1 Fix Pack 8. The Universal JDBC Driver XA
that is available with these releases solves the previously described issue for type 4 mode.





 
 java.rmi.MarshalException logged for application client due to incompatibility of JDBC driver
file versions



 Symptom




 For an application that includes an application client, the following error message is displayed
in the client log file of the application server:




java.rmi.MarshalException: CORBA MARSHAL 0x4942f89a No; nested exception is:
org.omg.CORBA.MARSHAL: Unable to read value from underlying bridge : Mismatched serialization
UIDs : Source (Rep. IDRMI:com.ibm.db2.jcc.c.SqlException:63EEE52211DCD763:82CE0C0DA2B0A000)
= 82CE0C0DA2B0A000 whereas Target (Rep. ID
 RMI:com.ibm.db2.jcc.c.SqlException:63EEE52211DCD763:91C6171BC645E41B)
= 91C6171BC645E41B  vmcid: 0x4942f000  minor code: 2202  completed: No 





 Problem




 The db2jcc.jar files on the application client machine and on the application server are from versions of DB2 that are not compatible with each other, or are not compatible with the version of DB2 that functions as the datastore.




 Solution




 Check the db2jcc.jar files on the application client machine, the application server, and your DB2
server. On the client machine and the application server, install files of the same version that is compatible with the DB2 server.





 
 Database failure triggers problematic -99999 exception for applications that use DB2
Universal Driver type 4



 Symptom




 If we use the DB2
Universal Driver type 4 for access to DB2 Network Server, and the database fails, the database server issues a generic -99999 exception in response to every JDBC getConnection request. This exception, which is exemplified in the following code excerpt, can cause unexpected behavior in the applications.




java.sql.SQLException: IO Exception opening socket to 
	server bs8.rchland.ibm.com on port 1527.
The DB2 Server may be down.DSRA0010E: SQL State = null,
	Error Code = -99,999DSRA0010E: SQL State = null,
Error Code = -99,999
at com.ibm.db2.jcc.b.a.<init>(a.java:125)
at com.ibm.db2.jcc.b.b.a(b.java:1011)
at com.ibm.db2.jcc.c.l.<init>(l.java:197)
at com.ibm.db2.jcc.b.b.<init>(b.java:258)
at com.ibm.db2.jcc.DB2PooledConnection.
	<init>(DB2PooledConnection.java:44)
at com.ibm.db2.jcc.DB2ConnectionPoolDataSource.getPooledConnectionX
	(DB2ConnectionPoolDataSource.java:80)
at com.ibm.db2.jcc.DB2ConnectionPoolDataSource.getPooledConnection
	(DB2ConnectionPoolDataSource.java:45)
at com.ibm.ws.rsadapter.DSConfigurationHelper$1.run
	(DSConfigurationHelper.java:945)





 Problem




 When running in type 4 mode, some versions of the DB2 Universal Driver trigger a generic exception for database
failure rather than a specific error code that WAS can map to a stale connection
exception. This problem occurs with versions of the driver associated with DB2 8.1 Fix Pack 6 or Fix
Pack 7, and DB2
8.2.




 Solution




 Upgrade to DB2
v8.2 Fix Pack 1, equivalent to v8.1 Fix Pack 8, which provides a valid error code in
the previously described scenario. WAS maps this error code to a StaleConnectionException, as expected.





 
 Cannot access DB2 on Linux when using the DB2 Universal JDBC Driver



 Symptom




 In the WAS on Linux environment, applications that use the DB2 Universal JDBC Driver to access DB2 on Linux might not
connect with the database. The database server can issue the following exceptions to the application server error log: 


 




 Problem



 The process for configuring DB2 on Linux to work with the Universal JDBC Driver is not complete.



 Solution




 


 





 
 Illegal conversion occurs on any VARCHAR FOR BIT DATA column in a container-managed
persistent bean



 When enterprise beans with container-managed persistent (CMP) types that have any VARCHAR FOR BIT
DATA columns defined on a DB2 table are deployed in the DB2 universal JDBC type 4 driver to persist the data, an SQLException of
illegal conversion is thrown at run time. This exception only occurs when using the DB2 universal JDBC type 4
driver and with the deferPrepares property being set to true. When the deferPrepares property is set
to true, the DB2
universal JDBC type 4 driver uses the standard JDBC data mapping. 




 Currently, the generated deployed code does not follow the standard JDBC specification mapping.
The failure at execution time is because of a problem in the tool that prepared the enterprise beans
for execution. 





 To avoid receiving this exception, choose one of the following options: 


 







 



 





 Related:



  •  Test connection service






  •  Data access problems


  •  Data access problems for Oracle data sources


  •  Data access problems for Microsoft SQL Server data sources


  •  Data access problems for Apache Derby databases


  •  Data access problems for Sybase data sources



  •   Application development, DB2 Universal Database for Linux, UNIX and Windows Version
8



  •   DB2 UDB v8 FixPak 7a for Linux on IA64 reports missing libdb.so.3
library