Interview Questions Answers in Data Stage

Question { Scope International, 29011 }

Hi I am Vijay
In my source i've 10 records in a single column.... but i
want to split those records into 5 sequential files each
seq file contains 2 records.?.... can any body help me?

Answer

Guy's,

This is Simple one. Use the row no column in SEQ_stage to
sequence numbers.

SEQ_STAGE>>>FILTER/SWITCH>>>TARGETS

seq_no are placed in SEQ column.

SO, in filter stage: seq>=0 and Seq<=1 for first target.
seq>=2 and Seq<=3 for Second Target
seq>=4 and Seq<=5 for Third Target
seq>=6 and Seq<=7 for Fourth Target
seq>=8 and Seq<=9 for Last Target

Is This Answer Correct ?

8 Yes

0 No

Question { IBM, 53968 }

HOW CAN WE SEE THE DATA IN DATASET?

Answer

Gentlemen, We can view the data in the Dataset in UNIX by
using orchadmin command.

syntax: orchadmin dump

we can also copy the content to flat file using copy command.

orchadmin copy

In order to count rows in Dataset:

orchadmin copy | wc -l

Is This Answer Correct ?

2 Yes

4 No

Question { IBM, 53968 }

HOW CAN WE SEE THE DATA IN DATASET?

Answer

Gentlemen, We can view the data in dataset in unix by using
orchadmin command
syntax: orchadmin dump

copy the contents of dataset into flat file:

Syntax: orchadmin copy

count of records:

Syntax: orchadmin copy | wc -l

Is This Answer Correct ?

6 Yes

4 No

Question { Polaris, 10988 }

What is diff between Junk dimensions and conform dimension?

Answer

Junk dimension: This dimension is mainly used for reference
purpose. It contains textual codes and flag variables.

Ex: country_id,Country_name
IND INDIA

Conform dimension: AS Avinash said the dimension table which
is shared by multiple fact tables are called as Conform
Dimension table.

Date,Geography,time etc.

Is This Answer Correct ?

1 Yes

0 No

Question { 7938 }

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

hai first use the copy stage. from that take three links.

in that first and second links are connected to aggregator
stages.

agg1-- max and min for sal group by empno...

agg2--max only group by empno...

then use lookup for agg2 and third link of copy stage...

in lookup join max{sal} to sal and get deptno...

finally, o/p links from agg1 and lookup are joined by
lookup.. join by max{sal}....

then u can get the desired o/p...

Is This Answer Correct ?

0 Yes

2 No

Question { IBM, 10742 }

one file contains
col1
100
200
300
400
500
100
300
600
300
from this i want to retrive the only duplicate like this
tr1
100
100
300
300
300 how it's possible in datastage?can any one plz explain
clearley..........?

Answer

In order to collect the duplicate values:

first cal the count output col in aggregator stage
group by col.
aggregator type: count rows.
count output col..

next, use the filter stage to separate the multiple occurrence.

finally, use the join stage or lookup stage to map the two
tables join type INNER ..

then u can get the desired output..

Is This Answer Correct ?

14 Yes

1 No

Question { Polaris, 17134 }

In Sequential file, how can i split a column into two, and
that column contains string datatype.
For Example, i have column of string datatype as subedar
khaja. Now i want get output as separately with subedar in
one column and khaja in second column.
How?
Coula anybody, solve it?

Answer

That's is right siva. Here is another set of solution:

i/p is ColA which contains Vinod Upputuri.
in order to spit this column into two columns, I am going
to use Column Import stage.

SEQSTAGE>>>COLUMNIMPORT>>>TARGET.

In column import stage>>> we have to specify the import key
column, then specify the column to export: in this case
First_name,Last_Name etc.

Note: Specify the Delimeter as per the input format.

Is This Answer Correct ?

2 Yes

0 No

Question { Wipro, 7695 }

i want for the 3rd max sal using for the any stage but max
sal is based on the deptno ?
any body know this ans pls send this mail siva3me@gmail.com

Answer

SIMPLE ONE IS.

USE THE TRANSFORMER STAGE.

BEFORE LOADING THE DATA IN TO TRANSFORMER STAGE THE DATA
MUST IN SORTED ORDER OF SAL AS DES AND DEPT AS ASC.

IN TRANSFORMER I AM GOING TO USE THE STAGE VARIABLES.

STR IF DEPT=STR1 THEN STR+1 ELSE 1

STR1 DEPT

THEN USE THE FILTER IN THAT PLACE CONDITION STR=3

Is This Answer Correct ?

1 Yes

6 No

Question { 7959 }

I have 2 Files like
fileA fileB Output1 Output2 Output3
1 6 1 6 11
2 7 2 7 12
3 8 3 8 13
4 9 4 9 14
5 10 5 10 15
6 11
7 12
8 13
9 14
10 15

please let know

Answer

You are right chetan, but in the second lookup we have to
use seq file 1 for main link and o/p2 is linked as reference.

Is This Answer Correct ?

0 Yes

0 No

Question { IBM, 9931 }

There are two file are there .1st file contains 5 records and
2nd file contain 10 records in target they want 50 records.how
can achieve this

Answer

use copy stage and funnel stage to make duplicate records.

SEQ>>COPY>>FUNNEL>>TARGET

the solution for this stupid question asked by interviewer:

SEQ>>COPY>>>4links>>>FUNNEL STAGE>>>SEQ.

in seq stage: sed -n '1,50p'

Is This Answer Correct ?

0 Yes

2 No

Question { Cap Gemini, 12244 }

I have a file it contain 2 records like empname,company as
Ram, Tcs and Ram, IBM. But i want empname,
company1,company2 as Ram, TCS,IBM in the target. How?

Answer

Venkatesh is Absolutely right but unfinished. After remove
duplicate stage we have to use either column import stage or
transformer stage.

SEQ>>>TRANS>>>REMOVEDUPLICATE>>>COLUMNIMPORT/TRANSFORMER>>>TARGET

if Transformer: use field function in derivation

for company1: field(company,',',1)
company2: filed(company,',',2)

for column import stage: specify the key column: company
default delimeter: ','
columns: company1 and company2

Is This Answer Correct ?

0 Yes

3 No

Question { TCS, 13236 }

1)How to Duplicate Records Delete in Sequential file?

Answer

In the sequential File stage there is a option called filter.
there we can use UNIX command.

syntax for Remove duplicate: uniq or sort -u

Is This Answer Correct ?

11 Yes

1 No

Question { TCS, 12428 }

How many number of reject links merge stage can have?

Answer

Important feature of Merge stage is: it collects the
rejected data from reference links when compared to master link.

Is This Answer Correct ?

3 Yes

1 No

Question { TCS, 8654 }

Can we use Round Robin for aggregator?is there any benefit
underlying?

Answer

Here is the Sample Scenario:
Max sal for each dept

I/P File: Dept
Deptno, Sal
10,21000
10,22000
20,23000
20,12000
30,41000

if you use the Round-robin for 2 node:

SEQ>>AGR>>Tar

no: 10,21000 n1: 10,22000 n2:20,23000
20,12000 30,41000

then the O/P: is all the records from all nodes. because in
no there is no matching deptno.

if you do the hash partition: then

no: 10,21000 n1:20,23000 30,41000
10,22000 20,12000

SO, the O/P: no:10,22000
n1:20,23000
n2:30,41000

Is This Answer Correct ?

10 Yes

2 No

Question { Infotech, 8254 }

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

Simple job:

SEQ>>>ORACLE_STAGE.

in ORCL_STAGE: SPECIFY WRITE METHOD: DELETE ROWS

specify the matching column as key column in column definition.

Is This Answer Correct ?

2 Yes

0 No

vinod