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:  

Connecting to and dropping databases

Now that you have created a database, we will see the process to connect to an existing database. For our example, we will connect to the Sample database that is created when DB2 is installed.

Connecting to a database

If this is the first time you are using Data Studio, the Sample database should not be in the Administration Explorer view. As you did with creating a new database, click on the drop-down arrow to the right of the New button. This time, select New Connection to a database. This will bring up the New Connection window.

On the left side of the window, you can see the many types of databases to which Data Studio can connect. This is because DB2 Connect is now packaged with IBM Data Studio.

Select DB2 for Linux, UNIX, and Windows from the list. Enter SAMPLE in the database field and similarly to when you created a database in the previous section, the default values for the host name and port should be localhost and 50000, respectively. Be sure to test your connection after you enter your username and password. If you do not wish to enter your password again, you may have Data Studio save it. After all the database connection information is entered and your ping to test the connection succeeds, click Finish. Just like when we created the MYNEWDB database, the SAMPLE database should appear in the Administration Explorer view.


Figure 8. New Database Connection Window
Screenshot shows close-up of the New Databse Connection Window

If the Sample database is already in the Administration Explorer view, all you need to do is double-click on the database. If you haven't saved your username and password, enter that and you will be connected.

To confirm that you are connected, you should see a list of folders coming from the database in the Administration Explorer view. These folders are the names of objects that make up DB2 databases. If you click on one, it will open a list of all objects of that type in the main work area. This list is called the List Editor.


Figure 9. Connected to the SAMPLE database
Screenshot shows Administration Explorer view and the folders confirming you are connected to the SAMPLE database

This is one of the few circumstances where Data Studio does not generate the command issued for those that use the command line. If connecting to the sample database, the command is CONNECT TO SAMPLE USER <USERNAME> USING <PASSWORD>;.

Entering your password this way will cause your password to be printed in the command-line window. This is an unadvisable practice for obvious security reasons. However, if you omit the USING clause, DB2 will immediately ask you to provide your password on the following line. No characters that are entered will show on the screen, and the cursor will not move until you press Enter.

CONNECT TO SAMPLE USER <USERNAME>;
    

If you are logged in to the operating system as the user, you may truncate the command even further to simply CONNECT TO SAMPLE;.


Disconnecting from a database

Disconnecting from a database is easy. For Data Studio, all you need to do is right-click on the database in the Administration Explorer view and select Disconnect. For the command line, simply enter TERMINATE;.


Figure 10. Disconnect option
Screenshot shows close-up of the Menu from right-clicking on the database in the Administration Explorer view

Of course, if you were to close the window, it will also sever your connection to the database.


Dropping a database

We will no longer be using the MYNEWDB database in any further examples, so we will now delete it. In Data Studio, before you can drop the database, you must first connect to it, then right-click on the database in the Administration Explorer view and select drop.


Figure 11. Drop option
Screenshot shows close-up of the Menu from right-clicking on the database in the Administration Explorer view

This will bring up a tab in the main work area that will look similar to the one used to create the database. Unlike that window, there are not options that you may adjust. If you preview the command again, you should see:

			    
CONNECT TO MYNEWDB;
UNQUIESCE DATABASE;
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
UNQUIESCE DATABASE;
CONNECT RESET;
DROP DATABASE MYNEWDB;
   

The point of the QUIESCE commands is to ensure that no other users are currently connected to the database. Since we have just created this database, we know that no on else is using it. If this is the case, in the command line, you only need to enter DROP DATABASE MYNEWDB;.

After you click Run and the command processes, the MYNEWDB database should no longer be visible in the Administration Explorer view. The MYNEWDB was removed completely from the disk.

4 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=