Bulk insert option in Teradata connector

[mouthou]

Hi all,

I am expecting a source file with more 12 million rows to load in Teradata with truncate & reload requirement. With the regular insert, this would be a time consuming load. Please let me know if there is any bulk load option using Teradata connector stage or any suggestion.

Couldn't think of using DRS stage etc as we don't have odbc connection setup.


Thanks

[vinothkumar]

Use Bulk option with load method as LOAD. This is equivalent to Fastload utility available in teradata. It will be faster in loading huge volume of data to empty table.

[mouthou]

Thanks. I already tried that but bulk option was not that effective with rows/sec. infact the jobs with normal insert run with 4000 rows/sec whereas with bulk option, it simply gave 2000 rows/sec range!

This may be the case if Fastload is done within DataStage. Is there any other way in/out of Datastage?

[vinothkumar]

Are there any primary indexes defined. Because it will highly impact the performance.

[mouthou]

Yes, there is one primary index but it is just a field with generated sequence number. Couldn't think it will create a performance issue for bulk load.

Actually, it is a good thought about the indexes and disabling/enabling as I used to do. Actually I had the same thought as my nightmare 2 days ago but left blank after getting up!

[chulett]

Be thankful it just 'highly impacts performance', in Oracle you can't perform a bulk load if there are any indexes on the table.

[sharmabhavesh]

As far as I know, Teradata generally has a primary index. I think only from versions 13 and above, this requirement has been made optional otherwise it was mandatory to have a primary index on Teradata tables (else Taearadat creates it itself).
So if I want to use fastload in versions before 13, will I get the same slow load results from Datastage as Vinoth is getting.