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 6, Developing COBOL Application Programs using TX (Transaction Demarcation) interface) before starting this more complex one.
This example was developed using the following configuration:
PostgreSQL 9.3.15 (client and server)
Oracle remote configuration with Instant Client 12.1
Oracle Pro*COBOL compiler as supplied by Oracle Instant Client 12.1
GnuCOBOL compiler and runtime as supplied by Ubuntu 14.04
If you were using a different version you would need to adapt some commands to your environment.
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-oracle-lib=/opt/oracle/instantclient_12_1 \ > --with-oracle-include=/opt/oracle/instantclient_12_1/sdk/include \ > --with-postgresql
Please don't forget you must compile and install every time you re-configure.
Prepare the environment following the following steps:
Set-up PostgreSQL environment as explained in the section called “”
Set-up the Oracle environment as explained in the section called “Oracle DMBS Configuration” and adapt the command to your Oracle Database Server version
Configure Oracle listener for remote connection as explained in the section called “Configure Oracle Listener”
Install and configure Oracle Instant Client as explained in the section called “Install and configure Oracle Instant Client”
Start the LIXA state server as explained in the section called “”
Create a shell script file, for example
oracle_env.sh
with some useful environment variables as below:
tiian@ubuntu1404-64:/tmp$ cat oracle_env.sh #!/bin/sh export LD_LIBRARY_PATH=/opt/oracle/instantclient_12_1:$LD_LIBRARY_PATH export PATH=/opt/oracle/instantclient_12_1:/opt/oracle/instantclient_12_1/sdk:$PATH export ORACLE_HOME=/opt/oracle/instantclient_12_1
source it and check the values:
tiian@ubuntu1404-64:/tmp$ . oracle_env.sh tiian@ubuntu1404-64:/tmp$ echo $LD_LIBRARY_PATH /opt/oracle/instantclient_12_1: tiian@ubuntu1404-64:/tmp$ echo $PATH /opt/oracle/instantclient_12_1:/opt/oracle/instantclient_12_1/sdk:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games tiian@ubuntu1404-64:/tmp$ echo $ORACLE_HOME /opt/oracle/instantclient_12_1
set LIXA environment variables:
tiian@ubuntu1404-64:/tmp$ export PATH=$PATH:/opt/lixa/bin tiian@ubuntu1404-64:/tmp$ echo $PATH /opt/oracle/instantclient_12_1:/opt/oracle/instantclient_12_1/sdk:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/opt/lixa/bin tiian@ubuntu1404-64:/tmp$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/lixa/lib tiian@ubuntu1404-64:/tmp$ echo $LD_LIBRARY_PATH /opt/oracle/instantclient_12_1::/opt/lixa/lib
Prepare the client (Application Program) using the below commands:
| [Shell terminal session] |
tiian@ubuntu1404-64:/tmp$ cp /opt/lixa/share/doc/lixa-X.Y.Z/examples/cobol/EXAMPLE6_PQL_ORA.pco . tiian@ubuntu1404-64:/tmp$ procob EXAMPLE6_PQL_ORA.pco Pro*COBOL: Release 12.1.0.2.0 - Production on Sat Jan 21 20:01:52 2017 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. System default option values taken from: /opt/oracle/instantclient_12_1/precomp/admin/pcbcfg.cfg tiian@ubuntu1404-64:/tmp$ export COB_LDFLAGS=-Wl,--no-as-needed tiian@ubuntu1404-64:/tmp$ cobc -x $(lixa-config -f -p) \ > -L/opt/oracle/instantclient_12_1 -lclntsh -lnnz12 \ > EXAMPLE6_PQL_ORA.cob /opt/oracle/instantclient_12_1/cobsqlintf.o |
Verify the executable produced by cobc:
| [Shell terminal session] |
tiian@ubuntu1404-64:/tmp$ ldd EXAMPLE6_PQL_ORA
linux-vdso.so.1 => (0x00007fffdf3fe000)
libcob.so.1 => /usr/lib/libcob.so.1 (0x00007eff28bed000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007eff288e7000)
libgmp.so.10 => /usr/lib/x86_64-linux-gnu/libgmp.so.10 (0x00007eff28672000)
libncurses.so.5 => /lib/x86_64-linux-gnu/libncurses.so.5 (0x00007eff2844f000)
libtinfo.so.5 => /lib/x86_64-linux-gnu/libtinfo.so.5 (0x00007eff28226000)
libdb-5.3.so => /usr/lib/x86_64-linux-gnu/libdb-5.3.so (0x00007eff27e83000)
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007eff27c7f000)
liblixac.so.0 => /opt/lixa/lib/liblixac.so.0 (0x00007eff27a64000)
liblixapq.so.0 => /opt/lixa/lib/liblixapq.so.0 (0x00007eff2785c000)
libclntsh.so.12.1 => /opt/oracle/instantclient_12_1/libclntsh.so.12.1 (0x00007eff2489f000)
libnnz12.so => /opt/oracle/instantclient_12_1/libnnz12.so (0x00007eff24195000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007eff23dcf000)
libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007eff23bb1000)
/lib64/ld-linux-x86-64.so.2 (0x00007eff28e2b000)
libgmodule-2.0.so.0 => /usr/lib/x86_64-linux-gnu/libgmodule-2.0.so.0 (0x00007eff239ac000)
libglib-2.0.so.0 => /lib/x86_64-linux-gnu/libglib-2.0.so.0 (0x00007eff236a4000)
libxml2.so.2 => /usr/lib/x86_64-linux-gnu/libxml2.so.2 (0x00007eff2333d000)
liblixab.so.0 => /opt/lixa/lib/liblixab.so.0 (0x00007eff23123000)
libpq.so.5 => /usr/lib/libpq.so.5 (0x00007eff22ef4000)
libmql1.so => /opt/oracle/instantclient_12_1/libmql1.so (0x00007eff22c7e000)
libipc1.so => /opt/oracle/instantclient_12_1/libipc1.so (0x00007eff228ff000)
libons.so => /opt/oracle/instantclient_12_1/libons.so (0x00007eff226ba000)
libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x00007eff224a0000)
librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007eff22297000)
libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x00007eff22095000)
libclntshcore.so.12.1 => /opt/oracle/instantclient_12_1/libclntshcore.so.12.1 (0x00007eff21b23000)
libpcre.so.3 => /lib/x86_64-linux-gnu/libpcre.so.3 (0x00007eff218e4000)
libz.so.1 => /lib/x86_64-linux-gnu/libz.so.1 (0x00007eff216cb000)
liblzma.so.5 => /lib/x86_64-linux-gnu/liblzma.so.5 (0x00007eff214a9000)
libuuid.so.1 => /lib/x86_64-linux-gnu/libuuid.so.1 (0x00007eff212a3000)
libssl.so.1.0.0 => /lib/x86_64-linux-gnu/libssl.so.1.0.0 (0x00007eff21044000)
libcrypto.so.1.0.0 => /lib/x86_64-linux-gnu/libcrypto.so.1.0.0 (0x00007eff20c68000)
libkrb5.so.3 => /usr/lib/x86_64-linux-gnu/libkrb5.so.3 (0x00007eff2099c000)
libcom_err.so.2 => /lib/x86_64-linux-gnu/libcom_err.so.2 (0x00007eff20798000)
libgssapi_krb5.so.2 => /usr/lib/x86_64-linux-gnu/libgssapi_krb5.so.2 (0x00007eff20551000)
libldap_r-2.4.so.2 => /usr/lib/x86_64-linux-gnu/libldap_r-2.4.so.2 (0x00007eff202ff000)
libk5crypto.so.3 => /usr/lib/x86_64-linux-gnu/libk5crypto.so.3 (0x00007eff200d0000)
libkrb5support.so.0 => /usr/lib/x86_64-linux-gnu/libkrb5support.so.0 (0x00007eff1fec4000)
libkeyutils.so.1 => /lib/x86_64-linux-gnu/libkeyutils.so.1 (0x00007eff1fcc0000)
libresolv.so.2 => /lib/x86_64-linux-gnu/libresolv.so.2 (0x00007eff1faa5000)
liblber-2.4.so.2 => /usr/lib/x86_64-linux-gnu/liblber-2.4.so.2 (0x00007eff1f895000)
libsasl2.so.2 => /usr/lib/x86_64-linux-gnu/libsasl2.so.2 (0x00007eff1f67a000)
libgssapi.so.3 => /usr/lib/x86_64-linux-gnu/libgssapi.so.3 (0x00007eff1f43c000)
libgnutls.so.26 => /usr/lib/x86_64-linux-gnu/libgnutls.so.26 (0x00007eff1f17d000)
libgcrypt.so.11 => /lib/x86_64-linux-gnu/libgcrypt.so.11 (0x00007eff1eefd000)
libheimntlm.so.0 => /usr/lib/x86_64-linux-gnu/libheimntlm.so.0 (0x00007eff1ecf3000)
libkrb5.so.26 => /usr/lib/x86_64-linux-gnu/libkrb5.so.26 (0x00007eff1ea6b000)
libasn1.so.8 => /usr/lib/x86_64-linux-gnu/libasn1.so.8 (0x00007eff1e7ca000)
libhcrypto.so.4 => /usr/lib/x86_64-linux-gnu/libhcrypto.so.4 (0x00007eff1e596000)
libroken.so.18 => /usr/lib/x86_64-linux-gnu/libroken.so.18 (0x00007eff1e381000)
libtasn1.so.6 => /usr/lib/x86_64-linux-gnu/libtasn1.so.6 (0x00007eff1e16d000)
libp11-kit.so.0 => /usr/lib/x86_64-linux-gnu/libp11-kit.so.0 (0x00007eff1df2a000)
libgpg-error.so.0 => /lib/x86_64-linux-gnu/libgpg-error.so.0 (0x00007eff1dd25000)
libwind.so.0 => /usr/lib/x86_64-linux-gnu/libwind.so.0 (0x00007eff1dafc000)
libheimbase.so.1 => /usr/lib/x86_64-linux-gnu/libheimbase.so.1 (0x00007eff1d8ed000)
libhx509.so.5 => /usr/lib/x86_64-linux-gnu/libhx509.so.5 (0x00007eff1d6a4000)
libsqlite3.so.0 => /usr/lib/x86_64-linux-gnu/libsqlite3.so.0 (0x00007eff1d3eb000)
libcrypt.so.1 => /lib/x86_64-linux-gnu/libcrypt.so.1 (0x00007eff1d1b1000)
libffi.so.6 => /usr/lib/x86_64-linux-gnu/libffi.so.6 (0x00007eff1cfa9000)
|
Set-up the LIXA_PROFILE environment variable:
| [Shell terminal session] |
tiian@ubuntu1404-64:/tmp$ export LIXA_PROFILE=PQL_STA_ORAIC_STA tiian@ubuntu1404-64:/tmp$ echo $LIXA_PROFILE PQL_STA_ORAIC_STA |
See the section called “Some checks before program execution” for additional details on the profile.
This example behaves has the sum of
EXAMPLE2_ORA explained in
the section called “An example with Oracle Pro*COBOL” and of
EXAMPLE5_PQL explained in
the section called “An example with PostgreSQL”:
the program tries to insert a row inside the Oracle database and a
row inside the PostgreSQL database.
The following paragraphs show the type of behavior that you try.
Both databases can insert the row:
| [Shell terminal session] |
tiian@ubuntu1404-64:/tmp$ ./EXAMPLE6_PQL_ORA INSERT Executing EXAMPLE6_PQL_ORA Inserting a row in the table... Status: +0000000000 PQexec INSERT Status: +0000000001 Execution terminated! |
Both databases can delete the row:
| [Shell terminal session] |
tiian@ubuntu1404-64:/tmp$ ./EXAMPLE6_PQL_ORA DELETE Executing EXAMPLE6_PQL_ORA Deleting a row from the table... Status: +0000000000 PQexec DELETE Status: +0000000001 Execution terminated! |
Oracle database can not insert the row:
| [Shell terminal session] |
tiian@ubuntu1404-64:/tmp$ ./EXAMPLE6_PQL_ORA INSERT
Executing EXAMPLE6_PQL_ORA
Inserting a row in the table...
Status: +0000000000
Error reported by Oracle: ORA-00001: unique constraint (HR.COUNTRY_C_ID_PK) violated
Rolling back due to SQL errors...
TXROLLBACK returned value +000000000
TXCLOSE returned value +000000000
|
and PostgreSQL has not inserted its row due to TXROLLBACK:
| [Shell terminal session] |
tiian@ubuntu1404-64:~$ psql testdb psql (9.3.15) Type "help" for help. testdb=> select * from AUTHORS; id | last_name | first_name ----+-----------+------------ (0 rows) |
PostgreSQL database can not insert the row:
| [Shell terminal session] |
tiian@ubuntu1404-64:/tmp$ ./EXAMPLE6_PQL_ORA INSERT Executing EXAMPLE6_PQL_ORA Inserting a row in the table... Status: +0000000000 PQexec INSERT Error in PQexec statement: ERROR: duplicate key value violates unique constraint "authors_pkey" DETAIL: Key (id)=(1) already exists. Rolling back due to SQL errors... TXROLLBACK returned value +000000000 TXCLOSE returned value +000000000 |
and Oracle has not inserted its row due to TXROLLBACK:
| [Shell terminal session] |
tiian@ubuntu1404-64:~$ sqlplus hr/hr@lixa_ora_db SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 21 21:44:23 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Sat Jan 21 2017 21:42:12 +01:00 Connected to: Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production SQL> select * from COUNTRIES where COUNTRY_ID = 'RS'; no rows selected |