DSXchange

I'd like to delete all the rows from a DB2 table using an ODBC Stage before doing a mass insert. How can this be done if there's no input/output from a stage? Also, the table has 500,000+ rows so I'd like to turn logging off. I read another post that mentioned using a command similiar to "ALTER TABLE TableName ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE" rather than using "Delete * from TableName". Again, there's no input or output; is there a way to do this easily?

Row generator --> Transformer --> ODBC
In the row generator set Number of records to one.

I don't see "row generator" in the palette...where is that?

I didn't read your post properly. Row generator is available only in parallel job.

Transformer --> ODBC
Define a dummy stage variable in transformer and add a constraint @OUTROWNUM < 2

In ODBC write a user defined sql to delete the table.

anbu wrote: In ODBC write a user defined sql to delete the table.

I belive, you have to define a dummy column in ODBC stage and need to Use/Handle the same in you user defined SQL, in order to pass the control from transformer stage to ODBC stage.

Thanks,
Nitin Swarnkar

Unlike any other stage, ODBC doesn't care if you don't bind all of the columns in your link into the SQL. So yes, you'll need at least one field in the link but you won't need to use it anywhere.

Rather than using a Transformer-->ODBC, is it possible to reverse this to ODBC(Delete)-->Transformer-->ODBC(Insert)? I need the Insert step to be dependent on a successful delete. By the way, the transformer has 2 inputs; the hash file to insert & the delete link with a dummy column. I keep getting an error stating there's an incorrect number of result columns on the Delete....there's gotta be an easier way to do this! Right?

No you'd still need something to trigger the first ODBC stage. You may as well use a Before SQL in the target ODBC stage.

Thanks for all the feedback; it's working now. I used the Transformer-->ODBC method with a dummy record as posted initially; that seemed to be the cleanest or most inituitive. I looked for the "before/after SQL" tabs that were mentioned, but I don't think they're available with ODBC connections.