Section 4.10. Case Study

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

Figure 4.47. Inserting a row into a table

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.

Figure 4.48. Activity Monitor summary page

Figure 4.49. Obtaining an Activity Monitor report

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 SQLstatements in the cache with the largest number of rows read.

Figure 4.50. The Activity Monitor report

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!