Creating a Tablespace for Archived Data
Before you archive data, consult your Database Administrator to create a tablespace large enough to hold the data you want to archive.
To calculate the amount of space you need for archiving:
1. Determine how many actual rows for your set of books are in the GL_BALANCES table for the fiscal year you want to archive using the following SQL statement:
select count(*)
from GL_BALANCES
where PERIOD_YEAR = [your archive year]
and ACTUAL_FLAG = 'A'
and SET_OF_BOOKS_ID =
(select SET_OF_BOOKS_ID
from GL_SETS_OF_BOOKS
where NAME=[your set of books name];
2. Determine how many rows are in the GL_JE_BATCHES table for the fiscal year you want to archive using the following SQL statement:
select count(*)
from GL_JE_BATCHES
where DEFAULT_PERIOD_NAME in [list of periods]
and ACTUAL_FLAG='A'
and SET_OF_BOOKS_ID =
(select SET_OF_BOOKS_ID
from GL_SETS_OF_BOOKS
where NAME=[your set of books name];
3. To determine how many rows are in the GL_JE_HEADERS or GL_JE_LINES tables for the fiscal year you want to archive, use the previous SQL statement, substituting GL_JE_HEADERS or GL_JE_LINES for GL_JE_BATCHES and PERIOD_NAME for DEFAULT_PERIOD_NAME.
4. Determine how many rows are in GL_IMPORT_REFERENCES table for the fiscal year you want to archive, using the following SQL statement:
select count(*)
from GL_IMPORT_REFERENCES
where JE_BATCH_ID =
(select JE_BATCH_ID
from GL_JE_BATCHES
where DEFAULT_PERIOD_NAME in [list of periods]
and ACTUAL_FLAG='A'
and SET_OF_BOOKS_ID =
(select SET_OF_BOOKS_ID
from GL_SETS_OF_BOOKS
where NAME=[your set of books name];
5. Determine the amount of space needed to archive the rows you want from the appropriate table(s). To do this, you must determine the average size of a row in each of those tables. First, determine the total number of rows in each table using the following SQL statement:
select count (*)
from [table name]
6. Consult your System Administrator to determine the total table size. Divide this table size by the total number of rows in the table to get the average size of a row in that table. Finally, multiply that average size by the number of rows you want to archive, as determined above.
Attention: Contact your Database Administrator if your tablespace is not large enough to store your archive data.
7. Select the archive tablespace and storage parameters for which to store the following interim tables using the Storage Parameters window:
GL_ARCHIVE_BALANCES
GL_ARCHIVE_BATCHES
GL_ARCHIVE_HEADERS
GL_ARCHIVE_LINES
GL_ARCHIVE_REFERENCES
See Also
Setting the Storage Parameters
Archiving Account Balances and Journal Detail
