DB2 Tutorial

  • Home
  • Start Here
  • Basics
  • Views
  • Triggers
  • Indexes
  • Functions
    • Aggregate Functions
    • Date Functions
    • String Functions
    • Window Functions
Home » Getting Started with Db2 » Create Db2 Sample Database

Create Db2 Sample Database

Summary: in this tutorial, you will learn how to create a new database in Db2 and load the sample database into the database server.

Step 1. Launch the Db2 command window

Launch DB2 Command Window

Step 2. Create the Books database

From the DB2 command window, type db2 command, you will see the following command line:

db2 =>
Code language: PHP (php)

Use the CREATE DATABASE statement to create the Books database:

db2 => CREATE DATABASE books
Code language: SQL (Structured Query Language) (sql)

It will take a while to create the database. Once, you see the following message:

DB20000I The CREATE DATABASE command completed successfully.
Code language: SQL (Structured Query Language) (sql)

It means you have successfully created a new database.

To list all databases in the current instance, you use the list database directory command:

db2 => list database directory
Code language: SQL (Structured Query Language) (sql)

Here is the output:

System Database Directory Number of entries in the directory = 2 Database 1 entry: Database alias = BOOKS Database name = BOOKS Local database directory = C: Database release level = 14.00 Comment = Directory entry type = Indirect Catalog database partition number = 0 Alternate server hostname = Alternate server port number = Database 2 entry: Database alias = SAMPLE Database name = SAMPLE Local database directory = C: Database release level = 14.00 Comment = Directory entry type = Indirect Catalog database partition number = 0 Alternate server hostname = Alternate server port number =
Code language: JavaScript (javascript)

There are two databases. The SAMPLE database that we created after installing DB2 database server and the Books database that we have created.

Step 3. Download Books sample database script files

Click the following link to download the Books sample database script. You must extract the zip file into a directory e.g., D:\bookdb

Download the Books Sample Database

There are three files:

  1. create.sql for creating tables
  2. data.sql for inserting data into the tables
  3. drop.sql for deleting all tables

Step 4. Load Books sample database

First, use quit command to exit the session:

db2 => quit
Code language: PHP (php)

you are now back to the BIN directory.

Next, connect to the books database using the db2admin user:

> db2 connect to books user db2admin using password
Code language: SQL (Structured Query Language) (sql)

Note that you must replace the password to your password that you provided during installing the Db2 database server.

Then, use the following command to run the create.sql script to create tables:

> db2 -stvf d:\bookdb\create.sql
Code language: SQL (Structured Query Language) (sql)

Verify if all commands completed successfully.

After that, use the following command to execute the data.sql script to insert data into the tables:

> db2 -stvf d:\bookdb\data.sql
Code language: SQL (Structured Query Language) (sql)

It will take a while to complete all commands in the file.

Finally, use the following command to double check if data is loaded successfully:

>db2 select count(*) author_count from authors
Code language: SQL (Structured Query Language) (sql)

If you see the following output, then congratulation, you have successfully created the books database:

AUTHOR_COUNT ------------ 1388 1 record(s) selected.

In this tutorial, you have learned how to create the Books sample database and execute the SQL script files to create tables and insert data into them.

  • Was this tutorial helpful ?
  • YesNo
Previous DB2 Sample Database
Next Connecting to a Database in Db2 Database Server

Getting Started

  • What is Db2
  • Installing Db2 Database Server
  • Db2 Sample Database
  • Creating a Db2 Sample Database
  • Connecting to a Db2 Database
  • Interacting with Db2 using SQL Developer

Data Manipulation

  • SELECT
  • ORDER BY
  • WHERE
  • SELECT DISTINCT
  • AND
  • OR
  • BETWEEN
  • LIKE
  • IN
  • LIMIT
  • FETCH
  • Join
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • Self-Join
  • CROSS JOIN
  • GROUP BY
  • Subquery
  • HAVING
  • UNION
  • INTERSECT
  • EXCEPT
  • Common Table Expression or CTE
  • INSERT
  • INSERT Multiple Rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE

Managing Database Objects

  • CREATE TABLE
  • Identity Columns
  • ALTER COLUMN
  • ADD COLUMN
  • DROP COLUMN
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME TABLE

Db2 Constraints

  • Primary Key
  • Foreign Key
  • DEFAULT
  • NOT NULL
  • CHECK
  • UNIQUE

Db2 Data Types

  • Integer
  • Decimal
  • VARCHAR
  • CHAR
  • DATE
  • TIME
  • TIMESTAMP

Useful Functions & Expressions

  • CAST
  • CASE Expression
  • COALESCE

About db2tutorial.com

The db2tutorial.com website provides you with a comprehensive IBM DB2 tutorial with many practical examples and hands-on sessions.

Recent Tutorials

  • Db2 Functions
  • Db2 RANK
  • Db2 ROW_NUMBER
  • Db2 Window Functions
  • What is Db2

Site Links

  • Home
  • About Us
  • Contact Us
  • Privacy Policy
Copyright © © 2021 by www.db2tutorial.com. All Rights Reserved.