Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2015
    Posts
    2

    Unanswered: Updating 1 million records

    HI ,

    I have 1 million of records(distinct accounts) in the TABLE1 , I want to update the SALE_LOC_ID ( location where the Account has been initiated) in the TABLE1.

    TABLE2 -- consists of the Tranaction details , so I need to take the SALE_LOC_ID from TABLE2 and update it in TABLE1 for each account.

    So I am making use of the procedure and using the cursor to update the records , its taking much time for updating.

    Is there any better solution to update the records other than the cursor option .

    Below is the procedure
    ----------------------------



    -----------Keeping all the statements in the procedure -------------------------------------

    DECLARE v_stmt VARCHAR (3000);
    DECLARE VARIABLE_ACCT_NO CHARACTER (19);
    DECLARE VARIABLE_SALE_LOC_ID INTEGER;

    DECLARE C1 CURSOR WITH HOLD FOR statement1;


    ------- Delcaring one temporary GTT----------------------
    DECLARE GLOBAL TEMPORARY TABLE SESSION.TMP1
    (
    "ACCT_NO" CHARACTER(21) ,
    "SALE_LOC_ID" INTEGER
    )
    NOT LOGGED
    WITH REPLACE
    ON COMMIT PRESERVE ROWS;

    ----------------Inserting the data into the Temporary GTT----------------------

    INSERT INTO SESSION.TMP1
    (select A.ACCT_NO, B.SALE_LOC_ID from TABLE1 A join TABLE2 B
    on (A.ACCT_NO = B.ACCT_NO)
    )


    SET v_stmt = 'SELECT A.ACCT_NO, A.SALE_LOC_ID FROM SESSION.TMP1 A FOR FETCH ONLY';

    PREPARE statement1 FROM v_stmt;

    OPEN C1;

    FETCH C1 INTO VARIABLE_ACCT_NO, VARIABLE_SALE_LOC_ID;

    UPDATE TABLE1
    SET SALE_LOC_ID = VARIABLE_SALE_LOC_ID WHERE ACCT_NO = VARIABLE_ACCT_NO WITH UR;
    CLOSE C1;


    Thanks
    Ganga reddy
    Last edited by Ganga Reddy; 05-11-15 at 15:40.

  2. #2
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    Check the explain plan. Are the tables indexed appropriately?
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  3. #3
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    You should commit after let's say every 1000 rows updated.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    have you tried just a single MERGE statement for that?
    Regards,
    Mark.

  5. #5
    Join Date
    Feb 2015
    Posts
    2
    Quote Originally Posted by tafster View Post
    Check the explain plan. Are the tables indexed appropriately?
    Hi All,

    Thanks for the response.Tables are indexed properly

    1)What is the Query to see the explain plan
    2) I am committing the records after every 10000 rows
    3) will merge option work better than the cursor option.Or is there any other option which is used update bulk records

    MERGE INTO TABLE1 A
    USING SESSION.SESSION.TMP1 B
    ON (A.ACCT_NO = B.ACCT_NO)
    WHEN MATCHED THEN
    UPDATE
    SET SALE_LOC_ID = VARIABLE_SALE_LOC_ID WHERE ACCT_NO = VARIABLE_ACCT_NO

  6. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    why don't you use a single statement like this instead of your procedure logic with cursor and temporary table?
    Code:
    merge into table1 a
    using 
    --(select acct_no, max(sale_loc_id) sale_loc_id from table2 group by acct_no)
    table2
    b
    on b.acct_no=a.acct_no
    when matched then 
    update set sale_loc_id=b.sale_loc_id
    You should use the commented out sub-select instead of table2 reference if you don't have a unique index on table2.acct_no.

    Does it work slower?
    Regards,
    Mark.

Posting Permissions

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