Skip to main content

By clicking Submit, you agree to the developerWorks terms of use.

The first time you sign into developerWorks, a profile is created for you. Select information in your developerWorks profile is displayed to the public, but you may edit the information at any time. Your first name, last name (unless you choose to hide them), and display name will accompany the content that you post.

All information submitted is secure.

  • Close [x]

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerworks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

By clicking Submit, you agree to the developerWorks terms of use.

All information submitted is secure.

  • Close [x]

DB2 10.1 fundamentals certification exam 610 prep, Part 3: Working with databases and database objects

Gerald Kupris (gkupris@us.ibm.com), Solution Migration Specialist, IBM
Gerald Kupris
Gerald A. Kupris is a DB2 migration specialist working in the IBM Information Management Technology Ecosystems team, of which he has been a member since 2010. He has worked with numerous partners and customers, providing DB2 knowledge transfer, implementation guidance, and assistance converting from competitive databases to DB2.

Summary:  This tutorial shows you the basic steps and requirements to create and connect to a database in DB2® 10.1. This tutorial also introduces you to the objects that make up a DB2 database, as well as how to create and manipulate them. This tutorial is third of a series of six "DB2 10.1 fundamentals certification exam 610 prep" tutorials that will help prepare you for the DB2 10.1 fundamentals certification exam 610.

View more content in this series

Date:  18 Oct 2012
Level:  Intermediate PDF:  A4 and Letter (1082 KB | 21 pages)Get Adobe® Reader®

Comments:  

Creating a database

Preparing to create 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
Screen capture shows 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
Screenshot shows IBM Data Studio when it is opened for the first time

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.


Creating a database

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
Screenshot shows the New Database option in the drop-down list for the New button

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
Image shows the Window asking you to specify the Instance where you want the database to be

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
Screenshot shows editable window to create the database

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 MYNEWDB.

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
Screenshot shows the checkmark in the SQL Results view stating that the command ran successfully

Figure 7. The newly created MYNEWDB database
Screenshot shows closeup of  Administration Explorer View

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.

3 of 8 | Previous | Next

Comments



static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=840784
TutorialTitle=DB2 10.1 fundamentals certification exam 610 prep, Part 3: Working with databases and database objects
publish-date=10182012
author1-email=gkupris@us.ibm.com
author1-email-cc=