Resolving problems when using databases

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

Before you begin

Procedure

Db2 error message SQL0443N is issued

Procedure

  • 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\.

    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

Procedure

  • 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.
    Linux platformUNIX platformOn 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.
    Windows platformOn 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

Procedure

  • 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

Procedure

  • 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

Procedure

  • 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 integration nodes 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 integration nodes.

Db2 error message SQL1224N is issued when you connect to Db2

Procedure

  • 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 integration node is running.
    9. Update the ODBC configuration file for each integration node 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/db2o.o
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 integration node, and test the flows.

You do not know how many database connections an integration node requires

Procedure

  • Scenario: You do not know how many database connections to set up for your integration node.
  • Solution: Determine the number of database connections required by an integration node 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 integration node are:

    • Five are required by internal integration node 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

Procedure

  • Scenario: You want to use XA with one or more Db2 databases.
  • Solution: Ensure that your queue manager is configured to use ThreadOfControl=THREAD.
    • Linux platformUNIX platformOn Linux and UNIX systems, configure this parameter in the XAResourceManager stanza in the file qm.ini for the integration node queue manager.
    • Windows platformOn Windows systems, configure this parameter in IBM MQ Explorer.

The db2swit file that is needed for XA with Db2 databases cannot be loaded

Procedure

  • Scenario: Error messages are present in the IBM MQ queue manager error logs indicating that the db2swit file cannot be loaded.
  • Explanation: IBM App Connect Enterprise provides a prebuilt switch load file that is used for XA with Db2. If this file cannot be loaded, XA transactions cannot take place.
  • Solution: There are 2 reasons why this situation might arise:
    1. IBM App Connect Enterprise and IBM MQ might not have been correctly configured. To establish correct configuration, refer to the instructions in Configuring global coordination with Db2.
    2. The switch load file, db2swit, that is provided with IBM App Connect Enterprise might not be compatible with the version of Db2 that you are running. If this is the case, you must build your own version of the db2swit file. For instructions on building your own db2swit file, refer to the sample/xatm/readme.db2 file in your IBM App Connect Enterprise installation.

XA coordination fails if the database restarts while the integration node is running

Procedure

  • 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 integration node is still running.
  • Solution: Stop and restart the integration node if the database goes down, or is brought down for a scheduled maintenance.

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

Procedure

  • 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 integration node 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 integration node.

    For more information, see Running database setup scripts before starting an integration node.

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

Procedure

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

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

Procedure

  • Scenario: Integration node 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]
    or 
    /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 integration node 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

Procedure

  • 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 integration node 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 integration node 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

Procedure

  • 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 integration node holds

Procedure

  • Scenario: You want to list the database connections that the integration node holds.
  • Solution: The integration node 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

Procedure

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

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

Procedure

  • 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 IBM 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 IBM 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 IBM MQ Services user ID is a member of the group DB2USERS, stop the IBM 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

Procedure

  • 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

Procedure

  • 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