# Compaq COBOL
DBMS Database Programming Manual

Previous | Contents | Index

Additional Reference

- Section 4.3, Scope of Names

Example

(The Technical Notes following this example explain execution of the USE procedures shown.)

    PROCEDURE DIVISION.
    DECLARATIVES.
    200-DATABASE-EXCEPTIONS SECTION.
        USE FOR DB-EXCEPTION ON OTHER.
    201-PROCEDURE.
        DISPLAY "DATABASE EXCEPTION CONDITION".
        PERFORM 250-DISPLAY-MNEMONIC.
    210-DATABASE-EXCEPTIONS SECTION.
        USE FOR DB-EXCEPTION ON DBM$_NOTIP.
    211-PROCEDURE.
        DISPLAY "DATABASE EXCEPTION CONDITION ON READY STATEMENT"
        PERFORM 250-DISPLAY-MNEMONIC.
    250-DISPLAY-MNEMONIC.

    *
    *   DBM$SIGNAL displays a diagnostic message based on the
    *   status code in DB-CONDITION.
    *
    *
        CALL "DBM$SIGNAL".

        STOP RUN.

    END DECLARATIVES.

Technical Notes

- If this program has an exception condition when executing a READY statement, or if the program does not ready the database before executing a DML statement, 210-DATABASE-EXCEPTIONS executes.
- If any other database exception condition occurs during program execution, 200-DATABASE-EXCEPTIONS executes.

## 4.9.1 RETAINING Clause

The RETAINING clause specifies which currency indicators are not updated during the execution of the COMMIT, CONNECT, FETCH, FIND, MODIFY, RECONNECT, and STORE statements.

> ### set-name

> is a set name which is available in the program's subschema.

You can prevent currency indicator updates for REALM, RECORD, or SET. Specifying REALM retains all realm currency indicators; specifying RECORD retains all record currency indicators.

If SET is used without the set-name list option, the Database Control System (DBCS) retains the currency indicators for all sets in the subschema. If you specify the set-name list option, only the currency indicators for the specified sets are retained. The same set-name cannot be listed more than once in a given set-name list.

If you specify the RETAINING clause and do not select an option, the DBCS retains all REALM, RECORD, and SET currency indicators.

# Chapter 5
Database Programming with Compaq COBOL

With Compaq COBOL database programming you can access data without designing separate files for specific applications. You accomplish this with the database management system (Oracle CODASYL DBMS) and the COBOL data manipulation language (DML). This chapter is a resource for information on:

- Compaq COBOL database program development
- Compaq COBOL database concepts
- Compaq COBOL programming tips and techniques
- Debugging and testing Compaq COBOL database programs

Database programmers and readers unfamiliar with Oracle CODASYL DBMS concepts and definitions should run the online self-paced demonstration package (see Section 5.1) as a prerequisite to this chapter. The demonstration package lets you test Oracle CODASYL DBMS features and concepts as you learn them. Additional useful information can be found in:

- Introduction to Oracle CODASYL DBMS
- Oracle CODASYL DBMS Database Administration Reference Manual

# 5.1 The Self-Paced Demonstration Package

To help you learn how to use a database, Compaq has provided you with a database called PARTS. PARTS is an online self-paced demonstration database configured to show some of the features of Oracle CODASYL DBMS. You create the PARTS database as part of the demonstration package. Examples in this chapter refer to either the PARTSS1 or PARTSS3 subschema in the PARTS database. A complete listing of the PARTS schema, including the PARTSS1 and PARTSS3 subschemas, can be found in the Oracle CODASYL DBMS documentation on data manipulation.

Before beginning the demonstration, you should do the following:

1. Create your own node in Oracle CDD/Repository using the Dictionary Management Utility (DMU). (Refer to the Oracle CDD/Repository documentation for more information.)

       $ RUN SYS$SYSTEM:DMU[RET]
       DMU> CREATE nodename[RET]
       DMU> SHOW DEFAULT[RET]
           defaultname
       DMU> EXIT[RET]
       $

   where:

   nodename | names the new node in the CDD to contain your personal PARTS database.
   defaultname | is your CDD default.

   For example:

       $ RUN SYS$SYSTEM:DMU[RET]
       DMU> CREATE DEMONODE[RET]
       DMU> SHOW DEFAULT[RET]
           CDD$TOP
       DMU> EXIT[RET]
       $

2. Define CDD$DEFAULT using the defaultname, a period, and the nodename from step 1. For example:

To run the demonstration package, type the following:

    $ @SYS$COMMON:[SYSTEST.DBM]DBMDEMO [RET]

You must run the entire demonstration to create and load the PARTS database. If you have already created the PARTS database but are unsure of or have changed its contents, you can reload it by running option 11 of the self-paced demonstration package. The demonstration package creates the NEW.ROO database instance. If you have any problems with the demonstration package, see your system manager or database administrator.

# 5.2 Concepts and Definitions

Some of the important concepts in database programming are described in the definitions of databases, schemas, and streams.

## 5.2.1 Database

A database is a collection of your organization's data gathered into a number of logically related units. The database administrator (DBA) and representatives from user departments decide on the organization's informational needs. After these individuals agree on the contents of the database, the DBA assumes responsibility for designing, creating, and maintaining the database.

## 5.2.2 Schema

The schema is a program written by the DBA using DDL statements. It describes the logical structure of the database, defining all record types, set types, areas, and data items in the database. The DBA writes the schema independently of any application run unit. Only one schema can exist for a database. For a more detailed description of the schema DDL, refer to the Oracle CODASYL DBMS documentation on database administration and design.

## 5.2.3 Storage Schema

The storage schema describes the physical structure of the database. It is written by the DBA using data storage description language (DSDL) statements. For a complete description of the storage schema, refer to the Oracle CODASYL DBMS documentation on database administration and design.

## 5.2.4 Subschema

The subschema is a subset of the schema; it is your run unit's view of the database. The DBA uses the subschema DDL to write a subschema, defining only those areas, set types, record types, and data items needed by one or more run units. You specify a subschema to be used by your run unit with the DB statement. A subschema contains data description entries like the record description entries you use for file processing. However, subschema data description entries are not compatible with COBOL data description entries; the Compaq COBOL compiler must translate them. The translated entries are made available to the COBOL program at compile time. By using the /MAP compiler qualifier, you obtain a database map showing the translated entries as part of your program listing.

Many subschemas can exist for a database. For further information on writing a subschema, refer to the Oracle CODASYL DBMS Database Administration Reference Manual.

## 5.2.5 Stream

A stream is an independent access channel between a run unit and a database. A stream has its own keeplists, locks, and currency indicators. You specify a stream to be used by your run unit with the DB statement. Streams let you do the following:

- Access multiple subschemas within the same database
- Access multiple databases

Because streams can lock against one another, it is possible to deadlock within a single process.

In Compaq COBOL, you can only specify one stream per separately compiled program. To access multiple subschemas within the same database or multiple databases, you must use multiple separately compiled programs and execute calls between the programs. For example, to gain multiple access to the databases OLD.ROO and NEW.ROO, you could set up a run unit as follows:

    IDENTIFICATION DIVISION.
    PROGRAM-ID.  MULTI-STREAM-1.
    DATA DIVISION.
    SUB-SCHEMA SECTION.
    DB PARTS1 WITHIN PARTS FOR "NEW.ROO" THRU STREAM-1.
        .
        .
        .
        CALL MULTI-STREAM-2
        .
        .
        .

    END PROGRAM MULTI-STREAM-1.
    IDENTIFICATION DIVISION.
    PROGRAM-ID.  MULTI-STREAM-2.
    DATA DIVISION.
    SUB-SCHEMA SECTION.
    DB DEFAULT_SUBSCHEMA WITHIN PARTS FOR "NEW.ROO" THRU STREAM-2.
        .
        .
        .
        CALL MULTI-STREAM-3.
        EXIT PROGRAM.
    IDENTIFICATION DIVISION.
    PROGRAM-ID.  MULTI-STREAM-3.
    DATA DIVISION.
    SUB-SCHEMA SECTION.
    DB OLDPARTS1 WITHIN OLDPARTS FOR "OLD.ROO" THRU "STREAM-3".
        .
        .
        .
        EXIT PROGRAM.

In this run unit, the main program (MULTI-STREAM-1) accesses the database NEW.ROO through STREAM-1 and performs a call to a subprogram. The subprogram (MULTI-STREAM-2) accesses another subschema to the database NEW.ROO through STREAM-2 and calls another subprogram. This subprogram (MULTI-STREAM-3) accesses a second database (OLD.ROO) through STREAM-3.

STREAM-1, STREAM-2, and STREAM-3 are stream names. Stream names assign a character string name to the database/subschema combination you specify in your DB statement. For more information, refer to the Compaq COBOL Reference Manual and the Oracle CODASYL DBMS documentation.

# 5.3 Using Oracle CDD/Repository

Oracle CODASYL schemas, storage schemas, and subschemas are stored in Oracle CDD/Repository. Oracle CDD/Repository separates data descriptions from actual data values that reside in VMS files. (For more information, refer to the Oracle CODASYL DBMS documentation on Common Data Dictionary Utilities and the Oracle CDD/Repository documentation.) Because of this separation, Compaq COBOL DML programs can be written independently of data. In addition, several subschemas can describe the same data according to their particular needs. This eliminates the need for redundant data and ensures data integrity.

At compile time, the COBOL DB statement, in effect, references Oracle CDD/Repository to obtain the data descriptions of a specific subschema. It is not until run time that the COBOL program has access to the database data values.

# 5.4 Database Records

A database record, like a record in a file, is a named collection of elementary database data items. Records appear in the database as record occurrences. Oracle CODASYL DBMS records are linked into sets.

In Compaq COBOL database applications, you do not describe database records in the COBOL program. Rather, you must use the DB statement to extract and translate subschema record definitions into your COBOL program as COBOL record definitions.

Each record description entry defined by the DBA in the schema describes one record type (see Section 5.7). For example, in Figure 5-7, PART is one record type and SUPPLY is another record type. Any number of records can be stored in a database.

In Oracle CODASYL DBMS, records are also called record occurrences. Figure 5-6 shows one occurrence of PART record type and two occurrences of SUPPLY record type.

The subschema describes records that you can access in your program. Note that subschema record descriptions might define only a portion of a schema record. For example, if a schema record description is 200 characters long, a corresponding subschema record description could be less than 200 characters long and use different data types.

Individual database records are locked by the DBCS as they are retrieved by the run unit, and the degree of locking depends on the specific DML command used. For more information, see Section 6.1.1.

# 5.5 Database Data Item

A database data item is the smallest unit of named data. Data items occur in the database as data values. These values can be character strings or any of several numeric data types.

# 5.6 Database Key

A database key (dbkey) identifies a record in the database. The value of the database key is the storage address of the database record. You can use this key to refer to the record pointed to by a currency indicator or an entry in a keeplist. For example, KEEP, FIND ALL, and FREE statements store and release these values from a keeplist you define in the subschema section.

# 5.7 Record Types

Records are grouped according to common features into record types. The database administrator (DBA) describes record types in the schema; record occurrences exist in the database. For example, a record that contains a specific part name, weight, and cost is a record occurrence. The PART record type, describing the structure of all occurrences of part records, would be defined in the schema. The unqualified term record implies record occurrence.

# 5.8 Set Types

A set type is a named relationship between two or more record types. The major characteristic of a set type is a relationship that relates one or more member records to an owner record. The owner and members of a set are called tenants of the set. For example, the PART record type could own a SUPPLIER record type in the set PART_INFO.

As with records, the DBA describes set types in the schema; set occurrences exist in the database. The unqualified term set implies set occurrence. A set occurrence is the actual data in the set, not its definition, which is the set type. Figure 5-1 illustrates a set relationship using a Bachman diagram.

Figure 5-1 Bachman Diagram

A Bachman diagram shows how member records are linked with owner records by arrows that point toward the members. It is a graphic representation of the set relationships between owner and member records used to analyze and document a database design. This simple format can be extended to describe many complex set relationships. The Oracle CODASYL DBMS documentation on data manipulation contains a complete Bachman diagram of the PARTS database.

Most of the examples in this chapter use the set types in the PARTSS1 and PARTSS3 subschemas (see the subschema compiler listings in Section 7.3, and the Bachman diagrams in this chapter, Figure 5-2 and Figure 5_3). Figure 5_4 and Figure 5-5 contain three PART records, two VENDOR records, and six SUPPLY records. The SUPPLY records show suppliers' lag times. Lag time starts when an item is ordered and ends when the item is received.

The examples assume the records are in the following order:

1. PART record type: LABEL, CASSETTE, TAPE
2. SUPPLY record type: 4-DAYS, 2-DAYS, 1-MONTH, 1-WEEK, 2-WEEKS, 5-DAYS
3. VENDOR record type: MUSICO INC., SOUND-OFF CO.

Note All occurrence diagrams display member records within a set in counterclockwise order.

Figure 5-2 Partial Bachman Diagram of the PARTSS1 Subschema

Figure 5-3 Bachman Diagram of the PARTSS3 Subschema

Figure 5-4 Sample Occurrence Diagram 1

Figure 5-5 Sample Occurrence Diagram 2

# 5.9 Sets

Sets are the basic structural units of a database. A set occurrence has one owner record occurrence and zero, one, or several member record occurrences. Figure 5-6 shows one occurrence of PART_SUPPLY set where PART A owner record occurrence owns two SUPPLY member record occurrences.

Set types establish a logical relationship between two or more types of records. A subschema usually includes one or more set types. Each set type has one record type that participates as the owner record and one or more record types that participate as members. These owner and member records are grouped into set occurrences.

Figure 5-6 One Occurrence of Set PART_SUPPLY

The DBA can specify a set type where each PART record occurrence can own SUPPLY record occurrences. Figure 5-7 is a Bachman diagram that shows the relationship between PART record types and SUPPLY record types. Bachman diagrams give you a picture of the schema or a portion of the schema. Each record type is enclosed in a box. Each set type is represented by an arrow pointing from the owner record type to the member record type or types. Thus, in Figure 5-7, PART is the owner record type of the PART_SUPPLY set type, and SUPPLY is the member record type.

Figure 5-7 Set Relationship

You can have many set relationships in a subschema. Figure 5-8 shows a set relationship where vendor records are also owners of supply records. You would use this relationship when many parts are supplied by one vendor, and many vendors supply one part. For example, Figure 5-9 shows a gasket supplied by three vendors. The supply records show the minimum quantity each vendor is willing to ship.

Figure 5-8 Set Relationships

Figure 5-9 Occurrence Diagram of a Relationship Between Two Set Types

## 5.9.1 Simple Set Relationships

A simple set relationship contains one owner record type and one or more member record types. Simple relationships are used to represent a basic one-to-many relationship where one owner record occurrence owns zero, one, or several member record occurrences. Simple relationships are created with a single set type. There are three kinds of sets in simple relationships: system-owned sets, simple sets, and forked sets.

### 5.9.1.1 System-Owned Sets

By definition, a set contains one owner record and may contain zero or more member records. Sets owned by the system, however, have only one occurrence in the database and are called system-owned sets. System-owned sets are used as entry points into the database. You cannot access the owner of a system-owned set (the system), but you can access its member records. System-owned sets are also called singular sets. Figure 5-10 is an example of a system-owned set type.

Figure 5-10 Bachman Diagram of a System-Owned Set Type

### 5.9.1.2 Simple Sets

In simple sets, each set contains only one type of member record. Figure 5-11 is a Bachman diagram of a simple set type where similar parts are grouped by class code. For example, plastic parts could be member records owned by a class record with a class code PL.

Figure 5-11 Bachman Diagram of a Simple Set Type

Example 5-1 prints a listing of all parts with a class code of PL.

Example 5-1 Printing a Listing of a Simple Set

    PROCEDURE DIVISION.
       .
       .
       .
    100-GET-PLASTICS-CLASS.
        MOVE "PL" TO CLASS_CODE
        FIND FIRST CLASS USING CLASS_CODE.
    200-GET-PLASTICS-PARTS.
        FETCH NEXT PART WITHIN CLASS_PART
          AT END GO TO 900-DONE-PLASTIC-PARTS.
       *****************************************************
       *   Plastic parts print routine.
       *****************************************************
          GO TO 200-GET-PLASTICS-PARTS.

### 5.9.1.3 Forked Sets

A forked set has one owner record type and members of two or more different member record types. In most forked sets, the member record types have common data characteristics. One such example is the set type PART_INFO in Figure 5-12, where member record types SUPPLY and PR_QUOTE both contain information about parts.

Figure 5-12 Bachman Diagram of a Forked Set Type

One advantage of a forked set type is the ability to connect many different record types to one set type. Another advantage is that owner records need only one set of pointers to access more than one member record type. Example 5-2 uses the forked set type shown in Figure 5_12 and the forked set occurrence in Figure 5-13 to perform a part analysis.

Example 5-2 Using Forked Sets

    PROCEDURE DIVISION.
        .
        .
        .
    100-GET-PART.
        DISPLAY "TYPE PART ID".
        ACCEPT PART_ID.
        IF PART_ID = "DONE"
           GO TO 900-DONE-PART-INQUIRY.
        FETCH FIRST PART USING PART_ID
           ON ERROR
           DISPLAY "PART " PART_ID " NOT IN DATABASE"
           GO TO 100-GET-PART.
    200-GET-SUPPLY-INFO.
        FETCH NEXT SUPPLY WITHIN PART_INFO
           AT END
           FETCH OWNER WITHIN PART_INFO
           GO TO 300-GET-QUOTE-INFO.
     *****************************************************
     * The FETCH OWNER statement resets currency to      *
     * point to the owner. This allows the search for    *
     * PR_QUOTE records to begin with the first member  *
     * record occurrence rather than after the           *
     * last SUPPLY record occurrence.                    *
     *****************************************************
        PERFORM 500-SUPPLY-ANALYSIS.
        GO TO 200-GET-SUPPLY-INFO.
    300-GET-QUOTE-INFO.
        FETCH NEXT PR_QUOTE WITHIN PART_INFO
           AT END
           GO TO 100-GET-PART.
        PERFORM 600-QUOTE-ANALYSIS.
        GO TO 300-GET-QUOTE-INFO.

Figure 5-13 is an occurrence diagram of a forked set. The figure shows a part record owning five PART_INFO member records.

Figure 5-13 Forked Set Occurrence

Previous | Next | Contents | Index