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

i WANTED TO USE THE RANGE LOOKUP SCENARIO IN DATASTAGE 7.5.2 SRVER JOB.i HAVE A DATE FIELD IN SOURCE AND I SHOULD MATCH IT WITH A FIELD IN LOOKUP FILE.BUT,THE FIELDS SHOULD MATCH EVEN THOUGH THERE IS SOME RANGE.CAN SOMEONE TELL ME HOW CAN I DO THAT. THANKS

1 Answers  

How many number of reject links merge stage can have?

7 Answers   TCS,

In my source i have 10 records but i want 11 records in target. How can i acheive this in Server jobs.

2 Answers   IBM,

which is the most complex job in your career? Please tell me answer?

2 Answers   IBM, Wipro,

Can you filter data in hashed file?

1 Answers  

CHANGE CAPTURE

1 Answers   CTS,

What is difference between symmetric multiprocessing and massive parallel processing?

1 Answers  

What is the use of datastage designer?

1 Answers  

What are the difference types of stages?

1 Answers  

A job is having only 2 stages I/p dataset and target table.Job is taking very long time to load 50 million records.How to improve performance of this job.

3 Answers   IBM,

What are the different options associated with dsjob command?

1 Answers  

What is datastage?

1 Answers  

For more Data Stage Interview Questions Click Here
Categories