Overview

Skill Level: Any Skill Level

Are you struggling with configurating Db2 and MQ to cooperate ? This is a step by step guide, that helps you to implement these functions in Db2 LUW.

Ingredients

Db2 LUW any edition and any version. The sample commands are based on Db2 Worgroup Edition V11

MQ (before named Websphere MQ).  Any version, any platform.  The samples are based on MQ Server V8

MQ Explorer is an optional tool/software that can be installed to configure MQ from a GUI. (command line interface is default at server)

Step-by-step

  1. Download Software from Partnerworld

    All software (Db2 and MQ) can be downloaded from IBM Partnerworld if you are licensed for these prodcuts.  Download the Version/Edition you need for the platform you want to install each component

  2. Install Db2

    Db2 has to be installed as usual. (see Infocenter Db2 for details)

    Create a server instance and create a database.  No requirements for install – create instance – create database.

    To demonstrate use of nicknames pointing to MQ queues, we activated FEDERATED in the database manager configuration

    UPDATE DBM CFG USING FEDERATED YES

    After change of this setting : execute db2stop-db2start

    This end the installation of Db2

  3. Install MQ

    All detailed information about the installation is also available in the Infocenter for MQ.

    Infocenter MQ

    According the documentation the MQ server has to be installed on the same machine.  The following is based on the same configuration.  For this installtion we used all the defaults. No setting for installation has been changed.

    The downloaded file, is a zipped tar file that has to be unzipped and unpacked 

    The installtion has to be done with a root userid

    gunzip   WS_MQ_V8.0.0.4_LINUX_ON_X86_64_IM.tar.gz

    tar -xvf WS_MQ_V8.0.0.4_LINUX_ON_X86_64_IM.tar

    From within the unpacked directory MQ_server  install the license for MQ

    sh ./mqlicense.sh

    Accept the license : enter 1

    Install MQ :  rpm –Uvh *rpm

    This will create a user mqm belonging to a group mqm – the home_directory for this user will be /var/mqm

    IBM MQ will be installed in /opt/mqm

    A password can be assigned to the user mqm with operating system commands.

    This  ends the installation of IBM MQ

  4. Configure MQ

    from root user 

    add Db2 instance owner to the group the user mqm belongs ( in this  sample : mqm)

    usermod -G mqm dbllud1  (the Db2 instance owner in this sample = dbllud1)

    Display the installation name, assigned by the current installation :

    /opt/mqm/bin/dspmqinst -p “/opt/mqm”   (where /opt/mqm is the installation path)

    Sample output :  InstName:      Installation1

    Set this installation as your primary installation

    /opt/mqm/bin/setmqinst -i -n Installation1 -p /opt/mqm  (where Installation1  = installtion name and /opt/mqm = installation path)

    Sample output : 120 of 120 tasks have been completed successfully.   ‘Installation1’ (/opt/mqm) set as the primary installation.

     

    from mqm user

    add the bin directory from the installation to the environment variable PATH (manually or in the shell profile)

    PATH=$PATH:/opt/mqm/bin

     

    verify the installed version  : dspmqver

    this ends the configuration steps of IBM MQ

  5. Prepare / Configure Db2

    From the Db2 instance owner

    add the lib64 directory from the IBM MQ installion to the environment variable LD_LIBRARY_PATH  (manually or in the shell profile)

    LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/mqm/lib64

    Set the environment variable AMT_DATA_PATH to the IBM MQ installation path (manually or in the shell profile)

    AMT_DATA_PATH=/opt/mqm

    Connect to the database to be configured

    db2 connect to sample

    Change the working directory :  cd ~/sqllib/cfg/mq

    Execute the SQL statements  : db2 –tvf amtsetup.sql

    List the tables created for the schema DB2MQ (should be 5) : db2 list tables for schema DB2MQ

    Now enable the DB2MQ functions in this database :

    enable_MQFunctions -n sample -u dbllud1 -p xxxxxx     ( where  sample=database name – dbllud1=instance owner  – xxxxxx=password of instance owner

    Sample output :

        — Create MQ Functions …

      Validate MQ Functions …

     Validate successfully.

     

     

     

     

     

     

  6. Sample Usage

    Now that the database has been enabled you can try to insert/read messages to the created queue

    see many samples on this page : DB2MQ samples for usage of DB2MQ functions available

    If the database manager setting FEDERATED was activated, there is also a nickname DB2MQ1C.MQ  available that points to this queue.

    You can directly insert/select rows from this nickname that are routed to the MQ queue.

    This ends the sample chapter and the document

3 comments on"Implementing MQ functions in Db2 LUW"

  1. This is On Linux,
    On Windows I couldn’t find the enable_MQFunctions as well as I couldn’t find amtsetup.sql file

    Do you have any Idea if it requires a specific Installation or higher DB2 version?

  2. Guy_Przytula February 23, 2020

    according the info : https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0010964.html
    there is no indication about platform – you should verify that all components are installed..

    • KhalafAllah May 18, 2020

      Yes I know, but it doesn’t exist, possibly it was removed although it is stated as non-platform-specific…
      The whole required artifacts do not exist, I checked even the express edition, & workgroup server edition both do not have these files…
      it only exists with Linux Installation so far from what I found.

Join The Discussion