A quick overview of some basic design concepts underlying the Oracle Database Management System will help you interface your COBOL program to it.
Transactions
The Oracle RDBMS is a transaction-based system. All of the work that you perform while using Oracle must occur within a transaction, whether that work is being done through Acu4GLTM for Oracle, or another 4GL application. When you use Acu4GL for Oracle, a transaction is implicitly started for you by the database engine itself with the first file I/O operation performed on a file associated with Oracle. Because all operations with Acu4GL for Oracle occur within a transaction, any record locked during processing remains locked until either a COMMIT WORK or ROLLBACK WORK is issued. This action results in behavior similar to the LOCK ON MULTIPLE RECORDS clause in COBOL.
The benefits of a transaction management system are best illustrated by an example. A COBOL application that handles order entry might perform these steps to accept an order:
1. Write an invoice record.
2. Update a customer record.
3. Write a payroll record for sales commissions.
4. Update an inventory record.
This series of four file operations is a logical unit. If the program were interrupted, and completed only some of the four file operations, then the files would be in an inconsistent state. For example, if the program terminated unexpectedly after it updated the customer record, but before it updated the inventory record, then a subsequent run might access non-existent inventory.
The solution to this problem is to provide a method for the programmer to define a set of operations that should either all occur or all not occur. Then, if the program encounters an error or terminates, the files are left in a consistent state.
All file operations that are part of a transaction are logged. Once logged, they can either be committed or rolled back (undone) by the program.
If a program dies, or the system fails, the log file can be used to reconstruct complete transactions, thus returning all files to a consistent state. Transaction logging thus offers these two facilities:
It provides the programmer with the ability to define transactions, and the
ability to commit them or "undo" them (usually in response to an error
condition). This "undo" facility is called a "rollback."
It provides the ability to reconstruct files into a consistent state after a
program dies or system failure occurs. This operation is called "recovery."
Note that transaction management facilities are available in ACUCOBOLTM-GT Version 2.4.0 and later.
Record-locking Issues in Transactions
Applications that are written for transaction management systems, or that perform work in small "operation-based" logical units, benefit greatly from Oracle's transaction management systems. Applications that are not written for transaction management encounter difficulty with record locking when operating against a system that enforces transaction management.
The difficulty can occur with an application that is performing more than one logical task at a time. Any operation that modifies or reads data in an I/O mode without the WITH NO LOCK phrase causes a lock to be placed in the database system. As a result, the application may have many more record locks present than would be expected by the normal rule of COBOL file locking. The application would act similarly as to when the LOCKS ON MULTIPLE RECORDS clause in COBOL is used. This can best be illustrated by an example:
1. The user is entering a customer's order.
2. As each line item is entered into the order, the inventory file is modified to reflect that items have been removed from the stock on hand.
3. The user must switch to a different part of the application to perform a different task, perhaps as a result of a phone call from a new customer.
4. All of the records that were locked, or modified, by the application before the switch remain locked because the first order is not complete. No COMMIT or ROLLBACK has been issued to complete the transaction. All of the records locked by the transaction remain locked until the application ends the transaction.
5. Because one order is open and not yet committed, other applications may be locked out of certain order items if they are still locked by the processing of the first order. The second order entry may be held up until the first order is completed.
6. Note that the first application is not locked out. A process can read its own locked records.
Acu4GL and Record Locking
Acu4GL provides semi-automated ways to handle transaction logging based on the setting of the COMMIT-COUNT environment variable. You can also directly alter your source code to deal with this issue. Individual users determine how much work they wish to do to conform to Oracle's transaction management system by choosing the method that best fits their needs and resources. The following methods are listed in order of increasing amount of work:
COMMIT-COUNT = 0 (Default)
When you set this variable to zero, the runtime tracks the number of logical
locks that are currently in effect. When the number of logical locks reaches
zero, the runtime assumes that a transaction is complete and issues a COMMIT
statement.
COMMIT-COUNT = n
When you set this variable to a nonzero value, the runtime tracks the number
of WRITE, REWRITE, and DELETE operations, until the value of COMMIT-COUNT is
reached, at which time the runtime issues a COMMIT statement. The READ, START and
READ NEXT operations do not count toward this total because the runtime is
tracking data-altering operations rather than logical record locks. The
disadvantage of this method is that when a COMMIT is issued, any record locks held by
the runtime are released.
COMMIT-COUNT = -1
No commit is issued by the Acu4GL product. When COMMIT-COUNT is set to -1, two alternate ways to perform a commit or rollback are available:
1. Call "sql.acu" with COMMIT WORK or ROLLBACK WORK.
2. Use the COBOL verbs COMMIT and ROLLBACK, available in ACUCOBOL-GT.
COMMIT-COUNT is set to -1 automatically when you use the transaction management facilities available in the ACUCOBOL-GT compiler. A COMMIT WORK is, however, issued on exit from the runtime (for example, on execution of a STOP RUN).
COMMIT VERB IN COBOL
This method forces a COMMIT to be sent to Oracle. It can be used in
conjunction with other modes of COMMIT handling. For non-ORACLE files, this is
equivalent to the UNLOCK ALL verb.
EXPLICITLY CODED TRANSACTIONS
This method provides the greatest flexibility in that transactions are
specifically tailored for the user's application. This method also requires the most
work for traditional COBOL programs in which transaction modules may not be
clearly defined.
Acu4GL for Oracle
The Oracle system parameter open_cursors should be set to enhance communication with Acu4GL. See section C.2.4 "Checking System Parameters" for more information.
Table Ownership
Table names in Oracle have the form owner.table_name. If you are the owner of a given table, you can refer to it as just table_name. If you are not the owner, you must refer to it with the owner of the table as a prefix. Acu4GL for Oracle provides a user configurable method (USER-PATH ) for implementing this.
Security
Security is implemented in the Oracle RDBMS. A user is required to log in to the RDBMS before any file processing can occur. Acu4GL for Oracle provides both a default and a user configurable method for implementing this. Oracle's security considerations pose several challenges for COBOL programmers. Because of the various levels of permissions, certain operations are not allowed unless you have Database Administrator (DBA) privileges. These restricted operations include:
creating a table under another user's name
dropping a table owned by another user
Oracle version 7.3.3 and later also provides additional security levels; however, you need to consult the Oracle documentation to determine if any of these permission levels are appropriate for your site.
Generally, it is best for someone with DBA privileges to create and drop the tables, allowing others only the permissions to process information contained in them. A table can be referenced either by owner.table_name or by a public synonym that you have created for the table. See the Oracle documentation for more details on DBA privileges and public synonyms.