Solved

How do I Reorg/compact a Sybase DB

Posted on 1999-01-20
5
2,223 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Agreggate sums in sql 1 522
Return results from Sybase DB Query with Powershell 7 1,572
SyBase SQL Query 7 153
SQL Query Syntax 11 161
Note: This is the third blog post in a series on email clearinghouses (https://www.xmatters.com/alert-management/blog-email-has-failed-us?utm_campaign=70138000000ydLoAAI&utm_source=exex&utm_medium=article&utm_content=blog-post).   We’ve been talki…
An analysis of the phishing scam that has been affecting Google users, along with steps to take for protection, as well as what to do if you receive one of the emails.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now