WebSphere logo Classic Federation Server for z/OS, Version 9.1
WebSphere logo Classic Replication Server for z/OS, Version 9.1
WebSphere logo Classic Data Event Publisher for z/OS, Version 9.1
WebSphere logo Data Integration Classic Connector for z/OS, Version 9.1


Record arrays

A group of data items in a database that have multiple occurrences within a single record in the database are referred to as record arrays.

Typically, you map tables and views by importing data definitions in COBOL copybooks or PL/I include files. The copybooks or include files can contain array definitions. Classic Data Architect maps array definitions that you have not flattened into columns by converting the definitions to statements within BEGINLEVEL and ENDLEVEL blocks. The application then generates the Data Definition Language (DDL) that contains the BEGINLEVEL statements. You run the DDL on the data server to create a user table.

You can optimize performance when you query array data. If you want to insert, update, delete, or perform change capture on array data, you must flatten the structure. See the related array topics for information about working with array data.

For information about working with COBOL, see the Enterprise COBOL for z/OS Language Reference and the Enterprise COBOL for z/OS Programming Guide. For information about working with PL/I, see the WebSphere Developer for System z PL/I for Windows Language Reference and the Enterprise PL/I for z/OS Programming Guide.

Fixed-length arrays

Fixed-length array constructs define an array in which the number of instances does not change. For example, an employee record can include the employee’s dependent information (spouse and children). Because an employee can have multiple dependents, you can declare an array of dependent information within an employee record by specifying a COBOL OCCURS clause or a PL/I DIMENSION (DIM) attribute. The following example of a COBOL OCCURS clause defines a fixed-length array.
    05 DEPENDENTS-ARRAY OCCURS 20 TIMES

The array appears 20 times in the source database record, regardless of how many dependents the employee has.

NULL IS processing

The query processor on the data server skips null array instances as SQL ROW candidates at runtime. In the example of the dependents array, if an employee has three dependents and the array occurs 20 times, 17 null instances of the array do not appear as a row in a result set.

You can include a NULL IS value in DDL to identify a given array instance as null, based on a comparison value. A comparison value can identify a null array instance in the following ways:

  • The start of the first column in the array instance matches the comparison value (NULL IS null-value).
  • Each character in the array instance matches a single-character comparison value (NULL IS ALL null-value).
  • The start of a specified column in the array matches the comparison value (NULL IS column-name EQUAL null-value).

In the following example of NULL IS ALL DDL, the single character X is the comparison value. If each character in the array instance is X, that instance of the array is null.

    MAXOCCURS 20 NULL IS ALL X

Null instances of a record array are not returned as a row in the result set unless ALL instances of the array are NULL. If all instances of the array are NULL, then Classic Federation returns a single row for the non-array information in the record and sets the array data items to NULL. In the dependents array example, the employee has no dependents.

Variable-length arrays

Another common record array construct defines variable-length data. The number of array instances is dependent on the value of a data item that precedes the array in the structure, such as NUMBER-OF-DEPENDENTS. In COBOL, the array declaration is as follows:
    05 NUMBER-OF-DEPENDENTS PIC 9(4) COMP.
    05 DEPENDENTS-ARRAY OCCURS 1 TO 20 TIMES
       DEPENDING ON NUMBER-OF-DEPENDENTS.
The next example shows a similar array declaration in PL/I that uses the REFER attribute to point to the variable containing the value for the number of dependents.
     5 NUMBER_OF_DEPENDENTS BIN FIXED(15),
     5 DEPENDENTS_ARRAY DIM(N1 REFER(NUMBER_OF_DEPENDENTS)),

PL/I has no equivalent syntax for the COBOL DEPENDING ON clause, so the data server calculates the number of array instances automatically based upon array offset, array size, and record or segment length. The formula is as follows:

<Number-of-array-instances> = (<record-length> - 1 - <array-offset>)/<array-size>

The number of instances does not appear in the DDL generated by the wizard. The number is calculated when the data server processes the DDL and validates the table information prior to creating the table definition in the catalog.

Restrictions

You cannot map a table that is based on nested variable-length arrays or a table that contains fixed columns after a variable-length array. The reason for this is that columns subsequent to variable-length array constructs do not appear at a predictable offset.

The New Table wizard supports PL/I REFER constructs, with the following restrictions:

  • The REFER clause must occur in the first dimension of a multi-dimensional array.
  • The array must have no later siblings, and no parents with siblings.
  • The REFER clause must be in the upper bound, and the lower bound must be 1.

    The upper bound must reflect the actual count, and contains the actual count only when the lower bound is 1.

If the PL/I parser encounters a REFER clause that is too complex to process, the parser ignores the entire structure and the validation process displays an error related to the array definition.

Related concepts
Performance considerations with multiple record arrays
Record array definitions for federation and change capture
Related reference
Array definition examples
Related information
Mapping data for Classic federation
Mapping data for change capture


Feedback

Update icon Last updated: 2007-10-09