Binary dump/load provides a fast way to dump and load a database with proper planning and dry runs before the main event.
Data IntegrityThe success of the binary dump and load method depends on the current health of data and indexes.
Run full database integrity checks with a DBTOOL database scan for bad record blocks and bad records and an IDXFIX Option 3 to verify key fields and index entries on Primary Indexes or the index that is planned to be used when the data are dumped.
Online or Offline? The database can be both dumped and loaded in multi user mode which facilitates concurrent sessions.
When run offline (single-user) crash recovery needs to run before each table is dumped or loaded, unless the -RO client parameter is used for binary
dump.Read-Only dump sessions can run concurrently against an online database, where Private Buffers are allocated by the -B parameter used. This method is not recommended. Refer to Article
Does a binary dump with -RO use buffers from the normal -B buffer pool? An alternative is to not start the database multi-user and run concurrent binary dump sessions using the -RO (client) parameter providing a -B parameter to allocate a medium size cache during the dump.
The binary load process should always be run multi-user. See "
Tune the BI Subsystem" below.
Large tables can be loaded during the same time they are dumped.Binary dump/load automatically creates and switches a new file (.bd2, .bd3, etc) when
- The current dump file reaches an OS or user file size limit.
- A threaded binary dump (-thread 1 -threadnum <n>) creates multiple binary dump files (one for each thread)
This facilitates scripting the load process as soon as the first dump file is available.
Generate dump and load scriptsThere is no option to dump and load the entire database with a single command. Each table in the database needs to be dumped then loaded from a separate PROUTIL command line. Dump and load scripts can be generated. Refer to Article
How to generate scripts to run binary dump and load for all tables? Tune the BI Subsystem:The load process generates I/O activity on the before-image file (.bi). Performance can be improved by increasing the bi cluster and bi block size to improve fast checkpointing and in addition load with the no-integrity parameter. Refer to Article
Using -i (no integrity) for binary load A binary dump/load can be faster than bulkload when tuned properly. Refer to Article
Performance of Binary Dump/Load versus Bulkload Build IndexesAfter the load process completes,
do not forget to rebuild all indexes. Refer to Article
How to run idxbuild the fastest way? Binary load does not create the index structures of database unless the "build indexes" option is specified when loading into empty tables. Refer to Article
Building Indexes with Binary Load Summary:
- -RO parameter for binary dump
- tune the -bi and -biblocksize
- Multi user mode to increase performance on load processes (-B, -spin, -hash, -bibuf, APW, BIW)
- -i for binary load
