Archives of the TeradataForum

Message Posted: Mon, 08 Nov 2010 @ 15:03:30 GMT

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

Hi,

We do run below script via ETL DataStage, we do have around 30 countries.

We do have the below delete query which is running in the interval of every 1 minute for 30 countries. When the system utilisation is less, there is no issue.

When the CPU is high for system, the delete statement goes for BLOCKING in TD Pmon.

Instead of completing in few minutes, it hangs for a long time. Any suggestion or help will be appreciated

     bt; delete TARGET_TABLE where location_id not in (select location_id from
     SOURCE_TABLE group by location_id) and instance_name='smith'; et;
     CREATE SET TABLE TARGET_TABLE ,FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           application VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

           instance_name VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           identifier INTEGER NOT NULL,
           location_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
                (START WITH 1
                 INCREMENT BY 1
                 MINVALUE -2147483647
                 MAXVALUE 2147483647
                 NO CYCLE),
           LOCATION_TYPE_CD CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC)
     UNIQUE PRIMARY INDEX ( location_id );

     CREATE SET TABLE SOURCE_TABLE ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           LOCATION_ID INTEGER NOT NULL,
           LOCATION_CD CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           LOCATION_NAME VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
           PARENT_LOCATION_ID INTEGER,
           LOCATION_TYPE_CD CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           INSERT_ID INTEGER,
           UPDATE_ID INTEGER)
     UNIQUE PRIMARY INDEX ( LOCATION_ID )
     INDEX TypeCd ( LOCATION_TYPE_CD )
     INDEX yp1 ( LOCATION_CD );

Thank You

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