2.4 Installing a Database

The User Application uses a database for various tasks such as storing configuration data and storing data for any workflow activities. Before you can install the Roles Based Provisioning Module and User Application, you must have one of the supported databases for your platform installed and configured. You need to perform these steps:

NOTE:If you are migrating to a new version of the Roles Based Provisioning Module, you must use the same User Application database that you used for the previous installation (that is, the installation from which you are migrating.)

2.4.1 Notes on Configuring a MySQL Database

The User Application requires certain configuration options for MySQL, as described below:

INNODB Storage Engine and Table Types

The User Application uses the INNODB storage engine, which enables you to choose INNODB table types for MySQL. If you create a MySQL table without specifying its table type, the table receives the MyISAM table type by default. To ensure that your MySQL server is using INNODB, verify that my.cnf (Linux or Solaris) or my.ini (Windows) contains the following option:

default-table-type=innodb

It should not contain the skip-innodb option.

As an alternative to setting the default-table-type=innodb option, you can append the ENGINE=InnoDB option to the Create Table statements in the SQL script for your database.

Character Set

Specify UTF-8 as the character set for the whole server or just for a database. Specify UTF-8 on a server-wide basis by including the following option in my.cnf (Linux or Solaris) or my.ini (Windows):

character_set_server=utf8

You can also specify the character set for a database at database creation time, using the following command:

create database databasename character set utf8 collate utf8_bin;

If you set the character set for the database, you must also specify the character set in the JDBC URL in the IDM-ds.xml file, as in the following example:

<connection-url>jdbc:mysql://localhost:3306/databasename?useUnicode=true&amp;characterEncoding=utf8&amp;connectionCollation=utf8_bin</connection-url>

Case Sensitivity

Ensure that case sensitivity is consistent across servers or platforms if you plan to back up and restore data across servers or platforms. To ensure consistency, specify the same value (either 0 or 1) for lower_case_table_names in all your my.cnf (Linux or Solaris) or my.ini (Windows) files, instead of accepting the default (Windows defaults to 0 and Linux defaults to 1.) Specify this value before you create the database to hold the Identity Manager tables. For example, you would specify

lower_case_table_names=1

in the my.cnf and my.ini files for all platforms on which you plan to back up and restore a database.

Ansi Setting

You need to add the ansi entry to your my.cnf (on Linux) or my.ini file (on Windows). If you do not add this entry, the RBPM tables will be created, but the initial data load of the tables will not be performed, and you may see a Guest Container Page definition not found error message.

Here’s what the my.cnf (or my.ini) file should look like after you’ve added the ansi entry:

# These variables are required for IDM User Application
character_set_server=utf8
default-table-type=innodb

# Put the server in ANSI SQL mode.
#See http://www.mysql.com/doc/en/ANSI_mode.html
ansi

To confirm the change to use ansi mode has taken effect, you can execute the following SQL on your MySQL server:

mysql> select @@global.sql_mode;
+-------------------------------------------------------------+
| @@global.sql_mode                                           |
+-------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

User Account Requirements

The user account that is used during the install process must have full access to (be the owner of) the database that will be used by the User Application. In addition, this account will need access to the tables in the system. The tables may vary, depending on your environment.

Create a user to log into the MySQL server and grant privileges to the user, for example:

GRANT ALL PRIVILEGES ON <dbname.>* TO <username>@<host> IDENTIFIED BY ‘password’

The minimum set of privileges is CREATE, INDEX, INSERT, UPDATE, DELETE, and LOCK TABLES. For documentation on the GRANT command, see http://www.mysql.org/doc/refman/5.0/en/grant.html.

IMPORTANT:The user account must also have select rights to the mysql.user table. Here is the SQL syntax needed to give the proper rights:

USE mysql;
GRANT SELECT ON mysql.user TO <username>@<host>;

2.4.2 Notes on Configuring an Oracle Database

When you create your Oracle database, you need to be sure to use AL32UTF8 to specify a Unicode-encoded character set. For more information on choosing a character set, see “Choosing an Oracle Database Character Set”.

When you create a user for your Oracle database, you need to issue the following statements using the SQL Plus utility. These statements create the user and set the user's privileges. Grant the user CONNECT and RESOURCE privileges, for example:

CREATE USER idmuser IDENTIFIED BY password

GRANT CONNECT, RESOURCE to idmuser

UTF-8 on Oracle 11g On Oracle 11g, you can issue the following command to confirm that you are enabled for UTF-8:

select * from nls_database_parameters;

If you are not setup for UTF-8, you will see this data returned:

NLS_CHARACTERSET
WE8MSWIN1252

If you are setup for UTF-8, you will see this data returned:

NLS_CHARACTERSET
AL32UTF8

The User Application requires the specific version of the Oracle JDBC Driver that is included with the installation package for the supported version of the Oracle database.

2.4.3 Notes on Configuring an MS SQL Server Database

Set up your MS SQL Server database as follows:

  1. Install the MS SQL server.

  2. Connect to the server and open an application for creating the database and database user (typically the SQL Server Management Studio application).

  3. Create a database. SQL Server does not allow users to select the character set for databases. The User Application stores SQL Server character data in a NCHAR column type, which supports UTF-8.

  4. Create a login.

  5. Add the login as a user of the database.

  6. Grant these privileges to the login: CREATE TABLE, CREATE INDEX, SELECT, INSERT, UPDATE, and DELETE.

The User Application requires version 3.0 of the Microsoft SQL Server 2008 JDBC Driver. The User Application has been tested specifically with version 3.0.119.0 of the Microsoft SQL Server 2008 JDBC Driver.

NOTE:Only the Sun Solaris, Red Hat Linux, and Windows 2000 operating systems are officially certified with the JDBC driver.

2.4.4 Notes on Configuring a DB2 Database

This section provides notes on DB2 configuration.

Providing the Database Driver JARs

The Database Driver JAR files need to be selected during the installation process on the Database Username and Password screen. However, the browse button for the Database Driver JAR File field only allows you to select one (1) jar. For DB2, you must provide two (2) jars:

  • db2jcc.jar

  • db2jcc_license_cu.jar

Therefore, if you are running the install program against WebSphere (the only Application Server supported with DB2), you can select one jar, but you will have to manually enter the second one using the correct file separator for the operating system that the install program is running on. Alternatively, you can manually enter both entries.

For example, on Windows:

c:\db2jars\db2jcc.jar;c:\db2jars\db2jcc_license_cu.jar

For example, on Solaris and Linux:

/home/lab/db2jars/db2jcc.jar:/home/lab/db2jcc_license_cu.jar

Tuning DB2 Databases to Prevent Deadlocks and Timeouts

When using DB2, if you see an error indicating that the current transaction has been rolled back because of a deadlock or timeout, the problem may be caused by a high level of user and database concurrency.

DB2 provides many techniques for resolving lock conflicts including tuning of the cost-based optimizer. The Performance Guide included in the DB2 Administration documentation is an excellent source that contains much information on the topic of tuning.

There are no prescribed tuning values that can be used for all installations since the level of concurrency and size of data varies. However, here are some DB2 tuning tips that may be relevant for your installation:

  • The reorgchk update statistics command will update the statistics used by the optimizer. Periodic updates of these statistics may be enough to alleviate the problem.

  • Use of the DB2 registry parameter DB2_RR_TO_RS can improve concurrency by not locking the next key of the row that was inserted or updated.

  • Increase the MAXLOCKS and LOCKLIST parameters on the database.

  • Increase the currentLockTimeout property on the database connection pool.

  • Use the Database Configuration Advisor and optimize for faster transactions.

  • Alter all the User Application tables to be VOLATILE to indicate to the optimizer that cardinality of the table will vary significantly. For example, to make the AFACTIVITY table VOLATILE, you might issue the command: ALTER TABLE AFACTIVITY VOLATILE

    The ALTER TABLE commands need to be run after the User Application has been started once and the database tables have been created. Refer to the ALTER TABLE documentation for more information on this statement. Here are the SQL statements for all the User Application tables:

    ALTER TABLE AFACTIVITY VOLATILE
    ALTER TABLE AFACTIVITYTIMERTASKS VOLATILE
    ALTER TABLE AFBRANCH VOLATILE
    ALTER TABLE AFCOMMENT VOLATILE
    ALTER TABLE AFDOCUMENT VOLATILE
    ALTER TABLE AFENGINE VOLATILE
    ALTER TABLE AFENGINESTATE VOLATILE
    ALTER TABLE AFMODEL VOLATILE
    ALTER TABLE AFPROCESS VOLATILE
    ALTER TABLE AFPROVISIONINGSTATUS VOLATILE
    ALTER TABLE AFQUORUM VOLATILE
    ALTER TABLE AFRESOURCEREQUESTINFO VOLATILE
    ALTER TABLE AFWORKTASK VOLATILE
    ALTER TABLE AF_ROLE_REQUEST_STATUS VOLATILE
    ALTER TABLE ATTESTATION_ATTESTER VOLATILE
    ALTER TABLE ATTESTATION_ATTRIBUTE VOLATILE
    ALTER TABLE ATTESTATION_QUESTION VOLATILE
    ALTER TABLE ATTESTATION_REPORT VOLATILE
    ALTER TABLE ATTESTATION_REQUEST VOLATILE
    ALTER TABLE ATTESTATION_RESPONSE VOLATILE
    ALTER TABLE ATTESTATION_SURVEY_QUESTION VOLATILE
    ALTER TABLE ATTESTATION_TARGET VOLATILE
    ALTER TABLE AUTHPROPS VOLATILE
    ALTER TABLE DATABASECHANGELOG VOLATILE
    ALTER TABLE DATABASECHANGELOGLOCK VOLATILE
    ALTER TABLE DSS_APPLET_BROWSER_TYPES VOLATILE
    ALTER TABLE DSS_APPLET_CFG VOLATILE
    ALTER TABLE DSS_APPLET_CFG_MAP VOLATILE
    ALTER TABLE DSS_BROWSER_TYPE VOLATILE
    ALTER TABLE DSS_CONFIG VOLATILE
    ALTER TABLE DSS_EXT_KEY_USAGE_RESTRICTION VOLATILE
    ALTER TABLE DSS_USR_POLICY_SET VOLATILE
    ALTER TABLE JBM_COUNTER VOLATILE
    ALTER TABLE JBM_DUAL VOLATILE
    ALTER TABLE JBM_ID_CACHE VOLATILE
    ALTER TABLE JBM_MSG VOLATILE
    ALTER TABLE JBM_MSG_REF VOLATILE
    ALTER TABLE JBM_POSTOFFICE VOLATILE
    ALTER TABLE JBM_ROLE VOLATILE
    ALTER TABLE JBM_TX VOLATILE
    ALTER TABLE JBM_USER VOLATILE
    ALTER TABLE PORTALCATEGORY VOLATILE
    ALTER TABLE PORTALPORTLETHANDLES VOLATILE
    ALTER TABLE PORTALPORTLETSETTINGS VOLATILE
    ALTER TABLE PORTALPRODUCERREGISTRY VOLATILE
    ALTER TABLE PORTALPRODUCERS VOLATILE
    ALTER TABLE PORTALREGISTRY VOLATILE
    ALTER TABLE PROFILEGROUPPREFERENCES VOLATILE
    ALTER TABLE PROFILEUSERPREFERENCES VOLATILE
    ALTER TABLE PROVISIONING_CODE_MAP VOLATILE
    ALTER TABLE PROVISIONING_CODE_MAP_LABEL VOLATILE
    ALTER TABLE PROVISIONING_VIEW_VALUE VOLATILE
    ALTER TABLE PROVISIONING_VIEW_VALUE_LABEL VOLATILE
    ALTER TABLE SECURITYACCESSRIGHTS VOLATILE
    ALTER TABLE SECURITYPERMISSIONMETA VOLATILE
    ALTER TABLE SECURITYPERMISSIONS VOLATILE
    ALTER TABLE SEC_DELPROXY_CFG VOLATILE
    ALTER TABLE SEC_DELPROXY_SRV_CFG VOLATILE
    ALTER TABLE SEC_SYNC_CLEANUP_QUEUE VOLATILE