Simple archiving setup for SQLAnyhere 10

[Former Member]

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?

[Breck_Carter]

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.

[Former Member]

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

[VolkerBarth]

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

[Former Member · Accepted solution]

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.

[VolkerBarth · Accepted solution]

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.

[Former Member · Accepted solution]

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