Considerations when CICS application interacts with multiple databases

Body

Suppose an application need to access to two (or more) database Database1 and Database2 from an application program as depicted in figure 1, follow this procedure to configure the TXSeries CICS region.

Configuration considerations:

1. Define all the XA databases to the transaction manager.
   Databases have to be defined to the TM prior to its usage. An example configuration on TXSeries CICS region is explained below.
   
   TXSeries CICS Region configuration:
   Both the databases (Database1 and Database2) need to be added to the XAD stanza of the TXSeries CICS region with cicsadd command utility.
   dbxa: cicsupdate –c xad –r <CICS Region> dbxa XAOpen="DB=Database1, uid=cics, pwd=cics"
   dbxb: cicsupdate –c xad –r <CICS Region> dbxb XAOpen="DB=Database2, uid=cics, pwd=cics"
   Adding a new database to the region requires a region restart in TXSeries.
   
   Order of database definitions in TXSeries:
   When multiple resource managers like DB2 or Oracle and a Messaging System like WebSphere MQ are configured to TXSeries region and if a transaction running on CICS region updates these resources, the CICS application makes XA calls to COMMIT the resources. The COMMIT call to each resource manager will happen in a serialized way. Suppose if you want COMMIT DB2 update before WebSphere MQ queue messaging update, the XA call flow to DB2 should happen before the WMQ.
   The XA operation order in which it selects the resource manager is based on XAD definition entry name ASCII value. TXSeries runtime loads the XAD entries in ascending order of ASCII value equivalent of XAD entries.  For example in the XAD stanza file entry, AABB is loaded first when compared to BBCC as the ASCII value of AABB is less than BBCC.

       Figure 1. CICS region with two XA resource Managers.

2. Choose the proper switchload file to suit your environment
   Depending on the need and requirements, either dynamic or a static switchload file can be used to configure. A dynamic switchload file will initiate XA calls to the database only when a transaction requires a participation of the particular database where as a using a static switchload file enforces all the configured databases to participate in a transaction. It is recommended to choose the dynamic switchload file unless there are reasons to justify the use of static switchload file.
    
3. Use a single switchload file for multiple configurations to databases of same type.
   Using two different switch load files to the two different DB2 databases can also result unpredictable results. Always use same switchload file to interact with multiple DB2 databases as both switchload files load into CICS application server.
    
4. Avoid using multiple XA definitions for a single database.
   Using two XAD entries for the same database will produce unpredictable results like CICS abends and SQL errors on TXSeries systems.
    
5. Define correct instance of the database
   If multiple instances of DB2 is running in a server, set the DB2INSTANCE to the necessary instance before configuring the TXSeries region with the database running in that instance.

Application considerations:

1. Switching to a database in multiple database configuration
   The application program should take care of switching the databases whenever the user needs access to a particular database using EXEC SQL SET CONNECTION.
   The prg_db2.sqc sample program could be designed as follows:

/*** Declarations ***/

main()

{

/*****Connect to database 1 ****/

EXEC SQL SET CONNECTION :db1;

EXEC SQL INSERT into test1(empno,empname)  values(:empno,:empname);

---- Other application code and SQL statements for Database 1-----

/*******Connect to Database 2 ****/

EXEC SQL SET CONNECTION :db2;  

EXEC SQL INSERT into test2(empname,salary)  values(:name,:salary);

---- Other application code and SQL statements for Database 2  -----

/*** Connect to Database 1 ***/

EXEC SQL SET CONNECTION :db1;

/*** SQL statements for Database 1 ***/

..

..

EXEC CICS RETURN;

}

/*** End of the program ***/

Note: Use EXEC SQL AT <database name> if connection switching to oracle database.

2. Switching between file manager and resource manager in an application
   If a database is used as file manager instead of SFS, then application must take care of switching to file manager database before executing any file, TSQ or Intrapartition TDQ operations which involve the database. If single phase switchload file is used to connect to two database (one as File Manager and other as Resource Manager), then the application programmer must ensure to set database connection to file manager using 'EXEC SQL SET CONNECTION' for file manager operations and set database connection to resource manager using 'EXEC SQL SET CONNECTION' for resource manager operations. Before returning from CICS application (before EXEC CICS RETURN) user also need to set the connection to file manager using 'EXEC SQL SET CONNECTION'.

Compiling the applications

Applications written on a TM environment uses both SQL and CICS calls. The applications have to be precompiled through SQL and CICS pre-compiler before compiling with the language compiler. It is suggested to precompile using database precompiler before precompiling with CICS.

Compiling procedure of a sample CICS database application in TXSeries is shown below:

The sample program prg_db2.sqc could be compiled by preparing the program, then connecting to the each database and finally compiling the application source with cicstcl utility using the following steps:

db2 prep  prg_db2.sqc bindfile collection cics;

db2 connect to Database1

db2 bind prg_db2.bnd

db2 connect reset

db2 connect to Database2

db2 bind prg_db2.bnd

db2 connect reset

mv  prg_db2.c prg_db2.ccs

cicstcl -e -d -lC  prg_db2.ccs

Note: If the application written in COBOL or any other language follow the respective db2 prep command and cicstcl language option to compile the application.