TD Connector Stage Bulk Load

G_rajeshk · Post by **G_rajeshk** » Thu Mar 21, 2013 7:08 pm

Hi All,

I am trying to load to Teradata using TD Connector Stage.

Job Design

DB2 ----Transformer----TD Connector (Access Method - Bulk and Load type - Load)

arrry-size- 2000
record count-2000
access method- Bulk
parallel synchronization- no

I'm getting the following error. The job failed.Can somebody help us in resolving the issue .

Teradata_Connector_20,0: The estimated row length of 431 bytes multiplied by the array size of 2000 is 862000 which exceeds the maximum buffer size of 64260 [:0]

Teradata_Connector_20,0: RDBMS code 2113: libloadop.so) instance(1): INITIATE method failed with status = Fatal Error SQL statement: INSERT INTO IPPT_CALC_PRD
<snip>
(CC_TeraParallelTransporter::initiate, file CC_TeraParallelTransporter.cpp, line 1249) [pxbridge.C:5955]

Thanks in Advance.

Thanks
Rajesh G

chulett · Post by **chulett** » Thu Mar 21, 2013 11:02 pm

Looks like one solution would be to drop your array size below 150.

prasannakumarkk · Post by **prasannakumarkk** » Thu Mar 21, 2013 11:31 pm

array size = buffer_size/row length.

ray.wurlod · Post by **ray.wurlod** » Fri Mar 22, 2013 12:18 am

You could also increase the default buffer size in Teradata. Beware, however, if you're using multiple sessions, that they share the buffer. So

Code: Select all

ArraySize = (buffersize / sessions) / rowlength

crystal_pup · Post by **crystal_pup** » Fri Mar 22, 2013 12:31 am

Decrease the array size value to somewhere like 150 or 200.It will solve the problem.

PaulVL · Post by **PaulVL** » Fri Mar 22, 2013 5:53 am

A bad array size should only cause the connector to swap out to TMPDIR, not abort the job. (I think)

Double check the table definition you are using actually matches the table you are loading.

Try modifying your BULK to IMMEDIATE to see if if that gets rid of the abort. (as a test)

Did you happen to use Balance Optimization to help create the job?

chulett · Post by **chulett** » Fri Mar 22, 2013 7:28 am

crystal_pup wrote:Decrease the array size value to somewhere like 150 or 200.It will solve the problem.

150 or 200 won't... do the math and you'll see that the maximum array size without increasing the buffer would be 149.0951276102088 which is why I said less than 150.

ray.wurlod · Post by **ray.wurlod** » Fri Mar 22, 2013 5:07 pm

Paul is correct. Overstating the array size will not abort the job; lacking other errors it will finish successfully, though less efficiently. The issue is buffering within Teradata (which is why increasing the default buffer size there is an option).

chulett · Post by **chulett** » Fri Mar 22, 2013 5:13 pm

And yet all it complained about was the array size and then logged a fatal error. So the array size isn't the source of the fatality, something else is going on here? Perhaps something not included in the post?

ray.wurlod · Post by **ray.wurlod** » Fri Mar 22, 2013 10:03 pm

There's something wrong with the INSERT statement, which is why the INITIATE method failed.

chulett · Post by **chulett** » Fri Mar 22, 2013 10:25 pm

Makes sense. I removed the rather large list of column names as all it was doing was pushing the edge of the post way too far to the right. I don't recalling there being anything that looked improper with it, perhaps just column name issues?

kwwilliams · Post by **kwwilliams** » Sat Mar 23, 2013 8:31 am

Teradata TPT ignores the array size when in bulk mode. Ignore the warning on the buffer size. If you switch the access method to immediate, the array size would be an issue. IBM is trying to be informative in telling you about the buffer size, but I wish they would not display it unless it is applicable it confuses everybody.

Post your insert statement, that is where you will find the error.