Warnings from lookup against Teradata Enterprise Stage

[gsherry1]

Hello Forum,

I am getting many strange warnings (close to 100). They come in the form:

ExtractConfiguration,3: Query returned no results. No read will be performed.

DateControl,9: Insufficient blocks for partition 9. Reading suppressed.

They occur when performing a lookup against Teradata Enterprise Stage. The 3 rows present in the table are successfully read, but I am receiving all these unwanted warnings.

Any suggestions?

Thanks,

Greg

[roy]

Hi,
Please post all the errors you get (in hope it will shed more light).

Query returned no results imply your sql returned no results, but sometimes the first error might cause others to pop up that are only a concequence of the first one, as you get with C++.

So please post the errors as the apears in your log first to last.

[ray.wurlod]

The Teradata Enterprise stage reads the results of the query into a virtual Data Set against which the lookups are performed. If the query returns no rows, then the Data Set is empty, and there's no way any lookup would work, hence the "no rows will be processed" message.

Since we have no information on what the DateControl stage is, it's impossible to comment on the "Insufficient blocks" message on node number 9. Perhaps you could check whether the file system mentioned as the disk resource for node number 9 in your configuration file is not full (does not fill during the run).

[gsherry1]

More Info from poster:

1. There are no failures, only warnings.
2. My config file has 2 nodes.
3. Only one of the warnings is still appearing. Please ignore the first one I reported.
4. Additionally, there is one information message after the warnings with the following report:

DateControl,0: Orchestrate Teradata Read Operator Summary
==========================================
Step Configuration:
-------------------
Total slave sessions: 100
Slave processes: 50
Nodes used: 2
Elapsed Times (in seconds):
---------------------------
Setup: 13
Reading from Teradata; 5
Disconnection: 4
Total: 22
Data Volume:
------------
Rows read: 3
Bytes read: 42
Rows rejected: 0

DateControl is the Teradata Enterprise stage.
I receive 47 warnings of the form:

DateControl,<n>: Insufficient blocks for partition <n>. Reading suppressed.

Where <n> ranges between 3-49.

My guess is that the query runs against each of the slave processes. Then some collection of the results occurs and a warning is generated for each slave that did not return a result.

[ray.wurlod]

Reference to "partition 9" is concerning, if you only have two processing nodes. Is it a partitioned table? If so, have the DBA ensure that there is sufficient work/scratch space for all partitions.

[track_star]

Greg--
How many VPROCs or AMPs is the table spread over? I am guessing that you have a rather large TD instance (like at least 50 VPROCs). You might want to set requestedsessions to something more manageable to keep the repartitioning to a minimum. You set it in the Advanced connection options in the stage, and it would look like this: requestedsessions=4.

The key to using that is to keep in mind how many nodes you're calling in the config file. For TD, the operator uses two values to determine how many players to start, requestedsessions and sessionsperplayer. By default, if no value is set for requestedsessions, it looks at the number of VPROCs used for the table. Sessionsperplayer defaults to 2 (one read session and one write session). Here's a rule of thumb:

requestedsessions / sesssionsperplayer = no. of player processes (which works best when it is the same value as the number of nodes in the config file)

Performance wise, it keeps the operator from repartitioning the data (which is expensive) and can cause various resource issues.

HTH....

[gsherry1]

Thanks track_star,

Not only did that eliminate the warnings, but startup time improved by a factor of 10 and runtime by a factor of 3!

[bcarlson]

Quick question. We have a server that connects to both DB2 and Teradata. We have 16 nodes defined in the APT_CONFIG file because that maps directly to our DB2 system of 32 nodes (1 DS node to 2 DB2 nodes). We have 124 VPROCS on the Teradata side.

What exactly is a player? Is it 1:1 to the number of DS nodes you have defined? So we'd have 16 players?

So by default, 124 sessions are requested with 2 sessions per player which equates to 64 player processes. Is 2 good for sessionsperplayer or is there a better number to work with? If 2, do I understand you that we should be looking at perhaps 32 for requestedstessions?

32 / 2 = 16

We are new to using DS with Teradata and are struggling to find the best fit and configuration, so I really appreciate the info that you've posted already and any help you may provide!!

Brad.

[ray.wurlod]

If you have "disable combination" set to True, there is one player process per Orchestrate operator on each processing node mentioned in the configuration file.

If you have "disable combination" set to False, then some player processes will manage more than one operator, so there will be fewer player processes.

The metaphor is that of an orchestra. For each parallel job there is one conductor process, there is one section leader process per processing node, and there are multiple player processes per node, depending on your job design.