Description of functions

Inserting in sets

BINILOAD can insert records it stores in the database in sets; in this case it is very important to distinguish between owner and member record types:

• Owner record type:
  In a CHAIN set, for example, BINILOAD automatically creates an empty set occurrence for each owner record stored.

• Member record type of one or more sets:
  You must specify with the INSERT statement (see section "Statements for BINILOAD") into which sets BINILOAD is to insert the records.

  This also applies to sets in which the record type has been defined as AUTOMATIC member.

Selecting the set occurrence

Before BINILOAD inserts member records into a set, it needs to select the set occurrence into which the member records are to be inserted. It does this by selecting the owner record using the following key values:

• the value of the CALC key (see "LOCATION MODE clause" in the "Design and Definition" manual)

• the value of the SEARCH key. Defined at record type level either as INDEX-SEARCH key or as CALC-SEARCH key (see "SEARCH KEY clause in the "Design and Definition" manual).

• the value of the database key. This method is always possible, since the database key value is a unique identifier within the database.

If the owner record is a member in a SYSTEM set, BINILOAD can select it using the following key values:

• the value of the ASC/DESC key (for a sorted SYSTEM set)

• the value of the SEARCH key. Defined at set level as INDEX-SEARCH key or as CALC-SEARCH key (see "Direct access" in the "Design and Definition" manual).

BINILOAD can be informed in two ways of the key of the owner record by means of which the set occurrence is to be selected:

• Firstly by specifying the position of the key in the records of the input file. The value of the key may then vary from record to record. Consequently BINILOAD inserts records with different key values in different set occurrences.

• Secondly by specifying the key as a literal in the OWNER statement of BINILOAD. In this case the key value is the same for all records to be stored. BINILOAD therefore inserts all records of the input file in a single set occurrence.

The following points must be taken into consideration when selecting a set occurrence:

• Duplicates
  If the set occurrence is selected by specifying the key value (CALC key, ASC/DESC key, SEARCH key) of the owner record type, and if there are several records with the same key value (DUPLICATES ARE ALLOWED), BINILOAD selects one owner record from the owner records having the same key value, without the user knowing which one will be chosen.

• MANUAL member
  If the records of the input file are to be inserted into a set in which the record type is defined as a MANUAL member, the DB administrator must specify whether or not the record is to be inserted by using the INSERT statement and identifying the input records for optional insertion in a BINILOAD run.

• Member record sequence
  If the set into which BINILOAD is to insert the records to be stored as member records is defined with
  ORDER IS FIRST/LAST/NEXT/PRIOR or IMMATERIAL, the DB administrator can specify with the SET-ORDER statement of BINILOAD whether BINILOAD should insert the records in the set occurrences

  • in ascending order, by the contents of one item of the input records, or

  • in the order in which they occur in the input file.

  In all other cases the sequence defined in the ORDER clause of the set description is used.

• Set occurrence not empty
  If the records to be stored are to be inserted as member records in a set occurrence in which member records have already been inserted, the structure of the existing set occurrence must be taken into consideration.

  This structure is determined by the following clauses:

  • the MODE clause (SSL) with POINTER-ARRAY, LIST, CHAIN

  • the ORDER clause (DDL) with FIRST, LAST, NEXT, PRIOR, IMMATERIAL, SORTED (can be used with CHAIN only), SORTED INDEXED.

  For DDL and SSL, see the "Design and Definition" manual.

  The following points must be observed:

  • If MODE IS CHAIN is specified with SORTED, SORTED INDEXED, an unfavorable chaining structure may arise;

  • If MODE IS LIST is specified, no further records can be inserted into an existing set occurrence by BINILOAD. This also applies for distributable lists.

  • If MODE IS LIST is specified, BINILOAD requires at least one page for level 0 of each set cccurrence.

Storing in the database

BINILOAD is designed to store large numbers of records in the database. Since BINILOAD does not use partially filled pages, memory space can be more efficiently used than when loading by means of an application program.

It can occur that input files, BINILOAD statements and Schema DDL and SSL specifications are incompatible. BINILOAD therefore offers a facility for checking the input file data before it is finally stored.

Specifying EXECUTION WITH CHECK causes the first phase of BINILOAD storage, the table creation phase, to be executed without actual alterations to the database. Any errors are detected and corresponding messages are output.

If no errors have occurred, BINILOAD repeats its run from the beginning and stores the input data in the database.

Specifying EXECUTION WITHOUT CHECK causes alterations to be written to the database immediately. If an error occurs, the BINILOAD run is aborted, error messages are output and the database is inconsistent.

• Interdependencies between Schema DDL and BINILOAD statements

  • DUPLICATES ARE NOT ALLOWED and presence of duplicates:

    If, despite specification of DUPLICATES ARE NOT ALLOWED, there are records in the input file that have the same key value, the following message is issued:

    DUPLICATE KEYS OR DBKEYS FOUND / REC REF‘S OR RSQ‘S OUT OF RANGE
SEE PRINTER OUTPUT

    In addition, up to the first 60 bytes of the key are output in the BINILOAD listing in character representation and up to the first 30 bytes of the key are output in hexadecimal form. The message is repeated for each key value found to be present more than once. After checking all the input records, BINILOAD terminates with the message:

    ABNORMAL END BINILOAD

    BINILOAD is only able to detect duplicates within the input file. It cannot identify duplication between input records and database records.

  • WITHIN clause and RECORD AREA statement of BINILOAD

    If more than one realm has been defined in the WITHIN clause of the DDL, the realm in which the records are to be stored must be specified in the RECORD AREA statement of BINILOAD.

• Interdependence of SSL and BINILOAD statements:

  • DATABASE-KEY-TRANSLATION-TABLE clause

    This clause specifies the size and location of the DBTT for a record type, and thereby determines the maximum number of records which may be stored in the database. Consequently the input file must not contain more records than there are free entries in the DBTT of the corresponding record type.

  • PLACEMENT OPTIMIZATION clause

    This clause packs the member records for the set contiguously in consecutive pages in accordance with the sort criterion. The member is not stored in the owner page.

  • SSL specifications with ATTACHED

    MODE IS POINTER-ARRAY ATTACHED

    MODE IS LIST ATTACHED

    INDEX NAME IS indexname PLACING IS ATTACHED

    Specifying ATTACHED in the SSL has no effect.
    BINILOAD stores pointer arrays, lists and indexes of all levels in empty database pages. These information elements are always stored DETACHED within the realm of the owner.

  • SSL specifications with DETACHED

    Specifying DETACHED WITHIN realm-name in the SSL causes the pointer arrays and indexes to be stored in the specified realm. The only exception here are the distributable lists. Here the table part (levels >0) and a possible indirect hash area are stored in realm-name.

  • POPULATION clause in the set entry of the SSL and the FILLING statement in BINILOAD

    The FILLING statement can be used to specify the occupancy level for table pages. In this way additional member records can be loaded by means of the Database Handler without direct extension and reorganization for these information elements. The storage space to be reserved for these table pages is determined exclusively by the occupancy level and not by the SET POPULATION clause in the SSL.

For the Schema DDL and SSL, see the "Design and Definition" manual.