Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2006
    Posts
    1

    Post Unanswered: Update performance on index columns.

    i like know what method offer best performance for update values on index columns, update versus delete/insert, over DB2 v720 on z/os. Regards. John

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    i think for all types of db2 update is a better method.
    make sure to run runstats after doing update, insert,delete on tables having large data so that optimizer is updated with new values.

    Runstats for Both tables and indexes:
    RUNSTATS ON TABLE tablename WITH DISTRIBUTION AND DETAILED INDEXES ALL

    Rahul Singh

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by jrbusta
    i like know what method offer best performance for update values on index columns, update versus delete/insert, over DB2 v720 on z/os. Regards. John
    If you are updating the clustering index and you want DB2 to recluster the data row (according to the clustering index), then you should do a delete and insert.

    If you are not changing the clustering index or you would rather defer the re-clustering task to when you do a reorg, then do an update.

    For best performance in a system with very high transaction rates, any index that gets changed often (insert, update, or delete) should have a reasonably high fullkeycard value (even if you have to add additonal columns to the index). Indexes with very low cardinality are slow to update.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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