ds material by suresh

8/14/2019 Ds Material by Suresh

1/243

DATASTAGE FAQs & TUTORIALs TOPIC INDEX

1. DATASTAGE QUESTIONS.............................................................................22. DATASTAGE FAQ from GEEK INTERVIEW QUESTIONS......................143. DATASTAGE FAQ.........................................................................................264. TOP 10 FEATURES IN DATASTAGE HAWK.............................................305. DATASTAGE NOTES....................................................................................326. DATASTAGE TUTORIA.............................................................................43

............................................................................................................................................44A!o"# D$#$S#$%&.................................................................................................................44

'()&*# 'om+o*&*#,.............................................................................................................44D$#$S#$%& D&,)%*&r. ..........................................................................................................44 D$#$S#$%& D)r&-#or.............................................................................................................44 D$#$S#$%& $*$%&r............................................................................................................44D$#$S#$%& A/m)*),#r$#or....................................................................................................45D$#$S#$%& $*$%&r Ro(&,..................................................................................................45 S&r&r 'om+o*&*#,............................................................................................................45 D$#$S#$%& F&$#"r&,.............................................................................................................45T+&, of o!,......................................................................................................................46 D$#$S#$%& NS...................................................................................................................46 O.....................................................................................................................................4")(#I* S#$%&, 7 S&r&r o!,.............................................................................................4A%%r&%$#or. ........................................................................................................................4H$,8&/ F)(&. .......................................................................................................................4U*)V&r,&. ...........................................................................................................................4 U*)D$#$..............................................................................................................................4 OD'.................................................................................................................................4S&9"&*#)$( F)(&. ..................................................................................................................4:Fo(/&r S#$%&. ......................................................................................................................4: Tr$*,form&r........................................................................................................................4: 'o*#$)*&r............................................................................................................................4; IP' S#$%&............................................................................................................................4;)*< 'o((&-#or S#$%&...........................................................................................................4;)*< P$r#)#)o*&r S#$%&.........................................................................................................50S&r&r o! Pro+&r#)&,..........................................................................................................50 'o*#$)*&r,...........................................................................................................................50o-$( -o*#$)*&r,. ...............................................................................................................50S8$r&/ -o*#$)*&r,. .............................................................................................................50o! S&9"&*-&,.....................................................................................................................51

. EARN FEATURES OF DATASTAGE.........................................................52:. INFORATI'A , DATASTAGE=................................................................;4

Page 1 of 243


2/243

;. EFORE >OU DESIGN >OUR APPI'ATION........................................10410. DATASTAGE .5?1 GUI FEATURES.........................................................11311. DATASTAGE @ DWH INTERVIEW QUESTIONS...................................1112. DATASTAGE ROUTINES............................................................................13113. SETOPARAETERSROUTINE........................................................1;:

DATASTAGE QUESTIONS

1 !"a# $s #"e f%o of %oa'$(g 'a#a $(#o fa)# & '$*e(s$o(a% #a+%es,

AB Fa)# #a+%e T$!(& C)#8 'o((&-#)o* of For&)%* K&, -orr&,+o*/)*% #o #8& Pr)m$r K&,)* D)m&*,)o*$( #$!(&. 'o*,),#, of f)&(/, C)#8 *"m&r)- $("&,.D$*e(s$o( #a+%e T$!(& C)#8 U*)9"& Pr)m$r K&.Loa' D$#$ ,8o"(/ !& f)r,# (o$/&/ )*#o /)m&*,)o*$( #$!(&. $,&/ o* #8& +r)m$r


3/243

D*$m)- f)(&, /o *o# +&rform $, C&(( $, $ C&(( /&,)%*&/ ,#$#)- f)(& !"# /o +&rform !#&r#8$* $ !$/( /&,)%*&/ o*&. W8&* -r&$#)*% $ /*$m)- f)(& o" -$* ,+&-)f #8& fo((oC)*%A(#8o"%8 $(( of #8&,& 8$& /&f$"(# $("&,B

D&f$"(# H$,8&/ f)(& ), D*$m)- T+& R$*/om 30 D

4 !"a# 'oes a Co(f$g F$%e $( 6a5a%%e% e7#e('e5 )o(s$s# of,

AB 'o*f)% f)(& -o*,),#, of #8& fo((oC)*%.$B N"m!&r of Pro-&,,&, or No/&,.!B A-#"$( D),< S#or$%& o-$#)o*.

!"a# $s o'-%-s a(' S6%$##$(g $( D0(a*$) /as"e' F$%e,

A. I* $ H$,8&/ F)(& #8& ,)& of #8& f)(&


4/243

12 /o #o "a('%e Da#e )o(:e5s$o(s $( Da#as#age, Co(:e5# a **B''B0000 fo5*a# #o

0000''**,

AB W& ",& $B I-o* f"*-#)o* I*#&r*$( 'o*&r,)o*.!B O-o* f"*-#)o* E?#&r*$( 'o*&r,)o*.

F"*-#)o* #o -o*&r# mmJ//J form$# #o //mm ),O-o*I-o*F)(&/*$m&DJD>224LBDD>224LB

13 /o 'o 0o- e7e)-#e 'a#as#age o+ f5o* )o**a(' %$(e 65o*6#,

AB U,)*% /,o! -omm$*/ $, fo((oC,./,o! r"* o!,#$#", +ro&-#*$m& o!*$m&

14 F-()#$o(a%$#0 of L$( Pa5#$#$o(e5 a(' L$( Co%%e)#o5,

L$( Pa5#$#$o(e5I# $-#"$(( ,+()#, /$#$ )*#o $r)o", +$r#)#)o*, or /$#$ f(oC, ",)*% $r)o",+$r#)#)o* mo/,.L$( Co%%e)#o5I# -o((&-#, #8& /$#$ -om)*% from +$r#)#)o*, m&r%&, )# )*#o $ ,)*%(& /$#$f(oC $*/ (o$/, #o #$r%.

1 T06es of D$*e(s$o(a% o'e%$(g,

AB D)m&*,)o*$( mo/&()*% ), $%$)* ,"! /))/&/ )*#o 2 #+&,.$B S#$r S-8&m$ S)m+(& @ "-8 F$,#&r. D&*orm$()&/ form.!B S*oCf($


5/243

P("%I*= $B Goo/ P&rform$*-&.!B D$#$!$,& ,+&-)f)-. O*( o*& /$#$!$,&B-B '$**o# 8$*/(& S#or&/ Pro-&/"r&,.

2? D$*e(s$o( o'e%%$(g #06es a%o(g $#" #"e$5 s$g($f$)a()e

D$#$ o/&(()*% ), ro$/( -($,,)f)&/ )*#o 2 #+&,.$B ER D)$%r$m, E*#)# R&($#)o,8)+,B.!B D)m&*,)o*$( o/&(()*%.

Q 21 !"a# a5e As)e(#$a% Das#as#age P5o'-)#s9 Co((e)#$:$#0

A(sAs)e(#$a% P5o'-)#s

A,-&*#)$( D$#$S#$%&A,-&*#)$( D$#$S#$%& EE 3BA,-&*#)$( D$#$S#$%& EE VSA,-&*#)$( D$#$S#$%& TA,-&*#)$( Q"$()#S#$%&A,-&*#)$( $S#$%&A,-&*#)$( RTI 2BA,-&*#)$( Prof)(&S#$%&A,-&*#)$( A"/)#S#$%&A,-&*#)$( 'omm&r-& $*$%&rI*/",#r So("#)o*,

Co((e)#$:$#0

F)(&,RDSR&$(#)m&PA'K,EDIO#8&r

Q 22 E76%a$( Da#a S#age A5)"$#e)#-5e,

D$#$ S#$%& -o*#$)*, #Co -om+o*&*#,C%$e(# Co*6o(e(#

Se5:e5 Co*6o(e(#

C%$e(# Co*6o(e(#

D$#$ S#$%& A/m)*),#r$#or.

D$#$ S#$%& $*$%&r

D$#$ S#$%& D&,)%*&r

D$#$ S#$%& D)r&-#or

Se5:e5 Co*6o(e(#s

D$#$ S#$%& E*%)*&

$ D$#$ R&+o,)#or

Page of 243


6/243

P$-


7/243

Da#a S#age Des$g(e5

W& -$* -r&$#& #8& o!,. W& -$* -om+)(& #8& o!. W& -$* r"* #8& o!. W& -$*/&-($r& ,#$%& $r)$!(& )* #r$*,form C& -$* -$(( ro"#)*&, #r$*,form m$-ro, f"*-#)o*,.

W& -$* Cr)#& -o*,#r$)*#,.

Da#a S#age D$5e)#o5

W& -$* r"* #8& o!,.W& -$* ,-8&/"(& #8& o!,. S-8&/"(& -$* !& /o*& /$)( C&&


8/243

I# ), $ AVA &*%)*& r"**)*% $# #8& !$- !"a# $s S(of%ae s)"e*a,

I# ), +$r#)$(( *orm$()&/ /)m&*,)o*$( mo/&( )* C8)-8 $# #Co r&+r&,&*#, (&$,# o*&/)m&*,)o* or mor& 8)&r$r-8 r&($#&/ #$!(&,.

Q 3? E76%a$( T06es of Fa)# Ta+%es,

Fa)#%ess Fa)#I# -o*#$)*, o*( for&)%*


9/243

Q 34 !"a# a5e A)#$:e a(' Pass$:e s#ages,

A)#$:e S#ageA-#)& ,#$%& mo/&( #8& f(oC of /$#$ $*/ +ro)/& m&-8$*),m, for -om!)*)*%/$#$ ,#r&$m, $%%r&%$#)*% /$#$ $*/ -o*&r#)*% /$#$ from o*& /$#$ #+& #o $*o#8&r. E%Tr$*,form&r $%%r&%$#or ,or# RoC &r%&r -.Pass$:e S#ageA P$,,)& ,#$%& 8$*/(&, $--&,, #o D$#$!$,& for #8& &?#r$-#)o* or Cr)#)*% of/$#$. E% IP' ,#$%& F)(& #+&, U*)&r,& U*)/$#$ DRS ,#$%& -.

Q 3 !"a# $s ODS,

O+&r$#)o*$( D$#$ S#or& ), $ ,#$%)*% $r&$ C8&r& /$#$ -$* !& ro((&/ !$- of 243


10/243

3) MyName = D!obName

To o!#$)* #8& f"(( -"rr&*# ,#$%& *$m&=

N$m& DSo!N$m& = .= DSS#$%&N$m&

Q 3; !"a# $s e0g#Ge#Ne7#8a%-e,

I# ), $ ")(#)* #r$*,form )# %&*&r$#&, S&9"&*#)$( *"m!&r,. I#, )*+"# #+& ), ()#&r$( ,#r)*% @o"#+"# #+& ), ,#r)*%.

Q 3= !"a# a5e s#ages,

T8& ,#$%&, $r& &)#8&r +$,,)& or $-#)& ,#$%&,.Pass$:e s#ages8$*/(& $--&,, #o /$#$!$,&, for &?#r$-#)*% or Cr)#)*% /$#$. A)#$:es#agesmo/&( #8& f(oC of /$#$ $*/ +ro)/& m&-8$*),m, for -om!)*)*% /$#$ ,#r&$m,$%%r&%$#)*% /$#$ $*/ -o*&r#)*% /$#$ from o*& /$#$ #+& #o $*o#8&r.

Q 3> !"a# $('e7 $s )5ea#e' o( Da#a !a5e"o-se,)#m$+ )*/&? ), -r&$#&/ )* D$#$ W$r&8o",&.

Q 4? !"a# $s )o(#a$(e5,

A -o*#$)*&r ), $ %ro"+ of ,#$%&, $*/ ()*


11/243

S+&-)f #8& o! o" C$*# #o -o*#ro( DSA##$-8o!

S +$r$m&r, for #8& o! o" C$*# #o -o*#ro( DSSP$r$m

S ()m)#, for #8& o! o" C$*# #o -o*#ro( DSSo!)m)#

R&9"&,# #8$# $ o! ), r"* DSR"*o!

W$)# for $ -$((&/ o! #o f)*),8 DSW$)#Foro!

G, #8& m$ /$#$ /$)(, for #8& ,+&-)f)&/ ()*< DSG)*


12/243

R"r* $ o! 8$*/(& +r&)o",( o!#$)*&/ from DSA##a)"


13/243

Q 4 !"a# $s o+ se-e()e5,

Q 4; !"a# a5e '$ffe5e(# a)#$:$#$es $( o+ se-e()e5,

Q 4= !"a# a5e #5$gge5s $( 'a#a S#ages, )o('$#$o(a%9 -()o('$#$o(a%9 o#"e5$se

Q 4> A5e - ge(e5a#e' o+ Re6o5#s, S

Q ? !"a# $s 6%-g$(,

Q 1 /a:e - )5ea#e' a(0 )-s#o* #5a(sfo5*, E76%a$(, O)o(:

Page 13 of 243


14/243

DATASTAGE FAQ from GEEK INTERVIEW QUESTIONS

Q-es#$o( D$*e(s$o( o'e%$(g #06es a%o(g $#" #"e$5 s$g($f$)a()e

A(se5

D$#$ o/&(()*% ), !ro$/( -($,,)f)&/ )*#o 2 #+&,.

AB ER D)$%r$m, E*#)# R&($#)o,8)+,B.B D)m&*,)o*$( o/&(()*%.

Q-es#$o( D$*e(s$o(a% *o'e%%$(g $s aga$( s-+ '$:$'e' $(#o 2 #06es

A(se5

AB S#$r S-8&m$ S)m+(& @ "-8 F$,#&r. D&*orm$()&/ form.B S*oCf($


15/243

Q-es#$o( !"a# a5e S#age 8a5$a+%es9 De5$:a#$o(s a(' Co(s#a(#s,

A(se5

S#$%& V$r)$!(& A* )*#&rm&/)$#& +ro-&,,)*% $r)$!(& #8$# r$)*, $("& /"r)*% r&$/ $*//o&,*# +$,, #8& $("& )*#o #$r% -o("m*.'o*,#r$)*# 'o*/)#)o*, #8$# $r& &)#8&r #r"& or f$(,& #8$# ,+&-)f)&, f(oC of /$#$ C)#8 $ ()*


16/243

A(se5

U,)*% /,o! -omm$*/ $, fo((oC,./,o! r"* o!,#$#", +ro&-#*$m& o!*$m&

Q-es#$o( !"a# a5e #"e )o**a(' %$(e f-()#$o(s #"a# $*6o5# a(' e76o5# #"e DS o+s,

A(se5

/,)m+or#.&?& )m+or#, #8& D$#$S#$%& -om+o*&*#,. /,&?+or#.&?& &?+or#, #8& D$#$S#$%& -om+o*&*#,.

Q-es#$o( /o #o 5-( a S"e%% S)5$6# $#"$( #"e s)o6e of a Da#a s#age o+,

A(se5

",)*% E?-&-SH -omm$*/ $# &for&JAf#&r o! +ro+&r#)&,.

Q-es#$o( !"a# a5e OCo(: a(' I)o(: f-()#$o(s a(' "e5e a5e #"e0 -se',

A(se5

I'o*B 'o*&r#, $ ,#r)*% #o $* )*#&r*$( ,#or$%& form$#O'o*B 'o*&r#, $* &?+r&,,)o* #o $* o"#+"# form$#.

Q-es#$o( /o #o "a('%e Da#e )o(:e5#$o(s $( Da#as#age, Co(:e5# **B''B0000

fo5*a# #o 0000''**,

A(se5

W& ",&$B I-o* f"*-#)o* I*#&r*$( 'o*&r#)o*.!B O-o* f"*-#)o* E?#&r*$( 'o*&r#)o*.

F"*-#)o* #o -o*&r# mmJ//J form$# #o //mm ),O-o*I-o*F)(&/*$m&DJD>224LBDD>224LB

Q-es#$o( T06es of Pa5a%%e% P5o)ess$(g,

A(se5P$r$((&( Pro-&,,)*% ), !ro$/( -($,,)f)&/ )*#o 2 #+&,.$B SP Smmr)-$( "(#) Pro-&,,)*%.!B PP $,,)& P$r$((&( Pro-&,,)*%.

Q-es#$o( !"a# 'oes a Co(f$g F$%e $( 6a5a%%e% e7#e('e5 )o(s$s# of,

A(se5

'o*f)% f)(& -o*,),#, of #8& fo((oC)*%.$B N"m!&r of Pro-&,,&, or No/&,.!B A-#"$( D),< S#or$%& o-$#)o*.

Q-es#$o( F-()#$o(a%$#0 of L$( Pa5#$#$o(e5 a(' L$( Co%%e)#o5,A(se5

)*< P$r#)#)o*&r= I# $-#"$(( ,+()#, /$#$ )*#o $r)o", +$r#)#)o*, or /$#$ f(oC, ",)*% $r)o",P$r#)#)o* mo/,.

)*< 'o((&-#or= I# -o((&-#, #8& /$#$ -om)*% from +$r#)#)o*, m&r%&, )# )*#o $ ,)*%(& /$#$f(oC $*/ (o$/, #o #$r%.

Q-es#$o( !"a# $s o'-%-s a(' S6%$##$(g $( D0(a*$) /as"e' F$%e,

Page 1 of 243


17/243

A(se5

I* $ H$,8&/ F)(& #8& ,)& of #8& f)(& &,.T8& fo((oC)*% $r& ,om& of #8& ,#&+,=D&f)*)#&( #$o" -$* ",& 'om+)(& A(( #oo( for #8),.$


18/243

S#$%&/ #8& /$#$ -om)*% from OD'JO'IJD2UD ,#$%&, or $* /$#$!$,& o* #8&,&r&r ",)*% H$,8JS&9"&*#)$( f)(&, for o+#)m"m +&rform$*-& $(,o for /$#$ r&-o&r )*-$,& o! $!or#,.

T"*&/ #8& O'I ,#$%& for Arr$ S)& $*/ RoC, +&r Tr$*,$-#)o* *"m&r)-$( $("&, forf$,#&r )*,&r#, "+/$#&, $*/ ,&(&-#,.

T"*&/ #8& Pro&-# T"*$!(&, )* A/m)*),#r$#or for !#&r +&rform$*-&. U,&/ ,or#&/ /$#$ for A%%r&%$#or.

Sor#&/ #8& /$#$ $, m"-8 $, +o,,)!(& )* D $*/ r&/"-&/ #8& ",& of DSSor# for !#&r+&rform$*-& of o!,.

R&mo&/ #8& /$#$ *o# ",&/ from #8& ,o"r-& $, &$r( $, +o,,)!(& )* #8& o!.

Wor -($",& )* #8& /$#$!$,&.

U,)*% $ -o*,#r$)*# #o f)(#&r $ r&-or/ , ), m"-8 ,(oC&r #8$* +&rform)*% $ SEE'T WHERE.

$


19/243

o,# of #8& #)m&, #8& /$#$ C$, ,&*# #o ", )* #8& form of f($# f)(&,. T8& /$#$ ), /"m+&/ $*/,&*# #o ",. I* ,om& -$,&, C&r& C& *&&/ #o -o**&-# #o D2 for (oo


20/243

A(se5

>&,. O*& of #8& mo,# )m+or#$*# r&9")r&m&*#,.

Q-es#$o( /o o-%' )a%% a( e7#e5(a%


21/243

Q-es#$o( /o goo' a5e 0o- $#" 0o-5 PLBSQL,

A(se5

O* #8& ,-$(& of 110 ,$ :.5;

Q-es#$o( !"a# :e5s$o(s of DS 0o- o5e' $#",

A(se5

DS .5 DS .0.2 DS 6.0 DS 5.2

Q-es#$o( !"a#Ks #"e '$ffe5e()e +e#ee( Da#as#age De:e%o6e5s,A(se5

D$#$,#$%& /&&(o+&r ), o*& 8oC C)(( -o/& #8& o!,. D$#$,#$%& /&,)%*&r ), 8oC C)(( /&,)%*#8& o! I m&$* 8& C)(( /&$( C)#8 !("& +r)*#, $*/ 8& C)(( /&,)%* #8& o!, #8& ,#$%&, #8$# $r&r&9")r&/ )* /&&(o+)*% #8& -o/&

Q-es#$o( !"a# a5e #"e 5e-$5e*e(#s fo5 0o-5 ETL #oo%,

A(se5

Do o" 8$& ($r%& ,&9"&*#)$( f)(&, 1 m)(()o* roC, for &?$m+(&B #8$# *&&/ #o !& -om+$r&/&&r /$ &r,", &,#&r/$If ,o #8&* $,< 8oC &$-8 &*/or Co"(/ /o #8$#. T8)*< $!o"# C8$# +ro-&,, #8& $r& %o)*% #o/o. Ar& #8& r&9")r)*% o" #o (o$/ &,#&r/$, f)(& )*#o $ #$!(& $*/ /o (oo


22/243

'$* #8& #oo(, $ /$#$ !& )*#&%r$#&/ C)#8 #8)r/+$r# /$#$ mo/&()*% $*/Jor

!",)*&,, )*#&(()%&*-& #oo(, If ,o 8oC $*/ C)#8 C8)-8 o*&, HoC C&(( /o&, &$-8 #oo( 8$*/(& -om+(&? #r$*,form$#)o*, $*/ 8oC m"-8 &?#&r*$(

,-r)+#)*% ), r&9")r&/ W8$# o" m)%8# !& ,"r+r),&/ $# ,om& of #8& $*,C&r,.


23/243

A(se5= $#-8 +ro%r$m ), #8& +ro%r$m )#, %&*&r$#& r"* #)m& #o m$)*#$)* ! #8& D$#$,#$%&)#,&(f !"# " -$* &$, #o -8$*%& oC* #8& !$,), of o"r r&9")r&m&*# E?#r$-#)o*Tr$*,form$#)o* o$/)*%B .$#-8 +ro%r$m $r& %&*&r$#& /&+&*/, o"r o! *$#"r& &)#8&r,)m+(& o! or ,&9"&*-&r o! o" -$* ,&& #8), +ro%r$m o* o! -o*#ro( o+#)o*.

Q-es#$o(S-66ose #"a# 4 o+ )o(#5o% +0 #"e se-e()e5 %$e o+ 19 o+ 29 o+ 39 o+ 4 $f o+ 1 "a:e 1?9??? 5o 9af#e5 5-( #"e o+ o(%0 ??? 'a#a "as +ee( %oa'e' $( #a5ge#

#a+%e 5e*a$($(g a5e (o# %oa'e' a(' 0o-5 o+ go$(g #o +e a+o5#e' #"e( /o )a(

s"o5# o-# #"e 65o+%e*,

A(se5=S"++o,& o! ,&9"&*-&r ,*-8ro*)&, or -o*#ro( 4 o! !"# o! 1 8$& +ro!(&m )* #8),-o*/)#)o* ,8o"(/ %o /)r&-#or $*/ -8&-< )# C8$# #+& of +ro!(&m ,8oC)*% &)#8&r /$#$ #+&+ro!(&m C$r*)*% m$,,$%& o! f$)( or o! $!or#&/ If o! f$)( m&$*, /$#$ #+& +ro!(&m orm),,)*% -o("m* $-#)o* .So " ,8o"(/ %o R"* C)*/oC '()-


24/243

A(se5 S&9"&*-&r, $r&o+ )o(#5o% 65og5a*s#8$# &?&-"#& o#8&r o!, C)#8 +r&, o!+$r$m&r,.

Q-es#$o( /o '$' 0o- "a('%e a( KA+o5#e'K se-e()e5,

A(se5I* $(mo,# $(( -$,&, C& 8$& #o /&(& #8& /$#$ )*,&r#&/ ! #8), from D m$*"$(($*/ f)? #8& o! $*/ #8&* r"* #8& o! $%$)*.

Q-es#$o(34 !"a# $s #"e '$ffe5e()e +e#ee( #"e F$%#e5 s#age a(' #"e S$#)" s#age,

A(sT8&r& $r& #Co m$)* /)ff&r&*-&, $*/ +ro!$!( ,om& m)*or o*&, $, C&((. T8& #Co

m$)* /)ff&r&*-&, $r& $, fo((oC,.

1B T8& F)(#&r ,#$%& -$* ,&*/ o*& )*+"# roC #o mor& #8$* o*& o"#+"# ()*


25/243

Q-es#$o( /o 'o 0o- e%$*$(a#e '-6%$)a#e 5os,

A(s D$#$ S#$%& +ro)/&, ", C)#8 $ ,#$%& R&mo& D"+()-$#&, )* E*#&r+r),& &/)#)o*. U,)*%#8$# ,#$%& C& -$* &()m)*$#& #8& /"+()-$#&, !$,&/ o* $


26/243

Technolo%ical a#$anta%es:

I# 8$*/(&, $(( -om+$* /$#$ $*/ $/$+#, #o #8& *&&/, I# off&r, #8& +o,,)!)()# for #8& or%$*)$#)o* of $ -om+(&? !",)*&,, )*#&(()%&*-&

F(&?)!( $*/ ,-$($!(&

I# $--&(&r$#&, #8& r"**)*% of #8& +ro&-# E$,)( )m+(&m&*#$!(&.

DATASTAGE FAQ

1 !"a# $s #"e a5)"$#e)#-5e of 'a#a s#age,

Page 2 of 243


27/243

$,)-$(( $r-8)#&-#"r& of DS ), -()&*#J,&r&r $r-8)#&-#"r&.

'()&*# -om+o*&*#, @ ,&r&r -om+o*&*#,

'()&*# -om+o*&*#, $r& 4 #+&, #8& $r&1. D$#$ ,#$%& /&,)%*&r2. D$#$ ,#$%& $/m)*),#r$#or3. D$#$ ,#$%& /)r&-#or4. D$#$ ,#$%& m$*$%&r

Da#a s#age 'es$g(e5), ",&r for #o /&,)%* #8& o!,

Da#a s#age *a(age5), ",&/ for #o )m+or# @ &?+or# #8& +ro&-# #o )&C @ &/)# #8& -o*#&*#,of #8& r&+o,)#or.

Da#a s#age a'*$($s#5a#o5), ",&/ for -r&$#)*% #8& +ro&-# /&()*% #8& +ro&-# @ ,#)*%#8& &*)ro*m&*# $r)$!(&,.

Da#a s#age '$5e)#o5), ",& for #o r"* #8& o!, $()/$#& #8& o!, ,-8&/"()*% #8& o!,.

Se5:e5 )o*6o(e(#s

DS se5:e5= r"*, &?&-"#$!(& ,&r&r o!, "*/&r #8& -o*#ro( of #8& DS /)r&-#or #8$# &?#r$-##r$*,form $*/ (o$/ /$#$ )*#o $ DWH.DS Pa)age $(s#a%%e5= A ",&r )*#&rf$-& ",&/ #o )*,#$(( +$-


28/243

1. VSS ),"$( ,o"r-& ,$f&2. 'VSS -o*-"rr&*# ),"$( ,o"r-& ,$f&.

VSS ), /&,)%*&/ ! )-ro,of# !"# #8& /),$/$*#$%& ), o*( o*& ",&r -$* $--&,, $# $ #)m&o#8&r ",&r -$* C$)# "*#)( #8& f)r,# ",&r -om+(& #8& o+&r$#)o*.'VSS ! ",)*% #8), m$* ",&r, -$* $--&,, -o*-"rr&*#(. W8&* -om+$r&/ #o VSS 'VSS-o,# ), 8)%8.

!"a# $s #"e '$ffe5e()e +e#ee( )%ea5 %og f$%e a(' )%ea5 s#a#-s f$%e,

C%ea5 %og C& -$* -(&$r #8& (o% /$)(, ! ",)*% #8& DS D)r&-#or. U*/&r o! m&*"-(&$r (o% o+#)o* ), $$)($!(&. ",)*% #8), o+#)o* C& -$* -(&$r #8& (o% /$)(, of+$r#)-"($r o!.

C%ea5 s#a#-s f$%e (, #8& ",&r r&mo& #8& ,#$#", of #8& r&-or/ $,,o-)$#&/ C)#8 $(( ,#$%&,of ,&(&-#&/ o!,.)* DS D)r&-#orB

; I 'e:e%o6e' 1 o+ $#" ? s#ages9 a# #"e 5-( #$*e o(e s#age $s *$sse' "o )a( -

$'e(#$f0 "$)" s#age $s *$ss$(g,

",)*% ",$%& $*$(,), #oo( C8)-8 ), $$)($!(& )* DS m$*$%&r C& -$* f)*/ o"# #8& C8$# r#8& )#&m, r ",&/ )* o!.

= 0 o+ #aes 3? *$(-#es #$*e #o 5-(9 I a(# #o 5-( #"e o+ %ess #"a( 3? *$(-#es,

!"a# 5 #"e s#e6s e "a:e #o #ae,

",)*% +&rform$*-& #"*)*% $,+&-#, C8)-8 $r& $$)($!(& )* DS C& -$* r&/"-& #)m&.T-($(g as6e)#

I* DS $/m)*),#r$#or = )*+ro-&,, $*/ )*#&r +ro-&,,I* !C&&* +$,,)& ,#$%&, = )*#&r +ro-&,, ,#$%&O'I ,#$%& = Arr$ ,)& $*/ #r$*,$-#)o* ,)&

A*/ $(,o ",& ()*< +$r#)#)o*&r @ ()*< -o((&-#or ,#$%& )* !C&&* +$,,)& ,#$%&,

> /o #o 'o 5oa' #5a(s6os$#$o( $( DS,

P)o# ,#$%& ), ",&/ #o #r$*,+o,)#)o* +"r+o,&. P)o# ), $* $-#)& ,#$%& #8$# m$+, ,, of-o("m*, )* $* )*+"# #$!(& #o $ ,)*%(& -o("m* )* $* o"#+"# #$!(&.

1? If a o+ %o)e' +0 so*e -se59 "o )a( 0o- -(%o) #"e 6a5#$)-%a5 o+ $( DS,

W& -$* "*(o-< #8& o! ! ",)*% )%ea( -6 5eso-5)es o6#$o( C8)-8 ), $$)($!(& )* DSD)r&-#or. O#8&r C),& C& -$* f)*/ PID +ro-&,, )/B $*/


29/243

A -o*#$)*&r ), $ %ro"+ of ,#$%&, $*/ ()*;9D, !"a# $s #"e X

:a%-e,

$("& ), Y&ro.I-o* F"*-#)o* 'o*&r#, $ ,#r)*% #o $* )*#&r*$( ,#or$%& form$#.I# #$


30/243

O!)o",( #8&r& ), ,om& +ro-&,, %o)*% o*. U*/&r -o&r, D, )*,&r#, $ -"#/oC*#r$*,form&r ,#$%& !C&&* #8& +$,,)& ,#$%&, C8)-8 ",# +$,,&, /$#$ ,#r$)%8# from o*&,#$%& #o #8& o#8&r.

1= !"a# $s #"e -se -se of Nes#e' )o('$#$o( a)#$:$#0,

N&,#&/ 'o*/)#)o*. A((oC, o" #o f"r#8&r !r$*-8 #8& &?&-"#)o* of $ ,&9"&*-& /&+&*/)*% o*$ -o*/)#)o*.

1> I "a:e #"5ee o+s A99C !"$)" a5e 'e6e('e(# o( ea)" o#"e5, I a(# #o 5-( A

& C o+s 'a$%0 a(' o+ 5-(s o(%0 o( S-('a0 /o )a( - 'o $#,

F)r,# o" 8$& #o ,-8&/"(& A @ ' o!, o*/$ #o S$#"r/$ )* o*& ,&9"&*-&.N&?# #$


31/243

1B T8& m$/$#$ ,&r&r. To !orroC $ ,)m)(& from #8$# "/%& o* Am&r)-$* I/o( U,)*%$S#$%& ),


32/243

,*-8ro*),&, o"r SQ ,&(&-# (),# C)#8 o"r ET -o("m* (),# #o $o)/ -o("m* m),m$#-8&,.I $m 8o+)*% #8& *&?# &r,)o* ), mor& f(&?)!(& $*/ -$* !")(/ -om+(&? SQ.

;B Im+ro&/ o! ,#$r#"+ #)m&,. Sm$(( +$r$((&( o!, C)(( r"* f$,#&r. I -$(( )# #8& /&$#8 of $#8o",$*/ -"#, o"r &r ($r%& +$r$((&( o! #$


33/243

2. >o" -$* %)& N *"m!&r )*+"# ()*


34/243

>o" -$* ",& $* OD' ,#$%& #o &?#r$-# Cr)#& or $%%r&%$#& /$#$. E$-8 OD' ,#$%& -$*8$& $* *"m!&r of )*+"#, or o"#+"#,. I*+"# ()*


35/243

C5ea#e #a+%e $( #a5ge# 'a#a+ase. S&(&-# #8), -8&-< !o? )f o" C$*# #o $"#om$#)-$((

-r&$#& $ #$!(& )* #8& #$r% /$#$!$,& $# r"* #)m&. A #$!(& ), -r&$#&/ !$,&/ o* #8& /&f)*&/-o("m* , for #8), ,#$%&. If o" ,&(&-# #8), o+#)o* $* $//)#)o*$( #$! E'$# DDL$++&$r,. T8), ,8oC, #8& SQ 'REATE ,#$#&m&*# #o !& ",&/ for #$!(& %&*&r$#)o*.

T5a(sa)#$o( /a('%$(g. T8), +$%& $((oC, o" #o ,+&-)f #8& #r$*,$-#)o* 8$*/()*%

f&$#"r&, of #8& ,#$%& $, )# Cr)#&, #o #8& OD' /$#$ ,o"r-&. >o" -$* -8oo,& C8&r #o",& #r$*,$-#)o* %ro"+)*% or *o# ,+&-)f $* ),o($#)o* (&&( #8& *"m!&r of roC, Cr)##&*!&for& &$-8 -omm)# $*/ #8& *"m!&r of roC, Cr)##&* )* &$-8 o+&r$#)o*.

Iso%a#$o( Le:e%s R&$/ U*-omm)##&/ R&$/ 'omm)##&/ R&+&$#$!(& R&$/S&r)$()$!(& V&r,)o*)*% $*/ A"#o'omm)#.

Ros 6e5 #5a(sa)#$o( f)&(/. T8), ), #8& *"m!&r of roC, Cr)##&* !&for& #8& /$#$ ),-omm)##&/ #o #8& /$#$ #$!(&. T8& /&f$"(# $("& ), 0 #8$# ), $(( #8& roC, $r& Cr)##&*!&for& !&)*% -omm)##&/ #o #8& /$#$ #$!(&.

Pa5a*e#e5 a55a0 s$.e f)&(/. T8), ), #8& *"m!&r of roC, Cr)##&* $# $ #)m&. T8&

/&f$"(# ), 1 #8$# ), &$-8 roC ), Cr)##&* )* $ ,&+$r$#& o+&r$#)o*.

ODC S#age MO-#6-# Page

PROCESSING S#ages

TRANSFORER S#age

Tr$*,form&r ,#$%&, /o *o# &?#r$-# /$#$ or Cr)#& /$#$ #o $ #$r% /$#$!$,&. T8& $r& ",&/ #o8$*/(& &?#r$-#&/ /$#$ +&rform $* -o*&r,)o*, r&9")r&/ $*/ +$,, /$#$ #o $*o#8&rTr$*,form&r ,#$%& or $ ,#$%& #8$# Cr)#&, /$#$ #o $ #$r% /$#$ #$!(&.

Tr$*,form&r ,#$%&, -$* 8$& $* *"m!&r of )*+"#, $*/ o"#+"#,. T8& ()*< from #8& m$)*/$#$ )*+"# ,o"r-& ), /&,)%*$#&/ #8& +r)m$r )*+"# ()*


36/243

r&f&r&*-& )*+"# -o*#$)*, *$m&, $*/ +8o*& *"m!&r, #8& r&f&r&*-& ()*< (a*e -o("m* ),m$r


37/243

$!o"# %r&&* $*/ !("& +$)*# o* )*


38/243

If o" C$*# #o $%%r&%$#& #8& )*+"# /$#$ )* $ *"m!&r of /)ff&r&*# C$, o" -$* 8$&,&&r$( o"#+"# ()*


39/243

T8& Co%-*(s #$! /&f)*&, $ m$?)m"m of #Co -o("m*,. T8& f)r,# -o("m* m",# !& m$ro" -$* ",& I*#&r+ro-&,, ,#$%&, #o o)* +$,,)& ,#$%&, #o%&r. For &?$m+(& o" -o"(/",& #8&m #o ,+&&/ "+ /$#$ #r$*,f&r !C&&* #Co /$#$ ,o"r-&,=

I* #8), &?$m+(& #8& o! C)(( r"* $, #Co +ro-&,,&, o*& 8$*/()*% #8& -omm"*)-$#)o* from,&9"&*#)$( f)(& ,#$%& #o IP' ,#$%& $*/ o*& 8$*/()*% -omm"*)-$#)o* from IP' ,#$%& #oOD' ,#$%&. A, ,oo* $, #8& S&9"&*#)$( F)(& ,#$%& 8$, o+&*&/ )#, o"#+"# ()*


40/243

T8& )*< P$r#)#)o*&r ,#$%& ), $* $-#)& ,#$%& C8)-8 #$


41/243

If o" )*,#$((&/ or )m+or#&/ $ o! #8& efo5es#age s-+5o-#$(e or Af#e5s#age s-+5o-#$(ef)&(/ m$ r&f&r&*-& $ ro"#)*& #8$# /o&, *o# &?),# o* o"r ,,#&m. I* #8), -$,& $ C$r*)*%m&,,$%& $++&$r, C8&* o" -(o,& #8& L$( Pa5#$#$o(e5 S#age /)$(o% !o?. >o" m",# )*,#$((or )m+or# #8& Zm),,)*%[ ro"#)*& or -8oo,& $* $(#&r*$#)& o*& #o ",&.

Def$($(g L$( Pa5#$#$o(e5 S#age P5o6e5#$es

T8& P5o6e5#$es #$! $((oC, o" #o ,+&-)f #Co +ro+&r#)&, for #8& )*< P$r#)#)o*&r ,#$%&=

Pa5#$#$o($(g A%go5$#"*. U,& #8), +ro+&r# #o ,+&-)f #8& mo/ #8& ,#$%& ",&, #o

+$r#)#)o* /$#$. '8oo,& from=

Ro-('Ro+$(. T8), ), #8& /&f$"(# mo/. U,)*% #8& ro"*/ro!)* mo/ #8& ,#$%&C)(( Cr)#& &$-8 )*-om)*% roC #o o*& of )#, o"#+"# ()*


42/243

Ge(e5a%. T8& Ge(e5a% #$! $((oC, o" #o ,+&-)f $* o+#)o*$( /&,-r)+#)o* of #8& ,#$%&.

Co%-*(s. T8& Co%-*(s #$! -o*#$)*, #8& -o("m* /&f)*)#)o*, for #8& /$#$ o* #8& )*+"#

()*o" -$* Loa' $ -o("m* /&f)*)#)o* from #8& R&+o,)#or or #+& o*& )* o"r,&(f$*/ Sa:e )# #o #8& R&+o,)#or )f r&9")r&/B. No#& #8$# #8& m$ /$#$ o* #8& o"#+"# ()* of DataStage make it possible for youto stipulate at design time that *obs should becompiled in this way. (here are two ways ofdoing this:8 Eplicitly K by inserting 62' stages between

connected acti#e stages.8 6mplicitly K by turning on inter-process rowbuffering either pro*ect wide )using theDataStage $dministrator+ or for indi#idual *obs)in the!ob ,roperties dialog bo+(he 62' facility can also be used to producemultiple processes where passi#e stages aredirectly connected. (his means that an operationreading from one data source and writing toanother could be di#ided into a reading processand a writing process able to take ad#antage of

multiprocessor systems.

Page ;> of 243


80/243

,artitioning and Collecting

With the introduction of the enhanced multi-processor support at elease>, there areopportunities to further enhance theperformance of ser#er *obs by partitioning data.(he &ink 2artitioner stage allows you to partitiondata you are reading so it can be processed byindi#idual processors running on multipleprocessors. (he &ink 'ollector stage allows youto collect partitioned data together again forwriting to a single data target. (he followingdiagram illustrates how you might use the &ink

2artitioner and &ink 'ollector stages within a *ob./oth stages are acti#e, and you should turn oninter-process row buffering at pro*ect or *ob le#elin order to implement process boundaries.

Page =? of 243


81/243

AggregatorStages

$ggregator stages classify data rows from asingle input link into groups and compute totalsor other aggregate functions for each group. (he

summed totals for each group are output fromthe stage #ia an output link.)sing an Aggregator Stage

6f you want to aggregate the input data in anumber of different ways, you can ha#e se#eraloutput links, each specifying a different set ofproperties to define how the input data isgrouped and summari0ed.When you edit an $ggregator stage, theAggregator Stage dialog bo appears:

(his dialog bo has three pages:8 Stage. Displays the name of the stage you areediting. (his page has a eneral tab whichcontains an optional description of the stage andnames of before- and after-stage routines8 %nputs. Specifies the column definitions for thedata input link.8 "utputs. Specifies the column definitions forthe data output link.

Defining Aggregator %nput Data

Data to be aggregated is passed from a pre#iousstage in the *ob design and into the $ggregatorstage #ia a single input link. (he properties of

Page =1 of 243


82/243

this link and the column definitions of the dataare defined on the %nputs page in theAggregator Stage dialog bo.

Note0 (he $ggregator stage does not preser#ethe order of input rows, e#en when the incomingdata is already sorted.(he %nputs page has the following field and twotabs:8 %nput name. (he name of the input link to the$ggregator stage.8 eneral. Displayed by default. 'ontains anoptional description of the link.8 Columns. 'ontains a grid displaying thecolumn definitions for the data being written to

the stage, and an optional sort order.Column name0(he name of the column.Sort "rder0 Specifies the sort order. (his

field is blank by default, that is, there is nosort order. 'hoose Ascending forascending order, Descending fordescending order, or %gnore if you do notwant the order to be checked.

4ey06ndicates whether the column is part ofthe primary key.

S: type0(he S%& data type.

engt(0(he data precision. (his is the lengthfor '!$ data and the maimum length for3$'!$ data.

Scale0(he data scale factor.Nullable0 Specifies whether the column can

contain null #alues.Display0(he maimum number of characters

required to display the column data.

Page =2 of 243


83/243

Data element0 (he type of data in thecolumn.

Description0 $ tet description of thecolumn.

Defining Aggregator "utput DataWhen you output data from an $ggregator stage,the properties of output links and the columndefinitions of the data are defined on the"utputs page in the Aggregator Stage dialogbo.

(he "utputs page has the following field andtwo tabs:8 "utput name. (he name of the output link.'hoose the link to edit from the "utput namedrop-down list bo. (his list bo displays all theoutput links from the stage.8 eneral. Displayed by default. 'ontains anoptional description of the link.8 Columns. 'ontains a grid displaying thecolumn definitions for the data being outputfrom the stage. (he grid has the followingcolumns:

Column name. (he name of the column.roup. Specifies whether to group by thedata in the column.

Derivation. 'ontains an epressionspecifying how the data is aggregated.(his is a comple cell, requiring more thanone piece of information. Double-clickingthe cell opens the Derivation

Page =3 of 243


84/243

TransformerStages

(ransformer stages do not etract data or writedata to a target database. (hey are used to

handle etracted data, perform any con#ersionsrequired, and pass data to another (ransformerstage or a stage that writes data to a target datatable.

)sing a Transformer Stage

(ransformer stages can ha#e any number ofinputs and outputs. (he link from the main datainput source is designated the primary input link.(here can only be one primary input link, but

there can be any number of reference inputs.When you edit a (ransformer stage, the(ransformer Editor appears. $n eample(ransformer stage is shown below. 6n thiseample, metadata has been defined for theinput and the output links.

in- Area

(he top area displays links to and from the(ransformer stage, showing their columns andthe relationships between them. (he link area iswhere all column definitions, key epressions,and stage #ariables are defined. (he link area isdi#ided into two panesL you can drag the splitter

Page =4 of 243


85/243

bar between them to resi0e the panes relati#e toone another. (here is also a hori0ontal scroll bar,allowing you to scroll the #iew left or right. (heleft pane shows input links, the right pane showsoutput links. (he input link shown at the top of

the left pane is always the primary link. $nysubsequent links are reference links. 7or all typesof link, key fields are shown in bold. eferencelink key fields that ha#e no epression definedare shown in red )or the color defined in ToolsM "ptions+, as are output columns that ha#e noderi#ation defined.Within the (ransformer Editor, a single link maybe selected at any one time. When selected, thelinkGs title bar is highlighted, and arrowheadsindicate any selected columns.

Metadata Area

(he bottom area shows the column metadata forinput and output links. $gain this area is di#idedinto two panes: the left showing input linkmetadata and the right showing output linkmetadata. (he metadata for each link is shownin a grid contained within a tabbed page. 'lickthe tab to bring the required link to the front.(hat link is also selected in the link area.

6f you select a link in the link area, its metadatatab is brought to the front automatically. "ou canedit the grids to change the column metadata onany of the links. "ou can also add and deletemetadata.

%nput in-s

(he main data source is *oined to the(ransformer stage #ia the primary link, but thestage can also ha#e any number of reference

input links.

$ reference link represents a table lookup. (heseare used to pro#ide information that might affectthe way the data is changed, but do not supplythe actual data to be changed. eference inputcolumns can be designated as key fields. "ou canspecify key epressions that are used to e#aluate

Page = of 243


86/243

the key fields. (he most common use for thekey epression is to specify an equi-*oin, which isa link between a primary link column and areference link column. 7or eample, if yourprimary input data contains names and

addresses, and a reference input contains namesand phone numbers, the reference link namecolumn is marked as a key field and the keyepression refers to the primary linkGs namecolumn. During processing, the name in theprimary input is looked up in the reference input.6f the names match, the reference data isconsolidated with the primary data. 6f the namesdo not match, i.e., there is no record in thereference input whose key matches theepression gi#en, all the columns specified for

the reference input are set to the null #alue.

"utput in-s

"ou can ha#e any number of output links fromyour (ransformer stage. "ou may want to passsome data straight through the (ransformerstage unaltered, but itGs likely that youGll want totransform data from some input columns beforeoutputting it from the (ransformer stage. "oucan specify such an operation by entering a

/$S6' epression or by selecting a transform toapply to the data. DataStage has many built-intransforms, or you can define your own customtransforms that are stored in the epository andcan be reused as required. (he source of anoutput link column is defined in that columnGsDerivation cell within the (ransformer Editor."ou can use the Epression Editor to enterepressions or transforms in this cell. "ou canalso simply drag an input column to an outputcolumnGs Derivation cell, to pass the data

straight through the (ransformer stage. 6naddition to specifying deri#ation details forindi#idual output columns, you can also specifyconstraints that operate on entire output links. $constraint is a /$S6' epression that specifiescriteria that data must meet before it can bepassed to the output link. "ou can also specify are*ect link, which is an output link that carries all

Page = of 243


87/243

the data not output on other links, that is,columns that ha#e not met the criteria. Eachoutput link is processed in turn. 6f the constraintepression e#aluates to (UE for an input row,the data row is output on that link. 'on#ersely, if

a constraint epression e#aluates to 7$&SE for aninput row, the data row is not output on that link.'onstraint epressions on different links areindependent. 6f you ha#e more than one outputlink, an input row may result in a data row beingoutput from some, none, or all of the outputlinks. 7or eample, if you consider the data thatcomes from a paint shop, it could includeinformation about any number of different colors.6f you want to separate the colors into differentfiles, you would set up different constraints. "ou

could output the information about green andblue paint on &ink $, red and yellow paint on &ink/, and black paint on &ink '. When an inputrow contains information about yellow paint, the&ink $ constraint epression e#aluates to 7$&SEand the row is not output on &ink $. !owe#er,the input data does satisfy the constraintcriterion for &ink / and the rows are output on&ink /. 6f the input data contains informationabout white paint, this does not satisfy anyconstraint and the data row is not output on

&inks $, / or ', but will be output on the re*ectlink. (he re*ect link is used to route data to atable or file that is a Ncatch-allO for rows that arenot output on any other link. (he table or filecontaining these re*ects is represented byanother stage in the *ob design.

Inter-ProcessStages

$n inter-process )62'+ stage is a passi#e stagewhich pro#ides a communication channel

between DataStage processes runningsimultaneously in the same *ob. 6t allows you todesign *obs that run on S12 systems with greatperformance benefits. (o understand thebenefits of using 62' stages, you need to know abit about how DataStage *obs actually run asprocesses,

Page =; of 243


88/243

6n this eample the *ob will run as two processes,one handling the communication from sequentialfile stage to 62' stage, and one handlingcommunication from 62' stage to 4D/' stage. $ssoon as the Sequential 7ile stage has opened itsoutput link, the 62' stage can start passing datato the 4D/' stage. 6f the *ob is running on a

multi-processor system, the two processor canrun simultaneously so the transfer will be muchfaster. "ou can also use the 62' stage toeplicitly specify that connected acti#e stagesshould run as separate processes. (his isad#antageous for performance on multi-processor systems. "ou can also specify thisbeha#ior implicitly by turning inter process rowbuffering on, either for the whole pro*ect #iaDataStage $dministrator, or indi#idually for a *obin its!ob ,roperties dialog bo.

Page == of 243


89/243

)sing t(e %,C Stage

When you edit an 62' stage, the %nter,rocessStage dialog bo appears.

(his dialog bo has three pages:8 Stage. (he Stage page has two tabs, eneraland ,roperties. (he eneral page allows youto specify an optional description of the page.(he ,roperties tab allows you to specify stageproperties.8 %nputs. (he 62' stage can only ha#e one inputlink. the %nputs page displays information about

that link. 8 "utputs. (he 62' stage can only ha#e oneoutput link. (he "utputs page displaysinformation about that link.

Defining %,C Stage ,roperties

(he ,roperties tab allows you to specify twoproperties for the 62' stage:8 #uffer Si?e. Defaults to 9@ Pb. (he 62' stageuses two blocks of memoryL one block can be

written to while the other is read from. (hisproperty defines the si0e of each block, so thatby default => Pb is allocated in total.8 Timeout. Defaults to 9C seconds. (his gi#estime limit for how long the stage will wait for aprocess to connect to it before timing out. (hisnormally will not need changing, but may beimportant where you are prototyping multi-

Page => of 243


90/243

processor *obs on single processor platforms andthere are likely to be delays.

Defining %,C Stage %nput Data

(he 62' stage can ha#e one input link. (his iswhere the process that is writing connects.(he 6nputs page has two tabs: eneral andColumns.8 eneral. (he eneral tab allows you tospecify an optional description of the stage.8 Columns. (he Columns tab contains thecolumn definitions for the data on the input link.(his is normally populated by the metadata ofthe stage connecting on the input side. "ou canalso oad a column definition from the

epository, or type one in yourself )and Save itto the epository if required+. 5ote that themetadata on the input link must be identical tothe metadata on the output link.

Defining %,C Stage "utput Data

(he 62' stage can ha#e one output link. (his iswhere the process that is reading connects.(he "utputs page has two tabs: eneral andColumns.

8 eneral. (he eneral tab allows you tospecify an optional description of the stage.8 Columns. (he Columns tab contains thecolumn definitions for the data on the input link.(his is normally populated by the metadata ofthe stage connecting on the input side. "ou canalso oad a column definition from theepository, or type one in yourself )and Save itto the epository if required+. 5ote that themetadata on the output link must be identical tothe metadata on the input link.

Lin P%rtition#r St%/

(he &ink 2artitioner stage is an acti#e stagewhich takes one input and allows you todistribute partitioned rows to up to >< output

Page >? of 243


91/243

links. (he stage epects the output links to usethe same metadata as the input link. 2artitioningyour data enables you to take ad#antage of amulti-processor system and ha#e the dataprocessed in parallel. 6t can be used in

con*unction with the &ink 'ollector stage topartition data, process it in parallel, and thencollect it together again before writing it to asingle target.

6n order for this *ob to compile and run asintended on a multi-processor system you mustha#e inter-process buffering turned on, either atpro*ect le#el using the DataStage $dministrator,or at *ob le#el from the !ob ,roperties dialogbo.

Defining in- ,artitioner Stage ,roperties

(he ,roperties tab allows you to specify twoproperties for the &ink 2artitioner stage:

8 ,artitioning Algorit(m. Use this property tospecify the method the stage uses to partitiondata. 'hoose from:& Round$Robin. (his is the default method.Using the round-robin method the stage will write

each incoming row to one of its output links inturn.& Random. Using this method the stage will usea random number generator to distributeincoming rows e#enly across all output links.& 'as(. Using this method the stage applies ahash function to one or more input column

Page >1 of 243


92/243

#alues to determine which output link the row ispassed to.& Modulus. Using this method the stage appliesa modulus function to an integer input column#alue to determine which output link the row is

passed to.

8 ,artitioning 4ey. (his property is onlysignificant where you ha#e chosen a partitioningalgorithm of !ash or 1odulus. 7or the !ashalgorithm, specify one or more column namesseparated by commas. (hese keys areconcatenated and a hash function applied todetermine the destination output link. 7or the1odulus algorithm, specify a single column namewhich identifies an integer numeric column. (he

#alue of this column #alue determines thedestination output link.

Link CollectorStages

(he &ink 'ollector stage is an acti#e stage whichtakes up to >< inputs and allows you to collectdata from this links and route it along a singleoutput link. (he stage epects the output link touse the same metadata as the input links. (he&ink 'ollector stage can be used in con*unction

with a &ink 2artitioner stage to enable you totake ad#antage of a multi-processor system andha#e data processed in parallel. (he &ink2artitioner stage partitions data, it is processedin parallel, then the &ink 'ollector stage collectsit together again before writing it to a singletarget.

(he following diagram illustrates how the &ink'ollector stage can be used in a *ob in this way.

Page >2 of 243


93/243

6n order for this *ob to compile and run asintended on a multi-processor system you mustha#e inter-process buffering turned on, either atpro*ect le#el using the Data Stage $dministrator,

or at *ob le#el from the !ob ,roperties dialogbo.

Defining in- Collector Stage ,roperties

(he ,roperties tab allows you to specify twoproperties for the &ink 'ollector stage:8 Collection Algorit(m. Use this property tospecify the method the stage uses to collectdata. 'hoose from:

& Round$Robin. (his is the default method.Using the round-robin method the stage will reada row from each input link in turn.& Sort@Merge. Using the sortImerge method thestage reads multiple sorted inputs and writes onesorted output.8 Sort 4ey. (his property is only significantwhere you ha#e chosen a collecting algorithm ofSortI1erge. 6t defines how each of the partitioneddata sets are known to be sorted and how themerged output will be sorted. (he key has the

following format: Column name Qsort orderF,Column name sort orderFF...

Page >3 of 243


94/243

INFORATIA 2! DATASTAGE/

F#%tur#! Inform%ti(% D%t%St%S)!t#m R#3uir#m#nt

- Platform Support Win NT/UNIX Win NT/UNIX/More Platforms

D#p+o)m#nt F%(i+it)

- Ability to handle initialdeployment, maor releases,minor releases and pat!hes"ith e#ual ease

$es,My e%perien!e has beenthat IN&A is definitelyeasier to implementinitially and up'rade(

No,As!ential has done a 'ood oin re!ent releases(

Tr%n!form%tion!

- No of a)ailabletransformation fun!tions

*+ +,S has many more !annedtransformation fun!tions than+(

- Support for loopin' thesour!e ro" .&or While oop0

Supports for !omparin'immediate pre)iousre!ord

oes not support

- Slo"ly 1han'in' imension &ull history, re!ent)alues, 1urrent 2 Pre))alues

Supports only throu'h 1ustoms!ripts( oes not ha)e a"i3ard to do this(S has a !omponent !alledProfileSta'e that handles thistype of !omparison( $ou4ll "ato use it udi!iously in yourprodu!tion pro!essin' be!ausit does ta5e e%tra resour!es touse it but I ha)e

found it to be )ery useful(- Time imension 'eneration oes not support( oes not support(

- 6ee!ted 6e!ords 1an be !aptured 1annot be !aptured inseparate file(S absolutely has the ability t!apture ree!ted re!ords in aseparate file( That4s a prettybasi! !apability and I don4t5no" of any 7T toolthat !an4t do it(((

- ebu''in' &a!ility Not Supported( Supports basi! debu''in'fa!ilities for testin'(

App+i(%tion Int#&r%tionFun(tion%+it)

- Support for real Timeata 7%!han'e

Not A)ailable Not A)ailable,The 8(% )ersion of S has a!omponent to handle real-timdata e%!han'e( I thin5 it is!alled 6T7(

- Support for 196:A/XM oes not support oes not support

#t%'%t%

Page >4 of 243


95/243

- Ability to )ie" 2 na)i'atemetadata on the "eb

oes Not Support ;ob sessions !an be monitoreusin' Informati!a1lasses(This is !ompletely not true( Shas a )ery stron' metadata!omponent .MetaSta'e0 that"or5s not only "ith S, butalso has plu'-ins to "or5"ith modelin' tools .li5e76Win0 and :I tools .li5e1o'nos0( This is oneof their stron' suits .a'ain,IM(>(Informati!a?( $es(>((ataSta'e? NoMy e%perien!e has been that IN&A is definitiely easier toimplementinitially and up'rade( As!ential has done a 'ood ob inre!ent releasesto impro)e, but IM of 243


96/243

@0 Transformations@(>( No of a)ailable transformation fun!tions@(>(>(Informati!a?( *+@(>((ataSta'e? +S has many more !anned transformation fun!tions than +( I4mnot sure"hat leads you to this number, but I4d re!he!5 it if I "ereyou(

@(( Support for loopin' the sour!e ro" .&or While oop0@((>(Informati!a?( Supports for !omparin' immediate pre)iousre!ord@(((ataSta'e? oes not support

@(@( Slo"ly 1han'in' imension@(@(>(Informati!a?( Supports &ull history, re!ent )alues, 1urrent 2Pre) )alues(@(@((ataSta'e? Supports only throu'h 1ustom s!ripts( oes notha)e a"i3ard to do thisS has a !omponent !alled ProfileSta'e that handles this type

of!omparison( $ou4ll "ant to use it udis!iously in your produ!tionpro!essin' be!ause it does ta5e e%tra resour!es to use it but I ha)efound it to be )ery useful(

@(( Time imension 'eneration@((>(Informati!a?( oes not support(@(((ataSta'e? oes not support

@(*( 6ee!ted 6e!ords@(*(>(Informati!a?( 1an be !aptured@(*((ataSta'e? 1annot be !aptured in separate fileS absolutely has the ability to !apture ree!ted re!ords in aseparatefile( That4s a pretty basi! !apability and I don4t 5no" of any

7T toolthat !an4t do it(((

@(*( ebu''in' &a!ility@(*(>(Informati!a?( Not Supported@(*((ataSta'e? Supports basi! debu''in' fa!ilities for testin'(

0 Appli!ation Inte'ration &un!tionality(>( Support for real Timeata 7%!han'e(>(>((Informati!a?( Not A)ailable(>((ataSta'e? Not A)ailable(The 8(% )ersion of S has a !omponent to handle real-timedatae%!han'e( I ha)e not personnaly used it yet, but you shouldloo5 intoit( I thin5 it is !alled 6T7(

(( Support for 196:A/XM(>(>((Informati!a?( oes not support(>((ataSta'e? oes not support

*0 Metadata*(>( Ability to )ie" 2 na)i'ate metadata on the "eb

Page > of 243


97/243

*(>(>((Informati!a?( oes not support*(>((ataSta'e? ;ob sessions !an be monitored usin' Informati!a1lasses

This is !ompletely not true( S has a )ery stron' metadata!omponent.MetaSta'e0 that "or5s not only "ith S, but also hasplu'-ins to "or5

"ith modelin' tools .li5e 76Win0 and :I tools .li5e 1o'nos0( This isoneof their stron' suits .a'ain, IM( Ability to 1ustomi3e )ie"s of metadata for different users .:A=s:usiness user0*(>(>((Informati!a?( Supports(*(>((ataSta'e? Not A)ailable

Also not true - MetaSta'e allo"s publishin' of metadata in ( Metadata repository !an be stored in 6:MS

*(>(>((Informati!a?( $es*(>((ataSta'e? No( :ut the proprietary meta data !an be mo)edto a6:MS usin' the 91 Tool

B0 Support And Maintenan!eB(>( 1ommand line operationB(>(>((Informati!a?( Pm!md -ser)er interfa!e for !ommand lineB(>((ataSta'e? Not A)ailable

B(( Ability to maintain )ersions of mappin'sB(>(>((Informati!a?( $esB(>((ataSta'e? No

Not true - this has been a "ea5 spot for S in past releases, butthe8(% )ersion of S has a 'ood )ersionin' tool(

80 ;ob 1ontrollin' 2 S!hedulin'8(>( Alerts li5e sendin' mails8(>(>((Informati!a?( Supported(8(>((ataSta'e? oes not support dire!tly . no option0( :utpossibleto !all !ustom pro'rams after the ob 'et e%e!uted0

&urther mista5es in your !omparison, mainly from a ataSta'ebased an'le as my e%perien!e is "ith that produ!t?

:oth ataSta'e and Informati!a support XM( ataSta'e!omes "ith XM input, transformation and output sta'es(

:oth produ!ts ha)e an unlimited number of transformationfun!tions sin!e you !an easily "rite your o"n usin' the!ommand interfa!e(

:oth produ!ts ha)e options for inte'ratin' "ith 76Psystems su!h as SAP, PeopleSoft and Seibel but these!ome at a si'nifi!ant e%tra !ost( $ou may need to e)aluate

Page >; of 243


98/243

these( SAP is a reseller of ataSta'e for SAP :W,PeopleSoft bundles ataSta'e in its 7PM produ!ts(

ataSta'e has some )ery 'ood debu''in' fa!ilitiesin!ludin' the ability to step throu'h a ob lin5 by lin5 or ro"by ro" and "at!h data )alues as a ob e%e!utes( Alsoser)er side tra!in'(

ataSta'e 8(% releases ha)e intelli'ent assistants ."i3ards0for !reatin' the template obs for ea!h type of slo"ly!han'in' dimension table loads( The ataSta'e :estPra!ti!es !ourse also pro)ides trainin' in W loadin' "ithS1 and surro'ate 5ey te!hni#ues(

As!ential and Informati!a both ha)e robust metadatamana'ement produ!ts( As!ential MetaSta'e !omesbundled free "ith ataSta'e 7nterprise and mana'esmetadata )ia a hub and spo5e ar!hite!ture( It !an importmetadata from a "ide ran'e of databases and modellin'tools and has a hi'h de'ree of intera!tion "ith ataSta'efor operational metadata( Informati!a SuperClue "asreleased last year and is rated more hi'hly by Cartner inthe metadata field( It inte'rates !losely "ith Po"er1enter

produ!ts( They both support multiple )ie"s .business andte!hni!al0 of metadata plus the fun!tions you "ould e%pe!tsu!h as impa!t analysis, semanti!s and data linea'e(

ataSta'e !an send emails( The se#uen!e ob has anemail sta'e that is easy to !onfi'ure( ataSta'e 8(* alsohas ne" mobile de)i!e support so you !an administer yourataSta'e obs )ia a palm pilot( There are also @rd party"eb based tools that let you run and re)ie" obs o)er abro"ser( I found it easy to send sms admin messa'es froma ataSta'e Uni% ser)er(

ataSta'e has a !ommand line interfa!e( The dsob!ommand !an be used by any s!hedulin' tool or from the!ommand line to run obs and !he!5 the results and lo's of

obs( :oth produ!ts inte'rate "ell "ith Trillium for data #uality,

ataSta'e also inte'rate "ith DualitySta'e for data #uality(This is the preferred method of address !leansin' and

fu33y mat!hin'(

Milind - I4)e 'ot to as5 - "here are you 'ettin' your informationfromEEE I ha)e done 7T tool !omparisons for se)eral !lients o)erthe past 8 or so years( They are both 'ood tools "ith differentstren'ths so it really depends on "hat your or'ani3ations needs /priorities are as to "hi!h one is FbetterF( I ha)e spent mu!h moretime in the past !ouple of years on S than IN&A so I don4t feel I!an spea5 to the !han'es IN&A has made lately, but I 5no" you

ha)e in!orre!t info about S(

I am !urrently "or5in' "ith a !lient on S )8(>( I4)e made a fe"!omments belo" for the more 'larin' ina!!ura!ies or topi!s "here Iha)e up-to-date e%perien!e( I su''est you re-resear!h and perhapsdo a proof-of-!on!ept "ith ea!h )endor(

&$I - I don4t 5no" if you ha)e loo5ed at the Parallel 7%tender!omponent of S 8(%, but it is a terrifi! !apability if you ha)e

Page >= of 243


99/243

!hallen'es "ith meetin' a)ailability re#uirements( It is one of themost impressi)e !han'es As!ential has made lately .IMo" m$ *&&/ #o&$("$#& #8&,&. SAP ), $ r&,&((&r of D$#$S#$%& for SAPW P&o+(&Sof# !"*/(&, D$#$S#$%& )* )#, EP+ro/"-#,.

- D$#$S#$%& 8$, ,om& &r %oo/ /&!"%%)*% f$-)()#)&,

)*-("/)*% #8& $!)()# #o ,#&+ #8ro"%8 $ o! ()*< ! ()* of 243


110/243

FT233; RB 233;

Date *on$ersions

T8& fo((oC)*% &?$m+(&, ,8oC #8& &ff&-# of $r)o", DD$#&B-o*&r,)o* -o/&,.

Co(:e5s$o( E765ess$o( I(#e5(a% 8a%-e

I-o*31 DE' 1;6 DB 0

I-o*2 A> ; D2B 1040

I-o*05J2J; D2JB 1040

I-o*2J05J1;; DJEB 1040

I-o*1;; 5 2 D >DB 1040

I-o*2 A> ; DD>A32LB

1040

I-o*5J2J; DJD>YY2LB 1040

I-o*2 A> 1;; DD>ALB

1040

I-o*; 05 2 D>D222LB 1040

Date *on$ersions

T8& fo((oC)*% &?$m+(&, ,8oC #8& &ff&-# of $r)o", DD$#&B-o*&r,)o* -o/&,.

Co(:e5s$o( E765ess$o( E7#e5(a% 8a%-e

O-o*0 DB 31 DE' 1;6

O-o*1040 D2B 2 A> ;

O-o*1040 D2JB 05J2J;

Page 11? of 243


111/243

O-o*1040 DJEB 2J05J1;;

O-o*1040 D>B 1;;14

O-o*1040 D2\>B 14\;

O-o*1040 D >DB 1;; 5 2

O-o*1040 D>A2LB

A> ;

O-o*1040 DD>A32LB

2 A> ;

O-o*1040

DJD>YY2LB

5J2J;

O-o*1040 DD>ALB

2 A> 1;;

O-o*1040D>D222LB

; 05 2

O-o*1040 DQB 2

O-o*1040 DAB A>

O-o*1040 DWB 2

O-o*1040 DWAB TUESDA>

O+&*S&9 .'o*#ro(F)(&,F)(&1 To P$#8F$r o-


112/243

DataStage Managerwill automatically ensure this when

you createa new before/after routine.

S"!ro"#)*& Ro"#)*&I*+"#Ar% Error'o/&B\ U,&r, -$* &*#&r $* ,#r)*% $("& #8& ()


113/243

(ase -rg 4

Reply 4 3-3(ase -rg 4 /Reply 4 3=3(ase -rg 6 / -nd -rg @ Reply 4 3(3(ase DTrue ;2 all other values

(all &Transform%rror3=ad arg3'-rg, 3+yTransform3Reply 4 33%nd (ase

ReturnReplyB

DATASTAGE 67891 GUI FEATURES

Ne a(' E76a('e' F-()#$o(a%$#0 #o a$' Da#aS#age -se5s

$( o+ 'es$g( a(' 'e+-gg$(g

Ne S#o5e' P5o)e'-5e S#age

A *&C ,#or&/ +ro-&/"r& ,#$%& $((oC, ",&r, #o &$,)( ",&Or$-(& ,#or&/ +ro-&/"r&, Cr)##&* )* PJSQ )$ O'I. T8&S#or&/ Pro-&/"r& S#$%& ,"++or#, )*+"# $*/ o"#+"#+$r$m&r, m$


114/243

!& +ro/"-&/ ",)*% #8& *&C m&*" o+#)o* )* D&,)%*&r= F)(& G&*&r$#& R&+or#. T8& f)*$( HT r&+or# -$* !& -",#om)&/ !$++()*% /)ff&r&*# S ,#(& ,8&, #o #8& %&*&r$#&/ f)(&.

C"a(ges #o F$%e & D$5e)#o50 5ose5 Fo5*

T8& o(/ ,#(& F)(& @ D)r&-#or !roC,&r form 8$, !&&* r&+($-&/

C)#8 o*& mo/&(&/ o* #8& ,#$*/$r/ W)*/oC, 2K !roC,&r. T8&*&C !roC,&r +ro)/&, &*8$*-&/ f"*-#)o*$()# o* /)r&-#or*$)%$#)o* #r&&or)&*#&/B $*/ f)(& ,&(&-#)o* )# 8$, f)(#&r)*% $,C&(( $, ,$)*% $*/ r&,#or)*% -$+$!)()#)&, for #8& ($,# )&C&/ f)(&(),#.

A+$%$#0 #o g%o+a%%0 se# A((o#a#$o( 65o6e5#$es

T8& A**o#$#)o* +ro+&r#)&, /)$(o% ), +r&,&*#&/ )* #8& Too(, O+#)o*, /)$(o% $*/ #8& ,#)*%, $r& ,$&/ )* #8& r&%),#r +&r",&r. T8& A**o#$#)o* ,#$%& $(C$, /&f$"(#, #o #8& ,$&/,#)*%,.

A+$%$#0 #o -(se# E(:$5o(*e(# 8a5$a+%es "e( a


115/243

E(#e565$se E'$#$o(

Co*6%e7 F%a# F$%e S#age

A *&C P$r$((&( 'om+(&? F($# F)(& ,#$%& 8$, !&&* $//&/ #o r&$/or Cr)#& f)(&, #8$# -o*#$)* -om+(&? ,#r"-#"r&, for &?$m+(&%ro"+, $rr$, r&/&f)*&, o--"r, /&+&*/)*% o* -.B. Arr$,from -om+(&? ,o"r-& -$* !& +$,,&/ $,), or o+#)o*$(( f($##&*&/or *orm$()&/.

Pa5a%%e%


116/243

RoC N"m!&r 'o("m* o+#)o*$(B 7 $//, $ -o("m* #o #8&,#$%& o"#+"# #8$# -o*#$)*, #8& roC *"m!&r of #8& r&-or/.A$)($!(& o* S&9"&*#)$( F)(& F)(& S $*/ E?#&r*$( So"r-&S#$%&,.

R&$/ F)r,# RoC, o+#)o*$(B 7 -o*,#r$)*, #8& ,#$%& #o o*(r&$/ #8& ,+&-)f)&/ *"m!&r of roC, from &$-8 f)(&.

A$)($!(& o* S&9"&*#)$( F)(& S#$%&. F)r,# )*& ), 'o("m* N$m&, m$*/$#orB 7 o* r&$/)*% #8),

#&((, #8& ,#$%& #o )%*or& #8& f)r,# ()*& ,)*-& )# -o*#$)*,-o("m* *$m&,. O* Cr)#)*% )# -$",&, #8& f)r,# ()*& Cr)##&* #o!& #8& -o("m* *$m&,. A$)($!(& o* S&9"&*#)$( F)(& S#$%&.

8$e Da#a f-()#$o(a%$#0 o( #"e So-5)e & Ta5ge# )-s#o*

s#ages

V)&C /$#$ ,"++or# C$, $//&/ #o -",#om +$r$((&( ,#$%&, for!o#8 ,o"r-& $*/ #$r%,.

ZS8oC f)(&[ 8$/ !&&* r&+($-&/ C)#8 ZV)&C D$#$[ forP$r$((&( o! S&9"&*#)$( F)(& $*/ F)(& S ,#$%&,.

Ne Pa5a%%e%


117/243

DATASTAGE 4 DWH INTERVIEWQUESTIONS

COPAN TCS Da#aS#age

1. T&(( $!o"# o"r,&(f2. T+&, of S#$%&, E?$m+(&,3. W8$# $r& $-#)& ,#$%&, $*/ +$,,)& ,#$%&,4. '$* o" f)(#&r /$#$ )* 8$,8&/ f)(& NoB5. D)ff&r&*-& !C&&* ,&9"&*#)$( $*/ 8$,8&/ f)(&6. HoC /o o" +o+"($#& #)m& /)m&*,)o*. '$* C& ",& #$r% 8$,8&/ f)(& $, (oo


118/243

30. HoC /o o" (o$/ /)m&*,)o* /$#$ $*/ f$-# /$#$ W8)-8 ),f)r,#

31. A* )/&$ $!o"# ,8&(( ,-r)+#)*% $*/ UNI32. D)ff&r&*-& !C&&* or$-(& f"*-#)o* $*/ +ro-&/"r&33. D)ff&r&*-& !C&&* "*)9"& $*/ +r)m$r


119/243

2:. W8$# ), S*oCf($


120/243

COPAN SNTEL9 -*+a$ Da#aS#age J Te%e6"o($)

I(#e5:$e

as$) D!/

1. T&(( m& $!o"# o"r -"rr&*# +ro&-#2. W8$# ), o"r ro(& or o! +rof)(& )* #8& +ro&-#3. W8$# ), o"r o! +rof)(&4. W8$# ), /)m&,)o* $*/ f$-#

5. W8$# $r& #+&, of /)m&*,)o*,6. W8$# $r& -o*f)rm&/ /)m&*,)o*,. W8$# $r& %&*&r$#&/ /)m&*,)o*,:. W8$# $r& ,(oC( -8$*%)*% /)m&*,)o*,;. HoC m$* /$#$ m$r#, )* o"r +ro&-#10. W8$# ), /$#$ m$r# *$m& )* o"r +ro&-#11. W8$# ), #8& ,)& of o"r /$#$ m$r#12. W8$# ), f$-#(&,, f$-# #$!(& G)& &?$m+(&.13. HoC m$* f$-# #$!(&, $r& ",&/ )* #8& +ro&-#14. W8$# ), o"r f$-# #$!(& *$m& )* o"r +ro&-#15. HoC m$* /)m&*,)o* #$!(&, ",&/ )* #8& +ro&-#16. W8$# $r& #8& *$m&, of #8& /)m&*,)o* #$!(&,

1. W8$# ), S-8&m$ T+&, E?+($)* S#$rS-8&m$ $*/S*oCf($


121/243

24. W8$# $r& S'D $*/ SGT D)ff&r&*-& !C&&* #8&mE?$m+(& of SGT from o"r +ro&-#.

25. HoC /o o" r$#& o"r,&(f )* /$#$ C$r&8o",&26. W8$# ), #8& ,#$#", of o"r -"rr&*# +ro&-#

Da#aS#age

2. HoC /o o" )m+or# o"r ,o"r-& $*/ #$r%, W8$# $r& #8+&, of ,o"r-&, $*/ #$r%,

2:. W8$# ), A-#)& S#$%&, $*/ P$,,)& S#$%&, m&$*, )*/$#$,#$%&

2;. W8$# ), /)ff&r&*-& !C&&* I*form$#)-$ $*/ D$#$S#$%&W8)-8 /o o" #8)*< ), !&,#

30. W8$# $r& #8& ,#$%&, o" ",&/ )* o"r +ro&-#31. W8om /o o" r&+or#32. W8$# ), or-8&,#r$#& D)ff&r&*-& !C&&* or-8&,#r$#& $*/

/$#$,#$%&33. W8$# ), +$r$((&( &?#&*/&r H$/ o" Cor< o* #8),

34. W8$# /o o" m&$* ! +$r$((&( +ro-&,,)*%35. W8$# ), /)ff&r&*-& !C&&* &r%& S#$%& $*/ o)* S#$%&36. W8$# ), /)ff&r&*-& !C&&* 'o+ S#$%& $*/ Tr$*,form&r

S#$%&3. W8$# ), /)ff&r&*-& !C&&* OD' S#$%& $*/ O'I S#$%&3:. W8$# ), /)ff&r&*-& !C&&* oo


122/243

5:. W8$# ), ,#$#", )&C W8 o" -(&$r #8), If o" -(&$r #8&,#$#", )&C C8$# )*#&r*$(( /o*&

5;. W8$# ), 8$,8&/ f)(& W8$# $r& #8& #+&, of 8$,8&/ f)(&W8)-8 o" ",& W8$# ), /&f$"(# W8$# ), m$)* $/$*#$%&of 8$,8&/ f)(& D)ff&r&*-& !C&&* #8&m. ,#$#)- $*//*$m)-B

60. W8$# $r& -o*#$)*&r, G)& &?$m+(& from o"r +ro&-#.61. H$/ o" /o*& $* 8$r/C$r& -o*f)%"r$#)o* C8)(& r"**)*%

+$r$((&( o!,62. W8$# $r& o+&r$#or, )* +$r$((&( o!,63. W8$# $r& +$r$m&r, $*/ +$r$m&r f)(&64. '$* o" ",& $r)$!(&, I* C8)-8 ,#$%&,65. HoC /o o" -o*&r# -o("m*, #o roC, $*/ roC, #o -o("m*,

)* /$#$,#$%& U,)*% P)o# S#$%&B.66. W8$# ), P)o# S#$%&6. W8$# ), &?&-"#)o* f(oC of -o*,#r$)*#, /&r)$#)o*, $*/

$r)$!(&, )* #r$*,form&r ,#$%& W8$# $r& #8&,&6:. HoC /o o" &()m)*$#& /"+()-$#&, )* /$#$,#$%& '$* o" ",&

8$,8 f)(& for )#6;. If 1,#$*/ :#8r&-or/ ), /"+()-$#& #8&* C8)-8 C)(( !& ,


123/243

4. W8$# $r& #8& /)ff&r&*# /$#$,#$%& f"*-#)o*, ",&/ )* o"r+ro&-#

5. D)ff&r&*-& !C&&* ,#$r ,-8&m$ $*/ ,*oC f($


124/243

COPAN I9 a(ga%o5e Da#aS#age J Te%e6"o($)

I(#e5:$e

1. T&(( m& $!o"# o"r &/"-$#)o*$( $*/ +rof&,,)o*$(

!$-o" /o o"r,&(f #8& -om+(&? (o%)- or ,om&o*& %)& o" #8&

,+&-)f)-$#)o*, $*/ o" -o*&r# #8&m #o /$#$,#$%&

:. W8$# $r& #8& ,o"r-&, o" ",&/ )* o"r +ro&-#;. W8$# $r& #8& ,#$%&, o" ",&/ )* o"r +ro&-#10. W8$# ), /)ff&r&*-& !C&&* OD' $*/ ORA'E O'I

,#$%&11. A, o" #o(/ )f o"r ,o"r-&, $r& f($# f)(&, $*/ ORA'E

O'I #8&* C8 o" *&&/ OD' )* o"r +ro&-# r$#8&r #8$*ORA'E O'I ,#$%&

12. W8$# /)ff&r&*-& !C&&* ,&9"&*#)$( f)(& $*/ 8$,8&/ f)(&13. '$* o" ",& ,&9"&*#)$( f)(& $, ,o"r-& #o 8$,8&/ f)(& H$&

o" /o*& )# W8$# &rror )# C)(( %)&14. W8 8$,8&/ f)(& )m+ro& #8& +&rform$*-&15. HoC /o o" ,or# o"r /$#$ )* o!,

16. H$/ o" ",& ,or# ,#$%& )* o"r o! ,or# ,#$%& ), +$r$((&(,#$%& !& ,"r& #8$# o" $r& ",)*% ,&r&r o!, o*( #8&* 8&C)(( $,< Q.12B

1. '$* $%%r&%$#or $*/ #r$*,form&r ,#$%& ",&/ for ,or#)*% /$#$HoC

1:. If I 8$& #Co ,o"r-&, #o $%%r&%$#or ,#$%& $*/ or$-(& $,#$r% I -$* ,or# /$#$ )* $%%r&%$#or !"# )f I /o*# C$*# #o",& $%%r&%$#or #o ,or# /$#$ #8&* 8oC o" C)(( /o )#

1;. W8 C& ",& ,"rro%$#&


125/243

#$!(& )f )# ), "*/&r #+&3 r)%8# Or *o -8$*%&, HoC /oo" )m+(&m&*# #8),B

24. HoC m$* /)m&*,)o*, )* o"r +ro&-# W8$# $r& #8&25. W8$# $r& #8& f$-#, )* o"r f$-# #$!(&26. Ar& $(( #8&,& f$-#, $r& ,+&-)f)- r&($#&/B #o $(( /)m&*,)o*,2. HoC /o o" % ,,#&m /$#& )* or$-(&

2:. W8$# ), $ /"$( #$!(& )* or$-(&2;. W8$# ), #8& ",& of UNION )* or$-(& If I Cr)#& 9"&r ,&(&-#

\ from EP UNION ,&(&-# \ from /&+# ), )# &?&-"#&/C&((

30. I 8$& $ 9"&r ,&(&-# \ from EP #$!(& %ro"+ ! /&+# ),#8), 9"&r &?&-"#&/ If *o C8$# ), #8& &rror

ORE QUESTIONS ON DATASTAGE

1. W8$# $r& #8& /)ff)-"(#)&, f$-&/ )* ",)*% D$#$S#$%&2. W8$# $r& #8& -o*,#r$)*#, )* ",)*% D$#$S#$%&3. HoC /o o" &()m)*$#& /"+()-$#& roC,4. HoC /o C& /o #8& $"#om$#)o* of /,o!,5. W8$# $r& f)(&, HoC /o o" r&$/ /$#$ from f)(&,

$*/ C8)-8 ,#$%& #o !& ",&/6. HoC /o o" -$#-8 !$/ roC, from O'I ,#$%&. W8 /o o" ",& SQ OADER or O'I STAGE:. HoC /o o" +o+"($#& ,o"r-& f)(&,;. HoC /o o" +$,, f)(&*$m& $, #8& +$r$m&r for $ o!10. HoC /o o" +$,, #8& +$r$m&r #o #8& o! ,&9"&*-& )f #8& o! ),

r"**)*% $# *)%8#11. W8$# 8$++&*, )f #8& o! f$)(, $# *)%8#12. W8$# ), SQ #"*)*% HoC /o o" /o )#13. W8$# ), +ro&-# ()f& --(& $*/ 8oC /o o" )m+(&m&*# )#14. HoC C)(( o" -$(( &?#&r*$( f"*-#)o* or ,"!ro"#)*& from

/$#$,#$%&15. HoC /o o" #r$-< +&rform$*-& ,#$#),#)-, $*/ &*8$*-& )#16. HoC /o o" /o or$-(& 4 C$ )**&r o)* )f #8&r& $r& 4 or$-(&

)*+"# f)(&,1. E?+($)* o"r ($,# +ro&-# $*/ o"r ro(& )* )#1:. W8$# $r& #8& of#&* ",&/ S#$%&, or ,#$%&, o" CorJQUAIT> ,#$%&2. Do o"


126/243

2;. W8$# ), )-o* $*/ o-o* f"*-#)o*,30. HoC -$* C& o)* o*& Or$-(& ,o"r-& $*/ S&9"&*#)$( f)(&31. HoC -$* C& )m+(&m&*# S(oC( '8$*%)*% D)m&*,)o*, )*

D$#$S#$%&32. HoC -$* C& )m+(&m&*# oo


127/243

1:. W8$# ), #8& $/$*#$%& of ",)*% O'I ,#$%& $, -om+$r&/ #oOD' ,#$%&

1;. Norm$()&r Tr$*,form$#)o*..20. C8$# ,#&+, C)(( o" #$


128/243

21. W8$# ), #8& /)ff&r&*-& !C&&* -(&$r(o%f)(& '(&$r,#$%&f)(&22. HoC #o ,-&8"/(& o!, C)#8 o"# ",)*% D$#$ ,#$%&23. W8$# ), #8& /)ff&r&*-& !C&&* S#$#)-8$,8 $*/

/*$m)-8$,8f)(&24. HoC #o /o &rror 8$*/()*% )* /$#$ ,#$%&25. W8$# ), #8& /)ff&r&*-& !C&&* A-#)& ,#$%& $*/ +$,,)& ,#$%&

W8$# $r& #8& A-#)& $*/ +$,,)& ,#$%&,26. HoC #o , E*)ro*m&*# $r)$!(&, )* /$#$,#%&2. W8$# ), o! -o*#ro((&/ ro"#)*"& HoC , o! +$r$m&r )* D$#$

,#$%&2:. HoC #o r&(&$,& $ o!2;. HoC #o /o A"#o+"r%& )* D$#$ ,#$%&30. W8$# ), #8& /)ff&r&*-& !C&&* D$#$,#%&.1 $*/ .5

DATA !ARE/OUSING QUESTIONS

1. W8$# $r& #8& /)ff&r&*# D)m&*,)o*$( mo/&()*% T&-8*)9"&,$r& A$)($!(&

2. W8$# ), #8& D)ff&r&*-& !C&&* S#$r,-8&m$ $*/ ,*oCf($


129/243

30. W8$# ), /)ff&r&*-& !C&&* /$#$ m$r# $*/ /$#$ C$r&8o",&31. W8$# ), -om+o,)#&


130/243

A /)m&*,)o* #$!(& -o**&-#, #o mor& #8$* o*& f$-# #$!(&.

W& +r&,&*# #8), ,$m& /)m&*,)o* #$!(& )* !o#8 ,-8&m&,$*/ C& r&f&r #o /)m&*,)o* #$!(& $, -o*form&/ /)m&*,)o*.

Co(fo5*e' fa)#

D&f)*)#)o*, of m&$,"r&m&*#, f$-#,B $r& 8)%8( -o*,),#&*#

C& -$(( #8&m $, -o*form&/ f$-#.


131/243

T06es of fa)#s

A''$#$:e f$-#, )*o(&/ )* #8& -$(-"($#)o*, for /&r))*%

,"mm$r)&/ /$#$.

Se*$ a''$#$:e f$-#, #8$# )*o(&/ )* #8& -$(-"($#)o*, $# $

+$r#)-"($r -o*#&?# of #)m&.

No( a''$#$:e f$-#, #8$# -$**o# )*o(&/ )* #8&-$(-"($#)o*, $# &&r +o)*# of #)m&.

DATASTAGE ROUTINES

L

OT :23? Re#-5(s LAN@ $f 6asse' :a%-e $s NOT

NULL o5 LAN@9 af#e5 #5$**$(g s6a)es

DataIn = "":Trim(Arg1)

If IsNull(DataIn) or DataIn = "" Then

Ans = ""

End Else

Ans = DataIn

End

C"e)F$%eRe)o5's

F-()#$o( C"e)F$%eRe)o5'sA5g19A5g2

vParamFile = Arg1 : "/" : Arg2

vCountVal = 0

OpenSeq vParamFile To FileVar Else

Call DSLogWarn("Cannot open ":vParamFile ,

"Cannot Open ParamFile")

End

Loop

ReadSeq Dummy From FileVar Else Exit ;* at

end-of-file

vCountVal = vCountVal + 1

Repeat

CloseSeq FileVar

Ans=vCountVal

Return (vCountVal)

CheckFileSizes:

Page 131 of 243


132/243

DIR =

"/interface/dashboard/dashbd_dev_dk_int/Source/"

FNAME = "GLEISND_OC_02_20040607_12455700.csv"

*CMD = "ll -tr ":DIR:"|grep ":FNAMECMD = "cmp -s ":DIR:"|grep ":FNAME

Call DSExecute("UNIX", CMD, Output,

SystemReturnCode)

Ans = Output

C"e)I'o)sSe(#

C"e)s If I'o) 'e%$:e50 o+ a)#-a%%0 se(# a(0 I'o)s #o SAP

T"$s 5o-#$(e $%% a#e*6# #o 5ea' #"e Da#aS#age D$5e)#o5%og fo5 #"e o+ (a*e s6e)$f$e' as a( a5g-*e(#

If #"e o+ "as a fa#a% e55o5 $#" No %$( f$%e9 #"e 5o-#$(e

$%% )o60 #"e IDOC %$( f$%es $(#o #"e $(#e5fa)e e55o5

fo%'e5

I( )ase #"e fa#a% e55o5 a+o:e $s (o# fo-(' #"e 5o-#$(e

a+o5#s #"e o+

A s$*6%e %og of "$)" 5-(s 65o'-)e e55o5 %$( f$%e $s

*a$(#a$(e' $( #"e *o'-%eKs %og '$5e)#o50

$INCLUDE DSINCLUDE JOBCONTROL.H

vRoutineName = "CheckIdocsSent"

Ans = "Ok"

If System(91) Then

OsType = 'NT'

OsDelim = '\'

NonOsDelim = '/'

Move = 'move '

End Else

OsType = 'UNIX'

OsDelim = '/'

NonOsDelim = '\'

Move = 'mv -f '

End

vJobHandle = DSAttachJob(JobName,

DSJ.ERRFATAL)

vLastRunStart = DSGetJobInfo(vJobHandle,

DSJ.JOBSTARTTIMESTAMP)

vLastRunEnd = DSGetJobInfo(vJobHandle,

DSJ.JOBLASTTIMESTAMP)

Page 132 of 243


133/243

* Get the delivery log for the last run

vLogSummary = DSGetLogSummary ( vJobHandle,

DSJ.LOGANY, vLastRunStart, vLastRunEnd, 500)

vLogSummary = Change(vLogSummary,@FM,'')

* Manipulate vLogSummary within routine to returnstatus

PosOfStr =

Index(Downcase(vLogSummary),"sent",1)

vLogMsg = vLogSummary[PosOfStr,20]

* Now work out Status

If PosOfStr = 0 then

Status = 'NOT SENT'

vLogMsg = ''

end else

Status = 'SENT'

vLogMsg = vLogSummary[PosOfStr,20]

end

Ans = Status

vErr = DSDetachJob(vJobHandle)

Call DSLogInfo("Job " : JobName : "

Detached" , vRoutineName)

***** Make a log entry to keep track of how often

the pack doesn't work ********

vMessageToWrite = Fmt(Module_Run_Parm, "12'

'L") : Fmt(Status, "10' 'L") : " - " : vLogMsg

vIdocLogFilePath =

Interface_Root_Path_Parm: OsDelim : "logs" :

OsDelim : "IdocSentLog.log"

******** Open the log file

OPENSEQ vIdocLogFilePath TO vIdocLogFile

Then

Call DSLogInfo("IdocSentLog Open" ,

vRoutineName)

** Label to return to if file is created

FileCreated:

*** Write the log entry

vIsLastRecord = @Null

Loop Until vIsLastRecord Do READSEQ vRecord From vIdocLogFile Then

*Call DSLogInfo("Record Read - " :

vRecord , vRoutineName)

End Else

*Call DSLogInfo("End of file

reached " , vRoutineName)

vIsLastRecord = @True

End

Page 133 of 243


134/243

Repeat

WRITESEQ vMessageToWrite To vIdocLogFile

Then

Call DSLogInfo("Log entry created : "

: vMessageToWrite, vRoutineName)

End Else

Call DSLogFatal("Cannot write to " :vIdocLogFilePath, vRoutineName)

End

End Else

Call DSLogInfo("Could not open file -

" : vIdocLogFilePath , vRoutineName)

Call DSLogInfo("Creating new file - " :

vIdocLogFilePath , vRoutineName)

CREATE vIdocLogFile ELSE Call

DSLogFatal("Could not create file - " :


WEOFSEQ vIdocLogFile

WRITESEQ Fmt("Module Run", "12' 'L") :

Fmt("Status", "10' 'L") : " " : "Message" TovIdocLogFile Else ABORT

Call DSLogInfo("Log file created : " :


GOTO FileCreated

End

**** Abort the delivery sequence and write error

message to the log. ************

If Status = 'NOT SENT' Then

Call DSLogInfo("No Idocs were actually

sent to SAP - Trying to clean up IDOC Link Files:

", vRoutineName)

vIdocSrcLinkPath =Field(Interface_Root_Path_Parm, OsDelim, 1, 4) :

OsDelim : "dsproject" : OsDelim :

Field(Interface_Root_Path_Parm, OsDelim, 4, 1)

vIdocTgtLinkPath =

Interface_Root_Path_Parm: OsDelim : "error"

OsCmd = Move : " " : vIdocSrcLinkPath :

OsDelim : JobName : ".*.lnk " :

vIdocTgtLinkPath : OsDelim

Call DSExecute(OsType, OsCmd, OsOutput,

OsStatus)

If OsStatus 0 Then

Call DSLogWarn("Error when trying to

move link file(s)", vRoutineName)

LogMessMoveFail = 'The move command(':OsCmd:') returned status

':OsStatus:':':@FM:OsOutput

Call DSLogWarn(LogMessMoveFail,

vRoutineName)

Call DSLogFatal("Cleaning up of IDOC

Link Files failed", vRoutineName)

End

Else

Page 134 of 243


135/243

LogMessMoveOK = "Link files were

moved to " : vIdocTgtLinkPath

Call DSLogInfo(LogMessMoveOK,

vRoutineName)

LogMessRetry = "Job " : JobName : "

is ready to be relaunched."

Call DSLogInfo(LogMessRetry,

vRoutineName) End

End Else

Call DSLogInfo("Delivery job log

indicates run OK ", vRoutineName)

End

C%ea5a66$(gTa+%e

SUBROUTINE ClearMappingTable

(Clear_Mapping_Table, Errorcode)

Error Code = 0 ;* set this to non-zero tostop the stage/job

**If Clear_Mapping_Table_Parm = 'Y' Then

EXECUTE "CLEARFILE Vendor_Map_HF.GEN"

**End Else

**End

ComaDotRmv:

DataIn = "":(Arg1)

If IsNull(DataIn) or DataIn = "" Then

Ans = "" End Else

DataIn = Ereplace(DataIn, ".", "")

DataIn = Ereplace(DataIn, ",", "")

Ans = DataIn

End

CopyFiles:

Move files from one directory to another

Function

CopyofFiles(sourceDir,SourceFileMask,TargetDir,TargetFileMask,Flags)

RoutineName = "CopyFiles"

If SourceDir = '' Then SourceDir = '.'

If TargetDir = '' Then TargetDir = '.'

Page 13 of 243


136/243

If SourceFileMask = '' Or SourceDir =

TargetDir Then Return(0)

! If SourceDir # '.' Then

! OpenPath SourceDir To Fv Else

! Call

DSU.DSMkDir(MkStatus,SourceDir,'','777')

! End! End

! If TargetDir # '.' Then

! OpenPath TargetDir To Fv Else

! Call

DSU.DSMkDir(MkStatus,TargetDir,'','777')

! End

! End

If System(91) Then

OsType = 'NT'

OsDelim = '\'

NonOsDelim = '/'

Copy = 'copy '

Flag = Flags End Else

OsType = 'UNIX'

OsDelim = '/'

NonOsDelim = '\'

Copy = 'cp -f '

End

If Flags "" then Flag = NonOsDelim:Flags

Else Flag = ""

SourceWorkFiles =

Trims(Convert(',',@FM,SourceFileMask))

SourceFileList =

Splice(Reuse(SourceDir),OsDelim,SourceWorkFiles)

Convert NonOsDelim To OsDelim In SourceFileList

TargetWorkFiles =

Trims(Convert(',',@FM,TargetFileMask))

TargetFileList =

Splice(Reuse(TargetDir),OsDelim,TargetWorkFiles)

Convert NonOsDelim To OsDelim In TargetFileList

OsCmd = Copy:' ' : Flag : " "

:SourceFileList:' ':TargetFileList

Call DSLogInfo('Copying ': SourceFileList: 'to ':TargetFileList,RoutineName)

Call DSExecute(OsType,OsCmd,OsOutput,OsStatus)

If OsStatus Then

Call DSLogWarn('The Copy command

(':OsCmd:') returned status

':OsStatus:':':@FM:OsOutput, RoutineName)

End Else

Call DSLogInfo('Files

moved...','DSMoveFiles')

Page 13 of 243


137/243

End

Ans = OsStatus

CopyofComareROWS:

FunctioncopyofcompareRows(Column_Name,Column_Value)


vJobName=DSGetJobInfo(DSJ.ME, DSJ.JOBNAME)

vStageName=DSGetStageInfo(DSJ.ME, DSJ.ME,

DSJ.STAGENAME)

vCommonName=CheckSum(vJobName) :

CheckSum(vStageName) : CheckSum(Column_Name)

Common /vCommonName/ LastValue

vLastValue=LastValue

vNewValue=Column_Value

If vNewValuevLastValue Then Ans=1 Else

Ans=0

LastValue=vNewValue

CopyOfZSTPKeyLookup

C"e) $f e0 6asse' e7$s#s $( f$%e 6asse'

A5g1/as" f$%e #o %oo $(

A5g2@e0 #o %oo fo5A5g3N-*+e5 of f$%e #o -se 1 o5 2

* Routine to look to see if the key passed exists

in the file passed

* If so, then the non-key field from the file is

returned

* If not found, "***Not Found***" is returned

*

* The routine requires the UniVerse file named to

have been created previously

*


EQUATE RoutineName TO 'ZSTPKeyLookup'

* Call DSLogInfo("Routine started",RoutineName)

Common /ZSTPkeylookup/ Init1, SeqFile1,

Init2, SeqFile2, RetVal, msgtext

Ans = 0

Page 13; of 243


138/243

If NOT(Init1) And Arg3 = "1" Then

* Not initialised. Therefore open file

Init1 = 1

Open Arg1 TO SeqFile1 Then Clearfile

SeqFile1

Else

Call DSLogInfo("Open failed1",RoutineName)

msgtext = "Cannot open ZSTP creation

control file ":Arg1

Call DSLogFatal(msgtext,RoutineName)

Ans = -1

End

End

If NOT(Init2) And Arg3 = "2" Then

* Not initialised. Therefore open file

Init2 = 1

Open Arg1 TO SeqFile2 Then Clearfile

SeqFile2

Else Call DSLogInfo("Open failed

2",RoutineName)

msgtext = "Cannot open ZSTP creation

control file ":Arg1

Call DSLogFatal(msgtext,RoutineName)

Ans = -1

End

End

* Read the file to get the data for the key

passed, if not found, return "***Not Found***"

If Arg3 = "1"

Then

Read RetVal From SeqFile1, Arg2 ElseRetVal = "***Not Found***"

End

Else

Read RetVal From SeqFile2, Arg2 Else

RetVal = "***Not Found***"

End

Ans = RetVal

C5ea#e12C"a5TS

F-()#$o( C5ea#e12C"a5TS


139/2

ds material by suresh

Documents