Unit 4 - Demonstration 1: Creating databases and data placement

Introduction

In this demonstration, you will learn how to create and manage a Db2 database. This includes setting up the environment, creating databases, configuring storage groups and tablespaces, and using Db2 commands to retrieve and display database information.

Task 1: Create the MUSICDB Database
  1. Connect to the Db2 Instance: Open your terminal and connect to the Db2 instance.
    db2 connect to db2inst1
  2. Create the MUSICDB Database: Run the following command to create the database.
    db2 create database MUSICDB
  3. Verify Database Creation: Ensure the database has been created successfully.
    db2 list database directory
Task 2: Configure a Database Connection
  1. Access Data Server Manager: Open Firefox and navigate to http://ibmclass:11080.
    Use the credentials: Username: db2admin, Password: ibm2blue
  2. Configure Connection: Add a new database connection with the following details:
    • Database connection name: MUSICDB
    • Database name: MUSICDB
    • Host name: ibmclass
    • Port number: 50004
  3. Test the Connection: Test the connection to ensure it is working properly.
Task 3: Update Database Configuration
  1. Connect to the Database: Open a terminal session and connect to MUSICDB.
    db2 connect to MUSICDB
  2. Update Configuration: Update the number of primary and secondary log files.
    db2 update db cfg for MUSICDB using logprimary 5 logsecond 10
  3. Verify Configuration: Verify the updated configuration settings.
    db2 get db cfg for MUSICDB
Task 4: Create a Storage Group
  1. Create Storage Group: Create a new storage group for the database.
    db2 "CREATE STOGROUP app_data ON '/dbauto/path2'"
  2. Verify Storage Group: Verify the storage group creation.
    db2pd -db MUSICDB -storage
Task 5: Create a New Tablespace
  1. Navigate to the DDL Directory: Change to the directory containing the DDL script.
    cd $HOME/ddl
  2. Execute the DDL Script: Run the DDL script to create a new tablespace.
    db2 -tvf create_tablespace_tsp01.ddl
  3. Verify Tablespace: Verify the new tablespace creation.
    db2pd -db MUSICDB -tablespaces
Task 6: Create Multiple Tablespaces
  1. Navigate to the SQL File Directory: Change to the directory containing the SQL file.
    cd $HOME/ddl
  2. Execute the SQL Script: Run the SQL file to create multiple tablespaces.
    db2 -tvf create_tablespaces.ddl
  3. Verify Tablespaces: Verify the creation of multiple tablespaces.
    db2 list tablespaces
Task 7: Retrieve Tablespace Information
  1. Execute SQL Query: Run the SQL script to retrieve tablespace information.
    db2 -tvf select_tablespaces.sql
  2. Additional Information: Execute another script to get disk path details.
    db2 -tvf dbpaths.sql
  3. List Storage Groups: Use the db2pd command to list storage groups.
    db2pd -db MUSICDB -storage