• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

Horizontal Sorting in Data Stage

Horizontal Combining

Combining primary rows with Secondary rows based on key column values

The stage that perform  Horizontal combining are

  • Join
  • Look – up
  • Merge

These Stage differ with each other w. r. t

  1. input requirements
  2. Treatment of unmatched records
  3. Memory usage

 

DescriptionjoinlookupMerge
Input Names Screenshot_38
  1. Primary /i/p
  2. Reference lookup

S1 – -> lookup

 Screenshot_39
Types of joinsInner leftouter,right outer, full outerInner, leftouterInner left outer
i/p,o/p,rejects
n-i/p2- i/p1-o/p

No reject

n-i/p2- i/p1-o/p

1-reject

n-i/p1-o/p

n-1-rejects

key column Name requirementsKey column Name should be SAME
In general- optional

It should be SAME in look up file set

Key column name should in SAME
inner join type
p.k column values should have matching with “ALL” secondary records

o/p:10

p/k column values should have matching with “all” secondary records.

o/p

p.k columns values should have matching with “Any” secondary records.

o/p:10,20,30

  1. i/p requirements w.r.to sorting
  1. primary sorting

secondary sorting

  1. mandatory

mandatory

  1. optional

optional

  1. mandatory

mandatory

  1. i/p requirements w.r.t de- duplication (remaining duplicates)
  1. primary

secondary

  1. no need to de- duplicate- ok

ok

  1. ok

warnings

  1. warnings(make sure that primary should be de- duplicate)

ok

  1. treatment of un matched records

a.  primary

b.  secondary

drop(inner)Target(left)b.Drop(inner)

target(right)

  1. drop/target/reject

only drop

  1. drop/target

drop/reject

memory usagelightheavylight

 

lookup

Cross  verification of primary records with Secondary records.

It is a processing Stage which performs Horizontal combining

In Data stage we have 4 types of look – ups

Screenshot_40

Normal lookup

Look up at memory, that is all the Secondary records are copied  to the memory and primary records are cross verified with Secondary records at memory.

Sparse look up

Look – up at Source , that is each primary record is send to the Source for cross verification.

look up, failure condition :-

  1. Continue 7 left outer joins
  2. Drop 7inner join
  3. fail
  4. Reject

Join

4  types of joins can be performed

 

E noE nameDept no
11a10
22b20
33c30

 

 

Dept NoD Nameloc
10AccountNY
20SearchOL
40SalesCH

 

Inner Join 

T1 n T2 = {10, 20}

Matched records from primary and Secondary Tables .

Left outer join

T1 n (t1 u t2)  = {10, 20, 30}

Matched records from primary and Secondary Tables and unmatched   records from primary.

Right outer join 

T2 n (t1 u t2)  = {10, 20, 40}

Matched records from primary and Secondary Tables and unmatched  records from Secondary.

Full outer Join 

T1 u T2 = {10,20,30, 40}

7Matched and un matched records from primary and secondary table

 

Job :- look up

Screenshot_41

 

  Primary 

 

EidEnameStateStatus
11SUMANAP1001
22MOONTN1000
33SumaNAP1111
44Moon STARNN1011
55SUMANAP1101
66MooNJK1001
77MoonSUNAP1011
88MooNMP1111

 

 

StateState desc
ApAndhra Pradesh
TNTamilNadu
JKJammu Kashmir
DLDelhi
KKKenya kumari

 

Sequential file 1 

Load primary.txt 7 columns7 select   state

 Sequential file  0 

Load primary.txt 7columns7 select   state

 

Look –up 

Drag key column value “State” from primary to ref   7In ref, for State, Set key = case less 7(that is case sensitive can look -up)

 

Key expressionRangecolumn
Eid
Ename
State
Status

 

Dslink

DerivationColumn Name
Primary – EIDEID
Primary- EnameEName
Ref.stateState-desc
Primary- statusStatus

 

Key expressionKey typesColumn name
State
State-desc

 

Click on yellow link on the TOP  7Constraints

Set look up failure = Drop   (or) Continue  (or) Reject 7Needs are reject link

Output

  1. (a) job :- Normal look –u p :-

 

Screenshot_42

Interested in mastering DataStage Training?
Enroll now for FREE demo on DataStage Training.

Dept  (4 rows)    [Secondary records are copied to memory, and primary are cross verified with Secondary at memory so, 4 rows]

 

oracle  0

Load dept 7 Set look up Type = Normal

oracle  1

Load EMP

look  up

Map key Column Value “Dept no”7Drag and drop required columns7Select continue

7Compile and RUN

 

(b )Job :- Sparse look up

7 when we need to use Sparse ?

  Sol:-

  1. When memory is insufficient
  2. When primary records are relative less than Secondary records

 

Screenshot_43

 

  • In Sparse :- (conditions apply)
  1. If ref is data base, then it Supports only 2 inputs .

Screenshot_44

 

Dept  (14 rows)  [Each primary record is send to the Source that is dept for cross verification So 14 rows]

oracle 0

Load dept 71st look – up Type = Sparse

 

o/p

 

Job:- Sparse look up Sequential file

If Ref file is Sequential file, then we have an Stage called “Look up file set” 7 extension is .lfs

 

Screenshot_45

 

Sequential file 0   :-  load ref file

Sequential file 1 :- load primary file

Look up 

Map key column value “State”7Drag and drop required columnsàSet look – up failure = continue7Compile and RUN

Screenshot_46

 

Look up file set

Look up keys7Key = State

Keep = false

Target

look up file set  = D: /shilpa/new sql look up.lfs

Look up Range

Range look up = No

Options Allow Duplicates = false

  • Compile and RUN

Look up file set 

  1. It creates indexing on that particular key   (key column value)
  2. In look – up file such same key column Name Should be there]
  • Copy the look up first set path
  1. Now Delete ref Sequential file in (a)

 

7Paste the lookup file set in the place of ref SEQ file is   (a)

7In the lookup file  set  à paste the file name here

7there is no need to map in the look up first

Screenshot_47

 

Output 

E idE nameStateStatusState – dense
11SUMANAP1001Andhra Pradesh
22MOONTN1000Tamilnadu

 

job :- Database Sparse look up

 

In Data base , we can use lookup file set if we have 2 ref Data bases

Screenshot_48

 

Output

Range look up:-

  1. job :- Grade based on Salary 

Screenshot_49

Oracle enterprise 1

Load EMP table 7 convert Sal to integer in EMP table

Oracle enterprise 1 

Load Sal grade table  7Convert Grade, lo Sal, HSal to integer in Sal grade table

 

 Lookup

Screenshot_50

EMP. Sal >= Sal grade . Lo Sal And

EMP. Sal <= Sal grade . H Sal  7Drag and Drop required columns7click on constraints7Select Drop/Continue

Data Set

PropertiesàFile7Browse  for file 7Range look up .ds

Compile and RUN

For indepth understanding of DataStage click on

Summary
Review Date
Reviewed Item
Horizontal Sorting in Data Stage
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Horizontal Sorting in Data Stage"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Site Disclaimer, Copyright © 2016 - All Rights Reserved.

    Support


    Please leave a message and we'll get back to you soon.

    I agree to be contacted via e-mail.