Importing the large flat files failing

Database

Importing the large flat files failing

Hi,

I am trying to import verylarge flat files(900 MB) with delimiter '|~|' but after 10 million rows the SQL Asstnt throws the following error in the log:

System.Runtime.InteropServices.COMException (0x800A0BDB): System resource exceeded.

Is it my system that doesnt have enough resources(like RAM,virt space etc ) or Teradata limitation in importing big files or is it the way i am trying to import the file?

The query i used is a follows:

   insert into <db_name.table_name> values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

Would appreciate any help on this.

Tags (1)
5 REPLIES

Re: Importing the large flat files failing

No response yet.

:(

Is it the limitation of TeraData i guess.

Probably we might have to limit TD and look at other options going forward.

Re: Importing the large flat files failing

I would say it is not a limitation of Teradata, as Teradata can handle trillions of rows. 

Rather it can be a limitation of SQL Assistant, ODBC and Network resources. 

When you import files using SQL Assistant, the DML statement is executed once for each record. So for such a large file executing 10M rows means executing this statement 10M times, obviously the system resources will exhausted. 

Further the SQL Assistant import is good for small files import, for large files you must use fastload, or multiload, else you can use TPT load OR update operators. 

Khurram

Re: Importing the large flat files failing

Handling 900MB file is a piece of cake for Teradata... Its the tool that you are using or probably it could be your system's limitations as well.

SQLA is not made to load such huge files... Try fastload, multiload or TPT and you will be amazed to see the processing power of Teradata.

Re: Importing the large flat files failing

Oh ok..

Thanks for the response Khurram and Qaiser.

Can you guide me which direction(links or other resources) i should proceed to deep dive into the error that I am getting ?

Appreciate your reponse.

Re: Importing the large flat files failing

Well, I am not sure if you are still thinking to solve the problem with SQL Assistant OR you are making your mind to use some other utilities(TPT, Fastload, Multiload). 

If you want to learn about TPT and other utilities then the best way is to go through the WBTs about these utilities. Also there are lot of topics on the developer exchange about these utilities.

You can also read the following for basics of TPT:

http://developer.teradata.com/tools/articles/teradata-parallel-transporter/teradata-parallel-transpo...

Khurram