What should be the Before clause in Teradata Mload?

nayanpatra · Post by **nayanpatra** » Wed Jun 10, 2009 4:39 am

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?

keshav0307 · Post by **keshav0307** » Thu Jun 18, 2009 6:54 pm

any DML

chulett · Post by **chulett** » Thu Jun 18, 2009 10:33 pm

Your Teradata DBA?

ray.wurlod · Post by **ray.wurlod** » Thu Jun 18, 2009 11:43 pm

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.

nayanpatra · Post by **nayanpatra** » Fri Jun 19, 2009 12:14 am

keshav0307 wrote:any DML

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

ray.wurlod · Post by **ray.wurlod** » Fri Jun 19, 2009 11:22 pm

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.

nayanpatra · Post by **nayanpatra** » Fri Jun 19, 2009 11:44 pm

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?

chulett · Post by **chulett** » Sat Jun 20, 2009 7:19 am

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.

hamzaqk · Post by **hamzaqk** » Tue Jul 07, 2009 4:09 am

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.

RShiva · Post by **RShiva** » Wed Jul 08, 2009 5:42 am

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.

DSXchange

What should be the Before clause in Teradata Mload?

What should be the Before clause in Teradata Mload?

Re: What should be the Before clause in Teradata Mload?

Example

Identifier names of my Teradata installation