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:
Install your database and database driver.
When you install the User Application, you need to specify a driver JAR file that has been provided by the database vendor for the particular database you are using. Driver JAR files provided by third-party vendors are not supported.
Create a database or a database instance.
Record the following database parameters for use in the installation procedure for the User Application:
host and port
database name, username, and user password
Create a datasource file that points to the database.
The method varies according to your application server. For JBoss, the User Application install program creates an application server datasource file pointing to the database and names the file based on the name of the Identity Manager Roles Based Provisioning Module WAR file. For WebSphere and WebLogic, configure the datasource manually prior to the install.
Enable the database for Unicode encoding.
The User Application requires that the database character set use Unicode encoding. For example, UTF-8 is an example of a character set that uses Unicode encoding, but Latin1 does not use Unicode encoding. Before installing the User Application, verify that your database is configured with a character set that has Unicode encoding.
Be sure not to use case-insensitive collation.
Case-insensitive collation is not supported. If you use case-insensitive collation, you might encounter duplicate key errors during migration. If a duplicate key error is encountered, check the collation and correct it, then re-install the User Application.
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.)
The User Application requires certain configuration options for MySQL, as described below:
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.
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&characterEncoding=utf8&connectionCollation=utf8_bin</connection-url>
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.
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)
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>;
When you create your Oracle database, you need to be sure to use AL32UTF8 to specify a Unicode-encoded character set. (See AL32UTF8.)
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
Set up your MS SQL Server database as follows:
Install the MS SQL server.
Connect to the server and open an application for creating the database and database user (typically the SQL Server Management Studio application).
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.
Create a login.
Add the login as a user of the database.
Grant these privileges to the login: CREATE TABLE, CREATE INDEX, SELECT, INSERT, UPDATE, and DELETE.
The User Application requires version 3.0.3.0.1119.0 of the Microsoft SQL Server 2008 JDBC Driver. Note that only the Sun Solaris, Red Hat Linux, and Windows 2000 or later operating systems are officially supported with this JDBC driver.
This section provides notes on DB2 configuration.
The Database Driver JAR files need to be selected during the installation process on the
screen. However, the browse button for the 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
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