Sequencer Palette Stages Can be grouped into four Categories

Job Sequence Stages:

1) Run :
a) Job Activity: Run a Job( ASequence Job Can ve called as well )
b) Executive Command : Run a System Command ( OS Command or a Script)
c) Notification Activity : Send an email ( SMPT Server needed)

2) Flow Control
a) Sequencer: Make a any /all Decision.
b) Wait for File: Go When file exists /doesn't exists.
c) Start loop/End loops : Construct Loops
d) Nested Condition : Implement complex control structures.

3)  Error Handling:
a) Exception Handler.
b) Terminator: Send Stop Signal to the calling sequence

4) User Variables and Routine Activity
a) User Variables can be created to use downstream .
b) Routines can be called using Routine Activity.


Datastage Tutorial is the best place to learn datastage Online. You can also  Buy Datastage Material   here to get all  material at one place.
Datastage Online Training is the  one and only best place to learn all about Datastage Stages  with Datastage Examples

Role of merge stage in Datastage


The Merge stage is just a processing stage. It can have any number of data connections or input links, a solitary output link and the same number of reject links as there are update input links.(as like to DS documentation)
Merge stage consolidates a master dataset with one or more overhaul datasets in light of the key columns. The output record contains all the segments from expert record in addition to any extra sections from every upgrade record that are needed.
An expert record and upgrade record will be blended just if both have same key segment values. The data sets input to the Merge stage should be key partitioned and sorted. This makes sure those columns with the same key segment qualities are placed in the same parcel and will be handled by the same hub. It likewise minimizes memory prerequisites in light of the fact that less lines need to be in memory at any one time.
As a component of preprocessing your information for the Merge stage, you ought to additionally expel copy records from the expert information set. If you have more than one upgrade data set, you must expel copy records from the redesign information sets too.
Not at all like Join stages and Lookup stages, the Merge stage permits you to determine a few reject joins. You can route update link rows that neglect to match an expert column down a reject interface that is particular for that link. You should have the same number of rejected links as you have upgrade joins. The Link Ordering tab on the Stage page gives you a chance to indicate that update links send rejected columns to those rejected links. You can likewise indicate whether to drop unmatched expert columns or output them on the output data link.
Example :
Master dataset:

CUSTOMER_ID
CUSTOMER_NAME
1
UMA
2
POOJITHA

Update dataset1



CUSTOMER_ID
CITY
ZIP_CODE
SEX
1
CYPRESS
90630
M
2
CYPRESS
90630
F

Output:




CUSTOMER_ID
CUSTOMER_NAME
CITY
ZIP_CODE
SEX
1
UMA
CYPRESS
90630
M
2
POOJITHA
CYPRESS
90630
F
Merge stage configuration steps :
Unmatched Masters Mode: Keep implies that unmatched columns (those with no upgrades) from the expert links are output; Drop implies that unmatched lines are dropped.
Caution on reject Updates: True to create a notice when awful records from any update links are rejected.
Caution on unmatched masters:True to create a notice when there are unmatched columns from the expert connection.
Partitioning: Hash on both expert input and update input include as demonstrated as follows
Aggregate and run the job:
Situation 1
Expel a record from the updates 1 and check the output:
Check for the datastage cautioning in the job log as we have chosen. Warn on unmatched experts = TRUE
stg_merge,0: Master record (0) has not updates.
stg_merge,1: Update record (1) of data set 1 is dropped; no masters are cleared out.
Situations 2
Drop unmatched expert record and catch reject records from updates 1.
Situation 3
Insert a copy record with same client id in the expert dataset and check for the outcomes.
Take a gander at the output and it is clear that merge stage consequently dropped the copy record from expert dataset.
Situation 4:
Added new update dataset 2 that contains taking after data.

Update Dataset2

CUSTOMER_ID
CITIZENSHIP
1
INDIAN
2
AMERICAN
Still, we have copy row in the expert dataset. If you accumulate the job with above configuration, you will get gathering blunder like below. If you view ate the above figure you can see 2 lines in the output because we have a coordinating column for the customer_id = 2 in the updates 2 .
Situation 5
 Add a copy line for customer_id=1 in updates dataset. Now, we have copy record both in expert dataset and updates1. Run the job and check the outcomes and warnings in the occupation log.
No change in the outcomes and merge stage naturally dropped the copy column.
Situation 6
 Modify a copy line for customer_id=1 in updates1 dataset with zipcode as 90630 rather than 90620.
Run the employment and check output results.
We can ran the same job numerous times and discover the merge stage is taking first record nearing as information from the updates 1 and dropping the following records with same client id.

This post covered the vast majority of the merge situation.

How lookup stage works in Datastage ?



Like Join and Merge stage, Lookup stage has various multiple input links, one is basic and others are reference, as per that lookup operation takes place. But it does not have condition like Merge stage i.e. 'Reject Links' ought to be equivalent to overhaul data interfaces likewise it not oblige information on any of the input links to be sorted. Lookup stage gives four conditions rely upon that in coming days of output data depends.
We will see these conditions in 'Step 4'.
Presently, let’s attempt to actualize or implement Lookup stage with the assistance of below given tables.
Table 1
ID
First Name
Last Name
Location
Network ID
EmailID
1
Jach
Simmons
Chicago
JS524
Letsc@gmail.com
2
Shumas
Jane
LA
Sj145
Jaene@ymail.com
3
Jonty
Waughn
Sydney
JW927
JontyW@sdbh.com
4
Suhana
Safar
Maxico
SS99
Sas@gmail.com
Table 2
ID
Dept
Dept Head
1
Electronics
Paul
3
CS
Jack
4
TS
Summur
         5
IT
Sean
Table 3
ID
Training Cent
1
CKG
2
AMD
3
WC
Step 1 : Design a job structure like underneath.
Consider Employee table as Primary connection or link as demonstrated. Contingent on every or each record in Primary connection, Lookup Stage performs turn upward operation on Reference Link according with key section.

Consider Employee's department information that is table second and Employee's training focus that is Table 3 as data on two reference links.

Step 2 : Now, we are going to Lookup Stage (Named as lkp_emp_det in configuration). Twofold click on Lookup Stage. Taking after window will pop up. Left sheet is for all inputs and right sheet is for output. Initially, link detail table is for Primary link. Second and third are for reference links.

Request of these reference links can be changed by utilizing this image on Title Bar as shown.
Step 3 : In left sheet, guide Key segment (here 'ID') by simply dragging it to the particular key section in reference joins. Guide all staying obliged segment to right sheet as demonstrated.

Step 4 : One of the most vital step is to situated gaze upward conditions that we can do by utilizing second choice on Title bar. Simply click on it, taking after window will pop up.
There is a rundown of reference connections in 'Connection Name' section. In "Condition" section, we can give conditions for every reference link. Whether this condition won't meet then what will happen to that information is chosen by 'Condition Not Met' section and if lookup comes up short it is chosen by 'Lookup Failure' segment.
Proceed with : Data will be sent to the Output link.
Reject : Data will be sent to the Reject link.
Drop : Data will neither go to Output link nor to Reject link.
Fail : Job will falls fail.
In this case, how about we first strive for without condition in "Condition" segment and "Proceed with" and "Reject" in different sections.
Step 5 : Compile and run the JOB.
How about we see what the output is :
Output : Stream link
It's demonstrating two records. As we have given 'Lookup Failure' condition as 'Reject', those records from essential connection that are not coordinated with reference join information are gathered in Reject Link "rjct_primary_rec" as demonstrated as follows.
Step 6 : Let's attempt to design for "Condition" section in 'Lookup Stage Conditions' sheet.
Recently put condition as ID=3 and "Reject" under 'Condition Not Met' as demonstrated as follows.
But ID=3 all records will get dismisses and get put away in 'Reject join'. Here information for ID=2 get rejected and we will get output for Stream interface as indicated.
Output for 'Reject Link'
Note :  Reject Link shows rejected record from basic info link just.
Practice for "Drop" and "Come up short" or fail condition.

Conclusion


DataStagehas constantly performed joins proficiently when there are accurate key fields that match utilizing the lookup, join or union stage. Range lookups are all the more difficult as its a less effective approach to join whether you are destroying it an ETL work or on a database. You can do an extent lookup in DataStage 7 utilizing a lookup stage and a channel stage, you can do it utilizing a meager lookup and you can do it by stacking both tables into a database organizing territory and going along with them in SQL. This exercise demonstrates to destroy it a solitary Lookup stage giving a much less complex outline.

Get to know the facts of Join Stage in Datastage




The "Join" stage is a processing stage that performs a join operation on two or more  input information sets and afterward gives output as one resultant data set.
There are Four sorts of joins in DataStage:
1. Left Outer Join: It joins two or more tables on JOIN condition where result table preserves record from first (Left) table and puts NULL wherever unmatched record from second (Right) table.
2. Right Outer Join: It is precisely inverse to the Left external join. It joins two tables on JOIN condition where result table preserves record from first (Right) table and puts NULL wherever unmatched record from second (Right) table.
3. Inner Join: It joins two or more tables and returns just those records that fulfill join condition.
4. Full Outer Join: It joins two or more tables and returns both coordinated and unmatched records from all tables.
Initially, let’s attempt to perform JOIN on two tables in DataStage.
Table 1:
ID
First Name
Last Name
Location
Network ID
EmailID
1
Jach
Simmons
Chicago
JS524
Letsc@gmail.com
2
Shumas
Jane
LA
Sj145
Jaene@ymail.com
3
Jonty
Waughn
Sydney
JW927
JontyW@sdbh.com
4
Suhana
Safar
Maxico
SS99
Sas@gmail.com
Table 2
ID
Dept
Dept Head
1
Electronics
Paul
3
CS
Jack
4
TS
Summur
5
IT
Sean

Step 1 : Design your job like below.

Step 2 : Configure Table 1 and Table 2 in Sequential record 1 and Sequential document 2.

Step 3 : While designing section name, verify that you ought to give right Datatype against every segment overall output won't be appropriately sort. Give "Number" Datatype for "ID" where we can utilize "varchar" too for it.

Step 4 : Double click on the JOIN stage. Under Properties tab notice 'Key = ID'. Beneath this tab you can notice Join Type as Inner, Left outer or Right Outer. Now, we should Practice for Left outer Join first.

Step 5 : Beneath 'link order' tab you can say or mention Left and right link on that Type of join is depends. i.e.Left or Right external. Order of link doesn't make a difference for Inner Join.

Step 6 : Map the obliged section under output stage.

Step 7 : Link order is depends on key section need to mapped. Those are not mapped will be shown by Red shading or it will indicate lapse while accumulating.
Step 8 : Compile and Run

Output:

1. Left external Join

Left link = lnk_emp_det Right link = lnk_dept_det said under 'Connection or link

As we already said that Table 1 as left table, result table demonstrates all records from left table with coordinated records from right i.e. Table 2. Unmatched records are vacant as indicated previously

2. Right Outer Join

Before accumulate to job for Right outer join, verify that you have mapped output accurately.

Key section from output ought to be mapped from Right table. Mention beneath:

Output

Precisely inverse to Left Outer Join, result Table demonstrates all records from Right table i.e Table 2 and unmatched records are void.

3. Internal Join:

While mapping for output section in 'Inward Join', Key segment (here Key=ID) ought to be mapped from Left table as examined in Step 6 else it will toss mistake amid compilation.Result table shows just coordinated record from both the tables.

4. Full Outer Join:

For Full Outer Join, we can guide key section from both table as demonstrated. It demonstrates an odd passage for key segment which is not exhibit. Here, its shown by "0" in yield


Conclusion

The join stage supports two or more sorted information connections or links and one output link The join stage proofreader or editor permits you  to determine  the keys on which  join  is performed. More  than one key can be determined. Determined keys ought to have same name on all connections. No fall flat/dismiss alternative for missed matches. Link requesting is critical while utilizing left or right external join furthermore the input information on all connections to join stage ought to be sorted.

The Join stage does not give reject link handling to unmatched records. Whether unmatched columns ought be captured, and an external join operation should be performed so that when a match does not happen, the Join stage embeds Null worth into the unmatched non-scratch segments gave non-scratch segment is characterized as null-able on the Join info links. After Join Stage, Use Transformer to channel Null records with the assistance of is Null Built capacity.

Hope this article is useful is useful for you.