Business Intelligence Tutorial

Lesson 2. Creating a warehouse database

In this lesson, you will create the database for your warehouse and register the database with ODBC.

As part of DB2 First Steps, you had DB2 create the DWCTBC database, which contains the source data for this tutorial.

In this lesson, you will create the database that is to contain a version of the source data that is transformed for the warehouse. In Lesson 3, Browsing the source data, you learn how to view the source data. The rest of the tutorial teaches you how to transform that data and work with your warehouse database.

In this lesson, you will also learn how to register your database with Open Database Connectivity (ODBC), which allows tools like Lotus^(R) Approach and Microsoft Access to work with your warehouse.

Creating a database

In this exercise, you will use the Create Database wizard to create the TUTWHS database for your warehouse.

To create the database:

1. Start the DB2 Control Center:
   • On Windows NT, click Start --> Programs -->IBM DB2 --> Control Center.
   • On AIX or Sun Solaris, type the following command:

     db2jstrt 6790
     db2cc 6790b
     

2. Right-click the Databases folder, and select Create --> Database Using Wizard. The Create Database wizard opens.
3. In the Database name field, type the name of the database:

   TUTWHS
   

4. From the Default drive list, select a drive for the database.
5. In the Comments field, type a description of the database:

   Tutorial warehouse database
   

   
   
   

6. Click Finish. All other fields and pages in this wizard are optional. The TUTWHS database is created and is listed in the DB2 Control Center.

Registering a database with ODBC

There are several ways that you can register a database with ODBC. You can use the Client Configuration Assistant on Windows NT, the Command Line Processor, or the ODBC32 Data Source Administrator on Windows NT. In this exercise, you will use the Client Configuration Assistant.

For more information about the Command Line Processor, see the DB2 Universal Database Command Reference. For more information about the ODBC32 Data Source Administrator, see the online help in the Administrator.

To register the TUTWHS database with ODBC:

1. Start the Client Configuration Assistant by clicking Start --> Programs --> DB2 for Windows NT --> Client Configuration Assistant. The Client Configuration Assistant window opens.
2. Select TUTWHS from the list of databases.
   
   
3. Click Properties. The Database Properties window opens.
4. Select Register this database for ODBC. Use the default selection of As a system data source, which means that the data is available to all users on the system.
   
   
5. Click OK. All other fields are optional. The TUTWHS database is registered with ODBC.

The Properties and Settings push buttons in the Client Configuration Assistant window are used to optimize your ODBC connections and configuration. You do not need to adjust these properties or settings for the tutorial, but there is online help available if you need to work with them in your daily environment.

Connecting to the target database

Before you use the database that you defined, you must verify that you can connect to the database.

To connect to the database:

1. From the DB2 Control Center, expand the tree until you see the TUTWHS database.
2. Right-click the name of the database and click Connect.

   The Connect window opens. It displays the user ID and password that you used to log on to the DB2 Control Center.

   
   
   

3. Click OK.

   The DB2 Control Center connects to the database.

What you just did

In this lesson, you created the TUTWHS database to contain the data for the warehouse. Then, you registered the database with ODBC. Finally, you verified that you can connect to the database. In the next lesson, you will view the source data that you will later transform and store in the database that you just created.