Difference between revisions of "Reorg rebuild"

Latest revision as of 18:57, 17 February 2009

Reorg rebuild is a command for Sybase ASE that you can use to reorganize tables.

Short introduction

Before ASE version 15 you could only run this command on tables with a datapages or datarows locking scheme, but that restriction has been dropped. Reorg rebuild can be used to defragment tables. To run the command you need to turn on the database option "select into/bulkcopy/pllsort" as in this example:

sp_dboption myDatabase,"select into/bulkcopy/pllsort",true
go

Then run the command like this

use myDatabase
go
reorg rebuild CustomerTable
go

During a reorg rebuild, the table is locked to users should have been informed or logged of from the system.

Remember to make a full database dump after you have run the reorg command, since the command creates unlogged transactions.

Stored procedure to reorganize all tables in a database

Here you can find a stored procedure to reorganize all user tables in a database. Since reorg rebuild effectively copies the table you need enough free space in a database that the largest table uses multiplied by two. The stored procedure will check for that. Some other features:

• Processing starts with smaller tables
• Proxy tables are skipped
• An sp_recompile is run after each table, to tell the server to generate new query plans.

Sample execution:

myDatase..sp_dba_reorg_rebuild

Stored procedure:

use sybsystemprocs
go

if exists (select *
   from sysobjects where type = 'P' and name = 'sp_dba_reorg_rebuild')
begin
   drop procedure sp_dba_reorg_rebuild
end
go

create proc sp_dba_reorg_rebuild
as

declare @user_name      varchar(30),
        @table_name     varchar(255),
        @reserved_pages int,
        @free_space     int,
        @cmd            varchar(1024),
        @db_name        varchar(30)

declare c2 cursor for
        select user_name(uid),
               name,
               reserved_pages(db_id(@db_name),id)
               from    sysobjects
               where   type    = "U"   -- User tables
               and     not (sysstat2 & 1024 = 1024 or  -- Remote
                       sysstat2 & 2048 = 2048)         -- Proxy
               order   by 3

set flushmessage on

select @db_name = db_name()

print "reorg rebuild started for database %1!", @db_name

exec sp_flushstats

open c2

fetch c2 into @user_name, @table_name, @reserved_pages

while @@sqlstatus = 0
begin
   select @free_space = sum(curunreservedpgs(db_id(), lstart, unreservedpgs))
          from  master..sysusages
          where dbid    = db_id()
          and   segmap != 4 -- logsegment

   if @free_space < round(@reserved_pages * 2.0,0)
      print   "Skipping table %1!.%2! not enough space in database", @user_name, @table_name
   else
   begin
      print "Table %1!.%2!", @user_name, @table_name

      select @cmd = "reorg rebuild " + @user_name + "." + @table_name

      exec (@cmd)

      if @@error != 0
         break

      select @cmd = rtrim(@user_name) + "." + @table_name

      exec sp_recompile @cmd

      if @@error != 0
         break
   end

   fetch c2 into @user_name, @table_name, @reserved_pages
end

close c2

deallocate cursor c2

return 0
go