0 / 0

Example 1 (DataStage®)

Last updated: Mar 12, 2025
Combine Records stage in DataStage: example 1

This example assumes that the job is running sequentially.

The column definitions for the input data set are as follows:

Table 1. Column definitions
Column name Key SQL type
keycol Yes Char
col1   TinyInt
col2   Time
col3   Dat
The following are some rows from the input data set:
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
Once combined by the stage, each group of rows will be output in a single column called suprecol. This contains the keycoll, col1, col2, and col3 columns. (If you do not take advantage of the runtime column propagation feature, you would have to set up the subrecord by using the Edit Column Meta Data dialog box to set a level number for each of the columns the subrecord column contains.)
Table 3. Output metadata
Level number Column name SQL type Key
  subreccol Char  
02 keycol Char Yes
02 col1 TinyInt  
02 col2 Time  
02 col3 Date  
Table 4. Output data set
    subreccol
  vector index col1 col2 col3 Keycol
row 0 1 00:11:01 1960-01-02 A
  1 3 08:45:54 1946-09-15 A
row 0 1 12:59:00 1955-12-22 B
  1 2 07:33:04 1950-03-10 B
  2 2 12:00:00 1967-02-06 B
  3 2 07:37:04 1950-03-10 B
  4 3 07:56:03 1977-04-14 B
  5 3 09:58:02 1960-05-18 B
row 0 1 11:43:02 1980-06-03 C
  1 2 01:30:01 1985-07-07 C
  2 2 11:30:01 1985-07-07 C
  3 3 10:28:02 1992-11-23 C
  4 3 12:27:00 1929-08-11 C
  5 3 06:33:03 1999-10-19 C
  6 3 11:18:22 1992-11-23 C