Kbase P139339: How to dump and load the Fathom Database
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
6/29/2009 |
|
Status: Verified
GOAL:
How to dump and load the Fathom Database
GOAL:
How to convert the FathomTrend database to Type II Storage Areas
GOAL:
How to re-organise the fathomtrenddb
GOAL:
How to re-create Fathom Trend database ?
FACT(s) (Environment):
All Supported Operating Systems
OpenEdge Management 3.1x
Fathom Management 3.x
Fathom Management 2.x
Fathom Management 2.0A
FIX:
Over time, the fathomtrenddb grows in relation to how much trending information is stored and how often. Running the Compact Trend option helps, but eventually the database scatter and fragmentation and index split operations start affecting the performance of (say) reports. This Solution describes a method to dump and load the Fathom Trend Database, where:
a. convert to type II Storage Areas
b. enable largefiles (assuming the OS and Filessystem allow largefiles)
c. increase the bi blocksize and biclustersize
d. separate data and indexes into distinct Type II Storage Areas
STEPS:
1. Stop the fathom database:
From Fathom Console: Fathom > My Collections > Home > FathomTrendDatabase > Control > Stop Database
2. Backup the current fathom database
$ cd /db/fathom # current location of the fathom trend database
$ probkup fathom /backup/fathom.bak
$ prorest fathom /backup/fathom.bak -vf
3. Dump the fathom database
Single user connect to the fathom database (multi-user is possible as well, if you first stop all trending from all resources, iow no 'other' access to the database)
$ pro fathom
Tools > Data Dictionary > Admin > Dump Data and Definitions > Data Definitions (.df file)...
Admin > Dump Data and Definitions > Sequences Current Values...
Admin > Dump Data and Definitions > User Table Contents...
Admin > Dump Data and Definitions > Table Contents (.d file)
4. Create a new .st file
# example, the fixed sizes are at your discretion
b . f 40960
b .
#
d "Schema Area":6,64;1 .
#
d "Trend_Data":7,128;8 . f 500000
d "Trend_Data":7,128;8 . f 500000
d "Trend_Data":7,128;8 .
#
d "Trend_Index":8,1;8 . f 500000
d "Trend_Index":8,1;8 .
5. Modify the fathom.df
Change the AREA for indexes to "Trend_Index"
6. Delete the current fathom database
$ echo y | prodel fathom
7. Create the new fathom database, with largefiles enabled and increase the bi blocksize and bi clustersize
$ prostrct create fathom -blocksize 8192
$ procopy $DLC/empty8 fathom.db
$ proutil fathom -C EnableLargeFiles
$ proutil fathom -C truncate bi -bi 131072 -biblocksize 16 -G 0
8. Load the new fathom database
$ pro fathom
Tools > Data Dictionary > Admin > Load Data and Definitions > Data Definitions (.df file)...
Admin > Load Data and Definitions > Sequences Current Values...
Admin > Load Data and Definitions > User Table Contents...
&nb.sp; Admin > Load Data and Definitions > Table Contents (.d file)...
Admin > Security > Edit User List... add user sysprogress, password sysprogress
NOTE: some checks (not mandatory)
a. _user list will now have 3 users: guest, trending and sysprogress
b. cf_control has all records: alert_id, ar_id, backup_id, compact_id, job_id, rep_id, resource_id, sample_id, status-id, status_id, task_id, version
9. Start Fathom database multi-user and Grant permissions to 'trending'
$ sqlexp -db fathom -user sysprogress -password sysprogress -S 1313
SQLExplorer>grant dba to "trending";
SQLExplorer>commit;
SQLExplorer>select * from sysprogress.sysdbauth;
GRANTEE DBA_ACC RES_ACC
-------------------------------- ------- -------
root y y
SYSPROGRESS y y
trending y
SQLExplorer>quit.
Start a 4GL/ABL connection to the fathom trend database and run the following code to create a GRANT script on all PUB tables:
OUTPUT TO "allfiles.SQL".
FOR EACH _file
WHERE _file._file-num GT 0
AND _file._file-num LT 32000 NO-LOCK:
PUT UNFORMATTED
'grant select on pub."' + _file._file-name + '" to public ~;'
SKIP
.
PUT UNFORMATTED
'COMMIT;'
SKIP
.
END.
$ sqlexp -char -infile allfiles.SQL -outfile report.txt -db fathom -user sysprogress -password sysprogress -S 1234
10. Take an online probkup of the fathom trend database
$ probkup online fathom fathom_new.bak
NOTES:
1. When first installing OpenEdge Management, there is a 'base' fathom database 'fathom.bak', it is a very good idea to preserve this baseline backup. If not needing to preserve the previous trending information, once could always merely prorest this 'base' backup into the new structure and a fresh fathom trend database will be ready for use.
2. If the Fathom database has no tables > 2GB, then a binary dump and load followed by proutil -C idxbuild may be faster
3. Advice: Run some reports pre- and post dump and load to gauge performance improvement eg: Area Status Report
.