Funnels in DataStage

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

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

Data set 1

Output 

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 

Look up 

Drag State from primary to ref

Drag Status from primary to ref 1

Now, Drag and Drop required ColumnsClick on Constraints

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

Now, Compile And RUN

Output

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