Solved

How do I Reorg/compact a Sybase DB

Posted on 1999-01-20
5
2,249 Views
Last Modified: 2012-05-04
Stats:
Using AIX, Sybase SQL server and Open Client/Server 10.0.2
Lots of Memory/HD space

In a sybase DB, when items are deleted, the data is simply flagged as not used.  In MSAccess, you can get rid of this excess data by "compacting" the DB.  This removes all the flagged data and leaves only the active items in the DB.  My question is: How do I do this for a Sybase DB.  I have heard about using a function called reorg, but I can't find anything like that anywhere, not even in the manuals, online documentation, or SyBooks.

Please give the specific commands that must be run in order to "compact" the DB.  And if possible, commands that can be run in a script.

Thanks
0
Comment
Question by:spenner
  • 3
  • 2
5 Comments
 
LVL 10

Accepted Solution

by:
bret earned 80 total points
ID: 1098567
The reorg command only exists in ASE 11.9 and higher, and only works againt the new "Data-Only-Locking" style tables.

In general, you can compact individual tables by building a clustered index on the table (if one already exists, drop and recreate it).

There is really nothing the compacts the whole database, though.  Even when you compact all the tables, the database still reserves the same amount of space on the file system/raw partitions, it is just that you have possibly freed some inefficiently used space for future use by the database (and improved performance by better clustering).
0
 
LVL 2

Author Comment

by:spenner
ID: 1098568
:The reorg command only exists in ASE 11.9 and higher, and only :works againt the new "Data-Only-Locking" style tables.

Sorry for being obtuse, but what exactly does ASE stand for (I'm a beginner in Sybase - although I do understand SQL)

:In general, you can compact individual tables by building a :clustered index on the table (if one already exists, drop and :recreate it).

What are the steps to recreating a clustered index.  Will doing this destroy any of the current data?

:There is really nothing the compacts the whole database, :though.  Even when you compact all the tables, the database :still reserves the same amount of space on the file system/raw :partitions, it is just that you have possibly freed some :inefficiently used space for future use by the database (and :improved performance by better clustering).

Since there is no way to compact the whole database, what about copying all the "non-flagged" data over to a newly created DB.  Would this "reorg" the DB?  And what would be a method of doing this?  Could it be done with a scripted command?

The reason I am asking is that this is a fairly active DB, so there will be a good bit of "entering/deleting", and while there is a lot of space to save the DB in, I want to use this space efficiently as the DB will eventually overrun its limits if not "reorganized".  I also want to avoid the current method of "reorging" the DB which is to simply delete the old one and create a new one, and then retype all the information in.  This method is horribly inefficient, and I want to use a different method.

Thanks
Shawn
0
 
LVL 10

Expert Comment

by:bret
ID: 1098569
ASE = Adaptive Server Enterprise, the new marketing name for Sybase SQL Server.  There is also an ASA, Adaptive Server Anywhere, and an ASIQ for datawarehousing.

To drop an index:

drop index <tablename>.<indexname>

to create a clustered index on a table:

create [unique] clustered index <indexname> on <tablename> ( <column name(s)>

Creating indexes does not destroy data.  Creating a clustered
index copies the data to some reserved space in a tightly compacted manner ordered by the columns in the column list, then
builds an index tree on top of it, finaly drops the original table.

You could copy the data into another database, but you would probably want to build indexes on the tables over there anyway (for perfomance reasons), so it would be faster and easier to just rebuild clustered indexes in the original database.
0
 
LVL 2

Author Comment

by:spenner
ID: 1098570
Thank You for your excellent answer - It helped me a lot.

Note: The commands that you stated above: Drop, Create.  Can they be run in AIX scripts?

Also, in your create command, you have [unique]  - does this mean I have to type it exactly as written (that is - type the word unique surrounded by square brackets), or do I have to insert a unique identifier in there


0
 
LVL 10

Expert Comment

by:bret
ID: 1098571
You can have these commands in a text file, which I would call an SQL script.  The commands have to be submitted to a Sybase client, however, not the AIX prompt.

One way to do this is called a "here" file (the syntax here indicating that everything between the lines of hyphens is a file named "nightly_compact.sql").  With this, you can use "nightly_compact.sql" as a UNIX command.

nightly_compact.sql
--------------------------------------
$SYBASE/bin/isql -Usa -Pmysapassword -Smyserver << EOF
use mydatabase
go
drop index mytable.myindex
go
create clustered index myindex on mytable (mycolumn)
go
-----------------------------------------


The brackets around "unique" indicate that it is an optional
parameter.  If you wanted the index to enforce uniqueness, you
would type just "unique" at the indicated location, if you don't
want that, don't type "unique".
0

Featured Post

ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Google always has something new and amazing up its sleeve, and the most current thing that they have been working on is another step in the evolution of Google Search, from machine learning to its brilliant successor, deep learning.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question