Deleting SQL rows with DataStage

[kwt10]

We have a file that is coming in as a complex flat file. We take the file and read each line (they can either be modified, new or deleted) to check for the type of data. We then use DataStage to input the records into the appropriate SQL table using ODBC to update or add new. Our situation is that if the record is a delete record then we need to delete the corresponding row from the SQL table.

Can anyone give me some ideas on how to get DataStage to do this? We are looking at kicking all the delete records to another file and then running a job to call a SQL delete for each one, but we are sure that there must be a way inside DataStage to do this.

Thanks for ANY help.

[chulett]

Unfortunately, without a specific Update Action to do deletes (no idea why that would be missing from the ODBC stage ) you'll have to fall back on Custom SQL.

Simply pass in whatever fields make up your 'key' and then issue a custom sql to do the deletes. The online help for the ODBC stage can help with constructing it - like needing to use ? as a parameter marker for each column you pass in.

[kcbland]

Simply load an ETL work table of the rows you wish to delete. Then, execute a sqlplus/isql/dbaccess/whatever call to issue a sql delete statement where your rows in the main table are in the ETL work table. This is pretty straightforward.

You failed to state your database, so I can't give you the exact solution. If you have Oracle, your SQL would be something like "DELETE FROM YOURTABLE WHERE YOURTABLEKEY IN (SELECT ETLWORKKEY FROM ETLWORKTABLE)"

[kwt10]

Thanks for all the help (so far). Our database is a Microsoft SQL file. I see what you are saying by creating a file that will contain the keys to the records that we want to delete. Then use a SQL call for the records in that file to delete the SQL rows that need to be deleted.

Thanks again

[kcbland]

Yeah, but I said load the file of delete rows into a work table and use SQL to perform the delete. Better yet, join the delete work table to the main table and write the rows to an audit file before doing the delete, in case anyone questions what just got deleted.

[kwt10]

Gotcha!!! Great idea KCBLAND, I'll give is a shot and see what we can come up with.

Thanks again!!

[roy]

Hi,
Just wanted to remind you that SQL Server can perform a delete with join, so almost the same syntax would do both for the audit trail (save of deleted data) and the actual delete.

IHTH