Simple archiving setup for SQLAnyhere 10

I'm running a SQLAny10-App where a couple of tables is frequently updated. I'm logging these changes via triggers into log tables (one head record for each changed record and n child records for each changed column).

The problem is, these log tables are growing quite fast, and are now consuming almost half of the database size. I've already set up an archive database at the customer's site, where we occasionaly unload the log tables from the main db, and reload them into the archive db, then truncate the log tables on the main db. I order to reclaim the space used, we have to rebuild the main db competely. This is a tedious an error-prone task, so I'm looking for a simpler way to archive the changes. I've already tried to let the triggers write directly into the remote archive-db (remote server is set up and accesible), but I'm running into problems with the identity column, which does not seem to work correctly for remote databases.

My question to you experts out there is: Is there a simple setup for archiving tables? I've started to look into QAnywhere, but this seems to be an oversized approach for this issue. Isn't there any simpler way?

asked 09 May '10, 19:22

Henning
43●2●2●4
accept rate: 0%

edited 10 May '10, 14:12

Calvin Allen
1.5k●23●26●38

Please explain how SQL Remote is involved... and whether or not you want the deletions and insertions replicated. FWIW the TRUNCATE TABLE statement resets the next value for a DEFAULT AUTOINCREMENT column, whereas DELETE does not.

(09 May '10, 20:30) Breck Carter

No SQL-Remote is involved. Autoincrement cols are a mess, looking forward to the new ANSI-Sequence-feature in SQLAny12. Will go with Zote's answer. Thx

(11 May '10, 20:00) Henning

@Henning: Why do you think AUTOINCREMENTs are a mess? I guess lots of SA developers (including us) use them with great confidence.

(12 May '10, 15:51) Volker Barth

flat view

2 Answers:

active answers oldest answers newest answers popular answers

We have same solution here. What we do is: Every day, log db connect to prod db and inserts all log records prior today (select * from logtable where date < current date) in log db and then delete those record from prod db.

With this approach, prod db will not fail if log db is offline.

permanent link

answered 10 May '10, 11:13

Zote
1.7k●36●40●51
accept rate: 43%

That approach with frequent DELETEs will also prevent the prod db from growing that large. As such the need for the rebuild step (to reclaim free space) won't be necessary.

(10 May '10, 12:35) Volker Barth

This seems a viable approach. Tried this locally and it works like a charm. Thx!

(11 May '10, 20:02) Henning

flat view

I think the flaw in your original implementation was the belief that you needed to rebuild the database in order to reclaim the space. While the database file wouldn't shrink when you deleted the logging data, the pages would be freed up within the database file and would then be reused by the database. Rebuilding the database would make the database file smaller, but then the database would just have to grow again until the next time you archived your data. If you stuck with deleting the archive data without rebuilding the db file then it would reach a "normal" size and reuse the empty free pages within the db without growing other than for any normal growth due to the size of your dataset.

I would suggest that the biggest reason to rebuild the database would be to then pre-allocate space to the db file once you know how big a database you are likely to need. The benefit of pre-allocating space would be to minimize file fragmentation on disk.

As others have said, archiving more frequently will reduce the maximum db file size you require, but that is independent of any misconception that you needed to rebuild the file in order to reclaim space.

permanent link

answered 12 May '10, 14:13

Rob Waywell
31●1●1●6
accept rate: 0%

@Rob: Good annotation! However, w.r.t the "biggest reason to rebuild the database would be to then pre-allocate space" - wouldn't an "ALTER DBSPACE ADD..." be enough (and easier) in such a case?

(12 May '10, 15:49) Volker Barth

flat view

Your answer

toggle preview

community wiki:

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

learn more about Markdown

Question tags:

sa-10 ×119
logging ×40
identity ×8
archiving ×2

question asked: 09 May '10, 19:22

question was seen: 1,855 times

last updated: 12 May '10, 14:13

SAP SQL Anywhere

Simple archiving setup for SQLAnyhere 10

Follow this question

Related questions