DB2 install and Database creation guide for ClearQuest

This blog has been produced to show how to install IBM DB2 v10.5 and IBM  Data Studio then how to create and configure a DB2 database for use with IBM ClearQuest.
​
Note: This has been written assuming that the reader does not need to be guided through the default install process.
​

1. Install

You should download IBM DB2 v10.5 and IBM Data Studio which will give you two zip files.

Unzip the zip file and selfextract the exe file by right clicking.

​2.1 DB2

Now run the setup.exe to install DB2
​

Accept the default to install and you should end up with the following
​

​Note: The install asks for Admin username and password, these will be created as usernames and passwords on the server, for this install I used db2_admin / db2_admin as username and password, and this install configured the use of port 50001 (See output window at the end of the install for port information)

Example output:
​
You can connect to the DB2 instance "DB2" using the port number "50001". Record it for future reference.
​

2.2 IBM Data Studio

Now run launchpad.exe from the Data studio directory

Select Install

Select the Admin Install, this will startup the IBM Installation Manager

Select IBM Data Studio client and perform a Default install

Select the client and perform the install using the default responses.

​3. DB2 Database Creation

​When installed, we need to create a DB2 database (in this example I use cqtest) and later I will store the ClearQuest Schema Repository  and the ClearQuest Production Database in this single DB2 Database, by creating two usernames and passwords.
​

Open a DB2 administrator command line window
​​

Db2 create database cqtest
 
Now we can open up the Data studio to configure the Database for use with ClearQuest.
​

​Enter your username and password (db2_admin / db2admin)

3.1 Configure the Database

We need to perform the following tasks...

1)       Create a buffer pool
2)       Create two table spaces , one regular and one temporary
3)       Add the public group to both table spaces
4)       Set parameters for each database (as detailed in tech note)
5)       Create a Schema Repo Username / Password  and User Database Username / Password
6)       Bind the packages to the database.

3.1.1  Create a buffer pool
When you create the buffer pool, set the page size to 32. Set the size of the 32 KB pages to 1024.
 

Give the pool a name and input the correct sizes. Save the changes and then run the DDL to implement
​

Run SQL to implement the changes and look for status succeeded in the SQL Results window,

3.1.2 Create two table spaces, one regular and one temporary
We are now going to create the two table spaces based on the pool we just created

Give it a name

Then from storage options select the buffer pool you just created from the drop down.

Now repeat for the Temporary Buffer pool

Give it a Name

Select the Buffer pool we created above

Now you can commit both these changes to the database as before
(1)    Save Change -> (2) Generate DDL -> (3) Review commands -> (4) Run SQL

3.1.3 Add the public group to both table spaces
Select the first table space and select Alter (TSPTEMP32 in this example)

​Select Privileges and then click on the Grant New Privilege key
​

Select the second table space and select Alter (TSPREG32 in this example)

​Select Privileges and then click on the Grant New Privilege key

Now you can commit both these changes to the database as before
(2)    Save Change -> (2) Generate DDL -> (3) Review commands -> (4) Run SQL

​3.1.4  Set parameters for the database (as detailed in tech note)

Performance parameter settings:

• APPL_MEMORY = AUTOMATIC (default setting)
• APPLHEAPSZ = AUTOMATIC (default setting)
• SORTHEAP = 1024

Log parameter settings:

• LOGFILSIZ = 2048
• LOGPRIMARY = 15
• LOGSECOND = 10

Double click on the pending changes column and enter the new value
​

When all changes have been made go to the top on the screen and press run
​

3.1.5 Create a Schema Repo Username / Password and User Database Username / Password 
​
Create a Username & Password in windows for the Two ClearQuest databases you are going to create, in this example we will create CQSCHEMA/ CQSCHEMA & CQUSER/ CQUSER

3.1.6 Bind the packages to the database.

The Final step before creating Databases in ClearQuest is to bind the packages to the database.
 
In the command window and using the credentials we created earlier issue the following command ..
 
 binddb2pkg server database DB2_admin password [port]
 
"C:\Program Files (x86)\ibm\RationalSDLC\ClearQuest\binddb2pkg.bat" ibmbpm cqtest2 db2_admin db2_admin 50001
 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
>DB2 packages have been bound to database cqtest2 on server ibmbpm if no error messages were displayed.

 3.1.7 Create ClearQuest Databases

Using all the above credentials we can now use the ClearQuest Maintenance Tool to create the Schema Repo and the CQ designer to create the user database.

The Vendor is DB2 the Server is the name of our database server IBMBPM the Database is the database we configured earlier cqtest. We use the first of the usernames and passwords we created CQSCHEMA / CQSCHEMA and because port 50000 was already in use on our server we specify port= 50001
​

The Vendor is DB2 ​​the Server is the name of our database server IBMBPM the Database is the database we configured earlier cqtest. We use the first of the usernames and passwords we created CQUSER / CQUSER and because port 50000 was already in use on our server we specify port=50001.

​Mark Calder
​Senior ClearCase and ClearQuest Consultant at HCL