Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jul 2002
    Posts
    86

    Unanswered: Question about Delete some rows from large table

    Hi
    I work with db2 7.2 AIX, I need to delete some rows from a large table with 20 mil records I use
    delete from TAB-A where co11= 'A';
    I have index on col1 but it takes long about 1/2 hours that is not acceptable, Some body suggested me I deaclare cursor and use "where current cursor cursor-name" in my delete sql statement. any idea and help will be appreciated.
    Thanks

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Question about Delete some rows from large table

    Too elementary ... but,
    Have you done a runstats on the table including the indexes ?

    Cheers

    Sathyaram

    Originally posted by David2002
    Hi
    I work with db2 7.2 AIX, I need to delete some rows from a large table with 20 mil records I use
    delete from TAB-A where co11= 'A';
    I have index on col1 but it takes long about 1/2 hours that is not acceptable, Some body suggested me I deaclare cursor and use "where current cursor cursor-name" in my delete sql statement. any idea and help will be appreciated.
    Thanks

  3. #3
    Join Date
    Jul 2002
    Posts
    86

    Re: Question about Delete some rows from large table

    Originally posted by sathyaram_s
    Too elementary ... but,
    Have you done a runstats on the table including the indexes ?

    Cheers

    Sathyaram
    Thank you for your answer , Yes I did runstats and bound the packages.
    I imagin if we have a table with 200 mil records (that I will have in future in our system) what is the best solution for deletition a part of table.
    Thank you in advanace for your co-operation

  4. #4
    Join Date
    Aug 2002
    Location
    Bonn/Germany
    Posts
    152
    200 million rows is a lot. You need to check the
    limit for a single table in DB2 which depends on
    the configured pagesize and ranges from 64 GB
    to 512 GB for single-partition-tables (no EEE).

    You might consider partitioning your database across
    several partitions using DB2 EEE or in DB2 ESE (Version 8.1),
    this might improve performance, because the delete will
    run in parallel mode.

    From my point of view you need some kind of fragmentation
    technology like the one Informix offers.
    With Informix you can fragment your table using for example
    range fragmentation into several dbspaces. This allows you
    to detach a single fragment without any resource consuming
    delete process, it takes only a second and your data is
    "deleted".

    But this is not possible with DB2 (at least not yet, but the
    IFMX guys will work on a future DB2 version, hopefully).
    You might consider using several stand-alone-tables and
    combining them thru a union-view.
    I read something that in Version 8.1 also inserts/updates/deletes
    against union views are possible. So that might help.


    Best regards

    Eric
    --
    IT-Consulting Herber
    WWW: http://www.herber-consulting.de
    Email: eric@herber-consulting.de

    ***********************************************
    Download the IFMX Database-Monitor for free at:
    http://www.herber-consulting.de/BusyBee
    ***********************************************

  5. #5
    Join Date
    Sep 2002
    Posts
    456

    Re: Question about Delete some rows from large table

    Here is one solution:

    >> export the table without the data you want to delete using where clause

    >>import an empty file in the table; this is the fastest way to delete data from table without using much of log activity

    >>load the file exported in first step which will contain only the data you wanted to keep

    You can automate this whole process to better use it.

    Hope this helps

    dollar

    Originally posted by David2002
    Thank you for your answer , Yes I did runstats and bound the packages.
    I imagin if we have a table with 200 mil records (that I will have in future in our system) what is the best solution for deletition a part of table.
    Thank you in advanace for your co-operation

  6. #6
    Join Date
    Jul 2002
    Posts
    86

    Re: Question about Delete some rows from large table

    Originally posted by dollar489
    Here is one solution:

    >> export the table without the data you want to delete using where clause

    >>import an empty file in the table; this is the fastest way to delete data from table without using much of log activity

    >>load the file exported in first step which will contain only the data you wanted to keep

    You can automate this whole process to better use it.

    Hope this helps

    dollar
    Thank you for your suggestion but if I use LOAD command it will lock the tablespace by the end of load process . am I right?

  7. #7
    Join Date
    Aug 2002
    Location
    Bonn/Germany
    Posts
    152
    In V7.x the tablespace is locked during the LOAD, in V8.1 only the table itself is locked.

    But this might also not be desirable in a 24x7 OLTP system.


    Best regards

    Eric
    --
    IT-Consulting Herber
    WWW: http://www.herber-consulting.de
    Email: eric@herber-consulting.de

    ***********************************************
    Download the IFMX Database-Monitor for free at:
    http://www.herber-consulting.de/BusyBee
    ***********************************************

  8. #8
    Join Date
    Jul 2002
    Posts
    86
    Originally posted by eherber
    In V7.x the tablespace is locked during the LOAD, in V8.1 only the table itself is locked.

    But this might also not be desirable in a 24x7 OLTP system.


    Best regards

    Eric
    --
    IT-Consulting Herber
    WWW: http://www.herber-consulting.de
    Email: eric@herber-consulting.de

    ***********************************************
    Download the IFMX Database-Monitor for free at:
    http://www.herber-consulting.de/BusyBee
    ***********************************************
    Thank you eric,
    so I can not use Load because delete command is used in application and we can not lock the tablespace (in that time we have read write in so many tables in tablespace. How about if I use dectare cursor and use delete command with "with current cursor <corsur-name>?

  9. #9
    Join Date
    Jan 2003
    Posts
    1,605

    Re: Question about Delete some rows from large table

    Hi,

    In DB2 vesion 8.1 exist multi-dimensional clustering (index type)

    Multi-Dimensional Clustering - Benefits:
    - Faster Query speed
    - Access only the data pages necessary - no searching
    - Get all the data in a few accesses versus 100s of page reads
    - Reduces CPU & disk I/O use for other users to leverage
    - Simple & Intuitive --Multidimensional keys and Star Schema friendly
    - Perfect for OLAP style hierarchical analysis
    - Executive reports, Summaries, etc.
    - Reduces index size --Saves disk, faster queries
    - One index entry covers entire data page, not one per row
    - Reduces DBA reorg's --row clustering managed by DB2
    - FASTER DELETES --JUST DROP A FEW DATA PAGES
    - Faster Inserts --store the record, but rarely insert an index entry

    Grofaty

    Originally posted by sathyaram_s
    Too elementary ... but,
    Have you done a runstats on the table including the indexes ?

    Cheers

    Sathyaram

  10. #10
    Join Date
    Aug 2002
    Location
    Bonn/Germany
    Posts
    152

    Re: Question about Delete some rows from large table

    Beside repeating the IBM marketing messages,
    did you ever verify this ?

    I doubt that MDC indexing is that useful in OLTP
    environments. Also I doubt that a EEE or in
    V8.1 a ESE with multiple partitions is really
    useful in HighEnd OLTP environments.
    Informix might be (at least today) the better choice.


    Best regards

    Eric
    --
    IT-Consulting Herber
    WWW: http://www.herber-consulting.de
    Email: eric@herber-consulting.de

    ***********************************************
    Download the IFMX Database-Monitor for free at:
    http://www.herber-consulting.de/BusyBee
    ***********************************************





    Originally posted by grofaty
    Hi,

    In DB2 vesion 8.1 exist multi-dimensional clustering (index type)

    Multi-Dimensional Clustering - Benefits:
    - Faster Query speed
    - Access only the data pages necessary - no searching
    - Get all the data in a few accesses versus 100s of page reads
    - Reduces CPU & disk I/O use for other users to leverage
    - Simple & Intuitive --Multidimensional keys and Star Schema friendly
    - Perfect for OLAP style hierarchical analysis
    - Executive reports, Summaries, etc.
    - Reduces index size --Saves disk, faster queries
    - One index entry covers entire data page, not one per row
    - Reduces DBA reorg's --row clustering managed by DB2
    - FASTER DELETES --JUST DROP A FEW DATA PAGES
    - Faster Inserts --store the record, but rarely insert an index entry

    Grofaty

  11. #11
    Join Date
    Feb 2003
    Posts
    22
    You probably have just verified this...however....
    If your table is partitioned deletind so many rows....are you sure that it doesn't make a lock escalation?
    I think so...so....using a load replace in this case is faster!

  12. #12
    Join Date
    Jan 2003
    Posts
    1,605

    Re: Question about Delete some rows from large table

    Hi,

    Yes you are probably right. MDC indexing is not sutible for OLTP systems. But it can be useful in warehouse environments.

    I haven't tested it yet, but it seem to be very promising...

    Grofaty

    Originally posted by eherber
    Beside repeating the IBM marketing messages,
    did you ever verify this ?

    I doubt that MDC indexing is that useful in OLTP
    environments. Also I doubt that a EEE or in
    V8.1 a ESE with multiple partitions is really
    useful in HighEnd OLTP environments.
    Informix might be (at least today) the better choice.


    Best regards

    Eric
    --
    IT-Consulting Herber
    WWW: http://www.herber-consulting.de
    Email: eric@herber-consulting.de

    ***********************************************
    Download the IFMX Database-Monitor for free at:
    http://www.herber-consulting.de/BusyBee
    ***********************************************

  13. #13
    Join Date
    Aug 2002
    Location
    Bonn/Germany
    Posts
    152
    Originally posted by David2002
    Thank you eric,
    so I can not use Load because delete command is used in application and we can not lock the tablespace (in that time we have read write in so many tables in tablespace. How about if I use dectare cursor and use delete command with "with current cursor <corsur-name>?
    Yes David, this might be the way to go if you need 24x7 availability
    of the table.

    However you should be careful that the transaction is not too
    big, because otherwise your log files will become full.
    So, you might split this transaction into smaller transaction
    groups (i.e. committing after 10.000 rows for example).
    If you are interested, you can download the 'tx_split' utility
    from my website:

    http://www.herber-consulting.de/cgi-...ction=IfmxUtil

    'tx_split' is an Esql/C program that is able to split big transactions
    (delete/update statements that hit a lot of rows) into smaller
    transaction groups.
    However it is written for the Informix databaseserver, but it should
    not be too hard to port it to DB2 UDB Esql/C.
    The advantage of the program is that you could use it for abitrary
    update- and delete-statements, without writing a new program
    every time.

    Two tips for your initial question about possible performance
    improvements:

    1) You might consider using raw devices (DMS tablespaces) if
    you do not already have.
    This allows you to detach the index from the table in
    a separate tablespace.

    2) Create these raw devices (logical volumes) as Raid-10
    (mirrored/striped).
    I know that DB2 does also a kind of striping, but if you align
    the stripesizes (DB2_STRIPED_CONTAINERS registry
    variable) this might improve the throughput further.

    3) Run you delete batch-job locally on the databaseserver.
    Otherwise the network might be an additional bottleneck
    because with the delete-where-current-of approach a
    lot of messages pass from client to server and the other
    way round.

    4) Test the same job defined as a sql stored procedure.
    This eliminates the client-/server traffic and everything
    is done in the databaseserver.

    5) Thiink about executing the delete in parallel where each
    parallel taks processes only a subset of the rows


    Best regards

    Eric
    --
    IT-Consulting Herber
    WWW: http://www.herber-consulting.de
    Email: eric@herber-consulting.de

    ***********************************************
    Download the IFMX Database-Monitor for free at:
    http://www.herber-consulting.de/BusyBee
    ***********************************************

    Best regards

    Eric
    --
    IT-Consulting Herber
    WWW: http://www.herber-consulting.de
    Email: eric@herber-consulting.de

    ***********************************************
    Download the IFMX Database-Monitor for free at:
    http://www.herber-consulting.de/BusyBee
    ***********************************************

  14. #14
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi,

    If you just have to avoid locking of tablespace and continue deleteing records then better do this using a procedure where you can select records in a cursor with one record (primary key) at a time and delete that record.
    This way there won't be any deadlock and locking also.

    Cheers,
    Prashant

  15. #15
    Join Date
    Feb 2003
    Posts
    22
    If you want, you can use QMF for generating the needed delete statement in the way decripted in the last post.
    You select the keys and make punctual delete statements. It avoids lock.
    Remember to use commit after every delete statement!!!!!!!!!
    In this way if something goes wrong rollback takes just few second (to rollback the last update!).
    Is it enough clear?
    Cristiana
    Data Base Administrator
    Bologna
    Italy

Posting Permissions

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