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.
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:
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.
05 NUMBER-OF-DEPENDENTS PIC 9(4) COMP. 05 DEPENDENTS-ARRAY OCCURS 1 TO 20 TIMES DEPENDING ON 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 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.