Strange error in Teradata Ent. Stage (read)

[bcarlson]

We got an interesting error from a DataStage read from Teradata.

3695 A Single AMP Select statement has been
issued in FastExport.
Explanation: The user has attempted to execute a
Select statement which accesses a single AMP while in
FastExport. This is most likely with equality constraints
on Primary or Unique Secondary Indexes. Use regular
SQL for such queries

The table that was being read was a single-amp table. Why can't we run a query against the table with the Teradata Enterprise stage? If we can't use the TD Ent stage, then what can we use? And why in the world would Ascential make a stage that couldn't read a simple lookup table???!!

Brad

[chulett]

Don't know TeraData, but it seems the sticky point is this bit: "while in FastExport". Is that an optional something that can be turned off?

[ghila]

Hello,

Have you tried adjusting the DB Options of the TeraData Enterprise stage? I mean "RequestedSessions" and "SessionsPerPlayer".

I never worked with a single AMP Teradata system. Our systems are between 2 to 360. But maybe the DB Options can help you to remove the error.

Try as well to set APT_TERA_NO_PERM_CHECKS.

[yannish]

Are you setting any WHERE-constraints in your select statement? FastExport cannot handle selects that use unique key constraint that would only return one row. This is set from the Teradata's side not by Datastage.

Janne

[bcarlson]

Are you setting any WHERE-constraints in your select statement? FastExport cannot handle selects that use unique key constraint that would only return one row. This is set from the Teradata's side not by Datastage.

Janne

I think this might be the problem. The query we run is a lookup against a table full of dates. Only one row should be returned, so yes, the where clause enforces that. Maybe we just need to either pull in the entire table as part of a lookup (at the most, it is only going to be a few hundred dates), or we just incorporate it into another query.

FYI, Daniel, the system is about 120 amps. It is just the result set of of the query that is single-amp. My bad, I misworded my description.

Thanks all for your help!

Brad

[chulett]

Don't know TeraData, but it seems the sticky point is this bit: "while in FastExport". Is that an optional something that can be turned off?

So... is that a 'no'?

[bcarlson]

As I understand it, the Teradata Enterprise stage uses FastExport to read data and FastLoad to insert. FastExport and FastLoad are Teradata utilities that DataStage uses, kind of like the DB2 load utilities used by the DB2 Enterprise stage.

So, all that to say, 'no'. I don't think you can turn off FastExport in the stage.

Brad.

[Krazykoolrohit]

As I understand it, the Teradata Enterprise stage uses FastExport to read data and FastLoad to insert.

One correction. It doesnt use it. It invokes the teradata utility. Just like telling them "you do the work i enjoy the perk"

[bcarlson]

I know this is way after the fact, but I just ran into this problem again and decided to see what was on DSXChange and found my own posting. Discovered that I never added the solution/workaround.

Old SQL:

Code: Select all

select process_dt, 1 as tmp_join_key
from srce_process_dt
where srce_system_cd = 'ABC'

New SQL:

Code: Select all

select process_dt, 1 as tmp_join_key
from srce_process_dt
where srce_system_cd = 'ABC'
sample 1

I still get some interesting warnings, but it works.

Brad.

[throbinson]

There is a limit to the number of utility slots available at any given time in a Teradata system. You are using one to return a single row. I think the Teradata API stage would be more appropriate.
Also the IBM documentation clearly states that single Amp Selects are not allowed as a Teradata Distributed Fastload restriction. I don't know enough about Fastload to state why.