25/11/2021 - Archiving Historical Journal Records
Archiving Historical Journal Records
|
|
|
When we post to Accounting products like MYOB AR Live, Xero, QBO and Sage Evolution, we post from 4 tables in Ostendo.
JOURNALHEADER (these records will be posted as General Ledger Journals in the Accounting Software)
JOURNALLINES (each journal may contain multiple lines linked to the JOURNALHEADER
JOURNALINVHEADER (these records will be posted as either Sales or Purchase Invoices in the Accounting Software)
JOURNALINVLINES (each journal may contain multiple lines linked to the JOURNALINVHEADER
These records are automatically created from our FINANCIAL table records when a transaction is generated in Ostendo. The FINANCIAL records themselves are not posted to these Accounting Products, only the journal records. Historical journal records are not required by Ostendo, once the posting is completed, however they build up over time and can have an impact on posting performance. Therefore we will now allow you to archive off journal records older than 12 months into new archive tables. This routine can be run as often as required.
Effectively these records are 'moved' from the current Journal tables to matching Archive tables, so the data is still retained within the Database if it is ever required.
By reducing number of records within the journal tables we believe this will reduce load on the posting process when journals are being posted to the Accounting Software. Severe loading at posting can potentially generate deadlock errors which usually indicate the server response times are too long. In other words, it does not have enough available resources to commit the transaction. It should be noted that archiving itself may note resolve deadlock errors if the server itself is under resourced. This is why Hardware reviews should be regularly undertaken to ensure the current IT framework suits current demands.
These routines are applicable to both Update 238 and Update 242 databases.
Implementation of Journal Archiving (Do this only once):
- Download the JournalArchiving.zip file from this link http://www.ostendo.info/downloads/ostendo/JournalArchiving.zip
- Unzip this file into a temporary file location (3 files)
- From Ostendo run the InstallJournalArchivingRoutine.txt command script located in the temporary file location. (This creates the following archive Tables)
- ARCHIVEJOURNALHEADER
- ARCHIVEJOURNALLINES
- ARCHIVEJOURNALINVHEADER
- ARCHIVEJOURNALINVLINES
- Setup a Standard Script called "Archive Journals"
- From the Script Tab, Import the RunningJournalArchiving.txt file located in the temporary location.
- Setup a Standard Script called "Restore All Archived Journals"
- From the Script Tab, Import the RestoringJournalArchiving.txt file located in the temporary location.
- Delete the zip and txt files from the temporary location
Running The "Archive Journals" Script
- Ensure no users are logged into Ostendo throughout this process
- Run the "Archive Journal Records" Script
- Enter a date you wish to purge back from (The script will not allow you to purge any journals within the current 12 month period)
- Press the "Answer" button. (Depending upon the number of records, this process may take some time). As an indication our test results processed the following number of records in around 30 seconds
- JOURNALHEADER 26000 records
- JOURNALLINES 56000 records
- JOURNALINVHEADER 10000 records
- JOURNALINVLINES 20000 records
Running The "Restore All Archived Journals" Script (Only required in some exceptional circumstances)
This process restores "ALL" journals currently in the ARCHIVE journal tables (You cannot restore records based on a selected date range)
- Ensure no users are logged into Ostendo throughout this process
- Run the "Restore All Archive Journals" Script
- Press the Run button. (Depending upon the number of records, this process may take some time). As an indication our test results processed the following number of records in 2 minutes
- ARCHIVEJOURNALHEADER 26000 records
- ARCHIVEJOURNALLINES 56000 records
- ARCHIVEJOURNALINVHEADER 10000 records
- ARCHIVEJOURNALINVLINES 20000 records
If you ever have a need to inquire on the Archive Tables, simply create new GL Ledger Inquiries similar to the standard ones, using the Archive Tables. (The table structure of the Archive tables is the same as that of the Journal tables)
|
|
|
Ian
Comments