Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2014
    Posts
    1

    Question Unanswered: DB2 for Z/OS update table performance

    Hi,

    I have a SAS process which maintaines a DB2 table. The target table has 880 k records. The SAS table has 6 k new records for insert and 70 k records for update.

    I load the update into a temp DB2 table. In order to perform the update I use following code:


    UPDATE YZ0024.TPO_&pt.DEFAULT_STATUS d
    SET (&vars.)
    = (SELECT &vars.
    FROM yz0024.temp t
    WHERE d.contrnum = t.contrnum
    AND d.start_dt_BII_def = t.start_dt_BII_def
    )
    WHERE EXISTS
    (SELECT 1
    FROM yz0024.temp t
    WHERE d.contrnum = t.contrnum
    AND d.start_dt_BII_def = t.start_dt_BII_def
    )


    The macro variable vars contains all the columns in the table to be updated.
    Alas the process ends with an error:

    DSN00905E UNSUCCESSFUL EXECUTION DUE TO RESOURCE LIMIT BEING EXCEEDED


    I appreciate any comments that helps me in improving the code.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    you have a couple of items here that you could do any or all.

    Change the RLST definition for you to allow the required resources. Or work on tuning your update or breaking it into smaller pieces.
    Hard to say what to improve on here, not knowing your actual structure and what columns are being updated.
    A few items to think of: Are you updating indexed columns, primary key columns, partitioning columns? Those could slow you down a lot. What is your locksize? Are you updating columns that do not need to be updated. What about column placement(log from first changed byte to end of row, so put those updated columns at the end of row)? Are the columns in your WHERE clause indexed?

Tags for this Thread

Posting Permissions

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