Reorg rebuild is a command for Sybase ASE that you can use to reorganize tables.
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.
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