db_build_dictionary command

You can use the db_build_dictionary script before migrating data with db_migrate or db_migrate_iias, to improve compression and save space on the target system.

The command migrates a sample of data to build a dictionary on the target system. It is available in two versions, depending on your migration scenario:
  • When migrating from NPS, you first run db_build_dictionary nz on the target system, and then run db_migrate to migrate the data.
    db_build_dictionary nz -h
usage: db_build_dictionary nz [-h] -shost <address> -suser <name> -spass
                              <pass> -tuser <name> -tpass <pass> -stable
                              <name> [-ttable <name>] [-tdb <name>]
                              [-sample <float>%] [-temptable <name>]
                              [-tempdir <dirpath>] [-tschema <name>] -sdb
                              <name> [-sschema <name>]
                              [-multiByteChars <yes/no>]

optional arguments:
  -h, --help            show this help message and exit
  -shost <address>      Source host (Netezza)
  -suser <name>         Source user
  -spass <pass>         Source password
  -tuser <name>         Target user
  -tpass <pass>         Target password
  -stable <name>        Source table name
  -ttable <name>        Target table name
  -tdb <name>           Target database name [DEFAULT=BLUDB]
  -sample <float>%      Bernoulli sampling percentage [DEFAULT=3.0]
  -temptable <name>     Temporary table name
  -tempdir <dirpath>    Temporary directory
  -tschema <name>       Target schema [DEFAULT=uppercase target user]
  -sdb <name>           Source database name [DEFAULT=system]
  -sschema <name>       Source schema
  -multiByteChars <yes/no>
                        Same as -multiByteChars option from db_migrate
                        [DEFAULT=no]
  • When you migrate from a Db2 Warehouse or IIAS, you first run the db_build_dictionary iias on the source system, and then run db_migrate_iias to migrate the data.
    db_build_dictionary iias -h
usage: db_build_dictionary iias [-h] -thost <address> -suser <name> -spass
                                <pass> -tuser <name> -tpass <pass> -stable
                                <name> [-ttable <name>] [-tdb <name>]
                                [-sample <float>%] [-temptable <name>]
                                [-tempdir <dirpath>] [-tschema <name>]
                                [-sdb <name>] [-sschema <name>]

optional arguments:
  -h, --help          show this help message and exit
  -thost <address>    Target host
  -suser <name>       Source user
  -spass <pass>       Source password
  -tuser <name>       Target user
  -tpass <pass>       Target password
  -stable <name>      Source table name
  -ttable <name>      Target table name
  -tdb <name>         Target database name [DEFAULT=BLUDB]
  -sample <float>%    Bernoulli sampling percentage [DEFAULT=3.0]
  -temptable <name>   Temporary table name
  -tempdir <dirpath>  Temporary directory
  -tschema <name>     Target schema [DEFAULT=uppercase target user]
  -sdb <name>         Source database name [DEFAULT=BLUDB]
  -sschema <name>     Source schema [DEFAULT=uppercase source user]
  -ts TABLESPACE, --tablespace TABLESPACE   
                      Specifies table space for new target tables

Example

[bluadmin@nc123456 - Db2wh ~]$ db_build_dictionary iias -thost localhost -suser bluadmin -spass bluadmin -tuser bluadmin -tpass bluadmin -stable TEST_REDUNDANT -ttable TEST_REDUNDANT_IMPROVED
Creating pipe at /mnt/blumeta0/home/bluadmin/com.ibm.compression.t7ND5F/compr.pipe.TEST_REDUNDANT.sample
[DEBUG] Created temporary directory at /mnt/blumeta0/home/bluadmin/com.ibm.migration.iias.N75FSY
[INFO] Searching for "BLUADMIN"."TEST_REDUNDANT" on source...
[INFO] Searching for "BLUADMIN"."TEST_REDUNDANT_IMPROVED" on target...
[INFO] "BLUADMIN"."TEST_REDUNDANT_IMPROVED" could not be found on the target database
[INFO] Creating "BLUADMIN"."TEST_REDUNDANT_IMPROVED"...
[INFO] Searching for "BLUADMIN"."TEST_REDUNDANT" on source...
[INFO] Searching for "BLUADMIN"."TEST_REDUNDANT_IMPROVED_T190708113227759139" on target...
[INFO] "BLUADMIN"."TEST_REDUNDANT_IMPROVED_T190708113227759139" could not be found on the target database
[INFO] Creating "BLUADMIN"."TEST_REDUNDANT_IMPROVED_T190708113227759139"...
[INFO] Total time: 2.75447297096 (0:00:02)
[INFO] Migration summary

        Source table: "BLUADMIN"."TEST_REDUNDANT"
        Target table: "BLUADMIN"."TEST_REDUNDANT_IMPROVED"
        Empty table:  True



        Source table: "BLUADMIN"."TEST_REDUNDANT"
        Target table: "BLUADMIN"."TEST_REDUNDANT_IMPROVED_T190708113227759139"
        Empty table:  True


Log file: /mnt/blumeta0/home/bluadmin/db_migrate_iias-20190708_113227.825714.log
Tables successfully created:
       TEST_REDUNDANT_IMPROVED
       TEST_REDUNDANT_IMPROVED_T190708113227759139
Running: dbload -host 'localhost' -u 'bluadmin' -pw 'bluadmin' -df '/mnt/blumeta0/home/bluadmin/com.ibm.compression.t7ND5F/compr.pipe.TEST_REDUNDANT.sample' -t '"TEST_REDUNDANT_IMPROVED_T190708113227759139"' -ctrlChars -ignoreZero 'no' -ccsid '1208' -schema '"BLUADMIN"' -includeZeroSeconds 'yes' -escapeChar '\' -delim '|'
============================== Load session:   1 ==============================
Connecting to: 'bludb'
Connected to: 'bludb'
CREATE EXTERNAL

'log' file: '/mnt/blumeta0/home/bluadmin/logs/dbload/TEST_REDUNDANT_IMPROVED_T190708113227759139.BLUADMIN.bludb.log'
'bad' file: not found

Load session of table 'bludb."BLUADMIN"."TEST_REDUNDANT_IMPROVED_T190708113227759139"' completed successfully
Session started: 2019-07-08 11:32:31
Session ended:   2019-07-08 11:32:31
Elapsed time [hh:mm:ss]: 00:00:00
===============================================================================
Sample sent successfully
Rebuilding dictionary based on the provided sample...
CALL SYSPROC.ADMIN_CMD('LOAD
Compression dictionary built successfully
Dropping sample table TEST_REDUNDANT_IMPROVED_T190708113227759139 ...
DROP TABLE
Sample table dropped successfully
Removing the pipe...