Tuesday, February 23, 2016

IBM DB2 - Dropping Indexes created for Unnamed Database Constraints

When comes to database indexes and constraints, it is always better to provide unique names for each and every constraint and index we create for better management. However there can be situations where there are already created indexes or constraints where we have not named them explicitly but having system generated names. In such situations, managing them would be little bit difficult where we have to put some extra effort to identify which index or constraint it is that we need to manage.

In this example I am comparing dropping a named index and an unnamed index in IBM DB2 where the index is an auto generated one for a unique constraint that we have defined.

First let’s see the named constraint/index scenario. Here we create the Student table that has a primary key constraint and two unique key constraints. All three constraints are provided with names for manageability.

db2 "CREATE TABLE STUDENT
(
   ID CHAR(5) NOT NULL,
   NAME VARCHAR(50),
   SSN CHAR(9) NOT NULL,
   ACCOUNT_NUMBER CHAR(10) NOT NULL,
   CONSTRAINT PK_STUDENT PRIMARY KEY (ID),
   CONSTRAINT UNQ_SSN_STUDENT UNIQUE (SSN),
   CONSTRAINT UNQ_ACC_NO_STUDENT UNIQUE (ACCOUNT_NUMBER)
)"




Once we create the table in DB2, we can view the constraints created for the table using the following query.

db2 " SELECT NAME, CONSTRAINTYP from SYSIBM.SYSTABCONST WHERE TBNAME = 'STUDENT' "


Here we can see the three constraints we have created for the Student table where  the CONSTRAINTYP column shows ‘P’ for Primary Key and ‘U’ for unique key. We can see that the names for the constraints are same as the names we have defined for each constraint.

In IBM DB2, when we create a primary key or a unique key constraint, it automatically creates indexes for each constraint. We can see the indexes using the following query.

db2 "SELECT NAME, COLNAMES FROM SYSIBM.SYSINDEXES WHERE TBNAME='STUDENT'"


Here since we have defined the constraints by providing names for each of them, the names of the indexes are also similar to the names of the constraints. The COLNAMES column shows on which columns the constraint is defined.

Since we have provided the names for the constraints and the automatically created indexes have the same names as constraints, we can easily identify which index is associated with which constraint. However we we need to verify the relationship, we can use the following query where CONSTNAME is the name of the constraint and BNAME is the name of the index associated with it.

db2 "SELECT CONSTNAME, BNAME FROM SYSCAT.CONSTDEP WHERE TABNAME = 'STUDENT'"




Following diagram shows the relationship of each constraint and it’s index.

Now if we need to drop one index created for a unique constraint, we cannot straight away drop the index. If we need to drop the auto created index in DB2, we need to drop the particular constraint by providing the name of the constraint [1].

db2 "ALTER TABLE STUDENT DROP UNIQUE UNQ_ACC_NO_STUDENT"

This will drop the constraint successfully. Since we knew the name of the constraint as well as the index, it was easy to drop the index/constraint without any issue.



However if we had not defined names for each constraint, it would be little bit difficult to drop the index by dropping the constraint as we do not exactly know the name of the constraint that is associated with the particular index.

Now let’s consider the same Student table where we do not provide names for the constraints we define.

db2 "CREATE TABLE STUDENT
(
   ID CHAR(5) NOT NULL,
   NAME VARCHAR(50),
   SSN CHAR(9) NOT NULL,
   ACCOUNT_NUMBER CHAR(10) NOT NULL,
   PRIMARY KEY (ID),
   UNIQUE (SSN),
   UNIQUE (ACCOUNT_NUMBER)
)"



We can run the following query and see what are the constraint created for the Student table.

db2 "SELECT NAME, CONSTRAINTYP from SYSIBM.SYSTABCONST WHERE TBNAME='STUDENT'"


We can see that DB2 has generated some random name for each constraint. We can run the following query to find out the names of the indexes that are automatically created for those constraints.

db2 "SELECT NAME, COLNAMES FROM SYSIBM.SYSINDEXES WHERE TBNAME='STUDENT'"


Now if we check the names of the indexes created, those have different values than their associated constraints. By looking at the COLNAMES column, we can identify the index that is associated with a particular constraint, referring to the table definition. However since we have two unique constraints here, we cannot directly identify a particular unique index just by looking at the names of the constraints.

If we try to drop one index created for a unique constraint using the following command [2], it will give an error.

db2 "DROP INDEX SQL160223082953340"





That is because in IBM DB2, automatically created indexes for constraints cannot be dropped directly [1].

Now let’s see how we can identify a particular constraint associated with a particular index. Here I need to find the one unique constraint. I can provide CONSTRAINTYP = 'U' and list down all the unique indexes on the Student table.

db2 "SELECT NAME from SYSIBM.SYSTABCONST WHERE TBNAME='STUDENT' AND CONSTRAINTYP = 'U'"



We still do not know which constraint here is linked to the particular index we need to drop. For that, we can run the following query that shows the association of the index with the particular constraint.

db2 "SELECT CONSTNAME, BNAME FROM SYSCAT.CONSTDEP WHERE TABNAME = 'STUDENT'"


Since we already know the name of the index we need to drop (BNAME column in above result), referring to that we can identify the name of the constraint.

The relationship of the constraint and the automatically created index can be shown as below. Note that the names of the indexes and constraints are system generated (random) and if you try out the same scenario, you will get different names.



We can even add a where clause to find the exact constraint name we need to drop. Here the index name 'SQL160223082953340' is the name of the index created for theunique constraint on  ACCOUNT_NUMBER column of the Student table.

db2 "SELECT CONSTNAME, BNAME FROM SYSCAT.CONSTDEP WHERE TABNAME = 'STUDENT' AND BNAME = 'SQL160223082953340'"



Now since we know the name of the constraint, we can drop the constraint using the following command where SQL160223082953380 is the name of the constraint.


db2 "ALTER TABLE STUDENT DROP UNIQUE SQL160223082953380"

This will automatically drop the index also which is created for the constraint. After dropping the constraint, we can verify if the index is also dropped successfully.



With above two examples, we can see that it eases the management of constraints and indexes when we provide names for them at the time we create them. If not later we need to put some extra effort to identify the indexes and constraints separately.


References


Tharindu Edirisinghe
Platform Security Team
WSO2

Sunday, February 14, 2016

A Simple Guide for Installing IBM DB2 and Running with WSO2 Servers

WSO2 servers are compatible with many well known database products such as Oracle, MySQL, Microsoft SQL Server, IBM DB2, PostgreSQL etc.  This is a quick start guide for you to setup IBM DB2 and use a DB2 database to run WSO2 products. In this post I am installing the IBM DB2 Express-C edition (free) on Linux (Ubuntu 15.04) and run the WSO2 Identity Server along with the DB2 database.

I have downloaded the IBM DB2 Express-C and once the zip file is extracted, I can find the db2setup executable file which can be run for GUI based installation.

Make sure that you run the installation from the root user because if you install as a non root user, you will run into several issues which would need several configuration changes.


Once you run the db2setup script, it will open the installation GUI.


Select the ‘Install a Product’ link from the left hand side panel and click the ‘Install Now’ button.


Continue the setup by clicking ‘Next’ in the following window as well.


Accept the License Agreement and click ‘Next’. 


Here I am using the Typical installation as this is just for demonstration purpose.


Here I install the DB2 Express-C and also save the response file.


I am installing the DB2 to the default file path. If you need to change it, you can specify a different file path. 


Unlike in other database products, IBM DB2 needs the Operating System level users to manage the database operations. (You can refer [1] to know about the different types of user groups needed for IBM DB2)

Here I am selecting the ‘New user’ option which would create a new user. For the DB2 Administration Server, it will suggest the uesrname like dausr1. In my operating system I already have a user named dausr1 so here it suggests dausr2 as the username. Define a password for this user and continue.



Next step is creating the database instance. 


As the DB2 Instance Owner we need another operating system user. It will suggest the username db2inst1 for this. (Here I already have a user with name db2inst1 so it has suggested db2inst2 for the username) These usernames can be changed upon your requirements. 


Next step is to create the Fenced User. The user defined functions and stored procedures will  execute under this user and group. The default suggested username would be db2fenc1. (Here I already have a user with name db2fenc1 so it has suggested db2fenc2).


Note that you can create these users separately by following [1] and choose corresponding users in the installation wizard instead of creating new users.

Then you will see a summary of the installation that is about to happen. 


Following are the details of my installation.

                                      
Product to install:                        DB2 Express-C
Installation type:                         Typical
                                     
Previously Installed Components:      
                                     
Selected Components:                  
    Base client support                   
    Java support                          
    SQL procedures                        
    Base server support                   
    DB2 data source support               
    DB2 LDAP support                      
    DB2 Instance Setup wizard             
    Integrated Flash Copy Support         
    Communication support - TCP/IP        
    DB2 Update Service                    
    Sample database source                
    First Steps                           
                                     
Languages:                            
    English                               
       All Products                      
                                     
Target directory:                          /opt/ibm/db2/V10.5
                                     
Space required:                            945 MB
                                     
New instances:                        
    Instance name:                         db2inst2
       Start instance on reboot:          Yes
       TCP/IP configuration:             
           Service name:                  db2c_db2inst2
           Port number:                   50000
       Instance user information:        
           User name:                     db2inst2
           Group name:                    db2iadm1
           Home directory:                /home/db2inst2
       Fenced user information:          
           User name:                     db2fenc2
           Group name:                    db2fadm1
           Home directory:                /home/db2fenc2
                                     
                                     
DB2 Administration server:            
    Instance user information:            
       User name:                         dasusr2
       Group name:                        dasadm1
       Home directory:                    /home/dasusr2
                                     
                                     
                                     
Contact specification:                
    New contacts:                         
       Name:                              db2inst2
       Instance:                          db2inst2
       E-mail address:                    db2inst2@tharindu-pc
       E-mail address is for a pager:     No
                                     
                                     
Response file name:                        /root/db2expc.rsp


Then it will show the progress of the installation.


Once the installation is successful, you will see the post installation steps.

These are the instructions I have got. I can see that the DB2 instance name is db2inst2 and DB2 is running on port 50000.

Required steps:

In order to start using DB2 you need to logon using a valid user ID such as the DB2 instance owner's ID "db2inst2".

You can connect to the DB2 instance "db2inst2" using the port number "50000". Record it for future reference.

Optional steps:

To validate your installation files, instance, and database functionality, run the Validation Tool, /opt/ibm/db2/V10.5/bin/db2val. For more information, see "db2val" in the DB2 Information Center.

Open First Steps by running "db2fs" using a valid user ID such as the DB2 instance owner's ID. You will need to have DISPLAY set and a supported web browser in the path of this user ID.

You should ensure that you have the correct license entitlements for DB2 products and features installed on this machine. Each DB2 product or feature comes with a license certificate file (also referred to as a license key) that is distributed on an Activation CD, which also includes instructions for applying the license file. If you purchased a base DB2 product, as well as, separately priced features, you might need to install more than one license certificate. The Activation CD for your product or feature can be downloaded from Passport Advantage if it is not part of the physical media pack you received from IBM. For more information about licensing, search the Information Center (http://publib.boulder.ibm.com/infocenter/db2luw/v10r5/index.jsp) using terms such as "license compliance", "licensing" or "db2licm".

To use your DB2 database product, you must have a valid license. For information about obtaining and applying DB2 license files, see  http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.qb.server.doc/doc/c0061199.html.

Refer to "What's New" http://publib.boulder.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.wn.doc/doc/c0052035.html in the DB2 Information Center to learn about the new functions for DB2 10.5.

Verify that you have access to the DB2 Information Center based on the choices you made during this installation. If you performed a typical or a compact installation, verify that you can access the IBM Web site using the internet. If you performed a custom installation, verify that you can access the DB2 Information Center location specified during the installation.

Review the response file created at /root/db2expc.rsp.  Additional information about response file installation is available in the DB2 documentation under "Installing DB2 using a response file".


You can also view the log file of the installation. 

Here is the log file I have got.

Checking license agreement acceptance :.......Success
Installing DB2 file sets :.......Success
Executing control tasks :.......Success
Updating global registry :.......Success
Starting DB2 Fault Monitor :.......Success
Updating the db2ls and db2greg link :.......Success
Registering DB2 licenses :.......Success
Setting default global profile registry variables :.......Success
Creating the DB2 Administration Server :.......Success
Initializing instance list :.......Success
Configuring DB2 instances :.......Success
Registering DB2 Update Service :.......Success
Configuring the DB2 Administration Server :.......Success
Updating global profile registry :.......Success
Configuring health alert notifications :.......Success

============================================================

DB2 Setup log file started at:  Mon Feb 15 08:31:27 AM 2016 IST
============================================================

Operating system information: Linux 3.19.0-44-generic.#50-Ubuntu SMP Mon Jan 4 18:37:30 UTC 2016 x86_64
                                     
Product to install:                        DB2 Express-C
Installation type:                         Typical
                                     
Previously Installed Components:      
                                     
Selected Components:                  
    Base client support                   
    Java support                          
    SQL procedures                        
    Base server support                   
    DB2 data source support               
    DB2 LDAP support                      
    DB2 Instance Setup wizard             
    Integrated Flash Copy Support         
    Communication support - TCP/IP        
    DB2 Update Service                    
    Sample database source                
    First Steps                           
                                     
Languages:                            
    English                               
       All Products                      
                                     
Target directory:                          /opt/ibm/db2/V10.5
                                     
Space required:                            945 MB
                                     
New instances:                        
    Instance name:                         db2inst2
       Start instance on reboot:          Yes
       TCP/IP configuration:             
           Service name:                  db2c_db2inst2
           Port number:                   50000
       Instance user information:        
           User name:                     db2inst2
           Group name:                    db2iadm1
           Home directory:                /home/db2inst2
       Fenced user information:          
           User name:                     db2fenc2
           Group name:                    db2fadm1
           Home directory:                /home/db2fenc2
                                     
                                     
DB2 Administration server:            
    Instance user information:            
       User name:                         dasusr2
       Group name:                        dasadm1
       Home directory:                    /home/dasusr2
                                     
                                     
                                     
Contact specification:                
    New contacts:                         
       Name:                              db2inst2
       Instance:                          db2inst2
       E-mail address:                    db2inst2@tharindu-pc
       E-mail address is for a pager:     No
                                     
                                     
Response file name:                        /root/db2expc.rsp

Checking license agreement acceptance :.......Success
Installing: BASE_CLIENT_R

Installing: DB2_PRODUCT_MESSAGES_EN

Installing: BASE_CLIENT

Installing: JAVA_RUNTIME_SUPPORT

Installing: DB2_JAVA_HELP_EN

Installing: BASE_DB2_ENGINE_R

Installing: GSK

Installing: JAVA_SUPPORT

Installing: SQL_PROCEDURES

Installing: ICU_SUP

Installing: JAVA_COMMON_FILES

Installing: BASE_DB2_ENGINE

Installing: DB2_DATA_SOURCE_SUPPORT

Installing: LDAP_EXPLOITATION

Installing: INSTANCE_SETUP_SUPPORT

Installing: ACS

Installing: COMMUNICATION_SUPPORT_TCPIP

Installing: DB2_UPDATE_SERVICE

Installing: EDB

Installing: DB2_SAMPLE_DATABASE

Installing: CLPPLUS

Installing: FIRST_STEPS

Installing: EXPC_PRODUCT_SIGNATURE

Installing DB2 file sets :.......Success
Executing control tasks :.......Success
Updating global registry :.......Success
Starting DB2 Fault Monitor :.......Success
Updating the db2ls and db2greg link :.......Success
Registering DB2 licenses :.......Success
The value "DB2_COMPATIBILITY_VECTOR=MYS" was set in the Profile Registry.

Setting default global profile registry variables :.......Success
Creating the DB2 Administration Server :.......Success
Initializing instance list :.......Success
The instance "db2inst2" has been created successfully.

The value "SVCENAME=db2c_db2inst2" was set in the DBM CFG file for the
"db2inst2" instance.

The value "DB2AUTOSTART=YES" was set in the Profile Registry for the "db2inst2"
instance.

Configuring DB2 instances :.......Success
Registering DB2 Update Service :.......Success
Configuring the DB2 Administration Server :.......Success
Updating global profile registry :.......Success
The contact "db2inst2" was successfully added.

Configuring health alert notifications :.......Success


Now I have successfully installed IBM DB2. In order to connect to the database, I can switch the user to the DB2 instance owner’s account. The username of my instance owner (specified during the installation) is db2inst2 and I switch user with the command ‘su db2inst2’ in the linux terminal.

In this user’s HOME directory, I can see a folder with the name sqllib is created. Inside that the bin folder is there which contains the db2 executable files.


Next step is to create a database. The command for creating a database is as following.

db2 create database <database name> automatic storage yes

Here, even if we do not specify the ‘automatic storage’ option, DB2 will by default create the database with this option (unless you specify the storage details) [2].

I create a database with name ‘wso2’. 


You can list all the databases from the following command.

db2 list db directory | grep "Database name"


I can connect to a database from the terminal by following the command below.

db2 connect to <database name> user <username> using <password>

Here my database name is wso2, the server admin username is dbsusr2 and the password for the user is admin@wso2.

Once you connect to the database, you can use the command ‘db2 list tables’ to view all the tables in the database. Here since this is a newly created database and I do not have any tables created, it will show that no table is there.


Next step is to connect this database to a WSO2 server and run it. Here I use WSO2 Identity Server. You can use any WSO2 product and follow the same steps.

The connection details to DB2 [3] database are as following which should be put in <SERVER_HOME>/repository/conf/datasources/master-datasources.xml file. (In some WSO2 products there are other database sources also i.e bps-datasources.xml and if you want to add an IBM DB2 database for such a datasource, you can add the similar configuration in the particular datasource file)

<datasource>
  <name>WSO2_CARBON_DB</name>
  <description>The datasource used for registry and user manager</description>
  <jndiConfig>
     <name>jdbc/WSO2CarbonDB</name>
  </jndiConfig>
  <definition type="RDBMS">
     <configuration>
        <url>jdbc:db2://localhost:50000/wso2</url>
        <username>dasusr2</username>
        <password>admin@WSO2</password>
        <driverClassName>com.ibm.db2.jcc.DB2Driver</driverClassName>
        <maxActive>50</maxActive>
        <maxWait>60000</maxWait>
        <testOnBorrow>true</testOnBorrow>
        <validationQuery>SELECT 1</validationQuery>
        <validationInterval>30000</validationInterval>
     </configuration>
  </definition>
</datasource>

The database connection URL would be in the pattern jdbc:db2://<hostname>:<port>/database_name

For the username and password, you can use the server admin credentials to try this out.

The database driver class name would be com.ibm.db2.jcc.DB2Driver. Since WSO2 products are java based, you need to copy the corresponding JDBC driver to the <SERVER_HOME>/repository/components/lib directory. You can download the JDBC driver for IBM DB2 from [4] based on the DB2 version you use.

Once all above steps are done, you can start the WSO2 product with the -Dsetup option which would create the database structure.
Once the WSO2 server is started, if you list the tables in the DB2 database (with the ‘db2 list tables’ command) , you can see that the tables related to the WSO2 product are created. 


You can even query the database and verify that table creating is successful when the server started up.

When trying to create a database, if it failes with the error 'SQL1220N The database manager shared memory set cannot be allocated', you can follow [5] and get it solved. 


References

Tharindu Edirisinghe
Platform Security Team
WSO2