CheatSheet: Installing and Configuring Oracle 10g Database with WebSphere Message Broker (Windows Platform)

By default, webSphere Message Broker uses Derby or DB2 as the broker database; and how to configure Oracle database is not very clear in the documentation. In this cheatsheet, the step-by-step instruction will be given.

1. Create an Oracle table space (TS_WSBKR).
2. Create an Oracle user (WSBKR), and grant unlimited usage on TS_WSBKR to the user.
3. Grant database previlege to the broker database user:

GRANT CREATE SESSION TO WSBKR;
GRANT CREATE TABLE TO WSBKR;

4. Define a DSN for ODBC Connection
4.1 Go to "System DSN" definition tab though "ODBC data source administrator" in windows administrative tools or Oracle menu.
4.2 Create a new datasource using driver:
MQSeries DataDirect Technologies 5.00 32-BIT Sybase Wire Protocol
4.3 Enter the DSN name (DSN_WSBKR), description, and server name (in this case, use orcl, which is a sample Oracle instance), and use 10g client.
4.4 (Optional) In the Advanced tab, Select Enable SQLDescribeParam. Select Procedure Returns Results, which results in the Windows registry a string value called ProcedureRetResults with the value 1 being created.
4.5 Test the connection using WSBKR account and click "OK" button to create the DSN.
4.6 Start Windows regedit, and locate the following value:

HKEY_LOCAL_MACHINE
SOFTWARE
ODBC
ODBC.INI
DSN_WSBKR

Add a String value to the key DSN_WSBKR, which is called "WorkArounds" with the value 536870912.
5. Create an MQ queue manager for the broker (WSBKR_QM).
6. Create a broker to populate the database. When you create a broker, if the WebSphere MQ queue manager does not already exist, the queue manager is automatically created. The broker database must already exist but the tables in which the broker stores its internal data are created automatically when the first broker to use that database is created. Subsequent brokers that you create specifying the same database and database user ID share these tables. An example is like this: C:\Program Files\IBM\MQSI\6.1>mqsicreatebroker WSMSGBROKER -i yelei -a pppassword -q WSBKR_QM -n DSN_WSBKR -u WSBKR -p pppassword.

Note the default broker creation wizard provided by websphere message broker toolkit only creates broker database. In order to store business data, user database should also be created.

Using Derby database with WebSphere Message Broker 6.1

WebSphere provides built-in commands for the usage of the embedded Derby database.
mqsicreatedb can be used to create database.
The first time a Derby database is created, a Windows service called IBM® MQSeries® Broker DatabaseInstanceMgr6 is created and started. This service is required in order to access Derby databases. This service can be started or stopped by the mqsistart and mqsistop commands, and automatically starts when Windows is started, if necessary. The service is deleted when the last Derby database is deleted. At most one Database Instance manager Windows service exists, even if you install WebSphere® Message Broker more than once on your Windows computer (multiple installed instances).
The database commands affect all the databases created in any installed instance on your Windows computer, regardless of the instance under which they are created. For example, the mqsilist DatabaseInstanceMgr6 command lists all the databases that have been created using the mqsicreatedb command on this Windows computer. Use the mqsichangedbimgr command to change the user name and password under which the Database Instance manager Windows service is run. Run this command only if passwords change or if user names are updated after the initial installation and configuration. For more information, see Using Derby databases on Windows.

Several pieces of code regarding Java and MQ

Here are some sources of sample code regarding Java & webshpere MQ:
1. http://www-304.ibm.com/jct09002c/isv/tech/sampmq.html
2. http://www.capitalware.biz/mq_code_java.html

Basic Websphere MQ Command

Lately I've been involved in solving a MQ problem. Not really familiar with this product, so I'll put down some basic commands here for later reference.
(1) Create a queue manager. Example:
crtmqm -q yelei.queue.manager
(2) Start the queue manager. Example:
strmqm yelei.queue.manager
(3) Launch MQSC console:
runmqsc
(4) Define a local queue in the console:
define qlocal yelei.queue
(5) Exit MQSC:
end
(6) Put a message on the queue. Example:
amqsput yelei.queue
(7) Get a message from the queue. Example:
amqsget yelei.queue