An example with PostgreSQL & IBM DB2

Figure 5.5. Deploy model of an example showing a distributed transaction with PostgreSQL and IBM DB2

Deploy model of an example showing a distributed transaction with PostgreSQL and IBM DB2

This example shows as you can implement DTP (Distributed Transaction Processing) with two Resource Managers (PostgreSQL and IBM DB2) coordinated by the LIXA Transaction Manager. It's strongly suggested you have played with the examples previously shown in this chapter (see Chapter 5, Developing C Application Programs using TX (Transaction Demarcation) interface) before starting this more complex one.

Note

If you did not yet installed the software provided by PostgreSQL, please refer to the official PostgreSQL site to download the software and to pick-up the information necessary to install and configure the database. This manual does not give you information related to PostgreSQL technology: it is assumed you already installed and configured the database.

If you did not yet installed the software provided by IBM, please refer to the official IBM site to download the software and to pick-up the information necessary to install and configure the database. This manual does not give you information related to IBM DB2 technology: it is assumed you already installed and configured the database.

Important

The LIXA software must be configured to support the PostgreSQL and the IBM DB2 resource managers as explained in the section called “Linking third party resource managers”. As a little hint, you should configure LIXA as below:

./configure --with-postgresql-include=/usr/include/postgresql --with-postgresql-lib=/usr/lib \
> --with-ibmdb2=/opt/ibm/db2/V9.7
	

Please don't forget you must compile and install every time you re-configure.

Please follow the instructions explained

Build the client program

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@ubuntu:~$ mkdir tmp
tiian@ubuntu:~$ cd tmp
tiian@ubuntu:~/tmp$ cp /opt/lixa/share/doc/lixa-X.Y.Z/examples/example7_pql_db2.c .
tiian@ubuntu:~/tmp$ gcc example7_pql_db2.c $(/opt/lixa/bin/lixa-config -c -f -p -d) \
> -I/usr/include/postgresql -lpq \
> -I/opt/ibm/db2/V9.7/include -L/opt/ibm/db2/V9.7/lib32 -ldb2 \
> -o example7_pql_db2
	  

Verify the executable produced by gcc:

[Shell terminal session]
tiian@ubuntu:~/tmp$ ldd example7_pql_db2
        linux-gate.so.1 =>  (0xb7780000)
        liblixac.so.0 => /opt/lixa/lib/liblixac.so.0 (0xb7765000)
        liblixapq.so.0 => /opt/lixa/lib/liblixapq.so.0 (0xb775e000)
        libpq.so.5 => /usr/lib/libpq.so.5 (0xb7730000)
        libdb2.so.1 => /usr/lib/libdb2.so.1 (0xb61fe000)
        libc.so.6 => /lib/tls/i686/cmov/libc.so.6 (0xb60af000)
        libgmodule-2.0.so.0 => /usr/lib/libgmodule-2.0.so.0 (0xb60ab000)
        libdl.so.2 => /lib/tls/i686/cmov/libdl.so.2 (0xb60a7000)
        libgthread-2.0.so.0 => /usr/lib/libgthread-2.0.so.0 (0xb60a2000)
        librt.so.1 => /lib/tls/i686/cmov/librt.so.1 (0xb6098000)
        libglib-2.0.so.0 => /usr/lib/libglib-2.0.so.0 (0xb5fe7000)
        libxml2.so.2 => /usr/lib/libxml2.so.2 (0xb5ec7000)
        liblixab.so.0 => /opt/lixa/lib/liblixab.so.0 (0xb5eb2000)
        libm.so.6 => /lib/tls/i686/cmov/libm.so.6 (0xb5e8d000)
        libpthread.so.0 => /lib/tls/i686/cmov/libpthread.so.0 (0xb5e74000)
        libssl.so.0.9.8 => /usr/lib/i686/cmov/libssl.so.0.9.8 (0xb5e2e000)
        libcrypto.so.0.9.8 => /usr/lib/i686/cmov/libcrypto.so.0.9.8 (0xb5cec000)
        libkrb5.so.3 => /usr/lib/libkrb5.so.3 (0xb5c5f000)
        libcom_err.so.2 => /lib/libcom_err.so.2 (0xb5c5c000)
        libgssapi_krb5.so.2 => /usr/lib/libgssapi_krb5.so.2 (0xb5c33000)
        libcrypt.so.1 => /lib/tls/i686/cmov/libcrypt.so.1 (0xb5c00000)
        libldap_r-2.4.so.2 => /usr/lib/libldap_r-2.4.so.2 (0xb5bc0000)
        libpam.so.0 => /lib/libpam.so.0 (0xb5bb6000)
        libdb2dascmn.so.1 => /opt/ibm/db2/V9.7/lib32/libdb2dascmn.so.1 (0xb5b88000)
        libdb2g11n.so.1 => /opt/ibm/db2/V9.7/lib32/libdb2g11n.so.1 (0xb551a000)
        libdb2genreg.so.1 => /opt/ibm/db2/V9.7/lib32/libdb2genreg.so.1 (0xb54da000)
        libdb2install.so.1 => /opt/ibm/db2/V9.7/lib32/libdb2install.so.1 (0xb54cf000)
        libdb2locale.so.1 => /opt/ibm/db2/V9.7/lib32/libdb2locale.so.1 (0xb54bc000)
        libdb2osse.so.1 => /opt/ibm/db2/V9.7/lib32/libdb2osse.so.1 (0xb51b6000)
        libdb2osse_db2.so.1 => /opt/ibm/db2/V9.7/lib32/libdb2osse_db2.so.1 (0xb5146000)
        libdb2trcapi.so.1 => /opt/ibm/db2/V9.7/lib32/libdb2trcapi.so.1 (0xb5133000)
        libstdc++.so.6 => /usr/lib/libstdc++.so.6 (0xb5040000)
        libgcc_s.so.1 => /lib/libgcc_s.so.1 (0xb5035000)
        /lib/ld-linux.so.2 (0xb7781000)
        libpcre.so.3 => /usr/lib/libpcre.so.3 (0xb500d000)
        libz.so.1 => /usr/lib/libz.so.1 (0xb4ff8000)
        libuuid.so.1 => /lib/libuuid.so.1 (0xb4ff4000)
        libk5crypto.so.3 => /usr/lib/libk5crypto.so.3 (0xb4fd1000)
        libkrb5support.so.0 => /usr/lib/libkrb5support.so.0 (0xb4fc9000)
        libkeyutils.so.1 => /lib/libkeyutils.so.1 (0xb4fc5000)
        libresolv.so.2 => /lib/tls/i686/cmov/libresolv.so.2 (0xb4fb2000)
        liblber-2.4.so.2 => /usr/lib/liblber-2.4.so.2 (0xb4fa5000)
        libsasl2.so.2 => /usr/lib/libsasl2.so.2 (0xb4f8e000)
        libgnutls.so.13 => /usr/lib/libgnutls.so.13 (0xb4f18000)
        libtasn1.so.3 => /usr/lib/libtasn1.so.3 (0xb4f07000)
        libgcrypt.so.11 => /lib/libgcrypt.so.11 (0xb4eba000)
        libgpg-error.so.0 => /lib/libgpg-error.so.0 (0xb4eb6000)
	  

Set-up LIXA environment

Set-up the necessary environment variables:

[Shell terminal session]
tiian@ubuntu:~/tmp$ echo $LIXA_PROFILE

tiian@ubuntu:~/tmp$ export LIXA_PROFILE=PQL_STA_DB2_DYN
tiian@ubuntu:~/tmp$ echo $LIXA_PROFILE
PQL_STA_DB2_DYN
	  

It is suggested to set the necessary environment variables in your profile if you are going to execute the programs many times. This is the list of the suggested variables: LIXA_PROFILE, PATH.

Some checks before program execution

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

    <profile name="PQL_STA_DB2_DYN">
      <sttsrvs>
        <sttsrv>local_1</sttsrv>
      </sttsrvs>
      <rsrmgrs>
        <rsrmgr>PostgreSQL_stareg</rsrmgr>
        <rsrmgr>IBMDB2_dynreg</rsrmgr>
      </rsrmgrs>
    </profile>
	

the profile references two Resource Managers: PostgreSQL_stareg and IBMDB2_dynreg, looking again at the config file:

    <rsrmgr name="PostgreSQL_stareg" switch_file="/opt/lixa/lib/switch_postgresql_stareg.so" xa_open_info="dbname=testdb" xa_close_info="" />
    <rsrmgr name="IBMDB2_dynreg" switch_file="/opt/lixa/lib/switch_ibmdb2_dynreg.so" xa_open_info="axlib=/opt/lixa/lib/liblixac.so,db=sample,tpm=lixa" xa_close_info="" />
	

we can discover how our application will access the resource managers [28] [29].

Program execution (dynamic registration)

It is suggested to open three different terminals: the first one connected to TESTDB PostgreSQL database, the second one connected to SAMPLE DB2 database and the third one pointing to the directory where the compiled program example7_pql_db2 lives.

[PostgreSQL terminal session]
tiian@ubuntu:~$ psql testdb
Welcome to psql 8.3.15, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

testdb=>
	  

[IBM DB2 terminal session]
tiian@ubuntu:~$ . /home/db2inst1/sqllib/db2profile
tiian@ubuntu:~$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 9.7.1

You can issue database manager commands and SQL statements from the command
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => connect to SAMPLE

   Database Connection Information

 Database server        = DB2/LINUX 9.7.1
 SQL authorization ID   = TIIAN
 Local database alias   = SAMPLE

db2 =>
	  

[Shell terminal session]
tiian@ubuntu:~/tmp$ ls -la
total 32
drwxr-xr-x  2 tiian tiian  4096 2011-09-25 17:11 .
drwxr-xr-x 40 tiian tiian  4096 2011-09-22 22:24 ..
-rwxr-xr-x  1 tiian tiian 12429 2011-09-22 22:47 example7_pql_db2
-rw-r--r--  1 tiian tiian  7541 2011-09-22 22:46 example7_pql_db2.c
	  

Check the content of the PostgreSQL table (AUTHORS):

[PostgreSQL terminal session]
testdb=> select * from AUTHORS;
 id | last_name | first_name
----+-----------+------------
(0 rows)
	  

Check the content of the DB2 tables (DB2INST1.ORG and DB2INST1.DEPT):

[IBM DB2 terminal session]
db2 => select * from DB2INST1.ORG where DEPTNUMB = 150

DEPTNUMB DEPTNAME       MANAGER DIVISION   LOCATION
-------- -------------- ------- ---------- -------------

  0 record(s) selected.

db2 => select * from DB2INST1.DEPT where DEPTNO='Z99'

DEPTNO DEPTNAME                             MGRNO  ADMRDEPT LOCATION
------ ------------------------------------ ------ -------- ----------------

  0 record(s) selected.
	  

Insert a row in PostgreSQL AUTHORS table and in DB2 ORG table and check the contents of the tables after the transaction execution:

[Third terminal session]
tiian@ubuntu:~/tmp$ . /home/db2inst1/sqllib/db2profile
tiian@ubuntu:~/tmp$ ./example7_pql_db2 insert org
Executing DB2 statement 'INSERT INTO DB2INST1.ORG(DEPTNUMB, DEPTNAME, MANAGER, DIVISION, LOCATION) VALUES(150, 'Europe', 231, 'R&D', 'Mojan')'...
Executing PostgreSQL statement 'INSERT INTO authors VALUES(1, 'Foo', 'Bar');'...	  

Now you can verify the content of the tables after the transaction:

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

[IBM DB2 terminal session]
db2 => select * from DB2INST1.ORG where DEPTNUMB = 150

DEPTNUMB DEPTNAME       MANAGER DIVISION   LOCATION
-------- -------------- ------- ---------- -------------
     150 Europe             231 R&D        Mojan

  1 record(s) selected.
	  

If you try to insert the same row again you can verify an automatic rollback due to an error thrown by the second resource manager:

[Shell terminal session]
tiian@ubuntu:~/tmp$ ./example7_pql_db2 insert org
Executing DB2 statement 'INSERT INTO DB2INST1.ORG(DEPTNUMB, DEPTNAME, MANAGER, DIVISION, LOCATION) VALUES(150, 'Europe', 231, 'R&D', 'Mojan')'...
Executing PostgreSQL statement 'INSERT INTO authors VALUES(1, 'Foo', 'Bar');'...
PostgreSQL error: ERROR:  duplicate key value violates unique constraint "authors_pkey"
	  

DB2 table (ORG) allows multiple rows, PostgreSQL table (AUTHORS) does not allow multiple rows: the error stops the program execution and the resource managers automatically rollback the changes. You can verify there is only one row in the tables:

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

[IBM DB2 terminal session]
db2 => select * from DB2INST1.ORG where DEPTNUMB = 150

DEPTNUMB DEPTNAME       MANAGER DIVISION   LOCATION
-------- -------------- ------- ---------- -------------
     150 Europe             231 R&D        Mojan

  1 record(s) selected.
	  

You can easily verify there are no prepared transactions inside PostgreSQL database:

[PostgreSQL terminal session]
testdb=> select * from pg_prepared_xacts;
 transaction | gid | prepared | owner | database
-------------+-----+----------+-------+----------
(0 rows)
	  

We can verify that DB2 is using dynamic registration:

[Shell terminal session]
tiian@ubuntu:~/tmp$ export LIXA_TRACE_MASK=0x00002000
tiian@ubuntu:~/tmp$ echo $LIXA_TRACE_MASK
0x00002000
tiian@ubuntu:~/tmp$ ./example7_pql_db2 delete org 2>&1 | grep ax_reg
2011-09-25 18:15:38.499702 [13586/3035527472] ax_reg: rmid=1, xid=0xbf9b2630, flags=0x0
2011-09-25 18:15:38.500405 [13586/3035527472] ax_reg: the application program has started a transaction (TX states S3); this XID '1279875137.0565b518222345ba852b4c4a09a660ae.725d0414e912e62f9ef42209ef693f36' will be returned
2011-09-25 18:15:38.500970 [13586/3035527472] ax_reg: sending 153 bytes to the server for step 8
2011-09-25 18:15:38.501415 [13586/3035527472] ax_reg/excp=7/ret_cod=0/errno=0
	  

If you used PQL_STA_DB2_STA profile instead of PQL_STA_DB2_DYN you could switch the DB2 to static behavior.

Note

Combining the previous two examples you can realize a program that access 3 resource managers: PostgreSQL, Oracle and DB2. It is left as an exercise to the reader.



[28] The content of xa_open_info is passed to PQconnectdb function: you can refer to PostgreSQL official documentation to discover what you can pass to this function. Please pay attention the xa_open_info can contain a maximum of 255 characters: this limitation is documented in [XAspec].

[29] The content of xa_open_info string is documented at IBM Infocenter: search the string xa_open string formats in the documentation relevant to your installed version; this is a link http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.2pc.doc/doc/r0005080.html but it may change in the future.