Wednesday, July 27, 2022
Tip of the Day: Archiving Table Data in R:BASE
Product.......: R:BASE X.5 and R:BASE X.5 Enterprise (Version 10.5)
Build.........: 10.5.4.20616 or higher
Sections......: Maintenance
Keywords......: Archive, Table, Data, Performance, ODBC
Archiving tables in R:BASE databases is an ideal strategy to move
infrequently accessed records, but are still of historical importance.
Data growth will impact an application's performance over time. With
each new transaction, inactive data grows putting more pressure on
application performance and availability. Operations and compliance
concerns may arise, and batch jobs, data replication, and disaster
recovery all run slower making users wait longer. Extended downtime
must also be planned to convert legacy data to new releases during
upgrade cycles.
Only the tables with a large number of records would be archived. An
archive point (specific date or column value) would be best
determined by you and application users/staff where the archive data is moved.
Archive tables may be stored in the existing database, or moved to
another database entirely, to be accessed via ODBC.
To create an archive table for an OrderHeader table within the
current database, use the following steps:
PROJECT OrderHeader_Archived FROM OrderHeader USING * WHERE LIMIT = 0
APPEND OrderHeader TO OrderHeader_Archived WHERE OrderDate <= 12/31/2020
DELETE ROWS FROM OrderHeader WHERE OrderDate <= 12/31/2020
You can otherwise use an unique identifier value (OrderID) for the
WHERE clause, to maintain primary key/foreign key relationships.
PROJECT OrderHeader_Archived FROM OrderHeader USING * WHERE LIMIT = 0
APPEND OrderHeader TO OrderHeader_Archived WHERE OrderID < 1002587
DELETE ROWS FROM OrderHeader WHERE OrderID < 1002587
PROJECT OrderDetails_Archived FROM OrderDetails USING * WHERE LIMIT = 0
APPEND OrderDetails TO OrderDetails_Archived WHERE OrderID < 1002587
DELETE ROWS FROM OrderDetails WHERE OrderID < 1002587
To use another database to stored archive records, the original
database table structure can be unloaded, to build an empty
repository. Then, the data may be unloaded using the desired WHERE
clause for the specific archive point.
If you will use the archive table for searching prior records, the
constraints should be added to maintain optimal performance. The
constraints may be unloaded from the original tables (UNLOAD
STRUCTURE...), and altered for use with the intended archive table.
The archive table constraints would match the original table
constraints, where the primary/foreign keys link the archive tables.
Only constraints related to the intended use would need to be
applied, as querying the records would still take place. However,
rules may not be needed if new data will no longer be added on a
row-by-row basis. The added indexes to the archive tables will not
effect the overall database performance.
Once the archive tables and record transfer process has been
completed, a PACK/RELOAD must be performed upon the database.
If there is a need to query both current and archive records, perhaps
more records would be left in the current tables, and less would be
archived. Or, based upon the intended use, the original and archive
content may be inserted into a temporary table for specific reports
and queries.
Very Best R:egards,
Razzak.
R:BASE Technologies, Inc.
https://www.rbase.com
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to rbase-l+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/0MGkbP-1oC4qd0NSD-00DWD6%40mrelay.perfora.net.
