Unit 5: Creating Database Objects

Demonstration 1: Creating database objects using the Data Server Manager and Db2 command line processor.

Introduction

This demonstration provides a comprehensive guide to creating database objects in the Db2 database named MUSICDB. You will learn to create tables, indexes, views, aliases, constraints, and triggers using the Data Server Manager and Db2 command line processor.

This process involves several critical steps and best practices to ensure effective database object management and configuration.

Task 1: Logon to the Linux system and start a terminal session

In this task, you will log on to the Linux system, start a terminal session, and prepare to run Db2 commands.

  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
    db2 connect to musicdb

Ensure that you have the necessary permissions to access and modify the files in the $HOME/ddl directory.

By completing this task, you have successfully logged on to the Linux system, opened a terminal session, and navigated to the directory containing the necessary files for the demonstration.

Task 2: Create the ALBUMS table

In this task, you will create the ALBUMS table in the MUSICDB database using either the Db2 command line processor or the Data Server Manager tool.

Option 1: Use the Db2 command line processor

  1. Use the file $HOME/ddl/create_table_albums.ddl to create the ALBUMS table with a Primary key defined on the ITEMNO column.
  2. Enter the following commands using the Linux terminal session:
    db2 -tvf create_table_albums.ddl
    db2 describe table music.albums

Using the command line processor allows for quick and efficient execution of SQL scripts and commands.

Option 2: Use the Data Server Manager tool

  1. Open a Firefox Browser and select Bookmarks > Bookmarks Toolbar > Log in: IBM Data Server Manager or type the following URL: http://ibmclass:11080.
  2. Log in using the user id db2admin and password ibm2blue.
  3. Select Administer > Schemas and create a new schema named MUSIC.
  4. Select Administer > Tables and create a new table named ALBUMS in the schema MUSIC with the following properties:
    • Table space: TSP04
    • Index table space: TSP05

    Define the columns as follows:

    • TITLE: Varchar(50)
    • ARTNO: Smallint, Not Null
    • ITEMNO: Smallint, Not Null

    Define a primary key constraint on the ITEMNO column.

Ensure that all column definitions and constraints are accurately specified to avoid errors during table creation.

By completing this task, you have successfully created the ALBUMS table in the MUSICDB database using either the command line processor or the Data Server Manager tool.

Task 3: Create a set of new tables using a SQL file

In this task, you will create a group of tables in the MUSICDB database using a SQL file containing the CREATE TABLE statements.

Option 1: Use the Db2 command line processor

  1. The file $HOME/ddl/create_tables.ddl contains five CREATE TABLE statements.
  2. Enter the following commands using the Linux terminal session:
    cd $HOME/ddl
    db2 connect to musicdb
    db2 -tvf create_tables.ddl | more
    db2 list tables for schema music

Using the command line processor allows for quick execution of multiple SQL statements contained in a file.

Option 2: Use the Data Server Manager tool

  1. Start DSM if not already started and select the MUSICDB database from the database list at the top. On the left, click Run SQL.
  2. Click Script > Open from Client. Use the Browse button to locate and select the file inst23/ddl/create_tables.ddl.
  3. Click Open, then OK to complete loading the SQL text into the SQL editor.
  4. Click Run > Run All and wait for the SQL statements to be processed.

Ensure that all CREATE TABLE statements are correctly specified and that the necessary table spaces are available.

By completing this task, you have successfully created a group of tables in the MUSICDB database using either the command line processor or the Data Server Manager tool.

Task 4: Create index, view, and alias objects for the application tables

In this task, you will create an index on the STOCK table and use SQL statements to create view and alias objects.

Option 1: Use the Db2 command line processor

  1. Enter the following commands using the Linux terminal session:
    cd $HOME/ddl
    db2 connect to musicdb
    db2 -tvf create_stock_ix.ddl
    db2 describe indexes for table music.stock
    db2 -tvf create_VIEW_ALIAS.ddl
    db2 list tables for schema music

Option 2: Use the Data Server Manager tool

  1. Select Administer > Indexes from the options on the left. Click Create to define a new index on the MUSIC.STOCK table.
  2. Enter the following values for the index:
    • Name: STOCKITEM_IX
    • Members: select Add, then select the ITEMNO column

    Click on the (+) next to the Command and click Run.

  3. Click Run SQL from the options on the left. Clear any SQL statements from the editor, then click Script > Open from Client and select the file inst23/ddl/create_VIEW_ALIAS.ddl.
  4. Click Run > Run All to execute the SQL statements.

Ensure that the SQL statements for creating views and aliases are correctly specified to avoid errors during execution.

By completing this task, you have successfully created index, view, and alias objects for the application tables in the MUSICDB database.

Task 5: Create several table constraints and a trigger

In this task, you will create foreign key constraints, check constraints, and a trigger for the tables in the MUSICDB database.

Option 1: Use the Db2 command line processor

  1. The file $HOME/ddl/create_ri_cc.ddl contains statements to create foreign key and check constraints.
  2. The file $HOME/ddl/create_trigger.ddl contains statements to create a trigger.
  3. Enter the following commands using the Linux terminal session:
    cd $HOME/ddl
    db2 connect to musicdb
    db2 -tvf create_ri_cc.ddl
    db2 -tvf create_trigger.ddl

Option 2: Use the Data Server Manager tool

  1. Click Run SQL from the options on the left. Clear any SQL statements from the editor, then click Script > Open from Client and select the file inst23/ddl/create_ri_cc.ddl.
  2. Click Run > Run All to execute the SQL statements.
  3. Repeat the steps to open and execute the file inst23/ddl/create_trigger.ddl.

Ensure that all constraint definitions and trigger logic are correctly specified to maintain data integrity and proper functionality.

By completing this task, you have successfully created the necessary table constraints and a trigger in the MUSICDB database.

Task 6: Use the db2look command line tool to generate the DDL statements to create database objects

In this task, you will use the db2look tool to extract selected DDL statements from the MUSICDB database.

  1. Enter the following commands using the Linux terminal session to generate the DDL associated with the ALBUMS table and save the output in a file named ALBUMS.DDL:
    cd $HOME/ddl
    db2look -d musicdb -e -z music -t albums -o ALBUMS.DDL
    more ALBUMS.DDL

The db2look tool is useful for generating DDL scripts that can be used for database migration or backup purposes.

By completing this task, you have successfully used the db2look tool to generate DDL statements for database objects in the MUSICDB database.

This demonstration has provided a comprehensive guide to creating and managing database objects in the Db2 database. By following the tasks and best practices outlined, you have gained hands-on experience with various Db2 tools and commands, enhancing your database administration skills.

Unit 5: Creating Database Objects - Demonstration

Unit 5: Creating Database Objects

Demonstration 1: Creating database objects using the Data Server Manager and Db2 command line processor.

Introduction

This demonstration provides a comprehensive guide to creating database objects in the Db2 database named MUSICDB. You will learn to create tables, indexes, views, aliases, constraints, and triggers using the Data Server Manager and Db2 command line processor.

This process involves several critical steps and best practices to ensure effective database object management and configuration.

Task 1: Logon to the Linux system and start a terminal session

In this task, you will log on to the Linux system, start a terminal session, and prepare to run Db2 commands.

  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
    db2 connect to musicdb

Ensure that you have the necessary permissions to access and modify the files in the $HOME/ddl directory.

By completing this task, you have successfully logged on to the Linux system, opened a terminal session, and navigated to the directory containing the necessary files for the demonstration.

Task 2: Create the ALBUMS table

In this task, you will create the ALBUMS table in the MUSICDB database using either the Db2 command line processor or the Data Server Manager tool.

Option 1: Use the Db2 command line processor

  1. Use the file $HOME/ddl/create_table_albums.ddl to create the ALBUMS table with a Primary key defined on the ITEMNO column.
  2. Enter the following commands using the Linux terminal session:
    db2 -tvf create_table_albums.ddl
    db2 describe table music.albums

Using the command line processor allows for quick and efficient execution of SQL scripts and commands.

Option 2: Use the Data Server Manager tool

  1. Open a Firefox Browser and select Bookmarks > Bookmarks Toolbar > Log in: IBM Data Server Manager or type the following URL: http://ibmclass:11080.
  2. Log in using the user id db2admin and password ibm2blue.
  3. Select Administer > Schemas and create a new schema named MUSIC.
  4. Select Administer > Tables and create a new table named ALBUMS in the schema MUSIC with the following properties:
    • Table space: TSP04
    • Index table space: TSP05

    Define the columns as follows:

    • TITLE: Varchar(50)
    • ARTNO: Smallint, Not Null
    • ITEMNO: Smallint, Not Null

    Define a primary key constraint on the ITEMNO column.

Ensure that all column definitions and constraints are accurately specified to avoid errors during table creation.

By completing this task, you have successfully created the ALBUMS table in the MUSICDB database using either the command line processor or the Data Server Manager tool.

Task 3: Create a set of new tables using a SQL file

In this task, you will create a group of tables in the MUSICDB database using a SQL file containing the CREATE TABLE statements.

Option 1: Use the Db2 command line processor

  1. The file $HOME/ddl/create_tables.ddl contains five CREATE TABLE statements.
  2. Enter the following commands using the Linux terminal session:
    cd $HOME/ddl
    db2 connect to musicdb
    db2 -tvf create_tables.ddl | more
    db2 list tables for schema music

Using the command line processor allows for quick execution of multiple SQL statements contained in a file.

Option 2: Use the Data Server Manager tool

  1. Start DSM if not already started and select the MUSICDB database from the database list at the top. On the left, click Run SQL.
  2. Click Script > Open from Client. Use the Browse button to locate and select the file inst23/ddl/create_tables.ddl.
  3. Click Open, then OK to complete loading the SQL text into the SQL editor.
  4. Click Run > Run All and wait for the SQL statements to be processed.

Ensure that all CREATE TABLE statements are correctly specified and that the necessary table spaces are available.

By completing this task, you have successfully created a group of tables in the MUSICDB database using either the command line processor or the Data Server Manager tool.

Task 4: Create index, view, and alias objects for the application tables

In this task, you will create an index on the STOCK table and use SQL statements to create view and alias objects.

Option 1: Use the Db2 command line processor

  1. Enter the following commands using the Linux terminal session:
    cd $HOME/ddl
    db2 connect to musicdb
    db2 -tvf create_stock_ix.ddl
    db2 describe indexes for table music.stock
    db2 -tvf create_VIEW_ALIAS.ddl
    db2 list tables for schema music

Option 2: Use the Data Server Manager tool

  1. Select Administer > Indexes from the options on the left. Click Create to define a new index on the MUSIC.STOCK table.
  2. Enter the following values for the index:
    • Name: STOCKITEM_IX
    • Members: select Add, then select the ITEMNO column

    Click on the (+) next to the Command and click Run.

  3. Click Run SQL from the options on the left. Clear any SQL statements from the editor, then click Script > Open from Client and select the file inst23/ddl/create_VIEW_ALIAS.ddl.
  4. Click Run > Run All to execute the SQL statements.

Ensure that the SQL statements for creating views and aliases are correctly specified to avoid errors during execution.

By completing this task, you have successfully created index, view, and alias objects for the application tables in the MUSICDB database.

Task 5: Create several table constraints and a trigger

In this task, you will create foreign key constraints, check constraints, and a trigger for the tables in the MUSICDB database.

Option 1: Use the Db2 command line processor

  1. The file $HOME/ddl/create_ri_cc.ddl contains statements to create foreign key and check constraints.
  2. The file $HOME/ddl/create_trigger.ddl contains statements to create a trigger.
  3. Enter the following commands using the Linux terminal session:
    cd $HOME/ddl
    db2 connect to musicdb
    db2 -tvf create_ri_cc.ddl
    db2 -tvf create_trigger.ddl

Option 2: Use the Data Server Manager tool

  1. Click Run SQL from the options on the left. Clear any SQL statements from the editor, then click Script > Open from Client and select the file inst23/ddl/create_ri_cc.ddl.
  2. Click Run > Run All to execute the SQL statements.
  3. Repeat the steps to open and execute the file inst23/ddl/create_trigger.ddl.

Ensure that all constraint definitions and trigger logic are correctly specified to maintain data integrity and proper functionality.

By completing this task, you have successfully created the necessary table constraints and a trigger in the MUSICDB database.

Task 6: Use the db2look command line tool to generate the DDL statements to create database objects

In this task, you will use the db2look tool to extract selected DDL statements from the MUSICDB database.

  1. Enter the following commands using the Linux terminal session to generate the DDL associated with the ALBUMS table and save the output in a file named ALBUMS.DDL:
    cd $HOME/ddl
    db2look -d musicdb -e -z music -t albums -o ALBUMS.DDL
    more ALBUMS.DDL

The db2look tool is useful for generating DDL scripts that can be used for database migration or backup purposes.

By completing this task, you have successfully used the db2look tool to generate DDL statements for database objects in the MUSICDB database.

This demonstration has provided a comprehensive guide to creating and managing database objects in the Db2 database. By following the tasks and best practices outlined, you have gained hands-on experience with various Db2 tools and commands, enhancing your database administration skills.