DELETE 22 tables

[srini.dw]

Hi,

I have a requirement like based on condition i need to delete the rows from 23 teradata tables, any ideas please. (from single job).
It will be run once in a year.

thanks

[ray.wurlod]

There's nothing in DataStage that will make this any easier. Create a script (shell script or BTEQ) to do this task.

[srini.dw]

Thanks, for the reply

1st job
I have a datastage job where i will do some processing and get 2 columns, deptid n dname.

2nd job
Based on the above 2 columns i need to delete 23 tables i.e., where condition.

How to do that in BTEQ or shell script.

Thanks

[ray.wurlod]

Dump your results into a file and have the script read that file.

[srini.dw]

Thanks, Can you please give me more details with regard to shell script, how to pass the 2 parameters inside shell script

[tsn]

You can write stored procedure to delete the 22 tables.

pass the filename, path as parameter, read the file and store the value of the content in variables and then while calling the stored procedure pass on the variables value as parameters to the procedures.

[keshav0307]

in the second job you can have a MLOAD/TPUMP stage,
and based on the condition, delete from the tables.
you can have 23 delete statement in the MLOAD/TPUMP script, and each statement will run only when the condition statisfy.

[ray.wurlod]

Was the requirement to delete tables or to delete records from tables.

[toshea]

You could use a Teradata API stage that contains the following queries:

DELETE FROM Table1 WHERE condition;
DELETE FROM Table2 WHERE condition;
etc.

You could put the queries in the main SQL or in the Before/After tab depending on whether you want to send in a row for the condition or want to send 0 rows and base the condition on job parameters.