how to get dataset column value in before stage

kamesh · Post by **kamesh** » Thu Aug 05, 2004 8:30 pm

I want to delete records in the db2 stage in before stage based on the dataset column in the where condition, can you pls any one tell me what is the syntax for this

Quary like in before stage

delete from table_name where column_name=dataset_column_value

Thanks
poduri

vmcburney · Post by **vmcburney** » Thu Aug 05, 2004 9:28 pm

Just need a bit more information, are you talking about a DB2 stage as a data source or a destination? Are you trying to perform a replace action on rows?

Try creating a DB2 output stage, select "Replace existing row" as the database update option and then look at the SQL tab, it will show you the SQL statements generated by a replace. There should be a delete followed by an insert. This may be more appropriate place for your delete statement as it will get executed for every row being processed.

The before tab on a DB2 stage is typically run just once per job execution whereas I think you are asking for a delete for each row in the job.

kamesh · Post by **kamesh** » Thu Aug 05, 2004 10:08 pm

yeah i am using db2 out put stage.. I have dataset having 10 columns and i am inserting records in to target table and also same time based on one of columns in the dataset value in before stage i am deleting records in other table. i dont want delete row wise..

ray.wurlod · Post by **ray.wurlod** » Thu Aug 05, 2004 10:41 pm

That description suggests that you DO want to use the value in each row to delete from the other table.
I understand from your description that you don't want to commit individual DELETE commands (one per row). This you would need to manage via "rows per transaction" being set to "all" (0), so that all of the generated DELETE statements are treated as a single transaction - that is, no COMMIT is transmitted until all rows have been processed. Make sure that the database server can handle a transaction of this size; it must have the capacity to store the entire set of before-image records (just in case you end up issuing a rollback).

kamesh · Post by **kamesh** » Thu Aug 05, 2004 11:02 pm

Thanks

Can you tell me how to pass column value to sql query in before stage?

ray.wurlod · Post by **ray.wurlod** » Fri Aug 06, 2004 5:12 am

It's not possible in before stage.

You do not have the column value until the stage itself has begun processing rows.

Do you mean the column NAME ?

Eric · Post by **Eric** » Fri Aug 06, 2004 9:33 am

I think the parallel DB2 stage has a delete rows option. The dataflow provided to the stage is used to determine which rows to delete. I think you would need to make this a seperate job though and not part of a before routine.