Teradata terasync table causing deadlocks

[bcarlson]

Has anyone else been getting deadlocks with the Terasync table on Teradata?

Also, is there a way to control the layout of the table? Or at least to make it multiset vs. set? What happens if I capture the DDL for the table and recreate it manually as MULTISET? Will DataStage still acknowledge it?

Here is the DDL that gets generated:

Code: Select all

CREATE SET TABLE acapsd_daily.terasync ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      cookie INTEGER NOT NULL,
      optype BYTEINT NOT NULL,
      ctlstate BYTEINT NOT NULL,
      eventcount INTEGER NOT NULL,
      lsn INTEGER,
      blockcount INTEGER,
      query VARCHAR(1024) CHARACTER SET LATIN NOT CASESPECIFIC,
      username VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      dbname VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      recordcount FLOAT,
      bytecount FLOAT,
      rejectcount FLOAT,
      starttime INTEGER,
      endtime INTEGER)
UNIQUE PRIMARY INDEX ( cookie );

So where does the DDL come from? Can it be modified in DataStage so that is created as multiset?

Brad.

[keshav0307]

terasync table is created dynamically whenever you use Teradat Enterprize stage.

yes. if you have specified the $APT_TERASYNC_DATABASE, pointing to the database where you are creating it manually

[bcarlson]

So altering the table from SET to MULTISET manually will not affect DataStage's ability to use it?

brad.

[keshav0307]

try it and let us know.

[throbinson]

I can't answer your most recent question but we had deadlocks for two reasons;
1. RequestedSession, SeesionsPerplayer was not set therefore they defaulted to RequestedSessions=the number of AMPS, SessionsPerPlayer = 2. This was way, way too high. With 68 sessions simultaneously trying to update the terasync table with runtime info, some were bound to deadlock and did.
2. The terasynctimeout parameter defaults to 30 seconds, I think. We bumped it to 5 minutes (teraysnctimeout=300) and the deadlock went away.
Terasync is NOT necessarily created dynamically. We created it once in a common database and all our Teradata jobs that contain the Enterprise Stage use it. We've not had a problem in a while. I'll bet you could manually run the DDL and create the table as a multi-set but I would wonder why you would want to do that.

[bcarlson]

2. The terasynctimeout parameter defaults to 30 seconds, I think. We bumped it to 5 minutes (teraysnctimeout=300) and the deadlock went away.

Where is this parameter set? In the DataStage project or within the job?

As for making Terasync multiset, it was just something that had been proposed. I don't remember the reasoning.

Brad

[throbinson]

This "parameter" is an Additional Connection Option comma delimited string within the Property Tree of the Teradata Enterprise Stage.
synctimeout=300.
Ex:
RequestedSessions=8,SessionsPerPlayer=2,synctimeout=300, WORKDB=TEMP,TENACITY=1,SLEEP=5

Using TENACITY and SLEEP require a patch from IBM.

[keshav0307]

RequestedSession=SessionsPerPlayer*number of compute nodes

[bcarlson]

Using TENACITY and SLEEP require a patch from IBM.

Do you by any chance know the 'name' of the patch? I am checking with our Infrastructure group to see if they can help me track this down, but a name would be helpful.

Thanks!

Brad

[throbinson]

E-case e94953