DSXchange

I am currently trying to insert data into Teradata Mload table. It's working fine. But what should be the BEFORE CLAUSE that can be used so that the table may be truncated or deleted or released before it starts processing?

Can anybody help me with the list of commands that will be applicable in the BEFORE (M)LOAD CLAUSE?

any DML

Your Teradata DBA?

nayanpatra wrote:I am currently trying to insert data into Teradata Mload table. It's working fine. But what should be the BEFORE CLAUSE that can be used so that the table may be truncated or deleted or released before it starts processing?

The correct command to clear that particular table.

keshav0307 wrote:any DML

Is there any particular syntax for it? Could you please give me some examples?

It's a Teradata command, not a DataStage command. Because we don't know all the identifier names in your Teradata installation, we can not help. Ask your database administrator (DBA) for assistance on Teradata command syntax.

ray.wurlod wrote:It's a Teradata command, not a DataStage command. Because we don't know all the identifier names in your Teradata installation, we can not help. Ask your database administrator (DBA) for assistance ...

I checked with the DBA. Actually I want to delete the Teradata tables before inserting data into them. Some of the installed queries applicable are delete, drop, release. But there is no identifier for truncate. So to be precise how am I going to use the delete syntax.

I have already tried with the delete command. But hasn't worked. I suppose there is a particular syntax for it. Could you please help me with the right syntax?

That is why you have a DBA supporting you, ask them. Or wait for someone here who may know Teradata to stumble across this post. I, for one, do not.

it should generate the drop statement itself for all the tables your specifying for Mload........ for the before command we usually check the two options specified in datastage in the before tab.

hamzaqk wrote:it should generate the drop statement itself for all the tables your specifying for Mload........ for the before command we usually check the two options specified in datastage in the before tab.

Apart from checking two options ( one to Drop error tables and second to treat SQL errors as non fatals) use below command
in before --script in TD Mload stage.

delete from <DATABASE_NAME>.<TABLE_NAME> all;

This has worked for us to delete the table before load by using mload stage(script).
Again if you need the stats of records went to error table then dont check -Drop error tables option.