Scenario: After you upgrade
the server to DB2® Universal
Database (DB2 UDB) Version 8.1
Fix Pack 10 (also known as Version 8.2 Fix Pack 3), 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
Scenario: When you run the mqsicreatebroker command, or 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
Scenario: When you use a globally coordinated message
flow with a DB2 8.1 database,
error SQL0998N is issued with Reason Code 09 and Subcode 02.
Solution: Use the following procedure to set the DB2 database manager configuration
parameter TP_MON_NAME to MQ,
and alter the XAOpenString in the qm.ini file
for your queue manager:
To set the TP_MON_NAME parameter,
use the following command from a login that has administrative authority
for the DB2 instance:
db2 update dbm cfg using TP_MON_NAME MQ
Stop and restart the DB2 instance
so that the value is recognized.
To set the AXLIB value in the qm.ini file,
alter the XAOpenString to match the following example:
Scenario: You are using DB2, 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.
If this database is one of the defined broker databases,
implying that a broker thread connection request has failed, the broker
is probably not functioning correctly.
Solution:
Stop all brokers that connect to the affected database.
Increase the value of the maxappls configuration
parameter. Also, check the value of the associated parameter maxagents and increase it in line
with maxappls.
Restart the DB2 database.
DB2 error message SQL1224N
is issued when you connect to DB2
Scenario: DB2 error
message SQL1224N is issued when you connect to DB2. This error indicates that a
database agent could not be started, or was terminated as a result
of a database shutdown or force command.
Solution: On AIX,
use TCP/IP to connect to DB2 to
avoid the shared memory limit of 10 connections. To set
up AIX and DB2 loop-back to use a TCP/IP connection:
Use the mqsideletebroker command to
delete your broker.
Configure DB2 to
use TCP/IP, and to start the TCP/IP listener. On the server machine,
log in as the DB2 instance owner,
typically db2inst1, and issue the following commands:
db2set DB2COMM=tcpip
db2stop
db2start
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
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.
Stop and restart the database by using the following
commands:
db2stop
db2start
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, provided that 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.
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; for
example, when you run the mqsicreatebroker command.
Stop and start DB2:
db2 terminate
db2stop
db2start
Log on with the broker service user ID.
Update the ODBC configuration
file for each broker to add definitions for the database (the default
file odbc.ini file is in directory /var/wmqi/odbc):
At the top of the file, add a definition for the database alias
name:
DBALIAS=IBM DB2 ODBC Driver
Add a new stanza for the database alias:
[DBALIAS]
Driver=INSTHOME/sqllib/lib/libdb2.a
Description=Broker Database Alias
Database=DBALIAS
where INSTHOME is
the path to your DB2 Instance
directory.
Create a new broker using the DBALIAS on
the -n parameter; for example:
Start the broker, and redefine it in the workbench.
Deploy the broker and test the flows.
DB2 error message SQLSTATE=58005 is
issued
Scenario: DB2 error SQLSTATE=58005 is
issued when you use WebSphere® Message
Broker on Linux with DB2 Version 8.1 and Fix Pack 9.
Explanation: This error is issued when the values
of the kernel parameters (msgmni, sem) are too low.
Solution: Increase the kernel parameters (msgmni,
sem).
These kernel parameters should be significantly
above their minimum values and at least the highest of the recommended
values for DB2, WebSphere MQ, and WebSphere Message
Broker.
The following
example shows possible values for a heavy workload environment where
the broker has two execution groups with 200 message flows deployed,
and approximately 45 applications that are using these message flows:
This might be caused by the user ID of the started task not being
authorized to use the current SQL ID. Re-configure the broker and
specify DB2_TABLE_NAME as a valid name, or create
an RACF® group and connect the started task user
ID to this group.
Error message BIP1780 is issued on AIX
Scenario: Error message BIP1780 is
issued on AIX, indicating that
the user ID could not be validated.
Explanation: If you change your AIX operating system password after
you create the broker, when you perform an action such as deploying
a bar file, it fails because the password change causes the connection
to the DB2 database to fail.
Solution: Run the mqsichangebroker command
for the user ID that is used to connect to the database:
mqsichangebrokerbroker -p password
This
allows DB2 to authenticate the
user ID correctly.
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 for maxappls is
40. The associated parameter maxagents also
impacts the current connections.
The connection requirements
for a single message broker are:
Five are required by internal broker threads.
One is required for each publish/subscribe neighbor,
if the topology has been deployed.
One is required for each message flow thread that
contains a Publication node.
One is required for each message flow thread that parses MRM messages.
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).
If you are using SCADA nodes with WebSphere MQ Everyplace®, add a further number of connections
depending on whether thread pooling is being used (check the Use Thread
Pooling property of the SCADAInput node):
If Use Thread Pooling is not selected (the default), add the number
of SCADA clients that will connect to the SCADAInput node.
If Use Thread Pooling is selected, add the value in the Max Threads
property of the SCADAInput node.
The default value is 500.
Connections for neighbors and Publication nodes are required
only if you are using retained publications.
You want to use XA with DB2
Scenario: You want to use XA with DB2.
Solution: If you want to use XA coordination with DB2 V8.1,
ensure that your queue manager is configured to use ThreadOfControl=THREAD.
On Linux or UNIX you can configure this parameter
in the XAResourceManager stanza in qm.ini . On Windows you can configure this parameter
using the WebSphere MQ Explorer or WebSphere MQ Services snap-in depending on
what version of WebSphere MQ you are
using.
XA coordination fails with DB2 V8
Fix Pack 2
Scenario: XA coordination fails with DB2 V8 Fix Pack 2 on AIX, HP-UX, Linux, Solaris, or Windows.
Explanation: If a non-XA coordinated message flow
tries to access a database that was previously being used by an XA
coordinated message flow, failure can occur because DB2 believes that the later flow
is still coordinated from the previous flow.
Solution: This is fixed in DB2 V8 Fix Pack 3 by APAR IY44711.
XA coordination fails if the database restarts while the broker
is running
Scenario: XA global coordination fails and you get
an error similar to the following example, which is from a DB2 user 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 user database if the user database
is restarted while the broker is still running.
Solution: Stop and restart the broker if the user
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 using a message flow in which
a Database node, Compute node, or Filter node attempts to access
a table on a DB2 data-sharing
group other than the one that the broker is using. 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 happens when the DSNACLI plan
has not been bound in the correct way.
Solution: Ensure that the DSNACLI plan is bound
correctly.
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, which 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: Such fixed length character strings
need to 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 desired rows from the table.
There is a memory leak from the Oracle Client Interface on HP-UX 11
Scenario: A memory leak has been observed from the
Oracle Client Interface (OCI) when using Oracle 9i Release 2 (9.2.0.1)
on HP-UX 11.
Solution: This problem has been fixed by Oracle
and the fix is available by upgrading to Oracle 9i Release 2 Database
Server Patch Set 2 for HP9000 Series HP-UX
(64-bit). This has the Oracle patch number of 2761332. To install
the above Oracle patch, you might need to upgrade your Oracle installer
(OUI) to version 2.2.0.18.0 first. The Oracle patch number for this
upgrade is 2878462.
Stored procedure parameter sizes are different in Oracle 9i
and Oracle 10g
Scenario: A stored procedure that contains a char(16)
returns a parameter size of 2000 instead of 16.
Explanation: Oracle 9i and Oracle 10g return different
sizes for parameters in a stored procedure. This is due to differences
in the way that Oracle 9 and Oracle 10 function. A statement from
Oracle about this change of behavior, and a scenario that illustrates
it, are available at the Oracle meta link web site in TAR 4567976.993.
Oracle error message ORA-12500 is issued on Solaris 8 with
Oracle 9
Scenario: Oracle error message ORA-12500 is issued
on Solaris 8 with Oracle 9 when you run workload applications that
are performing database insert, update, or delete operations.
Explanation: This error is issued when the values
of the tuning parameters are too low.
Solution: Increase the following Oracle 9i Release
2 tuning SGA parameters in the initSID.ora file:
JAVA_POOL_SIZE
SHARED_POOL_SIZE
SORT_AREA_SIZE
The following example shows possible increased
values for a workload setup with a single broker that has two execution
groups, each with 92 message flows:
Error messages BIP2731, BIP2321,
and BIP2322 are issued when you use retained publications
with a Sybase database
Scenario: The following error messages are issued
when you use retained publications and a Sybase broker database:
BIP2731 Database statement 'INSERT INTO dbo.BRETAINEDPUBS
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' could not be executed.
BIP2321 Database error: ODBC return code '-1'.
BIP2322 Database error: SQL State '40001'; Native Error Code '1205'.
Text '[SYBASE][ODBC Sybase Driver][SQL Server]Your server command
(family id #0, process id #234) encountered a deadlock situation.
Please re-run your command.'
Explanation: These errors are likely to occur only
when you use retained publications with multiple topics, with a substantial
workload.
Solution: Apply row level locking to one of the
broker's database tables:
At a Sybase command prompt, enter the command:
isql -Uusername -Ppassword
Connect to the broker's database:
use broker DSN
where broker
DSN is the ODBC Data Source Name (DSN) for the broker
database.
Enter the command:
alter table dbo.BRETAINEDPUBS lock datarows
where dbo is
the schema name.
Enter the command:
go
Error message BIP2275 is issued when a large
message flow is deployed to a Sybase broker database
Scenario: You are using Sybase as the broker database
and error messages BIP2275, BIP5009,
and BIP5004 are issued during broker startup.
Explanation: Error messages BIP2275, BIP5009,
and BIP5004 indicate that a message flow XML definition
retrieved from the broker database at startup is invalid XML. This
can be caused by a message flow definition which is greater than 1
MB in length being truncated when it is retrieved from the BROKERRESOURCES
table at broker startup. The reason that it is truncated is that the
default maximum length of data, for Sybase, that can be retrieved
at broker startup from the RESOURCEDATA column (where message flow
definitions are stored) of the BROKERRESOURCES table is 1 MB.
Solution:
Stop all brokers that connect to the affected database.
Add DefaultLongDataBuffLen=2048 to the DSN definition
in the ODBC configuration file(s) (either $ODBCINI or $ODBCINI64,
or both).
Restart the broker.
Error message BIP2322Driver 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
so the database must be created and configured to enable logging.
Solution: Consult your database administrator to
ensure transaction logging has been enabled on the Informix database that the message
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 this
causes the message flow to be rolled back, the commit is not issued
and the database updates are also rolled back. The situations in which
this 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, it might
be possible 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.
The DataDirect trace system is unable to open the ODBC trace
file
Scenario: Commands, such as mqsichangetrace and mqsicreatebroker, fail and
a message is displayed stating that the ODBC trace file cannot be
opened.
Explanation: The DataDirect ODBC file is inaccessible
or its size has exceeded 2 GB and tracing has been turned on.
Solution: To resolve this problem:
Turn off tracing in the file to which the ODBCINI environment
variable points.
Locate the ODBC trace file to which 'TraceFile' in the ODBC stanza
points. If you need to keep the trace, move the ODBC trace file to
another location. If you do not need to keep the trace, delete the
ODBC trace file.
If you still need to gather ODBC trace, reset TRACE=1 in
the odbc.ini file.
Abend when deploying a message flow
Scenario: You are running DB2 version 9 on HP-UX on PA-RISC. You try to deploy a message
flow, but the deployment fails.
Solution: Export environment variable MQSI_SIGNAL_EXCLUSIONS
in the broker environment:
export MQSI_SIGNAL_EXCLUSIONS=10
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 about these.