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? |
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.
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
@Henning: Why do you think AUTOINCREMENTs are a mess? I guess lots of SA developers (including us) use them with great confidence.