Tags: create, database, db2, delete, discard, files, getdb2, log, million, mysql, oracle, records, reorg, single, sql, table

REORG & DISCARD

On Database » DB2

5,128 words with 3 Comments; publish: Sat, 24 May 2008 13:03:00 GMT; (250140.63, « »)

I need to delete several million records from a single table but cannot get

DB2 8.1 to not create log files (I need to do this as fast as possible to

minimize downtime). I figure that creating the log files consumes a lot of

resources so I turned LOGRETAIN OFF & USEREXIT OFF, but the log files still

get generated. Is there a better way to run a large delete like this?

I see that you are able to run a delete with a where clause when Reorging a

Tablespace using the DISCARD (WHEN) option, but when I right click on the

Tablespace, REORG is not an option. Does anyone know why this is?

Can I use that same DISCARD (WHEN) option on a table REORG? I ask because I

it looks like you can specify NO LOG during these procedures and am hoping

that my delete will run faster this way. Any help or suggestions would be

greatly appreciated. Thanks.

All Comments

Leave a comment...

  • 3 Comments
    • Jason Gyetko wrote:

      > I need to delete several million records from a single table but cannot ge

      t

      > DB2 8.1 to not create log files (I need to do this as fast as possible to

      > minimize downtime). I figure that creating the log files consumes a lot o

      f

      > resources so I turned LOGRETAIN OFF & USEREXIT OFF, but the log files stil

      l

      > get generated. Is there a better way to run a large delete like this?

      LOGRETAIN controls archive logging vs. circular logging -- not whether

      transactions are logged or not.

      Here are a couple of options:

      1) Alter table activate not logged initially:

      db2 +c "alter table x activate not logged initially"

      db2 +c "delete from x where ..."

      db2 commit

      This will avoid logging, but make sure you back up your database

      after completing this.

      2) Export the table, excluding the data you want to delete, and then

      use LOAD to replace the contents of the table with the data you

      exported.

      3) Insert good records into a second table (either by insert into ...

      select ...) or by load from cursor, and then rename tables.

      create table sch.x_copy (...) not logged initially

      declare c1 cursor for select * from x where ...

      load from c1 of cursor replace into x_copy

      rename table sch.x to x_orig

      rename table sch.x_copy to x

      The advantage of (2) or (3) is that these eliminate the REORG problem.

      They are most effective if your delete eliminates a majority of the rows

      in the table (say, deleting 4M out of 5M rows). If you are deleting a

      small portion (say 4M out of 100M rows), these two options require a lot

      more work.

      #1; Sat, 24 May 2008 13:04:00 GMT
    • Thanks for the response. I've already tried option #2 in our test

      environment and it is much faster that actually issuing the SQL Delete. I

      just wasn't sure if that was the best way to go about it or not. Thanks.

      "Ian" <ianbjor.db2.todaysummary.com.mobileaudio.com> wrote in message

      news:423b0ff5$1_5.db2.todaysummary.com.newsfeed.slurp.net...

      > Jason Gyetko wrote:

      get[vbcol=seagreen]

      to[vbcol=seagreen]

      of[vbcol=seagreen]

      still[vbcol=seagreen]

      > LOGRETAIN controls archive logging vs. circular logging -- not whether

      > transactions are logged or not.

      > Here are a couple of options:

      > 1) Alter table activate not logged initially:

      > DB2 +c "alter table x activate not logged initially"

      > DB2 +c "delete from x where ..."

      > DB2 commit

      > This will avoid logging, but make sure you back up your database

      > after completing this.

      > 2) Export the table, excluding the data you want to delete, and then

      > use LOAD to replace the contents of the table with the data you

      > exported.

      > 3) Insert good records into a second table (either by insert into ...

      > select ...) or by load from cursor, and then rename tables.

      > create table sch.x_copy (...) not logged initially

      > declare c1 cursor for select * from x where ...

      > load from c1 of cursor replace into x_copy

      > rename table sch.x to x_orig

      > rename table sch.x_copy to x

      >

      > The advantage of (2) or (3) is that these eliminate the REORG problem.

      > They are most effective if your delete eliminates a majority of the rows

      > in the table (say, deleting 4M out of 5M rows). If you are deleting a

      > small portion (say 4M out of 100M rows), these two options require a lot

      > more work.

      >

      #2; Sat, 24 May 2008 13:05:00 GMT
    • Another option is to break up your large delete into multiple smaller

      deletes: it's usually not too difficult to write this smaller delete so

      that it can be run it every minute or so, each time deleting exactly

      20,000 (or whatever) rows that meet your criteria.

      Using identities, sequences, and olap row_number() are the key.

      ken

      #3; Sat, 24 May 2008 13:06:00 GMT