DSXchange

For every day run, I have to truncate the target table before loading.
The job runs on a 2- node configuration.

I am using a OPEN Command in the SQL server Enterprise stage.

DELETE FROM tablenameA;

This is not working . Looks like I cannot use a commit in open command. whats the feasible approach to do?

TRUNCATE TablenameA perhaps? This should be non-transactional.

Firstly, truncate table commands can be executed only by the owner. So the DBA's are reluctant to grant the owner privilege for the Datastage user.

Secondly, there is a reference for Foreign key to this table. So Truncate table command cannot be used in this case.

What is the best alternative for this process?

Typically, that would be a stored procedure written by the DBAs that you would have execute grants on. It would have the grants needed to do the truncate and could handle any RI constraints as well.

I used an ODBC connector stage instead of SQL server enterprise stage , to acheive this delete table request in run before SQL comand and then

--- Continuation to the previous post


I did " delete table " in before sql and then insert into table.