Using IBM DB2 in the CSLab

Weining Zhang

Department of Computer Science



A number of IBM data management products have been installed on computers in the CSLab. These products include IBM DB2 Universal Database Enterprise Edition 7.1, Intelligent Miner for Data, and Intelligent Miner for Text. This document provides information about the installation and usage for faculty and students.

Installed Products

The products are installed on a Sun Sparc 10 host named db2svr and can be accessed from any client machine in the CSLab network. The product directories are

DB2 Databases

The DB2 UDB comes with both server and client software. A database server will be running on the host db2svr. Each client is running on a Sun Sparc 10 workstation in the CSLab. A number of databases have been created on the server and can be remotely accessed from the clients. One database is the sample database. Another database is called datamine to be used for the Data Mining class. The owner of these databases is a user named db2inst1. Student and faculty users can only retrieve data from the sample database, but can create and access to tables, indexes, and functions in datamine database. The database objects created by an user will be within the schema under the user's name. To gain access to these databases, the user must log in on a client machine in the CSLab network and set up the correct environment variables.

Notice that when accessing the databases from a client machine, you must refer to these databases with their aliases. The alias of sample is cssample and the alias of datamine is dbmine. These aliases are needed to tell the system that these databases are stored remotely on db2svr instead of locally on the client machine.

In addition to interactive access to the databases, DB2 UDB also support access from programs through JDBC, ODBC, and embedded SQL.

Environment Variables

A number of environment variables must be set for the system to access to DB2 commands and databases. The simplest way to do this is to run a script located in the home directory of db2inst1. If you use cshell, you should issue the command

source /db2home/db2inst1/sqllib/db2cshrc

If you use sh, ksh, etc., the command to run is

. /db2home/db2inst1/sqllib/db2profile

You may want to include this line into the .cshrc or .profile start-up files in your home directory.

DB2 Commands

The commands are located in /db2home/db2inst1/sqllib/bin. Some commands can only be executed by database administrator (DBA). The command db2 will start an interactive command processor through which SQL statements as well as other DB2 statements can be executed. The following example shows how to access to the staff table in the sample database.

Log on a client machine as a student or faculty user

source /db2home/db2inst1/sqllib/db2cshrc (Assuming csh)

db2 (You should see lines of help information)

db2 => connect to cssample (Must connect to a database before issuing other SQL statements)

db2 => select * from db2inst1.staff where dept=20 (You should see a few lines of data)

db2 => quit

On-line help is available within db2 command processor. Just enter ? and read the help text.

On-line Documentation

A set of DB2 UDB manuals can be accessed using a Web browser. The URL is http://www.cs.utsa.edu/~wzhang/cs6973/f01/db2doc.html. Once you are there, the SQL and other related documents can be found by following the Administration link.

Intelligent Miner for Data

This product consists of a server, a client, and a toolkit. The server runs on the host db2svr and clients run on Windows NT and Windows 2000 computers. The toolkit contains tools to do association rule mining, classification, and clustering. These tools can work on data that are either stored in a relational database or in plain files. If a database is involved, the miner server will communicated with the database server.

Environment Variables

To be able to use Intelligent Miner for Data, the following changes must be made to environment variables.

Running Intelligent Miner Client on Windows 2000 (An Example)

The file dataFile.dat contains some 1500 data records in a text format. Before doing any mining task, we need to set up a database table to contain the data. To do this, we first log on a UNIX machine in the CSLab. In the directory where the data file is located, we create two SQL scripts createTable.db2 (to create a new table) and importData.db2 (to load the data file into the table). We then run the scripts as follows.

The command line processor db2 may take many options. Option t indicates that each db2 command will terminate with the semicolon, and option f is followed by the name of a script file

Now, we can start working with the Windows 2000 Iminer Client. After you login on a Windows 2000 computer in the CSLab, run the client with Start -> Programs -> DB2 Intelligent Miner -> Intelligent Miner. Each time you run the Iminer client, you need to set some preference. In the preference window, enter db2svr.cs.utsa.edu in the server field, enter your UNIX user name and password in appropriate fields. Click on OK. You are now set. If you encounter a connection error complaining "program nor registered", contact the system administrator about starting the Intelligent Miner server.

The next step is to create a new mining base. The tasks include the following:

    1. Create a data object to describe the source and type of the input data.

    2. Create a mining object to describe the mining task to be performed.

    3. Run the mining task and see the result (you can also print the result).

Create a Data Object

In the Client's main window, choose Create -> Data. The Data Wizard pops up. Click on Database Table/View, and fill in a Setting name (any name will do). Then click on Next. On the top of the window, select the DBMINE Database server from the pull-down menu. Then select your own Schema, then select the table that contains the data (in this case, the Training table). Click on the Readonly radio buttom) and click on Next. Then click Next, Next, and Finish.

Create a Mining Object

In the client's main window, choose Create -> Mining. Select a mining task, say Classification - Tree, and enter a Setting name (again, any name will do). In the Available Input Data window, select a data set by clicking on a data setting name. Click Next. Choose a Mode for the data (training data vs. testing data) and click Next. Select input data fields and the class field, then click Next. Choose "Do not create output" and click Next. Check "if a result with this name exists, overwrite it", unless you create a new name for the result. Click Next, then Finish (you can check "Run this settings immediately" first to run the task or run the task sometimes later).

Run a Mining Task

In the client's main window, open the Mining folder in the directory tree view, then the Classification folder. In the "Content of folder" view, click on a mining task. In the toolbar, click the green triangle button to run the task. Alternatively, you can click the right button on the task icon, and select Run in the pop-up menu.

The result will pop up in a seperate window with buttons that allow you to view pruned and unpruned decision trees. You can also print the result .

Save Mining Base

You can save the current mining base by choosing Mining Base -> Save Mining Base As, and give a name to the mining base. The saved mining base can be used in future sessions. The Intelligent Miner will create two directories in your UNIX home directory: imdmnb (for the mining bases) and idmres (for saving the results).

On-line Documentation

A number of on-line books on how to use the tools are accessible using a Web browser. Here are the books "Using the Intelligent Miner for Data", "Using the Associations Visualizer", "IBM DB2 Intelligent Miner API and Utility Reference", and "Stepwise Polynomial Regression".

Intelligent Miner for Text

This product consists of a set of tools for text based searching, classification, clustering, etc. More specifically, the following tools have been installed.

  1. Text Analysis Tools: the Language Identification tool, the Topic Categorization tool, the Feature Extraction tool, Clustering tools, the Summarizer tool.

  2. Text Search Engine

  3. Web Crawler

Environment Variables

To use the Text Analysis tools, add or change the following environment variables.

On-line Documentation

Several on-line manuals are in PDF format and can be accessed in directory /opt/IBMdb2/TextTools/books.



Last updated on 8/17/01 by wzhang@cs.utsa.edu