Chapter 7: New and Existing Databases

This chapter tells you how to handle your data in both existing and new databases.

Databases

You'll probably find yourself in one of two situations as you begin to use Database Connectors™. In situation one, the database files don't exist yet and will be brand new. In situation two, the database files already exist, and you want to access that existing data from a COBOL application. There may or may not already be existing COBOL programs that access the database.

Each situation brings up its own questions. How do I declare my COBOL data so that it matches the database data? Are there any special fields in the database that must be accessed in a special way? How do I choose COBOL record names, field names, index names, and data types that best conform to the Compiler, Database Connectors, and the database engine? We'll ask and answer questions of that type in the next few sections.

Default Database Connectors Behavior

The following are automatically handled by Database Connectors:

Conversion of upper-case field names to lower case (or vice versa) when necessary, so don't concern yourself with case differences
Data conversions needed to match the internal storage formats used by the database
Creation of database files, if needed, when you execute an OPEN OUTPUT verb. If this is your situation, you'll have no concerns about matching COBOL fields to database fields. The fields will match perfectly, because the database fields will be based on your COBOL code. As an alternative, however, you can use your database query tool to create a database.
How to...
Sequencing of data returned from a database: you may encounter unexpected sequencing. If your COBOL definition does not include alternate keys with duplicates, the data is returned in key order. If your COBOL definition does include alternate keys with duplicates, be aware that Database Connectors cannot control the sequence in which the data is returned. The database query optimizer decides how to order the returned records. For a set of records with the same key value, the records may not be in historical order

Accessing Existing Database Files

If you are accessing existing data in a relational database, you need to know how to declare your data so that it will match the database fields.

If your COBOL code isn't written yet, you should follow the guidelines given in the appendix of this manual for your specific relational database management system (RDBMS).

If your COBOL application already exists, you can make necessary adjustments by adding XFD directives to your code. XFD directives are comments that guide the creation of the eXtended File Descriptors (XFDs). The XFDs in turn help map the COBOL fields to their equivalent database fields. XFD directives are described in the chapter Using XFD Directives, and XFDs are described in the chapter XFDs.

How Do I Match Existing Text Fields?

To access character data, you simply declare the field as PICTURE X, with as many X's as appropriate.

How Do I Match Existing Numeric Fields?

Numeric fields are more database-specific than text fields. Most relational database systems accommodate INTEGER, DECIMAL, and DATE types. For information on the handling of other numeric types, see the appendix containing information specific to your RDBMS.

Field Names

If your COBOL application already exists, and if it must access a relational database that already exists, you may have to work around differences in the names of the fields, as well as naming conventions imposed by the RDBMS. For example, your program might use the name EMPLOYEE-NO, while the database uses the name EMP-NUMBER for the same item of information.

Resolving Name Conflicts

If naming differences exist, you need not rename your COBOL fields and you need not change the database. This is because the CREATEXFD directive builds XFDs that map your COBOL fields to the correct database fields. The mapping is automatic if the names are the same. If the COBOL name differs from the database name, you enable the Compiler to make the correct mapping by adding a NAME XFD directive to your COBOL code.

You can also use XFD directives to produce other effects when data is mapped from COBOL to the database. XFD directives are described in detail in the chapter Using XFD Directives.

Index Names

Database Connectors uses a specific naming convention for indexes it creates. Indexes created with Database Connectors use the i<TABLENAME>_<key value> convention. If you are using indexes created outside of the Database Connectors application, you need to make sure the index names match the Database Connectors naming convention.

Performance is affected by the number of indexes you may have. If you use tables with multiple indexes, keep in mind that when a record is written or updated, locks are put onto all of the indexes, and they are all basically rewritten during the course of the write/update. This is a costly process. There may be multiple columns per index, and multiple indexes per table. Each rewrite implies a certain amount of wait time. Tables with a large number of indexes can be slow on writes/updates and possibly other operations in the database query optimizer become confused.

For more information about indexes and performance issues, see the chapter Performance and Troubleshooting.