Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002
    Posts
    8

    Unanswered: Slow Running Update on V7.2

    HBK1 with 0.5 million records, HBK2 with 1.2 million records and realdate with 62000 records completed in about 5 mins.

    If HBK1 1.2 million records, HBK2 2.5 million records and realdate with 62000 records did not finish even after 16 hours.

    Any ideas ? Thanks in Advance ...

    UPDATE HBK2
    SET LOAD_RETURN_CODE = 16
    WHERE BKG_DATE ||
    CHAR(BKG_REF) ||
    OPER_DATE IN
    (
    SELECT BKG_DATE ||
    CHAR(BKG_REF) ||
    OPER_DATE
    FROM HBK2
    WHERE BKG_DATE NOT IN ( select * from realdate) OR
    BKG_REF = 0 OR
    PRODUCT = '' OR
    PRODUCT IS NULL OR
    PACKAGE = '' OR
    PACKAGE IS NULL OR
    REVENUE_TYPE IS NULL OR
    OPER_DATE NOT IN realdate) OR
    REVENUE_AMT IS NULL
    ) OR
    BKG_DATE ||
    CHAR(BKG_REF) ||
    OPER_DATE NOT IN
    (
    SELECT
    BKG_DATE ||
    CHAR(BKG_REF) ||
    OPER_DATE
    FROM HBK1
    WHERE LOAD_RETURN_CODE = 1024
    );

  2. #2
    Join Date
    Jul 2002
    Posts
    19
    Hi
    In the query you have given, Try avoiding the use of Select * (select * is not advisable under any circumstances). If you are checking against a date field BKG_DATE NOT IN (SELECT <give only that col name> FROM REALDATE) . If possible create an index on the selected field to speed up the retrieval.
    Hope this helps to some extent

  3. #3
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141

    Exclamation

    You're running V7.2, so you can code:

    UPDATE HBK2
    SET LOAD_RETURN_CODE = 16
    WHERE BKG_DATE, BKG_REF, OPER_DATE IN
    (SELECT BKG_DATE,
    BKG_REF,
    OPER_DATE
    FROM HBK2
    (...)

    If you use CONCAT (||), DB2 can't use a index. If you code this way, DB2 will check the possibitily for using a index (I'm not saying it will).

    Good luck.

  4. #4
    Join Date
    Mar 2002
    Posts
    34
    I would try several different ways of accomplishing the update and test them first with the explain plan.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •