Friday 5 December 2014

Datastage Scenarios v1.0



1. I have a file having CITY column having 100 records I want the Target as First
letter starts with “A” and “B” and Remaining records as a rejected output.
2. I have a source file like this I want out put as like
Source file Target1
10 10
20 20
10 30
10 Target2
10 10
20 10
30 20
3. I have a source file like this I want out put as like
Source file Target1
10 10
20 20
10
10 Target2
10 30
20
30
4. I have a source file like this I want out put as like
Source file Target1
10 10
20 20
10 10
10 10
10 20
20 Target2
30 30
5.I have a source file like this I want out put as like without using link petitioner
Source file Target1
1 1
2 4
3 7
4 Target2
5 2
6 5
7 8
8 Target3
9 3
6
6.I have a source file like this I want out put as like without using link petitioner
Source file Target1
1
2 even records
3
4 Target2
5
6 odd records
7
8
9
7. I want the count of the Each department number with out using Aggregator
Source File: Target
DEPTNO, SAL Count
10, 1000 1
10, 1100 2
10, 1200 3
20, 1100 1
20, 1200 2
30, 1200 1
30, 1300 2
30, 1400 3
8.I want the sum of the Each department number salary with out using Aggregator
Source File: Target
DEPTNO, SAL Count
10, 1000 1000
10, 1100 2100
10, 1200 3300
20, 1100 1100
20, 1200 2300
30, 1200 1200
30, 1300 2500
30, 1400 3900
9. I want the count of Each Deptno and Avg sal for Each Deptno using Aggregator
10.I want the count of the Each department number
Source File: Target
DEPTNO, SAL Deptno, Sum_Sal
10, 1000 10, 3300
10, 1100 20, 2300
10, 1200 30, 3900
20, 1100
20, 1200
30, 1200
30, 1300
30, 1400
11.Find The Output With out PIVOTE stage
Source File: Target
SNO, MATHS, PHYSICS, CHEMISTRY SNO, MARKS
101, 70, 80, 90 101, 70
102, 65, 55, 75 101, 80
101, 90
102, 65
102, 55
102, 75
12. Source: Target
EMPNO, SAL Empno,jan,feb,mar,april
101, 1000 101, 1000, 1100, 1000, 10000
101, 1100 102, 1100, 1300, 1200, 1500
101, 1000
101, 10000
102, 1100
102, 1300
102, 1200
102, 1500
13.Source File: Target
EMPNO, SAL Empno, Format
101, 1000 101, 1000
101, 1100 101, 1000, 1100
101, 1000 101, 1000, 1100, 1000
101, 10000 101, 1000, 1100, 1000, 10000
102, 1100 102, 1100
102, 1300 102, 1100, 1300
102, 1200 102, 1100, 1300, 1200
102, 1500 102, 1100, 1300, 1200, 1500
14.
Source File: Target1:
DEPTNO, SAL How to Capture Third Duplicate
10, 1000 from Each Deptno?
10, 1100
10, 1200 Target2:
20, 1100 Remaining records as a Rejected
20, 1200 output
30, 1200
30, 1300
30, 1400
15.
Source File: Target
DEPTNO, SAL Deptno,count,padding,padding
10, 1000 10,1,00001000,0010
10, 1100 10,2,00001100,0010
10, 1200 10,3,00001200,0010
20, 1100 20,1,00001100,0020
20, 1200 20,2,00001200,0020
30, 1200 30,1,00001200,0030
30, 1300 30,2,00001300,0030
30, 1400 30,3,00001400,0030
16.
Source File: Target
DEPTNO, SAL Deptno,count,sum_Sal,padding
10, 1000 10,1,1000,0010
10, 1100 10,2,2100,0010
10, 1200 10,3,3300,0010
20, 1100 20,1,1100,0020
20, 1200 20,2,2300,0020
30, 1200 30,1,1200,0030
30, 1300 30,2,2500,0030
30, 1400 30,3,3900,0030
17.
Source File: Target
Empno, Ename, Date Empno, Ename, Date
100, sudheer, 2010-1-5 100, sudheer, 2010-01-05
101, kumar, 2010-10-2 101, kumar, 2010-10-02
103, lsk, 2010-1-2 103, lsk, 2010-01-02
18
Source File: Target
Empno, Ename, Email Empno, Ename, Email
100, sudheer, sudheer@gmail.com 100, sudheer, sudheer
101, kumar, lsk.4a4@gmail.com 101, kumar, lsk.4a4
102, lsk, sudheer.4a4@gmail.com 102, lsk, sudheer.4a4
19.I want the out put like this
Source File Target File
EMPNO, ENAME, MGRNAME EMPNO, ENAME, MGRNAME
101, Siva, Thiru 101, Shiva,Thiru,Gandhi
102, Thiru, Gandhi 102, Thiru, Gandhi, Gandhi
20.I want the out put like this
Source File Target
File1 File2 File3 File1 count File2 count File3 count
1 11 25 1 1 11 1 25 1
2 12 26 2 2 12 2 26 2
3 13 27 3 3 13 3 27 3
: : : : : : : : :
: : : : : : : : :
10 25 40 10 10 25 15 40 15
21. I want the out put like this
Source File Target
Empno, Ename, Detno,Sal, City File1
City name starts with “A”
File2
Empno, Ename, Deptno, Max_Sal, City
File3
<>File1 and <>File 2
22. In my Target, I have 10 records but I want to store only one record. (Ex: 5th Record)
23. I want the out put like this
Source File:
Year q1sales q2sales q3sales q4sales
2000 8000 9000 7000 6000
2001 4000 5000 2000 3000
2002 1000 2000 4000 8000
Target:
2001 2002 2003
q1 q1 q1
q2 q2 q2
q3 q3 q3
q4 q4 q4
24. I have a sequential file stage and transformer after that the data is going to target as :
containing name start with “s”
Source File:
Empno, Ename, Sal, Deptno
100, sudheer, 1000, 10
101, kumar, 5000, 10
102, sudha, 2000, 20
103, manu, 6000, 30
104, suresh, 10245, 40
25.
Source File Target
Eid Ename Format
1 A 1-A,B,C
1 B
1 C
2 D 2-D,F
3 E 3-E
2 F
26.
Source File Target
Eid Ename Format
1 A 1-A
1 B
1 C
2 D 2-D
3 E 3-E
2 F
27.
Source File
Chaar
sudheer
kumar
lak
manu
madhu
manoj
28.
Source File o/p:target
a 3 a a a
b 4 b b b b
c 5 c c c c c
29.
Source File Target:
Jan Feb Mar
1 2 3 Jan=11
4 5 5 Feb=11
6 4 5 Mar=13
30.
Source Target
Calname-varchar Calname-varchar
mphasis MphasiS
satyam SatyaM
techmahindra TechmahindrA
wipro WiprO
ibm IbM
31. My source file is sequential file,in sequential file records is a,b,c,d,1,2,3.The expected output is
abcd,1
abcd,2
abcd,3
32. In my Source File:
a,b,c,d
x,y,t,l
Here a,b,c,d are columns and x,y,t,l are data in them.
Now I want in this place of “t” the value as “p”.Do it in datastage and unix
33. I/p source O/p target
------------ -------------
cid, acc1, acc2, amou1, amou2 1target: 10, SBI, 10000
10, SBI, ICICI, 10000, 20000 2target: 10, ICICI, 20000
34. I have file like this
i/p
Name, seq, desc
DWH, 02, ware
DWH, 01, data
DWH, 03, house
TCS, 01, Tata
TCS, 03, services
TCS, 02, consultancy
and I want the output like this
TCS Tataconsultancy services
DWS Dataware house
35.
Sourec File Target
Phdetails phddetails
3129985603912 312-9985603912
254625621365 254-625621365
123456789 123456789
36.
Sourec File Target
Phdetails phdcode phdnumber
312-9985603912 312 9985603912
2546-25621365 2546 25621365
123456789 000 123456789
37. How to Remove The 3rd Record From Source usung source file Stage?
38. Create a datastage job for following output.(with out any warning or fatal error).
SOURCE: TARGET:
EMPNO DEPTNO DEPTNO COUNT
100 10 10 2
101 20 20 3
102 30 30 2
103 20
104 20
105 30
106 10
39. Create a datastage job for increasing the employee salary (i.e., 10% increment for deptno=10,
12.5% for deptno=20 and 14.25% for deptno=30).
40. Create a datastage job for following output. (with out using Transformer)
Source having 5 records, target 3files also should have 5 records.
SOURCE:
EMPNO DEPTNO MGR HIREDATE
100 10 101 10-JAN-1999
101 20 102 22-JUL-1997
102 30 103 16-AUG-1996
103 20 104 05-DEC-1995
104 30 NULL 30-OCT-1992
TARGET1: TARGET2: TARGET3:
1file 2file 3file
EMPNO DEPTNO EMPNO MGR EMPNO HIREDATE
100 10 100 101 100 10-JAN-1999
101 20 101 102 101 22-JUL-1997
102 30 102 103 102 16-AUG-1996
103 20 103 104 103 05-DEC-1995
104 30 104 NULL 104 30-OCT-1992
41. Create a datastage job for getting unique records to target.
SOURCE:
EMPNO DEPTNO MGR HIREDATE
100 10 101 10-JAN-1999
101 20 102 22-JUL-1997
102 30 103 16-AUG-1996
103 20 104 05-DEC-1995
104 30 NULL 30-OCT-1992
102 30 103 17-AUG-1996
103 20 104 06-DEC-1995
100 10 101 12-JAN-1999
Here EMPNO is key column (observe hiredate)
TARGET:
EMPNO DEPTNO MGR HIREDATE
100 10 101 10-JAN-1999
101 20 102 22-JUL-1997
102 30 103 16-AUG-1996
103 20 104 05-DEC-1995
104 30 NULL 30-OCT-1992
42. Create a datastage job for getting unique records to target.
SOURCE:
EMPNO DEPTNO MGR HIREDATE
100 10 101 10-JAN-1999
101 20 102 22-JUL-1997
102 30 103 16-AUG-1996
103 20 104 05-DEC-1995
104 30 NULL 30-OCT-1992
102 30 103 17-AUG-1996
103 20 104 06-DEC-1995
100 10 101 12-JAN-1999
Here EMPNO is key column (observe hiredate)
TARGET:
EMPNO DEPTNO MGR HIREDATE
100 10 101 12-JAN-1999
101 20 102 22-JUL-1997
102 30 103 17-AUG-1996
103 20 104 05-DEC-1995
104 30 NULL 30-OCT-1992
43. Create a datastage job for create sequence numbers. ( From 234)
SOURCE:
EMPNO DEPTNO MGR HIREDATE
100 10 101 10-JAN-1999
101 20 102 22-JUL-1997
102 30 103 16-AUG-1996
103 20 104 05-DEC-1995
104 30 NULL 30-OCT-1992
TARGET:
SEQNO EMPNO DEPTNO MGR HIREDATE
234 100 10 101 10-JAN-1999
235 101 20 102 22-JUL-1997
236 102 30 103 16-AUG-1996
237 103 20 104 05-DEC-1995
238 104 30 NULL 30-OCT-1992
44. Create a datastage job for create sequence numbers. ( From 345 increment by 10)
SOURCE:
EMPNO DEPTNO MGR HIREDATE
100 10 101 10-JAN-1999
101 20 102 22-JUL-1997
102 30 103 16-AUG-1996
103 20 104 05-DEC-1995
104 30 NULL 30-OCT-1992
TARGET:
SEQNOEMPNO DEPTNO MGR HIREDATE
345 100 10 101 10-JAN-1999
355 101 20 102 22-JUL-1997
365 102 30 103 16-AUG-1996
375 103 20 104 05-DEC-1995
385 104 30 NULL 30-OCT-1992
45. Create a datastage job for separate the employees by deptno.(with out using Transformer). (create
multiple jobs with different stages)
SOURCE:
EMPNO DEPTNO MGR HIREDATE
100 10 101 10-JAN-1999
101 20 102 22-JUL-1997
102 30 103 16-AUG-1996
103 20 104 05-DEC-1995
104 30 NULL 30-OCT-1992
102 30 103 17-AUG-1996
103 20 104 06-DEC-1995
100 10 101 12-JAN-1999
TARGET10:
EMPNO DEPTNO MGR HIREDATE
100 10 101 10-JAN-1999
100 10 101 12-JAN-1999
TARGET20:
EMPNO DEPTNO MGR HIREDATE
101 20 102 22-JUL-1997
103 20 104 05-DEC-1995
103 20 104 06-DEC-1995
TARGET30:
EMPNO DEPTNO MGR HIREDATE
102 30 103 16-AUG-1996
104 30 NULL 30-OCT-1992
102 30 103 17-AUG-1996
46. Create a datastage job for arranging ascending and descending order of employees by their
respective deptno.
SOURCE:
EMPNO DEPTNO MGR HIREDATE
100 10 101 10-JAN-1999
101 20 102 22-JUL-1997
102 30 103 16-AUG-1996
103 20 104 05-DEC-1995
104 30 NULL 30-OCT-1992
102 30 103 17-AUG-1996
103 20 104 06-DEC-1995
100 10 101 12-JAN-1999
TARGET asc:
EMPNO DEPTNO MGR HIREDATE
100 10 101 10-JAN-1999
100 10 101 12-JAN-1999
101 20 102 22-JUL-1997
103 20 104 05-DEC-1995
103 20 104 06-DEC-1995
102 30 103 16-AUG-1996
104 30 NULL 30-OCT-1992
102 30 103 17-AUG-1996
TARGET desc:
EMPNO DEPTNO MGR HIREDATE
102 30 103 16-AUG-1996
104 30 NULL 30-OCT-1992
102 30 103 17-AUG-1996
101 20 102 22-JUL-1997
103 20 104 05-DEC-1995
103 20 104 06-DEC-1995
100 10 101 10-JAN-1999
100 10 101 12-JAN-1999
47. Create a datastage job for get the following target. (create multiple jobs using different stages
)
SOURCE 2 TABLES EMP, DEPT
EMP SOURCE:
EMPNO DEPTNO MGR HIREDATE
100 10 101 10-JAN-1999
101 20 102 22-JUL-1997
102 30 103 16-AUG-1996
103 20 104 05-DEC-1995
104 30 NULL 30-OCT-1992
108 60 109 10-JAN-2009
DEPT SOURCE:
DEPTNO DEPTNAME
10 SALES
20 FINANCIAL
30 TELECOM
40 BANKING
50 HEALTH
TARGET:
EMPNO DEPTNO DEPTNAME
100 10 SALES
101 20 FINANCIAL
102 30 TELECOM
103 20 FINANCIAL
104 30 TELECOM
108 60 NULL
48. Create a datastage job for generating 50 records(rows).
Each row should have 2 columns like
NO NAME
10 GNAN
11 GNAN
12 GNAN
13 GNAN
14 …………………
49. Create a datastage job for extract only 10 records from the source ( source have 100 records)
(create multiple jobs using different stages)
50. Create a datastage job for decrement the salaries of emp by 5% whose deptno not in the
DEPT table.
51. Create a datastage job for combining the data from more than 2 files.
SOURCE10:
EMPNO DEPTNO MGR HIREDATE
100 10 101 10-JAN-1999
100 10 101 12-JAN-1999
SORUCE20:
EMPNO DEPTNO MGR HIREDATE
101 20 102 22-JUL-1997
103 20 104 05-DEC-1995
103 20 104 06-DEC-1995
SORUCE30:
EMPNO DEPTNO MGR HIREDATE
102 30 103 16-AUG-1996
104 30 NULL 30-OCT-1992
102 30 103 17-AUG-1996
TARGET:
EMPNO DEPTNO MGR HIREDATE
100 10 101 10-JAN-1999
100 10 101 12-JAN-1999
101 20 102 22-JUL-1997
103 20 104 05-DEC-1995
103 20 104 06-DEC-1995
102 30 103 16-AUG-1996
104 30 NULL 30-OCT-1992
102 30 103 17-AUG-1996
52. Create a datastage job for following output
SORUCE:
EMPNO DEPTNO MGR HIREDATE
100 10 101 10-JAN-1999
103 20 104 05-DEC-1995
104 30 NULL 30-OCT-1992
TARGET: (single column)
EMPNO+DEPTNO+MGR+HIREDATE
100+10+101+10-JAN-1999
103+20+104+05-DEC-1995
104+30+NULL+30-OCT-1992
53. Create a datastage job for following output by using following source
SORUCE: (single column)
EMPNO+DEPTNO+MGR+HIREDATE
100+10+101+10-JAN-1999
103+20+104+05-DEC-1995
104+30+NULL+30-OCT-1992
TARGET:
EMPNO MGR
100 101
103 104
104 NULL
54 . Create a sequence job for above 55a and 55b jobs. For 55a job input is 55b job output.
55. Create a datastage job for compare two datasets and observe new record, update record and
delete record.
Before Dataset:
SSN NAME ADDRESS
A123 John Boston, US
B234 Michael Minneapolis, US
C345 Smith Michigan, US
D456 Clark New York, US
After Dataset:
SSN NAME ADDRESS
A123 John New jersy, US
B234 Michael Minneapolis, US
D456 Clark New York, US
E567 Gnan Mumbai, IND
F678 Ganesh Hyderabad, IND
56. Create a datastage job for extract only last 10 records from the source (source have 100
records)
57. If I want information in a separate file for your job name, invocation id, job start date time,
host, project name. How will you do this?
58. Source file/table having 1 Lakh records. I want to write 10% of these records into separate
file/table (with out using transformer). How will you do this?
Posted by at

1 comment:

Subscribe to: Post Comments (Atom)