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

IBM Db2 error message SQL0443N is issued

Procedure

  • Scenario: After you upgrade your IBM® DB2® server to a new fix pack level, a IBM Db2 error message SQL0443N is issued if you invoke a IBM 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 IBM Db2 instance, run the IBM Db2 CLI command db2 list database directory. For further information, see the IBM Db2 documentation.

IBM 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 IBM 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 IBM Db2, and db is the database name.

IBM Db2 error message SQL0998N is issued on Linux

Procedure

  • Scenario: You are trying to use a globally coordinated message flow with IBM 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.

IBM Db2 error message SQL0998N or SQL1248N is issued

Procedure

  • Scenario: When you try to use a globally coordinated message flow with a IBM 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 IBM Db2 to configure the database and XAResourceManager stanza.

IBM Db2 error message SQL1040N is issued

Procedure

  • Scenario: You are using a IBM Db2 database, and error message BIP2322 is issued with error SQL1040N.
  • Explanation: The following IBM Db2 message indicates that the value of the IBM Db2 database configuration parameter maxappls has been reached:
    "SQL1040N The maximum number of applications is already connected to the database.
    SQLSTATE=57030"

    IBM 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 IBM Db2 database.
    4. Restart the integration nodes.

IBM Db2 error message SQL1224N is issued when you connect to IBM Db2

Procedure

  • Scenario: IBM Db2 error message SQL1224N is issued when you connect to a IBM 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 IBM Db2 databases, to avoid the shared memory limit of 10 connections.
    To set up AIX and IBM Db2 loop-back to use a TCP/IP connection:
    1. Configure IBM Db2 to use TCP/IP, and to start the TCP/IP listener.
      On the database server machine, log in as the IBM Db2 instance owner, typically db2inst1, and issue the following commands:
      db2set DB2COMM=tcpip
      db2stop
      db2start
    2. If the IBM Db2 connection port is not defined in /etc/services, edit the services file to add the IBM 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 IBM Db2 configuration; for example:
      db2 update dbm cfg using svcename db2svc1 
      where db2svc1 is the name of the IBM 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 IBM 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 IBM 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 IBM 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.

IBM Db2 or ODBC error messages are issued on z/OS

Procedure

  • Scenario: IBM 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 IBM 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 IBM 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 integration node 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 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 IBM 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 IBM Db2 databases

Procedure

  • Scenario: You want to use XA with one or more IBM 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 WebSphere® MQ Explorer.

The db2swit file that is needed for XA with IBM 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 Integration Bus provides a prebuilt switch load file that is used for XA with IBM Db2. If this file cannot be loaded, XA transactions cannot take place.
  • Solution: There are 2 reasons why this situation might arise:
    1. IBM Integration Bus and IBM MQ might not have been correctly configured. To establish correct configuration, refer to the instructions in Configuring global coordination with IBM Db2.
    2. The switch load file, db2swit, that is provided with IBM Integration Bus might not be compatible with the version of IBM 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 Integration Bus 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 IBM 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 is issued when you access IBM Db2 on z/OS

Procedure

  • Scenario: You are running a message flow in which a node attempts to access a table on a IBM 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 IBM 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

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.

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

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

Procedure

  • Scenario: You have configured a queue manager for XA coordination with IBM 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 IBM 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 IBM Db2 database on Windows when you are using a sample

Procedure

  • Scenario: You are running a sample, and you are trying to remove a IBM 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 IBM Db2 Control Center to perform a query, a connection is opened against the database. This connection stays open until the IBM Db2 Control Center is closed, at which point the connection is ended.
  • Solution: Close the IBM Db2 Control Center application, and try to run the sample again.

IBM Db2 error message SQL7008N is issued

Procedure

  • Scenario: You are using IBM 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 IBM Db2 command:
      db2 update cli cfg for section <db_alias> using TxnIsolation 32

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

Procedure

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