Consultor Eletrônico

Status: Verified

GOAL:

How to ASCII dump and load a multi-volume database

GOAL:

How to dump and load a multi-volume database between Progress Versions


FACT(s) (Environment):

Progress 9.1x
Progress 8.x
OpenEdge 10.x


FIX:

The following instructions assume:

a) That in the case of v8 databases, the database is already a multi-volume database. If this is not the case, please refer to Progress Documentation on the prostrct convert utility.
b) That a compile-time license is installed in order to access the utilities, listed. If this is not the case, please refer to Solution P12956 below.


STEPS:

1.) Ensure that there is a valid backup of the database

2.) Make sure that you are in the file-structure (directory) where you want the database to end up in.

From a PROENV session: (this will set all the relevant environment variables for you, or alternatively from your shell where you have the Progress environment set for the associated DLC and PATH environment variables)

3.) Truncate the bi file existing database to ensure that there are no outstanding transactions:
$ proutil dbname -C truncate bi ?G 0

4.) Create a structure file reflecting the current database file structure:
$ prostrct list dbname dbname.st

5.) Start the Database Administrator:
# Note, this dump process can also be run in multi-user mode by proserv'ing the database first and then starting multiple DBADMIN sessions (without the "-1"), each dumping different sets of .d's. There is no point in doing this if there is only one CPU and or all the data are on one disk in the file system.
$ prowin32 dbname -p _admin.p -1

6.) FROM THE DBADMIN utiltiy:

Admin -> Dump Data and Definitions
-> Data Definitions .df
-> [Select Some, Table Name = *, OK]
-> [Output file dbname.df, Code Page ISO8859-1 (or the codepage is in use),
include r compatibility = TRUE]

Admin -> Dump Data and Definitions
-> Table Contents.d -> [Select Some, Table Name = *, OK]
NOTE: the dump of the data files can be run multi-threaded by proserving the database and running several instances, each dumping selected tables. You must however ensure that the database is not being updated by cron jobs/users.

Admin -> Dump Data and Definitions
-> Sequence Current Values

Admin -> Create Bulk Loader Description File
-> Select Some, Table Name = *, [OK]
-> [Output file dbname.fd OK]
EXIT the Database Administration Tool.

7.) Delete the existing database:
$ prodel dbname
# we have just dumped the needed information to create a new database and have made a backup in STEP 1 if needed.


If the dump and load operation is being undertaken to convert the existing database to a different major version (aka: V8 to V9, V8 to V10, V9 to V10 for example) change the environment to that version at this point by running the %DLC%\bin\proenv.bat on Windows or DLC/bin/proenv on Unix.


8.) Create the new void structure, with the planned database blocksize. If new database file locations, records per block, fixed extent sizes have been planned, then the dbname.st file (created in Step 4 above) needs to be modified as such. It is worth first verifying it to make sure that the directory listings are correct. This step will create a VOID 8K database (for example):
$ prostrct create dbname dbname.st -blocksize 8192


9.) Create an EMPTY database that can be accessed with a 8K blocksize
$ procopy %DLC%\empty8 dbname

10a) FROM THE DBADMIN utiltiy:
$ prowin32 dbname -p _admin.p -1

Admin -> LOAD Data and Definitions
-> Data Definitions .df
-> Input File name dbname.df

EXIT the Database Administration Tool.

10b) LOAD Data and Definitions with the bulkload utility which will require a full index rebuild afterwards:
$ proutil dbname -C bulkload dbname.fd -B 10240 -G 0

- OR -
The database can be proserve'd and you can start a multi-load session, defini.ng which database.d files each load session will be loading. For best results, load the tables with the smallest records first. Please refer to Solution 15479 below for more detail.

Start each session with:
$ prowin32 dbname -p _admin.p

Admin -> LOAD Data and Definitions
-> Table Contents.d
-> select the required tables that this session will be loading
-> input directory = Progress working directory where the table.d were dumped in step 6 above.

10c) Load the sequence current values (if in use) so that new records will have the next_correct_sequence_value:
prowin32 dbname -p _admin.p -1
Admin -> LOAD Data and Definitions
-> Sequence Current Values
-> _seqvals.d

EXIT the Database Administration Tool.

10d) If the bulkload option was selected (10b), then an idxbuild is needed:
$ proutil dbname -C idxbuild ALL -TB 24 -TM 32 -B 1024 -G 0
# Note, that if the database is > 2Gb, you will need to define a multi-volume srt file before undertaking the idxbuild ALL operation. Please refer to Solution P55740 below.

12) Take a backup of the new database: probkup dbname dbname.bak

Lastly, consider running the TABANALYS and IDXANALYS reports to use as a baseline for future reports when investigating the fragmentation indecies..