DBA
      Back to home
      1. ProTop Knowledge Base
      2. DBA

      Dump & Load Scripts (^d) - PAID

      Generate optimized example dump and load scripts for your database using the commercial version of ProTop. It will dump and load faster, and you end up with a database structure optimized for your data so it will also run faster!

      NOTE:  These are sample scripts.  They will not dump and load your database for you.  This is merely an example of how to go about a dump and load of the application data tables in your database. It does not include other schema components like audit policies, SQL views, LOBs etc.  These vary according to the OE components you are using. 

      You should be versed in operating system scripting (.bat or .sh) to understand and use these scripts.  

      These are UNSUPPORTED, use them AT YOUR OWN RISK!

      NOTE: To enable the commercial version of the Dump & Load Scripts, for subscribers, reach out to support@wss.com for a key to turn this feature on.

      CAUTION: Before using this command key, 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 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) - PAID

      img00100

      This panel allows users to create OS scripts to dump and load the application data for a  database more efficiently. 

      Descriptions of the fields in the form above:

      ProTop RT Label Description
      Large Table Defines the size threshold over which "large" tables are segregated in their own storage area
      Active Defines the read activity threshold over which "active" tables are segregated in their own storage area
      Block Size Target block size.
      Dump Index Choice of primary or no-index for type 2 storage areas
      Dump Threads The number of dump threads to spawn
      Dump & Load Work Dir Scripts are configured with this directory holding the newly created dump and load scripts
      Target DB Dir Scripts are configured with this directory containing the target database file
      Target BI Dir Scripts are configured with this directory containing the target database BI file
      Target AI Dir Scripts are configured with this directory containing the target database AI file

      A Note on Database Block Size

      Many valid recommendations regarding database block size exist, 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.

      Generating the advanced scripts in the Commercial Version or ProTop

      You will see the form above when you run the commercial version of ProTop and use the "^d" command key.  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:

      img00200

      Find the dump and load files and scripts in two directories inside the Dump & Load Work Dir you provided.  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

      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 and 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.


      See also:

      A dump & Load Component Checklist