Db2

Introduction

Large Data Movement

There are several factors that drive DB migration activities, such as transitioning to the cloud from on-premise setups or most-popular IT trend of cost reduction. The DB2 database, which handles complex data types including XML, JSON, and Blob, has been well-known data base management system for many years. However, due to the complexity to migrate various datatypes tables, Database Administrators (DBAs) often have numerous questions about the best strategies and tools for managing data movement during migration.

When faced with the choice of migrating a database using DB2's native backup and restoreutility, this approach should unquestionably be taken into account. Nonetheless, today's focus will centre around the utilization of 'db2move' – exploring its optimal use cases and the real-world scenarios where backup and restore are not an option.

Grasping the Fundamentals of Essential Data Movement Utilities in DB2

Understanding Export, Import, and Load 

Working of data movement utilities

DB2 Export and Import/Load Utilities persist as a conventional means of executing data movement tasks. Within this framework, there exists a load cursor method that proves advantageous when immediate intermediate storage isn't accessible for copying data from one end to subsequently loading it into the target. This approach is especially useful when dealing with large tables that make a direct export and load process challenging.

Furthermore, the export-load methodology facilitates the inclusion of options for loading data into individual tables. This capability proves useful when the objective involves transferring specific subsets of data as part of the larger data movement operation. However, challenges arise when attempting to manage the complete database or schema migration, necessitating the DBA to either invest significant time or script complex procedures to oversee the entire array of tables. Imagine the magnitude of dealing with terabytes of data spanning multiple schemas during such migration endeavours.

When addressing data movement within the same DBMS, such as moving between DB2 instances, it's advisable to give precedence to the utilization of the IXF format. Nevertheless, if the scenario involves a different DBMS product, favouring the Delimited format is generally the popular format used. It's imperative to acknowledge the possible risk of data exposure due to the inherent readability of the Delimited format. Therefore, exercise caution and make thoughtful decisions based on your specific requirements. Notably, the Load with Cursor option doesn't involve local data copying, rendering it inapplicable in this context.

In Conclusion , the Export and Import/Load utilities can be effectively employed in situations where the aim is to utilize a proven method for data movement. These utilities particularly shine when there's a need to move tables between locations while selectively transferring specific subsets of data within those tables to the target destination.

Maximizing Data Migration Efficiency with db2move

Sync Smarter, Not Slower: DB2Move Copy and parallelism in Perfect Harmony!

When researching into the understanding of any tool, it's important to grasp both its capabilities and limitations in order to make informed decisions. In the case of db2move, the following points outline its capabilities:

·      Database and Schema Movement: db2move can move an entire database or specific schemas as required. It accommodates single-schema or multi-schema migrations.

·      Parallel Processing: The utility supports parallelism up to 16 threads, allowing for more efficient copy of data.

·      Table Selection: Tables can be selected for movement using the only Include method. This can be achieved through either the -tn option (table names) or the -tf option (table names provided in a file).

·      Data Movement Options: db2move offers four options:

1.    Copy: This mode enables movement without requiring intermediate storage space. Copy Mode Variations: In Copy option, three modes are available:

a)    Create DDL only.

b)    Create DDL and Load data (default)

c)    Load only

2.    Export.

3.    Import.

4.    Load.

Also note non-recoverable option available in copy and load options allows skipping enforcement of tablespace backup.

For the purpose of this blog, we're focusing on creating objects separately for specific reasons. Consequently, the LOAD_ONLY option is being utilized.

DB2Move Copy Excellence Minus XML

The boundaries consist of; The COPY option does not work with tables containing XML columns. When utilizing db2move with import/load, similar challenges arise as seen with the IMPORT or load methods when handling columns generated with the "generated always" attribute.

Having acquired this knowledge, effectively utilizing db2move becomes a seamless effort. The methodology highlighted in this blog centres around the adoption of the COPY option. This decision negates the necessity of conducting a local export followed by an import or load into the target database. Embracing this streamlined approach requires only a few steps to initiate the load process. 

Setting the Stage: Key Steps for a Well-Prepared migration

With a focus on safety, we have opted for an approach that centers around managing a single schema instead of tackling the complexity of handling the entire database simultaneously. This involves employing the LOAD ONLY option within the copy mode.

1. Creating Essential Database Structure:

Database Skeleton, Minus the Data

It's crucial to obtain the DDL creation script for the target database. This script should create database components, including Buffer pools, table spaces, procedures, functions, tables, views, triggers and constraints. Ensuring the thorough inclusion of these elements sets the stage for a successful migration.

2. Create catalog entries:

Single Node, Dual Reach: Source and Target on Target Node

Within the scope of this blog, we're outlining a practical methodology that involves conducting migration operations locally on the destination or target server. This server houses the new database where data will eventually reside. To initiate the migration process, one of the essential steps involves cataloging the source database. This cataloging step is essential to establish the necessary connection for copying data.

3. Handling Constraints and Indexes:

For a seamless migration, consider disabling foreign key constraint checks during the migration process. This step mitigates potential conflicts and ensures a smoother data transition. Additionally, for larger tables boasting multiple indexes, contemplate dropping indexes prior to migration. It's worth noting that db2move often exhibits the intelligence to effectively rebuild indexes during the final stages of migration.

As a general guideline, for the efficient loading of sizable tables, typically exceeding 15 GB, it's advisable to temporarily drop indexes on these tables. Subsequently, these indexes can be reconstructed once the loading process is complete. This approach contributes to enhancing the overall performance of the loading operation.

4. Segregating Tables for Strategic Migration:

Enhance your migration strategy by categorizing tables into distinct lists based on their attributes. Create separate lists for:

Regular tables

XML-only tables

Identity-generated always tables

XML with generated always tables

This categorization empowers you to leverage different migration options based on the table characteristics. For instance, with regular tables, you can directly employ all tables, including partitions without XML columns, and generated always columns, using the db2move copy option.

Regular tables

XML-only tables

Identity-generated always tables

XML with generated always tables

Guided Command Walkthrough for migration using db2move:

1.     Stop connecting source database.

It's vital to recognize that the migration process outlined here doesn't operate within a real-time migration. Consequently, while undergoing this activity, it's recommended to temporarily suspend all applications that interact with the source database. This step ensures data consistency and accuracy during the migration process.

2.     Running db2move Silently: Executing in Nohup Mode for Uninterrupted Progress

Finally, initiate the db2move command in nohup mode, enabling it to perform its tasks seamlessly. Ensure to run this command to a specific schema using the load-only option, considering that the necessary objects have been established in preceding steps.

Relax and monitor the process until db2move completes its tasks.

Completing the Cycle: Essential Steps After Data Load

1.     Enforcing Check Constraints and Removing Tables from Integrity Pending State:

To wrap up the process, enforce the check constraints and remove the tables from the integrity pending state.

2.     Recreated drop indexes if dropped any

After the data load process, it's essential to assess whether any indexes were dropped before the load. If indexes were removed for optimization during the data load, it's now time to recreate them. This step ensures that the database maintains its performance efficiency and accessibility.

Conclusion

In conclusion, Numerous alternative methods exist, and naturally, Database Administrators (DBAs) often have their preferred tools and techniques. This blog aims to shed light on a approach by offering insights, rationale, and a structured method for utilizing db2move when the backup and restore option is not viable.

Contributors

Sumanth Madyalkar         - DB2 LUW Database Administrator/ Technical Architect

Sai ram Nadendla            - DB2 LUW Database Administrator

Bilal Abdullah                  - Data Architect/DBA

References

db2move - Database movement tool command

db2 Export

db2 import

LOAD command

How to import data from a DB2 database table (from a CSV file)