An example with WebSphere MQ, MySQL and PostgreSQL
Prev Chapter 5. Developing C Application Programs using TX (Transaction Demarcation) interface Next

An example with WebSphere MQ, MySQL and PostgreSQL

The example of this paragraph is quite complex because it involves three distinct Resource Managers: WebSphere MQ that's a message oriented middleware, MySQL and PostgreSQL thar are database servers. The proposed example can be easily adapted for WebSphere MQ and MySQL or WebSphere MQ and PostgreSQL removing useless stuff.

It is strongly suggested you have yet tryed the examples described in the section called “An example with MySQL/MariaDB”, in the section called “” and in the section called “An example with WebSphere MQ”.

Figure 5.10. Deploy model of an example with WebSphere MQ, MySQL and PostgreSQL

Deploy model of an example with WebSphere MQ, MySQL and PostgreSQL

Set-up WebSphere MQ, MySQL, PostgreSQL and LIXA environment

Please follow the instructions explained

Build the client program (SRV mode)

Prepare the client (Application Program) using the below commands (gcc command was splitted on several lines using \ to help readability, but you may use a single line):

[Shell terminal session]
[tiian@centos ~]$ mkdir tmp
[tiian@centos ~]$ cd tmp
[tiian@centos tmp]$ cp /opt/lixa/share/doc/lixa-X.Y.Z/examples/example13_wmq_mys_pql.c .
[tiian@centos tmp]$ /opt/lixa/bin/lixa-config -r|grep WebSphereMQ
WebSphereMQ     yes     (SRV)
[tiian@centos tmp]$ gcc example13_wmq_mys_pql.c \
> $(/opt/lixa/bin/lixa-config -c -f -m -p -d) \
> $(mysql_config --include --libs_r) -I/usr/include/postgresql -lpq \
> -I/opt/mqm71/inc -L/opt/mqm71/lib -Wl,-rpath -Wl,/opt/mqm71/lib \
> -lmqm_r -o example13_wmq_mys_pql

Verify the executable produced by gcc:

[Shell terminal session]
[tiian@centos tmp]$ ldd example13_wmq_mys_pql
        linux-gate.so.1 =>  (0x008bc000)
        liblixac.so.0 => /opt/lixa/lib/liblixac.so.0 (0x00fe3000)
        liblixapq.so.0 => /opt/lixa/lib/liblixapq.so.0 (0x00163000)
        liblixamy.so.0 => /opt/lixa/lib/liblixamy.so.0 (0x00684000)
        libmysqlclient_r.so.16 => /usr/lib/mysql/libmysqlclient_r.so.16 (0x0016a000)
        libz.so.1 => /lib/libz.so.1 (0x00ce1000)
        libpthread.so.0 => /lib/libpthread.so.0 (0x00b81000)
        libcrypt.so.1 => /lib/libcrypt.so.1 (0x05144000)
        libnsl.so.1 => /lib/libnsl.so.1 (0x0524e000)
        libm.so.6 => /lib/libm.so.6 (0x00b9e000)
        libssl.so.10 => /usr/lib/libssl.so.10 (0x053f2000)
        libcrypto.so.10 => /usr/lib/libcrypto.so.10 (0x06344000)
        libpq.so.5 => /usr/lib/libpq.so.5 (0x00110000)
        libmqm_r.so => /opt/mqm71/lib/libmqm_r.so (0x00559000)
        libc.so.6 => /lib/libc.so.6 (0x009e8000)
        libgmodule-2.0.so.0 => /lib/libgmodule-2.0.so.0 (0x00da2000)
        libgthread-2.0.so.0 => /lib/libgthread-2.0.so.0 (0x00da8000)
        librt.so.1 => /lib/librt.so.1 (0x00bca000)
        libglib-2.0.so.0 => /lib/libglib-2.0.so.0 (0x00bd5000)
        libxml2.so.2 => /usr/lib/libxml2.so.2 (0x02d3c000)
        liblixab.so.0 => /opt/lixa/lib/liblixab.so.0 (0x00137000)
        /lib/ld-linux.so.2 (0x009c2000)
        libfreebl3.so => /lib/libfreebl3.so (0x051d0000)
        libgssapi_krb5.so.2 => /lib/libgssapi_krb5.so.2 (0x00950000)
        libkrb5.so.3 => /lib/libkrb5.so.3 (0x064d3000)
        libcom_err.so.2 => /lib/libcom_err.so.2 (0x0090e000)
        libk5crypto.so.3 => /lib/libk5crypto.so.3 (0x00922000)
        libresolv.so.2 => /lib/libresolv.so.2 (0x00d86000)
        libdl.so.2 => /lib/libdl.so.2 (0x00b7a000)
        libldap_r-2.4.so.2 => /lib/libldap_r-2.4.so.2 (0x002e1000)
        libmqe_r.so => /opt/mqm71/lib/libmqe_r.so (0xb708f000)
        libuuid.so.1 => /lib/libuuid.so.1 (0x0076f000)
        libkrb5support.so.0 => /lib/libkrb5support.so.0 (0x00915000)
        libkeyutils.so.1 => /lib/libkeyutils.so.1 (0x00991000)
        liblber-2.4.so.2 => /lib/liblber-2.4.so.2 (0x04e6c000)
        libssl3.so => /usr/lib/libssl3.so (0x055de000)
        libsmime3.so => /usr/lib/libsmime3.so (0x05616000)
        libnss3.so => /usr/lib/libnss3.so (0x05469000)
        libnssutil3.so => /usr/lib/libnssutil3.so (0x055b6000)
        libplds4.so => /lib/libplds4.so (0x055aa000)
        libplc4.so => /lib/libplc4.so (0x053a3000)
        libnspr4.so => /lib/libnspr4.so (0x053b2000)
        libsasl2.so.2 => /usr/lib/libsasl2.so.2 (0x057d8000)
        libselinux.so.1 => /lib/libselinux.so.1 (0x00cc0000)

Set-up LIXA environment (SRV mode)

Set-up the LIXA_PROFILE environment variable:

[Shell terminal session]
[tiian@centos tmp]$ echo $LIXA_PROFILE

[tiian@centos tmp]$ export LIXA_PROFILE=MQS_DYN_MYS_STA_PQL_STA
[tiian@centos tmp]$ echo $LIXA_PROFILE
MQS_DYN_MYS_STA_PQL_STA

Some checks before program execution (SRV mode)

We set LIXA_PROFILE to value “MQS_DYN_MYS_STA_PQL_STA”, looking at /opt/lixa/etc/lixac_conf.xml: 

    <profile name="MQS_DYN_MYS_STA_PQL_STA">
      <sttsrvs>
        <sttsrv>local_1</sttsrv>
      </sttsrvs>
      <rsrmgrs>
        <rsrmgr>WSMQ_SRV_dynreg</rsrmgr>
        <rsrmgr>MySQL_stareg</rsrmgr>
        <rsrmgr>PostgreSQL_stareg</rsrmgr>
      </rsrmgrs>
    </profile>

the profile references the three Resource Managers named “WSMQ_SRV_dynreg”, “MySQL_stareg” and “PostgreSQL_stareg”; looking again at the config file: 

    <rsrmgr name="WSMQ_SRV_dynreg" switch_file="/opt/lixa/lib/switch_wsmq_dynreg.so" xa_open_info="axlib=/opt/lixa/lib/liblixac.so,qmname=LIXA,tpm=lixa" xa_close_info="" />
    <rsrmgr name="MySQL_stareg" switch_file="/opt/lixa/lib/switch_mysql_stareg.so" xa_open_info="host=localhost,user=lixa,passwd=,db=lixa,client_flag=0" xa_close_info="" />
    <rsrmgr name="PostgreSQL_stareg" switch_file="/opt/lixa/lib/switch_postgresql_stareg.so" xa_open_info="dbname=testdb" xa_close_info="" />

we can discover how the Resource Managers are configured for XA.

Program execution (SRV mode)

You should open three terminal sessions: one for the execution shell, one to check MySQL content and the last one to check PostgreSQL content.

Check the program is in place, set-up WebSphere MQ environment variables (you have previously set-up LIXA environment) and check the queue is empty:

[Shell terminal session]
[tiian@centos tmp]$ ls -la
total 136
drwxrwxr-x 2 tiian tiian  4096 Mar 18 07:00 .
drwx------ 8 tiian tiian  4096 Mar 17 17:58 ..
-rwxrwxr-x 1 tiian tiian 11879 Mar 18 07:00 example13_wmq_mys_pql
-rw-rw-r-- 1 tiian tiian  7843 Mar 18 06:59 example13_wmq_mys_pql.c
[tiian@centos tmp]$ . /opt/mqm71/bin/setmqenv -s
[tiian@centos tmp]$ /opt/mqm71/samp/bin/amqsget LIXA.QLOCAL LIXA
Sample AMQSGET0 start
no more messages
Sample AMQSGET0 end

Prepare a terminal session for MySQL and check the table is empty:

[MySQL terminal session]
[tiian@centos ~]$ mysql -h localhost -u lixa lixa
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.61 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT * FROM authors;
Empty set (0.00 sec)

Prepare a terminal session for PostgreSQL and check the table is empty:

[PostgreSQL terminal session]
[tiian@centos ~]$ psql testdb
psql (8.4.9)
Type "help" for help.

testdb=> SELECT * FROM authors;
 id | last_name | first_name
----+-----------+------------
(0 rows)

OK, now you are ready to produce one message and insert one row in each database:

[Shell terminal session]
[tiian@centos tmp]$ ./example13_wmq_mys_pql insert
target queue is LIXA.QLOCAL
Message inserted in queue LIXA.QLOCAL: 'Test message for LIXA'
Inserting a row in MySQL table...
Inserting a row in PostgreSQL table...

Check the content of MySQL:

[MySQL terminal session]
mysql> SELECT * FROM authors;
+----+-----------+------------+
| id | last_name | first_name |
+----+-----------+------------+
|  1 | Foo       | Bar        |
+----+-----------+------------+
1 row in set (0.00 sec)

Check the content of PostgreSQL:

[PostgreSQL terminal session]
testdb=> SELECT * FROM authors;
 id | last_name | first_name
----+-----------+------------
  1 | Foo       | Bar
(1 row)

We are leaving the message in the queue and testing the extraction:

[Shell terminal session]
[tiian@centos tmp]$ ./example13_wmq_mys_pql delete
target queue is LIXA.QLOCAL
Message retrieved from queue LIXA.QLOCAL: 'Test message for LIXA'
Deleting a row from MySQL  table...
Deleting a row from PostgreSQL table...

There was one message and the sample program picked it up; check the content of MySQL:

[MySQL terminal session]
mysql> SELECT * FROM authors;
Empty set (0.00 sec)

check the content of PostgreSQL:

[PostgreSQL terminal session]
testdb=> SELECT * FROM authors;
 id | last_name | first_name
----+-----------+------------
(0 rows)

It's time to verify what's happen with tx_rollback() instead of tx_commit(); move the comments in the source code from:

[example13_wmq_mys_pql.c (before)]
[...]
    if (TX_OK != (txrc = tx_commit())) {
        fprintf(stderr, "tx_commit error: %d\n", txrc);
        exit(txrc);
    }

    /*
    if (TX_OK != (txrc = tx_rollback())) {
        fprintf(stderr, "tx_rollback error: %d\n", txrc);
        exit(txrc);
    }
    */
[...]

to:

[example13_wmq_mys_pql.c (after)]
[...]
    /*
    if (TX_OK != (txrc = tx_commit())) {
        fprintf(stderr, "tx_commit error: %d\n", txrc);
        exit(txrc);
    }
    */

    if (TX_OK != (txrc = tx_rollback())) {
        fprintf(stderr, "tx_rollback error: %d\n", txrc);
        exit(txrc);
    }
[...]

and re-compile the program:

[Shell terminal session]
[tiian@centos tmp]$ gcc example13_wmq_mys_pql.c \
> $(/opt/lixa/bin/lixa-config -c -f -m -p -d) \
> $(mysql_config --include --libs_r) -I/usr/include/postgresql -lpq \
> -I/opt/mqm71/inc -L/opt/mqm71/lib -Wl,-rpath -Wl,/opt/mqm71/lib \
> -lmqm_r -o example13_wmq_mys_pql

Now you are ready to insert again message and row, but the rollback will discard the operation:

[Shell terminal session]
[tiian@centos tmp]$ ./example13_wmq_mys_pql insert
target queue is LIXA.QLOCAL
Message inserted in queue LIXA.QLOCAL: 'Test message for LIXA'
Inserting a row in MySQL table...
Inserting a row in PostgreSQL table...

Check the content of MySQL:

[MySQL terminal session]
mysql> SELECT * FROM authors;
Empty set (0.00 sec)

it's empty; check the content of PostgreSQL:

[PostgreSQL terminal session]
testdb=> SELECT * FROM authors;
 id | last_name | first_name
----+-----------+------------
(0 rows)

it's empty; check the content of WebSphere MQ:

[Shell terminal session]
[tiian@centos tmp]$ /opt/mqm71/samp/bin/amqsget LIXA.QLOCAL LIXA
Sample AMQSGET0 start
no more messages
Sample AMQSGET0 end

it's empty. Now you can restore the comment around tx_rollback() instead of tx_commit().

Static registration

If you want to use WebSphere MQ with static registration instead of dynamic registration you will use a different profile:

[Shell terminal session]
[tiian@centos tmp]$ echo $LIXA_PROFILE

[tiian@centos tmp]$ export LIXA_PROFILE=MQS_STA_MYS_STA_PQL_STA
[tiian@centos tmp]$ echo $LIXA_PROFILE
MQS_STA_MYS_STA_PQL_STA

Adapting the example to WebSphere MQ Extended Transactional Client (ETC)

To try this example using Extended Transactional Client (ETC) for WebSphere MQ, you must change these steps in the sequence previously described:

  • configure LIXA software using --with-wsmq=ETC option; clean the previous build (make clean) and install the build (su -c "make install")

  • check the output of /opt/lixa/bin/lixa-config -r command

  • link library mqic_r instead of library mqm_r to program example13_wmq_mys_pql

  • set variable LIXA_PROFILE to MQC_DYN_MYS_STA_PQL_STA (dynamic registration) or to MQC_STA_MYS_STA_PQL_STA (static registration)

  • use utilities amqsgetc, amqsputc instead of amqsget, amqsput

  • set variable MQSERVER to LIXA.CHANNEL/TCP/'127.0.0.1(1414)'

Prev Up Next
An example with WebSphere MQ Home Chapter 6. Developing COBOL Application Programs using TX (Transaction Demarcation) interface