This example shows as you can implement DTP (Distributed Transaction Processing) with two Resource Managers (PostgreSQL and Oracle Database Server) 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 Oracle, please refer to the official Oracle 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 Oracle technology: it is assumed you already installed and configured the database.
Important
The LIXA software must be configured to support the PostgreSQL and the Oracle Database Server 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-oracle=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
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 “Oracle DMBS Configuration” to set-up a running environment for Oracle Database Server
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/example6_pql_ora.c . tiian@ubuntu:~/tmp$ gcc example6_pql_ora.c $(/opt/lixa/bin/lixa-config -c -f -p -d) \ > -I/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/rdbms/public \ > -L/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib -l clntsh -l nnz10 \ > -I/usr/include/postgresql -lpq -o example6_pql_ora |
or if you are using Oracle 11.2 you will use something like this:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ gcc example6_pql_ora.c $(/opt/lixa/bin/lixa-config -c -f -p -d) \ > -I/u01/app/oracle/product/11.2.0/xe/rdbms/public \ > -L/u01/app/oracle/product/11.2.0/xe/lib -l clntsh -l nnz11 \ > -I/usr/include/postgresql -lpq -o example6_pql_ora |
Verify the executable produced by gcc:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ ldd example6_pql_ora
linux-gate.so.1 => (0xb7731000)
liblixac.so.0 => /opt/lixa/lib/liblixac.so.0 (0xb7716000)
liblixapq.so.0 => /opt/lixa/lib/liblixapq.so.0 (0xb770f000)
libclntsh.so.10.1 => not found
libnnz10.so => not found
libpq.so.5 => /usr/lib/libpq.so.5 (0xb76e1000)
libc.so.6 => /lib/tls/i686/cmov/libc.so.6 (0xb7592000)
libgmodule-2.0.so.0 => /usr/lib/libgmodule-2.0.so.0 (0xb758e000)
libdl.so.2 => /lib/tls/i686/cmov/libdl.so.2 (0xb7589000)
libgthread-2.0.so.0 => /usr/lib/libgthread-2.0.so.0 (0xb7584000)
librt.so.1 => /lib/tls/i686/cmov/librt.so.1 (0xb757b000)
libglib-2.0.so.0 => /usr/lib/libglib-2.0.so.0 (0xb74ca000)
libxml2.so.2 => /usr/lib/libxml2.so.2 (0xb73aa000)
liblixab.so.0 => /opt/lixa/lib/liblixab.so.0 (0xb7395000)
libm.so.6 => /lib/tls/i686/cmov/libm.so.6 (0xb736f000)
libpthread.so.0 => /lib/tls/i686/cmov/libpthread.so.0 (0xb7357000)
libssl.so.0.9.8 => /usr/lib/i686/cmov/libssl.so.0.9.8 (0xb7311000)
libcrypto.so.0.9.8 => /usr/lib/i686/cmov/libcrypto.so.0.9.8 (0xb71cf000)
libkrb5.so.3 => /usr/lib/libkrb5.so.3 (0xb7142000)
libcom_err.so.2 => /lib/libcom_err.so.2 (0xb713e000)
libgssapi_krb5.so.2 => /usr/lib/libgssapi_krb5.so.2 (0xb7115000)
libcrypt.so.1 => /lib/tls/i686/cmov/libcrypt.so.1 (0xb70e3000)
libldap_r-2.4.so.2 => /usr/lib/libldap_r-2.4.so.2 (0xb70a3000)
/lib/ld-linux.so.2 (0xb7732000)
libpcre.so.3 => /usr/lib/libpcre.so.3 (0xb707c000)
libz.so.1 => /usr/lib/libz.so.1 (0xb7066000)
libuuid.so.1 => /lib/libuuid.so.1 (0xb7062000)
libk5crypto.so.3 => /usr/lib/libk5crypto.so.3 (0xb703f000)
libkrb5support.so.0 => /usr/lib/libkrb5support.so.0 (0xb7037000)
libkeyutils.so.1 => /lib/libkeyutils.so.1 (0xb7034000)
libresolv.so.2 => /lib/tls/i686/cmov/libresolv.so.2 (0xb7020000)
liblber-2.4.so.2 => /usr/lib/liblber-2.4.so.2 (0xb7013000)
libsasl2.so.2 => /usr/lib/libsasl2.so.2 (0xb6ffc000)
libgnutls.so.13 => /usr/lib/libgnutls.so.13 (0xb6f86000)
libtasn1.so.3 => /usr/lib/libtasn1.so.3 (0xb6f76000)
libgcrypt.so.11 => /lib/libgcrypt.so.11 (0xb6f28000)
libgpg-error.so.0 => /lib/libgpg-error.so.0 (0xb6f24000)
|
There are four unresolved references that can be fixed setting up
the environment properly; you can fix the environment manually or
using this script supplied by Oracle:
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ echo $LD_LIBRARY_PATH tiian@ubuntu:~/tmp$ . /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh tiian@ubuntu:~/tmp$ echo $LD_LIBRARY_PATH /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib: |
Oracle 11.2 oracle_env.sh does not set
environment variable LD_LIBRARY_PATH and you
should set-up it manually:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ echo $LD_LIBRARY_PATH tiian@ubuntu:~/tmp$ export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/xe/lib tiian@ubuntu:~/tmp$ echo $LD_LIBRARY_PATH /u01/app/oracle/product/11.2.0/xe/lib |
Check again the executable:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ ldd example6_pql_ora linux-gate.so.1 => (0xb770b000)
liblixac.so.0 => /opt/lixa/lib/liblixac.so.0 (0xb76f0000)
liblixapq.so.0 => /opt/lixa/lib/liblixapq.so.0 (0xb76e9000)
libclntsh.so.10.1 => /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/libclntsh.so.10.1 (0xb6934000)
libnnz10.so => /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/libnnz10.so (0xb672f000)
libpq.so.5 => /usr/lib/libpq.so.5 (0xb6702000)
libc.so.6 => /lib/tls/i686/cmov/libc.so.6 (0xb65b3000)
libgmodule-2.0.so.0 => /usr/lib/libgmodule-2.0.so.0 (0xb65af000)
libdl.so.2 => /lib/tls/i686/cmov/libdl.so.2 (0xb65aa000)
libgthread-2.0.so.0 => /usr/lib/libgthread-2.0.so.0 (0xb65a5000)
librt.so.1 => /lib/tls/i686/cmov/librt.so.1 (0xb659c000)
libglib-2.0.so.0 => /usr/lib/libglib-2.0.so.0 (0xb64eb000)
libxml2.so.2 => /usr/lib/libxml2.so.2 (0xb63cb000)
liblixab.so.0 => /opt/lixa/lib/liblixab.so.0 (0xb63b6000)
libm.so.6 => /lib/tls/i686/cmov/libm.so.6 (0xb6390000)
libpthread.so.0 => /lib/tls/i686/cmov/libpthread.so.0 (0xb6378000)
libnsl.so.1 => /lib/tls/i686/cmov/libnsl.so.1 (0xb6360000)
libssl.so.0.9.8 => /usr/lib/i686/cmov/libssl.so.0.9.8 (0xb631a000)
libcrypto.so.0.9.8 => /usr/lib/i686/cmov/libcrypto.so.0.9.8 (0xb61d8000)
libkrb5.so.3 => /usr/lib/libkrb5.so.3 (0xb614a000)
libcom_err.so.2 => /lib/libcom_err.so.2 (0xb6147000)
libgssapi_krb5.so.2 => /usr/lib/libgssapi_krb5.so.2 (0xb611e000)
libcrypt.so.1 => /lib/tls/i686/cmov/libcrypt.so.1 (0xb60ec000)
libldap_r-2.4.so.2 => /usr/lib/libldap_r-2.4.so.2 (0xb60ac000)
/lib/ld-linux.so.2 (0xb770c000)
libpcre.so.3 => /usr/lib/libpcre.so.3 (0xb6084000)
libz.so.1 => /usr/lib/libz.so.1 (0xb606f000)
libuuid.so.1 => /lib/libuuid.so.1 (0xb606b000)
libk5crypto.so.3 => /usr/lib/libk5crypto.so.3 (0xb6048000)
libkrb5support.so.0 => /usr/lib/libkrb5support.so.0 (0xb6040000)
libkeyutils.so.1 => /lib/libkeyutils.so.1 (0xb603c000)
libresolv.so.2 => /lib/tls/i686/cmov/libresolv.so.2 (0xb6029000)
liblber-2.4.so.2 => /usr/lib/liblber-2.4.so.2 (0xb601c000)
libsasl2.so.2 => /usr/lib/libsasl2.so.2 (0xb6005000)
libgnutls.so.13 => /usr/lib/libgnutls.so.13 (0xb5f8f000)
libtasn1.so.3 => /usr/lib/libtasn1.so.3 (0xb5f7e000)
libgcrypt.so.11 => /lib/libgcrypt.so.11 (0xb5f31000)
libgpg-error.so.0 => /lib/libgpg-error.so.0 (0xb5f2d000)
|
Set-up the necessary environment variables:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ echo $LIXA_PROFILE tiian@ubuntu:~/tmp$ export LIXA_PROFILE=PQL_STA_ORA_DYN tiian@ubuntu:~/tmp$ echo $LIXA_PROFILE PQL_STA_ORA_DYN |
The below environment variables were set by the previous sourced script; take a look to them:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ echo $ORACLE_HOME /usr/lib/oracle/xe/app/oracle/product/10.2.0/server tiian@ubuntu:~/tmp$ echo $PATH /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games tiian@ubuntu:~/tmp$ echo $ORACLE_SID XE |
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:
LD_LIBRARY_PATH,
LIXA_PROFILE,
ORACLE_HOME,
ORACLE_SID,
PATH.
We set LIXA_PROFILE to value
“PQL_STA_ORA_DYN”, looking at
/opt/lixa/etc/lixac_conf.xml:
<profile name="PQL_STA_ORA_DYN">
<sttsrvs>
<sttsrv>local_1</sttsrv>
</sttsrvs>
<rsrmgrs>
<rsrmgr>PostgreSQL_stareg</rsrmgr>
<rsrmgr>OracleXE_dynreg</rsrmgr>
</rsrmgrs>
</profile>
the profile references two Resource Managers: “PostgreSQL_stareg” and “OracleXE_dynreg”, looking again at the config file:
<rsrmgr name="OracleXE_dynreg" switch_file="/opt/lixa/lib/switch_oracle_dynreg.so" xa_open_info="Oracle_XA+Acc=P/hr/hr+SesTm=30+LogDir=/tmp+threads=true+DbgFl=7+Loose_Coupling=true" 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 our application will access the resource managers [26] [27].
Verify no (Oracle) trace file exists:
tiian@ubuntu:~/tmp$ ls -la /tmp/xa* ls: cannot access /tmp/xa*: No such file or directory
It is suggested to open three different terminals: the first one connected to “testdb” PostgreSQL database, the second one connected to Oracle database and the third one pointing to the directory where the compiled program example6_pql_ora 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=>
|
| [Oracle terminal session] |
tiian@ubuntu:~$ . /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh tiian@ubuntu:~$ sqlplus "hr/hr" SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 3 21:52:06 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production SQL> |
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ ls -la total 28 drwxr-xr-x 2 tiian tiian 4096 2011-09-20 21:53 . drwxr-xr-x 40 tiian tiian 4096 2011-09-19 20:54 .. -rwxr-xr-x 1 tiian tiian 11219 2011-09-18 21:24 example6_pql_ora -rw-r--r-- 1 tiian tiian 7176 2011-09-18 21:24 example6_pql_ora.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 Oracle table (“COUNTRIES”):
| [Oracle terminal session] |
SQL> select * from COUNTRIES where COUNTRY_ID = 'RS'; no rows selected |
Insert a row in all the tables and check the contents of the tables after the transaction execution:
| [Third terminal session] |
tiian@ubuntu:~/tmp$ ./example6_pql_ora insert Inserting a row in the tables... Oracle INSERT statement executed! |
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) |
| [Oracle terminal session] |
SQL> select * from COUNTRIES where COUNTRY_ID = 'RS'; CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- RS Repubblica San Marino 1 |
With the opposite command you can remove the rows from the tables:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ ./example6_pql_ora delete Deleting a row from the tables... Oracle DELETE statement executed! |
and check the content of the tables again:
| [PostgreSQL terminal session] |
testdb=> select * from AUTHORS; id | last_name | first_name ----+-----------+------------ (0 rows) |
| [Oracle terminal session] |
SQL> select * from COUNTRIES where COUNTRY_ID = 'RS'; no rows selected |
We can verify the dynamic registration behavior of Oracle:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ export LIXA_TRACE_MASK=0x00002000 tiian@ubuntu:~/tmp$ echo $LIXA_TRACE_MASK 0x00002000 tiian@ubuntu:~/tmp$ ./example6_pql_ora insert 2>&1 | grep ax_reg 2011-09-20 22:11:01.669440 [6844/3052865280] ax_reg: rmid=1, xid=0xbfe4cdcc, flags=0x0 2011-09-20 22:11:01.669537 [6844/3052865280] ax_reg: the application program has started a transaction (TX states S3); this XID '1279875137.ce4993340a46495e94f07dbbdd5d0366.6e8ecf5972a778ab648b5950c0f96dd6' will be returned 2011-09-20 22:11:01.669591 [6844/3052865280] ax_reg: sending 153 bytes to the server for step 8 2011-09-20 22:11:01.669639 [6844/3052865280] ax_reg/excp=7/ret_cod=0/errno=0 |
We can check the static registration behavior of Oracle with a different profile:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ export LIXA_PROFILE=PQL_STA_ORA_STA tiian@ubuntu:~/tmp$ echo $LIXA_PROFILE PQL_STA_ORA_STA tiian@ubuntu:~/tmp$ ./example6_pql_ora delete 2>&1 | grep xa_start 2011-09-20 22:22:22.561677 [22658/3052615424] lixa_xa_start [...] 2011-09-20 22:22:22.608794 [22658/3052615424] lixa_xa_start: xa_start_entry(xid, 0, 0x0) = 0 2011-09-20 22:22:22.610649 [22658/3052615424] lixa_xa_start: xa_start_entry(xid, 1, 0x0) = 0 2011-09-20 22:22:22.610694 [22658/3052615424] lixa_xa_start: sending 281 bytes to the server for step 24 2011-09-20 22:22:22.610757 [22658/3052615424] lixa_xa_start/excp=10/ret_cod=0/errno=0 |
and check the content of the tables again:
| [PostgreSQL terminal session] |
testdb=> select * from AUTHORS; id | last_name | first_name ----+-----------+------------ (0 rows) |
| [Oracle terminal session] |
SQL> select * from COUNTRIES where COUNTRY_ID = 'RS'; no rows selected |
The activity of the Oracle database can be analyzed in the trace file:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ ls -la /tmp/xa_NULL09202011.trc -rw-r--r-- 1 tiian tiian 12042 2011-09-20 22:22 /tmp/xa_NULL09202011.trc |
[26]
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].
[27]
The content of xa_open_info string is
described in chapter 15 "Developing Applications with Oracle XA"
of the "Oracle Database Application Developer's Guide"
manual; please refer to the documentation published by
Oracle Corporation for further details.