DB2 Express-C 9.

5 DBA and
Developer Workshop

QuickLabs
Contents
QUICKLAB #1: INSTALL DB2 EXPRESS-C & CREATE SAMPLE DATABASE................................................ 3
QUICKLAB #2: CREATING A NEW DATABASE ............................................................................................. 6
QUICKLAB #3: WORKING WITH INSTANCES, DATABASES AND CONFIGURATION ......................................... 7
QUICKLAB #4: POPULATING THE EXPRESS DATABASE USING SCRIPTS .................................................... 9
QUICKLAB #5: CREATING AN INSTALLATION SCRIPT FOR THE EXPRESS DATABASE ............................. 11
QUICKLAB #6: USING THE CONFIGURATION ASSISTANT .......................................................................... 14
QUICKLAB #7: CREATING A NEW TABLE.................................................................................................. 16
QUICKLAB #8: EXTRACTING DDL FOR THE EXPRESS DATABASE (OPTIONAL)...................................... 19
QUICKLAB #9: GRANTING AND REVOKING USER PERMISSIONS ............................................................... 22
QUICKLAB #10: SCHEDULING A BACKUP ................................................................................................. 25
QUICKLAB #11: CONFIGURING AUTOMATED MAINTENANCE ................................................................... 28
QUICKLAB #12: CREATING A TRIGGER IN CONTROL CENTER ................................................................... 31
QUICKLAB #13: CREATING A USER-DEFINED FUNCTION USING THE IBM DATA STUDIO ......................... 34
QUICKLAB #14A: SQL/XML AND XQUERY ............................................................................................. 36
QUICKLAB #14B: SQL (OPTIONAL) .......................................................................................................... 38
QUICKLAB #15: APPLICATION DEVELOPMENT IN JAVA USING ECLIPSE (OPTIONAL) ............................... 40
QUICKLAB #16: USING THE CONFIGURATION ADVISOR (OPTIONAL) ....................................................... 50
QUICKLAB #17: USING THE DESIGN ADVISOR (OPTIONAL) ...................................................................... 52
QUICKLAB #18: CREATING AN EVENT MONITOR FOR DEADLOCKS (OPTIONAL) ...................................... 54
QUICKLAB #19: OTHER SOURCES OF DB2 INFORMATION (OPTIONAL) .................................................... 60

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 2

Quicklab #1: Install DB2 Express-C & create SAMPLE database

Objective

Before you can begin exploring all the features and tools that come with DB2 Express-C, you
must first install it on your system. In this Quicklab, you will perform a basic installation of DB2
Express-C on Windows. The same installation wizard is available on Linux; therefore the steps
are very similar on that platform.

Procedure

1. Obtain DB2 Express-C images. Download the appropriate DB2 Express-C image, or
order the Discovery Kit DVD with the images from the DB2 Express-C Web site
(ibm.com/db2/express). Unzip the files into any directory you wish.

2. Locate files. Navigate to the directory (or drive) containing the unzipped DB2 product
installation files.

3. Run Launchpad. Launch the DB2 Launchpad by double-clicking on the setup.exe

file. On Linux, run the db2setup command as root. From the Launchpad, click the
Install Product option on the left pane of the window.

4. Run DB2 setup wizard. The DB2 setup wizard checks that all system requirements are
met and sees if there are any existing DB2 installations. Click the Next button to continue
with the installation.

5. Review license agreement. Read and accept the license agreement (select the “I
Accept...” radio button) and click the Next button to continue.

6. Choose installation type. For this exercise, select the Typical option (this is the default).
The Compact option performs a basic installation, while the Custom option allows you to
customize the specific features you want to install. Click the Next button to continue.

7. Select installation folder. This screen allows you to customize the drive and directory
where the DB2 code is installed on your system. Ensure sufficient space exists for the
installation. Use the default drive and directory settings for this example (shown below):

Drive: C:
Directory: C:\Program Files\IBM\SQLLIB

Click the Next button to continue.

8. Set user information. Once DB2 Express-C is installed, certain DB2 processes are run
as system services. These services require an operating system account in order to run. In
the Windows environment, using the default db2admin user account is recommended. If
the user account does not yet exist, DB2 creates it in the operating system for you. You
can also specify to use an existing account, but that account must have local administrator
authority. We recommend using the defaults suggested. Ensure you specify a password
for the account. On Linux use the default db2inst1 userID for the instance owner,

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 3

 db2fenc1 for the fenced user and dasusr1 for the DB2 Administration server user. Click
the Next button to continue.

9. Configure DB2 instance. A DB2 instance can be thought of as a container for databases.
An instance must exist before a database can be created inside it. During a Windows
installation, an instance called DB2 is automatically be created. In a Linux environment,
the default instance name is db2inst1. We will cover instances later in this book.

By default, the DB2 instance is configured to listen for TCP/IP connections on port
50000. Both the default protocol and the port can be changed by clicking the Protocols
and Startup buttons, respectively. We recommend using the default settings in this
example. Click the Next button to continue.

10. Start installation. Review the installation options previously selected. Click the Install
button to begin copying the files to the installation location. DB2 will also perform some
initial configuration processes.

11. First Steps. After the installation is complete, another launch utility, called First Steps, is
displayed. First Steps can also be started later with the command db2fs.

12. The SAMPLE database is a database that you can use for test purposes. It is created
automatically right after the installation of DB2. Verify the database exists by opening
the DB2 Control Center tool. To open this tool, from the Windows Start Menu choose:
Start -> Programs -> IBM DB2 -> DB2COPY1 (Default) -> General Administration
Tools -> Control Center
You can also start the Control Center with the command db2cc.

13. If the SAMPLE database is displayed from the Control Center, you can jump to step 16.
If it is not displayed, select the Refresh item from the Control Center View menu to
ensure you are looking at the most up-to-date information. If the SAMPLE database still
does not display, it may not have been created. You can manually create it from First
Steps. Choose the tab that says “Database Creation”, and then follow the wizard to create
the SAMPLE database. Ensure the XML and SQL objects and data option is chosen, and
click OK. This last option will create a UNICODE database which was required in
version 9 to support pureXML, but is not longer needed in DB2 9.5.

14. The following progress screen is displayed while the database is being created. (This
procedure may take several minutes). When database creation is complete, click the OK
button and close the First Steps tool.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 4

 15. Go back to Control Center and verify that a database called SAMPLE now appears in the
Object Tree pane. You may have to refresh Control Center view to see the new changes.
To do this, select the Refresh item from the Control Center View menu.

16. Restart the computer. Although this step is not mentioned in the official DB2
installation documentation, we recommend rebooting the system (if possible, at least on
Windows) to ensure all processes start successfully and to clean up any memory
resources that might not have been cleaned up correctly. This is OPTIONAL.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 5

Quicklab #2: Creating a New Database

Objective

In this Quicklab, you will create a new database using the Create Database wizard in Control
Center.

Procedure

1. From the Control Center Object Tree pane, right-click the All Databases folder, select the
Create Database item, and choose the With Automatic Maintenance item. This launches
the Create Database Wizard.

2. Specify the database name and location in the Name page of the wizard. Use the
following values:

Database Name: EXPRESS

Default Drive: C:
Alias: This will default to EXPRESS if left blank
Comment: This is optional and can be left blank

Click the Enable database for XML checkbox, and then click on the Next button to
continue to the next page of the wizard.

3. In the “Specify where to store your data” don’t make any changes, and click Next.

4. In the “Select your maintenance strategy” leave the default (“Yes, I can specify an
offline ...”), and click next.

5. Specify the offline maintenance time window in the Timing page of the wizard. Set aside
two or more hours a week when DB2 can perform automated maintenance tasks to
preserve the health of your database. For now, configure the window to start at 1AM
every Monday through Thursday for a duration of 6 hours. Click the Next button to
continue to the next page of the wizard.

6. Configure notification on the Mail Server page of the wizard. DB2 can automatically
send an email or a page if a problem or unhealthy condition is detected. If you wanted to
configure this indicate the SMTP server. For this lab we don’t have an SMTP server
available, so leave this blank and click Next.

7. Review the options selected on the Summary page of the wizard. Click the Finish button
to begin the database creation process. Database creation usually takes a few minutes,
during which time a progress indicator is displayed.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 6

Quicklab #3: Working with instances, databases and
configuration

Objective

In this Quicklab, you will create a new instance, database, and change configuration parameters
on a DB2 server on Windows. You can do it from either the Control Center or the Command
Window. We provide the instructions using the Command Window.

Procedure

1. From Command Window, create a new instance called newinst

db2icrt newinst

2. In the new instance newinst, create a database newdb with default values
set db2instance=newinst
db2start
db2 create database newdb

3. List all the instances in your server

db2ilist

4. Switch to the DB2 instance and make sure you really switched
set db2instance=db2
db2 get instance

5. Change the dbm cfg parameter FEDERATED to a value of YES from NO and verify the
change occurred.
db2 update dbm cfg using FEDERATED YES
db2 force applications all
db2 terminate
db2stop
db2start
db2 get dbm cfg

6. Connect to the SAMPLE database with the userID/psw you are logged on to the
operating system
db2 connect to sample user <userID> using <psw>

7. Review how many applications are running in your current instance

db2 list applications show detail

8. Open another DB2 Command Window and connect again to the SAMPLE database
without specifying a userID/psw. Then review how many connections you have now.
db2 connect to sample
db2 list applications

9. Force off one of the DB2 command windows

db2 force application (<application handle obtained from the db2 list applications
command for application name “db2bp.exe”)

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 7

 10. Drop the instance newinst
db2idrop newinst

11. Drop and recreate the DAS,and start it.

db2admin stop
db2admin drop
db2admin create
db2admin start

12. Set the DB2 Registry variable DB2COMM to tcpip and npipe in your instance
db2set db2comm=tcpip,npipe
db2stop
db2start

13. Unset the DB2COMM registry variable

db2set db2comm=
db2stop
db2start

14. Check the current value of the LOGSECOND db cfg parameter, and then change it to a
value of 5 and verify the new value
db2 connect to sample
db2 get db cfg
db2 update db cfg using LOGSECOND 5
db2 get db cfg

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 8

Quicklab #4: Populating the EXPRESS Database using scripts

Objective

In this Quicklab, you will populate the EXPRESS database using the Command Editor and two
scripts supplied.

Procedure

1. In Quicklab #2 you created the EXPRESS database; now you need to populate it with a
few tables and some data. For your convenience, two scripts, called quicklab4.db2
and quicklab4.dat have been created to do this for you. The quicklab4.db2
script contains the commands used to create the tables and therefore must be run first.
The quicklab4.dat script contains statements that insert data into the tables. Both
scripts can be found in the C:\DB2workshop\quicklab4 folder. To run these
scripts, open Command Editor. Ensure that the new database you created is selected in
the drop-down list in the toolbar. If the new database does not appear in the list, add a
connection to it using the Add button.

2. Click the Selected Æ Open menu on the Command Editor and navigate to the folder
where the scripts are stored. Select the quicklab4.db2 file and click the OK button. The
contents of the file should now be displayed in Command Editor’s input area. Click the
Run button to run the script. Verify that there were no errors encountered when running
the script.

3. Repeat Step (2) for the quicklab4.dat file.

The new database you created is for a very simple Internet bookstore. The BOOKS table contains
all the information about the books the store carries. The CUSTOMERS table contains
information about each of the store’s customers. Finally, the SALES table contains sales data.
Whenever a customer purchases a book, a record is made in the SALES table. The diagram below
shows the design and relationship between the tables.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 9

 BOOKS table
book_id title cost image
(INTEGER) (INTEGER) DECIMAL(7,2) BLOB (1MB)
Primary Key

CUSTOMERS table
cust_id firstnme lastname address email
(INTEGER) VARCHAR VARCHAR(100) VARCHAR(300) VARCHAR(100)
Primary Key (100)

SALES table
sales_id prod_id cust_id qty price purch_date
(INTEGER) (INTEGER) (INTEGER) (INTEGER) DECIMAL(7,2) TIMESTAMP
Primary Key

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 10

Quicklab #5: Creating an Installation Script for the EXPRESS
Database

Objective

Scripts are a powerful mechanism for performing repetitive tasks such as database statistic
collection, backups, and database deployment. Operating system scripts have the advantage of
supporting script parameters, making them more flexible. In this Quicklab, you will create an
operating system script to deploy the EXPRESS database as the EXPRESS2 database. The script
will call the previously generated SQL scripts for database objects.

Procedure

1. Open a text editor, such as Wordpad (Start > Programs > Accessories > Wordpad).

2. Create a script as shown in the screenshot below.

3. Save the script (file) in a directory and call it create_database.bat. In the Save As
dialog window, ensure you choose the MS-DOS Format option. Also, put quotes around
the file name to ensure that Windows does not append a .TXT extension to it.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 11

 4. To run scripts that interact with DB2, you must have a DB2 command-line environment.
To open a DB2 Command Window, go to Start > Program Files > IBM DB2 > Command
Line Tools > Command Window.

Alternatively, you can use Start > Run > db2cmd

5. To run the script, enter the command:

cd C:\express
create_database.bat db2admin ibmdb2

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 12

 6. Take a moment to familiarize yourself with the script you just created. Do you
understand what is happening on each line? Discuss with your neighbor(s) what each line
of the script does. If you have any questions, ask your instructor.

Try to answer the following questions:

a. Where is the database connection established?

b. What do the %1 and %2 mean?

c. What does the following line of code do? Where is it used? For what?
SET DBPATH=C:

d. What does the following line of code do?

del schema.log, triggers.log, app_objects.log

e. What happens when the script isn’t called with any parameters?
e.g. create_database.bat

f. Why don’t the SQL scripts being called contain CONNECT TO statements?
How do they connect to the database?

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 13

Quicklab #6: Using the Configuration Assistant

Objective

The Configuration Assistant can be used to quickly and easily configure remote database
connections. In this Quicklab, you will catalog a database residing on a remote DB2 server (i.e.
your neighbor’s workstation). You will try using both Search and Discover mode. Once the
database is cataloged, you will be able to access it as if it were local. DB2 performs all the
communication processes “under the covers”.

Note:
This Quicklab assumes you are working within a network. If this is not the case, you can always
use your own computer as both the client and server machines and follow the instructions for
configuration below to connect to your own system.

Procedure

1. Ask your neighbor (or instructor) for the following information:

Remote Database Info:

(PR) Protocol __TCPIP____
(IP) IP Address or hostname ____________
(PN) Instance Port Number ____________
(DB) Database Name _ SAMPLE __

Hints:
• To obtain the hostname on Windows, type hostname from a command
window
• To obtain the IP address on Windows, type ipconfig from a command
window

2. Open the Configuration Assistant. (Hint: it is accessible through the Start menu).

3. Open the Selected menu and select Add Database Using Wizard.

4. On the Source page of the wizard, select the Manually Configure a Connection to a
Database option. Click the Next button to move to the next page of the wizard.

5. On the Protocol page of the wizard, select the TCP/IP option. Click the Next button to
move to the next page of the wizard.

6. On the TCP/IP page of the wizard, enter the full hostname or IP address that you wrote
down from (1). Enter the Port number you wrote down from (1). Click the Next button to
move to the next page of the wizard.

Note: The option for Service Name can be used if you have an entry in the local Services
file with a port number defined corresponding to the port the Instance on the remote
server is listening on. When you use this option, DB2 will look in the services file on the

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 14

 local machine, not on the server. You must add an entry to this file if you want to use this
option.

7. On the Database page of the wizard, enter the name of the database defined on the
remote server that you wrote down in (1) in the Database Name field. Note how the
Database Alias field is automatically filled out with the same value. The database alias is
the name that local applications will use to connect to this database. Since you already
have a local database called SAMPLE defined, DB2 will not let you catalog another
database with the same name. You must therefore use a different alias name. For this
example, change the database alias to SAMPLE1. You can enter an optional comment
about this database if you want. Click the Next button to move to the next page of the
wizard.

8. On the Data Source page of the wizard, you can optionally register this new database
(data source) as an ODBC data source. This automatically registers it in the Windows
ODBC Manager for you. For this example, un-check the Register this database for
ODBC since you will not be using ODBC. Click the Next button to move to the next page
of the wizard.

9. On the Node Options page of the wizard, specify the operating system of the server where
the remote database is located. All workstations in the lab use Microsoft Windows.
Ensure the Windows item in the drop-down list is selected. The Instance name field
should be set to DB2. If it is not, set its value to DB2. Click the Next button to move to
the next page of the wizard.

10. This System Options page of the wizard gives you the opportunity to ensure the system
and hostname are correct, and to verify the operating system setting. Click the Next
button to move to the next page of the wizard..

11. The Security Options page of the wizard allows you to specify where you want user
authentication to take place and what method you want to use. Select the option Use
authentication value in server’s DBM Configuration. This will use the method specified
by the AUTHENTICATION parameter in the remote Instance’s configuration file. Click
the Finish button to catalog the remote database and close the wizard. A confirmation
box should appear. Click the Test Connection button to ensure you can connect
successfully to the database. Also, ensure the username and password you provide is a
valid one defined on the remote server (since it is likely that the Server’s
AUTHENTICATION parameter is set to the value SERVER). If the test connection
succeeds, then you have successfully cataloged the remote database. If it does not
succeed, go back through the wizard and make sure all the correct values are specified.
(Click the Change button to go back through the wizard settings).

12. Open Control Center and try viewing the different tables in the newly cataloged remote
database.

13. Go back to the Configuration Assistant and try to catalog a different database, this time
using Search the Network option. Step through the wizard the same way you did for
manually configuring the connection. Note, on large networks, searched discovery could
take a long time to return results.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 15

Quicklab #7: Creating a New Table

Objective

So far, you have been using the existing tables in the SAMPLE database to illustrate concepts.
Eventually, you will need to create your own tables in your database. In this Quicklab, you will
use the Create Table Wizard to create two new tables in the SAMPLE database.

Procedure

1. Launch the Create Table Wizard as was previously shown in the presentation. (Control
Center > All Databases > SAMPLE > (right-click) Tables folder > (select) Create …
option

2. Define the table name, column definitions, and any constraints. The table will be used to
store information about the office supplies used by a project in the SAMPLE database.
Each time supplies are purchased, a row will be added to this table. The table will have
six columns:

• product_id: unique identifier of the item being purchased

• description: description of the item
• quantity: the quantity purchased
• cost: the cost of the item
• image: a picture of the item (if available)
• project_num: the project this product has been purchased for

3. In the first page of the wizard, use the user ID you are currently logged on as for the
schema name, and enter the following table name: SUPPLIES. You can also optionally
enter a comment. Click the Next button to continue to the next page of the wizard.

4. From this page, you can add columns to the table. Click the ADD button to add columns.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 16

 Enter the column name: product_id and select the data type: Integer. Uncheck Nullable,
and click the Apply button to define the column.

Repeat this step for the remaining columns of the table using the options shown in the
table below. Once all columns have been added (Applied), click the OK button and the
list of the columns you just created should be summarized. Click the Next button to
continue to the next page of the wizard.

Column Name Attributes

product_id (completed) INTEGER, NOT NULL
description VARCHAR, length 40, NOT NULL
quantity INTEGER, NOT NULL
cost DECIMAL, Precision 7, Scale 2, NOT NULL
image BLOB, 1MB, NULLABLE, NOT LOGGED
project_num CHAR, length 6, NOT NULL

Note: The NOT LOGGED option can be specified when declaring LOB columns. It is
mandatory for columns greater than 1GB in size. It is also generally recommended for
LOBs larger than 10MB as changes to large columns can quickly fill the log file. Even if
NOT LOGGED is used, changes to LOB files during a transaction can still be
successfully rolled back.

5. At this point, all the mandatory information for creating a table has been provided. By
skipping the other pages, you are choosing the default values for those options. You can
always add keys and constraints after a table has been created.

6. Add a constraint to the table to restrict values on the quantity column. On the Constraint
page of the wizard, click the ADD button. In the Check Name field, enter:
valid_quantities. In the Check Condition field, enter: quantity > 0

Click the OK button. You should see a summary of the constraint you just added in the
Constraint page of the wizard. Click the Next button to continue to the next page of the
wizard.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 17

 7. You can continue going through the wizard, changing the other parameters of the table.
Alternatively, you can skip to the Summary page, or simply click the Finish button to
create the table.

8. From Control Center, click on the Tables folder under the SAMPLE database in the
Object Tree pane. The table you just created should now appear in the list. A refresh of
Control Center might be necessary in order to see the changes.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 18

Quicklab #8: Extracting DDL for the EXPRESS Database
(Optional)

Objective

When you deploy a database, your goal should be to make re-creation of the database as
straightforward and repeatable as possible. This is usually done using SQL scripts, which can be
immediately executed after DB2 has been installed. In this Quicklab, you will extract the object
definitions from the EXPRESS database using the Control Center.

Procedure

1. Open the Control Center.

2. Right-click on the EXPRESS database in the object tree and select the Generate DDL
menu item. This launches the Generate DDL dialog window.

3. In the Generate DDL window, specify options for the generated DDL, as shown below. If
you created additional objects in your environment, such as table spaces, buffer pools,
etc., you would select them here. Since you have not created these types of objects,
uncheck the box. Database statistics have not been included because the production
environment will likely contain a different set of statistics than the development
environment. Similarly, configuration parameters will likely be different as well. In your
own environment, if everything is configured exactly the way it will be deployed, you
may choose to include those additional options.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 19

 4. Move to the Object tab. You are able to specifically choose the objects you want to
generate DDL for. In this case, select the user and the schema you have been using to
create all your objects in and generate the DDL for all objects in that schema. Click the
Generate button to start DDL generation

5. Review the resulting DDL. The result of the previous step is a single script with all the
SQL statements for the chosen objects. You will now organize this script into logical
groupings.

6. Create a directory called C:\express in the file system and save the generated DDL
file in this new directory to a file called schema.ddl. (Click the Save button)

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 20

 7. Open the newly saved file in Command Editor. (Hint: From Command Editor, File menu
> Open)

8. Although we only really wanted the DDL for tables, you will notice DDL for other
database objects is included as well. Move all the CREATE TRIGGER statements into a
separate new file called triggers.ddl. Even though we only created one trigger, it is
considered a best practice to separate objects by their types.

9. For now, we also recommend removing all:

• CONNECT TO database statements

• DISCONNECT statements
• CREATE FUNCTION and CREATE PROCEDURE statements
• Unnecessary comments

You should have two scripts at this point:

C:\express\schema.ddl DDL for tables, views, indexes, and constraints

C:\express\triggers.ddl DDL for triggers

10. Cleanse the script for deployment:

• Remove unnecessary comments (e.g. -- CONNECT TO…)

• Separate the functions and procedures into their own files (useful when there are
a lot of functions and procedures). You might also want to group them by
function or application (e.g. billing.ddl, math.ddl, stringfunc.ddl,
etc.)

11. You may have noticed that a special character is being used to delimit the end of the
triggers, functions and procedures (@). This is necessary in order to delimit the end of the
CREATE <object> statement versus just the end of a procedural statement within the
object.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 21

Quicklab #9: Granting and Revoking User Permissions

Objective

So far, you have been using the Instance administrator account (SYSADM) to issue all the
database commands. This account has full access to all the utilities, data, and database objects.
Therefore, it is very important to safeguard this account in order to avoid accidental or deliberate
data loss. In most cases, you will want to create different user accounts and/or groups with a
limited set of permissions. In this lab, you will create a new user account then assign it specific
privileges.

Procedure

1. Open the Windows Computer Management console by right-clicking on the My

Computer icon on the desktop, and selecting the Manage menu item.

2. Expand the System Tools selection in the tree on the left pane of the window, then expand
the Local Users and Groups folder. Right-click on the User folder and select the New
User item.

3. In the New User dialog window, enter the following information (also illustrated below).
In the User name field, enter: customer. In the Full name field, enter: Customer1. In the
Description field, enter: A typical bookstore customer. In the Password and Confirm
password fields, enter: ibmdb2. De-select the User must change password on next logon
option, and click the Create button to create the new user.

4. Ensure the Control Center advanced view is being used. To switch to the advanced view,
select the Customize Control Center menu item from the Control Center Tools menu.
Select the Advanced option and click the OK button.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 22

 5. Expand the Control Center object tree in the left object tree pane to All Databases >
EXPRESS > Tables.

6. Grant the required privileges to the newly created user. From the list of tables in the
EXPRESS database, right click the CUSTOMERS table, and select the Privileges item to
view the Table Privileges dialog window.

7. Click the Add User button and select the customer user just created. Click the OK button
to close the Add User dialog box.

8. You will notice that the customer user has been added to the user list, but has no
privileges assigned. To grant SELECT, INSERT, UPDATE, and DELETE privileges to
the user, change each drop down box to Yes. An Internet customer should be able to
view/add/update/delete their account data. We do not give the user the other permissions
because they do not require them. Click the OK button to close the Table Privileges
dialog window and accept the changes you made.

9. Repeat Steps 7-9 for the BOOKS and SALES tables. For the BOOKS table, only grant the
SELECT privilege because the customer should not be able to modify any of the store’s
inventory data. For the SALES table, only grant the SELECT and INSERT privileges. The
customer should NOT have the DELETE or UPDATE privilege because only store
employees should have access to modify sales transactions.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 23

 10. Connect to the database using the user ID created above. Try to SELECT data from the
customers table. What happens? Try to DELETE or UPDATE data in the SALES table.
What happens?

In this Quicklab, we only created one user; however, your application may contain many different types of
users. In your own time, experiment with creating other users and assigning them privileges. You can also
create Groups of users and assign privileges to the Groups you created, rather than to each individual user
specifically.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 24

Quicklab #10: Scheduling a Backup

Objective

Although DB2 is able to automate several database maintenance activities, sometimes you will
want to customize when certain activities occur. In this Quicklab, you will create a customized
nightly backup schedule for the EXPRESS database.

Procedure

1. From the Control Center object tree, navigate to Control Center > All Databases. Right-
click on the EXPRESS database and select the Backup item. This launches the Backup
Wizard.

2. The Introduction page of the wizard summarizes the current state of the database
including the time of the last backup and logging method. Click the Next button to move
to the next page of the wizard.

3. On the Image page of the wizard, select the destination of the backup image. You will
typically select a different physical drive than where the existing database is stored. For
now, create a new folder in the file system called C:\db2backup, and specify that
folder as the backup location. In the wizard, select the File System item from the Media
Type drop-down list. Click the Add button, select the folder you just created, then click
the OK button. Click the Next button to move to the next page of the wizard.

4. You can explore the Options and Performance pages, but the defaults options are
sufficient because DB2 automatically performs the database backup in the most optimal
way. Navigate to the Schedule page when you are finished exploring.

5. On Schedule page, if the scheduler has not yet been enabled, choose to enable it now.
Select the system to create the tools catalog on and create a new tools catalog. Specify a
schema for the tools catalog and choose to create it in the existing EXPRESS database.
The tools catalog holds metadata about all the scheduled tasks. Click the OK button to
continue. Click the Next button to move to the next page of the wizard once the tools
catalog has been created.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 25

 6. On the Schedule page, choose to create a schedule for task execution. Schedule the
backup to run each day, starting at 1AM. Click the Next button to move to the next page.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 26

 7. On the Summary page, you can review the scheduled tasks that will to be created. When
Click the Finish button to create the task.

8. Launch Task Center to view and/or modify the newly created backup task.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 27

Quicklab #11: Configuring Automated Maintenance

Objective

Wouldn’t it be nice to have the database automatically maintain itself? This is now possible in
DB2 Express-C! With a few easy steps, you can configure a DB2 database to automatically
maintain itself! In this Quicklab, you will configure the DB2 SAMPLE database for automatic
maintenance.

Procedure

1. From the Control Center object tree, right-click on the SAMPLE database and select the
Configure Automatic Maintenance menu item. This launches the Configure Automatic
Maintenance wizard.

2. The Introduction page of the wizard displays the current automated maintenance settings.
If you created the database with the automated maintenance option, then automated
maintenance is already configured. You can use this wizard to re-configure the automated
maintenance options. Click the Next button to move to the next page of the wizard.

3. The Type page of the wizard asks you to choose to disable all automated maintenance, or
change your automated maintenance settings. Select the option to change the current
automated maintenance settings. Click the Next button to move to the next page of the
wizard.

4. The timing page of the wizard asks you to specify the maintenance windows. Configure
the Offline window to be every Saturday and Sunday night from midnight to 6AM as
shown below. Click the Change button beside the offline maintenance window preview
pane and choose the desired times. After specifying the required information, click the
OK button to return to the wizard. Leave the online window as is. That is, online
maintenance can occur anytime. Click the Next button to move to the next page of the
wizard.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 28

 5. On the Notification page of the wizard, you can choose to define a contact in case an
automated maintenance activity fails. Skip this step for now. Click the Next button to
move to the next page of the wizard.

6. On the Activities page of the wizard, you can choose to individually automate or not to
automate specific activities as well as choose to be notified of particular activities. In this
example, ensure that all the Automate checkboxes are checked and the Notify checkboxes
are unchecked. Click the Next button to move to the next page of the wizard.

7. Before proceeding to the next page of the wizard, you should configure the backup
location of the database. Ideally, you want to store backups on a different physical drive
in case of disk failure. From the Activities page, select the Backup database option, then
click the Configure Settings button.

8. On the Backup Criteria tab of the Configure Settings dialog window, choose the Balance
Database Recoverability with Performance option. On the Backup Location tab, select
the existing backup location and click the Change button. Specify a different location to
perform the backup (ensure that enough room exists on the drive). On the Backup Mode
tab, ensure that Offline Backup is selected. Click the OK button to close the Backup
Criteria tab. Click the Next button to move to the next page of the wizard.

9. The Summary page of the Configure Automated Maintenance wizard contains a summary
of the choices you selected. Click the Finish button to accept and implement the changes.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 29

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 30
Quicklab #12: Creating a Trigger in the Control Center

Objective

Triggers are a database object used to perform business logic when a data modification operation
occurs on a table. In this Quicklab, you will create a trigger using Control Center. This trigger
will keep a log of changes (UPDATEs) made to the SALES table for auditing purposes. You will
log the user ID who made the change, as well as the time of day the change was made.

Procedure

1. Open Control Center.

2. For this Quicklab, you will need to create an additional table that will be used for logging.
Create a table with the following characteristics:

Table Name: saleslog

First Column:
Name: userid
Data type: VARCHAR(128)
Other attributes: NOT NULL

Second Column
Name: daytime
Data type: TIMESTAMP
Other attributes: NOT NULL

Hint: Create this table using the CREATE TABLE statement in Command Editor, or use
the Create Table wizard from Control Center.

3. From Control Center, expand the EXPRESS database folder. Right-click on the Triggers
folder and select the Create option. The Create Trigger dialog window opens.

4. Fill in the following information in the dialog window:

Trigger Schema: User ID of the user you are logged in as (should be the default setting)

Trigger Name: audit_sales

Table/View Schema: User ID of the user you are logged in as (should be the default
setting)

Table/View Name: Sales

Time to trigger action: After

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 31

 Operation that causes the trigger to be executed: Update of columns (do not specify any
columns because we want the trigger to fire when any of the columns are updated).
Comment: Logs all update actions on Sales table.

5. On the Triggered action tab, select the For Each STATEMENT option. Use the
following code for the triggered action:

WHEN ( 1=1 )
BEGIN ATOMIC
INSERT INTO saleslog (userid, daytime) VALUES (CURRENT USER,
CURRENT TIMESTAMP) ;
END

(Note: A statement trigger fires once after the statement activating the trigger has completed.
A row trigger specifies that the triggered action will execute every time the triggering SQL
statement affects a row.)

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 32

 Click the OK button to create the trigger.

6. You should now be able to view the trigger in the Triggers folder in Control Center.

7. Query the saleslog table to ensure there is no data in it. Delete any rows that may be in it
(DELETE FROM saleslog).

8. Try to update a record in the sales table. (Hint: Use Command Editor and/or SQL Assist
Wizard).

9. Check the contents of the saleslog table again. How many rows are in it?

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 33

Quicklab #13: Creating a User-Defined Function using the IBM
Data Studio

Objective

The IBM Data Studio is a power IDE for writing stored procedures and functions. In this
Quicklab, you will create a scalar user-defined function in IBM Data Studio. This will give you
more experience with the IBM Data Studio as well as improve your familiarity with the SQL PL
language for user-defined functions.

Procedure

1. Open the IBM Data Studio (Hint: it is accessible through the Start menu).

2. From the Data Project Explorer window, choose the project you created in the previous
Quicklab and select Open Project.

3. Right-click the User-Defined Functions folder. Select the New menu item. Select the SQL
User-Defined Function menu item. You could have alternatively selected the User-
Defined Function using Wizard item if you wanted to be guided through the process
using a GUI wizard.

4. The Editor view should open with a skeletal function. Modify the code as follows:

CREATE FUNCTION booktitle(p_bid INTEGER)

RETURNS VARCHAR(300)
-----------------------------------------------------------------
-- SQL UDF (Scalar)
-----------------------------------------------------------------
SPECIFIC booktitle
F1: BEGIN ATOMIC
DECLARE v_book_title VARCHAR(300);
DECLARE v_err VARCHAR(70);
SET v_book_title = (SELECT title FROM books WHERE p_bid = book_id);
SET v_err = 'Error: The book with ID ' || CHAR(p_bid) || '
was not found.';
IF v_book_title IS NULL THEN
SIGNAL SQLSTATE '80000' SET MESSAGE_TEXT=v_err;
END IF;
RETURN v_book_title;
END

5. Build the function by right-clicking on the function and choosing Deploy.

6. Run the function by clicking the Run button in the toolbar.

7. Since the function accepts one input parameter, a dialog window appears asking you to
fill in a value for the parameter.

Enter the value: 80002

What is the result?

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 34

 Try again with the value: 1002

What happens this time? (Hint: Look in the Messages section of the Output view).

8. Close IBM Data Studio when you are finished.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 35

Quicklab #14a: SQL/XML and Xquery

Objective

You have seen several examples of SQL/XML and Xquery syntax and have been introduced to
the DB2 Command Editor and IBM Data Studio. In this lab, you will test your SQL/XML and
Xquery knowledge and gain experience with these tools. We will use the “mydb” database used
during the demonstration performed by the instructor

Procedure

1. Ensure you create the “mydb” database and load it as discussed during the lecture

2. Using either the Command Editor or IBM Data Studio:

a) Retrieve all the comments XML documents from the ITEMS table in two ways,
but ONLY using XQuery
b) Why would issuing this SQL statement not returned the exact same output:
SELECT comments FROM items
c) Retrieve the ID and BRANDNAME for the records whose XML documents have
a ResponseRequested with a value of “No”

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 36

______________________________________________________________________
SOLUTIONS:
2a)
Xquery db2-fn:xmlcolumn('ITEMS.COMMENTS')
Xquery db2-fn:sqlquery("select comments from items")

2b)
Because SQL returns NULL values when a value is not present, while Xquery doesn’t return
anything

2c)
select id, brandname from items where
XMLEXISTS('$c/Comments/Comment[ResponseRequested="No"]'
passing ITEMS.COMMENTS as "c")

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 37

Quicklab #14b: SQL (Optional)

Objective

You have seen several examples of SQL syntax and have been introduced to the DB2 Command
Editor and SQL Assist wizard. In this lab, you will test your SQL knowledge and gain experience
with these tools.

Procedure

3. Using the SQL Assist Wizard, build a query that meets the following requirements:

List the employee numbers, first names, and last names of employees that were hired
after January 1, 1970. Order the results by the employee number.

a) Open Command Editor and ensure you are connected to the SAMPLE database
(select SAMPLE from the drop-down box). If it does not appear in the list, click
the ADD button beside the drop-down list to add a connection to SAMPLE.

b) From Command Editor, launch the SQL Assist Wizard.

c) Select the FROM section in the wizard’s Outline area (top left hand corner), then
expand the schema name of the user you are logged in as to see the various tables
associated with that schema. Select the employee table and move it into the
Selected Source Tables area using the > button.

d) Select the SELECT section (the one below the FROM section) in the Outline area,
expand the employee table to see its columns. Select the empno, firstnme, and
lastname columns and move them into the Result Columns area using the >
button.

e) In the WHERE section of the Outline area, select the hiredate column from the
Column drop-down list. In the Operator drop-down list, select the > operator. In
the Value drop down list, select the List Values option, then choose any value
from the list that appears and click the OK button. You inserted this “dummy”
value so you could find out what format DB2 expects the date to be in. Modify
the value that was inserted to: ‘1970-01-01’ and move it into the Search
Conditions area by clicking the > button. DB2 uses strong type casting. It is good
to get into the habit of explicitly casting values into a target type rather than let
DB2 do it implicitly at runtime. DB2 is only able to cast certain data values
implicitly, therefore, explicitly casting values is a good practice. To explicitly
cast the above value, wrap it in a DATE() function, like: DATE(‘1970-01-01’).

f) In the ORDER BY section of the Outline area, expand the employee table to see
its columns, then select the empno column and move it into the Sort Columns
area using the > button. You can also optionally choose to sort employee
numbers in ascending or descending order.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 38

 g) The full SQL query text should now be displayed in the bottom area of the
wizard. Click the RUN button to execute the query. A small query results
window should appear. You can close this results window by clicking its OK
button.

h) Click the OK button at the bottom of the wizard to return to Command Editor.
The query you built using the wizard should now appear in Command Editor’s
input area.

i) Click the Execute button in Command Editor to run the query. A results window
should appear where you can add/delete/modify the result set that is returned.

The SQL Query you created is:

__________________________________________________________________

4. Using the SQL Assist Wizard or your own knowledge of SQL, write a query that meets
the following requirements:

List the first and last names of all employees that are not managers. Sort by employee
lastname initially, then by first name.

__________________________________________________________________

5. Using the SQL Assist Wizard or your own knowledge of SQL, write a SQL statement
that gives all employees in department D11 a 15 percent raise.

__________________________________________________________________

6. Using the SQL Assist Wizard or your own knowledge of SQL, write a SQL statement
that will transfer ownership of project number AD3112 to Daniel S. Smith.

(Note: The SQL Assist Wizard does not have the capability of nesting sub-select statements
in data modification statements. Construct the sub-SELECT first using the wizard, then use
the wizard to construct the desired UPDATE statement. Merge the two statements together to
obtain the desired result.)

__________________________________________________________________

______________________________________________________________________
SOLUTIONS:
1. SELECT empno, firstnme, lastname FROM <schema>.employee WHERE hiredate > DATE('1970-01-01') ORDER BY empno
2. SELECT firstnme,lastname FROM <schema>.employee WHERE job NOT LIKE 'MANAGER' ORDER BY lastname, firstnme
3. UPDATE <schema>.employee SET salary = (salary * 1.15) WHERE workdept = 'D11'
4. UPDATE <schema>.project SET respemp =
(SELECT empno FROM <schema>.employee WHERE firstnme = 'DANIEL' AND midinit = ’S’ AND lastname = 'SMITH')
WHERE projno = 'AD3112'

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 39

Quicklab #15: Application Development in Java Using Eclipse
(Optional)

The Eclipse platform is an open platform for tool integration. Eclipse runs on multiple operating
systems and can be used for building applications in a variety of languages, including Hypertext
Markup Language (HTML), eXtensible Markup Language (XML), Java and C. One of the goals
of Eclipse is to provide a Java development environment; another is to integrate a variety of tools
and functionality on one platform. This allows tool vendors and developers the ability to create
new tools which meet specific requirements and provide diverse functionality. The Eclipse user
benefits from the variety of tools available to choose from in an integrated, easy-to-use consistent
platform.

In this Quicklab, you will create a simple Java application using the Eclipse IDE and the DB2
plug-ins for Eclipse. This will expose you to some of the powerful functionality of the Eclipse
platform and spark your interest in developing DB2 Java applications using Eclipse or Rational
Application Developer (i.e. Eclipse on steroids!).

Procedure

1. Open Eclipse. Open a Windows command prompt (Start > Programs > Accessories >
Command Prompt).

Navigate to the C:\eclipse directory (cd C:\eclipse).

Launch Eclipse using the following command:

eclipse –vm “C:\Program Files\IBM\SQLLIB\java\jdk\jre\bin”

Note: Eclipse has already been downloaded for you. If you would like instructions for
downloading it for your own PC, go to the following URL:

http://www.ibm.com/developerworks/db2/library/techarticle/dm-
0408cline2/index.html?ca=dgr-lnxd02db2plg4eclpse

Note that this download contains the Eclipse development platform and the DB2 plug-ins
for Eclipse.

2. When Eclipse first opens, it asks you to select a workspace. The workspace is an
important concept to understand in Eclipse, since it is used to manage the resources
associated with a task you wish to perform. When a user starts a new task in Eclipse, the
resources associated with that unit of work need to be grouped together. A workspace can

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 40

 be thought of as a grouping of resources associated with a task you want to accomplish.
Use the default Workspace location and click the OK button.

3. The Eclipse IDE opens. The diagram below highlights its main components.

4. Before you begin coding, you must create a new Project for the Java application being
developed. Select File > New > Project > Java Project and enter express as the project
name. Click the Finish button to create the Java project. Click Yes to switch to the Java
perspective if prompted.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 41

 5. The DB2 plug-ins for Eclipse are accessible from the Data (Cloudscape) perspective and
do not require association to a particular project. In order to simplify the development
process and avoid switching between the Java and Data perspective, add the DB2 plug-in
views to the Java perspective, specifically the Database Explorer view and the DB
Output view. You can do this through the Window > Show View > Other menu. Expand
the Data folder, select the Database Explorer item, then click OK. Repeat the previous
steps, this time selecting the DB Output item from the Data folder.

Both views should now be accessible from the Java perspective, as shown below.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 42

 6. Create a new database connection. Right-click in white area in the Database Explorer
view and select the New Connection item. In the New Database Connection wizard that
opens, name the connection expconn and select the Choose a DB2 alias option. Click the
Next button to move to the next page of the wizard.

7. Ensure all the connection details are correct. See the screenshot below for an illustration.
Click the Test Connection button to ensure a database connection can be established.
Click the Next button to move to the next page of the wizard.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 43

 8. You can filter the objects that show up in the database connection window by schema and
by object. For this exercise, use the default options and click the Next button to move to
the next page of the wizard.

9. The next page of the wizard allows you to specify additional parameters, such as the path
to the JDK and the default schema to be used. For this exercise, again use the default
options. Click the Finish button to create the connection. In the Database Explorer view,
you should see the new connection, as well as a tree of objects under it. You can always
right-click on the connection you created and select the Edit Connection option to re-
open the connection wizard and edit the connection properties.

10. Expand the Tables folder under the schema you have been using. Expand one of the
tables (such as BOOKS) to see the column definitions. Right-click on the table name and
choose the Sample Contents item. The table data is then displayed in the DB Output view.

11. You are also able to issue ad-hoc SQL. Right-click the connection in the Database
Explorer view and select Open SQL Scrapbook. A Scrapbook editor text editor should
open where you can enter SQL statements. Try entering a SQL statement. For instance,
try to find out what the current date and time is. To execute the SQL statement, go to the
SQL menu and select the Execute item. If there is a syntax error in the statement, view the
error messages in the Messages tab in the DB Output view. Results of the query can be
found in the Results tab.

If you are unsure about the exact SQL syntax for a statement, the scrapbook has a help
feature to remind you of common SQL syntax usage. For instance, enter the word
CREATE in the scrapbook. Right-click in the scrapbook editor area and select the Content
Tip item. A sample of the CREATE TABLE syntax will appear to refresh your memory.

Close the scrapbook editor when you are finished experimenting.

Note: Presently only one SQL statement can be issued at a time from the SQL Scrapbook.
However, when running statements from the Scrapbook if two dashes are placed in front
of statements preceding the statement you wish to run, the -- is parsed as a comment and
therefore not executed.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 44

 12. Now that you have seen a few of the development features in the Eclipse IDE (there are
many more Wizards and RAD tools available in Rational Application Developer), you
can begin developing the simple Java application. You first need to create a new Class
file that the application code will be placed in. In the Package Explorer view, right-click
the express project, select the New item, then select the Class item. In the Name field,
enter: CustomerReport. Use the default options for everything else, but feel free to
examine the other options. Click the Finish button to create the new class.

13. In the Package Explorer view, under the Express project, you should now see a new file
called CustomerReport.java under the Default Package tree node. You can also
expand the node further to see the Java classes and methods defined in it.

14. Double click the file to open it in an editor (if it is not open already). The file contains a
basic template for you to fill in. Replace the contents with the code below. The code
below contains a lot of comments in order to explain what each line does. You do not
have to copy the comments if you do not want to.

This code can also be found in a file called quicklab18.java in the

C:\DB2workshop\quicklab18 folder. Open the file in a text editor such as
Wordpad (Start > Program Files > Accessories > Wordpad) and copy and paste it into the
Eclipse editor.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 45

/*
* Created on Jun 24, 2005
*
* TODO To change the template for this generated file go to
* Window - Preferences - Java - Code Style - Code Templates
*/

// import the required Java API libraries

import java.util.*;
import java.sql.*;

/**
* @author <your name goes here>
*
* This class connects to the EXPRESS database, queries the
* customer table, and prints a report to the console window.
*/
public class CustomerReport
{

public static void main(String[] args)

{

// The database string, user ID, and password

// It is hardcoded here, but in reality would
// be passed in by the user
String DB_URL = "jdbc:db2:EXPRESS";
String userid = "db2admin";
String password = "t8796wass";

// the connection handle object

Connection connection = null;

// use a Properties object to store connection properties

Properties connectProperties = new Properties();
connectProperties.put("user", userid);
connectProperties.put("password", password);

try
{

// load the JDBC driver (the driver must be in the Classpath)

Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();

// try to establish a connection, using the properties specified earlier

connection = DriverManager.getConnection(DB_URL, connectProperties);

// create a PreparedStatement object and give it a statement to prepare

PreparedStatement pstmt = connection.prepareStatement("SELECT DISTINCT c.firstnme,
c.lastname, c.email FROM tedwas.sales s, tedwas.customers c WHERE s.cust_id = c.cust_id
AND s.purch_date BETWEEN (CURRENT TIMESTAMP - 2 years) AND CURRENT TIMESTAMP ORDER BY
c.lastname");

// execute the prepared statement

// create a ResultSet object to hold the result set
ResultSet rs = pstmt.executeQuery();

// format and print messages to the console window

System.out.println();
System.out.println("-------------------------------------\n");
System.out.println();
System.out.println(" Results: (entries appear in the following format: \n"
+ " FIRST NAME - LAST NAME - EMAIL \n"
+ " -------------------------------" + " ");

// initialize local variables that will hold

// column values from the result set
String firstname = "";
String lastname = "";
String email = "";

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 46

 // while there is still another row in the result set
while (rs.next())
{

// get the first, second, and third columns of the

// current row and store them in local variables
firstname = rs.getString(1);
lastname = rs.getString(2);
email = rs.getString(3);

// output their values in the console window

System.out.println(" " + firstname + " - " + lastname
+ " - " + email + " - ");
}

// release this result set's object's database and

// JDBC resources immediately
rs.close();

// release this statement object's database and JDBC

// resources immediately
pstmt.close();
}
catch (SQLException e)
{
// you can specifially capture SQLException objects for more
// customized error handling

// print trace of method calls (from the JVM stack) before

// this error occurred
e.printStackTrace();
}
catch (Exception e)
{
// print trace of method calls (from the JVM stack) before
// this error occurred
e.printStackTrace();
}
}
}

15. Before running or debugging this application, you must tell Eclipse where it can find the
DB2 JDBC driver. From the Project menu, select Properties. Select Java Build Path
from the left pane of the window. Select the Libraries tab at the top of the right pane.
Click the Add External JARs button. Navigate to the C:\Program
Files\IBM\SQLLIB\Java folder and select the db2jcc.jar file. Click the OK
button to add it to the library path. Repeat the previous steps, this time, adding the
db2jcc_license_cu.jar file. Click OK to close the Project Properties window.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 47

 16. Setup a Debug and Run configuration. Go to the Run menu and select the Debug item.
Select Java Application from the list in the left pane (Configuration). Click the New
button underneath the list to create a new configuration. Use all the default options;
however, feel free to explore the rest of the debugging options. Click the Close button to
close the debug configuration window.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 48

 17. To run the application, select the Run item from the Run menu. Select the
CustomerReport configuration created in the previous step, and click Run to run the
program.

18. If you copied the program in correctly and setup all the project options correctly, the
program should have run and printed a report. Open the Console view to view the output
of this simple program.

19. If you have additional time, experiment with some of the other features of the Eclipse
platform such as the debugger, the different views/perspectives available, and
customizing the editor/views etc.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 49

Quicklab #16: Using the Configuration Advisor (Optional)

Objective

In this Quicklab, you will use the Configuration Advisor to guide you through the process of
configuring the DBM and DB CFG parameters of the EXPRESS database. The advisor makes its
recommendations based on heuristics used by expert database administrators.

Procedure

1. Launch the Configuration Advisor. Expand the Control Center object tree to All
Databases. Right click on the EXPRESS database and select the Configuration Advisor
item.

2. The Introduction page of the advisor provides mainly informational messages. Click the
Next button to move to the next page of the advisor.

3. On the Memory page, you must specify the percentage of resources that can be used for
the target database. The advisor keeps the consumption of memory resources within this
limit. In making this decision, you must consider things such as the memory requirements
of the operating system, other applications running on the same machine, other databases
running on the machine, and development tools running on the same machine. Since you
are running in a development environment, you should use a lower value of 15%. In a
production environment, this value would likely be much higher (e.g. 80% for a
dedicated database server). You must be careful not to over-tune the database, causing
the system to use more memory than is physically available. System performance can
degrade rapidly in this case. Click the Next button to move to the next page of the advisor.

4. The Workload page of the advisor asks about the type of SQL workload expected from
the application(s) connecting to this database. If the workload consists mainly of
INSERT/UPDATE/DELETE statements, then the workload is considered transaction-
based. If the workload consists mainly of SELECT statements, it is considered report-
based. Remember, it is the frequency of statements being submitted that count, not the
total number of different ones. In our case, the workload is more transaction-based, so
select this option. Click the Next button to move to the next page of the advisor.

5. The Transaction page asks about the general nature of the transactions issued by the
application. Recall that a transaction is a set of related SQL statements that must be
executed successfully as a unit before the changes are made permanent. For the
EXPRESS database, transactions are considered short (< 10 SQL operations per
transaction). As for the transactions per minute, you estimate that it will be 15. Click the
Next button to move to the next page of the advisor.

6. The Priority page asks you to choose what is more important to you: faster transaction
processing or quicker recovery. Remember that performance tuning is a balancing act.
You must sacrifice some things in favor of others. Because you do not expect an
extremely high transaction rate and because speedy recovery is desirable to avoid long

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 50

 delays, assign equal importance to both runtime performance and recovery. Click the
Next button to move to the next page of the advisor.

7. The Populated page asks whether the database already contains real production data or
data that is representative of the production environment. The advisor can further
optimize resource allocation if the database is already populated. Since you have loaded
the data already, you should choose the Yes option. Over time, as the size of the database
grows, you can re-run the advisor periodically to fine-tune your configuration. Click the
Next button to move to the next page of the advisor.

8. The Connection page asks about the approximate number of concurrent database
connections you expect. This impacts the allocation of resources available in your system.
Since this is an Internet application, you estimate that during the peak period, there will
be about 100 connections (although only a fraction of them will be involved in
transactions). Click the Next button to move to the next page of the advisor.

9. The Isolation page configures parameters related to managing locks (something we have
not yet talked about in this workshop). For now, select Cursor Stability as the level of
isolation needed by the application. This is the default isolation level and is usually what
is needed. Click the Next button to move to the next page of the advisor.

10. The Schedule page of the advisor asks whether the recommendations should be
implemented right away or scheduled to be implemented at a later time. Some of the
parameter changes require the instance to be restarted (all users must be forced off), so it
may be more convenient to schedule the changes for a later time. In our case, select the
Run now without saving task history option since there are no users connected. With this
option, the changes are applied immediately. Click the Next button to move to the next
page of the advisor.

The Results page displays a set of recommended parameter changes that will improve
performance. Take a quick look at the recommendations, and click the Finish button to apply the
changes. Restart the instance for all the parameter changes to take effect.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 51

Quicklab #17: Using the Design Advisor (optional)
Objective

In order to improve query performance for the specific workload being processed by the SAMPLE
database, you will use the Design Advisor to recommend indexes that can be used to improve
performance.

Procedure

1. Launch the Design Advisor. Expand the Control Center object tree to All Databases.
Right click on the SAMPLE database and select the Design Advisor item.

2. The Introduction page of the Design Advisor explains the advisor’s capabilities. Click the
Next button to move to the next page of the advisor.

3. Select the Indexes checkbox on the Features page of the advisor. You will not consider
MQTs and MDCs in this lab. Feel free to experiment with these advanced database
objects at a later time. Click the Next button to move to the next page of the advisor.

4. On the Workload page of the advisor, you must provide the workload that is processed by
the application. In this example, you will just use a simple workload consisting of only 1
query. In your own environment, you would actually provide all the queries that are part
of the workload. Enter bigQuery in the Workload Name field. Select your current schema
as the default schema. Click the Add button to launch the Add SQL Statement dialog
window. In the Add SQL Statement window, enter the following query in the SQL
Statement field:

SELECT * FROM employee, department, project

WHERE employee.workdept = department.deptno
AND department.deptno LIKE ‘E%’
AND project.deptno = department.deptno
ORDER BY employee.lastname ASC,
employee.firstnme ASC

Substitute <schema> with the ID of the user you are currently logged on as. In the Name
field, enter: bq1 . In the Frequency field, enter 1.

Click the OK button to continue to close the Add Statement dialog. Ensure that only the
query above is being considered as part of the workload. The Design Advisor might also
include cached SQL statements that you submitted previously. Click the Next button to
move to the next page of the advisor.

5. If you want to update the statistics for a particular table before the Design Advisor makes
its recommendations, select the table from the list and click the > button to move it into
the Selected table list. For now, select the employee, department, and project tables to
have statistics updated. If any of these tables do not appear in the list, proceed without
adding them. Click the Next button to move to the next page of the advisor.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 52

 6. The Options page of the advisor provides several additional options to fine-tune the
recommendations. All settings on this page are optional. Of particular interest is the
option to limit the amount of disk space used by the recommendations. If you configure
this setting, the advisor picks the set of objects that yields the largest performance
improvement while keeping in mind the space constraint you specified. For now, use the
default setting. Click the Next button to move to the next page of the advisor.

7. On the Calculation page of the advisor, you can choose to run the calculation
immediately or schedule it to run at a later time when your database is less busy. The
Design Advisor uses significant system resources to search for the appropriate
recommendations; however, since the workload is very simple and the database is small
in this case, it should not be a significant factor. Select the option to run the Design
Advisor immediately. Click the Next button to move to the next page of the advisor.

8. The Recommendations page of the advisor shows the total performance improvement if
the recommended objects are created. This is calculated by comparing the resources
needed to run all the SQL in the workload with and without the recommended new
objects. If you click the Show Workload Detail button, a dialog window appears showing
the workload and the improvement in processing speed for each statement using the
recommended objects. You can choose to accept or reject each object recommendation.
For this example, accept all the recommendations. In your own environment, you may
want to review recommendations in more detail before accepting. Click the Next button
to move to the next page of the advisor.

9. The Unused Objects page of the advisor provides a summary of existing database objects
that were not accessed by DB2 to process the queries in the specified workload. This
page provides a consolidated view of what may not be needed so that you can drop these
objects and recover space. Note that just because these objects appear on the list does
NOT mean the Design Advisor is recommending removing them. They might have been
created by someone else and simply not required by the workload you specified. Remove
any objects with care. For now, do not to remove any existing objects. Click the Next
button to move to the next page of the advisor.

10. The Schedule page of the advisor provides the option of implementing the Design
Advisor’s recommendations immediately or scheduling them for implementation at a
later time. Choose the Run now without saving task history option. You can also generate
a report of the advisor’s recommendations by clicking on the Generate button. This
report can be distributed to the various stakeholders in your organization for approval
before implementing the recommendations. Click the Next button to move to the next
page of the advisor.

11. The Summary page provides a summary of the actions you have chosen. Click the Finish
button to apply the recommendations to the database.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 53

Quicklab #18: Creating an Event Monitor for Deadlocks (Optional)

Objective

An event monitor is a tool that gathers information based on events representing transitions in
database activity (connections, deadlocks, statements, etc.). In this Quicklab, you will create an
event monitor for deadlocks and simulate a deadlock scenario. The same steps can be followed to
create any type of monitor. You will also have the opportunity to use the DB2 Command Line
Processor and Event Analyzer tools.

Procedure

1. Open Control Center.

2. Expand the object tree for the EXPRESS database and right-click on the Event Monitors
folder. Select the Create menu item.

3. In the Create Event Monitor dialog window, specify the following information for the
event monitor to be created:

Name: DeadlockMonitor
Checkboxes: Deadlocks (with details)
Start Now

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 54

 Click the OK button to create the event monitor. In Control Center, select the Event
Monitor folder in the object tree. You should see the event monitor in a Started state.

4. Simulate a deadlock scenario. Open two separate DB2 Command Line Processor (CLP)
windows. To open a CLP window, go to Start > Programs > IBM DB2 > Command Line
Tools > Command Line Processor.

5. In the first window, issue the following commands (press Enter after each command):

UPDATE COMMAND OPTIONS USING c OFF

(Note: this turns off auto-commit)

CONNECT TO EXPRESS

CREATE TABLE deadtable (c1 INTEGER)

COMMIT

INSERT INTO deadtable VALUES (1)

6. In the second window, issue the following commands:

UPDATE COMMAND OPTIONS USING c OFF

(Note: this turns off auto-commit)

CONNECT TO EXPRESS

INSERT INTO deadtable VALUES(2)

SELECT * FROM deadtable

(You will see the cursor just hang).

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 55

 7. Wait about 15 seconds after finishing step (6). Then, from the first window, issue the
following command:

SELECT * FROM deadtable

8. A deadlock occurs because the first window and second window are lock-waiting on each
other, and one application must be forced to roll back to break the lock contention. The
DB2 deadlock monitor process will choose a victim application and roll it back within 10
seconds (default) or whatever the value is in the database configuration parameter
DLCHKTIME. One of the windows will return the query result, the other will return a
deadlock message.

9. Analyze the event monitor information. From Control Center, expand the Event Monitor
folder and right-click the event monitor object you created. Select the Stop Event
Monitoring menu item.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 56

 10. Right-click the event monitor object again and select the Analyze Event Monitor Records
menu item. This launches the Event Analyzer tool.

11. On the first screen you will see a list of events that have occurred. There should be one
event (the deadlock event you simulated). To find out more about this event, use the
analyzer tool to perform a series of drill-downs to more detailed data.

12. Since you are looking for deadlock information, start by drilling down to Deadlocks
(select the Deadlocks item from the drop down list).

13. From this view, you should see that information for one deadlock occurrence has been
recorded. To see the connections that were involved, drill-down further to Deadlock
Connections. Select Deadlock Connections from the drop-down list.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 57

 14. You should now see the two application IDs that were involved in the deadlock. Scroll
across the information to reveal additional information about the applications.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 58

 15. To see additional details about each connection, such as the SQL statement that was
executed when the deadlock occurred, drill-down down further to Data Elements from
the drop-down list.

16. As you can see, this tool is very useful for summarizing the information needed to determine
what caused an event to occur. You can then take corrective action based on your findings.

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 59

Quicklab #19: Other Sources of DB2 Information (Optional)

Objective

So far, you have used the DB2 Information Center to look up information about DB2 data types.
There are other excellent sources of DB2 information that you should be aware of. In this
Quicklab, you will examine a few of these other sources of DB2 Information. For sake of
illustration, you will try to find more information about DB2 concurrency.

Procedure

1. Open a web browser and go to the DB2 developerWorks website -

http://www.ibm.com/developerworks/db2/ . This website contains many links to different
DB2 resources including support, migration/porting, training, and news. Informative
articles about various DB2 topics are also posted on this site. Let’s search for some
articles on concurrency issues.

2. In the search bar at the top of the screen, type DB2 concurrency and select the DB2
option for the within drop-down list to limit our search to the DB2 developerWorks
section, rather than the entire developerWorks site. Click the Search button to start the
search.

3. The results screen contains links to various articles containing concurrency issues.
Sometimes though, you will need to be more specific about the topic you are interested in.
Concurrency is a broad term that covers many things. Let’s try to be more specific. In the
search bar, search for DB2 locking and see the results. Articles relating to locking
specifically are now presented at the top of the list.

4. The developerWorks website also contains forums where you can post your questions to
the DB2 community. The forums are located at:
http://www.ibm.com/developerworks/forums/db2_forums.jsp . Select the forum for DB2
Express and read some of the postings. This forum mirrors the
ibm.software.db2..db2express newsgroup. Use the developerWorks web interface to

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 60

 access/post to the forum (you must first register on the developerWorks website) or
access the newsgroup from your favorite newsgroup reader (e.g. Outlook Express,
Eudora, etc.)

5. Another good source of information is IBM Redbooks. These are written by experts in
the area and usually contain lots of examples, advice, and best practice information. Open
a web browser and go to the IBM Redbooks website - http://www.redbooks.ibm.com/

6. Again, try to search the Redbooks website for DB2 concurrency topics. Rather than
search through the whole Redbooks site, focus the search to the DB2 Information
Management group of products. On the left side of the website, select the DB2 Info Mgmt
option from the Redbook Domain drop-down list to show Redbooks specifically related
to DB2 products.

7. From this screen, you can see all the latest Redbooks and can browse through the
collection of DB2-related Redbooks. There is also a search capability at the top of the
screen where you can search the entire IBM Redbooks website using key words.

8. Finally, the IBM Virtual Innovation Center

(http://www.developer.ibm.com/welcome/vic.html) is designed for independent software
vendors, systems integrators, solution providers, value-added resellers, and developers
who are new to the IBM family or are existing IBM Business Partners. Registering in this
program provides you with many benefits while developing your application with several
IBM software products (including DB2 Express Edition). Such benefits include early
assistance in building your applications on IBM software, code for development and
training purposes at no charge, access to online IBM education courses, access to online
technical support via e-mail or via chat, access to online sales and marketing materials,
and no up front financial investments or commitments to IBM. If you meet the
registration requirements, this website will be of great value to you!

DB2 Express-C 9 DBA and Developer Workshop - Quicklabs 61