Page 1 of 2
Deleting records
Posted: Sun Jun 04, 2006 11:13 pm
by rafidwh
Hi All,
My source file is a flat file and the target is a OCI
Now i need to apply a logic like
If key fields matches and also
col1 =tar.col1 and
col2 =tar.col2 and
col3 =tar.col3 and
col4 =tar.col4
if the above condition satisfies then I should delete the records from the table
Please suggest ,how to implement the logic in Datastage
should i use the notisnull for all the columns and select the delete option from the update action from OCI
Should I need to write any stored procedure
Is there any other way to get this logic
Thnaks in advance
Saik.
Posted: Sun Jun 04, 2006 11:54 pm
by roy
Hi,
Bare in mind feeding a DB stage rows for deletion is bound ot have poor performance!, simply since your issueing each row in a seperate delete.
You might boost performance by populating a temporary table with the relevant rowid values you want to delete and issue the delete with a subquery on the temp table.
In any case of performance issues I strongly recomend consulting your DBA for optimal performance.
IHTH,
Posted: Thu Jun 08, 2006 3:20 am
by rafidwh
anymore suggestions guys
thanks
Posted: Thu Jun 08, 2006 4:08 am
by ArndW
What was missing from Roy's post, in your opinion?
Posted: Thu Jun 08, 2006 3:36 pm
by ray.wurlod
This is not the first time this question has been posted, and it has been answered in the past. Asking the question again wastes all our time.
![Mad :x](./images/smilies/icon_mad.gif)
Posted: Fri Jun 16, 2006 2:27 am
by rafidwh
Hi Roy,
I couldnt view your complete solution since it is saying premium content,Can you please explode the solution and let me know please.
Sorry ray.wurlord to again posting this.
Thanks in advance
Saik.
Posted: Fri Jun 16, 2006 2:55 am
by nick.bond
You have to sign up to view the premium content - follow the link through the icon that blocks the content
Posted: Fri Jun 16, 2006 4:31 am
by sb_akarmarkar
Please make rest of column col1, col2,col3 and col4 as key column also in datastage column defination tab......
Thanks,
Anupam
Posted: Fri Jun 16, 2006 4:34 am
by ArndW
sb_akarmarkar wrote:Please make rest of column col1, col2,col3 and col4 as key column also in datastage column defination tab......
I'm not sure what you mean by this suggestion - it won't affect the actual DELETE command at all.
Posted: Fri Jun 16, 2006 4:35 am
by rafidwh
Hi Anupam,
you mean to say that I should define the non key columns in the table as keys in the job and select the delete option from the update action from oci ,with out putting any logic?
Regards,
Saik
Posted: Fri Jun 16, 2006 4:39 am
by sb_akarmarkar
Please make rest of column col1, col2,col3 and col4 as key column also in datastage column defination tab......
ArndW wrote: I'm not sure what you mean by this suggestion - it won't affect the actual DELETE command at all.
I mean to say use update action as replace existing row ..... in ODBC and make rest of column col1, col2,col3 and col4 as key column also in datastage column defination tab......
Thanks,
Anupam
Posted: Fri Jun 16, 2006 4:41 am
by sb_akarmarkar
rafidwh wrote:Hi Anupam,
you mean to say that I should define the non key columns in the table as keys in the job and select the delete option from the update action from oci ,with out putting any logic?
Regards,
Saik
Yes with Update action as replace existsing row
Thanks,
Anupam
Posted: Fri Jun 16, 2006 4:45 am
by rafidwh
But I want to delete that row completely from the table
Posted: Fri Jun 16, 2006 5:27 am
by sb_akarmarkar
rafidwh wrote:But I want to delete that row completely from the table
yes it will delete....
If you see query in view sql there you can see that whatever column you made as key come under where clause of delete statement. and insert is as usual ....
Thanks,
Anupam
Posted: Fri Jun 16, 2006 6:14 am
by rafidwh
The what the delete existing rows does