Solved

How do I Reorg/compact a Sybase DB

Posted on 1999-01-20
5
2,194 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
Comment Utility
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
Comment Utility
: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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SyBase SQL Querry Syntax 1 243
connect to sybase then query 1 688
SQL Update query 9 354
Cluster Resource error 3 46
Marketing can be an uncomfortable undertaking, especially if your material is technology based. Luckily, we’ve compiled some simple and (relatively) painless tips to put an end to your trepidation and start your path to success.
Find out what Office 365 Transport Rules are, how they work and their limitations managing Office 365 signatures.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

763 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

12 Experts available now in Live!

Get 1:1 Help Now