Archives of the TeradataForum

Message Posted: Tue, 31 Aug 2010 @ 14:43:39 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

This is wrt to DataStage 8.1 with Teradata 13.

My table is given below.

     CREATE MULTISET TABLE change_id_lookup ,FALLBACK ,
           program VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           instance VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           identifier INTEGER NOT NULL,
           change_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
                (START WITH 1
                 INCREMENT BY 1
                 MINVALUE -2147483647
                 MAXVALUE 2147483647
                 NO CYCLE),
           INSERT_PROCESS_ID INTEGER,
           UPDATE_PROCESS_ID INTEGER)
     UNIQUE PRIMARY INDEX ( change_id );

Below is the UPDATE query which is used in the ETL job.

     Update change_id_lookup Set update_process_id = ORCHESTRATE.update_process_id

Where

ORCHESTRATE.identifier = sk_event_id_lookup.identifier AND ORCHESTRATE.program=sk_event_id_lookup.program AND ORCHESTRATE.instance = sk_event_id_lookup.instance

We do have 28 ETL jobs which run in every 1 minute which uses above UPDATE statement, if system is slow then above UPDATE statement is used by 10 ETL jobs at the same time which causes hanging issue or the ETL jobs gets delayed to complete. All jobs run under DS_PROD id, our DBA says

"DS_PROD user is blocked by different session from DS_PROD on change_id_lookup table. The jobs starting every minute trying to apply Write lock on lookup table. When the first job take additional time to complete the next job end up in queue to get the lock"

My Question. Please let me know if I change the columns i.,e identifier, program, instance to NUPI will the hanging issue resolve.

Or any other suggestions will be appreciated to resolve the hanging issue.

Thank you,

Naveen

	<Prev	Next>		<<First	<Prev	Next>	Last>>