Deleting records

[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.

[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,

[rafidwh]

anymore suggestions guys

thanks

[ArndW]

What was missing from Roy's post, in your opinion?

[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.

[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.

[nick.bond]

You have to sign up to view the premium content - follow the link through the icon that blocks the content

[sb_akarmarkar]

Please make rest of column col1, col2,col3 and col4 as key column also in datastage column defination tab......


Thanks,
Anupam

[ArndW]

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.

[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

[sb_akarmarkar]

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.

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

[sb_akarmarkar]

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

[rafidwh]

But I want to delete that row completely from the table

[sb_akarmarkar]

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

[rafidwh]

The what the delete existing rows does