I've searched and read many posts regarding terasync and requestedsessions and thought from this I had gained an understanding of how to control the process. Unfortunately having tested that understanding I am now again confused.
First I'll state my understanding and maybe someone can point out where it is flawed, then my experience.
I've learned that the number of players resulting from a TES is not controlled by the config file but rather by the requestedsessions and sessionsperplayer settings included in the db connection string. Therefore with requestedsessions=48 and sessionsperplayer=12 I should expect to see 4 TES players, which should match nicely with my 4 node config file. However, I would continue to have 4 TES players if I ran with an 8 node config file.
I also understand that matching the number of TES players to the number of nodes defined in the config file should result in the least amount of re-partitioning.
I expected that by reducing the number of players I would see fewer updates of the Terasync table. However, that is not what I have experienced.
Unfortunately when I first submitted my first job utilizing a TES I did not specify requestedsessions nor sessionsperplayer and therefore saw hundreds of terasync queries. So many in fact that we experienced insufficient memory errors and deadlocks. (That got the attention of the DBAs in a real hurry.)
After gaining the understandings mentioned above I ran some additional tests today but am confused again as the results are not what I anticipated. Even with requestedsessions=1 and sessionsperplayer=1 I am seeing lots (dozens) of terasync queries (updates and row locks). With requestedsessions=8 and sessionsperplayer=1 there were over 200 terasync queries. ( I even tried setting progressinterval to 0)
What am i missing? With a production system that has hundreds of AMPs and having to read some very large multi-million row tables how do I limit the number of terasync queries so I can be assured of not exhausting Teradatas resources........ again
Sounds about right to me. Might be a 8.x difficulty. Does setting any RequestedSessions and SessionsPerPlayer values have any effect? I'm wondering if the settings are being ignored completely? If so you might try matching the case of the two exactly as above.
It does appear that the settings affect the number of db queries that get submitted (the one that we wrote) but can not find a correlation to the number of Terasync and related queries, which is what is flooding the system.
I did check the job and I do have the case as you specify. I was just lazy in typing the post. Sorry.
Even using SessionsPerPlayer = 1 and RequestedSessions =1 we are still seeing 53 requests being sent to the Test Teradata system (which has 50 AMPs) so DBAs are still nervous about the use of DataStage. Until I can prove somehow that the job will scale up to the production system without flooding the TD system I am still not permitted to run any DS jobs that access TD tables.
I am arranging conversations with IBM DS engineers and our TD DBAs as well as a Peer call with another company that uses TD and DS which is being arranged by Teradata rep.
So I will post what I learn from those meetings when they are complete....unless someone here adds a brilliant post that solves this for me before then
