Question { 9086 }

Source Like
department_no, employee_name
----------------------------
20, R
10, A
10, D
20, P
10, B
10, C
20, Q
20, S

and Output should be like this

department_no, employee_list
--------------------------------
10, A
10, A,B
10, A,B,C
10, A,B,C,D
20, A,B,C,D,P
20, A,B,C,D,P,Q
20, A,B,C,D,P,Q,R
20, A,B,C,D,P,Q,R,S

Answer

small modification to the above ans:
SRC---->Transformer--->TGT
we do the 'HASH' partitioning on Dept_No.

SetNull value to the Stage variable----->SV_List

In the transformer stage variables:
SV_List :',' : Employee_Name ----->SV_List (Concatenating Employee
Names)

In the Column Derivation:
TRIM(SV_list, ',' , 'L')------->Employee_Name

Question { 5300 }

Is there no issue when you try to convert a NOt null column
in nullable and vice versa in aggregator styage and
transformer stage? When I tried i got the warnings but in a
running code I can see such type of scenarios. Please
explain

Answer

1) there is no issue when you are converting NOT NULL column to NULLABLE column even if get null data from source.

2) But when you are converting nullable column to NOT nullable column also you will not get any issue if you get the NOT NULL data from source,
if you get nullable data from source then job will ABORT.

Question { IBM, 10567 }

Input Data is:
Emp_Id, EmpInd
100, 0
100, 0
100, 0
101, 1
101, 1
102, 0
102, 0
102, 1
103, 1
103, 1
I want Output
100, 0
100, 0
100, 0
101, 1
101, 1
Means Indicator should either all ZEROs or all ONEs per
EmpId.
Impliment this using SQL and DataStage both.

Answer

In DataStage:
SRC--->CPY---->JOIN----TFM---TGT
--------|---- /
--------|--- /
--------|-- /
--------|- /
--------AGG
In AGG, GROUP BY EmpId, calculate MIN and MAX for each
EmpId.
JOIN both one copy from CPY and 2nd Aggrigated copy from
AGG.
In TFM, put constraint: IF MIN=MAX, then populate to TGT
then u will get required output.

Question { Satyam, 21181 }

How to remove ctrl M or(^M) characters in file using Sequential File stage?

Answer

We can use these ways also to remove Control M Characters

tr -d '\r' < yourfile.txt > yourfile1.txt
mv yourfile1.txt yourfile.txt

or

Change the line terminator in Sequential File stage from
UNIX-style to DOS-style(it's a stage property).

Question { CTS, 15881 }

INPUT file 'A' contains:
1
2
3
4
5
6
7
8
9
10

input file 'B' contains:
6
7
8
9
10
11
12
13
14
15

Output file 'X' contains:
1
2
3
4
5

Output file 'Y' contains:
6
7
8
9
10

Output file 'Z' contains:
11
12
13
14
15

How can we implement this in a single ds job?

Answer

Shar,
You explained the same as my 2nd method with examples.

Thanks
Subhash

Question { TCS, 11316 }

Difference between the Sequence and Sequencer in DS?

Answer

Sequence:
which allows you to specify a sequence of parallel jobs or server jobs to run. The sequence can also contain control information; for example, you can specify different courses of action to take depending on whether a job in the sequence succeeds or fails. Once you have defined a job sequence, it can be scheduled and run using the InfoSphere DataStage Director. It appears in the InfoSphere DataStage repository and in the InfoSphere DataStage Director client as a job.

Sequencer:
A sequencer allows you to synchronize the control flow of multiple activities in a job sequence. It can have multiple input triggers as well as multiple output triggers.
The sequencer operates in two modes:
ALL mode, In this mode all of the inputs to the sequencer must be TRUE for any of the sequencer outputs to fire.
ANY mode, In this mode, output triggers can be fired if any of the sequencer inputs are TRUE

Question { Amdocs, 7179 }

Hi every one, I am Suneel.

I/p o/p
--- -----
suneel suneel
suneel
suneel
suneel
suneel

how it will get.
Please design job with explain.

Thanks.

Answer

1. SRC---->TFM---->TGT
in the TFM, by using Looping we, can do this.
@ITERATION<=5

2.SRC---->COPY===>(5copies/links)==>FUNNEL---TGT

take 5 output links from copy stage, pass those to Funnel
then you will get the desired output.

3. SRC--->TGT
run the job 5 time in 'APPEND' mode

Question { IBM, 5363 }

in sequtial file 2 columns avaliable, i want only one column
load the target. for this we can do by modify and copy
stage. But here when using modify stage (in property drop
column1) until it is ok. if target is data set How to view
the data. with out using data management. what is the reason
for this. if any body know this answer plz tel me.

thanks.

Answer

We can not view directly, because it stores the data in the datastage internal format.
1. After running the job, directly open the DataSet and click on 'view data'
2. we can view data using 'DataSet management' which is available in Ds Director, tool menu.
3. we can view data using 'ORCHADMIN' unix command.

a. Orchadmin dump EMP.ds
b. Orchadmin dump EMP.ds 2>/dev/null ---> redirecting to output.
c. Orchadmin dump EMP.ds 2>/dev/null >SampleFile.txt --> redirecting output to file.

Question { Amdocs, 5107 }

Hi dude,

i/p
eno ename date edate deptno
1 x 10thjuly 30july A
2 y 10th aug 30 aug B
1 x 1aug ------ A
3 z 1jan ------ c
2 y 1sep ------ B

o/p is
eno ename date edate deptno
1 x 1aug ------ A
2 y 1sep ------ B
3 z 1jan ------ c


Here edate is System generates we don't know that value.
Please design the job by using SCD stage and Sql query.

If any body know this answer please tel me.

Tanks.

Answer

Using Sql Query:
SELECT
eno, ename, date, edate, deptno
FROM INPUT_TABLE
WHERE edate IS NULL
ORDER BY eno;

Question { TCS, 6726 }

I have 5 different sources i want same records in 5 different
targets Can you any body send me this question answer
rathdsetl@gmail.com

Answer

Qns is not clear, as per my understandings:

connect 5 sources(if they have same metadata) to funnel
funnel to copy stage
copy stage to 5 diff targets
then u will get same data in 5 targets.

Question { TCS, 7254 }

6) my sorce
d1
d2
d1
d2
d1
d3
d2
d1


my target

tartget
1 d1
2 d1
3 d1
4 d1


target2


1 d2
2 d2
3 d3

Target 3

1 d3
Can any body send me answer this is argent requierment

Answer

1. SRC--->FILTER---->TGT1,TGT2,TGT3
In source query,
SELECT COL1,
RANK() OVER(PARTITION BY COL1 ORDER BY COL1) SNO
FROM TABLE
in filter,
col1='d1'------>TGT1
col1='d2'------>TGT2
col1='d3'------>TGT3

2. SRC---->TFM---->TGT1,TGT2,TGT3
in TFM, do the HASH partitioning on col1 and use the stage
varibles as below:
if col1 =prevCol then SV1=S1+1
else SV1=1
prevCol---->col1

In the link constraints:
TGT1 link, COL1='d1'
map SV1, COL1 to the tgt.

TGT2 link, COL1='d2'
map SV1, COL1 to the tgt.

TGT3 link, COL1='d3'
map SV1, COL1 to the tgt.

Question { 4514 }

4)
source target

c1 c1 c2 c3
c2 c4 c4 c5
c3 c6 c7
c4
c5
c6
c7

please send me answer this question my mail

Answer

1. SRC---->TFM---->TGT1,TGT2,TGT#
If source is seq file add the ROW NUM column also.
If source is Oracle select ROWNUM column also.
then our input look like this:
C1 1
C2 2
C3 3
c4 4
c5 5
c6 6
c7 7

in TFM,
put below condition for each target link.
TGT1--->MOD(ROWNUM,3)=1
TGT2--->MOD(ROWNUM,3)=2
TGT3--->MOD(ROWNUM,3)=0

Question { TCS, 7756 }

Question
4)
source target

c1 c1 c2 c3
c2 c4 c4 c5
c3 c6 c7
c4
c5
c6
c7


Singal Source and Singal Target only subash,

Answer

1. SRC---->TFM---->PIVOT Stage---->TGT
If source is seq file add the ROW NUM column also.
If source is Oracle select ROWNUM column also.
then our input look like this:
COL1 COL2
C1 1
C2 2
C3 3
c4 4
c4 5
c5 6
c6 7
C7 8

in TFM,
put below condition for Col2 derivation.
If COL2<4 AND COL2>0 then 1 elseIf COL@>3 AND COL2<7 then 2 else 3
then out from tranformer looks like this
COL1 COL2
C1 1
C2 1
C3 1
c4 2
c4 2
c5 2
c6 3
C7 3
then do the Vertical PIVOT on
GROUP BY COL2,
PIVOT COL1
then u will get below output:
Col2 COL1_1 COL1_2 COL1_3

1 c1 c2 c3
2 c4 c4 c5
3 c6 c7
we can drop COL2 in TGT

Question { TCS, 13276 }

I want capture UnMatched records from Primary source and secondary source in JOIN stage?

Answer

SRC1, SRC2====>JOIN====>TGT

in JOIN stage, select the full outer join.
in the Filter/Transformer stage, give condition as SRC1.KEY is null then unmatched records from SRC2.
SRC2.KEY is null then unmatched record from SRC1.

Question { TCS, 8120 }

Unix Qn asked in datastage interview:
I have diff type(.txt, .tmp, .bat etc) of file in 4 diff directories, I want move all '.txt' file from 4 directories to other folder.
And need to delete all the files except which are created TODAY?

Answer

1.
mv dir1/*.txt TgtDir
mv dir2/*.txt TgtDir
mv dir3/*.txt TgtDir
mv dir4/*.txt TgtDir

2. find -mtime 1+ | del
(1+ 1day before created files, -5 --> files created last 5
days and 1 ---> created exact 1 day back )