Teradata SQL Initiate failed in DS8 TD Conn Read job

[bcarlson]

I have a job that reads from Teradata and writes to DB2. We pass in the table name, an optional where clause and an optional order by clause. It is failing:

Teradata_Table_Read,0: RDBMS code 2113: libexportop.so) instance(1): INITIATE method failed with status = Fatal Error SQL statement: LOCK ROW FOR ACCESS select * from D_HEIDSWK.DLY_ACCOUNT where 1 = 1 ORDER BY CUST_ACCT_ID (CC_TeraParallelTransporter::initiate, file CC_TeraParallelTransporter.cpp, line 1,249) [pxbridge.C:5949]

Here is the query in a little clearer format:

Code: Select all

LOCK ROW FOR ACCESS select * from D_HEIDSWK.DLY_ACCOUNT where 1 = 1 ORDER BY CUST_ACCT_ID 

When we construct it in the TD Connector, it looks liek this:

Code: Select all

select * from #ds_td_table# where #src_filter_condition# #src_orderby_clause#

Note: The filter has a forced default of 1 = 1 (recommended by teh DBA so there is always a where clause).

Any idea what is going on? It is not like there is a syntax error. The same query runs fine at the command line or in a query tool.

Thanks!

Brad.

[chulett]

This help at all?

http://www-01.ibm.com/support/docview.w ... wg21320296

[bcarlson]

Thanks, I took a look. Where do I find the option to turn off the logic push to the source? Not sure if it will help or not. It seems to be referring to pushing DS-internal sorts to the database, and this is a SQL with a sort in it already.

Brad.

[bcarlson]

We worked with Tom O'Shea (DSXChange user toshea), a resource at IBM, that gave us the following information:

Unfortunately, the error messages from the TPT export operator are rather poor. Teradata claims to have better error messages in Teradata 13.

You guys actually ran into this problem years ago when I first visited. The problem is that the TPT export operator does not support parallel exports when the query contains an ORDER BY. You must either remove the ORDER BY or execute the stage sequentially.

Also...

By the way, I had reported this issue to Teradata almost 2 years ago. Back then they claimed that they would fix it in Teradata 13, but if you are using Teradata 13 [then it may not be] fixed. I had filed the issue under Teradata incident RECD2ESVG, and they claimed they would open Teradata defect report DR129372 to fix later.

Code: Select all

Incident Number             RECD2ESVG 
Site ID                     ASCENTIAL2 
Site/Store                  ASCENTIAL SOFTWARE - ASCENTIAL2 
Customer Reference Number   
Customer Serial Number       
Contact Name                Patricia Morales 
Status                      ACTIVE 
Priority                    3 
Open Date (GMT)             2008/12/22-22:53 GMT 
Close Date (GMT)            N/A GMT 
Brief Description           RFC: CLIENT NF  DR129372 TPT export operator 
returns fatal error 2113 when multi-instance with ORDER BY 
_____________________________________________________________________ 

Activity Date:              2009/07/19-00:06 GMT 
Activity Type:              Current Incident Status 
Activity Author:            Patricia Morales 
Activity Synopsis:          <VIEWABLE>Sent following e-mail to Tom O'Shea. 
Activity Comment: 
Current Activity Status : RFC DR 129372 was created for this request. 

We are now looking into opening an incident with Teradata to see what, if anything, can be done to address this.

Brad.

[bcarlson]

ARRGGHHHH. The Teradata incident was to fix the error messaging, not to fix the problem. The RDBMS code 2113 is the default code when an error occurs and there is no specific error message to display.

We are now going to open our own incident with Teradata and see if we can convert it into an RFC and have the TPT updated to be able to accomodate a sorted parallel read. Of couse, that does not help us right now...

Brad.

[bcarlson]

Workaround - create multiple DS jobs that accept 0, 1, 2, or 3 sort columns. What a pain... more jobs to maintain.

Question - will DataStage ever be upgraded to accomodate a varying number of fields for sorts, joins, merges, etc? We can parameterize the field names themselves, but cannot alter the number of fields to be used. This would be extremely useful for creating more generic jobs.

Brad.