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

Answer

The upgrade and migrate process requires several steps. At then end of the process you are running Identity Manager Version 6 with DB2 and WebSphere Application Server Version 8.5. Those product versions are required to run Identity Service Center (ISC).

Note: The product name changed from IBM Tivoli Identity Manager to IBM Security Identity Manager starting with Version 6 and Version 7 VA.

Process overview

Please review this technote in full before starting the process of upgrading and migrating.

1. Upgrade from Identity Manager Version 5 to Identity Manager Version 6.0.0. Skip this step if you are already running Identity Manager Version 6.0.0 or Identity Manager Version 6.0.0.1.
2. Migrate the database from Microsoft SQL Server to IBM DB2. This step contains several sub-steps.
3. Upgrade WebSphere Application Server to Version 8.5.
4. Upgrade to Identity Manager Version 6.0.0.4 (Version 6 fix pack 4).

This document uses <isim-home> to refer to the installation directory where you installed Identity Manager. Example locations of <isim-home> are as follows:

• Windows systems: C:\IBM\isim
• UNIX and Linux systems: /opt/IBM/isim

1. Upgrade from Identity Manager Version 5 to Identity Manager Version 6.0.0.

Skip this step if you are already running Identity Manager Version 6.0.0 or Identity Manager Version 6.0.0.1.

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

2. Migrate the database from Microsoft SQL Server to IBM DB2.

Follow steps 2.1 to 2.11 below.

Important: DB2 must be installed before you use this procedure. See the Knowledge Center for your version of DB2 on UNIX, Linux, or Windows.

2.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 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.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> using logsecond 12  db2 update db cfg for <itim_dbname> using logfilsiz 10000  db2 update db cfg for <itim_dbname> using auto_runstats off  db2 disconnect current  

2.3. Run the enrole_admin_template script

1. Go to the db2 directory in the 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>/config/rdbms/db2/enrole_admin_template.sql  

2.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-01.ibm.com/marketing/iwm/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.

2.5. Change the extracted database schema

• The extracted schema is saved in the db2tables.sql file in the IDMT output directory. You must 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 command:

       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%'    

• Modify the SQL queries that are used to create the foreign key.
  • Remove the clauses ON UPDATE RESTRICT and ON DELETE RESTRICT in the db2fkeys.sql file for the following queries:

    •   ALTER TABLE USERRECERT_GROUP ADD CONSTRAINT USERRECERT_GRP_FK   FOREIGN KEY (RECERT_ID)   REFERENCES USERRECERT_HISTORY (ID);  

    •   ALTER TABLE SOD_RULE_ROLE ADD CONSTRAINT "FK46_SOD_RULE_ROLE"   FOREIGN KEY ( "POLICY_RULE_ID"  )   REFERENCES SOD_RULE ( "ID" );  

    •   ALTER TABLE SOD_VIOLATION_ROLE_MAP ADD CONSTRAINT "FK34_SOD_VIOLATION_ROLE_MAP"  FOREIGN KEY (  "VIOLATION_ID"  )   REFERENCES SOD_VIOLATION_HISTORY (  "ID"   );  

  • Remove the following entire query from the db2pkeys.sql file.

      ALTER TABLE SOD_VIOLATION_STATUS ADD CONSTRAINT UK1_SOD_VIOLATION_STATUS   UNIQUE ( "RULE_GLOBAL_ID");  

2.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/  

4. The output folder of IDMT tool contains db2views.db2 script containing SQL statements to create views. Through out the whole file, comment out the lines "() AS" that are present before the start of CREATE VIEW sql statement.
5. Manually execute this file by using the following command.

     db2 -tvdf FILE_PATH\db2views.db2  

2.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

2.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 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  

2.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 general form of the JDBC driver URL is as follows:

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

2.10. Create the DB2 data source and enter the database user name and password

Run the following command on the Identy 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.

The runconfig encrypts the password, updates enRoleDatabase.properties with the user and password values, and creates the data source.

2.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 \db2dropexceptiontables.sql

• UNIX and Linux systems:

  db2 -tvdf /db2dropexceptiontables.sql

2.12. Update generated columns in the migrated database

Save the attached generated_col_migration.ddl script, then run it. The examples show the script saved in the system root directory.

generated_col_migration.ddl

• Windows systems:

    db2 -tvdf C:\generated_col_migration.ddl   

• UNIX and Linux systems:

    db2 -tvdf /generated_col_migration.ddl  

  Note: During execution of generated_col_migration.ddl script, if you encounter a warning message such as

  "SQL3601W The statement caused one or more tables to automatically be places in the Set Integrity Pending State SQLSTATE=01586",

  then execute the following statements:

        SET INTEGRITY FOR T_AccessCatalog IMMEDIATE CHECKED;      SET INTEGRITY FOR T_ProvisioningPolicy IMMEDIATE CHECKED;      SET INTEGRITY FOR T_ServiceEntitlement IMMEDIATE CHECKED;      SET INTEGRITY FOR T_ServiceTags IMMEDIATE CHECKED;      SET INTEGRITY FOR T_AttributeEntitlement IMMEDIATE CHECKED;      SET INTEGRITY FOR T_PolicyMembership IMMEDIATE CHECKED;      SET INTEGRITY FOR TMP_HOSTSEBYPERSON IMMEDIATE CHECKED;      SET INTEGRITY FOR TMP_JSAEBYPERSON IMMEDIATE CHECKED;      SET INTEGRITY FOR T_Owner IMMEDIATE CHECKED ;

3. Upgrade WebSphere Application Server to Version 8.5.

Follow the instructions in the WebSphere Application Server Knowledge Center

Note: Upgrading the WebSphere Application Server to version 8.5 from the base installer might corrupt the schema or data. Follow the following steps to upgrade the WebSphere Application Server to version 8.5:

1. Backup the data folder of the IBM Security Identity Manager that is installed on the WebSphere Application Server 8.5.
2. Replace the data folder of a target setup (IBM Security Identity Manager with WebSphere Application Server 8.5) with the data folder of an earlier setup (IBM Security Identity Manager with an earlier version of the WebSphere Application Server).
3. Clear all the data from SIB tables.
4. From the enrole.properties file, change the WebSphere Application Server URL to point to the WebSphere Application Server 8.5 profile.
5. Update the database details from the WebSphere Application Server console. For example,

   WAS Console->Resources->JDBC->DataSource.

6. Restart the WebSphere Application Server.

IBM Security Identity Manager with WebSphere Application Server 8.5 points to the database and LDAP of IBM Security Identity Manager with an earlier version of the WebSphere Application Server.

4. Upgrade to Identity Manager Version 6.0.0.4 (Version 6 fix pack 4).

Follow the instructions in the Installing > Separate system upgrade and data migration section of the Knowledge Center for Identity Manager Version 6.0.0.4