[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

How do I Reorg/compact a Sybase DB

Posted on 1999-01-20
5
Medium Priority
?
2,403 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 320 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

656 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