Interested to Buy Any Domain ? << Click Here >> for more details...
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 ?
- 5 Answers
- 10369 Views
- Infotech, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
Difference between the Sequence and Sequencer in DS?
Describe the architecture of datastage?
How to create user defined environment varibale(parameter)?
Is there any possibility to generate alphanumeric surrogate key?
Hi guys, Design job sequence, we have 3 sources, in that 1st source in abort then only run the remaining sources.. How please design the job. Thanks.
What are the different type of jobs in datastage?
What is lookup table?
How do u convert the columns to rows in datastage?
What is diff between Junk dimensions and conform dimension?
how can we create rank using datastage?what is the meaning of rank?
How can we perform 2nd time extraction of client database without accepting the data which is already loaded in first time extraction?
Unix command to view the data in a dataset
Teradata 
