Migrating from Microsoft SQLServer to DB2 when upgrading from IBM Tivoli Identity Manager Version 5

Microsoft SQLServer is not supported for the Identity Service Center in IBM® Security Identity Manager Version 6.0.0.2 and later. You must migrate to DB2 before upgrading.

1. Upgrade from IBM Security Identity Manager Version 5 to IBM Security Identity Manager Version 6.0.0.
   Skip this step if you are already running IBM Security Identity Manager Version 6.0.0 or 6.0.0.1.

   Follow the instructions in the Installing > Separate system upgrade and data migration sections of the Knowledge Center for IBM Security Identity Manager Version 6.0.0.

2. Migrate the database from Microsoft SQL Server to IBM DB2.
   Follow steps 2.1 to 2.12 below.

   Important: DB2 must be installed before you start this procedure.
   1. Create a new database user for DB2.
      Create user itimuser on the host where the DB2 server is located. The itimuser name is the default for IBM Security Identity Manager and is used in examples below. You may user another user name.

      Note: you may use the same user name and password that you used for Microsoft SQL Server. Those credentials are in configuration files that you modify in later steps.

      • Windows systems: Create the user directly on the system. For example, use Start > Administrative Tools > Computer Management > Local Users and Groups > Users.
      • AIX systems: Use the SMIT or SMITTY tool to create the user.
      • Solaris systems: Use the System Management Console (SMC) to create the user.
      • Linux systems: Use the useradd command to create the user. Example command: useradd -d /home/itimuser -p password itimuser
   2. Create a new DB2 database.
      Run the following commands, substituting your database name and database user credentials:

      db2 create database <itim_dbname>; using codeset UTF-8 territory us
      db2 connect to <itim_dbname> user <itim_dbadmin_name> using <itim_dbadmin_password>
      db2 create bufferpool ENROLEBP size automatic pagesize 32k
      db2 update db cfg for <itim_dbname&gt; using logsecond 12
      db2 update db cfg for <itim_dbname&gt; using logfilsiz 10000
      db2 update db cfg for <itim_dbname&gt; using auto_runstats off
      db2 disconnect current</p>

   3. Run the enrole_admin_template script.
      1. Go to the db2 directory in the IBM Security Identity Manager installation.

         <isim-home>/config/rdbms/db2

      2. Check the enrole_admin_template.sql file. There are three lines at the end of the file where the database user appears. Edit the lines if necessary to use the name of the user you created for DB2. In the example below, user name itimuser is shown.

         GRANT USE OF TABLESPACE ENROLE_DATA TO USER itimuser WITH GRANT OPTION;
         GRANT USE OF TABLESPACE ENROLE_INDEXES TO USER itimuser WITH GRANT OPTION;
         GRANT CREATETAB, CREATE_EXTERNAL_ROUTINE, CONNECT,IMPLICIT_SCHEMA ON DATABASE TO USER itimuser;

      3. Run the enrole_admin_template.sql script. The full path to the script is shown in the example.

         db2 -tf <isim-home&gt;/config/rdbms/db2/enrole_admin_template.sql

   4. Extract the database schema from the Microsoft SQL Server database.
      Use the IBM Data Movement Tool (IDMT) to extract the schema and data from Microsoft SQL Server. It provides a command-line and GUI user interface.

      If you do not already have IDMT, get it from the following location and install it on the DB2 server. You need an IBM ID to access the download site.

      https://www.software.ibm.com/webapp/iwm/web/reg/download.do?source=idmt&S_PKG=dl&lang=en_US&cp=UTF-8

      Installation instructions and a link to the download site are in the following location on developerWorks.

      http://www.ibm.com/developerworks/data/library/techarticle/dm-0906datamovement/

      See the Extracting objects and data section for instructions on how to extract data.

   5. Change the extracted database schema.
      The extracted schema is saved in the db2tables.sql file in the IDMT output directory. You need to edit the file and make the following changes.

      1. Add the following parameters to all CREATE TABLE commands:

         IN ENROLE_DATA INDEX IN ENROLE_INDEXES

         Example:

         CREATE TABLE NEXTVALUE (
         ID VARCHAR(50) NOT NULL,
         NEXTID INTEGER NOT NULL,
         CONSTRAINT NEXTVALUE_PK PRIMARY KEY(ID)
         ) IN ENROLE_DATA INDEX IN ENROLE_INDEXES; 

      2. Add the following column specification to the CREATE TABLE commands for the SIB000, SIB001, and SIB002 tables.

         In a cluster installation, add the column specification to CREATE TABLE commands for all SIB000, SIB001, and SIB002 tables in the following schemas: ITIMS000, ITIML000, ITIML001, and ITIML002.

         "DATA" VARCHAR(3360) FOR BIT DATA

         Example:

         CREATE  TABLE "ITIML000"."SIB001"
         (
         "ID" BIGINT  NOT NULL ,
         "STREAM_ID" BIGINT  NOT NULL ,
         "TYPE" CHAR(2)  ,
         "EXPIRY_TIME" BIGINT  ,
         "STRATEGY" INTEGER  ,
         "REFERENCE" BIGINT  ,
         "CLASS_ID" INTEGER  NOT NULL ,
         "PRIORITY" INTEGER  ,
         "SEQUENCE" BIGINT  ,
         "PERMANENT_ID" INTEGER  ,
         "TEMPORARY_ID" INTEGER  ,
         "LOCK_ID" BIGINT  ,
         "DATA_SIZE" INTEGER  NOT NULL ,
         "DATA" VARCHAR(3360) FOR BIT DATA,
         "LONG_DATA" BLOB(2147483647) NOT LOGGED  ,
         "XID" VARCHAR(254)  ,
         "DELETED" SMALLINT  
         ) IN ENROLE_DATA INDEX IN ENROLE_INDEXES
         ;

      3. Run the following two queries to make sure the tables all use the two index spaces. If necessary, change the value for creator to use the database user name for Microsoft SQL Server.

         select name, substr(tbspace,1,15), substr(index_tbspace,1,15) from sysibm.systables where creator = 'ITIMUSER'
         select name, substr(tbspace,1,15), substr(index_tbspace,1,15) from sysibm.systables where creator like 'ITIM%'

   6. Load the changed schema to DB2
      1. Make sure your user has authority to load the data before loading the data. It requires administrative access.
      2. Grant permissions. Connect to DB2 as Administrator and run the following commands. You can also use DB2 control center to grant the permissions. Replace with the DB2 database user you created.

         db2 => GRANT  DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,
           IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,
           QUIESCE_CONNECT,SECADM ON DATABASE  TO USER  <isim-dbadmin-name>;

      3. Load the data. Use the IDMT tool to deploy DLL and Data objects. See the "Deploying objects and loading data" section on the IDMT developerWorks site (see also step 4 above for more information on IDMT).

         http://www.ibm.com/developerworks/data/library/techarticle/dm-0906datamovement/

   7. Remove Microsoft SQL Server data sources.
      After the migration, delete MS SQL data sources. Use the WebSphere Application Server administration console.

      1. In the administration console, go to Resources > JDBC > Data Sources.
      2. Remove the following data sources.
         • ITIM Bus Data Source
         • ITIM Data Source
   8. Update configuration files.
      Update the database type and class path LAX.CLASS.PATH in the following configuration files:

      • <isim-home>/bin/runconfig.lax
      • <isim-home>/bin/DBConfig.lax
      • <isim-home>/bin/DBUpgrade.lax

      1. Change the database type as follows:

         DB_TYPE=IBM_DB

      2. If you have not already done so, copy JARs from the DB2 server to the following locations on the IBM Security Identity Manager server.

         <isim-home>/lib/db2jcc.jar
         <isim-home>/lib/db2jcc_license_cu.jar

      3. Add two items to the class path in each configuration file. Use separators appropriate for your platform.

         <isim-home>/lib/db2jcc.jar
         <isim-home>/lib/db2jcc_license_cu.jar

   9. Update enRoleDatabase.properties
      Update the database type, JDBC driver URL, and JDBC driver name in <isim_home>/data/enRoleDatabase.properties. If you are running in a cluster environment, update the file on each node in the cluster.

      The following example shows only selected parts of the enRoleDatabase.properties file.

      # Database type; The possible values are DB2, ORACLE, MS SQL SERVER
      database.db.type=DB2
      
      ############################################################
      # JDBC driver URL and driver name
      #
      ############################################################
      
      # JDBC driver URL 
      database.jdbc.driverUrl=jdbc:db2://localhost:50002/itimdb
      # JDBC driver name
      database.jdbc.driver=com.ibm.db2.jcc.DB2Driver
      database.jdbc.connectionPool.testTableName=NEXTVALUE

      Note: The following code shows the general form of the JDBC driver URL.

      jdbc:db2://<isim_db_host_name>:<jdbc_port>/<isim_dbname>

   10. Create the DB2 data source and enter the database user name and password.
       Run the following command on the Identiy Manager server to create the data source:

       <isim-home>/bin/runconfig install

       The runconfig command starts a GUI.

       1. Click the Database tab.
       2. Enter the database user and password you created.
       3. Click OK.
   11. Remove the exception tables created by IDMT.
       Run the db2dropexceptiontables.sql script. This script is located in the IDMT output directory.

       • Windows systems:

         db2 -tvdf C:\generated_col_migration.ddl 

       • UNIX and Linux systems:

         db2 -tvdf /db2dropexceptiontables.sql 

   12. Update generated columns in the migrated database.
       • Save the following code in a DB2 script file named generated_col_migration.ddl.

         SET INTEGRITY FOR SA_EVALUATION_CREDENTIAL OFF;
         SET INTEGRITY FOR ERCREDENTIALLEASE OFF;
         SET INTEGRITY FOR SA_EVALUATION_BU_HIERARCHY OFF;
         SET INTEGRITY FOR SA_POLICY OFF;
         SET INTEGRITY FOR SA_POLICY_MEMBERSHIP OFF;
         SET INTEGRITY FOR SA_EVALUATION_CREDENTIAL_POOL OFF;
         SET INTEGRITY FOR SA_EVALUATION_BU OFF;
         SET INTEGRITY FOR SA_EVALUATION_SERVICE_TAG OFF;
         SET INTEGRITY FOR SA_EVAL_CRED_DESCRIPTION OFF;
         SET INTEGRITY FOR SA_EVALUATION_SERVICE OFF;
         SET INTEGRITY FOR SA_POLICY_ENTITLEMENT OFF;
         
         ALTER TABLE SA_EVALUATION_CREDENTIAL ALTER COLUMN L_DN SET GENERATED ALWAYS AS (LOWER(DN));
         ALTER TABLE SA_EVALUATION_CREDENTIAL ALTER COLUMN L_SERVICE_DN SET GENERATED ALWAYS AS (LOWER(SERVICE_DN));
         ALTER TABLE ERCREDENTIALLEASE ALTER COLUMN L_DN SET GENERATED ALWAYS AS (LOWER(DN));
         ALTER TABLE ERCREDENTIALLEASE ALTER COLUMN L_ERCVCATALOG SET GENERATED ALWAYS AS (LOWER(ERCVCATALOG));
         ALTER TABLE SA_EVALUATION_BU_HIERARCHY ALTER COLUMN L_BU_DN SET GENERATED ALWAYS AS (LOWER(BU_DN));
         ALTER TABLE SA_EVALUATION_BU_HIERARCHY ALTER COLUMN L_CHILD_DN SET GENERATED ALWAYS AS (LOWER(CHILD_DN));
         ALTER TABLE SA_POLICY ALTER COLUMN L_BU_DN SET GENERATED ALWAYS AS (LOWER(BU_DN));
         ALTER TABLE SA_POLICY_MEMBERSHIP ALTER COLUMN L_ROLE_DN SET GENERATED ALWAYS AS (LOWER(ROLE_DN));
         ALTER TABLE SA_EVALUATION_CREDENTIAL_POOL ALTER COLUMN L_SERVICE_DN SET GENERATED ALWAYS AS (LOWER(SERVICE_DN));
         ALTER TABLE SA_EVALUATION_CREDENTIAL_POOL ALTER COLUMN L_BU_DN SET GENERATED ALWAYS AS (LOWER(BU_DN));
         ALTER TABLE SA_EVALUATION_BU ALTER COLUMN L_DN SET GENERATED ALWAYS AS (LOWER(DN));
         ALTER TABLE SA_EVALUATION_SERVICE_TAG ALTER COLUMN L_SERVICE_DN SET GENERATED ALWAYS AS (LOWER(SERVICE_DN));
         ALTER TABLE SA_EVAL_CRED_DESCRIPTION ALTER COLUMN L_DN SET GENERATED ALWAYS AS (LOWER(DN));
         ALTER TABLE SA_EVALUATION_SERVICE ALTER COLUMN L_BU_DN SET GENERATED ALWAYS AS (LOWER(BU_DN));
         ALTER TABLE SA_EVALUATION_SERVICE ALTER COLUMN L_DN SET GENERATED ALWAYS AS (LOWER(DN));
         ALTER TABLE SA_POLICY_ENTITLEMENT ALTER COLUMN L_TARGET_DN SET GENERATED ALWAYS AS (LOWER(TARGET_DN));
         
         
         SET INTEGRITY FOR SA_EVALUATION_CREDENTIAL IMMEDIATE CHECKED FORCE GENERATED;
         SET INTEGRITY FOR ERCREDENTIALLEASE IMMEDIATE CHECKED FORCE GENERATED;
         SET INTEGRITY FOR SA_EVALUATION_BU_HIERARCHY IMMEDIATE CHECKED FORCE GENERATED;
         SET INTEGRITY FOR SA_POLICY IMMEDIATE CHECKED FORCE GENERATED;
         SET INTEGRITY FOR SA_POLICY_MEMBERSHIP IMMEDIATE CHECKED FORCE GENERATED;
         SET INTEGRITY FOR SA_EVALUATION_CREDENTIAL_POOL IMMEDIATE CHECKED FORCE GENERATED;
         SET INTEGRITY FOR SA_EVALUATION_BU IMMEDIATE CHECKED FORCE GENERATED;
         SET INTEGRITY FOR SA_EVALUATION_SERVICE_TAG IMMEDIATE CHECKED FORCE GENERATED;
         SET INTEGRITY FOR SA_EVAL_CRED_DESCRIPTION IMMEDIATE CHECKED FORCE GENERATED;
         SET INTEGRITY FOR SA_EVALUATION_SERVICE IMMEDIATE CHECKED FORCE GENERATED;
         SET INTEGRITY FOR SA_POLICY_ENTITLEMENT IMMEDIATE CHECKED FORCE GENERATED;

       • Run the script.
         • Windows systems:

           db2 -tvdf C:\generated_col_migration.ddl 

         • UNIX and Linux systems:

           db2 -tvdf /generated_col_migration.ddl

3. Upgrade WebSphere Application Server to Version 8.5.
   Follow the instructions in the Installing and configuring your application serving environment and Migrating, coexisting, and interoperating sections of the WebSphere Application Server (Distributed operating systems), Version 8.5.x section in the WebSphere Application Server 8.5.x Knowledge Center
4. Upgrade to IBM Security Identity Manager Version 6.0.0.4 (Version 6 fix pack 4) or later.
   Follow the instructions in the Installing > Separate system upgrade and data migration section of the Knowledge Center for IBM Security Identity Manager Version 6.0.0.4 or later.