Learning Visually with Examples [Electronic resources] نسخه متنی

4.10. Case Study

You recently installed the Try and Buy version of DB2 Personal Edition on your Windows laptop. During the installation, the

DB2 instance was created. Now you want to start using DB2 by creating a database. Since you are new to DB2, you decide to use the DB2 First Steps tool (see Figure 4.40).

You click on

Create Sample Database from the First Steps Launchpad. After the

sample database is created, you click on

Work with Databases , which launches the Control Center.

From the Control Center, choose the

Advanced view and then expand the

Object Tree to display the

sample database and all its database objects (see Figure 4.25).

The

sample database already has a set of tables defined in it. However, you decide to create a table of your own. To do so, you right-click on the Tables folder in the

Object Tree and choose

Create. This launches the Create Table Wizard, which will guide you through the process of creating a table. You are presented with the following pages.

• Identify the schema and name for the new table: In the

  Table schema field, enter the user ID you logged in with. We will discuss the significance of schemas in Chapter 7, Working with Database Objects; for now it is sufficient to enter your user ID. In the

  Table name field, enter the name of the table you want to create, say

  Table1 .

• Change column definitions for the new table: Click

  Add to add columns one at a time. In the

  Column name field, enter the name of the first column for

  Table1 , for example,

  Col1 . Choose the data type from the pull-down menu, say

  INTEGER . You could create more columns by repeating this step, but one column is sufficient for now.

There are other windows in which you can define the properties of other database objects. However, completing the above two windows is enough to create the table. Click on the

Finish button to create the table.

Table1 is displayed under the Tables folder once it is created (all tables are displayed in the Contents pane of the Control Centersee Figure 4.25). To display the contents of the table, right-click on the table name and choose

Open . Since nothing has been inserted into

Table1 , no contents are displayed. To insert a row into the table, click on the

Add Row button and enter a value under

Col1 . Click the Commit button to apply the changes (see Figure 4.47).

Your colleague, who is a DB2 expert, drops by your office and offers his help for any problems you may have. You tell him you would like to explore the Activity Monitor. He says you first need to turn on the DFT_MON_STMT database manager parameter to start collecting information that will be displayed by the Activity Monitor (database manager parameters are discussed in detail in Chapter 5, Understanding the DB2 Environment, DB2 Instances, and Databases). He quickly turns this on for you.

Next, you start the Activity Monitor and choose the

sample database in the

Introduction page. You see the four predefined functions of the Activity Monitor (see Figure 4.34). You decide to monitor dynamic SQL statements performed on this database. Therefore, you highlight the

Tuning the dynamic

SQL statement cache option and click

Next .

Figure 4.48 lists the SQL statement items that the Activity Monitor checks. Click

Finish to complete the Activity Monitor definition and you see the window shown in Figure 4.49.

To obtain the Activity Monitor report, select one of the four options listed. The Activity Monitor starts monitoring all dynamic SQL statements being executed on the database. If there are users connected to the database, all the dynamic SQL statements they submit will be monitored. Figure 4.50 shows an Activity Monitor report on

Dynamic

SQL statements in the cache with the largest number of rows read.

If there are no other users connected to the system, you can generate some SQL statements yourself. The simplest way to do that from the Control Center is to right-click on a table name and choose

Open to display its contents. This is equivalent to the following SQL statement:

SELECT * FROM  
table_name 

Though you have not finished exploring all the DB2 tools, this exercise has made you realize how easy to use and powerful they are!