Note: If users are using Oracle client software to connect to a database not on the current machine, they must set up an alias for the database they are connecting to using Oracle's "Net Configuration Assistant" or a similar tool.
This appendix provides you with the information you need that is specific to Database Connector™ for Oracle.
Database Connector for Oracle is based on the Oracle Call Interface (OCI), an API that makes the reUZE Developer code more portable to the maximum number of platforms.
A quick overview of some basic design concepts underlying the Oracle Database Management System relational database system (RDBMS) will help you interface your COBOL program to it.
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 Database Connector for Oracle or another 4GL application. When you use the Connector, 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 the Connector 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:
This series of four file operations is a logical unit. If the program were interrupted and had completed only some of the four file operations, 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, 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 be either 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, returning all files to a consistent state. Transaction logging thus offers these two facilities:
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 to when the LOCKS ON MULTIPLE RECORDS clause in COBOL is used. This can best be illustrated by an example:
Database Connectors 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 the Oracle 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:
When you set this variable to zero ("0"), the run-time system tracks the number of logical locks that are currently in effect. When the number of logical locks reaches zero, the run-time system assumes that a transaction is complete and issues a COMMIT statement.
When you set this variable to a nonzero value, the run-time system tracks the number of WRITE, REWRITE, and DELETE operations, until the value of COMMIT_COUNT is reached, at which time the run-time system issues a COMMIT statement. The READ, START, and READ NEXT operations do not count toward this total, because the run-time system 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 run-time system are released.
No commit is issued by the Connector. When COMMIT_COUNT is set to "1", two alternate ways to perform a commit or rollback are available:
COMMIT_COUNT is set to "-1" automatically when you use the transaction management facilities available in the Compiler. A COMMIT WORK is, however, issued on exit from the run-time system (for example, on execution of a STOP RUN).
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.
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.
COMMIT_COUNT configuration variable
The Oracle system parameter open_cursors should be set to enhance communication with the Connector. For more information, see the section "Checking System Parameters."
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. The Connector provides a user-configurable method (the USER_PATH ACUFH configuration variable) for implementing this.
USER_PATH configuration variable
Security is implemented in the Oracle RDBMS. A user is required to log in to the RDBMS before any file processing can occur. The Connector 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:
Oracle versions 9i and later also provide additional security levels; however, you must 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.
Note: By default, the Connector always checks to see if a public synonym is available for a file at open time, regardless of what USER_PATH is set to. If the name of a table owned by a current user is the same as a public synonym, the user-owned table is chosen.
USER_PATH configuration variable
The Oracle RDBMS must be installed and configured prior to the installation of Database Connector for Oracle.
We also recommend:
Micro Focus does not provide these products.
Make sure all environment variables Oracle requires, such as ORACLE_HOME, ORACLE_SID, and LD_LIBRARY_PATH (or equivalent, if necessary), are set up in the environment of each user who will be accessing the Connector (see your Oracle documentation for the list of variables required). Verify Oracle's setup and configuration using an Oracle tool, such as SQL*Plus. Also verify that the PATH references the necessary Oracle subdirectories.
The following sections describe the steps you must take before you begin using Database Connector for Oracle on a new system:
The following sections describe the installation process on Windows.
The Oracle RDBMS, version 9i or later, must be installed and configured prior to the installation of Database Connector for Oracle. Consult the Oracle documentation if you have any questions regarding this step.
This section details installation instructions for Database Connector for Oracle on your Windows machine.
Note: If users are using Oracle client software to connect to a database not on the current machine, they must set up an alias for the database they are connecting to using Oracle's "Net Configuration Assistant" or a similar tool.
With appropriate installation settings and procedures, Connector for Oracle communicates with versions 9i or later of Oracle. To install Database Connector for Oracle, perform the steps in the link below.
The Connector makes use of cursor caching to process Oracle RDBMS data efficiently. A cursor is a prepared query, saved in a parameterized format. When a like function is to be performed on a different record (such as a GET NEXT), the task can be performed by executing the saved query with new parameters. This improves performance by eliminating the need to regenerate the query. You may need to adjust the number of cursors available to your COBOL application.
The Connector must be able to connect to a user account. You may set up either one general account for all users or an account for each individual user. To set up an account, you must have database administrator (DBA) privileges.
Note: You may opt to use Oracle's Enterprise Manager on Windows platforms to simplify the login process. If you decide to use the Enterprise Manager, please skip this section, and refer to the Enterprise Manager documentation.
If you want to specify a database login name other than the system login name, you can use configured login. The SQL command syntax to enroll a new user by configured login is:
GRANT CONNECT, RESOURCE TO db_login_name IDENTIFIED BY password
where db_login_name is the name you want to use to log in to the database.
For more details on login, see your site's DBA and the Oracle Database Administrator's Guide.
The user's account should have been set up correctly to access the Oracle RDBMS system. This includes environment variables such as ORACLE_HOME and ORACLE_SID. See your Oracle documentation for more details.
In addition to the variables required for Oracle, you must do the following:
How to...SET ENVIRONMENT ORA_PASSWD TO user-entryIf you are opening an existing file, all file systems linked into the run-time system are searched for the named file. If, however, you are creating a new file, you must tell the run-time system which file system to use. You accomplish this with one of two ACUFH configuration file variables. The first is:
DEFAULT_HOST filesystem
This designates the file system to be used for newly created files that are not individually assigned. For example,
DEFAULT_HOST ORACLE
means that all new files will be Oracle files unless otherwise specified by the second ACUFH configuration variable, which is:
filename_HOST filesystem
where filename is the file name, without any extension, named in the ASSIGN TO clause of your SELECT statement. This ACUFH configuration variable is used to assign an individual data file to a file system. Any file so assigned will use the designated file system, and not the one specified by DEFAULT_HOST. For example,
myfile_HOST ORACLE
means that myfile will be under the reUZE Developer Oracle file system.
You can use these ACUFH configuration file variables in combination to assign your new files in a default with exceptions manner. For example, this set of entries:
DEFAULT_HOST ORACLE afile_HOST DB2 bfile_HOST DB2
means that all new files except afile and bfile will be assigned to Oracle, and those two files will be assigned to DB2.
You can change the values of these variables during program execution by including in your code:
SET ENVIRONMENT "filename_HOST" TO filesystem
or
SET ENVIRONMENT "DEFAULT_HOST" TO filesystem
This enables you to change file systems during the execution of your program. (This is not the typical way to specify a file system; normally it is designated in the ACUFH configuration file and is not changed in the COBOL program.)
Note: Database Connector for Oracle allows you to create a table with an OPEN OUTPUT statement, just as you can create indexed files. The Oracle equivalent of a reUZE Developer data file is a table, not a database. You must create a database for your Oracle tables before you run the COBOL program that creates the tables, just as you must create a directory for your files before you run a COBOL program that creates COBOL data files.
If you own a file because you created it with OPEN OUTPUT from COBOL, or because you created it using SQL, you don't need to do anything more to access the file. However, if you want to access files that are owned by other users on the system and do not have public synonyms, you must provide the run-time system with information on how to locate these files.
The USER_PATH ACUFH configuration variable is used by the run-time system to locate files. The syntax for this variable is:
USER_PATH user1 [user2]...
where the user argument may be either the name of a user on the system or a period (.), which indicates the files owned by yourself. For example, if you have the following settings:
ORA_LOGIN = OPS$ORACLE ORA_PASSWD = DATA_FERIT USER_PATH = barbara scott.
and then attempt to open for I/O the file myfile, the run-time system uses the following search path:
OPEN I-O barbara.myfile OPEN I-O scott.myfile OPEN I-O ops$oracle.myfile
Remember that another option for allowing access to a file to all users on the system is to create a public synonym for the file.
USER_PATH configuration variable
Issuing a COMMIT WORK database command terminates a transaction and makes all work permanent and accessible to other users. The Database Connector for Oracle run-time system issues this command automatically when no locks are held, but you can cause it to happen under other conditions by setting the value of the COMMIT_COUNT ACUFH configuration file variable.
For direct control over the transaction logging facility in Oracle, use the transaction management features available with the Compiler.
Note: You are now ready to prepare and compile your COBOL program and run the demonstration program. For additional information, see the section "Preparing and Compiling Your COBOL Program" in the chapter Working with COBOL and the section "The Demonstration Program" in the chapter Getting Started..
COMMIT_COUNT configuration variable
Preparing and Compiling Your COBOL Program
Instant Client, a free download from Oracle, allows you to run your applications without installing the standard Oracle client or having an ORACLE_HOME.
Note: Setting up Oracle Instant Client can be challenging and should be performed by advanced users only.
To use Oracle's Instant Client with Database Connector for Oracle, download the "Basic Lite" and "SQL*Plus" packages from Oracle. Follow Oracle's instructions to install and configure the products and test their connection using SQL*Plus before proceeding to work with Database Connectors.
Once you have completed this process, test the installation.
As you prepare to work with Database Connector for Oracle, you may find it helpful to understand the rules around filename interpretation and to understand how the names of tables and eXtended File Descriptor (XFD) files are formed and work together. (For detailed information on XFDs, see the chapter XFDs.
When the Compiler generates XFD files, it uses lowercase letters to name the XFD file. In addition, the Compiler changes hyphens to underscores when naming the XFD file.
Through ACUFH configuration variables, the run-time system translates the file name in the COBOL program into the filename that is passed to the open() function in the run-time system. The open() function determines which file system to pass the request to, but does not change the name of the file.
At this point, the Connector translates the file name to uppercase letters and changes hyphens to underscores. This "new" name is the one that the Connector will use in the future for references to the database table.
Before running Database Connectors, you must set the A_CONFIG environment variable to the name of the ACUFH configuration file. You may want to make and use a personalized copy of the ACUFH configuration file to avoid impacting other users. You could use the A_CONFIG environment variable to identify a personal ACUFH configuration file. For example:
SET A_CONFIG=directory name\file name
defines a configuration file with the name of your choice in the directory of your choice.
This section lists the ACUFH configuration file variables that are specific to Database Connector for Oracle. ACUFH configuration file variables that are generally applicable to any RDBMS with which the Connector communicates are discussed in the section "ACUFH Configuration Variables" in the chapter Run-time Configuration. For detailed descriptions of these variables, go to the Reference > Configuration Variables section of your Database Connectors documentation.
For information on dynamic redirection to a custom file handler, see the section Enabling Dynamic Redirection to a Custom File Handler.
| Variable | Description |
|---|---|
| A_ORA_DATABASE | Specifies the name of a network service that you have set up to refer to a database |
| A_ORA_HINTS | Enables the Oracle Hints feature |
| A_ORA_MAX_FILE_CURSORS | Allows you to set the maximum number of cursors that the run-time system is allowed to have per file |
| A_ORA_NLS_SORT | Forces the sort order of returned records to be in a binary order |
| A_ORA_WAIT_LOCK | Determines the type of Oracle locks used: wait or nowait |
| A_ORACLE_ERROR_FILE | Allows you to map errors using a text file to supplement the default method of providing errors |
| COMMIT_COUNT | Indicates the conditions under which you want to issue an automatic COMMIT_WORK operation |
| ORA_LOGIN | Indicates the user name under which you want to connect to the database system |
| ORA_PASSWD | Set to the password assigned to the database account associated with the user name specified by ORA_LOGIN |
| USER_PATH | Indicates the user name or names to be used when the Connector searches for files |
The database table is built and accessed automatically when the COBOL application is executed. To the end user, the interaction between the COBOL and the Oracle database is transparent, as though they were part of one process.
You also can access the database information directly from Oracle, at your option.
To create a sample COBOL application, see the section Creating a COBOL Application.
OPEN ALLOWING READERS is not supported by Oracle. You determine in your ACUFH configuration file how this phrase will be interpreted. Set the variable STRENGTHEN_LOCKS to "1" to cause this phrase to be treated as OPEN ALLOWING NO OTHERS. Set the variable to "0" to cause the phrase to be treated as OPEN ALLOWING ALL. The default value is "0".
Transactions are enforced in Oracle. Within a transaction, records locked by I/O operations that modify, delete, or otherwise result in a COBOL record-lock, remain locked until a COMMIT WORK or a ROLLBACK WORK is issued, or until your COBOL program encounters a COMMIT or ROLLBACK verb. (See the description of the COMMIT_COUNT ACUFH configuration variable.)
Oracle does not support record encryption, record compression, or alternate collating sequences. You may include these options in your program; however, they will be disregarded if they are specified.
Use utilities supplied by Oracle.
Whenever you are using the library routine RENAME, you must specify that you are using indexed files. This information is passed by the value "I" in the fourth parameter.
Unless you have DBA privileges, or special privileges provided by Oracle Version 9i, you cannot:
COMMIT_COUNT configuration variable
The following limits exist for the Oracle 9i file system:
| Maximum indexed key size: | 250 bytes |
| Maximum number of fields per key: | 16 |
| Maximum number of columns: | 254 |
| Maximum length of a char field: | 255 |
Note: The limits and ranges differ between releases of Oracle.
Database Connector for Oracle supports the following data types.
| COBOL | Oracle |
|---|---|
| PIC X | CHAR |
| PIC X(n) | CHAR(n) |
| PIC X(n) | VARCHAR2 1 |
| PIC X(n) | RAW(n) 2 |
| PIC 9(n) | NUMBER(n) |
| PIC 9(n) | RAW(n) 2 |
| PIC 9(n)V9(m) | NUMBER(n+m, m) |
| example: PIC999V99 | NUMBER(5,2) |
Footnotes:
Indicates that the VAR_LENGTH directive is required.
Indicates that the BINARY directive is required.
| Oracle | COBOL |
|---|---|
| CHAR | PIC X |
| CHAR(n) | PIC X(n) |
| VARCHAR2 | PIC X(n)3 |
| DATE | PIC 9(6) or PIC 9(8) |
| NUMBER(n) | PIC 9(n) |
| NUMBER(n, m) | PIC 9(n-m)V9(m) |
| RAW(n) | PIC X(n) or PIC 9(n) 4 |
| example: NUMBER(4,3) | PIC9V999 |
Footnotes:
Indicates that the VAR_LENGTH directive is required.
Indicates that the BINARY directive is required.
Internally, Database Connector for Oracle uses VARCHAR to prevent key fields that are all spaces from being converted to null.
These Oracle data types are not currently supported:
Other limits may also apply.
This section lists the Database Connectors error messages that could occur during execution of your program. For information on compile-time errors and several methods for retrieving run-time system errors, see the chapter Performance and Troubleshooting.
Follow a message's link to see its explanation and a recommended recovery procedure.
Run-time system errors will have this format:
9D,xxxx
The "9D" indicates a file system error and is reported in your FILE STATUS variable. The xx is a secondary, database-specific error code. These are the secondary errors reported directly from Database Connector for Oracle. For a detailed description of a particular message, go to Reference > Error Messages in your Database Connectors documentation.
| Error Messages |
|---|
| 9D,05 Too many fields in the key (more than 16 for Oracle) |
| 9D,1001 Invalid Cursor |
Performance and Troubleshooting
This section contains some questions and answers specific to Database Connector for Oracle. For additional questions and answers that pertain to the Database Connectors family of products, see the chapter General Questions and Answers.
When I try to open a file for output, I get the error 9D,904 Invalid Column Name. Why?
One of your record's data items probably has the same name as an Oracle reserved word. Locate the column by comparing a file trace of the CREATE TABLE to Oracle's list of reserved words and then apply the NAME XFD directive to the column in question.
I created a table the last time I ran the application, and now I can't find it. Why?
This is probably a file (table) ownership problem. If you were running the application under a different user name when you created the file, that table may not be visible unless the creating user's name is listed in your USER_PATH ACUFH configuration variable.
Can the Connector support a full datetime format?
The finest time granularity that Oracle supports is one second. Fractions of a second are not supported. In order to achieve this, you must be sure the "xfd date" code is correct and specify a date-time string in your COBOL application, as opposed to just a date. For additional information, see the reference topic "Date XFD directive."
Are there any reUZE Developer library routines that do not work with or would not make sense to use with the Connector?
Yes. There are two reUZE Developer library routines that either don't work with or do not make sense to use with Connector for Oracle: C$COPY and C$RECOVER.
Copyright © 2009 Micro Focus (IP) Ltd. All rights reserved.