Case Study 2
Using the SAMPLE database you created in the First Steps, you now explore the IBM Data Studio graphical interface. Launch the IBM Data Studio full client. Open the Database Administration Explorer and create a database connection to the SAMPLE database. After a database connection is established, navigate to the SAMPLE database folder.
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 the Tables folder and choose Create Table. You are presented with the following selections:
- Identify the schema for the new table. Select the user ID you logged in with. We discuss the significance of schemas in Chapter 7; for now it is sufficient to enter your user ID.
- The Properties pane is opened. Enter the name of the table you want to create, for example, Table1.
- Go to the Columns tab, click the Add icon. Enter the name of the first column for Table1, for example, Col1. Choose the data type from the pull-down menu, for example, INTEGER. You could create more columns by repeating this step, but one column is sufficient for now.
There are other tabs in which you can define the properties for the new table. However, completing these two windows is enough to create the table. Click the Review and deploy changes icon to generate the CREATE TABLE statement. Click the Advanced Options button to customize the deployment such as generating REORG and RUNSTATS commands. Click Run to run the statements now. Note that you can also schedule to deploy these changes some other time, such as the next maintenance window.
Table1 is displayed under the Tables view. To display the contents of the table, right-click on the table name and choose Browse data. Since nothing has been inserted into Table1, no contents are displayed. To insert a row into the table, right-click the table name, and click Edit Data. Enter a value under Col1. Click the Play button to commit the changes.
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 get familiar with how to tune SQL statements. He asks you to right-click on the database name, and select Start Tuning.
In the Capture tab, you enter the SQL statement you want to tune and get advise on. Click the Invoke Advisors and Tools button to run the explain command against the statement.
You are now in the Review tab where you are presented with recommendations based on the query, the current database statistics, and existing indexes defined in the database. You also check out the Access Plan Graph to understand how the query result is obtained. Lastly, you review the summary report and save the report for future reference.
Though you have not finished exploring all the functionality in IBM Data Studio, this exercise has made you realize how easy to use and powerful it is!