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, 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:
Set the AXLIB library path to one of the following values, depending
on your operating system:
On Windows:
AXLIB=mqmax
On AIX:
AXLIB=usr/mqm/lib/libmqmax_r.a
On HP-UX:
AXLIB=/opt/mqm/lib/libmqmax_r.sl
On Linux:
AXLIB=/opt/mqm/lib/libmqmax_r.so
On Solaris:
AXLIB=/opt/mqm/lib/libmqmax.so
Stop and restart your broker and queue manager for these changes
to take effect.
DB2 error message SQL1040N is
issued
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
Event 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 Event 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
Event 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 parses MRM messages.
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.
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.
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
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.