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.

For example, a record could be defined for an employee to include the employee’s dependent information (spouse and children) in the record. Because an employee can have multiple dependents, you can declare an array of dependent information within the actual employee record by specifying a COBOL OCCURS clause. In the following example, an array of exactly 20 dependents is declared in the record.
01  EMPLOYEE-RECORD.
05 EMP-LAST-NAME               PIC X(20).
05 EMP-FIRST-NAME              PIC X(20).
05 EMPSSN                                      PIC 9(9).
......
05 DEPENDENTS-ARRAY OCCURS 20 TIMES 
10 DEP-SSN                   PIC 9(9).
10 DEP-NAME                  PIC X(20).
10 DEP-DOB                   PIC 9(6).
10 DEP-RELATIONSHIP-TO-EMPL  PIC X.
Another common record array construct defines variably recurring data. In this example, the record contains up to 20 occurrences of the array data. The actual number of occurrences is dependent on the value of some other data item. In the following examples, SSN refers to Social Security Number, a national identifier used in the United States.
01 EMPLOYEE-RECORD.
05 EMP-LAST-NAME PIC X(20).
05 EMP-FIRST-NAME PIC X(20).
05 EMP-SSN PIC 9(9).
.......
05 NUMBER-OF-DEPENDENTS PIC 9(4) COMP.
05 DEPENDENTS-ARRAY OCCURS 1 TO 20 TIMES
   DEPENDING ON NUMBER-OF-DEPENDENTS.
10 DEP-SSN PIC 9(9).
10 DEP-NAME PIC X(20).
10 DEP-DOB PIC 9(6).
10 DEP-GENDER PIC X.
.......
The following example of metadata grammar maps a subset of the data items in the preceding COBOL copybook.
CREATE TABLE CAC.EMPL .....
(
  EMP_SSN SOURCE DEFINITION
   DATAMAP OFFSET 40 LENGTH 9 DATATYPE C 
   USE AS CHAR(9),
  NUMBER_OF_DEPENDENTS SOURCE DEFINITION
   DATAMAP OFFSET 49 LENGTH 2 DATATYPE H 
   USE AS SMALLINT,  
 BEGINLEVEL 1 OFFSET 51 LENGTH 36 OCCURS 20
   DEPENDING ON COLUMN NUMBER_OF_DEPENDENTS,
  DEP_SSN SOURCE DEFINITION
   DATAMAP OFFSET 0 LENGTH 9 DATATYPE C
   USE AS CHAR(9),
  DEP_NAME SOURCE DEFINITION
   DATAMAP OFFSET 9 LENGTH 20 DATATYPE C
   USE AS CHAR(20),
 ENDLEVEL 1
)

The example only maps the employee’s social security number field, the COBOL DEPENDING ON variable, the dependent social security number field, and the dependent name field. The items in the OCCURS clause DEPENDENTS-ARRAY are contained within a BEGINLEVEL, ENDLEVEL block of statements. The block of statements identifies a group of data items that repeat. When converting this mapping from a COBOL record to SQL columns, Classic Federation combines each occurrence of the DEPENDENT-ARRAY with the non-array data items to create SQL rows. In this case, each occurrence of the array data items DEP-SSN and DEP-NAME is combined with the non-array data items EMP-SSN and NUMBER-OF-DEPENDENTS.

For example, if the record in the database for the employee social security number '123456789' contains three dependents, three distinct rows are returned for that particular record.

The following query returns the following result set:

Query:

SELECT EMP_SSN, NUMBER_OF_DEPENDENTS, DEP_SSN, DEP_NAME FROM 
CAC.EMPL WHERE EMP_SSN = '123456789';

Result set:

EMP_SSN   NUMBER_OF_DEPENDENTS  DEP_SSN   DEP_NAME
123456789        3              111223333 Depen1
123456789        3              222334444 Depen2
123456789        3              333445555 Depen3

Restrictions

Classic Federation does not support nested OCCURS DEPENDING ON definitions. You cannot define a table that contains fixed columns after an OCCURS DEPENDING ON construct.

Record arrays that contain a fixed number of occurrences can include a NULL IS definition. These array occurrences are skipped as SQL ROW candidates at runtime. The NULL IS definition identifies a comparison value for the array itself or a column in the array that identifies an instance of the array as NULL. 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.

Related concepts
Occurs processing
Multiple record arrays in a single COBOL copybook


Feedback

Update icon Last updated: 2006-12-15