Figure 5.5. 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
in the section called “PostgreSQL Configuration” to set-up a running environment for PostgreSQL server
in the section called “Set-up DB2 environment” to set-up a running environment for IBM DB2
in the section called “Starting the state server (lixad)” to start up the LIXA state server
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 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.
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].
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.