Solved

How do I Reorg/compact a Sybase DB

Posted on 1999-01-20
5
2,285 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The following article is comprised of the pearls we have garnered deploying virtualization solutions since Virtual Server 2005 and subsequent 2008 RTM+ Hyper-V in standalone and clustered environments.
Changing a few Outlook Options can help keep you organized!
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decadeā€¦

739 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