Skip Headers
Oracle® Database Installation and Administration Guide
11g Release 2 (11.2) for Fujitsu BS2000/OSD

E27508-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

8 openUTM Product Set

This chapter describes how to use the BS2000/OSD transaction monitor openUTM for coordinated interoperation with Oracle Database 11g Release 2 (11.2). The following areas are covered:

8.1 Operation of Oracle Database Using openUTM Programs

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 Developing Applications with Oracle XA chapter in Oracle Database Advanced Application Developer's Guide for a description of the concepts of the XA interface.

8.2 Distributed openUTM Files

When you install Oracle Database, as described in Chapter 3, " Oracle Database Installation and Deinstallation", 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
    

8.3 Installing

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

  • 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.

8.4 Developing an Oracle Database/openUTM Application

Oracle Database 11g on BS2000 supports openUTM V6.0 or higher. openUTM supports the XA interface. Oracle Database 11g on BS2000 coordinates with openUTM through this XA interface.

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 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 coordinated warm-start of the openUTM application and the Oracle database.

      If you specify APPLIMODE=FAST, then no openUTM application restart is executed, as openUTM stores no restart information. In the event of an error, the application starts from scratch. Transactions that are still open after an openUTM-application malfunction 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.

    You must include the stub module XAOSTUB:

    INC-MOD LIB=ORAUID.XAO.LIB,ELEM=XAOSTUB
    

    Note:

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

    If you must write your own binding procedure, then 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>"
    

8.4.1 Defining an Open String

This section describes how to construct an open string. The transaction monitor uses this string to open the database. The maximum number of characters in an open string is 256, and the maximum number of open strings is 8. Construct the string as follows:

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

where the required_fields are:

  • Acc=P/user/access_info

  • SesTm=session_time_limit

and the optional_fields are:

  • DB=db_name

  • MaxCur=maximum_no_of_open_cursors

  • SqlNet=connect_string

  • DbgFl=value_from_1_to_15

Notes:

Remember the following:

  • You can enter the required fields and optional fields in any order when constructing the open string.

  • All field names are case-insensitive, although their values may or may not be case-sensitive depending on the system.

  • You may not use the "+" character as part of the actual open string.

8.4.1.1 Required Fields

The required fields for the open string are:

Item Meaning
Acc Specifies user access information.
P Indicates that explicit user and password information is provided.
user A valid Oracle Database username.
access_info The corresponding current password.

For example, Acc=P/scott/tiger indicates that user and password information is provided. In this case, the user is scott and the password is tiger.

For the correct process, ensure that scott has the SELECT privilege on the DBA_PENDING_TRANSACTIONS table.

Item Meaning
SesTm Specifies the maximum amount of time a transaction can be inactive before it is automatically deleted by the system.
session_time_limit This value should correspond to what you require as a maximum time from initiation of a global transaction and the completed commit or rollback of that transaction.

8.4.1.2 Optional Fields

Optional fields for the open string are described in the following table:

Item Meaning
DB Specifies the database name.
db_name Indicates the name used in Oracle Database precompilers to identify the database.

Application programs that use only the default database for the Oracle Database precompiler, that is, do not use the AT clause in their SQL statements, should omit the DB=db_name clause in the open string.

Note: This default database is represented in the ORAENV file by ORASID.

Applications that use explicitly–named databases should indicate that database name in their DB=db_name field.

For example, DB=payroll indicates that the database name is payroll and that the application server program uses that name in AT clauses.


For more information about precompilers, specifically Pro*C, refer to the section Using Precompilers with openUTM later in this chapter.

Item Meaning
MaxCur Specifies the number of cursors to be allocated when the database is opened. It serves the same purpose as the precompiler option maxopencursors.
maximum_no_of_open_cursors Indicates the number of open cursors. The default is 10.

For example, MaxCur=5 indicates that the process should try to keep five open cursors cached.

For more information about maxopencursors, refer to the Oracle Database Programmer's Guide to the Oracle Precompilers.

Item Meaning
SqlNet Specifies the SQL*Net connection string.
connect_string Indicates the string to be used to log onto the system. This can be any supported Oracle Net Services connect string.

For example:

SqlNet=MADRID_FINANCE indicates an entry in TNSNAMES.ORA referencing a protocol, a host, and a portnumber. For more information, refer to Chapter 9, " Oracle Net Services" in this book.

Item Meaning
DbgFl Specifies if debugging should be enabled (debug flag). For more information refer to Debugging in the subsequent section in this chapter.

8.4.1.3 Examples

This section contains examples of open strings using the preceding information.

Note:

If the string is longer than one line, then refer to the openUTM documentation for information about how to split up the string information.

For bequeath protocol:

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

For other protocols:

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

The optional fields LogDir, Loose_Coupling, SesWT, and Threads are not supported.

For more information about the fields in the open string refer to the Developing Applications with Oracle XA section in Oracle Database Advanced Application Developer's Guide.

8.4.2 Using Precompilers with openUTM

You can choose from two options when interfacing with precompilers:

  • Using precompilers with the default database

  • Using precompilers with a named database

You should run all precompiler programs with the option release_cursor set to no. Precompiler programs may be written in C or COBOL. In the following examples, the precompiler Pro*C is used.

8.4.2.1 Using Pro*C with the Default Database

To interface to Pro*C with the default database, ensure that the DB=db_name field used in the open string is not present. The absence of this field indicates the default connection as defined in the ORAENV file, and only one default connection is allowed for each process.

The following is an example of an open string identifying a default Pro*C connection:

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

Here, DB=db_name is absent, indicating an empty database identifier string.

The following is the syntax of a select statement:

EXEC SQL SELECT ENAME FROM EMP;

8.4.2.2 Using Pro*C with a Named Database

To interface to Pro*C with a named database, include the DB=db_name field in the open string. Any database you refer to must reference the same db_name specified in the corresponding open string.

An application may include the default database, as well as one or more named databases, as shown in the following examples.

For example, suppose you want to update an employee's salary in one database, the department number deptno in another, and the manager information in a third database. You would configure the following open strings in the transaction manager:

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

There is no DB=db_name field in the first open string.

In the application program, you would enter declarations such as:

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

Again, the default connection corresponding to the first open string that does not contain the db_name field, does not require a declaration.

When doing the update, enter statements similar to the following:

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;

There is no AT clause in the last statement because it refers to the default database.

You can use a character host variable in the AT clause, as the following example shows:

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...

For more information, refer to the respective sections in the Pro*COBOL Programmer's Guide and Pro*C/C++ Programmer's Guide that discusses concurrent logons.

Note:

  • Application servers must not create Oracle database connections of their own. Therefore, an openUTM user is not allowed to issue CONNECT statements within an openUTM program. Any work performed by them would be outside the global transaction, and may confuse the connection information given by openUTM.

  • SQL calls must not occur in the openUTM start exit routine, however may occur in the conversation exit routine (Vorgangs-Exit)

8.5 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.

8.5.1 Trace Files

The Oracle XA library logs any error and tracing information to its trace file. This information is useful in supplementing the XA error codes. For example, it can indicate whether an open failure is caused by an incorrect open string, failure to find the Oracle Database instance, or a login authorization failure. The name of the trace file is:

ORAXALOG.pid-db_name-date.TRC

where

pid is the process identifier (TSN)

db_name is the database name you specified in the open string field DB=db_name

date is the date when the trace file is created

8.5.1.1 Trace File Examples

Examples of two types of trace files are discussed in this section.

The following example shows a trace file for an application's task '1234' that was opened on April 2nd 1999. The DB field for this application was not specified in the open string when the resource manager was opened

ORAXALOG.1234-NULL-990402.TRC

The following example shows a trace file that was created on December 15th 1998 by task 5678. The DB field was specified as FINANCE in the open string when the resource manager was opened.

ORAXALOG.5678-FINANCE-981215.TRC

Each entry in the trace file contains information that looks like this:

1032.2: xa_switch rtn ORA-22

where 1032 is the time when the information is logged, 2 is the resource manager identifier, xa_switch is the module name, and ORA-22 is the returned Oracle database information.

8.5.2 Debugging

You can specify the DbgFl (debug flag) in the open string. For more information, refer to the Oracle XA chapter in Oracle Database Advanced Application Developer's Guide.

Depending on the debugging level (low:DbgFl=1,high:DbgFl=15) you can get more or less debug entries in the trace file ORAXALOG.pid-db_name-date.TRC (refer to the preceding section).

8.5.3 In-Doubt or Pending Transactions

In-doubt or pending transactions are transactions that have been prepared but not yet committed to the database. Generally, openUTM resolves any failure and recovery of any in-doubt or pending transaction. However, the Database Administrator may have to override an in-doubt transaction in working with UTM-F, that is, APPLIMODE=FAST, for example when the in-doubt transaction is:

  • Locking data that is required by other transactions

  • Not resolved in a reasonable amount of time

Note:

Overriding in-doubt transactions can cause inconsistency between openUTM and the database. For example, if the DB transaction is committed by the Database Administrator and the openUTM application rolls back the transaction in the warm-start phase, then the Oracle Database cannot roll this committed transaction back, therefore, causing an inconsistency.

8.5.4 Oracle Database SYS Account Tables

There are four tables under the Oracle Database SYS account that contain transactions generated by regular Oracle Database applications and Oracle Database/openUTM applications. These are as follows:

  • DBA_2PC_PENDING

  • DBA_2PC_NEIGHBORS

  • DBA_PENDING_TRANSACTIONS

  • V$GLOBAL_TRANSACTION

Note:

For detailed information about how to use these tables, refer to the sections in the Oracle Database Administrator's Guide that discuss failures during two-phase commit and manually overriding in-doubt transactions.

For transactions generated by Oracle Database/openUTM applications, the following column information applies specifically to the DBA_2PC_NEIGHBORS table:

  • The DBID column is always xa_orcl.

  • The DBUSER_OWNER column is always db_namexa.oracle.com.

Remember that the db_name is always specified as DB=db_name in the open string. If you do not specify this field in the open string, then the value of this column is NULLxa.oracle.com for transactions that are generated by Oracle Database/openUTM applications.

For example, you could use the following sample SQL statement to find out more information about in-doubt transactions that are generated by Oracle Database/openUTM applications.

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