Example 2 (DataStage®)

This example shows a more complex structure that can be derived by using the Top Level Keys Property.

This can be set to True to indicate that key columns should be left as top level columns and not included in the subrecord. This example assumes that the job is running sequentially. The same column definitions are used, except both col1 and keycol are defined as keys.
Table 1. Column definitions
Column name Key SQL type
keycol Yes Char
col1 Yes TinyInt
col2   Time
col3   Dat
The same input data set is used:
Table 2. Input data set
  col1 col2 col3 col4
row 1 00:11:01 1960-01-02 A
row 3 08:45:54 1946-09-15 A
row 1 12:59:00 1955-12-22 B
row 2 07:33:04 1950-03-10 B
row 2 12:00:00 1967-02-06 B
row 2 07:37:04 1950-03-10 B
row 3 07:56:03 1977-04-14 B
row 3 09:58:02 1960-05-18 B
row 1 11:43:02 1980-06-03 C
row 2 01:30:01 1985-07-07 C
row 2 11:30:01 1985-07-07 C
row 3 10:28:02 1992-11-23 C
row 3 12:27:00 1929-08-11 C
row 3 06:33:03 1999-10-19 C
row 3 11:18:22 1992-11-23 C
The Output column definitions have two separate columns that are defined for the keys, as well as the column carrying the subrecords:
Table 3. Output column definitions
Level number Column name Key SQL type
  subreccol Char  
02 keycol Char Yes
02 col1 TinyInt Yes
02 col2 Time  
02 col3 Date  
The Output data set will be:
Table 4. Output data set
  Keycol col1 subreccol
      vector index col2 col3
row A 1 0 00:11:01 1960-01-02
row A 3 0 08:45:54 1946-09-15
row B 1 0 12:59:00 1955-12-22
row B 2 0 07:33:04 1950-03-10
      1 12:00:00 1967-02-06
      2 07:37:04 1950-03-10
row B 3 0 07:56:03 1977-04-14
      1 09:58:02 1960-05-18
row C 1 0 11:43:02 1980-06-03
row C 2 0 01:30:01 1985-07-07
      1 11:30:01 1985-07-07
row C 3 0 10:28:02 1992-11-23
      1 12:27:00 1929-08-11
      2 06:33:03 1999-10-19
      3 11:18:22 1992-11-23