How to...This appendix provides you with the information you need that is specific to Database Connector™ for DB2.
A quick overview of some basic design concepts underlying the DB2 relational database management system (RDBMS) will help you interface your COBOL program to it.
The DB2 RDBMS is a transaction-based system. All of the work that you perform while using DB2 must occur within a transaction, whether that work is being done through the Database Connector for DB2 or another 4GL application. When you use Database Connector for DB2, 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 DB2. Because all operations with Database Connector for DB2 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 DB2'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 in a manner similar to when the LOCKS ON MULTIPLE RECORDS clause in COBOL is used. This can best be illustrated by an example:
Note that the first application is not locked out. A process can read its own locked records.
Your DB2 database may be set up for wait-level locks. The DB2 universal database products have the ability to time-out a lock and send an error return code to the waiting application. See your database administrator for details. (This SQL code would be placed in ERROR_MAP_FILE.)
The Connector provides semi-automated ways to handle transaction logging based on the setting of the 4GL_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:
When you set this variable to zero, 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 4GL_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 4GL_COMMIT_COUNT is set to "1", two alternate ways to perform a commit or rollback are available:
4GL_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 DB2. 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 tailored specifically 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.
For additional information, see the section "Record and Table Locking."
A_DB2_ERROR_MAP_FILE configuration variable
4GL_COMMIT_COUNT configuration variable
The following topics list the steps you must perform before you begin using Database Connector for DB2 on a new system.
Using Database Connector for DB2 on a new system.
The DB2 RDBMS, version 8.2.0 or higher, must be installed and configured prior to the installation of Database Connector for DB2. Consult your DB2 documentation if you have any questions regarding this step.
You also should have the client software installed and configured, along with any database aliases, before installing Database Connector for DB2.
Note: Database Connector for DB2 requires DB2 client software version 6.01 or higher on the client machine. The bin directory of your DB2 client software must be in your PATH.
The cblconfi.db2 file is a sample ACUFH configuration file. This file demonstrates the following:
Modify the cblconfi.db2 file to configure your individual login, password, and database (or database alias). These ACUFH configuration variables are:
The sample error mapping file is placed in the /etc subdirectory of your Database Connector for DB2 distribution. For additional information, see the section "ACUFH Configuration File Variables."
Note: Be sure to set the PATH to find ERROR_MAP_FILE.
ACUFH Configuration File Variables
You must use the sample ACUFH configuration file, described in the section "Sample ACUFH Configuration File," and error map file as your template. These files contain important settings for DB2:
In addition to setting the variables required for DB2, you will need to perform certain steps.
A_DB2_USE_CHAR_FOR_BINARY configuration variable
Sample ACUFH Configuration File
When your COBOL application opens an existing file, most file systems linked into the run-time system will be searched for the named file. However, each time the COBOL application creates a new file, it needs to know which file system to use. You provide the name of the file system with one of two run-time configuration file variables. The first is:
DEFAULT_HOST filesystem
This will designate the file system to be used for newly created files that are not individually assigned. For example,
DEFAULT_HOST DB2
means that all new files will be DB2 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 different file system. Any file so assigned will use the designated file system, and not the one specified by DEFAULT_HOST. For example,
myfile_HOST MFDATA
means that myfile will be under the reUZE Developer file system, and:
myfile_HOST DB2
means that myfile will be under the DB2 file system. The ability to designate a different file system for certain files enables you to tailor your application to a specific customer's needs or to implement an incremental conversion for a customer. With relational databases, this is particularly useful in that it allows you to tune an application for processing speed and resource requirements.
You can use these run-time configuration file variables in combination to assign your new files in a default with exceptions manner; for example, this set of entries:
DEFAULT_HOST MFDATA afile_HOST DB2 bfile_HOST DB2
means that all new files except afile and bfile will be assigned to reUZE Developer data, and those two files will be assigned to DB2.
You can also 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 run-time configuration file and is not changed in the COBOL program.
Note: Database Connector for DB2 allows you to create a DB2 table with an OPEN OUTPUT statement, just as you can create indexed files. The DB2 equivalent of an indexed file is a table, not a database. You must create a database for your DB2 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 indexed files.
You must tell the run-time system which data source to use for your DB2 file. You accomplish this by setting the ACUFH configuration variable A_DB2_DATASOURCE. You can set this variable in your COBOL ACUFH configuration file if you will be using only one data source.
If you don't know the data source name in advance, or if you intend to use more than one data source, you may set the data source name dynamically at run time. In your COBOL program, you would add code similar to this prior to the statement that opens the file:
SET ENVIRONMENT "A_DB2_DATASOURCE" TO "DB2 Database"
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.
A_DB2_DATASOURCE configuration variable
As you prepare to work with the Connector, 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 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.
The Connector translates the name to uppercase letters and changes hyphens to underscores. File extensions will be stripped, based on the setting of 4GL_IGNORED_SUFFIX_LIST. This "new" name is the one that the Connector will use in the future for references to database tables.
The Connector reads the decimal point character from the environment variable DECIMAL_POINT. If DECIMAL_POINT is set, the Connector uses that character. If the variable is not set, the Connector uses the decimal character that is encoded in the XFD file.
The two most common decimal indicators are the period “.”and the comma “,” characters. The comma is used often in European code and is often indicated in COBOL programs by the "DECIMAL POINT IS COMMA" clause.
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 run-time configuration file variables that are specific to Database Connector for DB2. 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, see 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.
To create a sample COBOL application, see the section Creating a COBOL Application.
| Variable | Description |
|---|---|
| A_DB2_ALTERNATE_COMMIT_LOGIC | Determines how the interface will respond to the setting of 4GL_COMMIT_COUNT |
| A_DB2_CATALOG | Indicates the catalog name to be used when the Connector searches for objects in the database |
| A_DB2_DATASOURCE | Set to the exact name of the host data source |
| A_DB2_ERROR_MAP_FILE | Allows you to map errors using a text file to supplement the default method of providing errors |
| A_DB2_ISOLATION_LEVEL | Sets the isolation level |
| A_DB2_LOCK_METHOD | Specifies the locking method that the Connector should use when accessing your data source |
| A_DB2_LOGIN | Indicates the user name under which you want to connect to the database system |
| A_DB2_PASSWD | Set to the password assigned to the database account associated with the user name specified by A_DB2_LOGIN |
| A_DB2_STRICT_EQUAL | Causes the interface to stop fetching data when it reaches the end of data specified by a SELECT statement |
| A_DB2_TABLE_TYPES | Specifies a table type that should be looked for when selecting a database table |
| A_DB2_USE_CATALOG | Enables you to use the catalog in the actual SQL queries |
| A_DB2_USE_CHAR_FOR_BINARY | Lets you store data that uses the BINARY XFD directive as hexadecimal encoded CHAR types |
| A_DB2_USE_SQLCOLUMNS | Causes the API function call SQLDescribeCol() to be used instead of SQLColumns() |
| A_DB2_USE_SQLTABLES | Builds a test SQL query and uses the API function call SQLNumResultTables() to determine if a table exists |
| USER_PATH | Indicates the user name or names (schemas) to be used when the Connector searches for files |
4GL_COMMIT_COUNT configuration variable
With the Connector, records are locked in a Wait mode. An application trying to read a locked record will hang until the record becomes available.
DB2 Universal Database products have the ability to time-out a lock and send an error return code to waiting applications. See your database administrator for additional information.
The following limits exist for the DB2 protocol:
| Maximum number of columns per key: | 16 |
| Maximum number of columns: | 500 |
| Maximum index size: | 255 |
| Maximum record size: | 4005 |
Note that these limits may be further constrained by your database configuration. For example, the size of your page affects the maximum row size. Some limits are given here, but please refer to your database documentation for additional information.
| Page Size | Maximum Row Size | Maximum Column Count | Maximum Index Size |
|---|---|---|---|
| 4K | 4,005 | 500 | 255 |
| 8K | 8,101 | 1,012 | – |
| 16K | 16,293 | 1,012 | – |
| 32K | 32,677 | 1,012 | – |
To achieve the same sort or retrieval sequence under DB2 as under the reUZE Developer file system, place a BINARY XFD directive immediately before each key field that contains signed numeric data. High values and low values can cause problems in key fields. If you want data that uses the BINARY XFD directive to be stored as hexadecimal encoded CHAR types, you can specify A_DB2_USE_CHAR_FOR_BINARY in the ACUFH configuration file.
DB2 data types must be mapped to COBOL data types. In the table below, please note that XFD directives have been applied to the COBOL data types. For additional information on XFD directives, see the chapter Using XFD Directives.
| COBOL Data Type | Database Connector for DB2 |
|---|---|
| Date (DATE XFD directive applied) | date |
| binary (BINARY XFD directive applied) | char (2 * x) |
| varchar (VAR_LENGTH XFD directive applied) | varchar |
| float | float |
| pic 9(4) | smallint |
| pic 9(9) | integer |
| pic 9(10) | decimal(10,0) |
| pic 9(x)v9(y) | decimal (x + y, y) |
| pic x(254) | char |
| pic x(255) | or greater must apply VAR_LENGTH XFD directive |
Sometimes developers are in a situation where they need to create a COBOL file descriptor (FD) based on an existing data source table. The most important thing to understand in this situation is that there is almost nothing that you can do wrong! When the Connector opens a data source table, the only thing it checks is that the column names match the COBOL data names.
When the Connector reads data from the data source, it essentially does a COBOL-style MOVE from the native data type to the COBOL data type, whatever it is. And since most types have a CHAR representation (in other words, you can actually display most data types, using a standard DB2-capable tool), using PIC X(nn) for each column will work perfectly well.
A better general rule is to use a COBOL type that closely matches the data source data type, but don't worry about getting an exact fit. So you can use PIC 9(9) whenever the data source has an INTEGER type.
If you have more information about the data source type, you might be able to use a different COBOL representation. For example, if you know that a particular column in an DB2 data source has values only in the range 0 to 999, you could use PIC 999 for your COBOL data. The COMP-type you use is really determined by your own preferences and should have little bearing on the COBOL data type you choose.
If you want to somehow choose your COBOL data types so that there is a best fit, you can use the following mapping:
| Database Connector for DB2 | COBOL Data Type |
|---|---|
| date | Date (DATE XFD directive applied) |
| varchar | varchar (VAR_LENGTH XFD directive applied) |
| float | float |
| smallint | pic 9(4) |
| integer | pic 9(9) |
| decimal(10,0) | pic 9(10) |
| decimal (x + y, y) | pic 9(x)v9(y) |
| char | pic x(254) |
| pic x(255) or greater must apply VAR_LENGTH XFD directive | pic x(255) |
This section lists the Connector 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,xx
The "9D" indicates a file system error and is reported in your FILE STATUS variable. The xx is a secondary error code. These are the secondary errors reported directly from Database Connectors. For a detailed description of a particular message, go to Reference > Error Messages in your Database Connectors documentation.
| Error Messages |
|---|
| 9D,02 Corrupt dictionary file |
| 9D,04 Too many fields in the key |
| 9D,12 DB2 library function returned an unexpected error |
| 9D,13 Illegal size or type of data for variable xxx |
| 9D,14 More than one table with the same name |
Performance and Troubleshooting
This section contains some questions and answers specific to Database Connector for DB2. For additional questions and answers that pertain to the Database Connectors family of products, see the chapter "General Questions and Answers."
I'm noticing some performance degradation when accessing my DB2 data source. What is the cause of this?
You may notice some performance impact if you were previously accessing indexed files directly. This is because DB2 adds a software layer between your applications and your data sources. In return for minor performance impact, you can reap the benefits of database independence and enhanced portability. Overall performance depends on several factors, including your network configuration and your specific data source.
Are there any reUZE Developer library routines that do not work with or would not make sense to use with Database Connector for DB2?
Yes. The C$COPY and C$RENAME library routines do not work with DB2.
Copyright © 2009 Micro Focus (IP) Ltd. All rights reserved.