1. Categories >> Software >> Data Warehouse >> Data Stage
  2. Suggest New Category

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 ?

Question Posted / karthikmareedu

Answers were Sorted based on User's Feedback



If seg file having 10 records ex:eid 1 2 " " 10 if oracle databa..

Answer / subhash

we can delete in 2 ways:
1) in TGT Oracle Stage,
select 'Write Mode' as 'DELETE'
and write the delete Query as
"DELETE FROM EMP_TGT
WHERE EID IN (SELECT EID FROM EMP_SRC)"

2)Select 'Write Mode' as 'UPDATE' and write some dummy update query(This statement will not execute as WHERE condition 1=2) as
"UPDATE EMP SER EID='1111' WHERE 1=2"
then write delete query in 'Run before SQL statemets' as
"DELETE FROM EMP_TGT
WHERE EID IN (SELECT EID FROM EMP_SRC)"

Is This Answer Correct ?    4 Yes 0 No

If seg file having 10 records ex:eid 1 2 " " 10 if oracle databa..

Answer / vaibhav

We can perform inner join between seq file & oracle stage and then in target oracle stage have write method as Delete where we can specify delete query using orchestrate incoming rows..

Is This Answer Correct ?    3 Yes 0 No

If seg file having 10 records ex:eid 1 2 " " 10 if oracle databa..

Answer / vinod upputuri

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

If seg file having 10 records ex:eid 1 2 " " 10 if oracle databa..

Answer / shar

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

Is This Answer Correct ?    0 Yes 0 No

If seg file having 10 records ex:eid 1 2 " " 10 if oracle databa..

Answer / shilpasagarg

1)The approach is to load the seq file data into one
temporary table say EMP_TEMP. (SEQ -> ODBC Stage (Oracle
(EMP_TEMP table)
EMP_TEMP contains
eid
1
2
"
"
10

2)Then take the ODBC Stage to connect to Oracle DB (EMP
table).
EMP table contains
eid
1
2
"
"
100

Here use the below query to delete the matched EID's
permanantly from oracle DB

DELETE EMP
FROM EMP
INNER JOIN EMP_TEMP
ON EMP.EID=EMP_TEMP.EID



Is This Answer Correct ?    0 Yes 3 No

Post New Answer

More Data Stage Interview Questions

what is datastage job Monitoring

6 Answers   CTS,

what is main difference between change capture and change apply stages

4 Answers   IBM,

Unix command to view the data in a dataset

2 Answers   IBM,

file having these input and we have to get 3 output using same job Input 1 1 1 2 3 4 4 4 o/p1 o/p2 o/p3 1 1 2 2 1 3 3 1 4 4 4

0 Answers  

explain how to create SCD-2 IN DATASTAGE 7.5X2 PLZ EXPLAIN WITH 4 OR 5 RECORDS TAKE IT EXAMPLE AND JOB DESINGN URGENT

3 Answers   IBM, Polaris,




in sequtial file 2 columns avaliable,iwant only one column load the target how do it.

10 Answers   Wipro,

Instead of using shared container in a job, I use jobs which perform similar function as Container in the sequence. Then what is the need of Shared Container?

1 Answers   TCS,

can we see the data in fixed width file? how can u change the datatype of fixed width files?

1 Answers   Infosys,

how to capture rejected data by using join stage not for lookup stage. please let me know

3 Answers   Satyam,

Hai..,in datastage how to explain project in interview?please explain any domain please.

1 Answers   Wipro,

1.What is the flow of Transformer? 2.How can you do INDEX table in DataStage level?

0 Answers   EDS,

what is .dsx files

3 Answers   IBM,

For more Data Stage Interview Questions Click Here
Categories