1) How to calculate Sum(sal), Avg(sal), Min(sal), Max(sal) with out using Aggregator stage.
2) How to find out First sal, Last sal in each dept with out using aggregator stage
3) How to Find Out Duplicate Values Using Transformer?
4) Database comparison – compare two databases using generic datastage job
5) Remove the header and footer while reading this file using sequential file stage of Datastage?
sed '1d;$d' file_name>new_file_name (type this in job before job subroutine then use new file in seq stage)
sed '1,$d' file_name>new_file_name ( or use filter option in sequential file stage )
6) How to generate sequence without using surrogate key generator
@PARTITIONNUM + (@NUMPARTITIONS * (@INROWNUM-1))
7) Input :-
ABC,Pune,Banglore,Mumbai
XYZ,Pune,Mumbai
Output :-
ABC,Pune,1
ABC,Bnaglore,2
ABC,Mumbai,3
XYZ,Pune,1
XYZ,Mumbai,2
8) Input
A
A
B
B
C
C
E
F
Output 1
A
B
C
E
F
Output2
A
B
C
Output3
E
F
9) Input
COL1
A
A
B
TARGET
COL1 COL2
A 1
A 2
B 1
10) Clss Rollno Marks
A 1 10
B 3 11
A 2 20
A 4 21
B 5 30
Generate rank for rollno on the basis of marks for each class
11) Input
Msgid text sno
1 hello 1
1 good 2
1 morning! 3
2 hey! 1
Output
1 hello good morning
2 hey!
12) Get Count of emp and total salary paid in a dept using only one aggregator stage
Dummy col – 1
Take sum of dummy col to get the count
13) Input:
file1
10
20
10
10
20
30
Output:
file2 file3
10 30
20
14) Input
file1
1
2
3
4
5
6
Output:
file2(odd) file3(even)
1 2
3 4
5 6
15)
Shirt|red|blue|green
Pant|pink|red|blue
Output should be,
Shirt:red
Shirt:blue
Shirt:green
pant:pink
pant:red
pant:blue
16) col1 col3
1 samsung
1 nokia
1 ercisson
2 iphone
2 motrolla
3 lava
3 blackberry
3 reliance
Expected Output
col 1 col2 col3 col4
1 samsung nokia ercission
2 iphone motrolla
3 lava blackberry reliance
17) How you make single record into multiple
ex:
input => baxter 1 record,
output=> b a x t e r pivot mode as 6 rows?
18) 5 input files data to be loaded into 5 different tables using single generic job/sequence.?
19) How to load the file where on specific day it comes with header and other days it may come without header, how do you process it?
20) We have two tables called A (1,2,3,4,6) and B (1,3,4,5,6,7) need to go to the output like matched records on A table compare to B into one target and non matching records into another target.
21) How will you populate a source that has 100 million records to the target table in Historical Load? What are the requirements?
22) How can we meet below requirement?
Table A Table B
------- -------
1 1
2 2
3 3
4 7
5 8
6 9
Table A and Table B has records as above.
• The records which are present in both A & B should be loaded to 1st Target?
• Records which are present in A but not in B should go to 2nd Target?
• Records which are present in B but not in A should go to 3rd Target?
23) Source data
Teacher Sub Students
T1 Physics S1,S2,S3
T2 Maths S4,S5
a. How to get student count under each teacher.
Output:
Teacher Student Count
T1 3
T2 2
b. How to get each student in different row with corresponding teacher.
Output:
Teacher Student
T1 S1
T1 S2
T1 S3
T2 S4
T2 S5
24) Table has 1 to 10 records and we have 3 different target tables. I want to load 1 to 3 records to 1st target table and 4 to 6 should go to 2nd target table and 7 to 9 records should goes to 3rd target table and 10th records should go again 1st target table. How create datastage job.
2) How to find out First sal, Last sal in each dept with out using aggregator stage
3) How to Find Out Duplicate Values Using Transformer?
4) Database comparison – compare two databases using generic datastage job
5) Remove the header and footer while reading this file using sequential file stage of Datastage?
sed '1d;$d' file_name>new_file_name (type this in job before job subroutine then use new file in seq stage)
sed '1,$d' file_name>new_file_name ( or use filter option in sequential file stage )
6) How to generate sequence without using surrogate key generator
@PARTITIONNUM + (@NUMPARTITIONS * (@INROWNUM-1))
7) Input :-
ABC,Pune,Banglore,Mumbai
XYZ,Pune,Mumbai
Output :-
ABC,Pune,1
ABC,Bnaglore,2
ABC,Mumbai,3
XYZ,Pune,1
XYZ,Mumbai,2
8) Input
A
A
B
B
C
C
E
F
Output 1
A
B
C
E
F
Output2
A
B
C
Output3
E
F
9) Input
COL1
A
A
B
TARGET
COL1 COL2
A 1
A 2
B 1
10) Clss Rollno Marks
A 1 10
B 3 11
A 2 20
A 4 21
B 5 30
Generate rank for rollno on the basis of marks for each class
11) Input
Msgid text sno
1 hello 1
1 good 2
1 morning! 3
2 hey! 1
Output
1 hello good morning
2 hey!
12) Get Count of emp and total salary paid in a dept using only one aggregator stage
Dummy col – 1
Take sum of dummy col to get the count
13) Input:
file1
10
20
10
10
20
30
Output:
file2 file3
10 30
20
14) Input
file1
1
2
3
4
5
6
Output:
file2(odd) file3(even)
1 2
3 4
5 6
15)
Shirt|red|blue|green
Pant|pink|red|blue
Output should be,
Shirt:red
Shirt:blue
Shirt:green
pant:pink
pant:red
pant:blue
16) col1 col3
1 samsung
1 nokia
1 ercisson
2 iphone
2 motrolla
3 lava
3 blackberry
3 reliance
Expected Output
col 1 col2 col3 col4
1 samsung nokia ercission
2 iphone motrolla
3 lava blackberry reliance
ex:
input => baxter 1 record,
output=> b a x t e r pivot mode as 6 rows?
18) 5 input files data to be loaded into 5 different tables using single generic job/sequence.?
19) How to load the file where on specific day it comes with header and other days it may come without header, how do you process it?
20) We have two tables called A (1,2,3,4,6) and B (1,3,4,5,6,7) need to go to the output like matched records on A table compare to B into one target and non matching records into another target.
21) How will you populate a source that has 100 million records to the target table in Historical Load? What are the requirements?
22) How can we meet below requirement?
Table A Table B
------- -------
1 1
2 2
3 3
4 7
5 8
6 9
Table A and Table B has records as above.
• The records which are present in both A & B should be loaded to 1st Target?
• Records which are present in A but not in B should go to 2nd Target?
• Records which are present in B but not in A should go to 3rd Target?
23) Source data
Teacher Sub Students
T1 Physics S1,S2,S3
T2 Maths S4,S5
a. How to get student count under each teacher.
Output:
Teacher Student Count
T1 3
T2 2
b. How to get each student in different row with corresponding teacher.
Output:
Teacher Student
T1 S1
T1 S2
T1 S3
T2 S4
T2 S5
24) Table has 1 to 10 records and we have 3 different target tables. I want to load 1 to 3 records to 1st target table and 4 to 6 should go to 2nd target table and 7 to 9 records should goes to 3rd target table and 10th records should go again 1st target table. How create datastage job.