IBM Integration Bus, Version 9.0.0.8 Operating Systems: AIX, HP-Itanium, Linux, Solaris, Windows, z/OS

See information about the latest product version

Resolving problems when using databases

Use the advice given here to help you to resolve problems that can arise when using databases.

Before you start:

Read Is there a problem with a database?

DB2 error message SQL0443N is issued

Scenario: After you upgrade your DB2® server to a new fix pack level, a DB2 error message SQL0443N is issued if you invoke a DB2 Call Level Interface (CLI) catalog function, such as SQLTables(), SQLColumns(), or SQLStatistics(). An example of the error message is:
SQL0443N Routine "SYSIBM.SQLTABLES" (specific name "TABLES") has returned an error SQLSTATE with diagnostic text SYSIBM:CLI:-805". SQLSTATE=38553.
Solution: Bind the db2schema.bnd file against each database by entering the following commands at a command prompt:
```
db2 terminate
db2 connect to database-name
db2 bind path\db2schema.bnd blocking all grant public sqlerror continue
db2 terminate
```
where database-name is the name of the database to which the utilities must be bound, and path is the full path name of the directory where the bind files are located. For example, the default location on Windows is C:\Program Files\IBM\SQLLIB\bnd\(on Windows 32-bit editions) or C:\Program Files(x86)\IBM\SQLLIB\bnd\ (on Windows 64-bit editions).
To list all the names of databases for a particular DB2 instance, run the DB2 CLI command db2 list database directory. For further information, see the DB2 documentation.

DB2 error message SQL0805N is issued

Scenario: When a message flow that includes a Database node runs, SQL error SQL0805N NULLID.SQLLF000 is issued.
Solution: Open a DB2 Command Line Processor window and issue a bind command to the database.
On Linux and UNIX systems, enter the commands:
```
connect to db
bind ~/sqllib/bnd/@db2cli.lst grant public CLIPKG 5
connect reset
```
where db is the database name.
On Windows systems, enter the commands:
```
connect to db
bind x:\sqllib\bnd\@db2cli.lst blocking all grant public
connect reset
```
where x: identifies the drive onto which you installed DB2, and db is the database name.

DB2 error message SQL0998N is issued on Linux

Scenario: You are trying to use a globally coordinated message flow with DB2 on Linux and error message SQL0998N is issued with Reason Code 09 and Subcode " ".
Solution: Check that the LD_ASSUME_KERNEL environment variable is not set. If it is set, use the unset command to remove it from your environment and ensure that you modify your profile scripts so that it remains unset.

DB2 error message SQL0998N or SQL1248N is issued

Scenario: When you try to use a globally coordinated message flow with a DB2 database, you get one of the following error messages:
- SQL0998N with Reason Code 09 and Subcode ""
- SQL1248N with a message indicating that the database is not defined with the transaction manager
Solution: Use the instructions in Configuring global coordination with DB2 to configure the database and XAResourceManager stanza.

DB2 error message SQL1040N is issued

Scenario: You are using a DB2 database, and error message BIP2322 is issued with error SQL1040N.
Explanation: The following DB2 message indicates that the value of the DB2 database configuration parameter maxappls has been reached:
```
"SQL1040N The maximum number of applications is already connected to the database.
SQLSTATE=57030"
```
DB2 has rejected the attempt to connect.
Solution:
1. Stop all brokers that connect to the affected database.
2. Increase the value of the maxappls configuration parameter. Also, check the value of the associated parameter maxagents, and increase it in line with maxappls.
3. Restart the DB2 database.
4. Restart the brokers.

DB2 error message SQL1224N is issued when you connect to DB2

Scenario: DB2 error message SQL1224N is issued when you connect to a DB2 database. This error indicates that a database agent could not be started, or was ended because of a database shutdown or force command.
Solution: On AIX®, use TCP/IP to connect to DB2 databases, to avoid the shared memory limit of 10 connections. To set up AIX and DB2 loop-back to use a TCP/IP connection:
1. Configure DB2 to use TCP/IP, and to start the TCP/IP listener. On the database server machine, log in as the DB2 instance owner, typically db2inst1, and issue the following commands:
```
db2set DB2COMM=tcpip
db2stop
db2start
```
2. If the DB2 connection port is not defined in /etc/services, edit the services file to add the DB2 connection and interrupt ports. You must use unique names, and port numbers that are not already defined in the services file; for example:
```
db2svc1     3700/tcp          # DB2 Connection Service
db2isvc1    3701/tcp          # DB2 Interrupt Service
```
3. Update the DB2 configuration; for example:
```
db2 update dbm cfg using svcename db2svc1 
```
  where db2svc1 is the name of the DB2 Connection port service in /etc/services.
  Alternatively, you can specify a port number directly.
4. Stop and restart the database by using the following commands:
```
db2stop
db2start
```
5. Catalog a new TCP/IP connection node:
```
db2 catalog tcpip node NODENAME remote HOSTNAME server db2svc1
```
  where:
  NODENAME
  
  is the name of the new TCP/IP connection node. You can use local as your node name, if it is a unique identifier.
  
  HOSTNAME
  
  is the name of your computer.
  
  db2svc1
  
  is the name of the DB2 connection port service in /etc/services.
  
  Message DB20000I is displayed when the command completes successfully.
6. Catalog the database with a new alias name; for example:
```
db2 catalog database DATABASE as DBALIAS at node NODENAME
```
  where:
  DATABASE
  
  is the physical name of the database.
  
  DBALIAS
  
  is the database alias name that you want to use.
  
  Specify the new alias name in all subsequent references to the local database.
7. Stop and start DB2:
```
db2 terminate
db2stop
db2start
```
8. Log on with the user ID under which the broker is running.
9. Update the ODBC configuration file for each broker to add definitions for the database:
  1. At the top of the file, add a definition for the database alias name:
```
DBALIAS=IBM DB2 ODBC Driver
```
  2. Add a new stanza for the database alias:
```
 [DBALIAS]
Driver=INSTHOME/sqllib/lib/db20.0
Description=Database Alias
Database=DBALIAS
```
    where INSTHOME is the path to your DB2 Instance directory.
10. Update your message flows to specify the alias database name, redeploy the BAR file to the broker, and test the flows.

DB2 or ODBC error messages are issued on z/OS

Scenario: DB2 or ODBC messages are issued on z/OS® indicating one or more of the following errors:
- An exception was caught while issuing the database SQL connect command.
- A database error occurred with an ODBC return code of -1, an SQL state of 58004 and a native error code of -99999.
Solution: If an ODBC message is displayed:
1. Turn ODBC application tracing on to produce the traceodbc file.
2. Locate the traceodbc file, which is written to the /output subdirectory. For example, the full path might be /u/argo/VCP0BRK/output/traceodbc.
3. Go to the bottom of this file and search for previous instances of SQLerror.
Common DB2 problems include:
- ODBC return code -1, SQL state 58004, Native error code -99999
  These codes might be returned for the following reasons:
  - No SQL code. The DB2 subsystem is not started
  - RRS is not started.
- SQLCODE 922.
  The user ID of the started task is not authorized to use plan DSNACLI.
- ODBC return code -1, SLQ state 42503, Native error code -553
  These codes might be returned if the user ID of the started task is not authorized to use the current SQL ID. Reconfigure the broker and specify DB2_TABLE_NAME as a valid name, or create a RACF® group, and connect the started task user ID to this group.

You do not know how many database connections a broker requires

Scenario: You do not know how many database connections to set up for your broker.
Solution: Determine the number of database connections required by a broker for capacity and resource planning. On DB2, the default action taken is to limit the number of concurrent connections to a database to the value of the maxappls configuration parameter; the default value for maxappls is 40. The associated parameter maxagents also affects the current connections.
The connection requirements for a single broker are:
- Five are required by internal broker threads.
- One is required for each database access node to separate ODBC data source names for each message flow thread (that is, if the same DSN is used by a different node, the same connection is used).

You want to use XA with DB2 databases

Scenario: You want to use XA with one or more DB2 databases.
Solution: Ensure that your queue manager is configured to use ThreadOfControl=THREAD.
- On Linux and UNIX systems, configure this parameter in the XAResourceManager stanza in the file qm.ini for the broker queue manager.
- On Windows systems, configure this parameter in WebSphere® MQ Explorer.

XA coordination fails if the database restarts while the broker is running

Scenario: XA global coordination fails, and you get an error like the following example, which is from a DB2 database:

Database error: SQL State '40003'; Native Error Code '-900';  Error Text '[IBM]
[CLI Driver] SQL0900N  The application state  is in error. A database connection 
does not exist.SQLSTATE=08003'.

Explanation: A globally coordinated message flow cannot automatically reconnect to a database if the database is restarted while the broker is still running.
Solution: Stop and restart the broker if the database goes down, or is brought down for a scheduled maintenance.

Error message BIP2322 is issued when you access DB2 on z/OS

Scenario: You are running a message flow in which a node attempts to access a table on a DB2 data-sharing group. If ODBC tracing is turned on, an error message is written to the traceodbc file:

SQLError( hEnv=0, hDbc=0, hStmt=1, pszSqlState=&302f8ecc, pfNativeError=&302f8ec8,
pszErrorMsg=&28f6a6d0, cbErrorMsgMax=1024, pcbErrorMsg=&302f8eb4 )  
SQLError( pszSqlState="51002", pfNativeError=-805, pszErrorMsg="{DB2 for OS/390}
{ODBC Driver}{DSN06011}
 DSNT408I SQLCODE = -805, ERROR:  DBRM OR PACKAGE NAME DSN610GH..DSNCLICS.16877-
          BE5086005F4 NOT FOUND IN PLAN DSNACLI. REASON 02                      
 DSNT418I SQLSTATE   = 51002 SQLSTATE RETURN CODE                               
 DSNT415I SQLERRP    = DSNXEPM SQL PROCEDURE DETECTING ERROR                    
 DSNT416I SQLERRD    = -350  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION          
 DSNT416I SQLERRD    = X'FFFFFEA2'  X'00000000'  X'00000000'  X'FFFFFFFF'       
          X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION

This error is accompanied by the following BIP2322 error message in the syslog:

BIP2322E: DATABASE ERROR: SQL STATE '51002'; NATIVE ERROR CODE '-805'

Explanation: This error occurs when the DSNACLI plan has not been bound in the correct way.
Solution: Ensure that the DSNACLI plan is bound correctly. See Binding a DB2 plan to use data-sharing groups on z/OS for information about how to complete this task.

Error message BIP2322 IM004 is issued when you connect to an Informix database

Scenario: You are using the mqsicvp command to connect to an Informix® database, and you see the following error message.

BIP2322E: Database error: SQL State 'IM004'; Native Error Code '0'; Error Text '[DataDirect][ODBC lib] 
Driver's SQLAllocHandle on SQL_HANDLE_ENV failed'. The error has the following diagnostic information: 
SQL State 'IM004' SQL Native Error Code '0' SQL Error Text '[DataDirect][ODBC lib] Driver's SQLAllocHandle 
on SQL_HANDLE_ENV failed'.

Explanation: This error can be caused when the environment for the database has not been initialized.
Solution: Check the documentation for the client on your broker system for details of the actions that you must take. For example, you might have to specify the following environment variables:
```
export INFORMIXDIR=/installation_directory_of_informix_client_software
export PATH=${INFORMIXDIR}/bin:${PATH}
export INFORMIXSERVER=server_name
export INFORMIXSQLHOSTS=${INFORMIXDIR}/etc/sqlhosts     
export TERMCAP=${INFORMIXDIR}/etc/termcap
export TERM=vt100
export LIBPATH=${INFORMIXDIR}/lib:${INFORMIXDIR}/lib/esql:
               ${INFORMIXDIR}/lib/cli:$LIBPATH
```
where server_name is defined in the file sqlhosts (the required value is typically the machine name), and the location of the file sqlhosts is set up as part of the installation process.

To configure your system to run this setup at the start of every session, add these statements to the login profile of the user that is going to run the broker.

For more information, see Command environment: Linux and UNIX systems.

On Oracle, a database operation fails to return any rows, even though the rows exist

Scenario: You are using Oracle databases in your message flows, and ESQL binds against columns that are declared as data type CHAR, and those parameter markers are referenced in a WHERE clause. The database operation fails to return any rows, even though the rows exist.
Explanation: Fixed-length character strings must be padded with blank characters on Oracle for this type of comparison to succeed.
Solution: Define the CHAR columns as VARCHAR2 columns, or pad the ESQL variable with blank characters to the required column length, so that the comparison locates the required rows from the table.

Broker commands fail when the Oracle 10g Release 2 client runs on Linux on POWER with Red Hat Enterprise Linux Advanced Server V4.0

Scenario: Broker commands fail in an environment where an Oracle 10g Release 2 client runs on Linux on POWER® with Red Hat Enterprise Linux Advanced Server V4.0. Abend files might be created, with contents like the following data:

/opt/mqsi/lib/libCommonServices.so(.ImbAbendSignalHandler+0x10)[0x800017d3c0] 
[0x80022b33f0]
/lib64/tls/libpthread.so.0[0x80cc2339d8]
/lib64/tls/libpthread.so.0[0x80cc230e4c]
/lib64/tls/libpthread.so.0[0x80cc22af54]
bipservice[0x10005e38]
/lib64/tls/libpthread.so.0[0x80cc22a1d8]
/lib64/tls/libc.so.6[0x80cc0dd3e4]

/opt/mqsi/lib/libImbCmdLib.so(._ZN15ImbCreateTables15createAllTablesEv+0x38)
[0x800014c9bc]mqsicreatebroker[0x10023324]mqsicreatebroker[0x1002c0c4]
/opt/mqsi/lib/libImbCmdLib.so(._ZN10ImbCmdBase20processCommandStringEv+0x6c)
[0x80001dcad4]mqsicreatebroker[0x1000d728]/lib64/tls/libc.so.6[0x80cc02423c]
/lib64/tls/libc.so.6[0x80cc0243c4]

Explanation: The Oracle installation library contains copies of libgcc library files. The Oracle user profile adds the directory containing these files to the environment variable LD_LIBRARY_PATH. This action causes the libgcc library files to be found before the system libraries, and leads to the failure of broker commands and the production of abend files.
Solution: Ensure that you add /lib64 to the environment variable LD_LIBRARY_PATH before the Oracle library path. Before you run mqsiprofile, include a string like:
```
/lib64::/usr/lib:/oracle/app/oracle/product/10.2.0/db_1/lib
```
which shows /lib64 preceding the Oracle library directory.

Error message BIP2322 `Driver not capable` is issued when you use an Informix database

Scenario: When you try to access an Informix database from a node in a message flow, the following error message is issued:

BIP2322E: Database error: SQL State 'HYC00'; Native Error Code '-11092';  
Error Text '[Informix][Informix ODBC Driver]Driver not capable.'.

Explanation: The broker uses transaction statements, therefore the database must be created, and configured to enable logging.
Solution: Consult your database administrator to ensure that transaction logging has been enabled on the Informix database that the broker is trying to access. For example, create the database with a buffered log:
```
create database with [buffered] log;
```

Database updates are not committed as expected

Scenario: You have included a Database node, Compute node, or Filter node in a message flow, and you have set the Transaction property to Commit. The message flow has raised an exception and has rolled back, but the database updates have not been committed.
Explanation: When you set Transaction to Commit, the database updates performed by the node are committed when the node completes successfully. If an exception is raised before the node has completed, and causes the message flow to be rolled back, the commit is not issued and the database updates are also rolled back. The conditions under which this situation can occur are:
- The node itself causes an exception to be raised. The commit is never performed.
- The ESQL contains a PROPAGATE statement. This statement does not complete until all processing along the path taken by the propagated message has completed, and control returns to the node. Only then can the commit be performed. If an exception is raised along this path, control is not returned and the database updates are rolled back as part of the message flow.
Solution: Review the operation of the node that performs the database updates. For example, you might be able to split the work between two nodes, with the first updating the database, and the second propagating the output message. Consider changing the ESQL code to process the message in a different way.

You want to list the database connections that the broker holds

Scenario: You want to list the database connections that the broker holds.
Solution: The broker does not have any functionality to list the connections that it has to a database. Use the facilities that your database supplies to list connections. Refer to the documentation for your database to find out how to perform this task.

You want to know whether the password that is set for a database is as expected

Scenario: You want to check the password that is set for a database that is associated with a broker is the password that you expect.
Solution: Use the mqsireportdbparms command with the broker name, user ID, and password. The command reports whether the entered password was correct or not. For more information, see Checking the security credentials that are used by an integration node.

The queue manager finds the XA resource manager is unavailable when configured for XA with DB2 on Windows

Scenario: You have configured a queue manager for XA coordination with DB2 on your Windows computer. When you restart the queue manager, it reports error AMQ7604 in the queue manager error log. All subsequent attempts at XA coordination between WebSphere MQ and DB2 fail.
The error message has the following content, or similar:
```
23/09/2008 15:43:54 - Process(5508.1) User(MUSR_MQADMIN) Program(amqzxma0.exe)
AMQ7604: The XA resource manager 'DB2 MQBankDB database' was not available 
when called for xa_open. 
The queue manager is continuing without this resource manager.
```
Explanation: The user ID that runs the WebSphere MQ Services process amqmsrvn.exe, which has a default value of MUSR_MQADMIN, is running with an access token that does not contain group membership information for the group DB2USERS.
Solution: Check that the WebSphere MQ Services user ID is a member of the group DB2USERS, stop the WebSphere MQ service (for example, by issuing the command net stop "IBM MQSeries"), and all other processes that are running under the same user ID, and then restart these processes. You can restart your computer to stop and restart these processes after you have checked the user ID status, but this action is typically not required.

Error messages are received when you are trying to remove a DB2 database on Windows when you are using a sample

Scenario: You are running a sample, and you are trying to remove a DB2 database on your Windows computer, and you receive a BIP9835E error message with the error code SQLSTATE=57019.

The error message has content like the following data:

BIP9830I: Deleting the DB2 Database <Your database name>.
BIP9835E: The DB2 batch command failed with the error code SQLSTATE=57019.
The database <Your database name> could not be created/deleted. 
The error code SQLSTATE=57019 was returned from the DB2 batch command.

Explanation: If you use the DB2 Control Center to perform a query, a connection is opened against the database. This connection stays open until the DB2 Control Center is closed, at which point the connection is ended.
Solution: Close the DB2 Control Center application, and try to run the sample again.

DB2 error message SQL7008N is issued

Scenario: You are using DB2 and encounter error SQL7008N when updating or inserting into tables on iSeries.
Explanation: SQL7008N is a common error when the tables being accessed on an iSeries server are not being journaled.
Solution: To correct the error, take one of the following steps:
- Journal your tables.
- Change the isolation level to No Commit. You can change the isolation level of the database alias referenced by your ODBC data source to No Commit by using the following DB2 command:
```
db2 update cli cfg for section <db_alias> using TxnIsolation 32
```

SQLCODE -981 is issued when you access DB2 on z/OS

Scenario: You are running a message flow that uses ODBC database interaction. When a commit or rollback is attempted, DB2 reports an error with SQLCODE=-981 and SQLSTATE=57015. An error message is seen similar to: {DB2 FOR OS/390®}{ODBC DRIVER}{DSN09015} DSNT408I SQLCODE = -981, ERROR: THE SQL STATEMENT FAILED BECAUSE THE RRSAF CONNECTION IS NOT IN A STATE THAT ALLOWS SQL OPERATIONS, REASON 00C12219
Explanation: You can choose for ODBC database operations to be committed or rolled back irrespective of the success or failure of the message flow transaction as a whole. This error can be seen if you attempt to use more than one uncoordinated ODBC database connection on a single message flow thread.
Solution: Only one uncoordinated ODBC database connection per thread is supported. Update your message flow to perform ODBC database operations outside of the message flow transaction on only one database. Any number of different databases are supported as part of a coordinated message flow transaction.

au16620_.htm |

Last updated Friday, 21 July 2017