IBM Infosphere Datastage – Change Data Capture, Difference and Compare stages

 

 

Table 1.
bcol0	bcol1	bcol2	bcol3	bcol4
0	0	0	0	a
1	7	1	1	b
2	2	2	2	c
3	3	3	3	d
4	5	4	4	e
5	2	5	5	f
6	6	6	6	g
7	7	7	7	h
8	8	8	8	i
9	9	9	9	j

 

 

Table 2.
bcol0	bcol1	bcol2	bcol3	bcol4
0	0	0	0	a
1	1	1	1	b
2	2	2	2	C
3	3	3	3	D
4	4	4	4	E
5	5	5	5	F
6	6	6	6	G
7	7	7	7	H
8	8	8	8	I
9	9	9	9	J

This is the data set output by the Change Capture stage (bcol4 is the key column, bcol1 the value column):

Table 3. Change data set
bcol0	bcol1	bcol2	bcol3	bcol4	change_code
1	1	1	1	B	3
4	4	4	4	E	3
5	5	5	5	F	3

 

 

This is the data set output by the Difference stage (Key is the key column, All non-key columns are values are set True, all other settings take the default):

Table 3. Output data set
acol0	acol1	acol2	acol3	key	diff
0	0	0	0	a	2
1	1	1	1	b	3
2	2	2	2	c	2
3	3	3	3	d	2
4	4	4	4	e	3
5	5	5	5	f	3
6	6	6	6	g	2
7	7	7	7	h	2
8	8	8	8	i	2
9	9	9	9	j	2

 

The stage compares on the Key columns bcol1 and bcol4. This is the output data set by compare stage:

Table 3. Output data set
Result	First					Second
	bcol0	bcol1	bcol2	bcol3	bcol4	bcol0	bcol1	bcol2	bcol3	bcol4
0	0	0	0	0	a	0	0	0	0	a
2	1	7	1	1	b	1	1	1	1	b
0	2	2	2	2	c	2	2	2	2	c
0	3	3	3	3	d	3	3	3	3	d
2	4	5	4	4	e	4	4	4	4	e
-1	5	2	5	5	f	5	5	5	5	f
0	6	6	6	6	g	6	6	6	6	g
0	7	7	7	7	h	7	7	7	7	h
0	8	8	8	8	i	8	8	8	8	i
0	9	9	9	9	j	9	9	9	9	j

 

The operation code by Change data capture Stage are

Copy – 0  (By default copy i.e. no change, records are not output by change data capture stage)

Insert – 1

Deleted – 2

Edit – 3

The operation code by Difference Stage are

Copy – 0  

Insert – 1

Deleted – 2

Edit – 3

The operation code by Compare Stage are

Equals – 0

First is empty – -2

Greater Than – 1

Less than -- -1

Second is empty – 2