http://petersap.nl/SybaseWiki/index.php?title=Reorg_rebuild&feed=atom&action=history
Reorg rebuild - Revision history
2024-03-28T22:24:24Z
Revision history for this page on the wiki
MediaWiki 1.24.2
http://petersap.nl/SybaseWiki/index.php?title=Reorg_rebuild&diff=1838&oldid=prev
Psap at 17:57, 17 February 2009
2009-02-17T17:57:01Z
<p></p>
<table class='diff diff-contentalign-left'>
<col class='diff-marker' />
<col class='diff-content' />
<col class='diff-marker' />
<col class='diff-content' />
<tr style='vertical-align: top;'>
<td colspan='2' style="background-color: white; color:black; text-align: center;">← Older revision</td>
<td colspan='2' style="background-color: white; color:black; text-align: center;">Revision as of 17:57, 17 February 2009</td>
</tr><tr><td colspan="2" class="diff-lineno">Line 1:</td>
<td colspan="2" class="diff-lineno">Line 1:</td></tr>
<tr><td class='diff-marker'>−</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>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:</div></td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>Reorg rebuild is a command for Sybase ASE that you can use to reorganize tables.</div></td></tr>
<tr><td colspan="2"> </td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div> </div></td></tr>
<tr><td colspan="2"> </td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins class="diffchange diffchange-inline">==Short introduction==</ins></div></td></tr>
<tr><td colspan="2"> </td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>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:</div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>  sp_dboption myDatabase,"select into/bulkcopy/pllsort",true</div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>  sp_dboption myDatabase,"select into/bulkcopy/pllsort",true</div></td></tr>
<tr><td colspan="2" class="diff-lineno">Line 10:</td>
<td colspan="2" class="diff-lineno">Line 13:</td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>  reorg rebuild CustomerTable</div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>  reorg rebuild CustomerTable</div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>  go</div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>  go</div></td></tr>
<tr><td colspan="2"> </td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></ins></div></td></tr>
<tr><td colspan="2"> </td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">During a reorg rebuild, the table is locked to users should have been informed or logged of from the system.</ins></div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>Remember to make a full database dump after you have run the reorg command, since the command creates unlogged transactions.</div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>Remember to make a full database dump after you have run the reorg command, since the command creates unlogged transactions.</div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"></td></tr>
<tr><td colspan="2"> </td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">==Stored procedure to reorganize all tables in a database==</ins></div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>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:</div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>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:</div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>* Processing starts with smaller tables</div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>* Processing starts with smaller tables</div></td></tr>
<tr><td colspan="2" class="diff-lineno">Line 47:</td>
<td colspan="2" class="diff-lineno">Line 53:</td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>         select user_name(uid),</div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>         select user_name(uid),</div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>                 name,</div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>                 name,</div></td></tr>
<tr><td class='diff-marker'>−</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>                 reserved_pages(db_id(@db_name),id<del class="diffchange diffchange-inline">,0</del>)</div></td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>                 reserved_pages(db_id(@db_name),id)</div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>                 from    sysobjects</div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>                 from    sysobjects</div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>                 where  type    = "U"  -- User tables</div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>                 where  type    = "U"  -- User tables</div></td></tr>
</table>
Psap
http://petersap.nl/SybaseWiki/index.php?title=Reorg_rebuild&diff=1837&oldid=prev
Psap at 14:51, 13 February 2009
2009-02-13T14:51:13Z
<p></p>
<p><b>New page</b></p><div>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:<br />
<br />
sp_dboption myDatabase,"select into/bulkcopy/pllsort",true<br />
go<br />
<br />
Then run the command like this<br />
<br />
use myDatabase<br />
go<br />
reorg rebuild CustomerTable<br />
go<br />
<br />
Remember to make a full database dump after you have run the reorg command, since the command creates unlogged transactions.<br />
<br />
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:<br />
* Processing starts with smaller tables<br />
* Proxy tables are skipped<br />
* An sp_recompile is run after each table, to tell the server to generate new query plans.<br />
<br />
Sample execution:<br />
<br />
myDatase..sp_dba_reorg_rebuild<br />
<br />
Stored procedure:<br />
<br />
use sybsystemprocs<br />
go<br />
<br />
if exists (select *<br />
from sysobjects where type = 'P' and name = 'sp_dba_reorg_rebuild')<br />
begin<br />
drop procedure sp_dba_reorg_rebuild<br />
end<br />
go<br />
<br />
create proc sp_dba_reorg_rebuild<br />
as<br />
<br />
declare @user_name varchar(30),<br />
@table_name varchar(255),<br />
@reserved_pages int,<br />
@free_space int,<br />
@cmd varchar(1024),<br />
@db_name varchar(30)<br />
<br />
declare c2 cursor for<br />
select user_name(uid),<br />
name,<br />
reserved_pages(db_id(@db_name),id,0)<br />
from sysobjects<br />
where type = "U" -- User tables<br />
and not (sysstat2 & 1024 = 1024 or -- Remote<br />
sysstat2 & 2048 = 2048) -- Proxy<br />
order by 3<br />
<br />
set flushmessage on<br />
<br />
select @db_name = db_name()<br />
<br />
print "reorg rebuild started for database %1!", @db_name<br />
<br />
exec sp_flushstats<br />
<br />
open c2<br />
<br />
fetch c2 into @user_name, @table_name, @reserved_pages<br />
<br />
while @@sqlstatus = 0<br />
begin<br />
select @free_space = sum(curunreservedpgs(db_id(), lstart, unreservedpgs))<br />
from master..sysusages<br />
where dbid = db_id()<br />
and segmap != 4 -- logsegment<br />
<br />
if @free_space < round(@reserved_pages * 2.0,0)<br />
print "Skipping table %1!.%2! not enough space in database", @user_name, @table_name<br />
else<br />
begin<br />
print "Table %1!.%2!", @user_name, @table_name<br />
<br />
select @cmd = "reorg rebuild " + @user_name + "." + @table_name<br />
<br />
exec (@cmd)<br />
<br />
if @@error != 0<br />
break<br />
<br />
select @cmd = rtrim(@user_name) + "." + @table_name<br />
<br />
exec sp_recompile @cmd<br />
<br />
if @@error != 0<br />
break<br />
end<br />
<br />
fetch c2 into @user_name, @table_name, @reserved_pages<br />
end<br />
<br />
close c2<br />
<br />
deallocate cursor c2<br />
<br />
return 0<br />
go<br />
<br />
[[Category:ASE]]</div>
Psap