Make Subrecord stage in DataStage: Examples

In this example, the Make Subrecord stage extracts data from four input columns, three of which carry vectors. The data is output in two columns, one carrying the vectors in a subrecord, and the nonvector column that is passed through the stage. The example assumes that the job is running sequentially. The following table shows column definitions for the input data set:

Table 1. Column definitions
Column name	SQL type
key	Char
acol	Integer
bcol	Char
ccol	Char

.

The following table shows the rows from the input data set (superscripts represents the vector index):

Table 2. Input data set
	Key	acol	bcol	ccol
row	A	12⁰13¹4²64³	Wills⁰wombat¹bill²william	D⁰0 ¹
row	B	22⁰6¹4²21³	Robin⁰Dally¹Rob²RD³	G⁰A¹
row	C	76⁰0¹52²2³	Beth⁰Betany¹Bethany²Bets³	B⁰7¹
row	D	4⁰6¹81²0³	Heathcliff⁰HC¹Hchop²Horror³	A⁰1¹
row	E	2⁰4¹6²8³	Chaz⁰Swot¹Chazlet²Twerp³	C⁰H¹
row	F	18⁰8¹5²8³	kayser⁰Cuddles¹KB²Ibn Kayeed³	M⁰1¹
row	G	12⁰10¹6²1³	Jayne⁰Jane¹J²JD³	F⁰2¹
row	H	12⁰0¹6²43³	Ann⁰Anne¹AK²AJK³	H⁰E¹
row	I	3⁰0¹7²82³	Kath⁰Cath¹Catherine²Katy³	⁰H¹
row	J	5⁰0¹7²02³	upert⁰Rupe¹Woopert²puss³	B⁰C¹

The stage outputs the subrecord that it builds from the input data in a single column called parent. The column that is called key will be output separately. The output column definitions are as follows:

Table 3. Output column definitions
Column name	SQL type
key	Char
parent	Char

Table 4. Output data set
	Key	Parent
		0	1	2	3
row	A	12	13	4	64
		Will	wombat	bill	William
		D	0	pad	pad
	B	22	6	4	21
		Robin	Dally	Rob	RD
		G	A	pad	pad
	C	76	0	52	2
		Beth	Betany	Bethany	Bets
		B	7	pad	pad
	D	4	6	81	0
		Heathcliff	HC	Hchop	Horror
		A	1	pad	pad
	E	2	4	6	8
		Chaz	Swot	Chazlet	Twerp
		C	H	pad	pad
	F	18	8	5	8
		Kayser	Cuddles	KB	Ibn Kayeed
		M	1	pad	pad
	G	12	10	6	43
		Jayne	Jane	J	JD
		F	2	pad	pad
	H	12	0	6	43
		Ann	Anne	AK	AJK
		H	E	pad	pad
	I	3	0	7	82
		Kath	Cath	Catherine	Katy
		C	H	pad	pad
	J	5	0	7	02
		Rupert	Rupe	Woopert	puss
		B	C	pad	pad

Examples (DataStage®)