helpinghand
search
needassistance
 
How To
Distributed Transactions with Mimer SQL and BEA Tuxedo
Categories: Programming Examples, Tools and Interfaces
Introduction

The integration of Mimer SQL with BEA Tuxedo allows access to Mimer SQL database data from BEA Tuxedo services. Changes to several different Mimer SQL databases can be grouped together in distributed transactions. This allows data changes to be made in either none, or all of the systems accessed by a transaction. In addition, changes in Mimer SQL can be coordinated with changes made in database management systems from other vendors.
Description

The integration of Mimer SQL and BEA Tuxedo is performed through a standard for distributed database access called XA. The steps taken below allow BEA Tuxedo to access the XA interface of the Mimer SQL database handler.

The programmer can then write Tuxedo services using Mimer Embedded SQL to access the database.
Environment

The example below is from a Windows environment.

Use Mimer SQL version 9.1.3 or later on your Windows system. Make sure you select "Development and sample files" during the Mimer SQL installation as this includes files necessary for the integration with BEA Tuxedo.

This howto has been verified with BEA Tuxedo version 8.0. It is likely that it will work with other version of this product as well.
Example

A number of steps are required for Mimer SQL and BEA Tuxedo to interface. The first two steps below are only done once and allow Tuxedo to access Mimer SQL. The remaining steps is an example of a Tuxedo service which access a Mimer SQL database.

1) Update the RM file. Start by creating a Transaction Management Server executable. This is done by adding information about Mimer SQL to the description file for different resource managers. This file is called RM and is located in the UDATAOBJ directory in the Tuxedo installation tree. Add the following line to the RM file with a standard text editor:

MimerSQL;mimsqlxa;mimsql32.lib mimsqlxa.obj

The fields are, respectively:

resource manager name; name of the xa_switch_t struct; library modules

The xa_switch_t is used by Tuxedo to locate all the necessary routines in Mimer SQL for performing distributed transactions.

2) Build the TM server. Build a Mimer SQL specific transaction management server (TMS) executable with the help of the RM file modified in step '1)' above:

%TUXDIR%\bin\buildtms –r MimerSQL –o %TUXDIR%\bin\TMS_MIMER.EXE

Where %TUXDIR% is the Tuxedo installation directory.

3) Write an access service. At this point you want to add one or more services to Tuxedo to access the Mimer SQL database. These applications are written with Embedded SQL for C or C++. The following is a short sample program that inserts a row into a table each time the service is invoked. The program is adapted from the Tuxedo sample program simpserv.c located in the Tuxedo catalog samples\atmi\simpapp. The program is renamed to an extension .ec, which stands for Embedded C, to allow SQL statements to be added to the file and processed correctly.

Simpserv.ec:

/*
* This is the standard Tuxedo example server simpapp, extended
* with the simplest possible logic to save a row in the Mimer
* database.
*/
#include <string.h>
#include <stdlib.h>
#include <stdio.h>
#include <ctype.h>
#include <atmi.h> /* TUXEDO Header File */
#include <userlog.h> /* TUXEDO Header File */
/*
* tpsvrinit is executed when a server is booted, before it begins
* processing requests. It is not necessary to have this function.
* Also available is tpsvrdone (not used in this example), which is
* called at server shutdown time.
*/
#if defined(__STDC__) || defined(__cplusplus)
tpsvrinit(int argc, char *argv[])
#else
tpsvrinit(argc, argv)
int argc;
char **argv;
#endif
{
/*
* Some compilers warn if argc and argv aren't used.
*/
argc = argc;
argv = argv;
/*
* userlog writes to the central TUXEDO message log
*/
(void)userlog("Welcome to the simple server");
/*
* Connect to the database
*/
if (tpopen() == -1) {
(void)userlog("Failed to open database due to %s \n", tpstrerror(tperrno));
argv = argv; argc = argc; /* unused */
return(-1);
}
return(0);
}
/*
* This function performs the actual service requested by the client.
* Its argument is a structure containing among other things a pointer
* to the data buffer, and the length of the data buffer.
*/
#ifdef __cplusplus
extern "C"
#endif
void
#if defined(__STDC__) || defined(__cplusplus)
TOUPPER(TPSVCINFO *rqst)
#else
TOUPPER(rqst)
TPSVCINFO *rqst;
#endif
{
int i;
int transaction_started_by_me;
/*
* The following section contains all variables referenced by Embedded
* SQL
*/
exec sql BEGIN DECLARE SECTION;
char col1[20];
int SQLCODE;
exec sql END DECLARE SECTION;
/*
* Check if a distributed transaction is started
*/
transaction_started_by_me = tpgetlev();
if (transaction_started_by_me == 0) {
/*
* Start a distributed transaction
*/
if (tpbegin(5,0) == -1) {
(void)userlog("tpbegin: failed due to %s \n", tpstrerror(tperrno));
goto end;
}
}
/*
* Save the in string in the database
*/

(void) strncpy(col1, rqst->data, rqst->len);
exec sql INSERT INTO TEST (COL1) VALUES (:col1);
if (SQLCODE != 0) {
(void)userlog("Mimer Insert: failed due to %d \n", SQLCODE);
if (transaction_started_by_me == 0) {
tpabort(0);
goto end;
}
}
if (transaction_started_by_me == 0) {
if (tpcommit(0) == -1) {
(void)userlog("tpcommit: failed due to %s \n", tpstrerror(tperrno));
goto end;
}
}
end:
/*
* Here is the original simpserv logic, to uppercase the input string
*/
for(i = 0; i < rqst->len-1; i++) rqst->data[i] = toupper(rqst->data[i]);
/*
* Return the transformed buffer to the requestor.
*/
tpreturn(TPSUCCESS, 0, rqst->data, 0L, 0);
}


4) Build the service. Preprocess the file above into a C-file with the Mimer/ESQL preprocessor. Then compile and link the resultant C-file:

esql –c simpserv.ec simpserv.c
%TUXDIR%\bin\buildserver -o TESTSERVER -r MimerSQL -f simpserv.c -s TOUPPER

(The service is called TOUPPER to allow for the client to be used without any modifications. If you want to use another name, remember to change both the client and the server to use the same service name.)

5) Configure the service. The next step is to update the ubbconfig-file with the new service. The following entries should be added to the file:

*GROUPS
MIMGROUPA LMID=MYLMID
GRPNO=117
OPENINFO=”MimerSQL:-uDEVUSER –pDEVPASS DBNAME”
TMS_NAME=”TMS_MIMER”

*SERVERS
TESTSERVER SRVGROUP=MIMGROUPA
SRVID=15000

Replace MIMGROUPA (a group name referenced under the *SERVERS section), MYLMID (Logical machine id; use same name as for other entries in this file), DEVUSER (Mimer SQL username), DEVPASS (Password for DEVUSER), and DBNAME (the Mimer SQL database name) with the settings you prefer. The GRPNO and SRVID can have any unique value. You need one group for each type of login you want to have in the Mimer SQL database server. You have one entry under servers for each server you add. In the example above a server called TESTSERVER is created (in our example it only contains the TOUPPER service). Please consult the Tuxedo documentation for further details about the ubbconfig file.

Please note that the information is added in two different sections in the file (*GROUPS and *SERVERS).

6) Load the configuration. Compile the ubbconfig-file with the TMLOADCF command:

%TUXDIR%\bin\tmloadcf ubbconfig.txt

Enter the name of your ubbconfig file in the command above.

7) Create a test case. Make sure that there exist a table TEST with a column COL1 in the Mimer SQL database that the Mimer SQL username used in step '5)' above have access to. The table can be created with the following SQL command:

CREATE TABLE TEST(COL1 VARCHAR(100) PRIMARY KEY);

Or if you do not have a databank available the following commands can be used. The user needs to have DATABANK privilege to execute the create databank command.

CREATE DATABANK TUXEDODB WITH TRANS OPTION;
CREATE TABLE TEST(COL1 VARCHAR(100) PRIMARY KEY) IN TUXEDODB;

You can, for example, use DbVisualizer in the Mimer SQL installation to run the above commands. To see the contents of the table execute the following SQL command:

SELECT * FROM TEST;

8) Start the service. You can now use commands such as tmboot and tmshutdown to control the Tuxedo services as usual. Please verify that the services start properly.

9) Build the client application. As this example server builds on the BEA supplied example SIMPAPP, the client in that example is used for testing. Compile, link, and run the unmodified program simplcl.c:

%TUXDIR%\bin\buildclient -o TESTCLIENT -f simpcl.c
TESTCLIENT Firststring
TESTCLIENT Secondstring

Verify that the client access work properly. Check the contents of the TEST table and verify that nothing went wrong in the output logs from the Tuxedo service.

10) Add more services. Repeat applicable parts of step 3 – 8 to add additional services.



Benefits

By using the Mimer SQL database server in connection with BEA Tuxedo, it is possible to build high performance, scalable, and robust applications. In addition, distributed transactions can be made over several Mimer SQL databases or even transactions that span other database brands. This allows changes to be made in either all or none of the systems, thereby ensuring the consistency of the data across database servers.


Links

Tuxedo home page: http://www.beasys.com/products/tuxedo.


Last updated: 2009-01-19

 

Powered by Mimer SQL

Powered by Mimer SQL