Question { HCL, 26517 }

what is the difference between lookup stage reject link and
merge stage reject link in datastage Parallel jobs?

Answer

Well
The prime difference is
lookup reject link can collect all unmatched primary mismatched data.
Where as
in merge stage, using reject link(s) it will allow us to identify from which update datasets records got rejected.
n in addition to this as we know
lookup supports only one reject link and merge supports as many no. of updates so many no. of reject links.
thats it.

Question { Cap Gemini, 8326 }

what is a force compile

Answer

see parallel jobs r by default optimized such that transformer stages only get recompiled if they have changed since the last compile. The force compile option overrides this and causes all transformer stages in the job to be compiled

Question { 5486 }

i have the source from Uk,north america how can i pass the
data two tables based on the locations

Answer

question incomplete.

but still we can differentiate recs of uk & america.
we can use col gen and assign a small code(uk/us) to rec(s) of each table and funnel them if they have same metadata and do the processing thing and load the processed rec(s) to target depending upon the business requirements.

However guys plz provide basic details in questions so that proper answer can be given.

Question { 8425 }

hi my source is::
empno,deptno,salary
1, 10, 3.5
2, 20, 8
2, 10, 4.5
1, 30, 5
3, 10, 6
3, 20, 4
1, 20, 9
then target should be in below form...

empno,max(salary),min(salary),deptno
1, 9, 3.5, 20
2, 8, 4.5, 20
3, 6, 4, 10
can anyone give data flow in data stage for the above
scenario....

thanks in advance...

Answer

the question framed wrong
it should have been
empno,dept,sal
1,10,9
1,10,8
1,10,7
2,20,7
2,20,8.5
2,20,9
3,30,4
3,30,6
then expecting an ans is correct
o/p
empno,max(sal),min(sal),deptno
1,9,7,10
2,9,7,20
3,6,4,30
---xx----

where as in the o/p of user's question
we have
empno,max(sal),min(sal),deptno
1,9,3.5,20
2,8,4.5,20
3,6,4,10
here empno=1 his max(sal)=9 from deptno 20 and min(sal)=3.5 then its deptno=? actually we r giving a correct information but client will get confused max(sal) and min(sal) r both from 20 or different departments.
-----xxx----
even though client is expecting the same output then laxmi answer is correct

Question { Cap Gemini, 28515 }

How to get max salary of an organization using data stage
stages...........

can any body help me plz.......

Answer

ok lets us say after getting result like
a,8000
b,9000
c,9999
acc to nandhini using dummy key would give me
a,8000,1
b,9000,1
c,9999,1
then use rem duplicate stage then we get 9999 as output after we assign retain duplicates=last for dummykey col.
what if
i have diff data and got output like below after aggreagation
a,9000
b,18000
c,10000
now what...?

Question { 9106 }

Source flat file contains
src
---
1
2
'
'
'
18
we had 3 targets
T1 T2 T3
-- -- --
1 4 7
2 5 8
3 6 9
10 13 16
11 14 17
12 15 18

How can i get?
**Using only datastage, but not unix or any other.
I am expecting the answer soon..
Thanks in advance.

Answer

whats wrong with the people
see we have 3 targets so.,.
use below under tx constraints
mod(src,3)=1 move it to target1
mod(src,3)=2 move it to target2
mod(src,3)=0 move it to target3

seq-->tx-->dataset(3)
whats the big deal in it?

Question { Wipro, 8594 }

how do u convert date in 20-12-07 to dec-20-2007 or 20-dec-
2007 or 20-dec-07 in parallel

Answer

First see what is the default date format of the project.
Generally it would be “%yyyy-%mm-%dd”
Then u need to change it to ur required format like :
“%dd-%mm-%yy”
Since the year is 2 digit year then u will have to mention
its four digit year before yy because datastage cannot
understand the latest and old year and it simply consider
year from 1900 century on wards.
So u will have to mention at ur job properties under default
at date as : “%dd-%mm-%2000yy” (must) without which
datastage will not consider u r given date format in
transformer stage date function and it simply go with the
Project’s default date format.
And also mentioning “%dd-%mm-%2000yy” format at job
properties and not mentioning “%dd-%mm-%2000yy” at
transformer stage also works fine.
Ex: if your date is (20-12-07,”%dd-%mm-%yy”) then datastage
consider default date format at job properties and will go
accordingly. And output will be 20-12-2007. And if
“%dd-%mm-%yy” is mentioned at job properties and u have
mentioned (20-12-07,”%dd-%mm-%2000yy”) then datastage will
not consider ur date format and simply it gives the output
as 1907-12-20.
So it is a good practice to see the default date format
assigned to the project and make changes accordingly and
attempt to do the process with it.
DateToString(StringToDate(DSLink3.date,"%dd-%mm-%2000yy"),"%mmm-%dd-%yyyy")
- dec-20-2007
DateToString(StringToDate(DSLink3.date,"%dd-%mm-%2000yy"),"%dd-%mmm-%yyyy")
-20-dec- 2007
DateToString(StringToDate(DSLink3.date,"%dd-%mm-%2000yy"),"%dd-%mmm-%yy")
- 20-dec-07

Now That’s what I call it cooooll.

Question { IBM, 5425 }

is it possible to access the same job by two users at a time
in DataStage?

Answer

Yes the above answer is correct.
At any time only one user can access a job. When doing so
dataStage imposes exclusive lock on it for that user so that
other must not access w r t edit and execute. However other
user can have access just to view or read the job.

That it.

Question { IBM, 5817 }

where the log files or tables can store in DS?

Answer

well it is
DataStage Repository.
It is the place where all the log files and tables .... are
stored in.

Question { IBM, 5204 }

where we use config file as parameter ?

Answer

datastage depends on configuration file.
Well if u know abt of configuration file then here i go with
the ans...
APT_CONFIG_FILE is the environment variable using which
datastage determine the configuration file.That will give us
an option to mention our designed configuration file instead
of default.apt which is default configuration file. And one
can have many configuration files for a project to be used.

In any job at job properties- properties tab we will be
asked to give name parameter name and its corresponding
default value.
However we will select $APT_CONFIGURATION_FILE and to use
our designed configuration file we mention that in Default
value column. So in that way we use config file as parameter.

Thats it.

Question { 7587 }

What is Cleanup Resources and when do you use it?

Answer

It is like:
1)When we want to clean DataStage Repository.
2)when we are trying to use a job then we get a message
telling error : job jobnumber is being used by another user.
we get only read only mode to use it.
it is being protected by a lock.
so if we want to use job we need to unlock it.
3)If a job is hanging or failing and not releasing locks :
4)to stop a job when its status is running.
Then go to Director (but not to Administrator) and select
the job u want to access and go to job menu where u will
find clear up resources option.
clicking it u will be shown a window where u will see to
panes top(processes) and bottom(locks).

here we can view and end job processes
and release the associated locks.
select a process which holds a lock and click logout.
by doing so we can unlock a job.

Actually a job can be used only by a single user at a time.

Question { 12006 }

How to remove blank spaces from data?

Answer

sed 's/' '//g ' sample
Or
Other way:(just in case)

In Transformer we can use function : CompactWhiteSpace(%string%)
Through which we can remove spaces.

Question { 9435 }

if a column contains data like
ram,rakesh,madhan,suraj,pradeep,bhaskar then I want to
place names separated by commas in another columns how can
we do?

Answer

Well the Question is not clear at the moment.
if ur question is
1)single col with single row like
name
ram,rakesh,madhan,suraj,pradeep,bhaskar
then
ur requirement of asking is again this?
like
ram,rakesh,madhan,suraj,pradeep,bhaskar
or
2)single col with multiple rows like
ram
rakesh
madhan
suraj
pradeep
bhaskar
and u want o/p like ram,rakesh,madhan,suraj,pradeep,bhaskar.
Then praney anwer doesn't suite to get output.
neither u can use pivot stage using vertical pivoting bec
what will u pivot and group by.... not possible.
Finally one way is:

Use SeqStg-->ColGen-->Tx-->RemDup-->Dataset.
At colgen give a new column and assign 1 to all rows and
send the result to tx.
At tx take three variables and expressions to concatenate
the input with the help of colgen new column.
Hint: If sv1 <> sv3 Then DSLink45.cname Else sv2 : ',' :
At RemDup specify Last value
And finally u will the output.

Still if u dont get what i explained just mail me
shharrath@yahoo.com

Question { FIS, 9490 }

HOw Hash Partion Works

Thank you in Advance

i have doubts on Hash Partion TEch Could please give me the
clear understandable notation

example

e_id,dept_no
1,10
2,10
3,20
4,20
5,30
6,40

i have TWo Nodes/Three Nodes

My questions are:

1).if i select hash key as e_id how Hash partion will
distribute the data in to two NOdes/three NOdes
2).if i select hash key as dept_no how Hash partion will
distribute the data in to two NOdes/three NOdes


sivakumar.katta7@gmail.com

Answer

Well The basic idea is:
Same key column values are given to the same node.

Hence
1.(e_id)
node1 node2 node1 node2 node3
1,10 2,10 1,10 2,10 3,20
3,20 4,20 4,20 5,30 6,40
5,30 6,40

2. (dept_no)
node1 node2 node1 node2 node3
1,10 3,20 1,10 3,20 5,30
2,10 4,20 2,10 4,20 6,40
5,30 6,40
(This is where pranay went wrong 6,40 will go to node3
instead of node1.)

Question { Infotech, 8736 }

If seg file having 10 records
ex:eid
1
2
"
"
10
if oracle database having 100 records
ex:eid
1
2
"
"
100
how to delete matched records permenently from oracle
database using datastage ?

Answer

Vaibhav can u plz explain how an sql query is written b/t
seq file and oralce table in oracle stage delete query?