Funnel 

It is a processing stage that supports n inputs and 1 output.

Combining Source 1 and Source 1 with out any key dependency

Funnel requirements

1. Structure of Source 1 and Source 2 should be same
2. Column Names should be same in Source 1 and Source 2.

Funnel types 

We have 3 Funnel types

1. Continuous funnel  Random
2. Sequence follows Linux order
3. Sort funnel  based on key column values

 

Copy 

It is having multi purpose

• Makes Source to Multiple copies
• Change the column Names
• Drop the columns
• used as Stub Stage.

job :- funnel, copy Generator and copy:-

Sequential file 0

 ↓

Load

  ↓

E id             E Name      Add

11                   A             HYD

22                    E             SEC

 

Sequential file 1

E id         E Name    Gender         Sal                 Add

33              B                 1                 23000             HYD

22             C                  0                 22000               Del

 

Column   Generator 

Column to Generate        = COUNTEY

Column to Generate        = COMPANY

Output

Do Mapping    (link 13)

 

Copy 

outputDump (link)à do Mapping

Now,OutputFunnel  (link)Do MappingRemove   DSlink 13.COMPANY / DSlink 13.COUNTRYGo to  Columnsif any column Name changes has to be mode, do it here.

Funnel 

funnel Type = Continuous funnelOutputDo Mapping

Output 

Data set 0

E id	E Name	Add
33	B	HYD
44	C	DEL
11	A	HYD
22	E	SEC

 

 

E id	E Name	Gender	Sal	Add	Country	Company
33	B	1	23000	HYD	aaa	aaa
44	C	0	22000	DEL	bbb	Bbb

 

Data set

2.Job:-  (Not Applicable)

 

Sequential file 1 

Load primary.txt

Sequential file 0 

Load ref.txt

In both  primary.txt and ref.txt   Columns   State

look up 

àMap State Drag and Drop Required ColumnsGo to ConstraintsSet reject

 

Column Generator 

Column to Generate      = STATE – DESCOutputàDo MappingColumnsRight click on STATE – DESCAlgorithm = CycleValue   = not applicableApplyClose

Compile And RUN

output 

Data set 0

E id	E Name	State	Status	State - desc
11	SUMAN	AP	1001	Andhra Pradesh
22	MOON	TN	1000	Tamil Nadu
33	SUMAN	AP	1111	Andhra Pradesh
55	SUMAN	AP	1101	Andhra Pradesh
66	MOON	JK	1001	Jammu Kashmer
77	MOONSUN	AP	1011	Andhra Pradesh

E id	E Name	State	Status	State - desc
44	MOONSTAR	NN	1011	Not Applicable
88	MOON	MP	1111	Not Applicable

 

Data set 1

 

Output 

E id	E Name	State	Status	State - desc
11	SUMAN	AP	1001	Andhra Pradesh
22	MOON	TN	1000	Tamil Nadu
33	SUMAN	AP	1111	Andhra Pradesh
55	SUMAN	AP	1101	Andhra Pradesh
66	MOON	JK	1001	Jammu Kashmer
77	MOONSUN	AP	1011	Andhra Pradesh
44	MOON STAR	NN	1011	Not Applicable
88	MOON	MP	1111	Not Applicable

These core tutorials will help you to learn 
the fundamentals of DataStage.
For an in-depth understanding and practical experience, 
explore Online DataStageTraining.

Conditions 

While using look up, We Can Write the conditions in Constraints

That is Generally when we take 2 sequential files

1.primary and 1 reference file, we can Write Conditions for only primary file

Constraints

Link Name               Condition           Condition Not Met                   Lookup failure

Ref                                                                              Drop                                      Drop

Right click Input column   primary

• If we need to write Conditions for reference file then, take 3 Sequential files
• That is 1 primary , reference file , reference file i

 

4. job

1. Job :- (Not Applicable)

 

 Sequential file  0 

         Load primary.txt columns  state

 

Sequential file 1

Load ref.txt   columnsstate

 

Sequential file 2 

Load rfe2.txt  columns  state

 

Ref2 

Status	Status- desc
1001	Married
1000	Divorced
1111	Single

 

Look up 

Drag State from primary to ref

Drag Status from primary to ref 1

Now, Drag and Drop required ColumnsClick on Constraints

 

Link Name	condition	Condition Not met	Look up failure
ref	Drop	Drop
2.ref1		Drop	Drop

 

Right clickInput Column ref ref . State –desc  = “Andhra  Pradesh”

Now, Compile And RUN

 

Output

E id	E Name	State	Status	State - desc
11	SUMAN	AP	1001	Andhra Pradesh	Married
33	SUMAN	Ap	1111	Andhra Pradesh	Single

For Indepth Knowledge on DataStage, click on below

• Introduction to DataStage
• Scheduling Jobs in DataStage
• String Functions in DataStage
• Partitioning Technique in DataStage
• DataStage Interview Questions & Answers