Micro Focus Database Connectors for Net Express/Server Express 5.1 Update 14

Welcome to Database Connectors

  • Database Connectors Overview
  • Getting Started

What's New

  • New Features

Featured Sections

  • Extended File Descriptors (XFDs)
  • Working with COBOL
  • Database Connectors for Microsoft SQL Server
  • Database Connectors for Oracle
  • Database Connectors for DB2

Online Resources

  • Micro Focus Infocenter
  • Micro Focus SupportLine
  • Micro Focus Product Updates
  • Micro Focus Knowledge Base
  • Micro Focus Community Forums
  • Micro Focus Training Store

Provide Feedback

  • Contacting Micro Focus
  • Email us feedback regarding this Help


Database Connectors User's Guide

This section describes the concept of Database Connectors interfaces, and how to configure access to relational database management systems from COBOL.

  • Database Connectors Overview
    This section provides an overview of Micro Focus Database Connectors for Net Express/Server Express 5.1 Update 14, and how you can use its interfaces to bridge the worlds of COBOL and Structured Query Language (SQL).
  • What's New
    This section lists the new features in Database Connectors for Net Express/Server Express 5.1 Update 14.
  • Getting Started
    This section is designed to get your started using Database Connectors after your installation.
  • XFDs
    This section explains the importance of Extended File Descriptors (XFDs), and how you understand and create them.
  • Using XFD Directives
    This section explains what XFD directives are, and how you can use them in your COBOL File Descriptors (FDs) to control how your database tables are built.
  • Invalid Data
    This section lists the COBOL data items that are considered invalid, and explains what happens to them when they are stored in the database. This section also covers database items that are considered invalid, and explains how those are translated to COBOL.
  • Working with COBOL
    This section explains how to prepare your COBOL programs for use with Database Connectors.
  • New and Existing Databases
    This section explains how to handle your data in both existing and new databases.
  • File Handling Configuration
    This section explains how to configure COBOL file handling operations to interact with supported RDBMS.
  • Run Time System Configuration
    Database Connectors uses an ACUFH configuration file to determine how the run time system should behave when interacting with data. This chapter describes how to configure the File Handler, and how to use ACUFH configuration variable options to configure the run time system for your relational database environment.
  • Database Connectors for DB2
    This appendix provides information that is specific to Database Connectors for DB2.
  • Database Connectors for Oracle
    This appendix provides information that is specific to Database Connectors for Oracle.
  • Database Connectors for Microsoft SQL Server
    This appendix provides information that is specific to Database Connectors for Microsoft SQL Server.
  • Performance and Troubleshooting
    This section provides some guidelines into improving performance when using Database Connectors. It also provides some reference topics to help troubleshoot some common problems.
  • Questions and Answers
    This section contains some answers to commonly asked questions, which apply to any of the relational database management systems you are using.


Database Connectors Overview

This section provides an overview of Micro Focus Database Connectors for Net Express/Server Express 5.1 Update 14, and how you can use its interfaces to bridge the worlds of COBOL and Structured Query Language (SQL).

It explains how to describe data in your COBOL programs and how to handle differences in the field names and data types that are passed between COBOL and the database engine.

  • Relational Database Management Systems Support
  • Accessing Data
  • Database Concepts
  • How Database Connectors Works
Parent topic: Database Connectors User's Guide


Relational Database Management Systems Support

Database Connectors supports the following Relational Database Management Systems (RDBMS):
  • Oracle
  • DB2
  • Microsoft SQL Server (Windows )
RDBMS Support includes the following:
RDBMS | Latest Versions Tested | Probable compatibility, based on vendor information (Client/Server may be used for connecting to other versions)
Microsoft SQL Server | 2016 | Versions greater than 6.5
Oracle | 12c | Versions greater than 9i
DB2 | 10.5 | Versions greater than 8.1

Database Connectors uses standard COBOL I/O statements to access databases. It handles the translation between standard COBOL and the SQL used by the RDBMS.

Specific instructions and status messages apply to each type of RDBMS, and these are included in their own RDBMS-specific sections.

Parent topic: Database Connectors Overview
Related information
Accessing Data
Database Concepts
How Database Connectors Works
Database Connectors for DB2
Database Connectors for Microsoft SQL Server
Database Connectors for Oracle


Accessing Data

COBOL systems include a standard file system, which can be configured to handle data in a format required by the supported RDBMS. Configuration is made simple, with the setting of the IDXFORMAT Compiler directive or File Handler configuration variable; see Configuring the File Handler for more information.

Extended File Descriptors (XFDs) files are another key component to interfacing successfully with an RDBMS. They are the files that map your COBOL records to database fields. it is important that you understand these, to understand how your COBOL data will be represented in a database. See XFDs for more information.

To then take that information and interface with one of the RDBMS, you need the specific database connector. These connectors are installed as part of your COBOL development installation, but you must license each one individually in order to use it.

Parent topic: Database Connectors Overview
Related reference
Configuring the File Handler
XFDs
Related information
Relational Database Management Systems Support
Database Concepts
How Database Connectors Works


Database Concepts

Relational databases differ from indexed file systems in several significant ways. These are the logical associations between database concepts and COBOL indexed file concepts:

Indexed File Concept | Database Concept
Directory | Database
File | Table
Record | Row
Field | Column

Or, put another way:

  • Database operations are performed on columns. Indexed file operations are performed on records
  • A COBOL indexed file is logically represented in database table format. Within a table, each column represents one COBOL field; each row represents one COBOL record
  • Database table columns are strictly associated with a particular data type, such as date, integer, or character. In COBOL, data can have multiple type definitions

For example, for Oracle, a COBOL record that looks like this:

01  terms-record.
    03  terms-code        pic 999.
    03  terms-rate        pic s9v999.
    03  terms-days        pic 9(2).
    03  terms-descript    pic x(15).

would be represented in the database as a table with a format similar to this:

Name | Null | Type
TERMS_CODE | NOT NULL | NUMBER (3)
TERMS_RATE | – | NUMBER (4, 3)
TERMS_DAYS | – | NUMBER (2)
TERMS_DESCRIPT | – | CHAR (15)
Parent topic: Database Connectors Overview
Related information
Relational Database Management Systems Support
Accessing Data
How Database Connectors Works


How Database Connectors Works

Database Connectors implements a direct, transparent interface between COBOL and RDBMSs.

Previously, accessing a relational database from a COBOL program involved writing SQL code and embedding that code in your program. You had to know SQL, and you had to write SQL statements appropriate for the specific database you wanted to access. Because your queries were tailored to suit one database management system, you had to re-code your COBOL application if you wanted to access a different RDBMS, or an indexed file system, or even to migrate a file from one system to another.

As an alternative, some interface products now translate COBOL I/O statements into direct reads and writes on the database files, without going through the driver, or engine, supplied by the database manufacturer. This means that the COBOL programmer must provide for enforcement of database rules that the engine already knows about and is designed to handle. Bypassing the database engine also means that new constraints or changes in the database structure require reprogramming of the COBOL application.

Database Connectors implements a more suitable approach by dynamically generating industry standard SQL from COBOL I/O statements. As the run time system is executing your COBOL application, Database Connectors is running behind the scenes to match up the requirements and rules of both COBOL and the RDBMS to accomplish the task set by your application. This means that Database Connectors uses the full power designed into the database engine. The engine enforces database rules and constraints; any violations are returned to the COBOL program as I/O error conditions.

  • What Is a Transparent Interface?
  • XFDs and Mapping
  • Summary
Parent topic: Database Connectors Overview
Related information
Relational Database Management Systems Support
Accessing Data
Database Concepts


What Is a Transparent Interface?

Database Connectors provide a transparent, efficient interface between your program and the relational database. The interface is categorized as transparent because the communication between your COBOL program and the database is smooth, with no special query coding on your part and no interruptions in the execution of your program. You need not change your COBOL code if you later want to access a different database or to access an alternate indexed file system.

The information exchange operations between the database and the COBOL program are invisible to the end user. For example, if your program specifies a READ, a database query is automatically generated by the interface. Then the data that is read from the database is translated into a COBOL record. This exchange occurs in fractions of a second, and the application proceeds without interruption.

Parent topic: How Database Connectors Works


XFDs and Mapping

Because relational databases handle I/O at the column (field) level, and COBOL handles I/O at the record level, some mapping is necessary to associate records with their fields. One function of Database Connectors is to map COBOL records into database fields, and to map the database fields back into records. Database Connectors does this by consulting XFDs generated by the Compiler. The detailed structure of XFDs is discussed in How XFDs are Formed.

Database Connectors builds its own database queries dynamically whenever an input or output request is received. You just compile your program, and Database Connectors does the rest.

Parent topic: How Database Connectors Works
Related reference
How XFDs are Formed


Summary

Database Connectors ensures that all database changes are immediately available to your COBOL program. It also ensures that all data updates to your COBOL program are immediately reflected in the database.

After your XFDs are generated, you can switch to a different RDBMS simply by setting the IDXFORMAT Compiler directive or file handling configuration option. No recompiling is necessary.

Because Database Connectors accesses the database through its native engine, the full relational integrity of the database is maintained. The COBOL program need not be concerned about such things as enforcing relationships between keys and foreign keys on tables or constraints on field relationships and contents.

If you have specified that a file is to reside on a RDBMS (by setting IDXFORMAT), an OPEN OUTPUT filename statement in your COBOL program generates a CREATE TABLE filename function on the specified host database. Appropriate permissions are granted based on login and password and access parameters; see the RDBMS-specific sections of this Help for specific details. For large tables, your systems database administrator can create the table and indexes for you. Databases offer many methods for creating tables that may improve performance for large tables, such as spreading the table across multiple hard drives or storing the data and the indexes on different drives.

Changes to the way a database functions should be carefully considered and tested before being fully implemented. If a database violation occurs, the engine detects it, and Database Connectors returns an I/O error condition to the COBOL program. The program can, if desired, call a standard COBOL library routine for an extended error description.

It is possible that there are library routines that do not work with or do not make sense to use with some of the Database Connectors interfaces. To find out if that is the case with your Database Connectors interface, look in the Common Questions and Answers section of the appendix specific to your interface.

Parent topic: How Database Connectors Works
Related reference
Configuring the File Handler


What's New

The following enhancements are available in Database Connectors for Net Express/Server Express 5.1 Update 14:

Product installation and licensing

Database Connectors is no longer installed as a separate AddPack; it is part of your standard development product installation. However, you must still license it separately; you must activate a separate license for each type of database you intend to connect to. See Licensing for more information.

File Handler Configuration

You now configure the File Handler to interact with your chosen database using the IDXFORMAT configuration variable or Compiler directive. See Configuring the File Handler for more details.

Parent topic: Database Connectors User's Guide
Related reference
Configuring the File Handler
Getting Started


Getting Started

For installation instructions specific to the RDBMS you plan to use, see the specific links below. Once you have installed and configured the Database Connectors interface, refer to the Licensing section, and then to test the setup, run the demonstration program.

The Working with COBOL section also contains demonstration programs, which can be compiled and run to produce database tables.

  • Licensing
  • The Demonstration Program
Parent topic: Database Connectors User's Guide
Related reference
Installation and Setup on DB2
Installation and Setup on Oracle
Installation and Setup on Microsoft SQL Server
Working with COBOL


Licensing

Database Connectors is a separately licensed product, distinct from the activation information you received for your development product. You are required to activate a separate license for each type of database you intend to connect to.

You may request a license file for one or more users. The number of users (user limit) is set in your license agreement with Micro Focus. When you receive your products, the package includes codes and keys for every product you ordered; enter these code/key pairs using the Activator utility, which creates the required license files and places them in the required location.

  • The Activator Utility
Parent topic: Getting Started


The Activator Utility

The Activator utility automates the process of creating a license file. When you install your development product, the Activator utility is, by default, installed in the bin sub-directory of your installation directory.

Windows: The executable filename is activator.exe.

UNIX: The executable filename is activator.

Launch the Activator to enter a new or updated product code and key. Note that Activator is case-insensitive and displays only uppercase characters. Activator also ignores embedded spaces and separating characters. License codes and keys do not contain the letters “O” or “I”.

When you have entered your product codes and keys, Activator displays a message to confirm whether license file activation was successful.

Parent topic: Licensing


The Demonstration Program

After your installation and setup are complete, you may run the demonstration program (demo.cbl), which can be found in the Connectors sub-directory within your Samples (Windows) or $COBDIR/demo (UNIX) directory. This program illustrates many of the Database Connectors product capabilities, including how to:

  • Map RDBMS data types to traditional COBOL data types
  • Compile a COBOL program in preparation for interfacing with an RDBMS
  • Modify records within the RDBMS database

The demo program simulates what might be used in a distributor’s shipping department. You'll create a table named orderfile, which contains information about the orders placed by the distributor's customers. The orderfile table has the following columns of information (the database data type and COBOL data type are shown for each).

Note: The data type may be slightly different depending on your RDBMS.
Column Name | Data Type | COBOL PIC
ORDER_NUM | CHAR(4) | pic x(4)
ORDER_DATE | DATE | pic 9(6)
CUSTOMER_NUM | CHAR(3) | pic x(3)
SHIP_INSTRUCT | CHAR(40) | pic x(40)
BACKLOG | CHAR(1) | pic x
PO_NUM | CHAR(10) | pic x(10)
SHIP_DATE | DATE | pic 9(6)
SHIP_WEIGHT | NUMBER(8,2) | pic 9(6)v99
SHIP_CHARGE | NUMBER(6,2) | pic 9(4)v99
PAID_DATE | DATE | pic 9(6)

Here is the COBOL file descriptor (FD) that matches the orderfile table. Note that the FD entries must match the names of the RDBMS fields and must match their data types:

fd  orders.
01  order-record.
    03  order-num              pic x(4).
    03  order-fields.
        05  order-date         pic 9(6).
        05  customer-num       pic x(3).
        05  ship-instruct      pic x(40).
        05  backlog            pic x.
        05  po-num             pic x(10).
        05  ship-date          pic 9(6).
        05  ship-weight        pic 9(6)v99.
        05  ship-charge        pic 9(4)v99.
        05  paid-date          pic 9(6).
Tip: Database Connectors creates new tables in a target database when your COBOL application issues an OPEN OUTPUT command. If you turn on file tracing for a COBOL program that performs this operation on a target file, you can see the series of SQL statements generated by the run time system to create the database table, indexes, and access permissions. This can be useful should you wish to have a set of stand-alone SQL scripts to generate test databases or base set of tables for an end user install for your COBOL files to work with.
Parent topic: Getting Started
Related reference
XFDs
To compile and run the demo program


XFDs

Extended File Descriptors (XFDs) map COBOL records to database fields. These are called XFDs because they're based on standard COBOL File Descriptors (FDs). An XFD file is created as an XML file. The XFD contains a description of a COBOL indexed, sequential, or relative file based on its fields. XFDs are used to interface to database management systems.

  • XFD Files
Parent topic: Database Connectors User's Guide


XFD Files

To create an XFD, use the CREATEXFD directive. When you specify the CREATEXFD directive with no parameters, an .xfd file is created for each indexed data file specified in the compiled program. XFDs are generated in XML format in the latest XFD version available (Version 6). XFDs are stored in your source code directory by default.

Specify all to generate XFDs for all indexed, relative, and sequential files, and specify an XFD path if you want to store your XFDs in a directory other than your source code directory.

Once you compile with the CREATEXFD directive, each COBOL file has a corresponding .xfd file. The directive creates .xfd files without changing anything in the object code.

Creating XFD files at compile time offers two significant advantages:

  • Any changes made to the file definitions are automatically included in the XFDs when the program is recompiled
  • The effects of all compile-time options, COPY REPLACING, and source-code control lines are reflected correctly in the XFDs
  • How XFDs are Formed
  • Defaults Used in XFD Files
  • Summary of XFD Fields
  • Identical Field Names
  • Long Field Names
  • Naming the XFD File
  • Forming the Final XFD Name
  • Mapping Other Files to an XFD
Parent topic: XFDs
Related information
CREATEXFD directive


How XFDs are Formed

XFDs enable the interface to create a table (or access an existing one) in the database for each indexed file. Each column in the table contains the values for one field. The column names are essentially the field names.

The table that is built is based on the largest record in the COBOL file and contains the fields from that record plus any key fields (key fields are those that are named in KEY IS phrases of SELECT verbs in the FILE CONTROL section). This ensures that data from all COBOL records fit within the table and simplifies the storage and retrieval process. If you were to examine the database columns, only the fields from the largest record, and the key fields, would appear. For additional information, see Defaults Used in XFD Files.

Note: If the field named in the KEY IS phrase is a group item, it does not become a column in the database table. Instead, each of the elementary items subordinate to the named group item become a column. You can force a group item to be a column by using the USE GROUP XFD directive.

With multiple record formats (level 01), not all COBOL fields are represented in the database by name, but all fields are storable and retrievable. The XFD maps fields from all records of a file to the corresponding locations in the master (largest) record of the file, and thus to the database table. Since Database Connectors has access to the XFD, it knows where the data from a given COBOL record fits in the database tables. This activity is invisible to the COBOL application.

For example, if your program has one file with the three records shown below, the underlined fields are included in the database table by default (this example assumes that ar-codes-key is named in a KEY IS phrase). Some fields do not appear in the table, but the XFD maps them to the master field names. Database Connectors thus eliminates redundancies for optimum performance.

01  ar-codes-record.
    03  ar-codes-key.
        05  ar-code-type      pic x.
        05  ar-code-num       pic 999.
01  ship-code-record.
    03  filler                pic x(4).
    03  ship-weight           pic s999v9.
    03  ship-instruct         pic x(15).
01  terms-code-record.
    03  filler                pic x(4).
    03  terms-rate-1          pic s9v999.
    03  terms-days-1          pic 9(3).
    03  terms-rate-2          pic s9v999.
    03  terms-descript        pic x(15).

The following diagram shows how Database Connectors creates database columns for some of the fields in the COBOL record, while the XFD maps other fields to those columns; this means that all of the fields are accessible to the COBOL program.

Making Field Accessible to COBOL Programs

See Defaults Used in XFD Files for a description of rules that Database Connectors follows as it builds the database table and an explanation of how you can override those rules when necessary.

Parent topic: XFD Files
Related information
USE GROUP XFD directive
Defaults Used in XFD Files
KEY IS Phrase


Defaults Used in XFD Files

Several elements of COBOL require special handling when XFDs are built. These include multiple record definitions REDEFINES, FILLER, and OCCUR. This section describes how the compiler handles each of these situations.

Note that in many instances you can override the default behavior described below by placing special comment lines in the FDs of your COBOL code. These comments are called XFD directives, and are described in Using XFD Directives. For example, the WHEN XFD directive lets you use multiple definitions for a single set of data by specifying when each definition should be used.

Databases generally do not support the notion of multiple definitions for the same column. As the following paragraphs explain, whenever a COBOL program gives more than one definition for the same data, the compiler chooses which definition to use in the XFD and disregards the rest.

  • KEY IS Phrase
  • REDEFINES Clause
Parent topic: XFD Files
Related information
WHEN XFD directive
Using XFD Directives


KEY IS Phrase

Fields named in KEY IS phrases of SELECT statements are included as column names. Other fields that occupy the same areas as the key fields (by either explicit or implicit redefinition) are not included by name, but are mapped to the key field column names by the XFD.

Remember, if the field named in the KEY IS phrase is a group item, it does not become a column in the database table unless a USE GROUP XFD directive is used See How XFDs are Formed for more information. Instead, the subordinate fields of the group item are used.

Parent topic: Defaults Used in XFD Files
Related information
USE GROUP XFD directive


REDEFINES Clause

Fields contained in a redefining item occupy the same positions as the fields being redefined. The compiler needs to select only one of the field definitions to use. The default rule that it follows is to use the fields in the item being redefined as column names; the XFD maps fields that appear subordinate to a REDEFINES clause to column names.

Multiple Record Definitions

This same rule extends to multiple record definitions. In COBOL, multiple record definitions are essentially redefinitions of the entire record area. This leads to the same complication that is encountered with REDEFINES: multiple definitions for the same data. So the compiler needs to select one definition to use.

Multiple Record Definitions

Because the multiple record types can be different sizes, the compiler needs to use the largest one, so that it can cover all of the fields adequately. Thus, the compiler's rule is to use the fields in the largest record defined for the file. If more than one record is of the largest size, the compiler uses the first one.

Group Items

Note that group items are, by default, never included in an XFD for the same reason that REDEFINES are excluded: they result in multiple names for the same data items. You can, however, choose to combine grouped fields into one data item by specifying the USE GROUP XFD directive.

FILLER Data Items

In a COBOL FD, FILLER data items are essentially place holders. FILLER items are not uniquely named and thus cannot be uniquely referenced. For this reason, they are not placed into the XFD. The XFD maintains the correct mapping of the other fields, and no COBOL record positional information is lost.

Sometimes you need to include a FILLER data item, such as when it occurs as part of a key. In such a case, you could include it under a USE GROUP XFD directive or give it a name of its own with the NAME XFD directive.

OCCURS Definition

An OCCURS clause always requires special handling, because the run time system must assign a unique name to each database column. The run time system accomplishes this by appending sequential index numbers to the item named in the OCCURS clause.

For example, if the following were part of a file's description:

03  employee-table occurs 20 times.
    05  employee-number            pic 9(3)

these column names would be created in the database table:

employee_number_1
employee_number_2
.
.
.
employee_number_10
employee_number_11
.
.
.
employee_number_20

Note that the hyphens in the COBOL code are translated to underscores in database field names, and the index number is preceded by an underscore.

Parent topic: Defaults Used in XFD Files
Related information
USE GROUP XFD directive
NAME XFD directive


Summary of XFD Fields

Fields defined with an OCCURS clause are assigned unique sequential names. Fields without names are disregarded. When multiple fields occupy the same area, the compiler chooses only one of them unless you have a WHEN XFD directive to distinguish them. To choose, the compiler:

  • Preserves fields mentioned in KEY IS phrases
  • Discards group items unless USE GROUP is specified
  • Discards REDEFINES
  • Uses the largest record if there are multiple record definitions
Parent topic: XFD Files
Related information
WHEN XFD directive


Identical Field Names

In COBOL, you distinguish fields with identical names by qualification. For example, there are two fields named RATE in the following code, but they can be qualified by their group items. Thus, you would reference RATE OF TERMS-CODE and RATE OF AR-CODE in your program:

01  record-area.
    03  terms-code.
        05  rate                   pic s9v999.
        05  days                   pic 9(3).
        05  descript               pic x(15).
    03  ar-code.
        05  rate                   pic s9v999.
        05  days                   pic 9(3).
        05  descript               pic x(15).

Database systems consider duplicate names an error. Thus, if more than one field in a particular file has the same name, the XFD is not generated for that file.

The solution to this situation is to add a NAME XFD directive that associates an alternate name with one or both of the conflicting fields.

Parent topic: XFD Files
Related information
NAME XFD directive


Long Field Names

To meet the SQL requirements of some relational database management systems (RDBMSs), the Database Connectors run time truncates long field names to the maximum allowed by the RDBMS. (In the case of the OCCURS clause described above, the truncation is to the original name, not the appended index numbers. However, the final name, including the index number, is limited to the RDBMS maximum.) It's a good idea to make sure that your field names are unique (and meaningful) within the first 18 characters.

The database you are connecting to may allow longer names, and if so, more characters are used. Names longer than 30 characters generate a warning. Instead of allowing default truncation, use the NAME XFD directive to give a shorter alias to a field with a long name. Note that within the COBOL application you can continue to use the original name. The NAME XFD directive affects only the XFD connection to the RDBMS table.

Parent topic: XFD Files
Related information
NAME XFD directive


Naming the XFD File

The compiler needs to give a name to each XFD file that is built. It attempts to build the name from your COBOL code, although there are some instances where the name in the code is nonspecific, and you must provide a name.

Each XFD name is built from a starting name that is derived (if possible) from the SELECT statement in your COBOL code. The following table explains how that occurs.

ASSIGN name is a variable | If the SELECT for the XFD file has a variable ASSIGN name (ASSIGN TO filename), you must specify a starting name for the .xfd file via a FILE XFD directive in your code. This process is described in Using XFD Directives
ASSIGN name is a constant | If the SELECT for the XFD file has a constant ASSIGN name (such as ASSIGN TO COMPFILE), that name is used as the starting name for the .xfd file name
ASSIGN name is generic | If the ASSIGN phrase refers to a generic device (such as ASSIGN TO DISK), the compiler uses the SELECT name as the starting name for the XFD
Parent topic: XFD Files
Related information
FILE XFD directive
Using XFD Directives


Forming the Final XFD Name

From the starting name, the final name is formed as follows:

  • The compiler removes any extensions from the starting name
  • It constructs a universal base name by stripping out directory information that fits any of the formats used by the operating systems that run the compiler
  • It reduces the base name to eight characters and converts it to lower case
  • It appends the letters .xfd to the base name

Examples of XFD names

COBOL code File name
ASSIGN TO "usr/ar/customer.dat"
 customer.xfd
SELECT TESTFILE, ASSIGN TO DISK
 testfile.xfd
ASSIGN TO FILENAME
 (you specify)
Parent topic: XFD Files


Mapping Other Files to an XFD

At run time, it is possible to use a single XFD for files that have different names. For example, suppose a site has customer files that have identical structures but different names (such as CUST0001, CUST0002, and CUST0003). It's not necessary to have a separate XFD for each file, as long as their record definitions are the same.

The individual files can all be mapped to the same XFD via an ACUFH configuration file variable called XFD_MAP. The following paragraphs describe how this works.

Suppose your COBOL application has a SELECT with a variable ASSIGN name, such as customer-file. This variable assumes different values (such as CUST0001 and CUST0002) during program execution.

Before compiling the application, you would use the FILE XFD directive to provide a base name for the XFD. Suppose you provide CUST as the base. The compiler would then generate an XFD named cust.xfd. (The compiler always converts XFD names to lower case.)

To ensure that all customer files, each having a unique name, use this same XFD, enter the following in your ACUFH configuration file:

XFD_MAP   CUST* = CUST

The asterisk ("*") in the example is a wildcard that matches any number of characters. Note that the extension .xfd should not be included in the map. This statement would cause the XFD cust.xfd to be used for all files whose names begin with CUST.

The XFD_MAP variable has this syntax:

XFD_MAP   [pattern = base-xfd-name]  ...

where pattern consists of any valid filename characters and may include "*" or "?". These two characters have special meanings in the pattern:

* | matches any number of characters
? | matches a single occurrence of any character

For example:

CUST???? | matches CUST0001 and CUSTOMER; does not match CUST001 or CUST00001
CUST* | matches all of the above
CUST*1 | matches CUST001 and CUST0001 and CUST00001; does not match CUSTOMER
*OMER | matches CUSTOMER; does not match CUST001 or CUST0001

The XFD_MAP variable is read during the open file stage of any Connector interfaces linked into the run time system.

XFD values can be replaced or added to the end of existing XFD map values by setting the XFD_MAP_RESETS ACUFH configuration file variable. This variable determines whether setting XFD_MAP adds to or replaces the existing value. When this variable is set to 0 (off, false, no), setting XFD_MAP adds new value patterns to the end of the existing value. When XFD_MAP_RESETS is set to 1 (on, true, yes), setting XFD_MAP replaces the existing value with a new value. The default value is 1 (on, true, yes).

This variable may be useful if you need to include multiple XFD_MAP lines in an ACUFH configuration file and want to avoid setting and resetting the variable. When multiple lines exist, all patterns are used in the order they are listed.

Parent topic: XFD Files
Related information
XFD_MAP configuration variable
FILE XFD directive
XFD_MAP_RESETS configuration variable


Using XFD Directives

This section explains what XFD directives are, and how you can use them in your COBOL File Descriptors (FDs) to control how your database tables are built.
  • Introduction
  • XFD Directive Syntax
  • Supported XFD Directives
Parent topic: Database Connectors User's Guide


Introduction

EXtended File Descriptors (XFDs) are based on your COBOL File Descriptors (FDs). Each XFD describes all of the fields for one file.

XFD directives are optional comments that you can use in your FDs to control how things look on the database side. Many applications use only the CREATEXFD directive. XFD directives are most commonly used when a site intends to do a lot of work with the relational database management system (RDBMS) outside of the COBOL application, and wants to control how the database table is built.

XFD directives are special comments placed into an FD in your COBOL source code. They guide the building of the XFDs, which in turn guide the building of the database table.

Each XFD directive includes the letters XFD. These three letters indicate to the compiler that the comment is to be used in XFD generation.

XFD directives provide extended control over how the database table is built. Among other things, they let you:

  • Specify a column name to be used in the database table, in place of a COBOL field name.
  • Map elementary items of a group item together into a single column.
  • Cause numeric COBOL data to be treated as a text string in the database.
  • Cause the fields from a specific record in a file to appear in the database table (rather than just the fields from the largest record).
  • Assign a column the DATE type, providing the built-in functionality that dates have in the RDBMS.
  • Give a name to the XFD file itself.

XFD directives are always placed within a COBOL FD. They do not affect Procedure Division I/O statements, and they do not change your COBOL fields in any way. Rather, they guide the building of the XFDs, giving you control over the way COBOL data maps to database fields.

You can build XFDs directly from your source code with only the CREATEXFD directive if the default mapping rules described in XFDs and New and Existing Databases are sufficient for your situation. Unlike the XFD directives, the CREATEXFD directive can be specified on the command line or within your project properties if you do not want to place the directive in your program. If you would like to override the default mapping behavior or map a field to a different name, you must add additional XFD directives to your COBOL code.

Parent topic: Using XFD Directives
Related reference
XFD Directives
New and Existing Databases


XFD Directive Syntax

Place each XFD directive syntax on a line by itself, immediately before the COBOL line to which it pertains.

Introduce each XFD directive with a $ in the Indicator Area (column 7 in standard ANSI source format), followed immediately by the letters XFD, and then the XFD directive itself. There should be no space between the $ and the XFD. Spaces are permitted elsewhere on the line as separators. For example, the NAME XFD directive looks like this:

$XFD  NAME = EMP-NUMBER

Note that XFD directives are case-insensitive and that hyphens and underscores are considered equivalent. Thus, for example,

$XFD VAR_LENGTH
$XFD VAR-LENGTH
$xfd var_length
$xfd var-length

are all recognized as equivalent.

Two or more XFD directives that pertain to the same line of COBOL code may be combined on one comment line. The XFD directives should be separated by a space or a comma. For example, to specify both USE GROUP and NUMERIC at the same time, you would add this line:

$XFD  USE GROUP, NUMERIC
  • Line Continuation for Complex XFDs
    Syntax for XFD directives that exceed one line.
Parent topic: Using XFD Directives


Line Continuation for Complex XFDs

$XFD directives may be continued onto multiple lines by specifying a single character backslash (\) as the last non-blank character on a line. For example:

$XFD WHEN (var1 = "abc" and var2 = "def"    \
$XFD       and (var3 = "ghi" or var4 = "jkl"))  \
$XFD       or var5 != "xyz"

Line Continuation Rules

  • $XFD directives are continued via a backslash as the last non-blank character on the line.
  • Continued lines begin with $XFD just like the first directive line (with the dollar sign in the indicator column).
  • The final continuation line omits the trailing backslash.
  • "The overall combined directive is a concatenation of all of the continuation lines.
  • The next character after the blank that follows $XFD is the next character of the directive. That character logically replaces the backslash on the previous line.
  • There is no limit on the number of continuation lines other than the limit on total program source lines, and the overall size of the continued directive is limited only by available memory. But it is highly unlikely that one XFD directive would ever need to exceed a few dozen lines or a few thousand characters.
  • In standard ANSI source format the contents of columns 73-80 are ignored, the continuation backslash must appear in Area B (columns 12-72).
  • Backslashes within data (literals) do not cause a problem because they are inside quoted strings (and cannot be the last non-blank character on the line).
  • In-line comments ("*>") are not allowed on continued XFD directive lines.
  • A new error message is generated for a syntax error within continued XFD directive lines and the erroneous continuation lines scanned up to that point are ignored. Possible syntax errors include placing a source line other than a comment line within an intended sequence of continued XFD directive lines, or omitting the final continuation line (the line that begins with $XFD and does not end with a trailing backslash). Other errors are possible when the directive itself is scanned and processed.
Parent topic: XFD Directive Syntax


Supported XFD Directives

The following XFD directives are supported. For complete definitions of these directives, see XFD Directives.

XFD Directive | Brief Description
ALPHA | Allows you to treat a data item as alphanumeric text in the database, when it is declared as numeric in the COBOL program
BINARY | Specifies that the data in the field could be alphanumeric data of any classification
COBOL-TRIGGER | Tells Database Connectors that a COBOL trigger is to be executed
COMMENT | Allows you to include comments in an XFD file
DATE | Stores a field in the database as a date or time
FILE | Supplies a starting name from which the XFD file name is formed
NAME | Assigns a database field name to the field defined on the next line
NUMERIC | Allows you to treat a data item as an unsigned integer when it is declared as alphanumeric
SECONDARY_TABLE | Indicates that the next data item may be placed into a subordinate table
USE GROUP | Allows you to enter a group item into the database as a single field
VAR_LENGTH | Requests that the data item that immediately follows the XFD directive be assigned a type that implies variable length
WHEN | Lets you include multiple record definitions or REDEFINES in a database table
XSL | Allows you to include a stylesheet reference in the XML header
Parent topic: Using XFD Directives
Related reference
XFD Directives


Invalid Data

This section lists the COBOL data items that are considered invalid, and explains what happens to them when they are stored in the database. This section also covers database items that are considered invalid, and explains how those are translated to COBOL.
  • Illegal COBOL Data
  • Invalid Database Data
Parent topic: Database Connectors User's Guide


Illegal COBOL Data

COBOL data (except key data) that is illegal by database standards is stored in the database as null unless you specifically override this default. (Use the ACUFH configuration file variable 4GL_ILLEGAL_DATA to override.)

The following COBOL data usages are considered illegal by the database:

 | Illegal Values
USAGE DISPLAY numbers | LOW-VALUES , HIGH-VALUES, and SPACES
COMP-2 numbers | HIGH-VALUES and SPACES
COMP-3 numbers | HIGH-VALUES

All other numeric types fit one of the preceding cases (depending on their internal storage format)

DATE fields | The value "zero" and any other illegal conditions as defined by the date's USAGE type (see above)
text fields | LOW-VALUES

BINARY numbers are always legal, and all values are legal in BINARY text fields.

  • Invalid Key Data
  • Invalid Data Other Than Keys
Parent topic: Invalid Data
Related information
4GL_ILLEGAL_DATA configuration variable


Invalid Key Data

Any field that is part of a key is never stored as null. (A null key does not have a well-defined position in relation to other keys.) If a key field has an illegal value, the field is stored in the database as described here:

Illegal LOW-VALUES | minimum possible value (0 or -99999...) minimum possible date value (01/01/01)
Illegal HIGH-VALUES | maximum possible value (99999...) maximum possible date value (database dependent)
Illegal SPACES | zero (or 01/01/01 for a date field)
Parent topic: Illegal COBOL Data
Related information
Invalid Data Other Than Keys


Invalid Data Other Than Keys

You have the option to convert illegal data, other than keys, into legal values by setting the configuration variable 4GL_ILLEGAL_DATA to Converted. When this is set, the Database Connectors interface converts data the same way it does for keys.

4GL_ILLEGAL_DATA can also be set to Null to indicate that illegal COBOL data other than keys should be converted to null. This is the default behavior.

Parent topic: Illegal COBOL Data
Related information
Invalid Key Data
4GL_ILLEGAL_DATA configuration variable


Invalid Database Data

When Database Connectors receives a null field from the database, it translates it to COBOL as follows:

Numbers (including dates): | zero
Text (including binary): | spaces

For additional information about the handling of invalid dates, see the DATE XFD directive.

Parent topic: Invalid Data
Related information
4GL_ILLEGAL_DATA configuration variable
DATE XFD directive


Working with COBOL

After the environment has been set up for the Database Connectors interface, you are ready to use the system. This section describes how to prepare your COBOL program and compile it for use with Database Connectors.

  • Examples of Preparing and Compiling a Program
Parent topic: Database Connectors User's Guide


Examples of Preparing and Compiling a Program

The DatabaseConnectors sub-directory within your Examples directory contains three sample programs that are based on the code excerpt below. These programs show different ways of how to manipulate your COBOL records in readiness for being written to a database. Remember to compile them with the appropriate IDXFORMAT directive and accompanying ACUFH configuration file.

Note: If you are not familiar with XFD directives, you may want to read the topics in the Using XFD Directives section before continuing with this topic.

The examples are based upon a purchase orders file from a COBOL program and will be stored in a database. The file descriptor below contains the records that handle all of the information from a fictitious company's purchase orders. Within it are two record types:

  • The purchase order (header) record (there is one of these for each purchase order form)
  • The purchase order detail record (there is one detail record for each line item in a purchase order)

The file is keyed off the purchase order number. This following three examples demonstrate three different approaches to using the COBOL file descriptor and XFD directives:

  • Example 1 (order1.cbl) only includes the largest record in the database table; see Example 1 - Compiling with just the CREATEXFD directive.
  • Example 2 (order2.cbl) uses the WHEN XFD directive, which causes all record formats to be included in the database; see Example 2 - Compiling with the WHEN XFD Directive.
  • Example 3 (order3.cbl) groups together several fields and renames them, for convenience; see Example 3 - Using Additional XFD Directives.
IDENTIFICATION DIVISION.
program-id.  purchase.
ENVIRONMENT DIVISION.
input-output section.
file-control.

select p-o-file
assign to disk "purch1"
organization is indexed
access mode is dynamic
record key is p-o-number
file status is p-o-status.

DATA DIVISION.
file section.
fd p-o-file.
01 p-o-record.
   03 p-o-division-number                pic 9(3).
   03 p-o-record-type                    pic x.
      88 header-record                   value "h".
      88 detail-record                   value "d".
   03 p-o-number                         pic 9(10).
   03 p-o-number-detail redefines
         p-o-number.
      05 picking-ticket-number           pic 9(6).
      05 shipping-region                 pic 9(2).
      05 p-o-customer-type               pic 9(2).
      05 p-o-customer-breakdown redefines
            p-o-customer-type.
         07 customer-category            pic x.
         88 p-o-customer-retail          value "r".
         88 p-o-customer-whlsale         value "w".
         07 customer-pay-format          pic x.
            88 is-net-30                 value "3".
            88 is-net-10                 value "1".
   03 p-o-date.
      05 p-o-yy                          pic 9(2).
      05 p-o-mm                          pic 9(2).
      05 p-o-dd                          pic 9(2).

01 p-o-detail-record.
   03 p-o-dept-number                    pic 9(3).
   03 p-o-record-type                    pic x.
   03 detail-p-o-number                  pic 9(10).
   03 p-o-shipping-info.
      05 p-o-quantity-to-ship            pic s9(4) comp.
      05 p-o-total-quantity              pic s9(4) comp.
   03 p-o-notes.
      05 notes-line occurs 3 times       pic x(40).
working-storage section.
01 p-o-status                            pic x(2).
PROCEDURE DIVISION.
level-1 section.
main-logic.

open output p-o-file.
close p-o-file.
stop run.
  • Example 1 - Compiling with just the CREATEXFD directive
  • Example 2 - Compiling with the WHEN XFD Directive
  • Example 3 - Using Additional XFD Directives
Parent topic: Working with COBOL
Related reference
Using XFD Directives
Related information
Example 1 - Compiling with just the CREATEXFD directive
Example 2 - Compiling with the WHEN XFD Directive
Example 3 - Using Additional XFD Directives


Example 1 - Compiling with just the CREATEXFD directive

This example (order1.cbl) just uses the CREATEXFD directive to generate the XFD. The compiled program builds the database table shown at the end of this section.

The database table builds as follows.
  • First, any fields listed in the KEY IS clause of the SELECT are included (p-o-number in this example).
  • The compiler then takes the largest record (p-o-detail-record) and lists the fields that make up that record.
The rest of this section shows the specific fields that are placed into the table.

All data from the COBOL program is stored in and retrieved from the database, even though not all fields are explicitly named in the database table. For a description of how this works, see XFDs.

The underlined fields are the only ones entered into the table:

fd p-o-file.
 01  p-o-record.
     03  p-o-division-number                   pic 9(3).
     03  p-o-record-type                       pic x.
         88  header-record                     value "h".
         88  detail-record                     value "d".
     03  p-o-number                            pic 9(10).
     03  p-o-number-detail redefines p-o-number.
         05  picking-ticket-number             pic 9(6).
         05  shipping-region                   pic 9(2).
         05  p-o-customer-type                 pic 9(2).
         05  p-o-customer-breakdown redefines
               p-o-customer-type.
             07  customer-category             pic x.
                 88  p-o-customer-retail       value "r".
                 88  p-o-customer-whlsale      value "w".
             07  customer-pay-format           pic x.
                 88 is-net-30                  value "3".
                 88 is-net-10                  value "1".
     03  p-o-date.
         05  p-o-yy                            pic 9(2).
         05  p-o-mm                            pic 9(2).
         05  p-o-dd                            pic 9(2).

 01  p-o-detail-record.
     03  p-o-dept-number                       pic 9(3).
     03  p-o-record-type                       pic x.
     03  detail-p-o-number                     pic 9(10).
     03  p-o-shipping-info.
         05  p-o-quantity-to-ship              pic s9(4) comp.
         05  p-o-total-quantity                pic s9(4) comp.
     03  p-o-notes.
         05  notes-line occurs 3 times         pic x(40).

As the table is built:

  • Any hyphens in the COBOL field names are converted to underscores
  • Field names longer than 18 characters generate a warning if the first 18 characters are not unique. Some databases limit you to 18 total characters. Use the least number of characters for optimal portability and end-user efficiency. You can use the NAME XFD directive for this to avoid changing your procedure division code.
  • Fields in OCCURS clauses get special handling, because the run time system must assign a unique name to each data item. Sequential index numbers are appended to the item named in the OCCURS. Limits vary between databases. See to the documentation for your RDBMS. In some instances, the name may be truncated by the Connector if necessary before the index is added.
Note: detail-p-o-number is not part of the table because the key overlays this area.

The following table is built in the database:

Column Name | Type
p_o_number | number(10)*
p_o_dept_number | number(3)*
p_o_record_type | char(1)
p_o_quantity_to_ship | number(4)*
p_o_total_quantity | number(4)*
notes_line_1 | char(40)
notes_line_2 | char(40)
notes_line_3 | char(40)

* The actual database data type may vary.

See the Limits and Ranges topic for your specific RDBMS for a list of supported data types and their COBOL equivalents.

Parent topic: Examples of Preparing and Compiling a Program
Related reference
XFDs
NAME XFD directive
Related information
Example 2 - Compiling with the WHEN XFD Directive
Example 3 - Using Additional XFD Directives


Example 2 - Compiling with the WHEN XFD Directive

This example (order2.cbl) enables you to override the default table build; it allows you to force certain columns to be built that would be built by default. Add the WHEN XFD directive in front of each record, which results in the underlined fields being entered into the table:

fd p-o-file.
$xfd when p-o-record-type = "h"
 01  p-o-record.
     03  p-o-division-number                       pic 9(3).
     03  p-o-record-type                           pic x.
         88  header-record                         value "h".
         88  detail-record                         value "d".
     03  p-o-number                                pic 9(10).
     03  p-o-number-detail redefines p-o-number.
         05  picking-ticket-number                 pic 9(6).
         05  shipping-region                       pic 9(2).
         05  p-o-customer-type                     pic 9(2).
         05  p-o-customer-breakdown redefines
               p-o-customer-type.
             07  customer-category                 pic x.
                 88  p-o-customer-retail           value "r".
                 88  p-o-customer-whlsale          value "w".
             07  customer-pay-format               pic x.
                 88  is-net-30                     value "3".
                 88  is-net-10                     value "1".
     03  p-o-date.
         05  p-o-yy                                pic 9(2).
         05  p-o-mm                                pic 9(2).
         05  p-o-dd                                pic 9(2).
$xfd when p-o-record-type = "d"
 01  p-o-detail-record.
     03  p-o-dept-number                           pic 9(3).
     03  p-o-record-type                           pic x.
     03  detail-p-o-number                         pic 9(10).
     03  p-o-shipping-info.
         05  p-o-quantity-to-ship                  pic s9(4) comp.
         05  p-o-total-quantity                    pic s9(4) comp.
     03  p-o-notes.
         05  notes-line occurs 3 times             pic x(40).
Note: p-o-record-type is entered into the table only once. The detail-p-o-number field is not part of the table, because the key overlays this area.

The following table is built in the database:

Column Name | Type
p_o_division_number | number(3)*
p_o_record_type | char(1)
p_o_number | number(10)*
p_o_yy | number(2)*
p_o_mm | number(2)*
p_o_dd | number(2)*
p_o_dept_number | number(3)*
p_o_quantity_to_ship | number(4)*
p_o_total_quantity | number(4)
notes_line_1 | char(40)
notes_line_2 | char(40)
notes_line_3 | char(40)

* The actual database data type may vary.

Note: When p_o_record_type is h, p_o_dept_number, p_o_quantity_to_ship, p_o_total_quantity, and notes_lines_1, 2, and 3 become NULL. When p_o_record_type is d, p_o_division_number, p_o_yy, p_o_mm and p_o_dd become NULL.
Parent topic: Examples of Preparing and Compiling a Program
Related reference
WHEN XFD directive
Related information
Example 1 - Compiling with just the CREATEXFD directive
Example 3 - Using Additional XFD Directives


Example 3 - Using Additional XFD Directives

This example (order3.cbl) demonstrates how to streamline the code to introduce the following changes:

  • Apply the USE GROUP XFD directive to the notes to group them, which improves execution speed and clarity. Accessing each note line individually from the database side is not required.
  • Rename the notes field for convenience.
  • Apply the USE GROUP and DATE XFD directives to the date, to give it all the properties of a date on the database side.
fd p-o-file.
$xfd when p-o-record-type = "h"
 01  p-o-record.
     03  p-o-division-number                      pic 9(3).
     03  p-o-record-type                          pic x.
         88  header-record                        value "h".
         88  detail-record                        value "d".
     03  p-o-number                               pic 9(10).
     03  p-o-number-detail redefines p-o-number.
         05  picking-ticket-number                pic 9(6).
         05  shipping-region                      pic 9(2).
         05  p-o-customer-type                    pic 9(2).
         05  p-o-customer-breakdown redefines
               p-o-customer-type.
             07  customer-category                pic x.
                 88  p-o-customer-retail          value "r".
                 88  p-o-customer-whlsale         value "w".
             07  customer-pay-format              pic x.
                 88  is-net-30                    value "3".
                 88  is-net-10                    value "1".
$xfd use group, date
     03  p-o-date.
         05  p-o-yy                               pic 9(2).
         05  p-o-mm                               pic 9(2).
         05  p-o-dd                               pic 9(2).
$xfd when p-o-record-type = "d"
 01  p-o-detail-record.
     03  p-o-dept-number                          pic 9(3).
     03  p-o-record-type                          pic x.
     03  detail-p-o-number                        pic 9(10).
     03  p-o-shipping-info.
         05  p-o-quantity-to-ship                 pic s9(4) comp.
         05  p-o-total-quantity                   pic s9(4) comp.
$xfd use group, name = notes
     03  p-o-notes.
         05  notes-line occurs 3 times            pic x(40).
Note: p-o-record-type is entered only once into the table. The detail-p-o-number field is not part of the table, because the key overlays this area.

The following table is built in the database:

Column Name | Type
p_o_division_number | number(3)
p_o_record_type | char(1)
p_o_number | number(10)
p_o_date | date
p_o_dept_number | number(3)
p_o_quantity_to_ship | number(4)
p_o_total_quantity | number(4)
notes | char(120)
Parent topic: Examples of Preparing and Compiling a Program
Related reference
DATE XFD directive
USE GROUP XFD directive
Related information
Example 1 - Compiling with just the CREATEXFD directive
Example 2 - Compiling with the WHEN XFD Directive


New and Existing Databases

You may encounter one of two situations as you begin to use Database Connectors. In situation one, the database files don't exist yet. 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 has potential questions.

  • How do I declare 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 create COBOL record names, field names, index names, and data types that best conform to the compiler, Database Connectors, and the database engine?
These questions are addressed in the next few sections.
  • Default Behavior
  • Accessing Existing Database Files
Parent topic: Database Connectors User's Guide


Default Behavior

The following are automatically handled by Database Connectors:

  • Conversion of uppercase field names to lowercase (or vice versa) when necessary.
  • 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, matching COBOL fields to database fields should not be an issue. Fields match perfectly, because the database fields are based on your COBOL code. As an alternative, you can use your database query tool to create a database.
  • 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, 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
Parent topic: New and Existing Databases


Accessing Existing Database Files

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

If your COBOL code isn't written yet, you should follow the guidelines given in the Appendix for your specific RDBMS.

If your COBOL code 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 Using XFD Directives, and XFDs are described in XFDs.

  • How Do I Match Existing Text Fields?
  • How Do I Match Existing Numeric Fields?
  • Field Names
Parent topic: New and Existing Databases
Related information
Using XFD Directives
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.

Parent topic: Accessing Existing Database Files
Related information
How Do I Match Existing Numeric Fields?
Field Names


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.

Parent topic: Accessing Existing Database Files
Related information
How Do I Match Existing Text Fields?
Field Names


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
Parent topic: Accessing Existing Database Files
Related information
How Do I Match Existing Text Fields?
How Do I Match Existing Numeric Fields?


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 Using XFD Directives.

Parent topic: Field Names
Related information
Using XFD Directives
Name XFD Directive


File Handling Configuration

This section explains how to configure COBOL file handling operations to interact with supported RDBMS.
  • Configuring the File Handler
Parent topic: Database Connectors User's Guide


Configuring the File Handler

As well as the database-specific information that you specify in an ACUFH configuration file, you also need to inform the File Handler of which database type it is working with when performing file handling operations. You do this by specifying the IDXFORMAT, which can be set using one of the following methods:

  • Set at compile-time, either within your source code or on the command line.
  • Set in a File Handler configuration file, which is queried at run time.

The supported values for the IDXFORMAT option are:

IDXFORMAT No: | RDBMS:
18 | Microsoft SQL Server
19 | Oracle
20 | DB2

Set the format at compile-time

Set the option within your source code using the following syntax (replacing nn with the appropriate value from the table above):

      $set IDXFORMAT"nn"
       Identification Division.
       Program-id. Dbc-demo.
       ...
       End program Dbc-demo.

Or specify the IDXFORMAT on the command line, during compilation:

Windows:

cobol Dbc-demo.cbl IDXFORMAT"nn" NOGNT"";

UNIX:

cob Dbc-demo.cbl IDXFORMAT"nn";
Note: Another method is to use the USE directive, specifying a directives file that contains the required IDXFORMAT.

Set the format at run time

Tip: Configuring the File Handler at run time enables you to use the same source code with more than one of the supported RDBMSs without the need to recompile. It also gives you greater control when setting different formats at once.

Set the option within a File Handler configuration file:

[XFH-DEFAULT]
IDXFORMAT=8
[purchdb2.dat]
IDXFORMAT=20
[purchorl.dat]
IDXFORMAT=19
Note: In the example above, purchdb2.dat file uses IDXFORMAT 20, and purchorl.dat uses IDXFORMAT 19, but all other files take the default specified in the [XFH_DEFAULT] section.

To use the configuration file at run time, you must specify it in the EXTFH environment variable:

Windows:

set EXTFH=c:\dbc\extfh.cfg

sets the file name to c:\dbc\extfh.cfg.

UNIX:

EXTFH=/home/dbc/extfh.cfg
export EXTFH

sets the file name to /home/dbc/extfh.cfg.

Parent topic: File Handling Configuration


Run Time System Configuration

Database Connectors uses an ACUFH configuration file to determine how the run time system should behave when interacting with data. This chapter describes how to configure the File Handler, and how to use ACUFH configuration variable options to configure the run time system for your relational database environment.
  • ACUFH Configuration Variables
Parent topic: Database Connectors User's Guide


ACUFH Configuration Variables

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. Set the A_CONFIG environment variable to identify your own personal copy. For example:

Windows:

SET A_CONFIG=directory-name\my-file-name

UNIX:

A_CONFIG=directory-name/my-file-name
export A_CONFIG

defines a configuration file with the name of your choice, in the directory of your choice.

There are several variables that can be set in your ACUFH configuration file that affect the Database Connectors processing. Those listed below are applicable to any relational database management system (RDBMS) with which Database Connectors communicates. Some ACUFH configuration variables are database-specific; those variables are discussed in the database-specific sections later in this guide.

Variable | Description
4GL_2000_CUTOFF | Determines which two-digit years are considered to be in the twentieth century and which are considered to be in the twenty-first century
4GL_8_DIGIT_CUTOFF | Determines whether to use 4GL_2000_CUTOFF for dates that don't use a format string and that are eight digits
4GL_COLUMN_CASE | Tells Database Connectors to leave the case of the field names found in the eXtended File Descriptor (XFD) unchanged
4GL_COMMIT_COUNT | Indicates the conditions under which you want to issue an automatic COMMIT_WORK operation
4GL_CONVERT_DATE_ZERO | Allows Database Connectors to locate and return those records written using the date 01/01/01
4GL_DB_MAP | Enables you to set one map at a time by using FILE_PREFIX as a list of directories, each of which maps to a different database
4GL_EXTRA_DB_COLS_OK (applies to Windows environments only) | Allows the database table to have more columns than the COBOL program's corresponding file descriptor
4GL_FULL_DATA_TEST | Allows you to specify whether an entire field is tested for illegal data or if only the first field is tested
4GL_IGNORED_SUFFIX_LIST | Lists the case-independent suffixes to ignore when opening Microsoft SQL Server tables or DB2 tables
4GL_ILLEGAL_DATA | Determines how COBOL data considered illegal by the database is converted before it is stored in the database
4GL_JULIAN_BASE_DATE | Sets the base date to be used for Julian date calculations
4GL_USEDIR_LEVEL (applies to Windows environments only) | Provides a method of mapping filenames (with directory information) to table names
4GL_WHERE_CONSTRAINT | Allows you to override any value given in the external variable A4GL_WHERE_CONSTRAINT
FILE_TRACE | Allows you to start file tracing without opening the debugger
XFD_DIRECTORY | Tells the run time system the name of the directory that contains the XFD files built by the compiler
XFD_MAP | Tells the run time system to associate certain filenames with a particular XFD
XFD_MAP_RESETS | Determines whether setting XFD_MAP adds to or replaces the existing value
XFD_PREFIX | Defines a specific series of directories to search for XFD files
Parent topic: Run Time System Configuration


Database Connectors for DB2

This appendix provides information that is specific to Database Connectors for DB2.
  • DB2 Concepts Overview
  • Client Configuration
  • Filename Translation
  • Decimal Points
  • ACUFH Configuration File Variables for DB2
  • Record and Table Locking
  • Limits and Ranges
  • Data Type Mapping
  • Run-Time System Errors
  • Common Questions and Answers
Parent topic: Database Connectors User's Guide


DB2 Concepts Overview

This quick overview of some basic design concepts underlying the DB2 relational database management system (RDBMS) helps to interface your COBOL program to it.

  • Transactions
  • Record-locking Issues in Transactions
  • Database Connectors and Record Locking
Parent topic: Database Connectors for DB2


Transactions

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 Connectors for DB2 or another application. When you use Database Connectors 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 Connectors for DB2 occur within a transaction, any record locked during processing remains locked until either a COMMIT WORK or ROLLBACK WORK is issued by Database Connectors. This action results in behavior similar to the LOCK ON MULTIPLE RECORDS clause in COBOL.

The benefits of this transaction management system are best illustrated by an example. A COBOL application that handles order entry might perform these steps to accept an order:

  • Write an invoice record
  • Update a customer record
  • Write a payroll record for sales commissions
  • Update an inventory record

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 gives the ability to:

  • Define and commit transactions, or undo them (usually in response to an error condition); this undo facility is called a rollback
  • Reconstruct files into a consistent state after a program dies or system failure occurs; this operation is called recovery
Parent topic: DB2 Concepts Overview
Related information
Record-locking Issues in Transactions
Database Connectors and Record Locking


Record-locking Issues in Transactions

Applications that perform work in small operation-based logical units, benefit greatly from DB2's transaction management systems. Other applications may encounter difficulty with record locking when operating against a system (DB2) 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:

  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. As no COMMIT or ROLLBACK has been issued, all of the records remain locked until the application ends, thus ending 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.

Note that the first application is not locked out. A process can read its own locked records.

Parent topic: DB2 Concepts Overview
Related information
Transactions
Database Connectors and Record Locking


Database Connectors and Record Locking

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.)

Database Connectors 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:

4GL_COMMIT_COUNT = 0 (Default)

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.

4GL_COMMIT_COUNT = n

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.

4GL_COMMIT_COUNT = -1

No commit is issued by Database Connectors. When 4GL_COMMIT_COUNT is set to -1, alternate ways to perform a commit or rollback include:

  • Call your database query tool with COMMIT WORK or ROLLBACK WORK.

A COMMIT WORK is, however, issued on exit from the run time system (for example, on execution of a STOP RUN).

Parent topic: DB2 Concepts Overview
Related information
Transactions
Record-locking Issues in Transactions
A_DB2_ERROR_MAP_FILE configuration variable
4GL_COMMIT_COUNT configuration variable


Client Configuration

The following section covers the client setup required before you begin using Database Connectors for DB2 on a new system.

Before you begin, you need to have:

  • Access to an already installed and configured DB2 database.
  • Configured any client software (if the server is on a remote machine), and verify a successful connection to the intended database.

You should consult your DB2 documentation for help with these tasks.

Once you have successfully connected to your DB2 database, continue with the Database Connectors-specific configuration.

  • Setting Up the User Environment
  • Designating the Host Data Source
  • Configuring the File Handler for DB2
Parent topic: Database Connectors for DB2


Setting Up the User Environment

To configure the client environment for Database Connectors, you usually use an ACUFH configuration file.

The minimum settings required would be the A_DB2_LOGIN, A_DB2_PASSWD, and A_DB2_DATASOURCE variables; refer to ACUFH Configuration File Variables for additional variables you can include.

You use the A_CONFIG environment variable to identify the ACUFH configuration file, which allows you to create your own personalized copy of the file, to avoid impacting other users.

If you are concerned about including passwords in a configuration file, you can set details such as A_DB2_PASSWD in your COBOL program or environment. The following syntax is an example of how you can set a variable within your code:

SET ENVIRONMENT A_DB2_PASSWD TO user-entry

See To Set Up The User Environment for more information.

Parent topic: Client Configuration
Related information
Designating the Host Data Source
Configuring the File Handler for DB2
To create an error map
ACUFH Configuration Variables
ACUFH Configuration File Variables for DB2
To Set Up The User Environment


Designating the Host Data Source

You must tell the run time system which data source to use for your DB2 file. You accomplish this by setting the ACUFH configuration file variable A_DB2_DATASOURCE. You can set this variable in your configuration file if 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"
Parent topic: Client Configuration
Related information
Setting Up the User Environment
Configuring the File Handler for DB2
A DB2 DATASOURCE configuration variable
Preparing and Compiling Your COBOL Program Example
The Demonstration Program


Configuring the File Handler for DB2

As well as the database-specific information that you specify in an ACUFH configuration file, you also need to inform the File Handler of which database type it is working with when performing file handling operations. You do this by specifying the IDXFORMAT, which can be set using one of the following methods:

  • Set at compile-time, either within your source code or on the command line.
  • Set in a File Handler configuration file, which is queried at run time.

The supported values for the IDXFORMAT option are:

IDXFORMAT No: | RDBMS:
18 | Microsoft SQL Server
19 | Oracle
20 | DB2

Set the format at compile-time

Set the option within your source code using the following syntax (replacing nn with the appropriate value from the table above):

      $set IDXFORMAT"nn"
       Identification Division.
       Program-id. Dbc-demo.
       ...
       End program Dbc-demo.

Or specify the IDXFORMAT on the command line, during compilation:

Windows:

cobol Dbc-demo.cbl IDXFORMAT"nn" NOGNT"";

UNIX:

cob Dbc-demo.cbl IDXFORMAT"nn";
Note: Another method is to use the USE directive, specifying a directives file that contains the required IDXFORMAT.

Set the format at run time

Tip: Configuring the File Handler at run time enables you to use the same source code with more than one of the supported RDBMSs without the need to recompile. It also gives you greater control when setting different formats at once.

Set the option within a File Handler configuration file:

[XFH-DEFAULT]
IDXFORMAT=8
[purchdb2.dat]
IDXFORMAT=20
[purchorl.dat]
IDXFORMAT=19
Note: In the example above, purchdb2.dat file uses IDXFORMAT 20, and purchorl.dat uses IDXFORMAT 19, but all other files take the default specified in the [XFH_DEFAULT] section.

To use the configuration file at run time, you must specify it in the EXTFH environment variable:

Windows:

set EXTFH=c:\dbc\extfh.cfg

sets the file name to c:\dbc\extfh.cfg.

UNIX:

EXTFH=/home/dbc/extfh.cfg
export EXTFH

sets the file name to /home/dbc/extfh.cfg.

Parent topic: Client Configuration
Related information
Setting Up the User Environment
Designating the Host Data Source


Filename Translation

As you prepare to work with Database Connectors, 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 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 file 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.

Database Connectors translate the name to uppercase letters and changes hyphens to underscores. File extensions are stripped, based on the setting of 4GL_IGNORED_SUFFIX_LIST. This new name is the one that Database Connectors uses for references to database tables.

Parent topic: Database Connectors for DB2
Related reference
XFDs
4GL IGNORED SUFFIX LIST configuration variable


Decimal Points

Database Connectors reads the decimal point character from the environment variable DECIMAL_POINT. If DECIMAL_POINT is set, Database Connectors uses that character. If the variable is not set, Database Connectors 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.

Parent topic: Database Connectors for DB2


ACUFH Configuration File Variables for DB2

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. Set the A_CONFIG environment variable to identify your own personal copy. For example:

Windows:

SET A_CONFIG=directory-name\my-file-name

UNIX:

A_CONFIG=directory-name/my-file-name
export A_CONFIG

ensures the specified configuration file is used when interfacing with your database.

This section lists the ACUFH configuration file variables that are specific to DB2. For ACUFH configuration file variables that are generally applicable to any RDBMS with which Database Connectors communicates, see ACUFH Configuration Variables in the Run Time Configuration section.

Variable | Description
A_DB2_ALTERNATE_COMMIT_LOGIC | Determines how the interface responds 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
Parent topic: Database Connectors for DB2
Related reference
ACUFH Configuration Variables


Record and Table Locking

With Database Connectors, records are locked in a Wait mode. An application trying to read a locked record does not respond 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.

Parent topic: Database Connectors for DB2


Limits and Ranges

The following limits exist when working with DB2:
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 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.

Parent topic: Database Connectors for DB2


Data Type Mapping

The table below shows the mappings used by Database Connectors when interacting with DB2. Note how some mappings use a combination of the data type and XFD directive. For additional information on XFD directives, see Using XFD Directives .

If you are in a position where you need to create a COBOL file descriptor (FD) based on an existing data source table, use these mappings as a guide; although, getting them correct is not critical, as when Database Connectors opens an existing data source, mapping is carried out by matching the COBOL data names with the column names.

If you do need to accurately represent your data in the COBOL program, setting your data items as PIC X(nn) will work perfectly well (since most DB2 data types have a CHAR representation). For INTEGER types, use PIC9(9), or if you know the range of an INTEGER column (say, 0 to 999), you can match it more closely (by using PIC 9(3)).

COBOL Data Type | Database Connectors 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
Parent topic: Database Connectors for DB2
Related reference
Using XFD Directives
DATE XFD directive
BINARY XFD directive
VAR LENGTH XFD directive


Run-Time System Errors

This topic lists the Database Connectors for DB2 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.

Run-time system errors have this format:

9/xxx

The 9 indicates a file system error and is reported as the first digit of the 2-byte FILE STATUS variable. The xxx is a binary value between 1 and 255 that provides more information. If xxx is 83, you can retrieve additional information with C$RERR. That additional information can come from either the database or the run time. Run-time codes returned with C$RERR include:

Code | Message
1 | Error reading the XFD file
2 | Corrupt XFD/dictionary file
3 | Error opening the XFD file
4 | Too many key fields (16 max)
12 | DB2 library function returned an unexpected error
13 | Illegal size or type of data for variable xxx
14 | More than one table with the same name

For a detailed description of a particular message, see the Error Messages section.

Parent topic: Database Connectors for DB2
Related reference
Troubleshooting
Error Messages


Common Questions and Answers

This topic contains some questions and answers specific to Database Connectors for DB2.

On HP-UX platforms, which type of COBOL run time system - non-threaded or multi-threaded - should I use when connecting to the database?
You need to use the multi-threaded RTS, because DB2 is multi-threaded. If you use the non-threaded RTS, you will get a database connection error, because HP-UX does not allow the mixing of multi-threaded and non-threaded code.
On other UNIX/Linux platforms, you can use either run time system.
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.
Parent topic: Database Connectors for DB2


Database Connectors for Oracle

This appendix provides information that is specific to Database Connectors for Oracle.
  • Oracle Concepts Overview
  • Client Configuration
  • Filename Translation
  • ACUFH Configuration File Variables for Oracle
  • Using the Database Table
  • Unsupported Features
  • Limits and Ranges
  • Data Type Mapping
  • Run-Time System Errors
  • Common Questions and Answers
Parent topic: Database Connectors User's Guide


Oracle Concepts Overview

Database Connectors for Oracle is based on the Oracle Call Interface (OCI), an API that makes the code more portable to the maximum number of platforms.

This quick overview of some basic design concepts underlying the Oracle Database Management System relational database system (RDBMS) help to interface your COBOL program to it.

  • Transactions
  • Record Locking Issues in Transactions
  • Database Connectors and Record Locking
  • The open_cursors Parameter
  • Table Ownership
  • Security
Parent topic: Database Connectors for Oracle


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 Database Connectors for Oracle or another 4GL application. When you use the Database Connectors, 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 Database Connectors occur within a transaction, any record locked during processing remains locked until either a COMMIT WORK or ROLLBACK WORK is issued by Database Connectors. This action results in behavior similar to the LOCK ON MULTIPLE RECORDS clause in COBOL.

The benefits of this transaction management system are best illustrated by an example. A COBOL application that handles order entry might perform these steps to accept an order:

  • Write an invoice record.
  • Update a customer record.
  • Write a payroll record for sales commissions.
  • Update an inventory record.

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:

  • 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
Parent topic: Oracle Concepts Overview


Record Locking Issues in Transactions

Applications that perform work in small operation-based logical units, benefit greatly from Oracle's transaction management systems. Other applications may encounter difficulty with record locking when operating against a system (Oracle) 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:

  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. As no COMMIT or ROLLBACK has been issued, all of the records remain locked until the application ends, thus ending 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.

Note that the first application is not locked out. A process can read its own locked records.

Parent topic: Oracle Concepts Overview


Database Connectors and Record Locking

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:

COMMIT_COUNT = 0 (Default)

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.

COMMIT_COUNT =n

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.

COMMIT_COUNT = –1

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:

  • Call your Oracle query tool with COMMIT WORK or ROLLBACK WORK.

A COMMIT WORK is, however, issued on exit from the run time system (for example, on execution of a STOP RUN).

Parent topic: Oracle Concepts Overview
Related information
COMMIT_COUNT configuration variable


The open_cursors Parameter

The Oracle system parameter open_cursors should be set to enhance communication with the Connector. For more information, see Checking System Parameters.

Parent topic: Oracle Concepts Overview
Related information
Checking System Parameters


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. Database Connectors provides a user-configurable method (the USER_PATH ACUFH configuration file) for implementing this.

Parent topic: Oracle Concepts Overview
Related information
USER_PATH configuration variable


Security

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:

  • Creating a table under another user's name
  • Dropping a table owned by another user

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, Database Connectors 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.
Parent topic: Oracle Concepts Overview
Related information
USER_PATH configuration variable


Client Configuration

The following section covers the client configuration required before using Database Connectors for Oracle on a new system.

Before you begin, you need to have:

  • Access to an already installed and configured Oracle database.
  • Configured any client software (if the server is on a remote machine), and verify a successful connection to the intended database.

The Oracle RDBMS must be installed and configured prior to this, which also includes the setting of any environment variables that may be required (such as ORACLE_HOME, ORACLE_SID, and LD_LIBRARY_PATH) - consult your Oracle documentation for details on this.

Typically, you'll install Oracle client software to connect to a database not on the current machine, and then set up a service name that references the database that you want to connect to. The easiest way to do that is to use Oracle's Net Configuration Assistant. This tool also allows you to test the connection to the database. Some other versions of Oracle may require you to set specific environment variables (such as ORACLE_HOME, and ORACLE_SID); again, consult your Oracle documentation for further details.

Client installation can also be achieved using Instant Client - a lightweight solution to the Oracle client install. If using this client, we recommend downloading the following components to help test your connection:

  • Oracle's SQL*Plus interactive query tool. SQL*Plus allows you to do database work outside of COBOL, including interactive queries, table creation, table modification, and creation of views, constraints, and relationships between tables.
  • Oracle's SCOTT/Tiger test schema. This is a schema that is installed by default on older Oracle versions. It can still be downloaded and installed separately, and is used for testing purposes.
Note: Micro Focus does not provide these downloads.

Once you have successfully connected to your Oracle database, continue with the Database Connectors-specific configuration.

  • Oracle's Instant Client
  • Checking System Parameters
  • Setting Up a User Account
  • Setting Up the User Environment
  • Configuring the File Handler for Oracle
  • Setting Up the Search Path
  • Handling Transactions
Parent topic: Database Connectors for Oracle
Related reference
To Set Up a Service Name for an Oracle Database


Oracle's Instant Client

Instant Client is a free-to-download, lightweight Oracle database client designed to allow you to run your applications without installing the standard Oracle client or having an ORACLE_HOME. You can use it in place of the standard Oracle client when using Database Connectors for Oracle.

Setting up Oracle Instant Client can be challenging and should be performed by advanced users only. You will need the Basic Lite and SQL*Plus packages, available from the Oracle web site. Follow Oracle's instructions to install and configure the products.

If you also installed the SCOTT/Tiger test schema, you can test the connection to the Oracle database using SQL*Plus before proceeding with the Database Connectors client install.

Parent topic: Client Configuration
Related information
To test the installation of Oracle's Instant Client


Checking System Parameters

Database Connectors 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.

Parent topic: Client Configuration
Related information
To set the system parameters


Setting Up a User Account

For Database Connectors for Oracle to be able to communicate with the Oracle database, you must specify a valid user login that has the required read and write access to the database. For this, you can set up either one general account for all Database Connectors 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 refer to your Enterprise Manager documentation instead.
Parent topic: Client Configuration
Related reference
To Configure a Login


Setting Up the User Environment

To configure the client environment for Database Connectors, you usually use an ACUFH configuration file.

The minimum settings required would be the ORA_LOGIN, ORA_PASSWD, and A_ORA_DATABASE variables; refer to ACUFH Configuration File Variables for additional variables you can include. If you are accessing files that you do not personally own and that do not have public synonyms, you may also need to set USER_PATH.

You use the A_CONFIG environment variable to identify the ACUFH configuration file, which allows you to create your own personalized copy of the file, to avoid impacting other users.

If you are concerned about including passwords in a configuration file, you can set these details in your COBOL program or environment. The following syntax is an example of how you can set a variable within your code:

SET ENVIRONMENT ORA_PASSWD TO user-entry
Parent topic: Client Configuration


Configuring the File Handler for Oracle

As well as the database-specific information that you specify in an ACUFH configuration file, you also need to inform the File Handler of which database type it is working with when performing file handling operations. You do this by specifying the IDXFORMAT, which can be set using one of the following methods:

  • Set at compile-time, either within your source code or on the command line.
  • Set in a File Handler configuration file, which is queried at run time.

The supported values for the IDXFORMAT option are:

IDXFORMAT No: | RDBMS:
18 | Microsoft SQL Server
19 | Oracle
20 | DB2

Set the format at compile-time

Set the option within your source code using the following syntax (replacing nn with the appropriate value from the table above):

      $set IDXFORMAT"nn"
       Identification Division.
       Program-id. Dbc-demo.
       ...
       End program Dbc-demo.

Or specify the IDXFORMAT on the command line, during compilation:

Windows:

cobol Dbc-demo.cbl IDXFORMAT"nn" NOGNT"";

UNIX:

cob Dbc-demo.cbl IDXFORMAT"nn";
Note: Another method is to use the USE directive, specifying a directives file that contains the required IDXFORMAT.

Set the format at run time

Tip: Configuring the File Handler at run time enables you to use the same source code with more than one of the supported RDBMSs without the need to recompile. It also gives you greater control when setting different formats at once.

Set the option within a File Handler configuration file:

[XFH-DEFAULT]
IDXFORMAT=8
[purchdb2.dat]
IDXFORMAT=20
[purchorl.dat]
IDXFORMAT=19
Note: In the example above, purchdb2.dat file uses IDXFORMAT 20, and purchorl.dat uses IDXFORMAT 19, but all other files take the default specified in the [XFH_DEFAULT] section.

To use the configuration file at run time, you must specify it in the EXTFH environment variable:

Windows:

set EXTFH=c:\dbc\extfh.cfg

sets the file name to c:\dbc\extfh.cfg.

UNIX:

EXTFH=/home/dbc/extfh.cfg
export EXTFH

sets the file name to /home/dbc/extfh.cfg.

Parent topic: Client Configuration


Setting Up the Search Path

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 file 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.

Parent topic: Client Configuration
Related information
USER_PATH configuration variable


Handling Transactions

When a COMMIT WORK database command is issued, it terminates a transaction and makes all work permanent and accessible to other users. The Database Connectors 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.

You are now ready to prepare and compile your COBOL program and run the demonstration program. For additional information, see Examples of Preparing and Compiling a Program and The Demonstration Program.

Parent topic: Client Configuration
Related reference
COMMIT_COUNT configuration variable
Examples of Preparing and Compiling a Program
The Demonstration Program


Filename Translation

As you prepare to work with Database Connectors 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 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 file 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, Database Connectors translates the file name to uppercase letters and changes hyphens to underscores. This new name is the one that Database Connectors uses for references to the database table.

Parent topic: Database Connectors for Oracle


ACUFH Configuration File Variables for Oracle

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:

Windows:

SET A_CONFIG=directory-name\my-file-name

UNIX:

A_CONFIG=directory-name/my-file-name
export A_CONFIG

ensures the specified configuration file is used when interfacing with your database.

This section lists the ACUFH configuration file variables that are specific to Oracle. For ACUFH configuration file variables that are generally applicable to any RDBMS with which Database Connectors communicates, see ACUFH Configuration Variables in the Run Time Configuration section.

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
Parent topic: Database Connectors for Oracle
Related information
ACUFH Configuration Variables


Using the Database Table

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 Examples of Preparing and Compiling a Program.

Parent topic: Database Connectors for Oracle
Related reference
Examples of Preparing and Compiling a Program


Unsupported Features

OPEN ALLOWING READERS is not supported by Oracle. Your ACUFH configuration file determines how to interpret this phrase. 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. See COMMIT_COUNT configuration variable for more information.

Oracle does not support record encryption, record compression, or alternate collating sequences. You may include these options in your program; however, they are disregarded if specified.

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 introduced in Oracle Version 9i, you cannot:

  • Delete a table you don't own
  • Create a table under another user's name
Parent topic: Database Connectors for Oracle
Related information
COMMIT_COUNT configuration variable


Limits and Ranges

The following limits exist when working with Oracle:

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.
Parent topic: Database Connectors for Oracle


Data Type Mapping

Database Connectors for Oracle supports the following data types.
COBOL | Oracle
PIC X | CHAR
PIC X(n) | CHAR(n)
PIC X(n) | VARCHAR2 (VAR_LENGTH directive is required)
PIC X(n) | RAW(n)
PIC 9(n) | NUMBER(n)
PIC 9(n) | RAW(n)
PIC 9(n)V9(m) | NUMBER(n+m, m)
example: PIC999V99 | NUMBER(5,2)
Oracle | COBOL
CHAR | PIC X
CHAR(n) | PIC X(n)
VARCHAR2 | PIC X(n) (VAR_LENGTH directive is required)
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) (BINARY directive is required)
example: NUMBER(4,3) | PIC9V999

Internally, Database Connectors 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:

  • LONG
  • LONG RAW
Parent topic: Database Connectors for Oracle
Related reference
VAR LENGTH XFD directive
BINARY XFD directive


Run-Time System Errors

This topic lists the Database Connectors for Oracle 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.

Run-time system errors have this format:

9/xxx

The 9 indicates a file system error and is reported as the first digit of the 2-byte FILE STATUS variable. The xxx is a binary value between 1 and 255 that provides more information. If xxx is 83, you can retrieve additional information with C$RERR. That additional information can come from either the database or the run time. Run-time codes returned with C$RERR include:

Code | Message
0 | Duplicate key
1 | Error reading the XFD file
2 | Corrupt XFD/dictionary file
3 | Error opening the XFD file
4 | Too many key fields (16 max)
5 | Too many key fields (16 max for Oracle)
1001 | Invalid Cursor

For a detailed description of a particular message, see the Error Messages section.

Parent topic: Database Connectors for Oracle
Related reference
Troubleshooting
Error Messages


Common Questions and Answers

On HP-UX platforms, which type of COBOL run time system - non-threaded or multi-threaded - should I use when connecting to the database?
You need to use the multi-threaded RTS, because Oracle is multi-threaded. If you use the non-threaded RTS, you will get a database connection error, because HP-UX does not allow the mixing of multi-threaded and non-threaded code.
On other UNIX/Linux platforms, you can use either run time system.
When I try to open a file for output, I get the error 9/83, with a secondary error of 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 file variable.
Can Database Connectors 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.
Parent topic: Database Connectors for Oracle


Database Connectors for Microsoft SQL Server

These topics provide information specific to Database Connectors for Microsoft SQL Server.

  • Microsoft SQL Server Concepts Overview
  • Client Configuration
  • Filename Translation
  • ACUFH Configuration File Variables for MSSQL
  • Using the Database Table
  • Table Locking
  • Stored Procedures
  • Limits and Ranges
  • Supported Data Types for SQL Server
  • Run-Time System Errors
  • Common Questions and Answers
Parent topic: Database Connectors User's Guide


Microsoft SQL Server Concepts Overview

This quick overview of some basic design concepts underlying the Microsoft SQL Server (MSSQL) Database Management System (RDBMS) helps to interface your COBOL program to it.

Servers

An MSSQL server is one copy of the database engine executing on a computer. A server has a name, and when a program wants to access a database controlled by that server, the program asks for a connection to the server by name. Multiple servers, controlling different databases, can be executing on a single machine. The default name that MSSQL gives to a server is localhost; refer to the A_MSSQL_DEFAULT_CONNECTION configuration variable for further details on how to connect your program to one or more servers.

Table Ownership

Table names in MSSQL have the form database.owner.table_name. Within MSSQL, 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; thus, different owners can have tables of the same name. However, this is not true when you use the Database Connectors for MSSQL interface.

The Database Connectors interface works a little differently: it automatically determines the owner name used to reference a table. It is therefore essential that there are not multiple tables with the same name in a single database, even though the tables have different owners. If there are such multiple tables, the interface does not necessarily find the correct one, and no diagnostic is issued.

Table names include dots (.) as separators. Because of this, you must make sure there are no extensions on COBOL file names that could be converted to table names. For example, for a COBOL file named IDX1.DTA, the Connector attempts to open a table DTA with owner IDX1. You can avoid this problem either by renaming your COBOL file in your source program or by using a configuration file variable to map the file name to an allowable file name, such as:

IDX1.DTA  IDX1

In the above example, IDX1.DTA is the name in the ASSIGN clause of the file's SELECT statement.

If you map your file name to a new name, drop the extension to form the new name. The compiler uses the base file name — without the extension — to create the eXtended File Descriptor (XFD) file name IDX1.XFD. XFDs are explained in detail in XFDs. The run time system needs to locate this file. But if you've mapped the file name to something completely different (such as MYFILE), the run time looks for an XFD file named MYFILE.XFD. So you would have to remember to change the name of IDX1.XFD to MYFILE.XFD in the XFD directory. Dropping the extension when you map the name prevents having to perform this extra step.

Refer to 4GL_IGNORED_SUFFIX_LIST configuration variable for an alternate method of removing file extensions.

Security

Security is implemented in the MSSQL RDBMS. A user is required to log in to the RDBMS before any file processing can occur. Database Connectors provides both a default and a user-configurable method for implementing this; refer to Setting up the User Environment for further details on how to supply login details.

Generally, it is best for someone with database administrator (DBA) privileges to create and drop the tables, allowing others only the permissions to add, change, or delete information contained in them.

See the Microsoft SQL Server documentation for more details on DBA privileges.

Parent topic: Database Connectors for Microsoft SQL Server
Related information
A_MSSQL_DEFAULT_CONNECTION configuration variable
4GL IGNORED SUFFIX LIST configuration variable
XFDs
Setting Up the User Environment


Client Configuration

The following section covers the client setup required before you begin using Database Connectors for Microsoft SQL Server (MSSQL) on a new system. The Microsoft SQL Server RDBMS must be installed and configured prior to this, and must be at least version 6.5.

You will also be required to install some stored procedures as part of the client installation. For that you will need something such as Microsoft SQL Server's Query Analyzer to run the query that installs the stored procedures.

Note: The default sort order for MSSQL is case-independent. Make sure that this is what you really want when you install the server. The Database Connectors connector uses the sort order as installed and cannot change its behavior to do case-dependent ordering if that is not what the server uses.

First you must install the files from the Micro Focus installation media onto the client machine. Then follow the instructions below for the server machine. Final setup steps on the client machine complete the installation.

Be sure to use the 64-bit libraries with a 64-bit run time system and 64-bit Database Connectors for MSSQL, and the 32-bit libraries with a 32-bit run time system and 32-bit Database Connectors for MSSQL.

Perform the following steps to set up Database Connectors on a new system:

  • Installing the Server and Client Machine
  • Setting Up a User Login
  • Setting Up the User Environment
  • Configuring the File Handler for Microsoft SQL Server
Parent topic: Database Connectors for Microsoft SQL Server


Installing the Server and Client Machine

The Database Connectors interface for MSSQL is an add-on module. The installation includes a .DLL file that is detected at run time. It is not necessary for 32-bit users to relink the run time system.

Typically, the setup is one of a client machine using a Database Connectors connector to access a SQL database on a separate server; however, your SQL server and your client can be on the same machine. The Database Connectors interface can be executed on a machine that is running any supported 32-bit Windows platform from Microsoft.

See To Install Database Connectors for Microsoft SQL Server for details on client and server configuration, regardless of how you are set up.

Parent topic: Client Configuration
Related information
Setting Up a User Login
Setting Up the User Environment
Configuring the File Handler for Microsoft SQL Server
To Install Database Connectors for Microsoft SQL Server


Setting Up a User Login

For Database Connectors for MSSQL to be able to communicate with the SQL Server, you must supply a valid user login that has access to the database that you are reading from and writing to. The user login must also have write access to the database in which you installed the stored procedures earlier.

You can set up either one general user login for all Database Connectors users or a login for each individual user. To set up a user login, you must have DBA privileges for the SQL Server; see the Microsoft SQL Server documentation for more information on setting up user logins.

Parent topic: Client Configuration
Related information
Installing the Server and Client Machine
Setting Up the User Environment
Configuring the File Handler for Microsoft SQL Server


Setting Up the User Environment

The previous step spoke of the need to use a valid user login. Once you have those details, you need to set them in your user environment by adding the values to the variables mentioned below.

To set up your user environment to use Database Connectors, you must do the following:

  • Set the A_MSSQL_LOGIN and A_MSSQL_PASSWD variables, either in your environment or in the ACUFH configuration file. Alternately, set the configuration variable A_MSSQL_NT_AUTHENTICATION to TRUE. For security reasons, it is best to set the password variable from your COBOL program by asking the user to enter a password and then executing:
    SET ENVIRONMENT "A_MSSQL_PASSWD" TO user-entry
  • Make a personalized copy of the ACUFH configuration file and populate it with configuration variables to suit your requirements; see ACUFH Configuration File Variables for MSSQL for more information. Ensure the configuration file is in effect when you run your Database Connectors application.
Parent topic: Client Configuration
Related information
Installing the Server and Client Machine
Setting Up a User Login
Configuring the File Handler for Microsoft SQL Server
ACUFH Configuration File Variables for MSSQL


Configuring the File Handler for Microsoft SQL Server

As well as the database-specific information that you specify in an ACUFH configuration file, you also need to inform the File Handler of which database type it is working with when performing file handling operations. You do this by specifying the IDXFORMAT, which can be set using one of the following methods:

  • Set at compile-time, either within your source code or on the command line.
  • Set in a File Handler configuration file, which is queried at run time.

The supported values for the IDXFORMAT option are:

IDXFORMAT No: | RDBMS:
18 | Microsoft SQL Server
19 | Oracle
20 | DB2

Set the format at compile-time

Set the option within your source code using the following syntax (replacing nn with the appropriate value from the table above):

      $set IDXFORMAT"nn"
       Identification Division.
       Program-id. Dbc-demo.
       ...
       End program Dbc-demo.

Or specify the IDXFORMAT on the command line, during compilation:

Windows:

cobol Dbc-demo.cbl IDXFORMAT"nn" NOGNT"";

UNIX:

cob Dbc-demo.cbl IDXFORMAT"nn";
Note: Another method is to use the USE directive, specifying a directives file that contains the required IDXFORMAT.

Set the format at run time

Tip: Configuring the File Handler at run time enables you to use the same source code with more than one of the supported RDBMSs without the need to recompile. It also gives you greater control when setting different formats at once.

Set the option within a File Handler configuration file:

[XFH-DEFAULT]
IDXFORMAT=8
[purchdb2.dat]
IDXFORMAT=20
[purchorl.dat]
IDXFORMAT=19
Note: In the example above, purchdb2.dat file uses IDXFORMAT 20, and purchorl.dat uses IDXFORMAT 19, but all other files take the default specified in the [XFH_DEFAULT] section.

To use the configuration file at run time, you must specify it in the EXTFH environment variable:

Windows:

set EXTFH=c:\dbc\extfh.cfg

sets the file name to c:\dbc\extfh.cfg.

UNIX:

EXTFH=/home/dbc/extfh.cfg
export EXTFH

sets the file name to /home/dbc/extfh.cfg.

Parent topic: Client Configuration
Related information
Installing the Server and Client Machine
Setting Up a User Login
Setting Up the User Environment
The Demonstration Program
Preparing and Compiling Your COBOL Program


Filename Translation

As you prepare to work with Database Connectors for MSSQL, you may find it helpful to understand the rules around filename interpretation and to understand how the names of tables and XFD files are formed and work together.

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 file 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.

However, the Connector needs the name of the file to find the appropriate XFD file. To do this, Database Connectors changes the name of the file to lowercase letters and changes hyphens to underscores. Note, however, that this is performed only on a local copy of the file. Once the XFD file is found, the filename reverts to the name that was originally passed to the open() function. Characters that are illegal in identifiers, such as a hyphen ("-"), are trapped by Database Connectors neither find the file nor create a new one.

Parent topic: Database Connectors for Microsoft SQL Server


ACUFH Configuration File Variables for MSSQL

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. Set the A_CONFIG environment variable to identify your own personal copy. For example:

SET A_CONFIG=directory-name\my-file-name

ensures the specified configuration file is used when interfacing with your database.

This section lists the ACUFH configuration file variables that are specific to MSSQL. For ACUFH configuration file variables that are generally applicable to any RDBMS with which Database Connectors communicates, see ACUFH Configuration Variables in the Run Time Configuration section.

As a minimum, you should set the following configuration variables:

  • A_MSSQL_DEFAULT_CONNECTION
  • A_MSSQL_LOCK_DB
  • A_MSSQL_DATABASE

and either:

  • A_MSSQL_LOGIN
  • A_MSSQL_PASSWD

or:

  • A_MSSQL_NT_AUTHENTICATION
Variable | Description
A_MSSQL_ADD_IDENTITY | Adds an extra column to any table created by the Connector
A_MSSQL_ADD_TIMESTAMP | Ensures that modifications made to a row are not overwriting someone else's changes
A_MSSQL_APPROLE_NAME | Allows the Connector to use approles
A_MSSQL_APPROLE_PASSWD | Allows the Connector to use approles
A_MSSQL_CURSOR_OPTION_1, A_MSSQL_CURSOR_OPTION_2, A_MSSQL_CURSOR_OPTION_3 | Allow you to fine-tune the declaration of cursors in the Connector
A_MSSQL_DATABASE | Specifies the name of the particular database to be accessed
A_MSSQL_DEADLOCK_LOOPS | Can be used to instruct the Connector to re-execute an INSERT statement
A_MSSQL_DEFAULT_CONNECTION | Specifies the name of the server to which the run time system connects
A_MSSQL_DEFAULT_OWNER | Specifies the name of the user of a table
A_MSSQL_FAST_ACCESS | Files opened while this variable is set to a nonzero value are optimized for forward sequential access
A_MSSQL_LIMIT_DROPDOWN configuration variable | This variable allows an application to direct the interface not to perform "drop down" query generation and instead return "end of file" when the records matching the current query have been exhausted.
A_MSSQL_LOCK_DB | Specifies the name of the database that holds the lock table
A_MSSQL_LOGIN | Indicates the user name under which you want to connect to the database system
A_MSSQL_MAX_CHARACTERS | Indicates the maximum number of bytes the Connector allows in a table row
A_MSSQL_MAX_COLUMNS | Indicates the maximum number of columns the Connector allows in a table
A_MSSQL_NO_CACHED_READ configuration variable | Determines if the 'cached-read' method, which can improve performance, is in effect.
A_MSSQL_NO_COUNT_CHECK | Causes the interface not to check that a record was updated
A_MSSQL_NO_DBID | Causes the interface to use a Database ID of "0", instead of the actual ID of the database
A_MSSQL_NO_RECORD_LOCKS | Causes all READS to be treated as READ NO LOCK
A_MSSQL_NO_TABLE_LOCKS | Causes the interface to not use the AcuOpenTables1 table, which causes all table locking to be disabled
A_MSSQL_NO_23_ON_START | Causes START not to detect whether records actually exist
A_MSSQL_NT_AUTHENTICATION | Indicates whether MSSQL authenticates users based on their Windows login
A_MSSQL_PACKETSIZE | Sets the size of network packets
A_MSSQL_PASSWD | Set to the password assigned to the database account associated with the user name specified by A_MSSQL_LOGIN
A_MSSQL_ROWCOUNT | Determines how many rows are returned by a SELECT statement sent to the server
A_MSSQL_SELECT_KEY_ONLY | Directs the interface to select key columns only when searching for records
A_MSSQL_SEPARATE_LOCK_CONN configuration variable | Determines whether a separate connect is made to handle the internal workings.
A_MSSQL_SKIP_ALTERNATE_KEYS | Determines whether alternate keys are used to form indexes during table creation
A_MSSQL_TRANSLATE_TO_ANSI | Causes the Connector to call the same translation function used by the Windows run time system to translate characters going to the server into the OEM character set, and to translate characters coming from the server to ANSI
A_MSSQL_UNLOCK_ON_EXECUTE | Causes all invocations of I$IO using the EXECUTE opcode to unlock all records
A_MSSQL_USE_DROPDOWN_QUERIES | Causes selects sent to the database to be of the drop-down variety, instead of a single large query
Parent topic: Database Connectors for Microsoft SQL Server
Related information
Setting Up the User Environment
ACUFH Configuration Variables


Using the Database Table

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 MSSQL database is invisible; queries are generated and data is exchanged in fractions of a second, and the application proceeds without interruption.

You can also access the database information directly from MSSQL, at your option.

To create a sample COBOL application, see Preparing and Compiling a Program Example.

Parent topic: Database Connectors for Microsoft SQL Server


Table Locking

By default, MSSQL doesn't support the type of record and table locking that COBOL expects. For this reason, the Connector implements its own locking method. This is accomplished with the addition of two tables to a database. You choose the database to hold these tables during installation of Database Connectors.

Before using the locking tables, you must execute the included ms_inst.sql script. (See the installation instructions you used from this manual for the exact procedure. You can find them in the Contents.) If you don't perform this step, the first time you try to execute a COBOL program that opens an MSSQL table, error 11, Lock Table Incorrect, occurs.

AcuLocks1 Table

The first locking table is called AcuLocks1; it holds the record locks. The columns in this table are the

  • DBID
  • Table ID
  • Process ID of the process holding the lock
  • Primary key of the record that is locked

There is a unique index on the DBID, the Table ID, and the Key Value, so that inserts into this table are automatically rejected if another user holds a lock on the row in question. This also gives the DBA the information needed to determine who has locks set, and whether the user in question still has a connection to the server.

AcuOpenTables1 Table

The second locking table is called AcuOpenTables1; it holds information about open tables.

The columns in this table are

  • The DBID
  • The Table ID
  • The process ID (PID) of the process that has the table open
  • The Open Mode (Input, Output, I/O or Extend)
  • Whether Multiple records can be locked
  • Whether the file can be open for I/O by any other users
  • Whether the file can be open at all by any other users
  • Whether Mass Update was specified in the open

There are no indices on this table, but there is a trigger, which reject opens that are not allowed based on other users' open modes.

By using these lock tables, Database Connectors is able to support all the types of locking ordinarily supported. No special configuration variables are required.

This method of locking is all that is needed if no applications other than COBOL programs are going to be using Database Connectors for MSSQL. But if your site has other applications that access the MSSQL databases, you must use a method of locking that is inherent to Microsoft SQL Server.

Another method of locking that Microsoft SQL Server supports internally is the result of time stamping and the use of BROWSE MODE (see the discussion of BROWSE MODE in the Microsoft SQL Server Commands Reference Manual). If a table has a timestamp column, the Connector uses browse mode. This allows the server to detect whether another application has modified a record while locked by the server.

Parent topic: Database Connectors for Microsoft SQL Server


Stored Procedures

A stored procedure is a collection of SQL statements residing on the server, stored as text in a table in the database. Stored procedures provide an efficient environment for Database Connectors because, once they are executed on the server, they don't need to be parsed and optimized each time they are executed. (However, if the server goes down, the stored procedure is parsed and optimized when the procedure is called after the database restarts.)

If you run a set of stored procedures for the database where data is manipulated (your production database), this database must be the setting for A_MSSQL_LOCK_DB. If you run the stored procedures against both the lock database and the production database, the lock database can be the setting for A_MSSQL_LOCK_DB. Note that if you run one version of stored procedures against the lock database and another version against the production database, the stored procedures in the lock database override those in the production database. Therefore, we recommend that you always update your stored procedures with each new installation of Database Connectors for MSSQL, so that these procedures are consistent when you run them against the tables in your database.

Note: If you are upgrading from an earlier version of Database Connectors, be sure to install the new stored procedures. Stored procedures are always upgraded to be compatible with older versions of the product, so installing new stored procedures over old ones does not affect your ability to run with an older version of the interface software.
  • Developer- or Site-supplied Stored Procedures
  • Built-in Stored Procedures
Parent topic: Database Connectors for Microsoft SQL Server


Developer- or Site-supplied Stored Procedures

This topic provides information on stored procedures you may want to create. It also supplies some example code.

Database Connectors checks for these stored procedures when opening a file and uses them in certain circumstances.

These stored procedures are

  • tablename_insert (where tablename is the name of the table being accessed)

    The INSERT stored procedures takes as arguments all columns in order listed in the XFD. It expects no columns to be returned.

  • tablename_delete

    The DELETE stored procedure takes as arguments the primary key fields in the order they are listed in the XFD. It expects no columns to be returned.

  • tablename_read

    The READ stored procedure takes as arguments the primary key fields in the order they are listed in the XFD (in the key section). It expects the columns to be returned in order of condition. So all condition 0 columns are retrieved first, then condition 1 columns, etc. For each condition, the columns are expected in order given in the XFD

  • tablename_update

    The UPDATE stored procedures takes as arguments all columns in order listed in the XFD. It expects no columns to be returned.

  • tablename_startnnn (where nnn is the key number to start on)

    The STARTnnn stored procedure takes as arguments a mode string (EQ, LE, GT, GE, LT), and then all columns of key nnn in order listed in the XFD. It must execute raiserror 523409 'Record not found' if no rows match the search criteria.

Note: Database Connectors does not create these stored procedures or check their accuracy. It is possible to create stored procedures in such a way as to make Database Connectors for MSSQL completely inoperable. Database Connectors uses these stored procedures for performance reasons only.
Parent topic: Stored Procedures
Related information
tablename_insert stored procedure
tablename_delete
tablename_read stored procedure
tablename_update stored procedure
tablename_startnnn stored procedure


Built-in Stored Procedures

Several stored procedures come with Database Connectors for MSSQL. One, sp_AcuInit, provides a means for customized initialization. sp_AcuInit is not installed; you must create it to use it for connection-time set-up. The others return information based on the AcuOpenTables1 and AcuLocks1 tables.

Note: Names of several stored procedures end in _1, indicating the first version of the stored procedure. Whenever a stored procedure is updated, the extension is updated by one. This is why you can install new stored procedures without overwriting older ones. Be sure to install all stored procedures when you install Database Connectors for MSSQL.
sp_AcuInit | A procedure you can set up to do customized initialization
sp_AcuRemoveUnusedLocks_1 | Determines who is logged in and removes Process IDs that are no longer active on the system
sp_AcuTableReport_1 | Shows who is using the tables in the database at the time this procedure is run
sp_AcuUserCount_1 | Use this to track table and database activity to ensure that your database is running as efficiently as possible
sp_AcuZeroUserCount_1 | Removes all locks on a table and closes it
Parent topic: Stored Procedures


Limits and Ranges

The following limits exist when working with MSSQL:
Maximum number of columns per key: | 16
Maximum number of columns: | 1024
Maximum number of bytes in a single row when using Database Connectors for MSSQL: | 8060

To achieve the same sort or retrieval sequence under MSSQL as under the file system, key fields that contain signed numeric data must be preceded by a BINARY XFD directive.

Database Connectors supports the data types shown below; when it's creating tables, the following conversion rules are used, in the sequence shown:

COBOL SQL Server
DATE XFD directive
DATETIME
BINARY XFD directive
VARBINARY(n) (if SIZE < 255)
IMAGE (if SIZE >255)
VAR_LENGTH XFD directive
VARCHAR(n) (if SIZE < 255)
Usage FLOAT
REAL (if SIZE = 4)
Usage DOUBLE
FLOAT (if SIZE = 8)

Any other numeric usage:

PIC 9(n)V9(m)
SMALLINT (if m = 0 and n < 5)
INT (if m = 0 and n < 10)
DECIMAL(n + m, m)(otherwise)

Any other usage:

PIC X(n)
CHAR(n) (if n < 255)
TEXT (if n > 255)
Parent topic: Database Connectors for Microsoft SQL Server


Supported Data Types for SQL Server

The table below shows the mappings used by Database Connectors when interacting with Microsoft SQL Server.

Data type | Generated using...
DATETIME | The DATE XFD directive, specifying both date and time information.
DATE | The DATE XFD directive, specifying only date information.
TIME | The DATE XFD directive, specifying only time information.
BINARY(n) | The BINARY XFD directive, specifying a COBOL field 8000 bytes or less. n refers to the number of bytes.
IMAGE | The BINARY XFD directive, specifying a COBOL field greater than 8000 bytes.
CHAR(n) | A COBOL field of type PIC X(n), where n is less than 8000 characters/digits.
TEXT | A COBOL field of type PIC X(n), where n is 8000 characters/digits or more.
VARCHAR(n) | The VAR_LENGTH XFD directive and specifying a COBOL field of type PIC X(n), where n refers to the size of the COBOL field.
FLOAT | A COBOL field of type DOUBLE.
REAL | A COBOL field of type FLOAT.
TINYINT | A COBOL field of type PIC 9 or PIC 99.
SMALLINT | A COBOL field of type PIC 9(3) or PIC 9(4).
INT | A COBOL field of type of PIC 9(5) to PIC 9(8).
BIGINT | A COBOL field of type PIC 9(9) to PIC 9(18).
DECIMAL(n,0) | A COBOL field of type PIC(19) or larger, where n is the number of digits.
DECIMAL(n,m) | A decimal COBOL field, where n refers to the number of digits to the left of the decimal point and m refers to the number of digits to the right of the decimal point.
Parent topic: Database Connectors for Microsoft SQL Server


Run-Time System Errors

This topic lists the Database Connectors for MSSQL 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.

Run time system errors have this format:

9/xxx

The 9 indicates a file system error and is reported as the first digit of the 2-byte FILE STATUS variable. The xxx is a binary value between 1 and 255 that provides more information. If xxx is 83, you can retrieve additional information with C$RERR. That additional information can come from either the database or the run time. Run-time codes returned with C$RERR include:

Code | Message
1 | Error reading the XFD file
2 | Corrupt XFD/dictionary file
3 | Error opening the XFD file
4 | Too many key fields (16 max)
5 | (no message associated with this error)
11 | Stored procedures not found
12 | A column of a key is of data type TEXT or IMAGE, which is illegal
13 | Internal error
14 | DB library function returned an unexpected error
16 | Trying to rename a table across databases
17 | Cache error
18 | Primary Key error
19 | Table Size Error
20 | (no message associated with this error)
21 | (no message associated with this error)

For a detailed description of a particular message, see the Error Messages section.

Parent topic: Database Connectors for Microsoft SQL Server
Related reference
Troubleshooting
Error Messages


Common Questions and Answers

This topic contains some questions and answers specific to Database Connectors for MSSQL.

When I try to open a file for output, I get the error 9/83, with a secondary error of 2714. There is already an object named "*" in the database. Why?
One of your record's data items probably has the same name as an MSSQL reserved word. Locate the column by comparing a file trace of the CREATE TABLE to MSSQL's list of reserved words. Apply the NAME XFD directive to the field in the file descriptor (FD) that is associated with the invalid column, then recompile the program to create a new XFD file.
Can I open tables in different databases?
Yes. Use a file name like:
database.owner.tablename

Note that, because Database Connectors for MSSQL automatically determines an owner, you can also specify a file name like database..tablename. The two dots are mandatory in this case.

Can I use multiple servers (on the same machine or on different machines) on my network?
Yes. Each server has a unique name.
I'm getting an error 9/83, with a secondary error of 11, Micro Focus lock table missing. I know that I added the lock table during installation.
This is probably a permissions problem. All users must have READ, WRITE, UPDATE, and DELETE access to AcuLocks1 (and therefore to the database that contains it). Be sure to check your permissions.
I keep receiving an error message saying that my login is invalid. But I'm sure I'm using the correct username and password.
All usernames, passwords, and database names are case-sensitive. Be sure that you are typing the names exactly as they are set up.
Are there any library routines that do not work with or would not make sense to use with Database Connectors for MSSQL?
Two library routines that either don't work with or do not make sense to use with Database Connectors for MSSQL: C$COPY and C$RECOVER.
Parent topic: Database Connectors for Microsoft SQL Server


Performance and Troubleshooting

These topics provide guidelines for improving system performance. Also included is an alphabetical listing of the error messages that can occur during the compilation of your program.

  • Performance Issues
  • Troubleshooting
  • Contacting Micro Focus
Parent topic: Database Connectors User's Guide


Performance Issues

Adding a relational database management system (RDBMS) brings significant complexity to any computer system. A few key guidelines may help to improve the performance of the system and prevent problems as the database grows.

  • Guidelines
  • The WHERE Constraint
Parent topic: Performance and Troubleshooting


Guidelines

The following sections describe some areas for you to consider as you prepare your database and your COBOL program.

  • Guideline 1 - Database Administrator
    A site with a database management system needs a database administrator (DBA).
  • Guideline 2 - Understand COBOL Operations and Database Operations
    Some COBOL operations are particularly stressful to a database. The more the application uses these operations, the more likely performance of the RDBMS suffers.
  • Guideline 3 - Program and Database Interaction
    A database can interact with your COBOL program in surprising ways. When you introduce a database to a mature COBOL application, your application may experience errors you have not seen before. Being aware of this possibility helps you to prepare your response.
  • Guideline 4 - Plan for Growth
    Planning ahead for growth is essential. You need to be able to predict the system resources that your application requires reaching a full load, both in terms of users and database size.
Parent topic: Performance Issues


Guideline 1 - Database Administrator

A site with a database management system needs a database administrator (DBA).

The administrator is critical in any large database installation. The DBA checks performance statistics and memory usage, performs general maintenance and backup for the database, initiates traces, executes database utilities, sets buffer sizes, determines how often buffers are flushing, and, in general, understands how database settings and events affect overall performance.

The DBA also performs periodic tuning of the database, including:

  • Using monitoring tools
  • Allocating table spaces
  • Examining output of the query optimizer to ensure that the most efficient paths are being used
  • Updating table statistics
  • Creating indexes

If a site experiences a performance problem, this usually means there is a bottleneck somewhere in the system. The DBA can help to isolate the bottleneck. Once the bottleneck is identified, the site can determine whether to apply more resources or correct the situation that is stressing the existing resources.

Parent topic: Guidelines


Guideline 2 - Understand COBOL Operations and Database Operations

Some COBOL operations are particularly stressful to a database. The more the application uses these operations, the more likely performance of the RDBMS suffers.

The more you understand about your RDBMS and how it operates, the more you can help your COBOL applications to work efficiently with it.

File Input and Output

Consider these standard COBOL I/O operations:

  • READ
  • REWRITE
  • WRITE
  • DELETE
  • OPEN

Each has an associated cost in terms of database performance. Each asks the database to do something that takes time. So if there are I/O operations that are optional in your COBOL application, you may want to remove them.

For example, developers sometimes OPEN and CLOSE files unnecessarily, using the OPEN–CLOSE pairing as a way to demarcate each new procedure:

OPEN file-A
procedural code
CLOSE file-A

But it's important to know that file OPENs are expensive in terms of performance. If you can eliminate non-essential OPENs from portions of your application, you may improve performance.

READ operations can also affect performance. All COBOL I/O is based on key indexes. Examining the output of your query optimizer allows you to determine if the most efficient execution path is being used for READs. The execution path for a given query can change as your data changes and as the size of the tables changes. It is also affected by database initialization parameters and any statistical information that you may have gathered on your tables. It might be helpful to know that, typically, the index path is the most efficient for Database Connectors applications.

Tables with Multiple Indexes

If using 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, possibly leading other operations in the database query optimizer to become confused.

There are two things you can do in this circumstance:

  • Restructure your data. The benefits of data restructuring may be significant. For example, if you have any situations in which two indexes start out with the same column or set of columns, you may be able to improve performance appreciably by changing your data definition.

    Suppose two indexes both start with MONTH, DAY, YEAR. These identical first three columns can cause the RDBMS's query optimizer to choose the wrong index, in which case you generate a significant amount of unnecessary and unproductive overhead. Restructuring one of the indexes can make a difference.

  • Use the file system for some of your data. If you cannot restructure your data but are finding certain operations to be too expensive, you might want to consider moving some data into the indexed file system.
Guiding the Data Searches
You can guide the data searches that result from database queries, and thus improve performance, by making use of an external variable called A4GL_WHERE_CONSTRAINT. This process is explained in The WHERE Constraint.
Parent topic: Guidelines


Guideline 3 - Program and Database Interaction

A database can interact with your COBOL program in surprising ways. When you introduce a database to a mature COBOL application, your application may experience errors you have not seen before. Being aware of this possibility helps you to prepare your response.

For example, your existing application without the database may rarely exceed the limit on the number of data files that can be open simultaneously. But when you add a database, you increase the likelihood of this significantly. This is because the query processing may often require temporary tables. Also, the ORDER BY clause (used in SQL statements to sequence retrieved data) opens temporary work files. So you may find that these files cause you to reach the limit sooner. (Note that proper tuning of the query optimizer can reduce the number of temporary files required.)

When upgrading to a new version of the RDBMS, new software components may interact with your applications differently than their predecessors did. It's important to rerun your pre-installation simulations (see Guideline 4 — Plan for Growth) to determine whether your system resources are still adequate. Investigate any differences in the two simulations. You may have to make adjustments to compensate for the differences in the RDBMS versions.

Upgrading or switching to a new database may also mean that you need to modify your makefile to coordinate with the settings of the new database. You can edit your makefile manually, or use one of the editing tools distributed with Database Connectors. For more information, see the Appendix specific to the Connector interface you are using.

If you notice a change in performance with a new release of your RDBMS, keep in mind that certain database settings can have a significant effect on speed. Fine-tuning your settings can make a difference.

Several other options to help you improve performance are listed below:

  • Try one of the many third-party monitoring tools available
  • Break up data onto several connected hard drives to free up space and resources
  • Limit the number of indexes you have assigned with each database table
Parent topic: Guidelines


Guideline 4 - Plan for Growth

Planning ahead for growth is essential. You need to be able to predict the system resources that your application requires reaching a full load, both in terms of users and database size.

Before you choose and install hardware, it is best to run a simulation. Your hardware vendor or RDBMS vendor can help you to simulate a large number of users on a given platform with a given RDBMS.

Setting up and running a simulation that includes your own application lets you anticipate when moving into any installation, and can help to mitigate costly consequences.

A potentially costly mistake is to test in your office with a small database and a small number of users. Databases respond differently to small amounts of data and a small number of users than they do to large amounts of data and a large number of users. Functionally, the database works differently as the load increases. You might think of this adjustment as switching into a different gear. Significant changes in the load on your database can lead to large increases in overhead. The behaviors and loads encountered as the database expands cannot be determined from a linear projection based on the smaller scenario.

Parent topic: Guidelines


The WHERE Constraint

The Database Connectors WHERE constraint is an external variable that gives the developer some control over the data searches that result from database queries. It can help to improve performance in some situations. This topic describes purpose of the WHERE constraint and shows how it is implemented.

COBOL data processing is based on keyed READ operations following a positioning operation. Records are read until the key value being processed changes. Because traditional COBOL data processing is based on a B+ tree file system, the overhead for such operations is relatively minor.

RDBMS data processing introduces a new level of complexity to data processing. The database's query optimizer receives the SQL query for the COBOL operation being performed and then builds a working set of data that satisfies that query. Because the database optimizer has many different possible execution methods, this can result in poor performance if the optimizer chooses a query execution path that is less than optimal.

Performance degradation may also result from the fact that queries generated by COBOL operations result in unbounded index queries. Unbounded queries are generated because COBOL positioning operations (Start Not Less Than and Start Not Greater Than) provide only one of the bounding conditions for the working set, instead of both an upper and lower boundary.

As an example, consider the case in which an application needs to process all items in a warehouse on aisle 17, shelf 8, and bin 2. If each of these items is a field in a key, the COBOL program might generate the following query for a READ operation:

Note: The following example applies to the Database Connectors for Oracle product. The SQL generated differs based on the interface.
SELECT * FROM warehouse_items WHERE
   aisle = 17 and
   shelf = 8 and
   bin = 2
ORDER BY aisle ASC, shelf ASC, bin ASC;

This query achieves the desired result but has one problem. For the COBOL program to end its processing, it must read a record that has a new value for bin. The COBOL application has no way of specifying an upper boundary for the read operation, so when all rows of data from bin 2 have been read, the Connector attempts to read the next record by generating the following query:

SELECT * FROM warehouse_items WHERE
   aisle = 17 and
   shelf = 8 and
   bin > 2
ORDER BY aisle ASC, shelf ASC, bin ASC;

This query causes the database query optimizer to gather all records pertaining to items on the remainder of shelf 8 to build its working set. This is excessive from the COBOL application's point of view, because the COBOL program needs only the first record of the working set to determine that it has finished processing.

This problem can be even more serious if the application is processing the last bin on a shelf. Because there are no more bins on that shelf, the query would drop down a level and generate the following:

SELECT * FROM warehouse_items WHERE
   aisle = 17 and
   shelf > 8
ORDER BY aisle ASC, shelf ASC, bin ASC;

This would select all items on the remainder of that aisle of the warehouse, which could be a very large working set if each aisle had 130 shelves.

The database query optimizer normally does not build the entire working set if it has been properly tuned, but instead performs INDEXED READS to process the query. This means that the query optimizer traverses an index tree to retrieve the records, much as COBOL index files do, as opposed to using combinations of indexes and sort and merge operations.

It can be helpful to influence precisely which information is to be returned. If the precise scope of the record processing performed by the read operations is known at compile time (or before the query is executed), information retrieval can be more finely tuned.

Database Connectors provides a method by which the COBOL programmer can provide additional information to the database query optimizer by providing more specific selection information to the Connector. This selection information is added to the WHERE clause of the SQL queries generated by the Connector. This can be particularly useful in providing upper boundaries to queries being generated, with the result that the working set is smaller.

Upper boundaries can be provided on the key segments for a select, or any other selection criteria needed to constrain the working set to just the desired subset of rows. This additional information is added to generated queries with the AND condition. It is not possible for the application developer to specify a larger working set than would otherwise have resulted. The developer may only constrain the working set to a smaller subset.

Parent topic: Performance Issues
Related information
To use the WHERE constraint from COBOL


Troubleshooting

The following topics list the possible error messages that can occur during compilation or run time.

Note: You may experience slower performance simply because the Database Connectors application is limited by of the rules of COBOL. There are many third-party tools available to help monitor performance levels.
  • Setting External Variables
  • Compiler Errors
  • Run-Time System Errors
Parent topic: Performance and Troubleshooting


Setting External Variables

When using Database Connectors, file status variables and declarations may not be able to provide enough information to determine the cause of the error and the recommended resolution.

Setting external variables can provide additional information in troubleshooting situations. For example, if the database returns an error that does not map to one of the standard COBOL errors (record locked, duplicate key, etc.), the external variables F-ERRNO, F-INT-ERRNO, F-INT2-ERRNO and F-ERRMSG contain additional information. Typically, F-ERRNO is E_INTERNAL (which has a value of 15). F-INT-ERRNO and F-INT2-ERRNO are the error values returned by the database, and F-ERRMSG points to a text message returned by the database.

Add the following EXTERNAL declarations to working-storage:

01  F-INT-ERRNO   BINARY-LONG  SIGNED 	EXTERNAL.
01  F-INT2-ERRNO  BINARY-LONG  SIGNED	 EXTERNAL.
01  F-ERRMSG      POINTER             	EXTERNAL.
77  F-ERRNO       BINARY-SHORT SIGNED 	EXTERNAL.
Note: Not all errors return textual messages.
Parent topic: Troubleshooting


Compiler Errors

Compilation errors can occur when you compile with the CREATEXFD directive. In some cases, the XFD file cannot be built until you remove the error condition. (XFD errors do not, however, prevent the object code from being generated.) For information about XFDs, see the XFDs section.

For detailed information about a particular error message, see the Compiler Errors section.

Parent topic: Troubleshooting
Related reference
Compiler Errors


Run-Time System Errors

You can determine the meanings of your database error codes by referring to the database documentation.

You can see run-time system errors by using the FILE_TRACE ACUFH configuration variable with the Consolidated Tracing Facility (CTF).

Unless noted otherwise, this method applies to all RDBMSs supported by the Database Connectors family of interfaces. For a listing of run-time system errors, refer to the Run-Time System Errors topic of the section specific to your RDBMS.

  • Using the FILE_TRACE ACUFH Configuration Variable
Parent topic: Troubleshooting
Related reference
FILE TRACE configuration variable
Run-time System Errors for MSSQL
Run-time System Errors for DB2
Run-time System Errors for Oracle


Using the FILE_TRACE ACUFH Configuration Variable

At run time, if you specify a CTF trace file and set the FILE_TRACE ACUFH configuration variable, the run time system puts the extended error code and some text associated with the error into the error file.

The text of the error would then have this format in the file:

*** File system error value = 3 ***
*** Dictionary (.xfd) file not found***
File error 9S,03 on filename
Dictionary (.xfd) file not found

Occasionally you may receive an error message that means syntax error. You can examine the CTF trace file and determine the cause of the problem if you receive this error code.

Parent topic: Run-Time System Errors
Related reference
To enable tracing for Database Connectors


Contacting Micro Focus

Our Web site gives up-to-date details of contact numbers and addresses.

  • Further Information and Product Support
  • Information We Need
  • Contact information
Parent topic: Performance and Troubleshooting


Further Information and Product Support

Additional technical information or advice is available from several sources.

The product support pages contain a considerable amount of additional information, such as:

  • The Product Updates section of the Micro Focus SupportLine Web site, where you can download fixes and documentation updates.
  • The Examples and Utilities section of the Micro Focus SupportLine Web site, including demos and additional product documentation.
  • The Support Resources section of the Micro Focus SupportLine Web site, that includes troubleshooting guides and information about how to raise an incident.

To connect, enter http://www.microfocus.com in your browser to go to the Micro Focus home page, then click Support.

Note: Some information may be available only to customers who have maintenance agreements.

If you obtained this product directly from Micro Focus, contact us as described on the Micro Focus Web site, www.microfocus.com. If you obtained the product from another source, such as an authorized distributor, contact them for help first. If they are unable to help, contact us.

Also, visit:

  • The Micro Focus Community Web site, where you can browse the Knowledge Base, read articles and blogs, find demonstration programs and examples, and discuss this product with other users and Micro Focus specialists.
  • The Micro Focus YouTube channel for videos related to your product.
Parent topic: Contacting Micro Focus
Related information
Micro Focus SupportLine
Examples and Utilities
Support Resources
Micro Focus Community
White Papers and Webcasts
Micro Focus YouTube Channel


Information We Need

However you contact us, please try to include the information below, if you have it. The more information you can give, the better Micro Focus SupportLine can help you. But if you don't know all the answers, or you think some are irrelevant to your problem, please give whatever information you have.

  • The name and version number of all products that you think might be causing a problem.
  • Your computer make and model.
  • Your operating system version number and details of any networking software you are using.
  • The amount of memory in your computer.
  • The relevant page reference or section in the documentation.
  • Your serial number. To find out these numbers, look .

On UNIX, you can use the Micro Focus UNIX Support Scan Utility, mfsupport, to create a log file that contains the details about your environment, product, and settings. The mfsupport script is stored in $COBDIR/bin.

To run mfsupport:

  1. Start a UNIX shell.
  2. Set COBDIR to the product with issues.
  3. Execute mfsupport from a directory where you have write permissions.

    This creates a log file, mfpoll.txt, in that directory.

  4. When the script finishes, send the mfpoll.txt file to your Micro Focus SupportLine representative.
Note:

If COBDIR is set to a location that does not contain etc/cobver or COBDIR is not set, mfsupport gives you the option to search your machine for possible product locations. Note that the search can take some time if you have a large amount of disc storage and files.

Parent topic: Contacting Micro Focus


Contact information

Our Web site gives up-to-date details of contact numbers and addresses.

Additional technical information or advice is available from several sources.

The product support pages contain considerable additional information, including the Product Updates section of the Micro Focus SupportLine Web site, where you can download fixes and documentation updates. To connect, enter http://www.microfocus.com in your browser to go to the Micro Focus home page, then click Support.

If you are a Micro Focus SupportLine customer, please see the Welcome to Customer Care document that includes information about downloading and licensing your product, contacting Customer Care, and about reporting an incident. You can download it from our Web site. Support from Micro Focus may be available only to customers who have maintenance agreements.

Parent topic: Contacting Micro Focus
Related information
Micro Focus SupportLine
Welcome to Customer Care


Questions and Answers

The following are some answers to common questions that can apply to any relational database management system (RDBMS) data source supported by the Database Connectors family of interfaces.

I can't seem to get ACUFH configuration file to create the files in my database. They keep coming up as indexed files.
Check to see that you have the correct IDXFORMAT in effect. Specify it in the source code, on the command line or in the project properties during compilation, or at run time in an EXTFH configuration file.
How can I find out what an error message is?
If you run your application with the FILE_TRACE ACUFH configuration file variable, you receive extended error numbers that include those returned by your RDBMS application.
Note: For Oracle, if you are using the OERR command-line utility, the error code must include five digits. If your error code does not already include five digits, you must add leading zeros to complete this requirement. For example, if your error code is code number 150, the syntax for this option is OERR ORA 00150.

If you have sent the errors to an output file with the FILE_TRACE ACUFH configuration variable, the run time system also attempts to include text that explains the errors. See the example in the section Runtime System Errors in the chapter Performance and Troubleshooting.

Do my eXtended File Descriptor (XFD) files have to be in the same directory as my object files?
No. You can instruct the compiler to put the XFD files in an alternate directory with the CREATEXFD directive. Then at run time, make sure you have the ACUFH configuration file variable XFD_PREFIX set to include that same directory.
Why aren't my KEYs being retrieved in the correct order?
If your KEY field is numeric or alphanumeric, you may have illegal data in the field. For example, if you've used LOW-VALUES or HIGH-VALUES to mark control records, those values are considered invalid and can cause the records containing them to be retrieved in an unexpected sequence.

To enable special values such as these to be processed, use the BINARY XFD directive in front of the key field. This allows data of any classification to be processed. Either designate an individual field as binary, or specify USE GROUP, BINARY in front of a group of fields.

The method of storing variables declared as binary is database-specific. For example, for Oracle databases, variables declared as binary are stored as raw fields. Refer to your database documentation for information specific to your data source or RDBMS.

Is it possible to use the same XFD file for data files with different names, if they all have the same structure? This would be useful when I create several customer files that use the same record definitions.
At run time, it is possible to use a single XFD for files that have different names. For example, suppose a site has customer files that have identical structures but different names (such as CUST0001, CUST0002, and CUST0003). It's not necessary to have a separate XFD for each file, as long as their record definitions are the same. The individual files can all be mapped to the same XFD via the configuration variable XFD_MAP. Here's how it works.

Suppose your COBOL application has a SELECT with a variable ASSIGN name, such as customer-file. This variable assumes different values (such as CUST0001 and CUST0002) during program execution.

Before compiling the application, you would use the FILE XFD directive to provide a base name for the XFD. Suppose you provide CUST as the base. The Compiler would then generate an XFD named cust.xfd. (The Compiler always converts XFD names to lower case.)

To ensure that all customer files, each having a unique name, use this same XFD, enter the following in your configuration file:

XFD_MAP  CUST* = CUST

The asterisk (*) in the example is a wildcard that matches any number of characters. The extension .xfd should not be included in the map. This statement would cause the XFD cust.xfd to be used for all files whose names begin with CUST.

The XFD_MAP variable has this syntax:

XFD_MAP   [pattern = base-xfd-name]  ...
where pattern consists of any valid filename characters and may include "*" or "?". These two characters have special meanings in the pattern:
* matches any number of characters
? matches a single occurrence of any character
For example:
CUST???? matches CUST0001 and CUSTOMER;

does not match CUST001 or CUST00001

CUST* matches all of the above
CUST*1 matches CUST001 and CUST0001 and CUST00001;

does not match CUSTOMER

*OMER matches CUSTOMER;

does not match CUST001 or CUST0001

The XFD_MAP variable is read during the open file stage with the run time system.
I'm unsuccessful in trying to create a table in MSSQL Server, and when I check the trace log, I find the following "File Error:9k":
ssinit_connection sss
Trying to open a connection to server MyBadServer
report_status called from 1176
[1] 08001 17 – [DBNETLIB]SQL Server does not exist or access denied.
[2] 01000 53 – [DBNETLIB]ConnectionOpen (Connect()).sssssss
where MyBadServer is the value set in my configuration file for A-MSSQL-DEFAULT-CONNECTION.
You might be using the wrong server name in the A-MSSQL-DEFAULT-CONNECTION variable. One way you can verify the server name is by following the steps below. This method is useful if you don't have the client tools for SQL Server installed.
  1. Create a new ODBC Datasource for SQL Server.
  2. When prompted for the server name, select your local server from the drop-down list of available servers.
  3. Connect to this server using ODBC Administrator.
This gives the server name you need to use in the A-MSSQL-DEFAULT-CONNECTION variable.
Parent topic: Database Connectors User's Guide


Database Connectors Tasks

  • To Configure a Login
  • To create an error map
  • To enable tracing for Database Connectors connections
  • To install Database Connectors for Microsoft SQL Server
  • To prepare your program for execution using Database Connectors
  • To set the system parameters
  • To Set Up a Service Name for an Oracle Database
  • To Set Up The DB2 User Environment
  • To compile and run the demo program
  • To Test the Installation of Oracle's Instant Client
  • To use the WHERE constraint from COBOL


To Configure a Login

Note: You will probably need database administrator privileges to perform the following action.
If you want to specify a database login name other than the system login name, you can grant it the necessary privileges using the following SQL syntax, when connected to the database:
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.

Parent topic: Database Connectors Tasks
Related reference
Setting Up a User Account


To create an error map

Create an error map file to map DB2 error messages to COBOL error responses. This file is optional.

Although you can check your data source documentation for error code information, the easiest way to determine which error codes need to be mapped to more appropriate COBOL codes is through trial and error. As users use Database Connectors DB2, they may report receiving error messages that don't make sense based on their situation. Research these errors and try to determine a more appropriate COBOL error response.

When you create your error map file, use the following guidelines:

  • Begin comment lines with "#". Blank lines are also considered comments
  • Break the rest of the file into sections, with each section header comprising all the information enclosed in brackets from the data source error function.

    The following example leads to the entry in the sample DB2 error file:

      OdbcOneInfo: State: 23505, Native Error: -803
  '[IBM][CLI Driver][DB2/NT]SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because they would produce duplicate rows for a table with a primary key, unique constraint, or unique index.  SQLSTATE=23505'
  Setting f_errno = 19
Leaving, error
***File system error value = -803 ***
>>>file status = 9S,-803
  • Include two fields in each line in the section: the internal error number, and a mapping string.

    Using the same example, you would include this line in the error_map file:

    -803 E_DUPLICATE

    The valid values for the second field are as follows:

    E_SYS_ERR
E_PARAM_ERR
E_TOO_MANY_FILES
E_MODE_CLASH
E_REC_LOCKED
E_BROKEN
E_DUPLICATE
E_NOT_FOUND
E_UNDEF_RECORD
E_DISK_FULL
E_FILE_LOCKED
E_REC_CHANGED
E_MISMATCH
E_NO_MEMORY
E_MISSING_FILE
E_PERMISSION
E_NO_SUPPORT
E_NO_LOCKS
Parent topic: Database Connectors Tasks
Related reference
A DB2 ERROR MAP FILE configuration variable


To enable tracing for Database Connectors connections

Edit a Consolidated Tracing Facility (CTF) configuration file to include the tracing elements for Database Connectors.

  1. Edit or create a CTF configuration file, config.ctf, and ensure it includes:
    mftrace.comp.mf.rts#eprintf
mftrace.level=info
  2. Set the MFTRACE_CONFIG and MFTRACE_LOGS environment variables:
    MFTRACE_CONFIG=config.ctf
MFTRACE_LOGS=.
    Note: These values specify that a configuration file called config.ctf is used within the current working directory, and that the tracing log file is also created in the current working directory. You may change these values to suit your needs.
  3. In your ACUFH configuration file, set the following variables:
    FILE-TRACE 1
FILE-TRACE-FLUSH 1
  4. Run your Database Connectors COBOL application.

    The interaction is traced in the log file, located in the current working directory.

Parent topic: Database Connectors Tasks
Related reference
FILE TRACE configuration variable
FILE TRACE FLUSH configuration variable


To install Database Connectors for Microsoft SQL Server

Note: Your Microsoft SQL Server can be installed on a separate server, or on the same machine as your client. See the Microsoft SQL Server help for details on how to configure the relevant communications.
  1. Install a supported version of Microsoft SQL Server.

    You must install and configure Microsoft SQL Server, version 6.5 or later, on the Windows server machine before you install Database Connectors for MSSQL on the client machine. Follow the instructions from your RDBMS vendor.

  2. Copy MS_INST.CMD from the install sub-directory of your Micro Focus product installation directory to a directory on the server.

    MS_INST.CMD is the batch file supplied by Micro Focus that creates the stored procedures necessary for using Database Connectors.

  3. Execute the batch file using the following command:

    MS_INST LockDatabase

    where LockDatabase is the database you want to use for the internal lock tables; this does not need to be an existing database, as one will be created, if required, in the next step. However, you must ensure the required Database Connectors users have write access to this database.

    Executing this command produces a MS_INST.SQL file.

  4. Execute the contents of this file (MS_INST.SQL) using the SQL Query Analyzer or the SQL Server Management Studio (which depends on the version of SQL Server you have installed).

    When complete, the stored procedures necessary for the MSSQL connector are installed.

This completes the setup on the Windows server machine.

Note: If you are upgrading from an earlier version of Database Connectors, be sure to install the new stored procedures. Stored procedures are always upgraded in such a way that they are compatible with older versions of the product, so installing new stored procedures over old ones does not affect your ability to run with an older version of the interface software. Your new version of Database Connectors for MSSQL may not run properly without the corresponding stored procedures.

It can be difficult to maintain multiple copies of stored procedures; therefore, we recommend that you continue to create the stored procedures in the same database. If your installation does not permit this, you do have the flexibility to create the stored procedures elsewhere. However, to facilitate maintenance of the stored procedures, we recommend that you create as few databases as possible.

Parent topic: Database Connectors Tasks


To prepare your program for execution using Database Connectors

There are a number of mandatory and optional steps that you must perform to prepare your COBOL program, and then run it to interface with an RDBMS. Some of these steps can be achieved a number of different ways, with some offering greater benefits over others.

The CREATEXFD directive
This directive must be specified in order to create the .XFD file required to interface with an RDBMS.
You would usually specify it within your source code or during compilation (either on the command line or within project properties). It does not matter where you specify it as long as it is picked up during compilation.
If you specify it without any further parameters, an XFD file is only created for each indexed file specified within your source code. Specify FILE-TYPE=ALL within parentheses to create XFD files for all file organizations.
By default, any XFD files are created in the current working folder, to specify another directory in which to place them, specify XFD-DIRECTORY=pathname within parentheses to create XFD files in another directory.
See CREATEXFD XFD Directive for more information.
Additional XFD directives
These directives are optional, and are used if you want to override the default settings when creating or amending a table in your RBDMS.
These directives are always placed within a COBOL FD in your source code. They do not affect Procedure Division I/O statements, and they do not change your COBOL fields in any way. Rather, they guide the building of the XFDs, giving you more control over the way COBOL data maps to database fields.
See XFD Directives for more information.
The IDXFORMAT directive or File Handler configuration option
The IDXFORMAT option is mandatory, and tells the COBOL run-time system which RDBMS it is interfacing with when your program executes a FILE I/O operation.
You can set the directive directly into your source code, or during compilation (either on the command line or within project properties), but if you specify the option within a File Handler configuration file, you can simply change the value at run time, enabling you to use the same compiled code to interface with different types of RDBMSs.
See Configuring the File Handler for more information.
Database configuration options
Each supported RDBMS has a number of specific configuration options that you can set. Some are mandatory (such as specifying the database to work with) and some are optional. There are also a number of general configuration options that can be applied to any RDBMS.
You specify these options within the ACUFH configuration file, and then use the A_CONFIG environment variable to make the file available at run time.
See ACUFH Configuration File Variables for RDBMS and ACUFH Configuration Variables for more information.
Parent topic: Database Connectors Tasks
Related reference
CREATEXFD XFD directive
XFD Directives
Configuring the File Handler
ACUFH Configuration Variables
ACUFH Configuration File Variables for DB2
ACUFH Configuration File Variables for MSSQL
ACUFH Configuration File Variables for Oracle


To set the system parameters

Set the Oracle system parameter OPEN_CURSORS to indicate the total number of cursors you want to have. Look in Oracle's init.ora and initSID.ora, or any other database initialization files used at your site, and add or modify the line:

open_cursors = nnn

The number of cursors can be equal to the number of keys times the number of segments per key for each file. For example, if you have 5 files, each of which has 3 keys, and each key has 3 segments, you should set OPEN_CURSORS to a minimum of "45".

Note: Setting OPEN_CURSORS should be performed only by your system administrator or database administrator, because it affects all applications using the database.
Parent topic: Database Connectors Tasks


To Set Up a Service Name for an Oracle Database

If you are using Oracle client software to connect to a database not on the current machine, you must set up a service name for your database. To set this up, you need the following details from your database administrator:

  • Service name
  • Protocol
  • Host name
  • Port

When you have these, you can use Oracle's Net Configuration Assistant, which is supplied with the Oracle client install. After entering the correct details, the wizard enables you to test the connection.

Tip: You can also test the connection using SQL*Plus: use user-name@service-name for the login.

If successful, you are ready to start configuring the Database Connectors client by editing the ACUFH configuration file. These connection details equate to the A_ORA_DATABASE (service name), ORA_LOGIN (user-name), and ORA_PASSWD (password) configuration variables.

Parent topic: Database Connectors Tasks
Related reference
A ORA DATABASE configuration variable
ORA LOGIN configuration variable
ORA PASSWD configuration variable


To Set Up The DB2 User Environment

  1. Create a plain text file that will serve as your ACUFH configuration file.
  2. Within the ACUFH configuration file, set the A_DB2_LOGIN and A_DB2_PASSWD variables, like so:
    A_DB2_LOGIN user-name
A_DB2_PASSWD passwd
    Note: Alternatively, you may not want to specify such details in a file. In which case, you can either set them as environment variables, or set the password variable from your COBOL program by prompting the user to enter a password and then executing:
    SET ENVIRONMENT "A_DB2_PASSWD" TO user-entry
  4. Set the A_DB2_DATASOURCE variable using one of the methods mentioned in the previous step.
  5. Define any other ACUFH configuration file variables you require for your unique environment. For example, consider whether or not setting the USER_PATH variable would be useful in your setup situation.
    Note: Due to current limitations with the interface, ensure A_DB2_USE_CHAR_FOR_BINARY is included and set to 1.
  6. Set the A_CONFIG environment variable to identify your ACUFH configuration file.
Parent topic: Database Connectors Tasks
Related reference
USER_PATH (for DB2) configuration variable
A_DB2_LOGIN configuration variable
A_DB2_PASSWD configuration variable
Run Time System Configuration
ACUFH Configuration File Variables for DB2
A_DB2_USE_CHAR_FOR_BINARY configuration variable


To compile and run the demo program

Before you compile the demo program, ensure that your RDBMS data source is available and correctly configured. The demo program (demo.cbl) by default is located in the Connectors sub-directory within your Samples (Windows) or $COBDIR/demo (UNIX) directory.

  1. From a Net Express command or shell prompt, set the environment variable A_CONFIG to the path of your ACUFH configuration file.
    Note: See the specific RDBMS sections for details of the variables your configuration file should contain as a minimum requirement. You do not need to add the variables for user name and password, as these are prompted for when the program runs.

    Windows:

    set A_CONFIG=c:\path\to\mycfg.cfg

    UNIX:

    A_CONFIG=/path/to/myconfig.cfg
export A_CONFIG
  2. Enter the compilation command:

    Windows:

    cobol demo.cbl IDXFORMAT"nn" NOGNT"";

    UNIX:

    cob demo.cbl IDXFORMAT"nn";

    where nn is either 18 (for MSSQL), 19 (for Oracle) or 20 (for DB2).

    Note: The CREATEXFD directive is also a minimum requirement, but this is explicitly specified within the program.

    An executable file (demo.int) and an XFD file (orderfile.xfd) is built.

  3. After compilation, run the program:

    Windows:

    run demo.int

    UNIX:

    cobrun demo.int
    Note:

    If you are not running the executable from the same directory as the XFD file, you will also need to add XFD_PREFIX path/to/xfd-file to your ACUFH configuration file.

    On HP-UX platforms, you must use the cobrun_t syntax so that your application and the RDBMS both use a multi-threaded run time system; a mix of single- and multi-threaded run time systems will produce an error.

  4. When prompted, enter a valid username and password for your RDBMS.

  5. When the following screen appears, use A)dd to add a new record.

    When you save the record, it is written to a new table (orderfile) within the database specified in the ACUFH configuration file.

  6. Press Q)uit to end the demo program.
Parent topic: Database Connectors Tasks
Related reference
Run Time System Configuration
File Handling Configuration
Configuring the File Handler for DB2
Configuring the File Handler for Microsoft SQL Server
Configuring the File Handler for Oracle


To Test the Installation of Oracle's Instant Client

Go to a different directory from the one on which you installed Oracle's Instant Client and enter the following command:

sqlplus scott@bigdb/tiger
select user from dual;

If this test is successful, you are ready to use the run-time. The run time loads the Oracle Instant Client dynamically from the specified path.

Parent topic: Database Connectors Tasks


To use the WHERE constraint from COBOL

The following steps are required for using the WHERE constraint.

  1. Declare an External Variable

    To make use of WHERE constraints from COBOL, the application must declare an external variable for communication with the Database Connectors interface. This variable is declared as follows:

    77  a4gl-where-constraint  pic x(32750) external.
  2. Modify Your COBOL Procedures

    Your COBOL application should move the information that you want added to the WHERE clause to the new external variable before a COBOL positioning operation such as START or READ is performed. The additional constraint is then applied to any SQL read query performed on that file until a new positioning operation is performed.

    The additional query information is also stored in the Connector's cursor cache, so that if the same read conditions occur in later processing, the existing closed cursor can be reused with new bind variables instead of being regenerated.

    Be sure to fill the external variable before a positioning operation (START or READ). The WHERE constraint affects only READ NEXT operations preceded by a positioning operation. The WHERE constraint does not affect a READ NEXT that was not preceded by a positioning operation (such as a READ NEXT without a START immediately after opening a file).

Example

In your COBOL program you include this statement:

Move "ftest_key > 3 and ftest_key < 6" to
   A4GL_WHERE_CONSTRAINT.
Inspect A4GL_WHERE_CONSTRAINT replacing trailing spaces by
   low-values.
START FTEST-FILE KEY NOT LESS FTEST-KEY.

These results occur:

CURSOR 0:
SELECT *,ROWID FROM ftest WHERE (ftest_key1_seg1 = ? AND
ftest_key1_seg2 >= ?) AND (ftest_key > 3 and ftest_key < 6)
ORDER BY ftest_key1_seg1 ASC, ftest_key1_seg2 ASC;
CURSOR 1:
SELECT *,ROWID FROM ftest WHERE (ftest_key1_seg1 > ?) AND
(ftest_key > 3 and ftest_key < 6) ORDER BY ftest_key1_seg1 ASC,
ftest_key1_seg2 ASC;

Limitations

  • WHERE constraints added by the COBOL program may not be portable between databases.
  • The application may specify conditions of such complexity that they confuse the database query optimizer, resulting in poor performance. Be sure to examine the results of the optimizer trace facilities to ensure optimal performance.
  • Verify the COBOL application is not sending information that results in a syntax error that is not detected until run time.
  • The WHERE constraint should reference only columns in the primary table.
  • If users have different configurations at different sites, so that some files can be vision files at one site, but Acu4GL tables at another site, then the WHERE-CONSTRAINT can be difficult to use. This happens because after the WHERE-CONSTRAINT is used, it is marked empty so it won't be used again. However, if a file is a vision file, the WHERE-CONSTRAINT is not used, and might be used for the next file. For example: At site 1, files AA and AB are both SQL Server tables. At site 2, file AA is a vision file, and AB is a SQL Server table. If the COBOL program sets a WHERE-CONSTRAINT that would work with file AA, then at site 2, that WHERE-CONSTRAINT will instead be used for file AB, which will fail.
Parent topic: Database Connectors Tasks


Database Connectors Reference

  • Configuration Variables
    Use the following variables to configure Database Connectors.
  • Error Messages
  • Stored Procedures
    The following stored procedures are available for use with Database Connectors.
  • XFD Directives
    The following are XFD directives for Database Connectors.
  • Compiler Directives Affecting XFD Creation
    The following are compiler directives that directly affect XFD creation.


Configuration Variables

  • 4GL_2000_CUTOFF configuration variable
  • 4GL_8_DIGIT_CUTOFF configuration variable
  • 4GL_COLUMN_CASE configuration variable
  • 4GL_COMMIT_COUNT configuration variable
  • 4GL_CONVERT_DATE_ZERO configuration variable
  • 4GL_DB_MAP configuration variable
  • 4GL_EXTRA_DB_COLS_OK configuration variable
  • 4GL_FULL_DATA_TEST configuration variable
  • 4GL_IGNORED_SUFFIX_LIST configuration variable
  • 4GL_ILLEGAL_DATA configuration variable
  • 4GL_JULIAN_BASE_DATE configuration variable
  • 4GL_USEDIR_LEVEL configuration variable
  • 4GL_WHERE_CONSTRAINT configuration variable
  • A_DB2_ALTERNATE_COMMIT_LOGIC configuration variable
  • A_DB2_CATALOG configuration variable
  • A_DB2_DATASOURCE configuration variable
  • A_DB2_ERROR_MAP_FILE configuration variable
  • A_DB2_ISOLATION_LEVEL configuration variable
  • A_DB2_LOCK_METHOD configuration variable
  • A_DB2_LOGIN configuration variable
  • A_DB2_PASSWD configuration variable
  • A_DB2_STRICT_EQUAL configuration variable
  • A_DB2_TABLE_TYPES configuration variable
  • A_DB2_USE_CATALOG configuration variable
  • A_DB2_USE_CHAR_FOR_BINARY configuration variable
  • A_DB2_USE_SQLCOLUMNS configuration variable
  • A_DB2_USE_SQLTABLES configuration variable
  • A_MSSQL_ADD_IDENTITY configuration variable
  • A_MSSQL_ADD_TIMESTAMP configuration variable
  • A_MSSQL_APPROLE_NAME configuration variable
  • A_MSSQL_APPROLE_PASSWD configuration variable
  • A_MSSQL_CURSOR_OPTION_1,2,3 configuration variables
  • A_MSSQL_DATABASE configuration variable
  • A_MSSQL_DEADLOCK_LOOPS configuration variable
  • A_MSSQL_DEFAULT_CONNECTION configuration variable
  • A_MSSQL_DEFAULT_OWNER configuration variable
  • A_MSSQL_FAST_ACCESS configuration variable
  • A_MSSQL_LIMIT_DROPDOWN configuration variable
  • A_MSSQL_LOCK_DB configuration variable
  • A_MSSQL_LOGIN configuration variable
  • A_MSSQL_MAX_CHARACTERS configuration variable
  • A_MSSQL_MAX_COLUMNS configuration variable
  • A_MSSQL_NO_23_ON_START configuration variable
  • A_MSSQL_NO_CACHED_READ configuration variable
  • A_MSSQL_NO_COUNT_CHECK configuration variable
  • A_MSSQL_NO_DBID configuration variable
  • A_MSSQL_NO_RECORD_LOCKS configuration variable
  • A_MSSQL_NO_TABLE_LOCKS configuration variable
  • A_MSSQL_NT_AUTHENTICATION configuration variable
  • A_MSSQL_PACKETSIZE configuration variable
  • A_MSSQL_PASSWD configuration variable
  • A_MSSQL_ROWCOUNT configuration variable
  • A_MSSQL_SELECT_KEY_ONLY configuration variable
  • A_MSSQL_SEPARATE_LOCK_CONN configuration variable
  • A_MSSQL_SKIP_ALTERNATE_KEYS configuration variable
  • A_MSSQL_TRANSLATE_TO_ANSI configuration variable
  • A_MSSQL_UNLOCK_ON_EXECUTE configuration variable
  • A_MSSQL_USE_DROPDOWN_QUERIES configuration variable
  • A_ORA_DATABASE configuration variable
  • A_ORA_HINTS configuration variable
  • A_ORA_MAX_FILE_CURSORS configuration variable
  • A_ORA_NLS_SORT configuration variable
  • A_ORA_WAIT_LOCK configuration variable
  • A_ORACLE_ERROR_FILE configuration variable
  • COMMIT_COUNT configuration variable
  • FILE_TRACE configuration variable
  • FILE TRACE FLUSH
  • ORA_LOGIN configuration variable
  • ORA_PASSWD configuration variable
  • USER_PATH (for Oracle) configuration variable
  • USER_PATH (for DB2) configuration variable
  • XFD_DIRECTORY configuration variable
  • XFD_MAP configuration variable
  • XFD_MAP_RESETS configuration variable
  • XFD_PREFIX configuration variable
Parent topic: Database Connectors Reference


4GL_2000_CUTOFF configuration variable

This variable implements "date windowing" for Database Connectors. It determines the two-digit years considered to be in the twentieth century and the two-digit years considered to be in the twenty-first century.

This variable accepts a two-digit value. Two-digit years that are smaller than this value are considered part of the twenty-first century ("2000" is added to these dates).

Two-digit years that are equal to or larger than this value are considered to be in the twentieth century ("1900" is added to them).

The default value for 4GL_2000_CUTOFF is 20.

Parent topic: Configuration Variables
Related information
4GL_8_DIGIT_CUTOFF configuration variable


4GL_8_DIGIT_CUTOFF configuration variable

This variable determines whether to use the 4GL_2000_CUTOFF variable for dates that do not use a format string and that are 8 digits. Setting this variable to 1 causes Database Connectors to apply the 4GL_2000_CUTOFF logic for 8-character-long dates. The default value is 0 (off, false, no). This variable can also take values of 1 (on, true, yes).

Parent topic: Configuration Variables
Related information
4GL_2000_CUTOFF configuration variable


4GL_COLUMN_CASE configuration variable

This configuration variable causes Database Connectors to leave the case of the field names found in the XFD unchanged. Normally, all field names are converted to lower case by the Connector (except for Oracle, which converts all values to upper case), and all hyphens are converted to underscores.

Valid values are:

UNCHANGED | All field names are used unchanged when the database table is created. Be aware that hyphens are not converted to underscores in this situation. Most databases do not accept hyphens in column names, so the XFD must be modified by hand to replace hyphens with underscores
LOWER (default) | Field names are converted to lower case and hyphens are converted to underscores
Parent topic: Configuration Variables


4GL_COMMIT_COUNT configuration variable

The 4GL_COMMIT_COUNT variable sets the conditions in which Database Connectors issues an automatic COMMIT WORK operation. Valid values are:

0 (Default) | 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
n | When set 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
-1 | No commit is issued by Database Connectors

When 4GL_COMMIT_COUNT is set to -1, the alternatives are:

  • Call your database query tool with COMMIT WORK or ROLLBACK WORK

A COMMIT WORK is, however, issued on exit from the run time system (for example, on execution of a STOP RUN).

Examples

4GL_COMMIT_COUNT 0

A commit is issued when no locks are held, either because all files that had locked records are closed, or because a COBOL COMMIT verb has been issued. This is the default value.

Note that some data sources lose the current row when a COMMIT or ROLLBACK is executed. For these data sources, setting 4GL_COMMIT_COUNT to 0 can slow performance.

4GL_COMMIT_COUNT n

A commit is issued after n operations. WRITE, REWRITE, and DELETE count towards n; READ, START, and READ NEXT do not.

4GL_COMMIT_COUNT  -1

No commit is issued by Database Connectors. When 4GL_COMMIT_COUNT is set to -1, the only way to perform a commit or rollback is to call your database query tool with COMMIT WORK or ROLLBACK WORK.

Note:
  • 4GL_COMMIT_COUNT can be set in the ACUFH configuration file only
  • 4GL_COMMIT_COUNT is available only in the Database Connectors for DB2 interface
Parent topic: Configuration Variables


4GL_CONVERT_DATE_ZERO configuration variable

If your data uses dates as part of database keys, some database dates cannot be written as NULL or illegal values. To write data that includes NULL or illegal values, the Connector converts the NULL or illegal date values used in keys to a value of 01/01/01 when writing data to some databases. Unfortunately, the code would not make a similar conversion when reading the same record, and those records written with 01/01/01 were not found. This could easily result in writing a record that cannot be read or deleted. When 4GL_CONVERT_DATE_ZERO is set to the default setting CONVERTED, the Connector locates and returns those records written using the date 01/01/01. This functionality can be disabled by setting the variable to UNCHANGED.

Parent topic: Configuration Variables


4GL_DB_MAP configuration variable

This configuration variable lets you set one map at a time, by using FILE-PREFIX as a list of directories, each of which maps to a different database. Note that in this case "database" refers to "collection of tables" rather than distinguishing between, say, DB2 and Microsoft SQL Server.

The syntax of the value of 4GL_DB_MAP is:

directory = database

where directory is matched against the file specification, and database is used when a directory matches. For example, if you have two databases, data01 and data02, and have COBOL programs written that reference data files in /home/dataA and /home/dataB, you can set 4GL_DB_MAP as follows:

4GL-DB-MAP /home/dataA = data01
4GL-DB-MAP /home/dataB = data02

When the run time attempts to open a file with a directory specification of /home/dataA, the Connector uses the table in database data01.

By setting this variable to blank, all maps are reset to nothing, so no mapping is performed.

Currently, this feature is available with the Database Connectors for Microsoft SQL Server interface. Any special instructions are included in the section describing support.

The 4GL_USEDIR_LEVEL variable determines how many levels of directory information to use as part of a table name. 4GL_DB_MAP interacts with 4GL_USEDIR_LEVEL, in the following way:

  • 4GL_DB_MAP is tested first, to determine which database to search for the table
  • Then 4GL_USEDIR_LEVEL is used to change the final name of the table
Parent topic: Configuration Variables
Related information
4GL_USEDIR_LEVEL configuration variable


4GL_EXTRA_DB_COLS_OK configuration variable

4GL_EXTRA_DB_COLS_OK allows the database table to have more columns than the COBOL program's corresponding file descriptor.

If the 4GL_EXTRA_DB_COLS_OK variable is set to True (on, yes), its default value, the number of columns in the database table do not have to match up with the number of fields in the COBOL program that is accessing the table. The database table can have more columns than the COBOL program references; however, the COBOL program may not have more fields than the database table.

Ensure that these extra columns are set correctly when new rows are added to the table. This is database-dependent. For example, under some databases, the columns should either have a default value or should allow NULL. Ensuring that the column value is set is important because the SQL generated by the Database Connectors interface does not reference the extra columns when inserting rows. This ACUFH configuration file can also take values of False (off, no).

Note: Only Database Connectors for Microsoft SQL Server supports this variable.
Parent topic: Configuration Variables


4GL_FULL_DATA_TEST configuration variable

The ACUFH configuration file variable 4GL_FULL_DATA_TEST lets you specify whether an entire field is tested for illegal data or if only the first byte is tested. The default value is False (0, off, no), meaning that only the first byte of data is tested. However, if set to True (1, on, yes), the entire field is tested for illegal data.

Parent topic: Configuration Variables


4GL_IGNORED_SUFFIX_LIST configuration variable

This variable is a space-delimited list of case-independent suffixes to ignore when opening Microsoft SQL Server tables or DB2 tables.

The standard syntax for MSSQL tables is

server.database.owner.table

with a dot (".") as the separator. This syntax causes the filename idx1.dat to be interpreted as "the table dat owned by the user idx1." By setting this variable (which must be set in the ACUFH configuration file, and cannot be set via SET ENVIRONMENT), the Connector ignores the listed suffixes.

There can be up to 10 ignored suffixes (more than that are not kept internally, and are ignored.) The default value for this variable is dat.

Example

4GL_IGNORED_SUFFIX_LIST dat idx txt
Parent topic: Configuration Variables


4GL_ILLEGAL_DATA configuration variable

4GL_ILLEGAL_DATA determines how COBOL data considered illegal by the database is converted before being stored in the database.

The default value is NULL. This causes all illegal data (except key fields) to be converted to null before it is stored.

The value CONVERTED causes the following conversions to occur:

Illegal LOW-VALUES | Minimum possible value (0 or 99999...) Minimum possible date value (01/01/01)
Illegal HIGH-VALUES | Maximum possible value (99999...) Maximum possible date value (database dependent)
Illegal SPACES | Zero (or 01/01/01 for a date field)

Illegal data in key fields is always converted, regardless of the value of this variable.

Parent topic: Configuration Variables


4GL_JULIAN_BASE_DATE configuration variable

This variable sets the base date to be used for Julian date calculations. It is used in conjunction with the DATE XFD directive.

To define your own base date for Julian dates, set this variable to a date having the format YYYYMMDD.

The default value is set internally at 01/01/4713 BC. (Dates other than the default must be greater than 01/02/4713 BC.)

For example, suppose your COBOL program uses dates in the form of the number of days since 12/31/1899. If you want to store these dates in a database accessed by Database Connectors, you can set your DATE XFD directives to be $XFD DATE=JJJJJJ (make sure that the number of characters matches your date field). Then you should set the configuration variable 4GL_JULIAN_BASE_DATE to 18991231.

If you are using the run time intrinsic functions that work with Julian dates, set 4GL_JULIAN_BASE_DATE to December 31, 1600 using the Gregorian calendar.

Parent topic: Configuration Variables
Related information
DATE directive


4GL_USEDIR_LEVEL configuration variable

This variable provides a method of mapping filenames (with directory information) to table names. 4GL_USEDIR_LEVEL lets you use files of the same name, which would normally go into separate directories, as separate names in a single database. This variable determines how many levels of directory information to use as part of the table name.

  • If set to the default value of 0, all directory information is removed from the filename before it is used as a table name
  • When set to negative values, nothing is done differently
  • When set to a positive value, the number determines how much directory information to keep

For example, given the file /home/data1/gl/master.dat, the following values result in the given table name:

4GL-USEDIR-LEVEL | Final resolved name
0 | master.dat
1 | glmaster.dat
2 | data1glmaster.dat
3 or larger | homedata1glmaster.dat

This variable has an upper limit of 127 characters and is currently available with the Database Connectors for Microsoft SQL Server interface. Any special instructions are included in the section describing support.

The 4GL_DB_MAP configuration variable enables you to set one map at a time. 4GL_USEDIR_LEVEL interacts with 4GL_DB_MAP as follows.

  • 4GL_DB_MAP is tested first, to determine which database to search for the table
  • Then 4GL_USEDIR_LEVEL is used to change the final name of the table
Parent topic: Configuration Variables
Related information
4GL_DB_MAP configuration variable


4GL_WHERE_CONSTRAINT configuration variable

4GL_WHERE_CONSTRAINT lets you override any value given in the external variable A4GL-WHERE-CONSTRAINT. This ACUFH configuration file variable can have a value with any length (limited only by machine memory). If a value is set in 4GL_WHERE_CONSTRAINT, any value in the external variable is ignored. For information on the external variable, see The WHERE Constraint.

Parent topic: Configuration Variables
Related information
The WHERE Constraint


A_DB2_ALTERNATE_COMMIT_LOGIC configuration variable

The primary purpose of the configuration variable 4GL_COMMIT_COUNT is to provide for applications that must communicate with a transaction-oriented database but have not explicitly coded transactions into their COBOL application.Note that explicitly coded transactions are not supported in this version of Database Connectors.

A_DB2_ALTERNATE_COMMIT_LOGIC determines how the interface responds to the setting of 4GL_COMMIT_COUNT.

Value | Result
0 or FALSE (the default) | The value of 4GL_COMMIT_COUNT is checked only at startup. The interface issues a commit when the criteria set by 4GL_COMMIT_COUNT is met, regardless of the current transaction state
1 | The value of 4GL_COMMIT_COUNT is checked after each WRITE, REWRITE, DELETE, or UNLOCK operation. A commit is issued; however, only if the run time is not currently in a transaction

Example

4GL_COMMIT_COUNT=1
A_DB2_ALTERNATE_COMMIT_LOGIC=0

Each WRITE operation is committed to the database immediately. This prevents an application from being able to rollback a WRITE.

Parent topic: Configuration Variables
Related information
4GL_COMMIT_COUNT configuration variable


A_DB2_CATALOG configuration variable

This variable indicates the catalog name to be used when Database Connectors searches for objects in the database. Note that not all data sources support a catalog. Using A_DB2_CATALOG with other variables, such as USER_PATH and A_DB2_TABLE_TYPES, can speed up the finding of tables in large databases. It can also prevent an error 9/14: More than one table with the same name, thereby enabling access to tables with identical names, but with different catalogs.

For example, If USER_PATH and A_DB2_CATALOG are used, the form of the SQL statement is modified from:

select COL1, ... from TABLENAME ...

to:

SELECT COL1, ... FROM [catalog.][username.]TABLENAME ...

where catalog and username are filled in if provided.

Parent topic: Configuration Variables
Related information
A_DB2_TABLE_TYPES configuration variable
USER_PATH configuration variable


A_DB2_DATASOURCE configuration variable

Set A_DB2_DATASOURCE to the exact name of the host data source. You can set this variable in your COBOL ACUFH configuration file if using only one data source.

Example

A_DB2_DATASOURCE stores

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 "data source name"

There is no default value for this variable.

Parent topic: Configuration Variables


A_DB2_ERROR_MAP_FILE configuration variable

Set A_DB2_ERROR_MAP_FILE to the name of the file that maps database-specific errors to COBOL errors. This variable allows you to map errors using a text file to supplement the default method of providing errors. Create the file using the guidelines described in To create an error map, and then use this variable to indicate the name and location of the file you created.

Example

If the file used for mapping is called db2.err, and this file is located in the directory C:\DB2, you would specify:

A_DB2_ERROR_MAP_FILE  c:\DB2\db2.err

There is no default value for this variable.

Parent topic: Configuration Variables
Related information
To create an error map


A_DB2_ISOLATION_LEVEL configuration variable

Use the A_DB2_ISOLATION_LEVEL variable to set the isolation level. The default ordering of isolation levels is:

  1. SQL_TXN_READ_COMMITTED
  2. SQL_TXN_READ_UNCOMMITTED
  3. SQL_TXN_REPEATABLE_READ
  4. SQL_TXN_SERIALIZABLE

You can change the isolation level by setting this variable to an integer in the ACUFH configuration file. The settings are:

Isolation level | Setting in ACUFH configuration file
SQL_TXN_READ_UNCOMMITTED | 1
SQL_TXN_READ_COMMITTED | 2
SQL_TXN_REPEATABLE_READ | 3
SQL_TXN_SERIALIZABLE | 4

For example, to set the isolation level to SQL_TXN_READ_COMMITTED, add the following entry to the ACUFH configuration file:

A_DB2_ISOLATION_LEVEL 2

If the user-set isolation level is not supported by the driver, the default method of selecting a level is used.

Parent topic: Configuration Variables


A_DB2_LOCK_METHOD configuration variable

Use this configuration variable to specify the locking method that Database Connectors for DB2 should use when accessing your data source. Possible values are:

  • None
  • SETPOS
  • SETSTMTOPTION
  • UPDATECOLUMN

The default is UPDATECOLUMN.

Example:

A_DB2_LOCK_METHOD UPDATECOLUMN

SETPOS

Specify SETPOS as your lock method to tell the Connector to perform the following locking sequence:

  • When setting up a statement handle for accessing the DB2 data source, Database Connectors calls a function called SQLSetScrollOptions, with values (SQL-CONCUR-LOCK, SQL-SCROLL-KEYSET-DRIVEN, 1)
  • When fetching rows from the data source, Database Connectors calls SQLExtendedFetch instead of SQLFetch
  • Finally, the Connector calls SQLSetPos, with the values (1, SQL-POSITION, SQL-LOCK-EXCLUSIVE)

If any of these functions do not exist, A_DB2_LOCK_METHOD reverts to None.

Note: Even if these functions do exist, there is no guarantee that any rows lock using this sequence of calls. Contact your database vendor to determine whether or not this sequence is effective for your data source.

SETSTMTOPTION

This option is similar to SETPOS, except a function called SQLSetStmtOption performs the task of SQLSetScrollOptions and has more functionality.

In particular, when using this method of locking, Database Connectors calls SQLSetStmtOption a number of times, with values (SQL-CONCURRENCY, SQL-CONCUR-LOCK), (SQL-CURSOR-TYPES, SQL-CURSOR-KEYSET-DRIVEN), and (SQL_KEYSET-SIZE, 1). Again, if the function does not exist, A_DB2_LOCK_METHOD reverts to None.

UPDATECOLUMN

Specifying UPDATECOLUMN as your lock method performs an entirely different type of locking. Instead of trying to lock a row while reading it, this method creates a new statement handle for the data source. Then, after fetching the data from the data source, it resubmits an SQL query to select the same row (based on the primary key) and adds an UPDATE clause. Last, it fetches the data from the data source. This method has the most overhead, but it is much more likely to succeed in locking records.

Parent topic: Configuration Variables


A_DB2_LOGIN configuration variable

A_DB2_LOGIN indicates the user name under which you want to connect to the database system. Not all data sources require a user login name. Those that do may have case requirements. Check your data source documentation to determine if login is case-sensitive.

Example

To connect to the database with the user name MYNAME, you could specify:

A_DB2_LOGIN  MYNAME

or

A_DB2_LOGIN  myname

in the Database Connectors. There is no default value for this variable. If no login is specified, none is used.

Parent topic: Configuration Variables
Related information
A_DB2_PASSWD configuration variable


A_DB2_PASSWD configuration variable

A_DB2_PASSWD should be set to the password assigned to the database account associated with the user name specified by A_DB2_LOGIN. Not all data sources require a password. Those that do may have case requirements. Check your data source documentation to determine if password is case-sensitive.

Examples

If the account with the user name in A_DB2_LOGIN has the associated password CW021535, you would specify:

A_DB2_PASSWD  CW021535

in the Database Connectors.

For better security, you can accept a password from the user during program execution; set the A_DB2_PASSWD variable based on the response:

ACCEPT RESPONSE NO-ECHO.
SET ENVIRONMENT "A_DB2_PASSWD" TO RESPONSE.
Note: If the user has been set up without a password, this variable need not be set. There is no default value for this variable. If no password is specified, none is used.
Parent topic: Configuration Variables
Related information
A_DB2_LOGIN configuration variable


A_DB2_STRICT_EQUAL configuration variable

When Database Connectors for DB2 executes a START ... KEY EQUAL keyval, the interface generates a SELECT statement to select all rows with a key equal to the key columns in keyval. When fetching the data, Database Connectors eventually detects that there is no more data.

If A_DB2_STRICT_EQUAL is set to True at the time the START is executed, the interface returns the error END OF FILE (error 10) at this point. If A_DB2_STRICT_EQUAL is set to False (default), Database Connectors then generates a new SELECT statement to continue reading records until the end of the file rather than the subset of records represented by keyval, and these records may be discarded by the program. (Note that this is program-dependent behavior.)

Setting A_DB2_STRICT_EQUAL to True causes the interface to stop reading records earlier, which can make SELECT statements generated by Database Connectors more efficient.

Note the following conditions:

  • A_DB2_STRICT_EQUAL must be set to True when the START is executed
  • The START must be a KEY EQUAL start
  • There must be no COMMITs between the START and the end of the file. Because DB2 closes all cursors on COMMIT and ROLLBACK (according to the ANSI 92 SQL standard), the interface must regenerate a SELECT the first time it attempts to execute a READ NEXT after a COMMIT or ROLLBACK. This means that 4GL_COMMIT_COUNT should be set to -1, so that no COMMITs are issued by Database Connectors
  • A_DB2_STRICT_EQUAL is most useful with alternate keys that allow duplicates

While the default value is False (off, no, 0), this variable can also take the value True (on, yes, 1).

Note: This same behavior can be accomplished with the 4GL_WHERE_CONSTRAINT, but using A_DB2_STRICT_EQUAL requires less programming.
Parent topic: Configuration Variables
Related information
4GL_COMMIT_COUNT configuration variable
4GL_WHERE_CONSTRAINT configuration variable


A_DB2_TABLE_TYPES configuration variable

This variable is used to specify a table type (such as TABLE or VIEW) to search for when selecting a database table. Using A_DB2_TABLE_TYPES with other variables, such as USER_PATH, can speed up finding tables in large databases.

This information is passed to the API function call SQLTables() as the TableType parameter. Please refer to your driver’s documentation for supported types.

Example

A_DB2_TABLE_TYPES  TABLE,VIEW
Parent topic: Configuration Variables
Related information
USER_PATH configuration variable
A_DB2_CATALOG configuration variable


A_DB2_USE_CATALOG configuration variable

Not all data sources support a catalog, or the data sources may return a catalog name that is not useful. The default behavior of the run time is to not use the catalog in the actual SQL queries. This configuration variable lets you modify this behavior.

The default value is FALSE, which causes the catalog portion of the table name to be treated as blank in SQL queries. Setting this variable to TRUE uses the catalog value returned by SQLTables ODBC function subsequent SQL queries.

Parent topic: Configuration Variables


A_DB2_USE_CHAR_FOR_BINARY configuration variable

Some data sources have restrictions on the number of binary large objects (BLOBs) that can be placed into a single table. If your data source has such restrictions, specify A_DB2_USE_CHAR_FOR_BINARY in the ACUFH configuration file. Possible values for this variable are:

0 | use BINARY type
1 | use CHAR type

A nonzero value for A_DB2_USE_CHAR_FOR_BINARY lets you store data that uses the BINARY XFD directive as hexadecimal encoded CHAR types. This lets you work around your data source restriction. While the default value is 0 (off, false, no), this variable can also take values of On (true, yes).

Because of current limitations of the interface, you must set this variable to 1, or the database returns an error on file creation.

Example

A_DB2_USE_CHAR_FOR_BINARY 1
Parent topic: Configuration Variables


A_DB2_USE_SQLCOLUMNS configuration variable

For some large databases, the API function SQLColumns(), which is called to get a description of the columns in a table, sometimes takes a long time to execute. If you are experiencing such problems, you can use the API function call SQLDescribeCol() instead, which can improve performance for large databases.

To turn on this new functionality, set A_DB2_USE_SQLCOLUMNS to FALSE. The default value for this variable is TRUE.

Parent topic: Configuration Variables


A_DB2_USE_SQLTABLES configuration variable

For some large databases, the API function SQLTables(), which is called to get a list of the tables and information about them, sometimes takes a long time to execute. If you are experiencing such problems, you can instruct the interface to build a test SQL query and use the API function call SQLNumResultTables() to determine if the table exists. This may improve performance for large databases.

To turn on this new functionality, set the A_DB2_USE_SQLTABLES configuration variable to FALSE. The default value of this variable is TRUE, which means the interface uses the SQLTables() API function.

Parent topic: Configuration Variables


A_MSSQL_ADD_IDENTITY configuration variable

When set to the default of On (true, yes), A_MSSQL_ADD_IDENTITY adds an extra column to any table created by Database Connectors for MSSQL. The extra column includes the identity property and is included on all indexes that are not unique. Otherwise, when A_MSSQL_ADD_IDENTITY is set to False (false, no), no extra column is added.

Note: The COBOL FD should not include the identity column.

Example

A_MSSQL_ADD_IDENTITY TRUE

On keys that allow duplicates, this variable has been found to vastly improve performance. Note that the default value is True for a file that allows duplicates and is False for a file with no duplicate keys.

Parent topic: Configuration Variables


A_MSSQL_ADD_TIMESTAMP configuration variable

Using a timestamp column is the only way to absolutely ensure that modifications made to a row are not overwriting someone else's changes. When it's reading a table that is open for I/O,Database Connectors for MssQL uses BROWSE MODE if a timestamp column exists. When Database Connectors is creating a table, if the value of A_MSSQL_ADD_TIMESTAMP is TRUE, a timestamp column is included in the table. (Note that your COBOL FD should not include the timestamp column.) While the default value is Off (false, no), this variable can also take values of On (true, yes).

Note: Microsoft discourages the use of BROWSE MODE on Select statements because of performance reasons. Therefore, unless it is absolutely necessary, do not set this variable to On.

Example

A_MSSQL_ADD_TIMESTAMP  1
Note: If you do not use this option, be prepared for REWRITE statements to fail with an error stating that someone else has modified the row. Because of the AcuLocks1 tables, this can happen only from non-COBOL applications. For information about AcuLocks1 tables, see Table Locking.
Parent topic: Configuration Variables
Related information
Table Locking


A_MSSQL_APPROLE_NAME configuration variable

This variable, in conjunction with A_MSSQL_APPROLE_PASSWD, allows Database Connectors to use approles. Before connecting to a database, set A_MSSQL_APPROLE_NAME to the name of a role. Note that you must also set A_MSSQL_APPROLE_PASSWD to a password for that role. By using these two variables, you set the run time to use this approle.

For more information on approles, please see the Microsoft SQL Server documentation.

Parent topic: Configuration Variables
Related information
A_MSSQL_APPROLE_PASSWD configuration variable


A_MSSQL_APPROLE_PASSWD configuration variable

This variable, in conjunction with A_MSSQL_APPROLE_NAME, enables use of approles. Before connecting to a database, set A_MSSQL_APPROLE_NAME to the name of a role. Then set A_MSSQL_APPROLE_PASSWD to a password for that role. By using these two variables, you set the run time to use this approle.

For more information on approles, please see the Microsoft SQL Server documentation.

Parent topic: Configuration Variables
Related information
A_MSSQL_APPROLE_NAME configuration variable


A_MSSQL_CURSOR_OPTION_1,2,3 configuration variables

A_MSSQL_CURSOR_OPTION_1
A_MSSQL_CURSOR_OPTION_2
A_MSSQL_CURSOR_OPTION_3

These ACUFH configuration file variables allow you to fine-tune the declaration of cursors. In general, cursors are declared with the following syntax:

DECLARE cursor_name option_1 CURSOR option_2 FOR
  <select....> option_3

In other words, different phrases can go in each of the option_X places. Also, different versions of Microsoft SQL Server allow different options in each of those places. Because of this, Database Connectors allows customization of the cursor declaration via these three variables. The values of these variables are placed verbatim into the declare phrase when building a cursor. Note that any errors in the values of these variables may cause Database Connectors to be inoperable. Be sure to read the Microsoft SQL Server documentation to determine what phrases are allowed in each case.

The default values are as follows (the limit is 65 characters for each option):

OPTION_1: | "SCROLL"
OPTION_2: | blank
OPTION_3: | "For read only"
Note: This variable was added because of an issue in SQL Server 6.5 and SQL Server 7.0 that would return incorrect results if this phrase was not used.
Parent topic: Configuration Variables


A_MSSQL_DATABASE configuration variable

A_MSSQL_DATABASE specifies the name of the particular database to be accessed. You cannot open any database files until you have set this variable.

Example

A_MSSQL_DATABASE stores

indicates the "stores" database is to be accessed.

Parent topic: Configuration Variables


A_MSSQL_DEADLOCK_LOOPS configuration variable

Use A_MSSQL_DEADLOCK_LOOPS if user typically open a number of tables at the same time. This configuration variable can be used to instruct Database Connectors to re-execute an INSERT statement that could not execute because a row in the AcuOpen1Tables table was locked, or to return an error if the user chooses not to run the query again.

Valid values are:

0 (the default) | Returns an error 1205 indicating that a table is locked
A positive numeric value. | Re-executes by the number specified by the query that caused the deadlock. Note that it can be as long as 10 seconds until SQL Server detects the deadlock, and the application appears to stop responding while the repeated attempts to re-execute the query are in progress.
-1 or MESSAGE Displays a message box containing the text of the SQL Server error message and the option to rerun the query.
SQL Server has returned an error
(text of message from SQL Server)
Do you want to retry the operation?

If the user answers Yes, the interface reruns the query; if the user answers No, the interface returns an error.

Setting A_MSSQL_DEADLOCK_LOOPS to -1 or MESSAGE is the preferred action; the time it takes to inform users of the problem allows other connections to finish opening the tables, giving the AcuOpenTables1 table time to remove the deadlock. For information about AcuOpenTables1 table, see Table Locking.

Parent topic: Configuration Variables
Related information
Table Locking


A_MSSQL_DEFAULT_CONNECTION configuration variable

A_MSSQL_DEFAULT_CONNECTION specifies the name of the server to which the run time system connects. If A_MSSQL_DEFAULT_CONNECTION is not set, a server name of localhost is used. To reference tables on a server other than the one specified by this variable, open the file using the following syntax:servername.database.owner.table.

Example

Suppose you have two servers, one named TOM and one named HARRY. If most of the tables you want to access are on the server HARRY, you should set:

A_MSSQL_DEFAULT_CONNECTION  HARRY

For those occasions when you want to access the TOM server, you could open the file this way:

TOM.stores.johndoe.purch1
Parent topic: Configuration Variables


A_MSSQL_DEFAULT_OWNER configuration variable

In Microsoft SQL Server, tables are named database.user.tablename. The value of A_MSSQL_DEFAULT_OWNER specifies the name of the user. If this variable is set, it must match the owner of a table. Tables are referenced as owner.table, where owner is the value of A_MSSQL_DEFAULT_OWNER. If this variable is set to a value for which owner.table does not exist, the interface fails to access the table.

Note that newly created tables are owned by the users who create them, not the owner named in A_MSSQL_DEFAULT_OWNER. The default value of this variable is blank, causing the interface to call an internal stored procedure to determine the actual owner of the table. Note also that we discourage multiple tables with the same tablename in a single database.

Parent topic: Configuration Variables


A_MSSQL_FAST_ACCESS configuration variable

A_MSSQL_FAST_ACCESS is set from your COBOL program. Files opened while this variable is set to a nonzero value are optimized for forward sequential access.

This option improves the READ NEXT performance in some cases. For example, benchmark testing yielded the following results for the READ AND SKIP operation:

No FAST_ACCESS: | 72.76 seconds
FAST_ACCESS, ROWCOUNT 0: | 148.88 seconds
FAST_ACCESS, ROWCOUNT 10: | 8.09 seconds

(10 is the perfect value for ROWCOUNT in this benchmark, because the program does a START, 10 READ NEXT operations, and then does it again.)

For certain reporting programs, this option can dramatically improve performance. However, please note the following restrictions:

  • Files must be open INPUT or open IO with MASS-UPDATE. FAST_ACCESS boosts performance only when no locking is required. In files that allow locking, a record must be reread after being locked; this prevents an uninterrupted forward sequential traversal.
  • Files opened with FAST_ACCESS use a dedicated connection for reading from the file. Since connections are memory-intensive (both on the client, and on the server), the number of files opened with FAST_ACCESS should be kept to a minimum. In the event that opening a connection fails, the file open continues, but FAST_ACCESS mode is disabled, with the following message appearing in the trace file:
    FAST_ACCESS mode not available.

    Also, a new connection technically uses a new concurrent Microsoft SQL Server license from Microsoft.

  • Files opened with FAST_ACCESS cannot be read backwards. In other words, READ PREVIOUS does not work with FAST_ACCESS files. If you try to READ PREVIOUS on a file opened with FAST_ACCESS, an error 9/83 with a secondary code of 20 occurs.
  • The ANSI standard states that READ NEXT after a READ returns the next record. Some applications depend on this, and some applications just want to read dynamically from a file, and don't use the positioning facility. Because of this ambiguity, files that are opened with FAST_ACCESS cannot be READ dynamically. If you try to READ on a file opened with FAST_ACCESS, an error 9/83 with a secondary code of 20 occurs.

There are no restrictions on WRITE, REWRITE, and DELETE. However, these operations use the cursor-based connection, not the dedicated connection. For this reason, transactions may stop responding.

While the default value is 0 (off, false, no), this variable can also take values of On (true, yes).

Parent topic: Configuration Variables


A_MSSQL_LIMIT_DROPDOWN configuration variable

This configuration option is closely related to A_MSSQL_USE_DROPDOWN_QUERIES.

When a sequence of START and READ NEXT/PREVIOUS operations are performed by an application, Database Connectors/MSSQL generates a sequence of queries to return the set of records matching the application's request. To improve performance, the interface generates a sequence of "drop down" queries based upon the key of reference's key segments going from the most specific subset using the most number of segments to the most general using the least number of segments. This functionality is turned on by setting the configuration variable A_MSSQL_USE_DROPDOWN_QUERIES to TRUE.

For example if a key is described by:

    03 MY-ALTKEY.
       05 MY-ALTKEY-SEG1        PIC X(2).
       05 MY-ALTKEY-SEG2        PIC X(2).
       05 MY-ALTKEY-SEG3        PIC X(2).

Then a START followed by a sequence of READ NEXT operations might generate the selection criteria of:

WHERE MY-ALTKEY-SEG1 = :w0 AND MY-ALTKEY-SEG2 = :w1 AND
MY-ALTKEY-SEG3 >= :w3
WHERE MY-ALTKEY-SEG1 = :w0 AND MY-ALTKEY-SEG2 > :w1
WHERE MY-ALTKEY-SEG1 > :w0

This can improve performance because the target for each query is kept to a minimal size. If a set of records is not required, the database does not need to spend the time building the working set. When a "DROP DOWN" does occur however, the subsequent working set can require a large amount of time to process because of the potential magnitude of records. Normally there is not a way for a COBOL application to instruct the interface to stop processing when it has finished with the records based on a given key segment.

To address this, use A_MSSQL_LIMIT_DROPDOWN configuration variable.

This variable allows an application to direct the interface not to perform "drop down" query generation and instead return "end of file" when the records matching the current query have been exhausted.

This variable may be set to one of the following options:

OFF | This is the current default. The interface performs "drop down" queries.
PARTIAL | If the record positioning was performed by a START with a SIZE clause such that the initial positioning was performed using fewer than the total number of columns in the key, the process stops after all records matching the START columns have been exhausted.
FULL | If the record positioning was performed by a START without a SIZE clause, the process stops after all records matching the START columns have been exhausted.
ALL | Regardless of what form of START was used for the initial positioning, the process stops after all records matching the START columns have been exhausted.

This variable is performed at the time of each start, so the application may change its value.

Parent topic: Configuration Variables
Related information
A MSSQL USE DROPDOWN QUERIES configuration variable


A_MSSQL_LOCK_DB configuration variable

A_MSSQL_LOCK_DB specifies the name of the database that holds the lock table. For information about AcuLocks1 tables, see Table Locking.

Parent topic: Configuration Variables
Related information
Table Locking


A_MSSQL_LOGIN configuration variable

A_MSSQL_LOGIN indicates the user name under which you want to connect to the database system.

Example

To connect to the database with the user name MYNAME, specify A_MSSQL_LOGIN MYNAME in the ACUFH configuration file.

See A_MSSQL_NT_AUTHENTICATION configuration variable for an alternate method to log onto the server. See Setting Up a User Account for more information.

Parent topic: Configuration Variables
Related information
A_MSSQL_PASSWD configuration variable
A MSSQL NT AUTHENTICATION configuration variable
Setting Up a User Account


A_MSSQL_MAX_CHARACTERS configuration variable

A_MSSQL_MAX_CHARACTERS indicates the maximum number of bytes the Database Connectors allows in a table row.

Microsoft SQL Server places a limit on the number of bytes per table row. Database Connectors adheres to this limit, but sometimes it cannot accurately count how many bytes a particular row contains (because of overhead bytes that Microsoft SQL Server adds). This variable enables the developer to set Database Connectors upper bound.

You might want to try reducing this value if a table cannot be created. By reducing the upper bound, you may be able to prevent this problem.

If Microsoft SQL Server increases the maximum number of bytes allowed in a row (in a future release of the product), you can increase the value of this variable to take advantage of the new limit.

The A_MSSQL_MAX_CHARACTERS variable has a default value of 8060.

Parent topic: Configuration Variables


A_MSSQL_MAX_COLUMNS configuration variable

A_MSSQL_MAX_COLUMNS indicates the maximum number of columns Database Connectors allows in a table. The default value is 1024.

Microsoft SQL Server places a limit on the number of columns per table. Database Connectors adheres to this limit, but sometimes it cannot accurately count how many columns a table contains (because a column has been added to a table without Database Connectors knowledge). This variable enables the developer to set Database Connectors upper bound. You might want to try reducing the upper bound if you discover that a table cannot be created. By reducing the upper bound, you allow for uncountable columns and thus may be able to prevent this problem.

If Microsoft SQL Server increases the maximum number of columns allowed per table (in a future release of the product), you could increase the value of this variable to take advantage of the new limit. Consult your Microsoft SQL Server documentation to decide the new maximum number of columns allowed per table.

Parent topic: Configuration Variables


A_MSSQL_NO_23_ON_START configuration variable

When A_MSSQL_NO_23_ON_START is set to TRUE, START does not detect whether records matching the search criteria actually exist. Because it does not detect the existence of records, it is possible, when using this variable, to do a START without error, and for the next READ NEXT to return END_OF_FILE, contrary to the ANSI standard. The default value is FALSE.

Example

A_MSSQL_NO_23_ON_START TRUE
Parent topic: Configuration Variables


A_MSSQL_NO_CACHED_READ configuration variable

By default, the Database Connectors for MSSQL run time applies the same READ logic on a previously-read record as is used for the Vision indexed file system. Instead of sending another request to MSSQL to read the same record, the run time gets the record from cache. This behavior is referred to as Cached Read and can improve performance.

A_MSSQL_NO_CACHED_READ controls this behavior. By default cached read is enabled. It can be disabled by setting A_MSSQL_NO_CACHED_READ to true or 1.

If the last operation was a successful READ with LOCK and the next operation is a READ with LOCK on the same key with the same key value then the cached record returneds without accessing the database. A trace file entry is made that says cached read.

Parent topic: Configuration Variables


A_MSSQL_NO_COUNT_CHECK configuration variable

When performing a REWRITE, the interface checks to see that a record was actually updated. If not, it returns an error 23. Setting this variable to ON disables that check. This improve REWRITE performance, at the risk of missing an error. While the default value is Off (false, no), this variable can also take the value of On (true, yes).

Parent topic: Configuration Variables


A_MSSQL_NO_DBID configuration variable

The interface stores the Database ID in the AcuLocks1 and AcuOpenTables1 tables, to distinguish different tables in different databases. Sometimes this causes problems. Setting this variable to On causes the interface to use a Database ID of 0, instead of the actual ID of the database. While the default value is Off (false, no), this variable can also take the value of On (true, yes).

For information about AcuLocks1 and AcuOpenTables1, see Table Locking.

Parent topic: Configuration Variables
Related information
Table Locking


A_MSSQL_NO_RECORD_LOCKS configuration variable

Setting this variable ON causes all READS to be treated as READ NO LOCK, which can improve performance (but has the obvious consequences). While the default value is Off (false, no), this variable can also take the value of On (true, yes).

Parent topic: Configuration Variables


A_MSSQL_NO_TABLE_LOCKS configuration variable

Setting this variable to On means the interface does not use the AcuOpenTables1 table, which causes all table locking to be disabled. This can improve performance on OPEN and CLOSE statements. While the default value is Off (false, no), this variable can also take the value of On (true, yes).

For information about AcuOpenTables1, see Table Locking.

Parent topic: Configuration Variables
Related information
Table Locking


A_MSSQL_NT_AUTHENTICATION configuration variable

The A_MSSQL_NT_AUTHENTICATION configuration variable indicates whether Microsoft SQL Server authenticates users based on their Windows login.

If this variable is set to True (on, yes), Database Connectors for MSSQL interface attempts to log users on using the Microsoft SQL Server Windows NT authentication mode. (See your SQL Server documentation for information about this authentication mode.) When A_MSSQL_NT_AUTHENTICATION is enabled, the A_MSSQL_LOGIN and A_MSSQL_PASSWD configuration variables are no longer needed or used. (They are still available if you are not using Windows NT authentication mode.) If you have not set up Microsoft SQL Server itself to allow this type of authentication, setting this variable to TRUE causes all login attempts to fail. See your Microsoft SQL Server documentation for information on how to set up this type of authentication.

The default is False (off, no), indicating that Database Connectors does not use Windows NT authentication mode when logging users on and continues to use login and password authentication.

Parent topic: Configuration Variables
Related information
A_MSSQL_LOGIN configuration variable
A_MSSQL_PASSWD configuration variable


A_MSSQL_PACKETSIZE configuration variable

The A_MSSQL_PACKETSIZE configuration variable sets the size of network packets. Setting this variable can affect performance, since fewer and larger network calls can improve performance.

This variable must be set in the ACUFH configuration file and has no effect if set in a COBOL program via SET CONFIGURATION or SET ENVIRONMENT. The value of this variable is the largest size that the transport layer uses for network packets (although the underlying library may reduce the size specified; this is out of the control of the interface.) The largest value that can be specified is 32767. The default depends on which version of the client libraries are linked into the run time system, although 512 is the most common default.

Use this variable to tune your database performance. To set the packet size to 8192 use:

A_MSSQL_PACKETSIZE   8192

Setting this variable to 0 or to a negative value causes Database Connectors to use the default value.

Parent topic: Configuration Variables


A_MSSQL_PASSWD configuration variable

The variable A_MSSQL_PASSWD should be set to the password assigned to the database account associated with the user name specified by A_MSSQL_LOGIN.

Examples

If the account with the user name in A_MSSQL_LOGIN has the associated password CW021535, specify:

A_MSSQL_PASSWD  CW021535

in the ACUFH configuration file.

For better security, you can accept a password from the user during program execution; set the A_MSSQL_PASSWD variable based on the response:

ACCEPT RESPONSE NO-ECHO.
SET ENVIRONMENT "A_MSSQL_PASSWD" TO RESPONSE.
Note: If the user has been set up without a password, this variable need not be set.
Parent topic: Configuration Variables
Related information
A_MSSQL_LOGIN configuration variable


A_MSSQL_ROWCOUNT configuration variable

This variable has an effect only if you are reading on a key that does not allow duplicates, or if you have added an Identity column to the table. A_MSSQL_ROWCOUNT determines how many rows are returned by a SELECT statement sent to the server.

This variable can be used to speed up the Connector. For example, if only reading one record at a time, and reading from a unique key, you can set A_MSSQL_ROWCOUNT to 1, thus speeding up the processing.

If you know you are going to be reading records ten rows at a time, set A_MSSQL_ROWCOUNT to 10. If you don't have any information about how many rows are going to be requested, set this variable to 0, which is the default.

Note: Setting this variable to a non-optional value can actually degrade performance, since the interface may be forced to issue more SELECT statements once the rowcount has been determined. Use caution when setting this variable.
Parent topic: Configuration Variables
Related information
A_MSSQL_ADD_IDENTITY configuration variable


A_MSSQL_SELECT_KEY_ONLY configuration variable

This variable directs the interface to select key columns only when searching for records. Its use can improve READ performance on large tables with many rows.

When set to True (on, yes), the default value, A_MSSQL_SELECT_KEY_ONLY causes the interface to select only key columns when searching for records, and then select the entire row of the single record that must be returned to the COBOL program. This improves performance on large tables with many rows.

Setting A_MSSQL_SELECT_KEY_ONLY to False (off, no) does not affect how the select is created for files open I/O (since the record must be locked and then the rest of the data fetched), but causes the interface to select all the columns of the table for files open INPUT.

Parent topic: Configuration Variables


A_MSSQL_SEPARATE_LOCK_CONN configuration variable

When set to TRUE, this variable causes the Database Connectors for MSSQL interface to open two connections to any server it connects to. One of those connections performs all the normal COBOL data work (write, rewrite, read next, etc).

The second connection does all the internal work needed by the interface to do its normal work, such as updating the lock tables, testing for locks, etc.

The reason for the configuration variable is that a separate connection to SQL Server requires a separate license from Microsoft.

If you are experiencing frequent deadlocks during record locking or opening of tables, you may be able to solve those by setting this variable to TRUE.

Parent topic: Configuration Variables


A_MSSQL_SKIP_ALTERNATE_KEYS configuration variable

A_MSSQL_SKIP_ALTERNATE_KEYS determines whether alternate keys are used to form indexes during table creation. The default value of this variable is 0, which allows alternate keys. This configuration variable can also take values of 1 (on, true, yes).

If you set this variable to a nonzero value (such as 1), alternate keys are not used to form indexes, which speeds up processing if many writes or rewrites are being performed. (Note that a value of 1 may slow processing if the application is reading sequentially using an alternate key.)

Parent topic: Configuration Variables


A_MSSQL_TRANSLATE_TO_ANSI configuration variable

Setting this variable to True (on, yes) causes Database Connectors to call the same translation function used by the Windows run time to translate characters going to the server into the OEM character set, and to translate characters coming from the server to ANSI.

The default is False (off, no), which indicates that Database Connectors does not call the translation function, but passes the data as is to the library.

Parent topic: Configuration Variables


A_MSSQL_UNLOCK_ON_EXECUTE configuration variable

Setting this variable to True (on, yes) causes all invocations of I$IO using the EXECUTE opcode to unlock all records.The default value is Off (false, no).

Parent topic: Configuration Variables


A_MSSQL_USE_DROPDOWN_QUERIES configuration variable

Setting A_MSSQL_USE_DROPDOWN_QUERIES to TRUE causes selects sent to the database to be of the drop-down variety, instead of a single large query.

For example, if you have a file with three fields in the primary key, (keyseg1, keyseg2, keyseg3), and your COBOL program does a START, the following query is sent to the database:

select (columns) from (table) where
((keyseg1 = value1 and keyseg2 = value2 and
keyseg3 > value3) or (keyseg1 = value1 and
keyseg2 > value2) or (keyseg1 > value1))
order by keyseg1, keyseg2, keyseg3

If you use drop-down queries, the following collection of queries is sent instead:

select (columns) from (table) where (keyseg1
= value1 and keyseg2 = value2 and keyseg3 >
value3) order by keyseg1, keyseg2, keyseg3

followed by:

select (columns) from (table) where (keyseg1
= value1 and keyseg2 > value2) order by
keyseg1, keyseg2, keyseg3

and:

select (columns) from (table) where (keyseg1
> value1) order by keyseg1, keyseg2, keyseg3

There are advantages and disadvantages to each method. If you use the A4GL_WHERE_CONSTRAINT variable, you should probably set A_MSSQL_USE_DROPDOWN_QUERIES to FALSE, because the WHERE constraint limits the result set sufficiently that the larger query is more efficient. For additional information, see The WHERE Constraint.

If you usually START files and read to the end, you should set A_MSSQL_USE_DROPDOWN_QUERIES to FALSE, because a fewer number of queries need to be sent to the database. On the other hand, if you START files and stop reading after some condition, but have not used the WHERE constraint, you may get more efficient access by setting this variable to TRUE and using the drop-down style of query. In either case, run some tests to see which value makes the most sense for your application.

This variable is accessed only during a positioning operation, so you can set it at different times for different tables.

The default value is FALSE.

Example

A_MSSQL_USE_DROPDOWN_QUERIES TRUE
Parent topic: Configuration Variables
Related information
The WHERE Constraint


A_ORA_DATABASE configuration variable

A_ORA_DATABASE specifies the name of a network service that you have set up to refer to a database using an Oracle tool such as "Net Configuration Assistant," or by manually editing tnsnames.ora.

Parent topic: Configuration Variables
Related information
ORA_LOGIN configuration variable
ORA_PASSWD configuration variable


A_ORA_HINTS configuration variable

This variable enables the Oracle Hints feature. The default value is 1 (on, true, yes). To turn off this feature, set it to 0 (off, false, no).

Oracle Hints can improve database performance considerably in some situations, by "strongly suggesting" to the Oracle query optimizer that it use a given index to process a query.

Note that keys must use your COBOL development system's naming convention for naming indexes in the database to use this function. For example, the primary key on the file ftest would be named "iftest_0".

Parent topic: Configuration Variables


A_ORA_MAX_FILE_CURSORS configuration variable

This variable allows you to set the maximum number of cursors that the run time is allowed to have per file.

The default value for A_ORA_MAX_FILE_CURSORS is 0, meaning that the run time system uses all of the cursors that it can. Because each file must have access to at least two cursors, set this variable to a value greater than 2 if you want to specify a particular value. If all the allotted cursors for a file have been used, the least recently used cursor for that file is freed.

Note that this variable is read only at startup time, and should, therefore, be set in the ACUFH configuration file.

Parent topic: Configuration Variables


A_ORA_NLS_SORT configuration variable

This variable forces the sort order of returned records to be in a binary order. Set A_ORA_NLS_SORT to 1 to turn off the default binary order and use the native language sort (NLS) instead. The default is 0, which means that the sort order is not NLS order and will be binary.

This variable must be set before the first database file is accessed.

Parent topic: Configuration Variables


A_ORA_WAIT_LOCK configuration variable

This configuration variable determines the type of Oracle locks used: wait or nowait locks. The default is FALSE, nowait locks. This variable is read at file open time for each file.

Parent topic: Configuration Variables


A_ORACLE_ERROR_FILE configuration variable

This configuration file variable allows you to map errors using a text file to supplement the default method of providing errors. By adding the name of the file that contains the actual error mapping to A_ORACLE_ERROR_FILE, database-specific errors are mapped to COBOL errors.

Note: Some messages related to COBOL I/O cannot be supplemented.

Example

A sample syntax for this variable would be:

A_ORACLE_ERROR_FILE=ORCLerrs

where ORCLerrs is a file of a specified format containing a mapping of database-specific errors to COBOL errors. One such entry might be:

1 DUPLICATE_RECORD
Parent topic: Configuration Variables


COMMIT_COUNT configuration variable

The value of COMMIT_COUNT indicates the conditions under which you want Database Connectors to issue an automatic COMMIT_WORK operation.

COMMIT_COUNT <value>

Valid values are:

0 | A commit is issued when no locks are held, either because all files that had locked records have been closed, or because a COBOL COMMIT verb has been issued. This is the default value
"n" | A commit is issued after n operations. WRITE, REWRITE, and DELETE count towards n; READ, START, and READ NEXT do not
-1

No commit is issued by Database Connectors when COMMIT_COUNT is set to -1; the alternative when this is set:

  • Call your Oracle query tool with COMMIT WORK or ROLLBACK WORK

A COMMIT WORK, however, is issued on exit from the run time system (for example, on execution of a STOP RUN)

For information about supported features, see Supported Features for Oracle Information.

Parent topic: Configuration Variables
Related information
Supported Features


FILE_TRACE configuration variable

This variable allows you to start file tracing without opening the debugger. Set this variable to a non-zero value to save information about all file OPENs, READs, and WRITES in the error file. The default is 0.

Parent topic: Configuration Variables


FILE TRACE FLUSH

Set this variable to 1 (on, true, yes) to flush the error file after every WRITE statement.

Parent topic: Configuration Variables


ORA_LOGIN configuration variable

ORA_LOGIN indicates the user name under which you want to connect to the database system.

Example

To connect to the database with the user name MYNAME, you would specify:

ORA_LOGIN  MYNAME

in the Database Connectors.

For information on user accounts, see Setting up a User Account.

Parent topic: Configuration Variables
Related information
ORA_PASSWD configuration variable
A_ORA_DATABASE configuration variable


ORA_PASSWD configuration variable

ORA_PASSWD should be set to the password assigned to the database account associated with the user name specified by ORA_LOGIN.

Example

If the account with the user name has the associated password CW021535, you would specify:

ORA_PASSWD  CW021535

in the ACUFH configuration file or the environment.

For a full login from a client to a remote server, you could have either of the following two settings:

ORA_LOGIN username
ORA_PASSWD userpass
A_ORA_DATABASE remotesrv

or

ORA_LOGIN username@remotesrv
ORA_PASSWD userpass

In this example, remotesrv is the net service name you have set up, as defined in A_ORA_DATABASE configuration variable.

Parent topic: Configuration Variables
Related information
ORA_LOGIN configuration variable


USER_PATH (for Oracle) configuration variable

USER_PATH indicates the user name or names to be used when Database Connectors searches for files. The order of the names is significant. 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.

The type of OPEN being issued for the file determines the effects of this setting.

Examples

If an OPEN INPUT or OPEN I/O is issued, and USER_PATH is defined in the ACUFH configuration file, Database Connectors searches for a user of the named file in the following sequence of locations:

  1. The list of users in USER_PATH
  2. The public synonyms

The first valid file is opened.

If USER_PATH is defined and the current user is the owner of the file, the current user must be included as one of the users (as indicated by a "."). If this is not the case, even though the current user has created the table, it is not be found and a file error "35" appears. This circumstance can occur if the file was created with the OPEN OUTPUT phrase and "." is not an element in USER_PATH. When the table is created, the current user is the owner of that table. When the run time system attempts to open the table, the system does not look for tables owned by the current user unless USER_PATH is not set, or unless "." is part of the setting.

If an OPEN INPUT or OPEN I/O is issued, and no USER_PATH variable is in the configuration file, Database Connectors searches for a user of the named file in the following sequence of locations:

  1. The user named for login (ORA_LOGIN or OPS$username)
  2. Public synonyms

Database Connectors opens the first file that has a valid combination of user and file name.

If an OPEN OUTPUT is issued (whether USER_PATH is present or not), a new table is created with the owner being:

  • The name specified in ORA_LOGIN
  • The name constructed by automatic login

For information on automatic login, see Setting Up a User Account, and for information on the search path, see Setting Up the Search Path.

Parent topic: Configuration Variables


USER_PATH (for DB2) configuration variable

USER_PATH indicates the user name or names (schemas) to be used when Database Connectors searches for files. The order of the names is significant. The syntax for this variable is:

USER_PATH  user1 [user2]...

where the user argument may be either the name of a user (schema) on the system, or a period (“.”), which indicates the files owned by yourself.

The type of OPEN being issued for the file determines the effects of this setting.

Examples

If an OPEN INPUT or OPEN I/O is issued, and a USER_PATH variable is defined in the ACUFH configuration file, Database Connectors searches for a user of the named file in the list of users in USER_PATH. The first valid file is opened.

If USER_PATH is defined and the current user is the owner of the file, the current user must be included as one of the users, as indicated by a “.” or the setting of your login schema (A_DB2_LOGIN) in the USER_PATH. If this is not the case, even though the current user has created the table, it is not found and a file error 35 occurs. This circumstance can occur if the file is created with the OPEN OUTPUT phrase, and “.” is not an element in USER_PATH. When the table is created, the current user is the owner of that table. When the run time attempts to open the table, it does not look for tables owned by the current user unless USER_PATH is not set, or unless “.” or the user's current schema is part of the USER_PATH setting.

If an OPEN INPUT or OPEN I/O is issued, and no USER_PATH variable is in the ACUFH configuration file, Database Connectors searches for a user of the named file in the user named for login A_DB2_LOGIN). Database Connectors opens the first file that has a valid combination of user and file name.

If an OPEN OUTPUT is issued (whether USER_PATH is present or not), a new table is created with the owner being the name specified in A_DB2_LOGIN.

Using USER_PATH with other variables such as A_DB2_CATALOG can speed up the finding of tables in large databases. It can also prevent an error 14: More than one table with the same name, thereby enabling access to tables with identical names, but with different schemas.

For example, If using both USER_PATH and A_DB2_CATALOG , the form of the SQL statement changes from:

select COL1, ... from TABLENAME ...

to:

SELECT COL1, ... FROM [catalog.][username.]TABLENAME ...

where catalog and username are filled in if provided.

Parent topic: Configuration Variables
Related information
A_DB2_LOGIN configuration variable
A_DB2_CATALOG configuration variable


XFD_DIRECTORY configuration variable

XFD_DIRECTORY tells the run time system the name of the directory that contains the XFD files built by the compiler. The default value is the current directory.

Example

To tell the run time system that the XFDs are stored in the directory /usr/inventory/dictionaries, enter the following:

xfd–directory   /usr/inventory/dictionaries
Note: This variable is ignored if you have specified a search path for XFD files using the XFD_PREFIX configuration variable. XFD_PREFIX extends the functionality of XFD_DIRECTORY.

For information about the directive that tells the compiler where to put the XFDs, see the CREATEXFD directive. Unless you have moved the XFD files, you should use the same value in XFD_PREFIX (or for XFD_DIRECTORY) that you used with the CREATEXFD directive.

Parent topic: Configuration Variables


XFD_MAP configuration variable

XFD_MAP tells the run time system to associate certain filenames with a particular XFD. This enables you to use one XFD for many different files. You can use XFD_MAP to add or replace the existing value, depending on the setting of the XFD_MAPS_RESETS configuration variable.

The XFD_MAP variable has this syntax:

XFD_MAP   [pattern = base-xfd-name]  ...

where pattern consists of any valid filename characters and may include "*" or "?". These two characters have special meanings in the pattern:

* | matches any number of characters
? | matches a single occurrence of any character

For example:

CUST???? | matches CUST0001 and CUSTOMER; does not match CUST001 or CUST00001
CUST* | matches all of the above
CUST*1 | matches CUST001 and CUST0001 and CUST00001; does not match CUSTOMER
*OMER | matches CUSTOMER; does not match CUST001 or CUST0001

Note that XFD_MAP cannot be read from the ACCEPT FROM ENVIRONMENT statement.

Parent topic: Configuration Variables
Related information
XFD_MAP_RESETS configuration variable


XFD_MAP_RESETS configuration variable

This ACUFH configuration file variable determines whether setting the XFD_MAP adds to or replaces the existing value.

Valid values are:

Value | Effect when setting the ACUFH configuration file variable
"0" (off, false, no) | Adds new value patterns to the end of the existing value
"1" (on, true, yes) the default | Replaces the existing value with a new value

This variable may be useful if you need to include multiple XFD_MAP lines in an ACUFH configuration file and want to avoid setting and resetting the variable. When multiple lines exist, all patterns are used in the order they are listed.

Parent topic: Configuration Variables
Related information
XFD_MAP configuration variable


XFD_PREFIX configuration variable

When this ACUFH configuration file variable is assigned a value, it is used to locate the XFD file, and the variable XFD_DIRECTORY is ignored. XFD_PREFIX enables you to define a specific series of directories to search for XFD files, rather than indicating only one. The run time system searches each directory in the order specified until an XFD file matching the name of the desired file is found and then assumes that is the correct file.

Therefore, when using the XFD_PREFIX variable, be aware that the run time system:

  • Searches each named directory and not its subdirectories
  • Stops searching once a matching file name is found, even if other files of the same name are located in a different directory later in the series
  • Does not validate that the file specifications (such as key or record size parameters) of the XFD file match those in the COBOL program. Be sure the correct file you want to use is in the path specified

You can specify a directory path that contains embedded spaces if you surround the path with quotation marks. You separate entries using a semi-colon (;). For example:

XFD_PREFIX C:\"Sales Data";C:\Customers

You can specify up to 4096 characters for this variable.

Note: The default for XFD_PREFIX is empty. If this variable is set to any other value, the XFD_DIRECTORY (in which you specify only one directory) is ignored.
Parent topic: Configuration Variables
Related information
XFD_DIRECTORY configuration variable


Error Messages

All non-standard file status errors returned by Database Connectors are of the form 9/83, with a secondary error that can be determined by examining the external variable f-int-errno, or by calling C$RERR. The secondary errors that can be returned include the following.

  • Run Time - File Status Errors
  • Compiler Errors
Parent topic: Database Connectors Reference


Run Time - File Status Errors

  • 01 Read error on dictionary file
  • 02 Corrupt dictionary file
  • 04 Open error
  • 04 Too many fields in the key
  • 05 (no message associated with this error)
  • 05 Too many fields in the key (more than 16 for Oracle)
  • 11 Stored procedures not found
  • 12 A column of a key is of data type TEXT or IMAGE, which is illegal
  • 12 DB2 library function returned an unexpected error
  • 13 Illegal size or type of data for variable xxx
  • 13 Internal error
  • 14 More than one table with the same name
  • 16 Trying to rename a table across databases
  • 17 Cache error
  • 18 Primary Key error
  • 09/19 Table Size Error
  • 20 (no message associated with this error)
  • 21 (no message associated with this error)
  • 1001 Invalid Cursor
  • 22007 Invalid Character value for Cast Specification
Parent topic: Error Messages


01 Read error on dictionary file

An error occurred while reading the XFD file; this probably means the XFD is corrupt.

Resolution:

Recompile with the CREATEXFD directive to re-create the XFD.

Parent topic: Run Time - File Status Errors
Related information
CREATEXFD directive


02 Corrupt dictionary file

The XFD file for one of your COBOL files is corrupt and cannot be read.

Resolution:

Recompile with the CREATEXFD directive to re-create the XFD.

Parent topic: Run Time - File Status Errors
Related information
CREATEXFD directive


04 Open error

There was an error attempting to open the XFD file.

Resolution:

Recompile with the CREATEXFD directive to re-create the XFD.

Parent topic: Run Time - File Status Errors
Related information
CREATEXFD directive


04 Too many fields in the key

There are more than 16 fields in a key.

Resolution:

Check your key definitions and restructure the key that is illegal, then recompile with the CREATEXFD directive.

Parent topic: Run Time - File Status Errors
Related information
CREATEXFD directive


05 (no message associated with this error)

A date given to the Database Connectors interface is invalid and cannot be converted.

Parent topic: Run Time - File Status Errors


05 Too many fields in the key (more than 16 for Oracle)

There are more than 16 fields in a key.

Resolution:

Check your key definitions and restructure the key that is illegal, then recompile with the CREATEXFD directive.

Parent topic: Run Time - File Status Errors
Related information
CREATEXFD directive


11 Stored procedures not found

The installation of Database Connector for Microsoft SQL Server creates a number of stored procedures and tables. At least one of these was not found.

Parent topic: Run Time - File Status Errors


12 A column of a key is of data type TEXT or IMAGE, which is illegal

Columns that are part of an index may not be of type TEXT or type IMAGE.

Resolution:

Check your key definition.

Parent topic: Run Time - File Status Errors


12 DB2 library function returned an unexpected error

One of the DB2 library functions returned an error that was not expected.

Parent topic: Run Time - File Status Errors


13 Illegal size or type of data for variable xxx

An elementary data item in your FD was larger than 255 bytes, or there is no DB2 type that matches the current data type.

Parent topic: Run Time - File Status Errors


13 Internal error

Multiple records were found with the same index (this is a Microsoft SQL Server error).

Parent topic: Run Time - File Status Errors


14 More than one table with the same name

When tables were listed, more than one table was found with the same name.

Resolution:

Consider whether or not setting the USER_PATH ACUFH configuration variable resolves the issue. This variable enables tables with identical names, but with different ownership (located in a different schema) to be found and accessed.

Parent topic: Run Time - File Status Errors
Related information
USER_PATH configuration variable


16 Trying to rename a table across databases

RENAME works only within the same database.

Parent topic: Run Time - File Status Errors


17 Cache error

(Internal error) The internal process cache has been corrupted.

Resolution:

Contact Micro Focus Product Support.

Parent topic: Run Time - File Status Errors


18 Primary Key error

An error occurred when creating the primary key for a secondary table.

Parent topic: Run Time - File Status Errors


09/19 Table Size Error

The table is larger than Microsoft SQL Server accepts, either in the number of columns or in the number of bytes in the row.

Resolution:

Use the SECONDARY_TABLE XFD directive to get around this.

Parent topic: Run Time - File Status Errors
Related information
SECONDARY_TABLE directive


20 (no message associated with this error)

This error tells you that you are trying to do something with a FAST_ACCESS table that is not allowed.

Resolution:

The description of the ACUFH configuration variable in the section A_MSSQL_FAST_ACCESS details the restrictions for tables opened this way.

Parent topic: Run Time - File Status Errors
Related information
A_MSSQL_FAST_ACCESS configuration variable


21 (no message associated with this error)

There was a problem accessing one or more files, and processing of this request cannot continue. The tertiary error is an error code returned from the COBOL file system.

Parent topic: Run Time - File Status Errors


1001 Invalid Cursor

The prepare portion is aged out of cache.

Resolution:

Re-enter the code while invalidating the prepare portion.

Parent topic: Run Time - File Status Errors


22007 Invalid Character value for Cast Specification

The error sub-code 22007 indicates that an attempt was made to write non-numeric data into a numeric field (database column).
Parent topic: Run Time - File Status Errors


Compiler Errors

  • Bad picture for DATE: keyname
    The PICTURE must be six or eight bytes in length, either alphanumeric or numeric with no sign.
  • Data missing from key segment keyname
    This error occurs when some part of the named key cannot be placed in the XFD; the XFD cannot be generated in this situation.
  • Directive word too long: keyname
    With one exception, the words contained in an XFD directive, including field names, cannot exceed 30 characters. The value of a WHEN XFD directive may consist of up to 50 characters.
  • Field xxx causes duplicate database data
  • GROUP expected after USE
    The USE GROUP XFD directive must include both words.
  • Missing '=' in XFD directive
    The NAME XFD directive requires an equal ("=") sign. The WHEN XFD directive requires a comparison operator.
  • Missing field name after WHEN
    A valid field name, or the word "OTHER", must be specified with the WHEN XFD directive.
  • Net Express lock table missing
    The installation of Database Connectors for Microsoft SQL Server creates a number of stored procedures and tables. At least one of these was not found.
  • Value should be a literal: xxx
    This error occurs when the item to the right of an "=" should be a literal, and it isn't. The xxx in the message is the value found. A literal is either a quoted string or a numeric integer.
  • Value should be a name: xxx
    This error occurs when the item to the right of an "=" should be a name, and it isn't. For example, it would be an error to use a quoted string with the NAME XFD directive: $XFD NAME=some text. The xxx in the message is the value found.
  • Value should be numeric: xxx
    This error occurs when the item to the right of an "=" should be numeric and it isn't. The xxx in the message is the value found.
  • Variable file name requires FILE directive
    This message occurs when the Compiler cannot assign a name to the .xfd file, because the ASSIGN phrase for the file names a variable file name.
  • WHEN variable xxx not found in record
    This happens if you have a WHEN XFD directive that mentions a variable that doesn't exist in the record.
  • xxx not unique in first 30 characters
  • xxx: unknown XFD directive
    The Compiler did not recognize the XFD directive you used. The xxx is the XFD directive found.
Parent topic: Error Messages


Bad picture for DATE: keyname

The PICTURE must be six or eight bytes in length, either alphanumeric or numeric with no sign.
Parent topic: Compiler Errors


Data missing from key segment keyname

This error occurs when some part of the named key cannot be placed in the XFD; the XFD cannot be generated in this situation.

This usually occurs because of filler. For example:

01  my-record.
    03  my-key.
        05  filler            pic xx.
        05  field-1           pic xx.

If my-key is declared as a record key, this error occurs because the area of the key described by filler is not included in the XFD.

Resolution:

To correct this error, ensure that every character that is part of the key is included in some field that is part of the XFD. Use an XFD to give a field name to each filler, to ensure that fillers are included:

 01  my-record.
     03  my-key.
$xfd name=myfiller
         05   filler            pic xx.
         05   field-1           pic xx.
Parent topic: Compiler Errors


Directive word too long: keyname

With one exception, the words contained in an XFD directive, including field names, cannot exceed 30 characters. The value of a WHEN XFD directive may consist of up to 50 characters.
Parent topic: Compiler Errors
Related information
WHEN directive


Field xxx causes duplicate database data

The warning informs you of a special database situation that is not advisable. The XFD builds and the interface operates correctly.

This warning means that your record definition should be restructured. Your current definition is set up in such a way that:

  1. You have overlapping key fields, and
  2. Both keys must be represented in the database as separate items.

Database Connectors handles this situation by keeping the overlapping keys updated simultaneously, so that they always have the same value. However, the warning alerts you that you have the same data represented twice in the database. This is dangerous, because someone at the site might access the database via SQL and accidentally change only one of the keys.

Here's an example of the problem and a description of how to correct it (the example assumes that both key-1and key-2 have been declared as keys):

01  order–record.
    03  key–1.
        05  field–a                  pic x(5).
        05  field–b                  pic 9(5).
        05  key–2
            redefines field–b        pic x(3).

This example generates the warning message.

Because key-2 is a key, it must also be represented in the database. It doesn't correspond exactly to any other data field, so it must be entered as a separate column in the database.

In the COBOL view of the file, key-1and key-2 overlap. But the requirements of database storage force the same data (known to COBOL as field-b) to be physically represented twice in the database. Any updates to the data from any COBOL program correctly updates both columns. Updates from outside of this COBOL program carry no such guarantee.

Resolution

In this example, you can correct the situation by breaking field-b into two columns, so that key-2 corresponds exactly to another data field:

01  order–record.
    03  key–1.
        05  field–a                        pic x(5).
        05  field–b.
            07  field-b1                   pic x(3).
            07  field-b2                   pic 9(2).
        05  key–2
            redefines field–b              pic x(3).
Parent topic: Compiler Errors
Related information
CREATEXFD directive


GROUP expected after USE

The USE GROUP XFD directive must include both words.
Parent topic: Compiler Errors
Related information
USE GROUP directive


Missing '=' in XFD directive

The NAME XFD directive requires an equal ("=") sign. The WHEN XFD directive requires a comparison operator.
Parent topic: Compiler Errors
Related information
NAME directive
WHEN directive


Missing field name after WHEN

A valid field name, or the word "OTHER", must be specified with the WHEN XFD directive.
Parent topic: Compiler Errors
Related information
WHEN directive


Net Express lock table missing

The installation of Database Connectors for Microsoft SQL Server creates a number of stored procedures and tables. At least one of these was not found.
Parent topic: Compiler Errors


Value should be a literal: xxx

This error occurs when the item to the right of an "=" should be a literal, and it isn't. The xxx in the message is the value found. A literal is either a quoted string or a numeric integer.
Parent topic: Compiler Errors


Value should be a name: xxx

This error occurs when the item to the right of an "=" should be a name, and it isn't. For example, it would be an error to use a quoted string with the NAME XFD directive: $XFD NAME=some text. The xxx in the message is the value found.
Parent topic: Compiler Errors


Value should be numeric: xxx

This error occurs when the item to the right of an "=" should be numeric and it isn't. The xxx in the message is the value found.
Parent topic: Compiler Errors


Variable file name requires FILE directive

This message occurs when the Compiler cannot assign a name to the .xfd file, because the ASSIGN phrase for the file names a variable file name.

Resolution:

In this case, you must use a FILE XDF directive to name the .xfd file.

Parent topic: Compiler Errors
Related information
FILE directive


WHEN variable xxx not found in record

This happens if you have a WHEN XFD directive that mentions a variable that doesn't exist in the record.
Parent topic: Compiler Errors
Related information
WHEN directive


xxx not unique in first 30 characters

This message occurs if a field name is not unique within the first 30 characters. The xxx is the name found.

Resolution:

You can either change the field name or apply the NAME XFD directive.

Parent topic: Compiler Errors
Related information
NAME XFD directive


xxx: unknown XFD directive

The Compiler did not recognize the XFD directive you used. The xxx is the XFD directive found.

Resolution:

Check for a typographical error.

Parent topic: Compiler Errors


Stored Procedures

  • Sample XFD
  • sp_AcuInit stored procedure
  • sp_AcuRemoveUnusedLocks_1 stored procedure
  • sp_AcuTableReport_1 stored procedure
  • sp_AcuUserCount_1 stored procedure
  • sp_AcuZeroUserCount_1 stored procedure
  • tablename_delete stored procedure
  • tablename_insert stored procedure
  • tablename_read stored procedure
  • tablename_startnnn stored procedure
  • tablename_update stored procedure
Parent topic: Database Connectors Reference


Sample XFD

The following code-block is a sample XFD that the stored procedure examples in this section are acting upon:

<?xml version="1.0" encoding="utf-8" standalone="no"?>
<!-- ftestdat.xfd - generated on 19-Apr-16 14:29
     Micro Focus COBOL                  V2.3 revision 002
     AXCGG/AA0/00000  GNR-005203002AI
 -->
<xfd:xfdfile
     xmlns:xfd="http://www.microfocus.com">
  <xfd:identification
       xfd:version="7">
    <xfd:select-name>FTEST2-FILE</xfd:select-name>
    <xfd:maximum-numeric-digits>31</xfd:maximum-numeric-digits>
    <xfd:alphabet>None</xfd:alphabet>
    <xfd:period-character>.</xfd:period-character>
    <xfd:comma-character>,</xfd:comma-character>
    <xfd:file-organization>Indexed</xfd:file-organization>
    <xfd:table-name>FTESTDAT</xfd:table-name>
    <xfd:number-of-keys>3</xfd:number-of-keys>
    <xfd:minimum-record-size>36</xfd:minimum-record-size>
    <xfd:maximum-record-size>36</xfd:maximum-record-size>
    <xfd:sign-compatibility>8</xfd:sign-compatibility>
  </xfd:identification>
  <xfd:keys>
    <xfd:key
         xfd:segment-count="1"
         xfd:duplicates-allowed="false">
      <xfd:segments>
        <xfd:segment
             xfd:segment-size="4"
             xfd:segment-offset="0"/>
      </xfd:segments>
      <xfd:key-columns
           xfd:key-column-count="1">
        <xfd:key-column
             xfd:key-column-name="ftest2-key"/>
      </xfd:key-columns>
    </xfd:key>
    <xfd:key
         xfd:segment-count="1"
         xfd:duplicates-allowed="true">
      <xfd:segments>
        <xfd:segment
             xfd:segment-size="4"
             xfd:segment-offset="4"/>
      </xfd:segments>
      <xfd:key-columns
           xfd:key-column-count="2">
        <xfd:key-column
             xfd:key-column-name="ftest2-key1-seg1"/>
        <xfd:key-column
             xfd:key-column-name="ftest2-key1-seg2"/>
      </xfd:key-columns>
    </xfd:key>
    <xfd:key
         xfd:segment-count="1"
         xfd:duplicates-allowed="false">
      <xfd:segments>
        <xfd:segment
             xfd:segment-size="4"
             xfd:segment-offset="8"/>
      </xfd:segments>
      <xfd:key-columns
           xfd:key-column-count="1">
        <xfd:key-column
             xfd:key-column-name="ftest2-altkey2"/>
      </xfd:key-columns>
    </xfd:key>
  </xfd:keys>
  <xfd:conditions
       xfd:condition-count="0"/>
  <xfd:fields
       xfd:elementary-items="7"
       xfd:elementary-items-with-occurs="7"
       xfd:total-items="9"
       xfd:total-items-with-occurs="9">
    <xfd:field
         xfd:field-level="1"
         xfd:field-name="ftest2-record"
         xfd:field-condition="999"
         xfd:field-offset="0"
         xfd:field-bytes="36"
         xfd:field-type="152"
         xfd:field-length="36"
         xfd:field-scale="0"
         xfd:field-user-flags="0"/>
    <xfd:field
         xfd:field-level="3"
         xfd:field-name="ftest2-key"
         xfd:field-offset="0"
         xfd:field-bytes="4"
         xfd:field-type="152"
         xfd:field-length="4"
         xfd:field-scale="0"
         xfd:field-user-flags="0"
         xfd:field-condition="0"/>
    <xfd:field
         xfd:field-level="3"
         xfd:field-name="ftest2-altkey1"
         xfd:field-condition="999"
         xfd:field-offset="4"
         xfd:field-bytes="4"
         xfd:field-type="152"
         xfd:field-length="4"
         xfd:field-scale="0"
         xfd:field-user-flags="0"/>
    <xfd:field
         xfd:field-level="5"
         xfd:field-name="ftest2-key1-seg1"
         xfd:field-offset="4"
         xfd:field-bytes="2"
         xfd:field-type="152"
         xfd:field-length="2"
         xfd:field-scale="0"
         xfd:field-user-flags="0"
         xfd:field-condition="0"/>
    <xfd:field
         xfd:field-level="5"
         xfd:field-name="ftest2-key1-seg2"
         xfd:field-offset="6"
         xfd:field-bytes="2"
         xfd:field-type="152"
         xfd:field-length="2"
         xfd:field-scale="0"
         xfd:field-user-flags="0"
         xfd:field-condition="0"/>
    <xfd:field
         xfd:field-level="3"
         xfd:field-name="ftest2-altkey2"
         xfd:field-offset="8"
         xfd:field-bytes="4"
         xfd:field-type="152"
         xfd:field-length="4"
         xfd:field-scale="0"
         xfd:field-user-flags="0"
         xfd:field-condition="0"/>
    <xfd:field
         xfd:field-level="3"
         xfd:field-name="ftest2-number"
         xfd:field-offset="12"
         xfd:field-bytes="9"
         xfd:field-type="144"
         xfd:field-length="9"
         xfd:field-scale="0"
         xfd:field-user-flags="0"
         xfd:field-condition="0"/>
    <xfd:field
         xfd:field-level="3"
         xfd:field-name="ftest2-info"
         xfd:field-offset="21"
         xfd:field-bytes="10"
         xfd:field-type="152"
         xfd:field-length="10"
         xfd:field-scale="0"
         xfd:field-user-flags="0"
         xfd:field-condition="0"/>
    <xfd:field
         xfd:field-level="3"
         xfd:field-name="ftest2-in"
         xfd:field-offset="31"
         xfd:field-bytes="5"
         xfd:field-type="152"
         xfd:field-length="5"
         xfd:field-scale="0"
         xfd:field-user-flags="0"
         xfd:field-condition="0"/>
  </xfd:fields>
</xfd:xfdfile>
Parent topic: Stored Procedures


sp_AcuInit stored procedure

If the stored procedure sp_AcuInit exists, it is executed when the connection is made to the server. This is a procedure you can set up to do customized initialization. This stored procedure does not take any parameters and does not return any results. This optional stored procedure is executed for all connections by Database Connectors interface to the database, not just for the primary connection.

As an example of customized initialization, you can use this stored procedure to remove stale locks by calling sp_AcuRemoveUnusedLocks_1 (which is installed when all the other stored procedures are installed), or to limit access to the database by certain users during certain hours.

If sp_AcuInit returns an error, the connection is denied and the error is reported to the COBOL program. The method for returning an error is to execute the Transact-SQL statement "raiserror". See your Microsoft SQL Server documentation for information about Transact-SQL and stored procedures.

If the sp_AcuInit procedure exists, it executes whenever Database Connectors makes a new database connection, including FAST_ACCESS connections. Therefore, any customization that you've indicated via sp_AcuInit applies to all connections a transaction makes to the database, not just the primary connection.

Note: The sp_AcuInit procedure executes at connection time. It can exist in any database, but should reside either in the current database or a database in which stored procedures are searched by default (such as master).
Parent topic: Stored Procedures
Related information
sp_AcuRemoveUnusedLocks_1 stored procedure


sp_AcuRemoveUnusedLocks_1 stored procedure

Use this stored procedure to determine who is logged in and to remove Process IDs that are no longer active on the system. You can call this from sp_AcuInit each time a user connects to the database to ensure that the lock table contains only active locks. This stored procedure must reside in the same database as the AcuOpenTables1 and AcuLocks1 tables; it is placed there automatically when these tables are created during installation.

Parent topic: Stored Procedures
Related information
sp_AcuInit_1 stored procedure
Table Locking


sp_AcuTableReport_1 stored procedure

Use this stored procedure to learn who is using the tables in the database at the time this procedure is run. Run this procedure before running the sp_AcuZeroUserCount stored procedure, so that you can contact users to inform them that the database will be closing. This stored procedure must reside in the same database as the AcuOpenTables1 and AcuLocks1 tables; it is placed there automatically when these tables are created during installation.

Parent topic: Stored Procedures
Related information
sp_AcuZeroUserCount_1 stored procedure
Table Locking


sp_AcuUserCount_1 stored procedure

You can run sp_AcuUserCount from the query analyzer to learn how many users have a particular table open. You can use this to track table and database activity to ensure that your database is running as efficiently as possible. This stored procedure must reside in the same database as the AcuOpenTables1 and AcuLocks1 tables; it is placed there automatically when these tables are created during installation.

Parent topic: Stored Procedures
Related information
Table Locking


sp_AcuZeroUserCount_1 stored procedure

Use sp_AcuZeroUserCount to remove all locks on a table and close it. Be sure to run this stored procedure on all tables in the database if shutting down the database for any reason. This stored procedure must reside in the same database as the AcuOpenTables1 and AcuLocks1 tables; it is placed there automatically when these tables are created during installation.

Parent topic: Stored Procedures
Related information
Table Locking


tablename_delete stored procedure

tablename_delete is used to DELETE a record from an XFD file. The parameters passed to the stored procedure are the values of the primary key, in the order they are listed in the XFD.

Based on the Sample XFD, you might want to create the following stored procedure for deleting records from a file:

create procedure ftestdat_delete
@ft2_key char(4)
as
delete from ftestdat where ftest2_key = @ft2_key

grant execute on ftestdat_delete to public
Parent topic: Stored Procedures
Related information
Sample XFD
tablename_insert stored procedure
Developer- or Site-supplied Stored Procedures
tablename_startnnn stored procedure
tablename_update stored procedure
tablename_read stored procedure


tablename_insert stored procedure

tablename_insert is used to WRITE a record to the file. The parameters passed to the stored procedure are the values of all the columns in the row, in the order of the columns in the database. The timestamp column and identity column (if present in the table) are not passed to the stored procedure.

Given the Sample XFD, you might want to create the following stored procedure for writing records to a file:

create procedure ftestdat_insert
@ft2_key char(4),
@ft2_key1_seg1 char(2),
@ft2_key1_seg2 char(2),
@ft2_altkey2 char(4),
@ft2_number char(9),
@ft2_info char(10)
as
insert into ftestdat (ftest2_key, ftest2_key1_seg1,
ftest2_key1_seg2, ftest2_altkey2, ftest2_number,
ftest2_info) values (@ft2_key, @ft2_key1_seg1,
@ft2_key1_seg2, @ft2_altkey2, @ft2_number, @ft2_info)
grant execute on ftestdat_insert to public
Parent topic: Stored Procedures
Related information
Sample XFD
Developer- or Site-supplied Stored Procedures
tablename_startnnn stored procedure
tablename_update stored procedure
tablename_read stored procedure
tablename_delete stored procedure


tablename_read stored procedure

tablename_read is used to read a random record (READ only, not READ NEXT or READ PREVIOUS). The parameters passed to the stored procedure are the values of the primary key, in the order they are listed in the XFD. The expected rowset is the columns in the first table (if secondary tables are used), or the columns of the table (if secondary tables are not necessary).

This stored procedure is very similar to tablename_start.

Parent topic: Stored Procedures
Related information
Developer- or Site-supplied Stored Procedures
tablename_insert stored procedure
tablename_startnnn stored procedure
tablename_update stored procedure
tablename_delete stored procedure


tablename_startnnn stored procedure

tablename_startnnn is used to START a file. The nnn value is the key number to start on, and is 0 filled. For example, the start procedure for the primary key for table mytab is "mytab_start000".

Note: If A_MSSQL_NO_23_ON_START is set to Yes, the start stored procedure is disabled.

Because there can be up to 119 alternate keys, the Connector does not search for a start procedure unless, or until, it is used. The parameters passed to the stored procedure are a 2-char mode [it is a varchar(2) field], with one of the following values: ">", ">=", "=", "<=", or "<". The rest of the parameters are the columns of the key used to start. Because the ANSI specification for START includes information about the size of the key being used (and in particular allows partial keys), the start procedure is used only if an entire key is given to the start verb. This procedure is also special in that it does not return data, but needs to raise an error condition if the start fails. The way to raise the error condition from within the stored procedure is to include code similar to the following:

raiserror (523409,16,1 "Record not found")

The code 523409 is very important. It is the code searched for in setting the error condition from within Database Connectors. If you use a different number, your starts may succeed when they should actually fail.

For example, based on the Sample XFD, you can create the following stored procedure to start a file:

create procedure ftestdat_start001
@mode varchar(2),
@ft2_key1_seg1 char(2),
@ft2_key1_seg2 char(2)
as

if exists (select 1 from ftestdat where

(ftest2_key1_seg1 = @ft2_key1_seg1 and
((@mode = ">=" and ftest2_key1_seg2 >=@ft2_key1_seg2) or
(@mode = ">" and ftest2_key1_seg2 > @ft2_key1_seg2) or
(@mode = "=" and ftest2_key1_seg2 = @ft2_key1_seg2) or
(@mode = "<" and ftest2_key1_seg2 < @ft2_key1_seg2) or
(@mode = "<=" and ftest2_key1_seg2 <= @ft2_key1_seg2))))
return
if exists (select 1 from ftestdat where
(((@mode = ">=" or @mode = ">") and
ftest2_key1_seg1 > @ft2_key1_seg1) or
((@mode = "<=" or @mode = "<") and
ftest2_key1_seg1 < @ft2_key1_seg1)))
return
raiserror (523409,16,1 "Record not found")

grant execute on ftestdat_start001 to public

This table shows the codes that can be used when setting the error condition from within Acu4GL.

Code | Message
523401 | No table name given
523402 | Table must be in current database
523403 | Table does not exist
523404 | Internal error - no table id given
523405 | Internal error - Bad object id given
523406 | Table is open by another user
523407 | Table is write-locked by another user
523408 | Table is read-locked by another user
523409 | Record not found
Parent topic: Stored Procedures
Related information
Sample XFD
A_MSSQL_NO_23_ON_START configuration variable
Developer- or Site-supplied Stored Procedures
tablename_insert stored procedure
tablename_update stored procedure
tablename_read stored procedure
tablename_delete stored procedure


tablename_update stored procedure

tablename_update is used to REWRITE a record from the file. The parameters passed to the stored procedure are the values of all the columns in the row, in the order of the columns in the database. The timestamp column and identity column (if present in the table) are not passed to the stored procedure.

For example, based on the Sample XFD, you might want to create the following stored procedure for rewriting a record:

create procedure ftestdat_update
@ft2_key char(4),
@ft2_key1_seg1 char(2),
@ft2_key1_seg2 char(2),
@ft2_altkey2 char(4),
@ft2_number char(9),
@ft2_info char(10)
as
update ftestdat set
ftest2_key = @ft2_key,
ftest2_key1_seg1 = @ft2_key1_seg1,
ftest2_key1_seg2 = @ft2_key1_seg2,
ftest2_altkey2 = @ft2_altkey2,
ftest2_number = @ft2_number,
ftest2_info = @ft2_info
where ftest2_key = @ft2_key

grant execute on ftestdat_update to public
Parent topic: Stored Procedures
Related information
Sample XFD
Sample XFD
Developer- or Site-supplied Stored Procedures
tablename_insert stored procedure
tablename_startnnn stored procedure
tablename_read stored procedure
tablename_delete stored procedure


XFD Directives

The following are XFD directives for Database Connectors.

XFD directives provide extended control over how the database table is built. Among other things, they let you:

  • Specify a column name to be used in the database table, in place of a COBOL field name.
  • Map elementary items of a group item together into a single column.
  • Cause numeric COBOL data to be treated as a text string in the database.
  • Cause the fields from a specific record in a file to appear in the database table (rather than just the fields from the largest record).
  • Assign a column the DATE type, providing the built-in functionality that dates have in the RDBMS.
  • Give a name to the XFD file itself.

XFD directives are always placed within a COBOL FD. They do not affect Procedure Division I/O statements, and they do not change your COBOL fields in any way. Rather, they guide the building of the XFDs, giving you control over the way COBOL data maps to database fields.

  • ALPHA XFD directive
  • BINARY XFD directive
  • COBOL-TRIGGER XFD directive
  • COMMENT XFD directive
  • DATE XFD directive
  • FILE XFD directive
  • NAME XFD directive
  • NULL XFD Directive
  • NUMERIC XFD directive
  • SECONDARY_TABLE XFD directive
  • USE GROUP XFD directive
  • VAR_LENGTH XFD directive
  • WHEN XFD directive
  • XSL XFD directive
Parent topic: Database Connectors Reference


ALPHA XFD directive

The ALPHA XFD directive allows you to treat a data item as alphanumeric text in the database, when it is declared as numeric in the COBOL program.

Syntax

$XFD ALPHA

This is especially useful when you have numeric keys in which you occasionally store non-numeric data, such as LOW-VALUES or special codes. In this situation, treating the field as alphanumeric allows you to move any kind of data to it.

Example

Suppose you have specified KEY IS code-key. Then assume the following record definition:

01  code-record.
    03  code-key.
        05  code-num      pic 9(5).

In the database, group items are disregarded, so CODE-NUM is the actual key field. Suppose you needed to move a non-numeric value to the key:

MOVE "C0531" TO CODE-KEY.
WRITE CODE-RECORD.

In this case the results are not well-defined because a non-numeric value has been moved into a numeric field. The database might very well reject the record.

One way to solve this problem is to use the ALPHA XFD directive. This causes the corresponding database field to accept alphanumeric data:

 01  code-record.
     03  code-key.
$XFD ALPHA
         05  code-num      pic 9(5).

As an alternative, you could specify the USE GROUP XFD directive on the line before code-key. The USE GROUP XFD directive implies that the field is alphanumeric.

Parent topic: XFD Directives
Related information
USE GROUP directive


BINARY XFD directive

The BINARY XFD directive is used to specify that the data in the field could be alphanumeric data of any classification. Absolutely any data is allowed.

The BINARY XFD directive cannot be used in combination with the VAR_LENGTH XFD directive.

The method of storing fields declared as binary is database-specific. For example, Oracle uses the data type raw for the field.

Syntax

$XFD BINARY

Example

You might use this XFD directive when you need to store a key that contains LOW-VALUES; COBOL allows a numeric field to contain LOW or HIGH values, but these are invalid for a numeric field in the RDBMS:

 01  code-record.
     03  code-key.
         05  code-indic          pic x.
$XFD BINARY
         05  code-num            pic 9(5).
         05  code-suffix         pic x(3).
             .
             .
             .
 move low-values to code-num.
Parent topic: XFD Directives
Related information
VAR_LENGTH directive


COBOL-TRIGGER XFD directive

This XFD directive indicates that a COBOL trigger is to be executed.

Note: This XFD directive must come immediately before the file descriptor of the file for which the trigger is defined. It uses the standard XFD directive syntax.

Syntax

XFD COBOL-TRIGGER=program-name

The following three parameters are passed to the COBOL program:

01  OPCODE.
  88  READ-BEFORE       VALUE "r".
  88  READ-AFTER        VALUE "R".
  88  WRITE-BEFORE      VALUE "w".
  88  WRITE-AFTER       VALUE "W".
  88  REWRITE-BEFORE    VALUE "u".
  88  REWRITE-AFTER     VALUE "U".
  88  DELETE-BEFORE     VALUE "d".
  88  DELETE-AFTER      VALUE "D".

01  FILE-RECORD         PIC X(MAX-RECORD-SIZE).

01  ERROR-CODE          PIC 99.

In cases in which the record size is known, the actual record is passed [READ-AFTER (all), WRITE-BEFORE, WRITE-AFTER, REWRITE-BEFORE, REWRITE-AFTER].

In cases in which the record size is not known, the maximum record size is passed [READ-BEFORE (all), DELETE-BEFORE, DELETE-AFTER].

The contents of this field are identical to the record area given to the file operation. The BEFORE images have the value before the file operation, and the AFTER images have the value after the file operation.

Use the ERROR-CODE parameter to signal that an error occurred:

  • If you set this parameter in a BEFORE trigger, the file operation does not execute
  • If you set this parameter in an AFTER trigger, the run time system treats the file operation as an error, though in fact the file operation did execute. This means that, for sequential access (next or previous), the file position has changed and a subsequent next or previous act as if the prior file operation succeeded

Also, if you set an error in the REWRITE-AFTER or DELETE-AFTER, the record does not return to its prior state — the record is modified or deleted in the database.

Please note that if the COBOL program cannot be called (for any reason), it is treated as having succeeded.

Parent topic: XFD Directives


COMMENT XFD directive

The COMMENT XFD directive lets you include comments in an XFD file. Because the information is embedded in a comment, it doesn't interfere with processing by the interfaces. Each comment entered with this XFD directive appears in the XFD file with the symbol "#" in column one.

Syntax

$XFD COMMENT text
Parent topic: XFD Directives


DATE XFD directive

The DATE XFD directive is used to store a field in the database as a date. Because there's no COBOL syntax that identifies a field as a date, you may want to add this XFD directive to differentiate dates from other numbers, so that they enjoy the properties associated with dates in the RDBMS. To view special options for two-digit dates, see the description of the ACUFH configuration file variable 4GL_2000_CUTOFF in 4GL_2000_CUTOFF configuration variable.

Syntax

$XFD DATE=date-format-string

This XFD directive implies the NUMERIC XFD directive.

If no date-format-string is specified, six-digit (or six-character) fields are retrieved as YYMMDD from the database. Eight-digit fields are retrieved as YYYYMMDD.

The date-format-string is a description of the desired date format, composed of characters from the following list:

M | Month (01–12)
Y | Year (two or four digits)
D | Day of month (01–31)
J | Julian day (00000000–99999999)
E | Day of year (001–366)
H | Hour (00–23)
N | Minute
S | Second
T | Hundredths of a second

Any other characters cause the date format string to be invalid and result in a corrupt XFD error or a compile-time warning.

Each character in a date format string can be considered a place holder that represents the type of information stored at that location. The characters also determine how many digits are used for each type of data.

For example, although you would typically represent the month with two digits, if you specify "MMM" as part of your date format, the resulting date uses three digits for the month, left-zero-filling the value. If the month is given as "M", the resulting date uses a single digit and truncates on the left.

Julian Dates

Because the definition of Julian day varies, the DATE XFD directive offers a great deal of flexibility for representing Julian dates. Many source books define the Julian day as the day of the year, with January 1st being 001, January 2nd being 002, and so forth. If you want to use this definition for Julian day, simply use "EEE" (day of year) in your date formats.

Other reference books define the Julian day as the number of days since some specific base date in the past. This definition is represented in the DATE XFD directive with the letter "J" (for example, a six-digit date field would be preceded with the XFD directive $XFD DATE=JJJJJJ). The default base date for this form of Julian date is 12/31/4714 BC.

You may define your own base date for Julian date calculations by setting the ACUFH configuration file variable 4GL_JULIAN_BASE_DATE.

Handling Invalid Dates

Dates in the following range are considered valid:

01/01/0001 to 12/31/9999

If the interface considers a date valid and passes it to an RDBMS that considers it invalid, the results depend on the particular RDBMS. Some systems return an error code. Some store the date as NULL and do not return an error. Some store the date in an unpredictable fashion and do not return an error. We recommend that you determine the error handling procedures of the RDBMS in use.

If a COBOL program attempts to write a record containing a date that the Database Connectors knows is invalid, the Database Connectors inserts NULLs into the date field and writes the record.

If a COBOL program attempts to read into a record from a table with a NULL date field, zeroes are inserted into that field in the COBOL record.

For date fields having two-digit years, by default years 0 through 19 are inserted as 2000 through 2019, and years 20 through 99 are inserted as 1920 through 1999.

You can control a number of these situations using configuration variables; see the links at the bottom of the page for more details.

Note:

If a field is used as part of a key, the field cannot be a NULL value. For information about converting date values using the 4GL_CONVERT_DATE_ZERO variable, see 4GL_CONVERT_DATE_ZERO configuration variable.

Using Group Items

You may place the DATE XFD directive in front of a group item, as long as you also use the USE GROUP XFD directive.

Examples

Example 1

 $xfd date
      03  date-hired    pic 9(8).
      03  pay-scale     pic x(3).

The column "date-hired" has eight digits is type DATE in the database, with a format of YYYYMMDD.

Example 2

$XFD DATE, USE GROUP
      03  date-hired.
          05  yyyy      pic 9(4).
          05  mm        pic 9(2).
          05  dd        pic 9(2).

This also produces a column named "date-hired" with eight digits and type DATE in the database, format YYYYMMDD.

Example 3

 $XFD DATE=EEEYYYY
      03  date-sold      pic 9(7).
      03  sales-rep      pic x(30).

This produces a column named "date-sold" with seven digits and type DATE in the database. The date contains the day of the year (for example, February 1st would be 032), followed by the four-digit year, such as 1999).

Parent topic: XFD Directives
Related information
4GL_2000_CUTOFF configuration variable
4GL_JULIAN_BASE_DATE configuration variable
4GL_CONVERT_DATE_ZERO configuration variable
NUMERIC directive
USE GROUP directive


FILE XFD directive

The FILE XFD directive supplies a starting name from which the XFD file name is formed. This XFD directive is required only when the file name in the COBOL code is nonspecific. For example, use this XFD directive when the SELECT for the file has a variable ASSIGN name (ASSIGN TO variable_name). In this case, the Connector cannot form a file name automatically, and you must provide a name. The starting name serves as the basis for the XFD name.

Syntax

$XFD FILE=name

This XFD directive must appear on the line immediately preceding the file's FD.

Example

Suppose your SELECT statement has a variable ASSIGN name such as the one shown here:

select my-file
assign to my-variable.

Then you would need to add the FILE XFD directive as shown here:

select my-file
 assign to my-variable.
    .
    .
    .
$xfd  file=payroll
 fd my-file
Parent topic: XFD Directives
Related information
XFD_MAP configuration variable


NAME XFD directive

The NAME XFD directive assigns a database field name to the field defined on the next line.

Syntax

$XFD NAME=fieldname

This XFD directive has several uses, as shown in the following examples.

Example 1 — Non-unique field names

Within a database file, all field names must be unique. (Multiple database tables may include the same field name, but duplicates may not exist within a single table.) Unique field names are not required in COBOL, because names can be qualified by group items.

For example, the following is acceptable in COBOL:

01  employee-record.
    03  date-hired.
        05  yy      pic 99.
        05  mm      pic 99.
        05  dd      pic 99.
    03  date-last-paid.
        05  yy      pic 99.
        05  mm      pic 99.
        05  dd      pic 99.

You need not change the field names in your COBOL program to access a database. Instead, you use the NAME XFD directive to provide unique database names for the fields. For example:

  01  employee-record.
      03  date-hired.
          05  yy      pic 99.
          05  mm      pic 99.
          05  dd      pic 99.
      03      date-last-paid.
 $xfd  name=year-paid
          05  yy      pic 99.
 $xfd  name=month-paid
          05  mm      pic 99.
 $xfd  name=day-paid
          05  dd      pic 99.

The dates portion of the resulting database table is as follows.

yy | mm | dd | year_paid | month_paid | day_paid
88 | 02 | 15 | 94 | 04 | 30

Example 2 — Names not unique within first 18 characters or longer than 18 characters

Some SQL-based databases require that names be unique within 18 characters, and some require that names be no longer than 18 characters. For those systems, the run time system truncates longer COBOL names after the first 18 characters.

For names that are identical within the first 18 characters, or are not meaningful if shortened to the first 18 characters, use the NAME XFD directive to assign them different database field names.

Suppose you had:

01  acme-employee-record.
    03  acme-employee-record-date-hired          pic x(6).
    03  acme-employee-record-date-last-paid      pic x(6).

You could add two NAME XFD directives to differentiate the two item names by making them meaningful within 18 characters:

  01  acme-employee-record.
 $xfd name=date-hired
      03  acme-employee-record-date-hired        pic x(6).
 $xfd name=date-last-paid
      03  acme-employee-record-date-last-paid    pic x(6).

Note that your COBOL names have not changed. The new names are used only for the database fields.

Each time you compile your program to create XFDs, any field names longer than 18 characters is checked for uniqueness within the first 18. If any field names are identical for the first 18 characters, a compiler warning message appears. A warning of this type does not prevent the program from compiling, and the XFD is generated.

Example 3 — Assigning shorter names

You may want to use the NAME XFD directive to assign shorter names than those used in your COBOL programs. This makes the formation of interactive SQL queries easier and quicker. For example:

 $XFD  NAME=EMPNO
      03  employee-number        pic x(8).

This XFD directive causes the XFD to map EMPLOYEE-NUMBER to EMPNO in the database.

Example 4 — Matching field names and COBOL FDs

If your database already exists, and a field name in the database does not match the name used in your COBOL FD, you can use a NAME XFD directive to associate the two names. For example:

 $xfd name=employee-no
      03  employee-number        pic x(8).

This XFD directive causes the XFD to map EMPLOYEE-NUMBER in the COBOL program to EMPLOYEE-NO in the database.

Example 5 — Renaming fields that begin with a numeric character

If your COBOL program uses field names that begin with a numeric character, use the NAME XFD directive to assign a different name for use with your database. SQL typically generates a syntax error when it encounters a column name that begins with a numeric character. For example:

    03  12-months-sales      pic 9(5)V99.

could be renamed this way:

 $xfd  name=twelve-months-sales
      03  12-months-sales    pic 9(5)V99.
Parent topic: XFD Directives


NULL XFD Directive

The NULL XFD directive handles NULL values in numeric tables by comparing non-key field values when performing read and write actions on the database to determine and perform appropriate actions.

Syntax

$XFD NULL=literal
where literal is any literal value, or one of the following special literals to indicate that the field is comprised entirely of a specific type of value:
  • LOW-VALUES
  • HIGH-VALUES
  • SPACES
  • ZEROES

Specify the NULL XFD directive on individual numeric fields within the COBOL FD.

During a write to the database, this directive compares the value of the COBOL numeric field against its own set literal value. If the values are equal, it writes a NULL value to the database.

During a read of the database, if a NULL value is returned, this directive places its own set literal value into the COBOL numeric field before returning to the COBOL program.

Note: Fields allowing NULL values are not allowed as part of a record key. If the compiler encounters a record key containing NULL values, it returns a syntax error.
Parent topic: XFD Directives


NUMERIC XFD directive

The NUMERIC XFD directive allows you to treat a data item as an unsigned integer when it is declared as alphanumeric. You might use this when the data stored in the item is always numeric.

Syntax

$XFD NUMERIC

Example

$xfd numeric
 01  student-code      pic x(7).
Parent topic: XFD Directives


SECONDARY_TABLE XFD directive

Some Database Connectors interfaces (including MSSQL Server) permit subordinate tables. When this is the case, you can use the SECONDARY_TABLE XFD directive to indicate that the next data item may be placed into a subordinate table if more than one table is necessary to accommodate the data.

Up to 26 subordinate tables can be created from a single record description. Each table name is based on the original table name, with a letter from A to Z appended. For example, if the original table were named "my-table", subsequent subordinate tables would be given these names:

  • my-tableA
  • my-tableB
  • my-tableC
  • my-tableD

When the run time system accesses the XFD, it makes an initial pass through the data, taking all eligible data items for which the SECONDARY_TABLE XFD directive is not specified.

SECONDARY_TABLE is ignored for certain data items. These include:

  • Any field that is part of a key
  • Any field that is an indicator for a WHEN XFD directive

If the table size is not exceeded in the first pass, then, in a second pass, the run time system appends to the original table all items marked SECONDARY_TABLE that can be accommodated.

When the first table reaches a limit (either in total number of columns or total number of characters), that table is created, and a new table is begun. Items that did not fit into the previous table are placed into a subordinate table, in the order in which they are encountered.

The process repeats until all items have been accommodated.

It is permissible to place the SECONDARY_TABLE XFD directive just before a level 01 record definition. In this case, it applies to all fields underneath the level 01.

Syntax

$XFD SECONDARY_TABLE

Example

 $xfd secondary_table
  01  description      pic x(80).
Parent topic: XFD Directives
Related information
WHEN XFD directive


USE GROUP XFD directive

The USE GROUP XFD directive allows you to enter a group item into the database as a single field, instead of using the elements contained in the group. This is necessary if the item is stored in an existing database as a group, rather than as individual fields.

Combining fields of data into meaningful groups for database storage also improves I/O efficiency.

Syntax

$XFD USE GROUP

By default, the USE GROUP XFD directive implies that the consolidated field is alphanumeric. If you want a numeric field, simply add the word NUMERIC at the end of the XFD directive.

Example 1 – Combining DATE and USE GROUP XFD directives

For example, the XFD directive in the following code combines the functions of the USE GROUP and DATE XFD directives, and indicates that the date should be entered into the database as a single date-formatted data item instead of three distinct fields:

  $XFD  USE GROUP, DATE
       03  date-entered.
           05  yy      pic 99.
           05  mm      pic 99.
           05  dd      pic 99.

Either a comma or a space (or both) may separate the word "DATE" from the words "USE GROUP".

Example 2 – Forming larger units

Other fields with which you might use this XFD directive include multi-part account numbers or department numbers, or keys that are referenced as a unit but not by their individual pieces. Here's an example of an item that might be grouped:

 $xfd  use group
  01  gl-acct-no.
      03  main-acct     pic 9(4).
      03  sub-acct      pic 9(3).
      03  dept-no       pic 9(3).

If you are using an existing database in which certain fields are grouped, they must also be grouped in your COBOL file descriptor.

If the database does not yet exist, keep in mind that combining fields into groups can improve execution speed. Whether to group fields or not also depends on whether they are always stored and used together. Someone who really knows how the data is being used might help to identify groups of fields that can be combined to speed processing.

Parent topic: XFD Directives
Related information
DATE XFD directive
NUMERIC XFD directive


VAR_LENGTH XFD directive

By default, the Compiler generates fixed-length fields in the XFD. The VAR_LENGTH XFD directive requests that the data item that immediately follows the XFD directive be assigned a type that implies variable length, if possible. This can save considerable space in your database.

The VAR_LENGTH XFD directive cannot be used in combination with the BINARY XFD directive.

The precise variable type that is assigned to the data item depends on which RDBMS is in use. Possible variable types that might be assigned are VARCHAR and VARBINARY.

Syntax

$XFD VAR_LENGTH

Example

For example, the XFD directive in the following code indicates that the employee-name field should be entered into a DB2 database as a VARCHAR data item.

 $XFD  VAR_LENGTH
      03  employee-notes      pic x(300).
Parent topic: XFD Directives
Related information
BINARY XFD directive


WHEN XFD directive

The WHEN XFD directive is used when you want to include multiple record definitions or REDEFINES in a database table. It's typically used when you want to force certain columns to be built that wouldn't be built by default (because you want to use them from the RDBMS side). For more information, see REDEFINES Clause.

Recall that the key fields and the fields from the largest record are automatically included as explicit columns in the database table. All fields are stored and retrieved from the database, whether they appear as explicit columns or not. So you don't need to use WHEN unless you want to ensure that some additional fields are explicitly listed by name in the table. For details about how fields are included in the database table, see How XFDs Are Formed.

WHEN declares that the field (or subordinate fields, if it is a group item) that immediately follow the XFD directive must appear as a column (or columns) in the database table. It also states one condition under which the columns are to be used. The WHEN XFD directive guarantees that the fields are explicitly included in the table, as long as they aren't FILLER and don't occupy the same area as key fields.

The WHEN XFD directive results in a table that unites all fields subordinate to the WHEN XFD directive. This table might include more columns and could affect performance and storage requirements.

Syntax

$XFD WHEN field = value     (equals)
$XFD WHEN field <= value    (is less than or equals)
$XFD WHEN field < value     (is less than)
$XFD WHEN field >= value    (is greater than or equals)
$XFD WHEN field > value     (is greater than)
$XFD WHEN field != value    (is not equal to)
$XFD WHEN field = OTHER

The value may be an explicit data value (in quotes). The word "OTHER" can be used only with "=". It means use the following field(s) only if the WHEN condition(s) listed at the level of this field are not met.

Note: WHEN values must be on individual lines as single values in the code. WHEN values cannot be combined.

For example:

     03 ar-code-type                  pic x.
$xfd when ar-code-type = "s"
     03 ship-code-record              pic x(4).
$xfd when ar-code-type = "b"
     03 backorder-code-record redefines ship-code-record.
$xfd when ar-code-type = other
     03 obsolete-code-record redefines ship-code-record.

OTHER may be used before one record definition, and may be used once at each level within each record definition.

Note: A WHEN XFD directive with condition OTHER must be used if there is a possibility that the data in the field does not meet any of the explicit conditions specified in the other WHEN XFD directives. If this is not done, results are undefined.

Example 1

01 ar-codes-record.
  03 ar-codes-key.
    05 ar-code-type    pic x.
    05 ar-code-num     pic 999.
01 ship-code-record.
  03 filler            pic x(4).
  03 ship-instruct     pic x(15).
01 terms-code-record.
  03 filler            pic x(4).
  03 terms-rate-1      pic s9v999.
  03 terms-days-1      pic 9(3).
  03 terms-rate-2      pic s9v999.
  03 terms-descript    pic x(15).

If you add the WHEN XFD directive as shown below, it causes the fields from the SHIP-CODE-RECORD to be included in the database table, and determines when specific database columns are used. The underlined fields then appear as columns in the database table:

 01 ar-codes-record.
   03 ar-codes-key.
     05 ar-code-type      pic x.
     05 ar-code-num       pic 999.
$xfd when ar-code-type = "s"
 01 ship-code-record.
   03 filler              pic x(4).
   03 ship-instruct       pic x(15).
$xfd when ar-code-type = "t"
 01 terms-code-record.
   03 filler              pic x(4).
   03 terms-rate-1        pic s9v999.
   03 terms-days-1        pic 9(3)
   03 terms-rate-2        pic s9v999.
   03 terms-descript      pic x(15).

FILLER data items don't have unique names and are thus not used to form columns in the database table. You could use the NAME XFD directive to give them a name if you really need to see them in the database table.

Note: In this example, the FILLER data items implicitly redefine key fields. Thus, they would be disregarded even if you provided a name for them.

Example 2

In the following code, in which no WHEN XFD directives are used, the underlined fields are explicitly named in the database table. (Key fields have the suffix key in their names in this example.)

Note that REDEFINES records simply re-map the same data area and are not explicitly included in the database table by default:

01 archive-record.
  03 filler               pic x(33).
  03 archive-code         pic 9(6).
  03 archive-location     pic 9(2).
  03 filler               pic x(10).
01 master-record.
  03 animal-id-key.
    05 patient-id         pic 9(6).
    05 species-code-type  pic 9(5).
    05 species-name       pic x(6).
  03 service-code-key.
    05 service-code-type  pic 9(6).
    05 service-name       pic x(10).
  03 billing-code.
    05 billing-code-type  pic 9(4).
    05 plan-name          pic x(8).
  03 office-info.
    05 date-in-office     pic 9(8).
    05 served-by-name     pic x(10).
  03 remote-info redefines office-info.
    05 van-id             pic 9(4).
    05 proc-code          pic 9(8).
    05 vet-name           pic x(6).

If you add the WHEN XFD directives shown below, you add several columns to the database table. The fields which then appear in the table are underlined:

$xfd when animal-id-key = "00000000000000000"
 01 archive-record.
   03 filler              pic x(33).
   03 archive-code        pic 9(6).
   03 archive-location    pic 9(2).
   03 filler              pic x(10).
$xfd when animal-id-key = other
 01 master-record.
$xfd use group
   03 animal-id-key.
     05 patient-id        pic 9(6).
     05 species-code-type pic 9(5).
     05 species-name      pic x(6).
   03 service-code-key.
     05 service-code-type pic 9(6).
     05 service-name      pic x(10).
   03 billing-code.
     05 billing-code-type pic 9(4).
     05 plan-name         pic x(8).
$xfd when billing-code-type = "1440"
   03 office-info.
     05 date-in-office    pic 9(8).
     05 served-by-name    pic x(10).
$xfd when billing-code-type = other
   03 remote-info redefines office-info.
     05 van-id            pic 9(4).
     05 proc-code         pic 9(8).
     05 vet-name          pic x(6).

Example 3

If your application has a REDEFINES whose field names are more meaningful than the fields they redefine, you might consider switching the order of your code, rather than using a WHEN XFD directive. Use the less significant field names in the REDEFINES.

For example, you might change this:

   03 code-info.
     05 filler        pic 9(8).
     05 code-1        pic x(10).
   03 patient-info redefines code-info.
     05 patient-id    pic 9(4).
     05 service-code  pic 9(8).
     05 server-name   pic x(6).

to this:

   03 patient-info.
     05 patient-id    pic 9(4).
     05 service-code  pic 9(8).
     05 server-name   pic x(6).
   03 code-info redefines patient-info.
     05 filler        pic 9(8).
     05 code-1        pic x(10).

The fields that would appear in the database table by default are underlined above. This shows how the column names might become more meaningful when the order is reversed. Your application operates the same either way.

Note: When a WHEN XFD directive condition is met, COBOL record definitions or REDEFINES records that are subordinate to other WHEN XFD directives are not used. Database columns in rows that correspond to those records are set to the special database value NULL. This means that there is no value provided for those columns. NULL is not equivalent to zero, and it has special properties in the RDBMS. For example, you can select all rows for which a given column is NULL.

Example 4

This COBOL code:

01  col-type                         pic x.
 $xfd  when col-type = "a"
      03  def1                         pic x(2).
 $xfd  when col-type = "b"
      03  def2 redefines def1          pic 9(2).

results in this database table:

col_type | def1 | def2
a | Xx | <Null>
b | <Null> | 10
  • WHEN Complex Expressions
    Syntax and rules for using complex expressions with WHEN directives.
Parent topic: XFD Directives
Related information
How XFDs are Formed
NAME XFD directive
REDEFINES Clause


WHEN Complex Expressions

The XFD WHEN directive supports complex expressions such as those containing multiple AND and OR operations and parentheses to group sub-expressions for proper order of evaluation. For example:

$XFD WHEN (var1 = "abc" or var1 = "def") and \
$XFD      (var2 > "000" or var3 > "000") and \
$XFD       var4 != "xyzzy"

Here is a more complete example:

       fd idx1-file.
       01 idx1-record1.
          03 filler pic x(46).

       01 idx1-record2.
          03 idx1-key.
             05 idx1-num1  pic 9(3).
             05 idx1-num2  pic 9(3).
             05 idx1-num3  pic 9(3).
          03 filler pic x(37).

      $xfd when ((idx1-num1 = "000" or idx1-num1 = "009") and  \
      $xfd       (idx1-num2 = "000" or idx1-num2 = "009"))     \
      $xfd   tablename=one
       01 idx1-record3.
          03 filler pic x(6).
          03 rec3-name1  pic x(20).
      $xfd when idx1-num3 = "333" or idx1-num3 = "444", tablename=two
          03 rec3-name2  pic x(20).

      $xfd when idx1-num1 = "001" AND idx1-num2 = "001"
       01 idx1-record4.
          03 filler pic x(6).
          03 rec4-name1  pic x(30).
          03 rec4-name2  pic x(10).

      $xfd when idx1-num1 > "001" OR idx1-num2 > "001"
       01 idx1-record5.
          03 filler pic x(6).
          03 rec5-text  pic x(40).

WHEN Expression Rules

  1. The AND operation takes precedence over the OR operation. If sc1, sc2, and sc3 are simple conditions (as defined above) then the expression sc1 or sc2 and sc3 is interpreted as sc1 or (sc2 and sc3) meaning that the AND of sc2 and sc3 is performed first and the result of that operation is OR'ed with sc1. If you want the OR operation performed first then you must specify (sc1 or sc2) and sc3 using parentheses.
  2. Since expressions are likely to use parentheses it is strongly recommended that the "$XFD" syntax be used rather than the "*(( XFD" with trailing "))" syntax.
  3. Mismatched parentheses cause an error to be reported.
  4. The maximum number of simple conditions in one expression is 50. An error is reported if the maximum is exceeded.
  5. Only one WHEN expression directive is allowed on consecutive lines. For example, the following three lines cause a syntax error to be reported:
    $XFD WHEN var1 = "abc" or var2 = "def"
$XFD WHEN var3 = "xyz"
01  record-type-2.

    If what you meant was:

    $XFD WHEN (var1 = "abc" or var2 = "def") \
$XFD   and var3 = "xyz"
01  record-type-2.

    then you must code it that way (note the use of parentheses and the backslash XFD directive line continuation character).

    "The optional condition tablename at the end of a WHEN directive applies to all simple conditions within the expression and to the overall combined expression. For example:

    $XFD WHEN (var1 = "abc" or var1 = "def") and \
$XFD      (var2 = "123" or var2 = "456") \
$XFD      tablename = table-2
01  record-type-2.

    The condition tablename table-2 is applied to all four simple conditions and propagated to the two OR conditions and to the final AND condition of the expression.

Parent topic: WHEN XFD directive


XSL XFD directive

The XSL XFD directive allows you to include a stylesheet reference in the XML header.

Syntax

$XFD XSL=stylesheet

where stylesheet is an alphanumeric literal indicating the appropriate stylesheet. The Compiler includes the following line in all generated XML-style XFD files:

<?xml-stylesheet type="text/xsl" href="stylesheet"?>

For example:

$XFD XSL="myxsl.xsl"

generates this line:

<?xml-stylesheet type="text/xsl" href="myxsl.xsl"?>
Parent topic: XFD Directives


Compiler Directives Affecting XFD Creation

  • CREATEXFD XFD directive
Parent topic: Database Connectors Reference


CREATEXFD XFD directive

Use the CREATEXFD directive to tell the compiler to generate XFDs for the COBOL files contained in your program.

This directive has the following format:

Syntax

$SET CREATEXFD ( version=n, xfd-directory=pathname, file-type=all )

All of the arguments are optional, and can be given in any order.

version=n | Causes XFD files of type n to be used. Currently, "6" is the only valid value here. Any other value causes XFD files of the latest version to be created
xfd-directory=pathname | Causes the XFD files to be created in the directory named by pathname. Pathname must be a valid directory, either absolute or relative, and must be writable to the user
file-type=all | Causes XFD files for all file types to be created. Without this argument, the Compiler creates XFD files for only indexed files. If anything other than "all" is specified, the Compiler creates XFD files only for indexed files
Parent topic: Compiler Directives Affecting XFD Creation