Creating a database
In this section, you'll learn the basic steps involved in creating and connecting to a database. This tutorial will show you how to do so using IBM Data Studio 3.1.1 and will assume you have just installed it and are using it for the first time. DB2 also allows you to create and manage database objects from a command line. If you want to use the command line to interact with DB2, we will show you the commands that Data Studio generates along each step of the way.
After DB2 is installed, you can easily access the DB2 command window by
opening a Windows command prompt and typing
db2cmd. In Linux, you may run the commands that follow in a terminal. If you
open and use the command line in this manner, the commands below must
be preceded by
db2, a space, then the command shown.
Before you can create a database, you must have either SYSADM or SYSCTRL authority. For more information about these authorities, please refer to Part 2 of this series (see Resources). If you have just installed a copy of DB2 to go through this tutorial, you already will have SYSADM authority.
IBM Data Studio is an Eclipse-based environment. If you have used Eclipse or other Eclipse-based environments, you should feel comfortable. In preparation for creating a database, open IBM Data Studio. When it opens, you will see a window asking you to select a workspace.
Figure 1. Select workspace window
This location identifies the default location of all the files you create in Data Studio. Also, it creates a .metadata folder containing information about all your preferences and your database connections. If you relaunch Data Studio after using it for a while and select a different location for your workspace, you will no longer see any database connections.
When you click Ok, Data Studio should open in the Database Administration Perspective. This can be confirmed by looking at the tab in the upper-right corner of the window, below the menu bar. Looking around the window, there are many panes along the left and bottom. In Data Studio, these are called views. Primarily, you will use the Administration Explorer view, which will be on the left side of the window as well as the Properties and SQL Results view, which are tabs on the pane that is across most of the bottom of the window.
Figure 2. IBM Data Studio upon opening
Perspectives in Data Studio provide groups of tools and other functions based on the tasks you are trying to accomplish. In our case, it is Database Administration. For more detailed information about using Data Studio and its features, please see the Data Studio Information Roadmap provided in the Resources section.
To create a database, locate the Administration Explorer view. At the top, middle of that view, there is the New button. Click on the drop-down arrow on the right side of it and select New Database.
Figure 3. New Database Option from the drop-down list
The new database window will open, asking you to specify the instance where the database will be located. Assuming you are running DB2 on your computer, the default values for the host name and port should be localhost and 50000, respectively. The Instance name, username, and password were set when DB2 was installed. The default instance name in Windows is DB2, while in Linux/UNIX it is db2inst1. You should test the connection prior to clicking Finish to be sure that you entered the information correctly.
Figure 4. Specify Instance Window
In the Administration Explorer view, the hostname of the instance will appear as a server under the "All Databases" folder. Also, a new tab will open in your main work area to enter information about your new database.
Figure 5. Create Database Tab in the workspace
The very least you must do to create a new database is enter the
name you would like to call the database. For this example, enter
To see the command Data Studio issues, click Preview Command next to the Run button. This will expand the Command section of the tab. If you have been following along, this section should have the following command:
CREATE DATABASE MYNEWDB AUTOMATIC STORAGE YES;
This also is the command you can issue in the command line to
create a database named MYNEWDB. Any changes you make prior to
running the command will automatically update this section. For
example: If you were to change the default buffer pool and tablespace
page size option from its default of 4 KB to 8 KB, you will see the
command change to:
CREATE DATABASE MYNEWDB
AUTOMATIC STORAGE YES PAGESIZE 8 K;.
There are many more options available for creating database. To see all these options, see the CREATE DATABASE command page of the DB2 10.1 Information Center.
To create the database, click Run.
When the command runs, the SQL Results view on the pane at the bottom of the window should be visible. Assuming all information was entered correctly, the status column should have a blue checkmark and say Success. Also, the database name should now be visible in the Administration Explorer view to the right of the window.
Figure 6. Success in the SQL Results View
Figure 7. The newly created MYNEWDB database
Congratulations! You just created a database in DB2 10.1. To create more, you may continue to alter the information under the New Database tab and click the Run button again. Each database must have its own unique name.