To shrink sybase database to reclaim physical space we are doing dbunload for whole db. As it is taking more time, we are planning to do unload only specific tables to reclaim the physical space. Can any one please give me an example of how to do unload specific tables. dbunload -d -e

asked 15 Nov '11, 05:16

srisudha's gravatar image

srisudha
31113
accept rate: 0%


There is no mechanism to reduce the size of an existing database file.

The "dbunload - dbinit - reload" process results in a NEW database file that is smaller than the old one so the overall effect is one of shrinking.

So... running dbunload for only a few tables isn't going to shrink anything.

Question: Does your database grow in size again after you shrink it? If so, what is gained by the process of shrinking it?

Free space is re-used for new rows if you leave it alone, but if you shrink it, extra effort is required to re-grow the file to make room for new rows... and that newly allocated space will be fragmented if you're running on Windows. Folks often do exactly the opposite: ALTER DBSPACE SYSTEM ADD to expand the file beyond what is required, defragment the physical file, and then leave it alone for SQL Anywhere to use and re-use pages within the file.

If you are unloading and reloading to reorganize the tables inside the database, you can accomplish that with the REORGANIZE TABLE statement without having to do an unload/relaod ... but you should check first to make sure it's necessary.

permanent link

answered 15 Nov '11, 05:35

Breck%20Carter's gravatar image

Breck Carter
26.8k420580826
accept rate: 20%

edited 15 Nov '11, 06:12

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646

Hi Breck, Thanks for you reply. Currently we are doing the same. We are creating a new database file and replacing the older file. Coming to your question, after compression, the db size is growing in size based on data logging. We are periodically checking the DB size and doing operations.If the DB size limit exceeds the limit, some of the operations we are not able to do. So to get out from that we are doing DB compression. Since it is taking more time, we are planing for dbunload for specific tables.

If REORGANIZE TABLE will help to our case, can you please explain me the procedure and how it is differ from dbunload.

(16 Nov '11, 05:28) srisudha
Replies hidden

Reorganize table is all about minimizing table fragmentation inside the database file, rather than about saving space. From the help:

Reduce table fragmentation

Table fragmentation occurs when rows are not stored contiguously, or when rows are split between multiple pages. These rows require additional page access and this reduces the performance of the database server.

The effect that fragmentation has on performance varies. A table might be highly fragmented, but if it fits in memory, and the way it is accessed allows the pages to be cached, then the impact may be minimal. However, a fragmented table may cause much more I/O to be done and can significantly reduce performance if split rows are accessed frequently and the cost of extra I/Os is not reduced by caching.

While reorganizing tables and rebuilding a database can reduce fragmentation, doing so too frequently or not frequently enough, can also impact performance. Experiment using the tools and methods described in the following section to determine an acceptable level of fragmentation for your tables.

If you reduce fragmentation and performance is still poor, another issue may be to blame, such as inaccurate statistics. See Diagnosing performance issues.

Determine the degree of table fragmentation Checking the table fragmentation just once is not helpful in determining whether to defragment to improve performance. Instead, rebuild your database and check the table fragmentation to establish baseline results. Then, continue to check the table fragmentation periodically over an extended length of time, looking for correlation between the change in fragmentation to changes in performance measures. This method helps you determine the rate at which tables become fragmented to the degree that performance is impacted, and so determine the optimal frequency at which to defragment tables.

To obtain information about the degree of fragmentation of your database tables, use one of the following methods:

The sa_table_fragmentation system procedure. See sa_table_fragmentation system procedure.

The Fragmentation tab in the SQL Anywhere plug-in. The Fragmentation tab provides a graphical representation of the results from running sa_table_fragmentation system procedure on base tables. See Using the Fragmentation tab (SQL Anywhere plug-in).

To determine the degree of fragmentation (SQL) Connect to the database as a user with DBA authority.

Run the sa_table_fragmentation system procedure.

For example, run the following statement to get information about all tables in the database:

CALL sa_table_fragmentation( );

See sa_table_fragmentation system procedure.

(16 Nov '11, 05:41) Justin Willey

Please explain what you mean by this statement "If the DB size limit exceeds the limit, some of the operations we are not able to do."

Generally speaking, the file size has little to do with SQL operations.

(16 Nov '11, 05:53) Breck Carter

Are you facing a limited disk space situation, or what does prevent you from doing "some operations" when the DB size exceeds the limit?

(16 Nov '11, 05:53) Volker Barth

Just to answer the direct question in the subject line. You could create a new user, give that user permission only to the tables you want to unload, and then run the dbunload utility with the authentication credentials of the new user.

permanent link

answered 16 Nov '11, 16:04

TObject's gravatar image

TObject
36115
accept rate: 0%

AFAIK, dbunload expects DBA permissions. At least in my tests, reloading with a non-DBA user failed with missing permissions to select from SYSUSERPERM.

(22 Nov '11, 03:30) Volker Barth

We are facing diskspace issues. The limit is 150GB, If the DB size reaches to >150GB, after deleting the data from tables, actual DB size is reducing to 120GB,but physical size is showing as 150GB. To reclaim the physical DB we are doing DBUnload for compressing DB option. As it is taking more time(more than 6 hours) we are planning to do with specific tables. Please suggest me which one will help in this case...

permanent link

answered 16 Nov '11, 22:57

srisudha's gravatar image

srisudha
31113
accept rate: 0%

3

Given the low prices of disk space nowadays, I would suspect that any unload routine you plan to develop might very easily be more expensive in the long run than a bigger disk...

Just my 2 cents

(17 Nov '11, 05:19) Volker Barth
1

I agree with Volker... buy a bigger disk drive. High performance 500GB for U$150. If you don't need the speed, 2TB for $200.

Buy two, one for backups. And/or buy external USB drives for backups. You may be taking unnecessary risks... what if your current disk drive fails? Have you tested your backup and recovery plan?

(17 Nov '11, 06:12) Breck Carter

FWIW, just in the particular case that "too limited disk space" is your concern and more disk space isn't affordable (not everyone has a budget...), and you're willing to adapt your database design or maintenance:

To reduce the space needed, you might consider the following hints (among others):

  • Use the smallest datatypes possible (e.g. smallint instead of int and the like)
  • Don't use UNICODE unless needed
  • Declare columns NOT NULL when possible
  • Insert data with as much "filled values" as possible (i.e. don't insert "empty rows" and fill then later by updates as that may lead to row splits)
  • If you're using BLOBS or long strings, store them as COMPRESSED columns - or use an external compression facility for particular BLOBs (AFAIK the builtin compresssion is rather generic - in contrast, e.g. storing WORD docs as PDFs will reduce much space)
  • If you are allowed to delete "old" data, delete that before space becomes a problem. If you have high data throughput but the actual size of the "current data" size will be quite stable (i.e. the number of deletes and inserts will be quite equal), then you might get along well with a database size where the pages of deleted data get re-used by new data.
  • If you might have to archive "old" data in a different database, you might be able to use proxy tables to "move" that data to a different database on a different disk.

As said, just some hints to reduce the database size... I'm sure others have even better suggestions! All this should allow to reduce disk space while the database is running instead of having to fix it afterwards.

permanent link

answered 17 Nov '11, 16:31

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

edited 17 Nov '11, 16:32

I have tried with REORGANIZE TABLE, but it is not reducing the physical size of the db. Can any body please give me an example for dbunload -e -d option for unload tables alone.

permanent link

answered 22 Nov '11, 00:21

srisudha's gravatar image

srisudha
31113
accept rate: 0%

Well, as the docs tell

  • the -e option expects a list of those tables that should not be unloaded.
  • The opposite switch -t expects a list of those that should be unloaded.

So given you would like to export only tables T1 and T2, you might use something like

"%SQLANY12%\bin32\dbunload" -c "DSN=..." -d -t T1,T2 -r c:\myreload.sql -xx c:\myUnload

The created myReload.sql will not contain any DDL but simply consist of LOAD STATISTICS and LOAD TABLE statements for these tables.

(22 Nov '11, 03:26) Volker Barth

FWIW, as @Justin has explained in his comment, REORGANIZE TABLE is not expected to shrink the file size - it reduces fragmenation inside the database file. So your results are as expected.

(22 Nov '11, 03:34) Volker Barth

Hi Volker, Thanks for your reply. I have one doubt. While doing dbunload, do we really required to stop db. Currently for DBUnload for full db, we are stopping the DB. Can you please explain for unloading a table alone do we need to stop db?

(22 Nov '11, 07:59) srisudha
Replies hidden

Note, I have turned your posting into a comment below this answer - I would recommend to classify your coming postings as answers or comments, as this forum has nice features to add comments to answers or to other comments ... see the buttons "add new comment" and "reply" on the right bottom egde of each answer/comment.


To your question: As you can see from my sample, I have run DBUNLOAD against a running database (specified by the -c connection parameters), so there's no need to stop the database for this usage.

I guess that's different when reloading/unloading the whole database (say, with -ac/-an/-ar). That should require a non-running database.

(22 Nov '11, 08:14) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×38

question asked: 15 Nov '11, 05:16

question was seen: 4,185 times

last updated: 22 Nov '11, 08:14