Clear tables

jzijl · Post by **jzijl** » Mon Jan 15, 2007 7:36 am

Hi,

I want to clear tables using DataStage. The way I do it know is to read an empty table. In the target table I have selected in the update action 'Clear table then insert rows'.
I have the impresion that tables are cleared via the SQL statement delete from while I would prefer truncate table, which is much faster.

Kind regards,

Jan

chulett · Post by **chulett** » Mon Jan 15, 2007 7:53 am

Yes, 'clear' is a transactional delete of all rows in the table.

You don't actually need to read an empty table, any method to send zero rows to the stage will work. For example, starting from a Transformer stage with the constraint set to @FALSE works great. All you need is a bogus stage variable to allow the job to compile.

Then select 'Truncate table then insert rows' if that option is available. Are you using ODBC by chance? In that case, you could use User-Defined SQL I would think.

jzijl · Post by **jzijl** » Mon Jan 15, 2007 8:52 am

Dear chulett,

Thanks for your reply.

The 'Truncate table then insert row' is available. I should have looked at all the options in the 'update action' instead of the option used.

The other remark about not needing an empty table I will use also.

Thanks,

Jan

kumar_s · Post by **kumar_s** » Tue Jan 16, 2007 2:29 am

Is your topic Resolved?

jzijl · Post by **jzijl** » Tue Jan 16, 2007 3:52 am

Dear chulett,

I do not use an empty table anymore to clear/truncate a table.

Using truncate I meet the following problem.
I have two tables TableA and TableB there is a foreign key constraint between the two tables. When I use truncate for TableA it succeeds but when I use truncate for TableB I get the message:

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot truncate table 'TableB' because it is being referenced by a FOREIGN KEY constraint.

When I use clear for TableB it is succesfull.

What to do?

Kind regards,

Jan

chulett · Post by **chulett** » Tue Jan 16, 2007 8:12 am

That's a database constraint, not a DataStage one. Probably best to talk to your DBA and see what your options are. For example, if you have the grants you need you may be able to disable the FK constraint in the 'before sql' tab, do the truncate and then re-enable it 'after sql'.

Other places setup stored procedures and allow you to call them to do the truncation and that handle fun stuff like FK constraints automagically. Talk to your DBA.

Also, there's no need to contact me offline about answering this new question. Anyone could have answered it, it's not like I have exclusive 'dibs' on the solution. Never mind the fact that I was sleeping when this question and your email came in. Someone will get to you as soon as practical, so have patience.

jzijl · Post by **jzijl** » Tue Jan 16, 2007 8:53 am

Dear Chulett,

Thanks for the information and sorry for the inconvenience.
I was not sure if my post was looked upon after you had answered it and my addition to the post was today while the post was yesterday's.

Kind regards,

Jan

kumar_s · Post by **kumar_s** » Tue Jan 16, 2007 4:53 pm

Hi Jan,
Truncating the Child table while parent table data are still populated, will cause the Foriegn key Cascade constraint error. Make sure you truncating the Parent table first and then the child table.