Scenario: After you upgrade the server to DB2 Universal
Database (DB2 UDB) Version 8.1 FixPak 10 (also known as Version 8.2 FixPak
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 in 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.
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,
indicating 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 the TCP/IP node to connect to DB2 to
avoid the limit of 10 connections. To set up AIX and DB2 loop-back to use the TCP/IP node:
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, usually db2inst1,
and issue the 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 svcenamedb2svc1
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 using the commands:
db2stop
db2start
Catalog a new TCP/IP node:
db2 catalog tcpip node NODENAME remote HOSTNAME server db2svc1
where:
NODENAME
is the name of the new node. You can use local as your node name, providing
it is unique.
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 using the broker service user ID.
To enable WebSphere Message Broker brokers
to use the database, update the ODBC configuration file to add definitions
for the database (if you are using the default odbc.ini file,
this is located at /var/wmqi/odbc/.odbc.ini):
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:
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 Version 6.0 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
Database updates are not committed as expected
Scenario: You have included a Compute, Database, 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 (PA-RISCÂ platform).
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.