Teradata write created hundreds of sessions

[bcarlson]

I guess You're trying to load a terradata tables isn't it ?? If so did you set "-sessionsperplayer" and "-requestedsessions " as DB options ???
All the best

I copied this from a posting by l_homme_de_rio made in response to "multiload utility in Parallel mode", viewtopic.php?t=90722.

While we have been using Torrent and now DataStage PX with DB2 for several years, we are very new to using PX with Teradata. I have gotten a read to work successfully - after some fighting. I am now having problems with the write. In my read, I had set sessionsperplayer=1, but did not use the requestedsessions option. The read worked fine.

FYI, Our setup: We are running PX 7.1 on an IBM AIX server with 8 logical nodes. We have 1 physical node for our dev Teradata database, running V2R5.1, with 7 amps.

Now that I am trying a write, I just about blew up our Teradata database with all the sessions it tried to create. I used the same settings as used in the read. In the same topic mentioned above, I noticed another posting with this comment (again, user l_homme_de_rio):

Note: Setting the value of SessionsPerPlayer too low on a large system can result in so many players that the step
fails due to insufficient resources. In that case, SessionsPerPlayer should be increased. The value of the optional
RequestedSessions is a number between 1 and the number of vprocs in the database. The default is the maximum number
of available sessions.

I am wondering if the bolded comment is describing what happened to me. Do I understand correctly that in PX terms, 1 node (the Unix server, that is) = 1 player? If not, how can you determine the actual number of players that are being used? I am guessing that I will need to use the RequestedSessions option? If we have 7 amps, would I set that option to 7?

Any idea why I would run into problems with the write, when the read worked just fine?

I really appreciate the help. Thanks!

[nrevezzo]

It would be unusual to have a 7 AMP Teradata system. The number of AMPS is usually an even number; 7 nodes would be more likely. This would translate into x-number of AMPS; actually VAMPS.
When the Teradata Enterprise stage is used for writing it runs the Teradata FastLoad utility. The default number of sessions for a FastLoad utility is = the number of VAMPS.
My recommendation, if indeed you have a 7 NODE system, is set the RequestedSessions to 16 and let the SessionsPerPlay default to 2 or set it to 2.

Be aware that the processing restrictions from the Teradata Enterprise Stage are the same restrictions imposed by the FastLoad Utility.

[bcarlson]

Well, unfortunately, it really is a (slow!) single node 7-AMP system:

Code: Select all

*** Query completed. 7 rows found. 2 columns returned. 
 *** Total elapsed time was 1 second.

 vproc        count
------  -----------
     0         1973
     1         1848
     2         1848
     3         1847
     4         1848
     5         1846
     6         1847

So, how did you come up with 16 for RequestedSessions and 2 for SessionsPerPlayer? I really need to understand the relationship between these settings and the number of AMPs on our system.

This 1-node 7-AMP system is for development, but we have a 4-node 28-AMP system for production, soon to be upgraded to 12 nodes and ??-AMPs. I am guessing that we will need to parameterize the values we use for RequestedSessions and SessionsPerPlayer so we can keep the process dynamic and don't have to hardcode anything.

The problems I have been having are related to using the Teradata Enterprise Stage in write mode, which I believe works like DB2 and simply sends batches of inserts to the database. I haven't tried using the plugin stages yet (for mload, fexp, tpump, or fastload).

Would I use the same sessions setting regardless of the TD stage I use? Or would the settings for a write be different than for a MultiLoad or FastLoad?

[nrevezzo]

The 16 requested sesions is a rule of thumb I use for a Teradata system with more than about 100 VAMPS. Because the default setting in Fastload, MultiLoad, and FastExport is 1 session per VAMP I try to limit the number of sessions that my job starts to reduce start-up time. The sessions per player I never used until I started using DataStage and 2 is the DataStage default.
You may want to discuss which DataStage stage to use with you Teradata DBA because there are implications to using the Teradata Enterprise stage which invokes FastLoad versus using the TDMLoad stage which invokes MultiLoad. FastLoad and MultiLoad are both good large volume loading utilities but each has its strengths and weaknesses. That's why they both exist.
BTW, when the Teradata Enterprise stage is used as a source it invokes the FastExport utility.

Your Teradata DBA and DataStage administrator should set down guidelines for which stage to use when based on data volumes. On my current project we use mostly the TDAPI stage for reads and writes with minimal volumes and the TDMload stage for high volume writes to a populated table.