8 openUTM Product Set

Operation of Oracle Database Using openUTM Programs

The Universal Transaction Monitor (openUTM) controls the execution of user programs that can be used from a large number of terminals at the same time.

An openUTM application consists of a structured sequence of processing stages that are supplied with access rights for the specific user. These stages, in turn, consist of openUTM transactions that are carried out either in their entirety, or not at all.

If several users are working under openUTM at the same time then simultaneous access to the shared database is also usually required. The database/data communications system (DB/DC system), Oracle Database/openUTM, synchronizes access by openUTM applications to Oracle Database, and ensures that the database remains in a consistent state. In the event of system failure, the DB/DC system performs an automatic recovery, which ensures that the database remains in a consistent state.

Synchronization of Oracle and openUTM is done through the XA interface. The XA interface is an X/Open interface for the coordination between database systems and transaction monitors. Refer to the Developing Applications with Oracle XA chapter in Oracle Database Application Developer's Guide - Fundamentals for a description of the concepts of the XA interface.

Distributed openUTM Files

When you install the Oracle Database, as described in Chapter 3, " Installing Oracle Database Software", the openUTM related software of the Oracle Database software is installed. The distributed openUTM files comprise of:

• XAO.LIB

  This file contains the connection module for the XA interface.

• The following files provide examples of procedures and programs:

  UTM.DEMO.P.COMPILE.C
  UTM.DEMO.P.COMPILE.COBOL
  UTM.DEMO.P.KDCDEF
  UTM.DEMO.P.KDCROOT
  UTM.DEMO.P.PROBIND
  UTM.DEMO.P.PROSTRT
  UTM.DEMO.CSELEMP.PC
  UTM.DEMO.SELDEP.PCO
  UTM.DEMO.SELEMP.PCO
  UTM.DEMO.UPDEMP.PCO
  UTM.DEMO.ERRSQL.C
  UTM.DEMO.ERRTXT.C
  

Installing

Perform the following step to install after studying the Oracle Database Application Developer's Guide - Fundamentals:

• Grant the SELECT privilege to the DBA_PENDING_TRANSACTIONS table for all openUTM users connecting to the Oracle Database. Use the following example to grant the SELECT privilege to user scott:

  grant select on DBA_PENDING_TRANSACTIONS to scott;
  

  The openUTM users are identified in the Open String with the Item Acc. Refer to Defining an Open String section in this chapter.

Developing an Oracle Database/openUTM Application

Oracle Database 10g on BS2000 supports openUTM V5.3 and higher. Starting with the release V5.3 openUTM supports the XA interface. Oracle Database 10g on BS2000 coordinates with openUTM through this XA interface. Using this new interface of openUTM results in substantial changes in the steps of developing an Oracle Database/openUTM application compared to previous releases of openUTM and Oracle Database.

If you have an existing Oracle Database/openUTM application, then refer to the section Upgrading an existing Oracle Database / openUTM application from Oracle9i later in this chapter.

The steps involved in developing an Oracle Database application for coordinated inter-operation with openUTM are described in this section. The main steps are as follows:

1. Building the openUTM program units

2. Defining the configuration

3. Translating the KDCROOT table module and openUTM program units

4. Linking the openUTM application program

5. Starting the openUTM application

In addition, this section also describes how you define open strings and how you use precompilers with the Oracle XA library.

1. Building the openUTM program units:

   (refer to the openUTM manual Programming Applications with KDCS for COBOL, C, and C++, and the Oracle Database User's Guide for Fujitsu Siemens BS2000/OSD)

2. Defining the configuration:

   (refer to the openUTM manuals Generating Applications and Administering Applications)

   An Oracle Database/openUTM application requires the following information for execution:

   • Information about the application

   • Username/password with access protection

   • Information about the terminal and communication partners

   • Information about the transaction codes

   These properties collectively form the configuration, which is stored in the KDCFILE file. The configuration definition is carried out by the KDCDEF utility.

   This section gives the descriptions for three commands that are important for connecting to the Oracle database. They are:

   • DATABASE

     When the Oracle Database/openUTM application is generated, you must specify that openUTM communicates with the Oracle Database. Enter the following command to specify openUTM communication with the database:

     DATABASE TYPE=XA,ENTRY=XAOSWD
     

     where TYPE=XA specifies the use of the XA interface and ENTRY=XAOSWD specifies the name of the XA switch for the Oracle database (for dynamic registration).

   • OPTION

     If you specify the corresponding GEN operand in the OPTION command, then the KDCDEF utility also produces the source-code for the KDCROOT table module. The syntax of OPTION is as follows:

     OPTION [DATA=filename][,GEN={KDCFILE|ROOTSRC|NO|ALL}]
     [,ROOTSRC=filename][,SHARETAB=filename]
     [,TEST={N[o]|Y[ES]}]
     

   • MAX

     Another important operand is APPLIMODE, which is specified in the MAX command. This determines restart behavior after a system failure. The syntax of MAX is as follows:

     MAX APPLINAME=name[,APPLIMODE={S[ECURE]|F[AST]}]
     [,ASYNTASKS=number][...]
     

     APPLIMODE=SECURE means that openUTM continues after an application malfunction with a co-ordinated warm-start of the openUTM application and the Oracle database.

     If you specify APPLIMODE=FAST, no openUTM application restart is carried out, as openUTM stores no restart information. In the event of an error, the application starts from scratch. Transactions that are still open after a openUTM-application malfunctions are rolled back automatically.

   See the UTM.DEMO.P.KDCDEF file for an example procedure for building the KDCFILE and the KDCROOT table module.

3. Translating the KDCROOT table module and openUTM program units:

   The source of the KDCROOT table module should be compiled with the BS2000 Assembler and the openUTM program units should be compiled with the corresponding programming language compilers. See the example procedure UTM.DEMO.P.KDCROOT for the compilation of the KDCROOT table module.

4. Linking the openUTM application program:

   The openUTM application program is produced by linking the KDCROOT table module with the openUTM program units.

   Note:

   Instead of writing the binding procedure you should use the example procedure UTM.DEMO.P.PROBIND and apply modifications when needed.

   If you cannot do without writing your own binding procedure please study the example carefully before writing one.

5. Starting the openUTM application:

   An example procedure for starting the openUTM application can be found in the file UTM.DEMO.P.PROSTRT.

   When starting the openUTM application, you must specify the start parameters for openUTM, as well as for the Oracle Database.

   The openUTM start parameters are described in the openUTM manual Using openUTM Applications under BS2000/OSD.

   The start parameter for using the XA interface for coordinated inter-operation with Oracle Database 10g is:

   .RMXA RM="Oracle_XA",OS="<ORACLE open string>"
   

Oracle_XA{+required_fields...}[+optional_fields...]

Oracle_XA+Acc=P/scott/tiger+SesTm=0+DbgFl=15

Oracle_XA+SqlNet=MADRID_FINANCE+Acc=P/scott/tiger+SesTm=0
Oracle_XA+DB=finance+SqlNet=MADRID_FINANCE+Acc=P/scott/tiger
+SesTM=0

Oracle_XA+SqlNet=MADRID_FINANCE+Acc=P/scott/tiger+SesTm=0

EXEC SQL SELECT ENAME FROM EMP;

Oracle_XA+SqlNet=MADRID_FINANCE1+Acc=P/scott/tiger+SesTm=0
Oracle_XA+DB=MANAGERS+SqlNet=MADRID_FINANCE2+
Acc=P/scott/tiger+SesTm=0
Oracle_XA+DB=PAYROLL+SqlNet=MADRID_FINANCE3+
Acc=P/scott/tiger+SesTm=0

EXEC SQL DECLARE PAYROLL DATABASE; 
EXEC SQL DECLARE MANAGERS DATABASE;

EXEC SQL AT PAYROLL update emp set sal=4500 where empno=7788;
EXEC SQL AT MANAGERS update emp set mgr=7566 where empno=7788;
EXEC SQL update emp set deptno=30 where empno=7788;

EXEC SQL BEGIN DECLARE SECTION;
db_name1 CHARACTER(10);
db_name2 CHARACTER(10)
EXEC SQL END DECLARE SECTION;
.
.
set db_name1 = 'PAYROLL' 
set db_name2 = 'MANAGERS'
.
.
EXEC SQL AT :db_name1 UPDATE...
EXEC SQL AT :db_name2 UPDATE...

Troubleshooting

This section discusses how to recover data if there are problems or a system failure. Both trace files and recovering pending transactions are discussed in the following sections.

ORAXALOG.pid-db_name-date.TRC

ORAXALOG.1234-NULL-990402.TRC

ORAXALOG.5678-FINANCE-981215.TRC

1032.2: xa_switch rtn ORA-22

SELECT * FROM DBA_2PC_PENDING p, DBA_2PC_NEIGHBORS n 
WHERE p.LOCAL_TRAN_ID = n.LOCAL_TRAN_ID 
AND
n.DBID = 'xa_orcl';

Upgrading an existing Oracle Database / openUTM application from Oracle9i

Starting with the release V5.3, openUTM supports the XA interface. Oracle Database 10g on BS2000 does coordinated interoperation with openUTM now exclusively through this XA interface. This results in substantial changes in all steps of developing an Oracle Database/openUTM application compared to previous releases of openUTM and Oracle Database, where the coordinated interoperation had been done through the proprietary interface IUTMDB of openUTM on BS2000/OSD.

If you have an existing openUTM application on BS2000/OSD, which does coordinated interoperation with one or more Oracle instances through the IUTMDB interface, then you have to apply the following changes to the steps of the application development in order to migrate to the new XA interface of openUTM.

The following table lists the steps used for Oracle9i:

The following table lists the steps to be used for Oracle Database 10g:

Oracle Database 10g release 2 on BS2000/OSD does not support the IUTMDB interface of openUTM.