Before you start

About this series

This tutorial series teaches basic to advanced SQL and basic XQuery topics and shows how to express commonly asked business questions as database queries by using SQL queries or XQueries. Developers and database administrators can use this tutorial to enhance their database query skills. Academic Initiative members can use this tutorial series as a part of their database curriculum.

All the examples in this document are based on Aroma, a sample database that contains sales data for coffee and tea products sold in stores across the United States. Each example consists of three parts:

• A business question, expressed in everyday language
• One or more example queries, expressed in SQL or XQuery
• A table of results returned from the database

This guide is designed to allow participants to learn the SQL language and XQuery. As with any learning, it is important to supplement it with hands-on exercises. This is facilitated by the table definitions and data.

For students using this as part of an academic class, obtain from your instructor the instructions to connect to the Aroma database and learn about any differences between the guide and your local set up.

This tutorial was written for DB2 Express-C 9 for UNIX®, Linux® and Windows® (formerly known as Viper).

About this tutorial

This tutorial introduces participants to the Aroma database, especially the retail group of tables, table definitions, sample data, and the relationship between tables.

These tables were created to show an environment where:

• There are few tables
• Column names use the analyst's vocabulary
• Columns are reflective of the natural dimension of the business

This is a typical, small data warehouse using a star schema. The other type of database is Online Transaction Processing (OLTP). OLTP databases contain transacations at the most granular level.

This tutorial also introduces the basics of SQL and suggests some typical business questions that IBM DB2 9 can be used to answer.

System requirements

To use this tutorial to the fullest, you should have IBM DB2 9 installed. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edtion and provides a solid base to build and deploy applications. You will also need to install the Aroma database, which can be downloaded from here.

Installing DB2 Express-C 9

To install DB2 9, follow these steps (default options should be used unless otherwise specified):

1. Navigate to the folder containing the downloaded file - db2exc_viper_WIN_x86.zip (we will assume the file is downloaded to the C:\DownloadDirector folder).
2. Unzip the file.
3. Find setup.exe in the unzipped folder and double-click on it. The DB2 Setup Launchpad will appear.
4. Within the DB2 Setup Launchpad, click on Install a Product from the panel on the left.
5. Click on Install New under DB2 Express.
6. In the installer, click Next to view the License Agreement. You will need to Accept the agreement to continue and click Next.
7. Choose a Typical installation and click Next.
8. Confirm that you plan to "Install DB2 Universal Database Express Edition on this computer and save my settings in a response file" and click Next.
9. Verify the installation directory and click Next.
10. In the "Configure DB2 instances" screen, click Next.
11. In the "User Information" screen, set the Domain to None -- use local user account using the pull-down menu, use db2admin as the User name, and set a password.
    (Optionally, you can select the LocalSystem account, but there are limitations introduced if this option is used. Click the Help button to learn more.)
12. Make sure the checkbox Use the same user name and password for the remaining DB2 services is checked and click Next.
13. Click Next to skip the "Prepare the DB2 tools catalog" screen.
14. To simplify installation, uncheck Set up your DB2 server to send notifications and click Next.
15. Verify that the Enable operating system security checkbox is checked. Leave the DB2 administrators group as the default value DB2ADMNS and leave DB2 users group as the default value DB2USERS. Click Next.
16. Click Finish to start the DB2 product installation when you reach the "Start copying files and create response file" screen.
17. When the installation is complete, a window with the message "Setup is complete" will appear. Click Finish to complete the installation procedure.
18. A window entitled "DB2 First Steps" will now appear. Click Create profile. Your Web browser will open a page called DB2 First Steps. This tutorial contains many useful links to information on DB2, but it will not be used during this tutorial. Close your Web browser and continue with this tutorial.

Installing the Aroma database

1. Ensure DB2 Express-C 9 is installed.
2. Start the Command Window from the Start menu ==> all Programs ==> IBM DB2 ==> Command Line Tools ==> Command Window.
3. Execute create_aroma.bat in the Command Window.
   Note: Don't forget to move to the directory where the batch file is located using the cd command.
4. Two files, create.txt and report.txt, will be created. Edit report.txt and compare to the expected results below:
          TABLE                                          RECORDS
          ---------------------  -----------
          aroma.class                          9
          aroma.deal                            9
          aroma.line_items        182
          aroma.market                  17
          aroma.orders                    27
          aroma.period                    821
          aroma.product                59
          aroma.promotion        194
          aroma.sales                          69941
          aroma.store                        18
          aroma.supplier                9

XML support

XML and basic XQueries will be introduced in Part 7 of this series. To support XML, the aromadb database was created with the statement:

			db2 create db aromadb using codeset utf-8 territory us

Note the specification of UTF-8 as the codeset for the database. The use of XML features is restricted to a database that is defined with codeset UTF-8 and has only one database partition. When creating your own databases in the future, don't forget to include this parameter or your database won't support XML features!

1 of 12 | Next

Comments

Back to top

Table of contents

• Before you start
• Aroma database group of tables
• Basic Aroma tables
• What is SQL?
• Data types
• Ways to run SQL
• Getting started
• Metadata
• Summary
• Download
• Resources
• About the authors

---

1 of 12 | Next

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.