DSXchange

I had a job that inserted 110 million rows into a Teradata table, which took two days to complete.

It was suggested that Teradata FastLoad would be faster, so I changed the Teradata Connector access method from Immediate to Bulk, and the bulk method to Load. The Teradata Connectivity Guide suggests that this combination will cause FastLoad to be used.

I also configured the err1, err2, log and work tables as required.

The job runs at exactly the same rate (rows/second) as previously. It's as if DataStage is using immediate mode to load the work table, and will start the FastLoad after that.

Does anyone have any suggestions about how to load an empty Teradata table faster?

(The Teradata instance is on a separate AIX machine from the DataStage engine. Teradata version is 14.xx)

Thank you for your time.

I would have used fast load script to insert 110 million records. As you said that you have already tried bulk load method in Teradata connector stage and it hasn't helped - try using the following environment variables in the job.

APT_TERA_64K_BUFFER
TENACITY
SESSIONS

I don't exactly remember the variable name for TENACITY and SESSIONS, you can search it on google for the exact name.

Cheers!

One of the restrictions against using Bulk/Load is that it can't be a Multiset table. By default the Teradata Connector, when you choose Replace or Create, specifies that Teradata should create its default table style, which in most sites IS Multiset.
In the Create Table options section in the Connector, specify No as the value for the "allow duplicate rows" and "make duplicate copies" to ensure that you create a Set table.