C H A P T E R 10 - Accessing an RDBMS Using the SQL Interface

This chapter describes how Sun MTP supports the SQL interface to relational database management systems (RDBMS).

The typical application transaction contains one or more CICS application programs written with a CICS-style command level interface to perform communication, as well as program flow and executive services, combined with an RDBMS-style programmatic interface for database access.

Application Design Techniques

Application design for the Sun MTP/RDBMS environment combines two programming techniques.

The procedural SQL statements, which must be preprocessed by the RDBMS, result in direct calls to the RDBMS runtime system; that is, SQL calls are not intercepted by Sun MTP.

The working storage of a CICS program is initialized to binary zeroes unless an explicit "value" statement is assigned to a data name within the program definition. The developer must take this into consideration for each data structure within the program, including the SQLCA structure, which is used to communicate between the RDBMS and the user program. The SQLCA structure is included at the appropriate location in the COBOL data division of a program using the statement:

The embedded SQL preprocessor expands the EXEC statement into a COBOL structure. See Compiling COBOL Programs.

Maintaining Database Integrity

If the CICS transaction completes normally, all updates are written to the application database. If the transaction completes abnormally, the database remains in the state that it was in at the start of the transaction.

Commit and rollback operations are either implicit or explicit. Sun MTP implicitly commits all database operations when a transaction terminates normally, and rolls back a database when a transaction terminates abnormally. In addition, an application program can request an explicit commit by executing a SYNCPOINT, and can request an explicit rollback by executing a SYNCPOINT ROLLBACK.

SQL provides a COMMIT WORK facility. The execution of this verb commits only the updates for the particular RDBMS for which it is coded; that is, an Oracle COMMIT WORK does not result in an Informix or Sybase database being committed. Because a COMMIT WORK is local to each RDBMS, do not use it within an application that uses more than one RDBMS or a combination of one RDBMS and VSAM files.

Implementing Implicit Operations

You can assume certain implicit database operations during the life of a transaction. These implicit operations are usually implemented within the user exit modules of the Sun MTP transaction server process (unikixtran) and batch server process (unikixvsam).

The user exit module provides exits from the Sun MTP software at six predetermined points:

The administrator and the application designer must coordinate the number and names of databases within transactions. Without this coordination, problems will arise if databases are updated by the application and no database commit is executed at commit time.

Refer to the Sun Mainframe Transaction Processing Software Administrator's Guide for a description of the RDBMS user exit module implementation.

Accessing More Than One RDBMS Within a Transaction

Access to more than one RDBMS within a single transaction is not fully supported within the Sun MTP product. To support multiple RDBMS, each RDBMS and the on-line transaction processing (OLTP) executive must adhere to a common two-phase commit protocol.

Although Sun MTP does not fully and generically support a multi-RDBMS application, the software can access more than one RDBMS for a specific case. In a multi-RDBMS application, if only one RDBMS is updating a database, the application functions normally. If more than one RDBMS is updating a database, each RDBMS must commit individually. This creates a window in time where one RDBMS might have committed and, for some reason, a second or subsequent RDBMS might fail to commit. In this situation, the changes committed by the first RDBMS cannot be rolled back. This results in a logically inconsistent application database.

Certain applications can tolerate such behavior, in which case more than one RDBMS can be used within an application. However, design your applications to avoid this situation.

RDBMS Security Management

An RDBMS handles security through some form of permission assigned to an object or resource. For example, you can control verbs such as CREATE TABLE at the userid level.

The RDBMS client code is combined with two Sun MTP executables: the transaction and batch servers. The region executes as the UNIX userid of the user that starts the Sun MTP region. The application programs execute within either the transaction or batch server process; therefore, they execute with the userid of that process.

You can enforce security at the transaction or terminal level in the user exit module with the kxtctinfo function of Sun MTP. This function returns information related to the terminal user. Combined with the security features provided by the RDBMS, this function enables the administrator to implement security. The kxtctinfo function is described in more detail in the Sun Mainframe Transaction Processing Software Administrator's Guide.

The method for implementing security differs from RDBMS to RDBMS. The following sections provide examples of security implementations for each RDBMS.

Oracle Security Considerations

Oracle supports the ROLE function, which can set the privileges on database objects during the database operation.

Example: To set the permissions for each table and user listed in the following table:

Table/User	USRA	USRB	USRC
TABLEA	ALL	NONE	SELECT
TABLEB	ALL	SELECT	NONE

CREATE ROLE USRA;

CREATE ROLE USRB;

CREATE ROLE USRC;

GRANT ALL ON TABLEA TO USRA;

GRANT SELECT ON TABLEA TO USRC;

GRANT ALL ON TABLEB TO USRA;

GRANT SELECT ON TABLEB TO USRB;

In this scenario, the userid of the Sun MTP region owner is used to make the initial connection to the Oracle server using the transaction/batch server initialization user exit. The userid can be the application name in the SIT, which is determined using the kxsysinfo function, or some other application-specific method.

The userid USRA, USRB and USRC are defined in the SNT along with their transaction privileges. During the transaction initialization user exit, the userid of the transaction initiator is used to set the ROLE of the transaction user. The userid is determined using the kxtctinfo function, which specializes the privileges of the transaction user when accessing the database objects.

If USRB is the transaction initiator and the transaction code accesses TABLEA, a SQLERROR is returned during program execution.

Similarly, if there is a USRn with no privileges on the database, the transaction initiation generates a failure because there is no ROLE defined in the database.

DB2 Security Considerations

DB2 contains the user privileges in a set of system tables for each database. Perform a database query at the time of transaction initiation to check for the access privilege before continuing with the execution.

Sybase Security Considerations

Example: To set these permissions for each table and user listed in the following table:

Table/User	USRA	USRB	USRC
TABLEA	ALL	NONE	SELECT
TABLEB	ALL	SELECT	NONE

USRA

sp_adduser USRB

sp_adduser USRC

GRANT ALL ON TABLEA TO USRA

GRANT ALL ON TABLEB TO USRA

GRANT SELECT ON TABLEA TO USRC

GRANT SELECT ON TABLEB TO USRB

go

The SNT and SIT have the same information as in the Oracle example. The userid of the database owner is used within the transaction server initialization user exit to connect to the database.

The transaction initiation phase determines the userid using the kxtctinfo function and issues a SETUSER SQL statement to inform Sybase of the userid. This filters the privileges on the database objects at execution time.

If the initiator was USRB and the transaction accessed TABLEA, a SQLERROR is returned to the application because there are no privileges on TABLEA for USRB.

Similarly, if USRn tries to initiate the transaction, the initiation fails because there is no database user USRn, even if USRn is defined in the SNT with privileges to use the transaction.

Managing SQL Cursors

The SQLCA communication block is used to communicate between the RDBMS runtime system and the application program. One of the resources it controls is the management of SQL cursors.

Normally, a SQL cursor is opened, processed, and closed within the same application program. This program might be a single source program or the SQLCA structure might be globally visible to multiple modules within the same program. In either case, processing is handled entirely within a single executable.

Using Multiple Executables

CICS provides facilities to modularize the processing into multiple executables, which are bound together into a single transaction by using the LINK and XCTL functions of the CICS command level API. You can combine the XCTL and LINK functions.

In general, all RDBMS support cursor operations across multiple programs because the cursor management is done within the runtime system rather than by the SQLCA. However, if the application requires information from the execution of a previous SQL statement to be present in the SQLCA of a program that is being accessed by a XCTL or LINK command, the RDBMS does not provide this service. The application designer must handle this situation within the logic of the programs.

Compiling COBOL Programs

If your COBOL application program contains RDBMS-specific SQL statements, CICS command-level API statements and standard COBOL statements, you must convert it into a form suitable for execution.

1. Pass the source code through the RDBMS preprocessor, which converts the embedded SQL statements into COBOL calls to the RDBMS runtime system.

2. Pass the resulting program through kixclt, the Sun MTP preprocessor. kixclt converts the CICS command-level API statements into COBOL calls to the Sun MTP runtime system.

You can automate the compilation process by incorporating the three steps into a makefile.

1. Using the Oracle Pro*Cobol preprocessor, generate an intermediate source file.

Alternatively, you can customize the sample makefile located in the $UNIKIX/test/oracle/cobol directory.

2. Compile the program using your COBOL compiler, which also compiles DB2 statements.

Alternatively, you can customize the sample makefile located in the $UNIKIX/test/db26000/cobol directory.

DB2 requires that you make the COBOL runtime system with DB2 compiler options; that is, make $COBDIR/rts32 with DB2 libraries and entry points. Refer to the DB2 Installation Guide for more information.

1. Using the Sybase embedded SQL/COBOL preprocessor, generate an intermediate source file.

Alternatively, you can customize the sample makefile located in the $UNIKIX/test/sybase/cobol directory.

Compiling Oracle PL/I Programs

If your application program contains Oracle RDBMS-specific SQL statements, CICS command-level API statements, and standard Open PL/I statements, you must convert them into a form suitable for execution.

1. Pass the source code through the Oracle preprocessor to convert the embedded SQL statements into PL/I calls to the Oracle runtime system and generate an intermediate source file.

2. Pass the resulting program through the Sun MTP kixplt preprocessor to convert the CICS command-level API statements into PL/I calls to the Sun MTP runtime system.

You can automate the process by incorporating the three steps into a UNIX makefile. Refer to the Liant Open PL/I documentation.

Commit	Applies all database updates
Rollback	Reverts to the initial state of the database