Scenario: Redistributing data in new database partitions

This scenario shows how to add new database partitions to a database and redistribute data between the database partitions. The REDISTRIBUTE DATABASE PARTITION GROUP command is demonstrated as part of showing how to redistribute data on different table sets within a database partition group.

To redistribute data between the database partitions in DBPG1:

1. Identify objects that must be disabled or removed before the redistribution.
   1. Replicate MQTs: This type of MQT is not supported as part of the redistribution operation. They must be dropped before running the redistribution and recreated afterward.

      SELECT tabschema, tabname 
        FROM syscat.tables 
        WHERE partition_mode = 'R'

   2. Write-to-table event monitors: Disable any automatically activated write-to-table event monitors that have a table that resides in the database partition group to be redistributed.

      SELECT distinct evmonname 
        FROM syscat.eventtables E
        JOIN syscat.tables T on T.tabname = E.tabname 
          AND T.tabschema = E.tabschema
        JOIN syscat.tablespaces S on S.tbspace = T.tbspace 
          AND S.ngname = 'DBPG_1'

   3. Explain tables: It is recommended to create the explain tables in a single partition database partition group. If they are defined in a database partition group that requires redistribution, however, and the data generated to date does not need to be maintained, consider dropping them. The explain tables can be redefined once the redistribution is complete.
   4. Table access mode and state: Ensure that all tables in the database partition groups to be redistributed are in full access mode and normal table states.

      SELECT DISTINCT TRIM(T.OWNER) || \'.\' || TRIM(T.TABNAME) 
        AS NAME, T.ACCESS_MODE, A.LOAD_STATUS 
       FROM SYSCAT.TABLES T, SYSCAT.DBPARTITIONGROUPS 
        N, SYSIBMADM.ADMINTABINFO A 
       WHERE T.PMAP_ID = N.PMAP_ID 
        AND A.TABSCHEMA = T.OWNER 
        AND A.TABNAME = T.TABNAME 
        AND N.DBPGNAME = 'DBPG_1' 
        AND (T.ACCESS_MODE <> 'F' OR A.LOAD_STATUS IS NOT NULL)

   5. Statistics profiles: If a statistics profile is defined for the table, table statistics can be updated as part of the redistribution process. Having the redistribution utility update the statistics for the table reduces I/O, as all the data is scanned for the redistribution and no additional scan of the data is needed for RUNSTATS.

      RUNSTATS on table schema.table 
        USE PROFILE runstats_profile 
        SET PROFILE ONLY

2. Review the database configuration.
   The util_heap_sz is critical to the data movement processing between database partitions - allocate as much memory as possible to util_heap_sz for the duration of the redistribution. Sufficient sortheap is required, if index rebuild is done as part of the redistribution. Increase util_heap_sz and sortheap as necessary to improve redistribution performance.
3. Retrieve the database configuration settings to be used for the new database partitions.
   When adding database partitions, a default database configuration is used. As a result, it is important to update the database configuration on the new database partitions before the REDISTRIBUTE DATABASE PARTITION GROUP command is issued. This sequence of events ensures that the configuration is balanced.

   SELECT name,
     CASE WHEN deferred_value_flags = 'AUTOMATIC' 
       THEN deferred_value_flags 
       ELSE substr(deferred_value,1,20) 
       END 
     AS deferred_value
     FROM sysibmadm.dbcfg
     WHERE dbpartitionnum = existing-node
       AND deferred_value != '' 
       AND name NOT IN ('hadr_local_host','hadr_local_svc','hadr_peer_window',
                    'hadr_remote_host','hadr_remote_inst','hadr_remote_svc',
                    'hadr_syncmode','hadr_timeout','backup_pending','codepage',
                    'codeset','collate_info','country','database_consistent',
                    'database_level','hadr_db_role','log_retain_status',
                    'loghead','logpath','multipage_alloc','numsegs','pagesize',
                    'release','restore_pending','restrict_access',
                    'rollfwd_pending','territory','user_exit_status',
                    'number_compat','varchar2_compat','database_memory')

4. Back up the database (or the table spaces in the pertinent database partition group), before starting the redistribution process.
   This action ensures a recent recovery point.
5. Add three new database partitions to the database.
   Issue the following commands:

   START DBM DBPARTITIONNUM 3 ADD DBPARTITIONNUM HOSTNAME HOSTNAME3
    PORT PORT3 WITHOUT TABLESPACES;

   START DBM DBPARTITIONNUM 4 ADD DBPARTITIONNUM HOSTNAME HOSTNAME4
    PORT PORT4 WITHOUT TABLESPACES;

   START DBM DBPARTITIONNUM 5 ADD DBPARTITIONNUM HOSTNAME HOSTNAME5
    PORT PORT5 WITHOUT TABLESPACES;

   If the DB2_FORCE_OFFLINE_ADD_PARTITION is set to TRUE, new database partitions are not visible to the instance until it has been shut down and restarted. For example:

   STOP DBM;
   START DBM;

6. Define system temporary table space containers on the newly defined database partitions.

   ALTER TABLESPACE tablespace_name 
     ADD container_information 
     ON dbpartitionnums (3 to 5)

7. Add the new database partitions to the database partition groups.
   The following command changes the DBPG_1 definition from (0, 1) to (0, 1, 3, 4, 5):

   ALTER DATABASE PARTITION GROUP DBPG_1
     ADD dbpartitionnums (3 to 5)
     WITHOUT TABLESPACES

8. Define permanent data table space containers on the newly defined database partitions.

   ALTER TABLESPACE tablespace_name
     ADD container_information 
     ON dbpartitionnums (3 to 5)

9. Apply the database configuration settings to the new database partitions (or issue a single UPDATE DB CFG command against all database partitions).
10. Capture the definition of and then drop any replicated MQTs existing in the database partition groups to be redistributed.

    db2look -d DBPG1 -e -z 
      schema -t replicated_MQT_table_names 
      -o repMQTs.clp

11. Disable any write-to-table event monitors that exist in the database partition groups to be redistributed.

    SET EVENT MONITOR monitor_name STATE 0

12. Run the redistribution utility to redistribute uniformly across all database partitions.

    REDISTRIBUTE DATABASE PARTITION GROUP DBPG_1 NOT ROLLFORWARD RECOVERABLE
     UNIFORM STOP AT 2006-03-10-07.00.00.000000;

    Let us presume that the command ran successfully for tables T1, T2 and T3, and then stopped due to the specification of the STOP AT option.

    To abort the data redistribution for the database partition group and to revert the changes made to tables T1, T2, and T3, issue the following command:

    REDISTRIBUTE DATABASE PARTITION GROUP DBPG_1 
    NOT ROLLFORWARD RECOVERABLE ABORT;

    You might abort the data redistribution when an error or an interruption occurs and you do not want to continue the redistribute operation. For this scenario, presume that this command was run successfully and that tables T1 and T2 were reverted to their original state.

    To redistribute T5 and T4 only with 5000 4K pages as DATA BUFFER:

    REDISTRIBUTE DATABASE PARTITION GROUP DBPG_1 NOT ROLLFORWARD RECOVERABLE
     UNIFORM TABLE (T5, T4) ONLY DATA BUFFER 5000;

    If the command ran successfully, the data in tables T4 and T5 have been redistributed successfully.

    To complete the redistribution of data on table T1, T2, and T3 in a specified order, issue:

    REDISTRIBUTE DATABASE PARTITION GROUP DBPG_1 NOT ROLLFORWARD RECOVERABLE
     CONTINUE TABLE (T1) FIRST;

    Specifying TABLE (T1) FIRST forces the database manager to process table T1 first so that it can return to being online (read-only) before other tables. All other tables are processed in an order determined by the database manager.

    Note:

    • The ADD DBPARTITIONNUM parameter can be specified in the REDISTRIBUTE DATABASE PARTITION GROUP command as an alternative to performing the ALTER DATABASE PARTITION GROUP and ALTER TABLESPACE statements in steps 7 and 8. When a database partition is added by using this command parameter, containers for table spaces are based on the containers of the corresponding table space on the lowest numbered existing partition in the database partition group.
    • The REDISTRIBUTE DATABASE PARTITION GROUP command in this example is not roll-forward recoverable.
    • After the REDISTRIBUTE DATABASE PARTITION GROUP command finishes, all the table spaces it accessed will be left in the BACKUP PENDING state. Such table spaces must be backed up before the tables they contain are accessible for write operations.

    For more information, refer to the REDISTRIBUTE DATABASE PARTITION GROUP command.

    Consider also specifying a table list as input to the REDISTRIBUTE DATABASE PARTITION GROUP command to enforce the order that the tables are processed. The redistribution utility will move the data (compressed and compacted). Optionally, indexes will be rebuilt and statistics updated if statistics profiles are defined. Therefore instead of previous command, the following script can be run:

    REDISTRIBUTE DATABASE PARTITION GROUP DBPG_1
      NOT ROLLFORWARD RECOVERABLE uniform 
      TABLE (t1, t2,...) FIRST;