{"id":84,"date":"2019-03-24T01:16:07","date_gmt":"2019-03-24T09:16:07","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=84"},"modified":"2020-06-30T00:15:46","modified_gmt":"2020-06-30T08:15:46","slug":"create-db2-sample-database","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/getting-started\/create-db2-sample-database\/","title":{"rendered":"Create Db2 Sample Database"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to create a new database in Db2 and load the sample database into the database server.<\/p>\n\n\n\n

Step 1. Launch the Db2 command window<\/h3>\n\n\n\n
\"Launch<\/figure>\n\n\n\n

Step 2. Create the Books database<\/h3>\n\n\n\n

From the DB2 command window, type db2<\/code> command, you will see the following command line:<\/p>\n\n\n

db2 =><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Use the CREATE DATABASE<\/code> statement to create the Books<\/code> database:<\/p>\n\n\n
db2 => CREATE<\/span> DATABASE<\/span> books<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It will take a while to create the database. Once, you see the following message:<\/p>\n\n\n
DB20000I  The CREATE<\/span> DATABASE<\/span> command completed successfully.<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It means you have successfully created a new database.<\/p>\n\n\n\n
To list all databases in the current instance, you use the list database directory<\/code> command:<\/p>\n\n\n
db2 => list database directory<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
System Database Directory\n\n Number<\/span> of<\/span> entries in<\/span> the directory = 2<\/span>\n\nDatabase 1<\/span> entry:\n\n Database alias                       = BOOKS\n Database name                        = BOOKS\n Local database directory             = C:\n Database release level               = 14.00<\/span>\n Comment                              =\n Directory entry type                 = Indirect\n Catalog database partition number    = 0<\/span>\n Alternate server hostname            =\n Alternate server port number         =\n\nDatabase 2<\/span> entry:\n\n Database alias                       = SAMPLE\n Database name                        = SAMPLE\n Local database directory             = C:\n Database release level               = 14.00<\/span>\n Comment                              =\n Directory entry type                 = Indirect\n Catalog database partition number    = 0<\/span>\n Alternate server hostname            =\n Alternate server port number         =<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
There are two databases. The SAMPLE<\/code> database that we created after installing DB2<\/a> database server and the Books<\/code> database that we have created.<\/p>\n\n\n\n
Step 3. Download Books sample database script files<\/h3>\n\n\n\n
Click the following link to download the Books<\/code> sample database script. You must extract the zip file into a directory e.g., D:\\bookdb<\/code><\/p>\n\n\n\n
Download the Books Sample Database<\/a><\/p>\n\n\n\n
There are three files:<\/p>\n\n\n\n
  1. create.sql for creating tables<\/li>
  2. data.sql for inserting data into the tables<\/li>
  3. drop.sql for deleting all tables<\/li><\/ol>\n\n\n\n
    Step 4. Load Books sample database<\/h3>\n\n\n\n
    First, use quit command to exit the session:<\/p>\n\n\n
    db2 => quit<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
    you are now back to the BIN directory.<\/p>\n\n\n\n
    Next, connect to the books<\/code> database using the db2admin<\/code> user:<\/p>\n\n\n
    > db2 connect to books user db2admin using password<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Note that you must replace the password to your password that you provided during installing the Db2 database server.<\/p>\n\n\n\n
    Then, use the following command to run the create.sql<\/code> script to create tables:<\/p>\n\n\n
    > db2 -stvf d:\\bookdb\\create.sql<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Verify if all commands completed successfully.<\/p>\n\n\n\n
    After that, use the following command to execute the data.sql script to insert data into the tables:<\/p>\n\n\n
    > db2 -stvf d:\\bookdb\\data.sql<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    It will take a while to complete all commands in the file.<\/p>\n\n\n\n
    Finally, use the following command to double check if data is loaded successfully:<\/p>\n\n\n
    >db2 select<\/span> count<\/span>(*) author_count from<\/span> authors<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    If you see the following output, then congratulation, you have successfully created the books database:<\/p>\n\n\n
    AUTHOR_COUNT\n------------\n        1388\n\n1 record(s) selected.<\/code><\/span><\/pre>\n\n\n
    In this tutorial, you have learned how to create the Books<\/code> sample database and execute the SQL script files to create tables and insert data into them.<\/p>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t