Archives of the TeradataForum

Message Posted: Tue, 02 Jul 2013 @ 15:31:25 GMT

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

Hi Dieter,

The tables are loaded using ODBC load. I looked into the DBQLOGTBL and found that for a single session there are thousands of plain simple insert statements like INSERT INTO DB_1.TABLE (A,B,C,D,E,F,G,H,I) VALUES ( ? , ? , ? , ? , ? , ? , ? , ? ) ; So this session is blocking the other session which again is inserting thousands of records to the same table. Below is the report

     DATE       TIME    DELAY          BLOCKER      BLKINGSESSNO
     BLOCKING LEVEL   BLOCKING MODE   BLOCKED USER      BLKDSESSNO
     BLOCKED LEVEL      BLOCKED MODE   STATEMENT TYPE    DB\TABLE
     6/22/2013   9:06:55    42.55    YYC302TDATETNL   2,197,692
     RowHash       Wr      YYC302TDATETNL   2,197,743
     RowHash    Wr         Insert         DB_1.TABLE1

The blocks are on the actual table being loaded

The DDL of one of the tables causing row hash blocks. I have changed the columns names and tables name ( No triggers / Foreign keys)

     CREATE SET TABLE DB_1.TABLE1 ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT,
          DEFAULT MERGEBLOCKRATIO
          (
           A  INTEGER TITLE 'A' NOT NULL,
           B  INTEGER TITLE 'B' NOT NULL,
           C INTEGER TITLE 'C' NOT NULL,
           D  VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'D' NOT NULL,
           E INTEGER TITLE 'E',
           F  DECIMAL(19,4) TITLE 'F',
           G  INTEGER TITLE 'G',
           H  INTEGER TITLE 'H',
           I  DECIMAL(19,4) TITLE 'I',
           J  INTEGER TITLE 'J',
           K  INTEGER TITLE 'K',
           L  DECIMAL(19,4) TITLE 'L',
           M  INTEGER TITLE 'M',
           N  INTEGER TITLE 'N',
           O  DECIMAL(19,4) TITLE 'O',
           P  INTEGER TITLE 'P',
           Q  INTEGER TITLE 'Q',
           R  DECIMAL(19,4)  'R',
           S FORECAST_ROOMS_AVAIL_1_NR INTEGER TITLE 'S',
           T  VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'T',
           U INTEGER TITLE 'U' NOT NULL,
           V  TIMESTAMP(6) TITLE 'V')
     UNIQUE PRIMARY INDEX XP_D_H_B_F ( A  ,B  ,C  ,D  );

Yes, DBQL is enabled for the user. Below is the query logging that is enabled for the user

     REPLACE QUERY LOGGING WITH SQL LIMIT SQLTEXT = 500 AND THRESHOLD = 30  ON ALL;

Query I ran to determine the hash synonyms

     SEL MAX(CNT) (TITLE'MAXIMUM//RPH')
     ,AVERAGE(CNT)(TITLE'AVERAGE//RPH',FORMAT'ZZZ,ZZ9.999')
     FROM
     (SEL HASHROW(A,B,C,D)
     ,COUNT(*)
     FROM DB.TABLE
     GROUP BY 1
     )DT1 (H,CNT);

     MAXIMUM RPH   AVERAGE RPH
     2                    1.00

Thanks

Roopalini

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