Installing and using the DB2 WebSphere MQ functions

The DB2 WebSphere® MQ functions are available in DB2® Version 9.1 for Linux®, UNIX, and Windows or later as user-defined functions. By using these functions, users can access the WebSphere MQ queues from DB2 database objects.

Before you begin

Install DB2.
Install WebSphere MQ Server Version 6 or a later edition on the same machine as the DB2 server.
Ensure that the owner of the DB2 instance and the owner of the db2fmp process are in the mqm group.
On Solaris, open a command prompt, and type: ulimit -n 1024. This command sets the limit for the number of files that are opened and allows you to create a queue manager for Solaris.
If you use the WebSphere MQ user defined functions with the db2mq1c schema and you want to enable the functions for transactional context, you must issue the following command:
```
update dbm cfg using federated yes
```
If you choose not to use the DB2 default queue manager and queue, create the message queues and WebSphere MQ objects by using the WebSphere MQ script commands (MQSC) that are provided by WebSphere MQ and DB2.
Edit the queue information in the amtsamp.tst and amtsdfts.tst sample files to help you create the appropriate objects.
Define the queues for the target queue Manager with the runmqsc command, as in the following example:
```
runmqsc QMName <amtsamp.tst
```
You can use the message queues and objects in SQL statements only after they are created.

About this task

You use the commands, enable_MQFunctions and disable_MQFunctions, for transactional and nontransactional MQ user-defined functions. The MQ user-defined functions are defined as a group or set under different schema names. The groups that do not support transactions have the schema db2mq. The groups that do support transactions have the schema db2mq1c. The enable_MQFunctions command with the options that support transactions, allows you to select a set of MQ user-defined functions to install or uninstall for transactional support. If you set a value in the -v parameter, you specify the type of schema that the enable_MQFunctions command creates. The possible values are all, 0pc, or 1pc. If you specify all, then the enablement creates all schemas under user-defined functions (db2mq, db2mq1c).

If you do not specify a value in -v parameter, the enablement defaults to the all option.

Restrictions

The DB2 Version 9.1 MQ transactional functions that exist under the schema db2mq1c do not support CLOB type messages.
The enable utility of the transactional MQ user-defined functions allows only 40 corresponding policies to exist in the MQPolicy table for the queue manager specified with the -q option. The -q option only applies to MQ user defined functions under schema db2mq1c. If you want to use a queue manager other than DB2MQ_DEFAULT_MQM, then you must create the queue manager.
The transactional MQ user-defined functions support only one Queue Manager within a single transaction. The queue manager that you specify in Service and Policy must match. If you leave the Queue Manager blank in the service point, WebSphere MQ defaults to the manager designated by Policy. There is a default set of MQ queues and a default Queue Manager that is normally created during the MQ installation and the enable_MQFunctions processes.
The Queue name and the Queue Manager name in the configuration tables do not support a double byte character set or a Unicode character set.
MQ user-defined functions are not supported on a multiple processor partition (MPP) or DB2 Database Partitioning Feature (DPF) environment.

Procedure

Connect to the database on which you want to enable MQ functions.
For example, if you are working in the SAMPLE database, issue the following command:
```
db2 connect to sample
```
Run the amtsetup.sql script on the database if this is the first time you are trying to enable the database, or if you want to drop the existing configuration tables and start a new configuration. If this is the first time you are enabling the database there are no configuration tables that exist for this database, so you can ignore the errors from the drop table statements.
1. Change your current directory to sqllib/cfg/mq.
2. From the mq directory type db2 -tvf amtsetup.sql.
Configure and enable a database for the WebSphere MQ functions. The enable_MQFunctions command checks that you have properly set up the WebSphere MQ environment. It then installs and creates a default configuration for the WebSphere MQ functions. Then, it enables the specified database with these functions, and confirms that the configuration works.
1. Enable the transactional and nontransactional user-defined functions.
  This example assumes that the user is connected to the SAMPLE database.
```
enable_MQFunctions -n sample -u user1 -p password1
```
2. Create DB2MQ1C functions under the schema DB2MQ1C.
  This example assumes that the user is connected to the SAMPLE database. The value 1pc in the -v parameter means that you want to create the db2mq1c schema:
```
enable_MQFunctions -n sample -u user1 -p password1 -v 1pc
```

Test the MQ functions by using the command line processor on a Windows environment. Issue the following commands after you connect to the currently enabled database:

Command	Description
values DB2MQ1C.MQSEND('a test')	Sends the message `a test` to the DB2MQ_DEFAULT_Q queue. You can use this statement in a DB2 transaction that you can commit or roll back as part of the unit of work.
values DB2MQ1C.MQRECEIVE()	Receives the message back. The statement assumes that you have used some default configuration. You can use this statement in a DB2 transaction that you can commit or roll back as part of the unit of work.