Dump & Load Scripts (^d)

NOTE: Before using this command key, be sure to have a recent database analysis in your PROTOP/dbanalys directory.  Doing so will allow ProTop to generate an accurate, optimized  proposed database structure file.  

You can do this manually from ProTop RT by using the "!" command key (during off-hours).  Or, schedule it during off hours by uncommenting or adding this line to your PROTOP/etc/schedule.<site>.cfg file:

0 1 * * 0 foreachdb.sh dbanalys.sh > ${PTTMP}/foreachdb_dbanal.err 2>&1

dump & load scripts (^d)

This panel allows the user to create OS scripts to dump and load an entire database. With the free version of ProTop, only a simple, single-threaded D&L script is created. The options above are available in the commercial version of ProTop, which adds the ability to optimize the D&L with various options.  

Advanced Scripts in the Commercial Version

If you are running the commercial version of ProTop and use the "^d" command key, you will see the form above.  Tab through and set each value.  Use F1/GO/Ctrl-X to create the dump & load scripts with these advanced features:

• The proposed structure file has dedicated areas for large tables and other areas based on rows per block
• The dump scripts are multi-threaded and balanced according to database contents

Once generated you will see a message similar to this:

And you find the dump and load files and scripts in two directories inside your dump & load work directory.  For example:

protop mb117sports (^d)

Dump & Load Work Dir:  /tmp and Dump Threads: 4

Dump & Load Scripts found in:  /tmp/mb117sports.dl.4

dlenv  
load.sh 
mb117sports.clean.sh
mb117sports.dump00.sh
mb117sports.dump01.sh
mb117sports.dump02.sh
mb117sports.dump03.sh
mb117sports.dump04.sh
mb117sports.zdumpall.sh

Build scripts are found in: /tmp/build

build.sh 
idxbuild.sh 
mb117sports.df 
mb117sports.df.noarea 
mb117sports.st 
mb117sports.tblmv.sh

A Note on Database Block Size

There are many valid recommendations regarding database block size, but we tend to favor 8192 (the largest possible value) in all cases. Other common answers are a) 4096 on Windows and Linux, 8192 on UNIX; b) match the file system block size to avoid torn pages.

The Free Version

Presents the "basic" dialog:

And provides these features:

• The st file has simple data, index, and LOB areas
• The dump scripts provide for a single-threaded dump

Using the ProTop Generated Advanced Dump and Load Scripts

1. Test, test, and then test again.
2. verify you have or probkup the database you want to D&L
3. run protop myDB and execute the ^d command key to dump the scripts tailored to that database
4. run [d&l workdir]/myDB.dl.4/myDB.zdumpall.sh - this will start all of the dump threads against myDB
5. while the dumps are running, run build/build.sh to:
   1. (delete and re) create the new database structure
   2. enable licensed features
   3. start the db and writers
   4. load schema
   5. move user tables & indexes to their proper storage areas
   6. back up the new empty database
6. verify dumps are complete
7. (this is repeatable) run [d&l workdir]/myDB.dl.4/load.sh which will prompt you for responses to various options but in general: 
   1. shut down the new database (because this is repeatable)
   2. clean up from previous runs according to your responses
   3. restore the copy of the new empty db
   4. load the data
   5. build indexes
   6. run a table analysis if desired etc
8. Test, test and test again to familiarize yourself with all the moving parts.