Funnel_Stage

Funnel Stage:

Funnel stage is used to combine multiple input datasets into a single input dataset.This stage can have any number of input links and single output link.

It operates in 3 modes:

Continuous Funnel combines records as they arrive (i.e. no particular order);

Sort Funnel combines the input records in the order defined by one or more key fields;

Sequence copies all records from the first input data set to the output data set, then all the records from the second input data set, etc.

Note:Metadata for all inputs must be identical.

Sort funnel requires data must be sorted and partitioned by the same key columns as to be used by the funnel operation.

Hash Partition guarantees that all records with same key column values are located in the same partition and are processed in the same node.

1)Continuous funnel:

Go to the properties of the funnel stage page and set Funnel Type to continuous funnel.

2)Sequence:

Note:In order to use sequence funnel you need to specify which order the input links you need to process and also make sure the stage runs in sequential mode.

Usually we use sequence funnel when we create a file with header,detail and trailer records.

3)Sort Funnel:

Note: If you are running your sort funnel stage in parallel, you should be aware of the various
considerations about sorting data and partitions

Thats all about funnel stage usage in datastage.

FUNNEL STAGE WITH REAL TIME EXAMPLE

Some times we get data in multiple files which belongs to same bank customers information.

In that time we need to funnel the tables to get the multiple files data into the single file.( table)

For Example , if we have the data two files as below

xyzbank1

e_id,e_name,e_loc

111,tom,sydney

222,renu,melboourne

333,james,canberra

444,merlin,melbourne

xyzbank2

e_id,e_name,e_loc

555,,flower,perth

666,paul,goldenbeach

777,raun,Aucland

888,ten,kiwi

For Funnel take the Job design as

Read and Load the data into two sequential files.

Go to Funnel stage Properties and

Select Funnel Type = Continous Funnel

( Or Any other according to your requirement )

Go to output Drag and drop the Columns

( Remember Source Columns Stucture Should be same ) Then click ok

Give file name for the target dataset then

compile and run th job