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?
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
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?
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.
