Unit 6: Moving Data

Demonstration 1: Moving data using the Db2 IMPORT, INGEST, and LOAD commands.

Introduction

This demonstration provides a comprehensive guide to moving data into Db2 tables using various methods including IMPORT, INGEST, and LOAD commands. You will also use the SET INTEGRITY statement to resolve constraint checking after using a LOAD command.

Moving data into Db2 tables involves several steps and best practices to ensure data integrity and efficient loading processes.

Task 1: Use the IMPORT utility to load data into a table

In this task, you will use the IMPORT utility to load data from a delimited file into a Db2 table.

  1. Logon to the Linux system:

    Logon to the Linux system using the user id inst23, with a password of ibm2blue.

    ssh inst23@<hostname>
  2. Open a Terminal Session:

    Right-click the empty Linux desktop and select Open in Terminal.

  3. Change to the DDL Directory:

    A set of course files are located in the directory $HOME/ddl. Change to this directory to make it easier to access these files that contain Db2 commands or SQL statements.

    cd $HOME/ddl
  4. Run the IMPORT Command:

    Use the IMPORT utility to load data from the file $HOME/artists.del into the ARTISTS table.

    db2 connect to musicdb
    db2 IMPORT from $HOME/artists.del of del insert into music.artists

Ensure that the input file artists.del exists in the specified directory and contains valid data.

By completing this task, you have successfully loaded data into the ARTISTS table using the IMPORT utility.

Task 2: Use the INGEST command to load data into a Db2 table

In this task, you will use the INGEST command to efficiently load data from a file into a Db2 table.

  1. Create the INGEST Restart Table:

    Create the SYSTOOLS.INGESTRESTART table using the cr_toolspace.ddl file.

    cd $HOME/ddl
    db2 connect to musicdb
    db2 -tvf cr_toolspace.ddl
  2. Run the INGEST Command:

    Use the INGEST command to load data from the file /home/inst23/albums.del into the ALBUMS table.

    db2 -tvf ingest_albums.ddl

The INGEST command is useful for high-speed data loading and can handle large volumes of data efficiently.

By completing this task, you have successfully loaded data into the ALBUMS table using the INGEST command.

Task 3: Use the Db2 LOAD utility to load data into a table that has a foreign key constraint defined

In this task, you will use the Db2 LOAD utility to add data to the STOCK table and resolve integrity constraints using the SET INTEGRITY command.

  1. Run the LOAD Command:

    Use the LOAD command in the file load_stock1.ddl to load data into the STOCK table.

    cd $HOME/ddl
    db2 connect to musicdb
    db2 -tvf load_stock1.ddl
  2. Run the SET INTEGRITY Command:

    Use the set_integrity_stock.sql file to run SET INTEGRITY and resolve any pending integrity constraints.

    db2 -tvf set_integrity_stock.sql

Ensure that all referential and check constraints are correctly defined and that the data being loaded complies with these constraints.

By completing this task, you have successfully loaded data into the STOCK table and resolved integrity constraints using the SET INTEGRITY command.

Task 4: Run a file containing a series of LOAD commands and SET INTEGRITY statements

In this task, you will periodically refresh or extend tables with new data using a command script that contains LOAD commands and SET INTEGRITY statements.

Option 1: Use the Db2 command line processor

  1. Create Exception Tables:

    Run the create_exception_tables.ddl script to create exception tables for ARTISTS and ALBUMS.

    cd $HOME/ddl
    db2 connect to musicdb
    db2 -tvf create_exception_tables.ddl
  2. Run the LOAD and SET INTEGRITY Commands:

    Use the load_tables_clp.ddl script to load data into multiple tables and resolve integrity constraints.

    db2 -tvf load_tables_clp.ddl

Option 2: Use the Data Server Manager tool

  1. Create Exception Tables:

    Start DSM, select the MUSICDB database, and run the create_exception_tables.ddl script.

    Script > Open from Client > Browse > create_exception_tables.ddl
  2. Run the LOAD and SET INTEGRITY Commands:

    Use DSM to run the load_tables.sql script.

    Script > Open from Client > Browse > load_tables.sql

Using exception tables allows the LOAD utility to complete processing even if there are exceptions to table constraints.

By completing this task, you have successfully loaded data into multiple tables and resolved integrity constraints using a command script.

Conclusion

This demonstration has provided a comprehensive guide to moving data into Db2 tables using various methods. By following the tasks and best practices outlined, you have gained hands-on experience with Db2 utilities and commands, enhancing your data management skills.