IBM Security Identity Manager Database How To
How to update ITIM MS SQL JDBC driver
Here is an example of what needs to be done for MSSQLJDBC v3
- Stop ITIM (IBM WebSphere Application Server V7.0)
- Stop SQL Server Agent (MSSQLSERVER), SQL Server (MSSQLSERVER)
- Copy sqljdbc_3.0.1301.101\xa\x64\sqljdbc_xa.dll to "x:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqljdbc_xa.dll"
- Start SQL Server Agent (MSSQLSERVER), SQL Server (MSSQLSERVER)
- Executed sqljdbc_3.0.1301.101\xa\xa_install.sql in the SQL Server Management studio
- Copy AND RENAME sqljdbc_3.0.1301.101\sqljdbc4.jar to "x:\Program Files\IBM\itim\lib\sqljdbc.jar"
- Start TIM
How to trim ITIM data manually to a specified time period
If for some reason you are averse to running
F:\Program Files\ibm\itim\bin\win\DBPurge.cmd -age <days>
Then run the following script
use itimdb
ALTER DATABASE itimdb SET RECOVERY SIMPLE
GO
DECLARE @yearsago datetime
SET @yearsago = dateadd(yy, -2, getdate())
RAISERROR ('Counting processes...', 0, 1) WITH NOWAIT
DECLARE @process_count int
SELECT @process_count = count(*) FROM ENROLE.PROCESS ps WHERE (COMPLETED IS NOT NULL
AND convert(datetime, substring(ps.completed, 1, 19)) < @yearsago)
OR (COMPLETED IS NULL AND LASTMODIFIED IS NOT NULL
AND convert(datetime, substring(ps.lastmodified, 1, 19)) < @yearsago)
WHILE @process_count > 0
BEGIN
RAISERROR ('Processing 2000 processes of %i left', 0, 1, @process_count) WITH NOWAIT
CHECKPOINT
DELETE FROM ENROLE.PROCESSLOG WHERE PROCESS_ID IN
(SELECT top 2000 ID FROM ENROLE.PROCESS ps WHERE (COMPLETED IS NOT NULL
AND convert(datetime, substring(ps.completed, 1, 19)) < @yearsago)
OR (COMPLETED IS NULL AND LASTMODIFIED IS NOT NULL
AND convert(datetime, substring(ps.lastmodified, 1, 19)) < @yearsago))
CHECKPOINT
DELETE FROM ENROLE.PROCESSDATA WHERE PROCESS_ID IN
(SELECT top 2000 ID FROM ENROLE.PROCESS ps WHERE (COMPLETED IS NOT NULL
AND convert(datetime, substring(ps.completed, 1, 19)) < @yearsago)
OR (COMPLETED IS NULL AND LASTMODIFIED IS NOT NULL
AND convert(datetime, substring(ps.lastmodified, 1, 19)) < @yearsago))
CHECKPOINT
DELETE FROM ENROLE.WORKITEM WHERE PROCESS_ID IN
(SELECT top 2000 ID FROM ENROLE.PROCESS ps WHERE (COMPLETED IS NOT NULL
AND convert(datetime, substring(ps.completed, 1, 19)) < @yearsago)
OR (COMPLETED IS NULL AND LASTMODIFIED IS NOT NULL
AND convert(datetime, substring(ps.lastmodified, 1, 19)) < @yearsago))
CHECKPOINT
DELETE FROM ENROLE.PENDING WHERE PROCESS_ID IN
(SELECT top 2000 ID FROM ENROLE.PROCESS ps WHERE (COMPLETED IS NOT NULL
AND convert(datetime, substring(ps.completed, 1, 19)) < @yearsago)
OR (COMPLETED IS NULL AND LASTMODIFIED IS NOT NULL
AND convert(datetime, substring(ps.lastmodified, 1, 19)) < @yearsago))
CHECKPOINT
DELETE FROM ENROLE.PASSWORD_TRANSACTION WHERE PROCESS_ID IN
(SELECT top 2000 ID FROM ENROLE.PROCESS ps WHERE (COMPLETED IS NOT NULL
AND convert(datetime, substring(ps.completed, 1, 19)) < @yearsago)
OR (COMPLETED IS NULL AND LASTMODIFIED IS NOT NULL
AND convert(datetime, substring(ps.lastmodified, 1, 19)) < @yearsago))
CHECKPOINT
DELETE FROM ENROLE.WORKFLOW_CALLBACK WHERE PROCESS_ID IN
(SELECT top 2000 ID FROM ENROLE.PROCESS ps WHERE (COMPLETED IS NOT NULL
AND convert(datetime, substring(ps.completed, 1, 19)) < @yearsago)
OR (COMPLETED IS NULL AND LASTMODIFIED IS NOT NULL
AND convert(datetime, substring(ps.lastmodified, 1, 19)) < @yearsago))
CHECKPOINT
DELETE FROM ENROLE.ACTIVITY_LOCK WHERE PROCESS_ID IN
(SELECT top 2000 ID FROM ENROLE.PROCESS ps WHERE (COMPLETED IS NOT NULL
AND convert(datetime, substring(ps.completed, 1, 19)) < @yearsago)
OR (COMPLETED IS NULL AND LASTMODIFIED IS NOT NULL
AND convert(datetime, substring(ps.lastmodified, 1, 19)) < @yearsago))
CHECKPOINT
DELETE FROM ENROLE.ACTIVITY WHERE PROCESS_ID IN
(SELECT top 2000 ID FROM ENROLE.PROCESS ps WHERE (COMPLETED IS NOT NULL
AND convert(datetime, substring(ps.completed, 1, 19)) < @yearsago)
OR (COMPLETED IS NULL AND LASTMODIFIED IS NOT NULL
AND convert(datetime, substring(ps.lastmodified, 1, 19)) < @yearsago))
CHECKPOINT
DELETE FROM ENROLE.PROCESS WHERE ID IN
(SELECT top 2000 ID FROM ENROLE.PROCESS ps WHERE (COMPLETED IS NOT NULL
AND convert(datetime, substring(ps.completed, 1, 19)) < @yearsago)
OR (COMPLETED IS NULL AND LASTMODIFIED IS NOT NULL
AND convert(datetime, substring(ps.lastmodified, 1, 19)) < @yearsago))
CHECKPOINT
SELECT @process_count = count(*) FROM ENROLE.PROCESS ps WHERE (COMPLETED IS NOT NULL
AND convert(datetime, substring(ps.completed, 1, 19)) < @yearsago)
OR (COMPLETED IS NULL AND LASTMODIFIED IS NOT NULL
AND convert(datetime, substring(ps.lastmodified, 1, 19)) < @yearsago)
END
RAISERROR ('Processing reconciliations...', 0, 1) WITH NOWAIT
DECLARE @recon_count int
SELECT @recon_count = count(*) FROM ENROLE.RECONCILIATION rec WHERE STARTED IS NOT NULL AND rec.started < @yearsago
WHILE @recon_count > 0
BEGIN
RAISERROR ('Processing 250 reconciliations of %i left', 0, 1, @recon_count) WITH NOWAIT
CHECKPOINT
DELETE FROM ENROLE.RECONCILIATION_INFO WHERE RECONID IN
(SELECT top 250 RECONID FROM ENROLE.RECONCILIATION rec WHERE STARTED IS NOT NULL AND rec.started < @yearsago)
CHECKPOINT
DELETE FROM ENROLE.RECONCILIATION WHERE RECONID IN
(SELECT top 250 RECONID FROM ENROLE.RECONCILIATION rec WHERE STARTED IS NOT NULL AND rec.started < @yearsago)
CHECKPOINT
SELECT @recon_count = count(*) FROM ENROLE.RECONCILIATION rec WHERE STARTED IS NOT NULL AND rec.started < @yearsago
END
RAISERROR ('Processing remote services requests...', 0, 1) WITH NOWAIT
DECLARE @rsr_count int
SELECT @rsr_count = count(*) FROM enrole.REMOTE_SERVICES_REQUESTS WHERE request_id NOT IN (SELECT cast(id AS varchar (20)) FROM enrole.ACTIVITY)
WHILE @rsr_count > 0
BEGIN
RAISERROR ('Clearing 2000 remote services requests of %i left', 0, 1, @rsr_count) WITH NOWAIT
CHECKPOINT
DELETE FROM enrole.REMOTE_SERVICES_REQUESTS WHERE REQUEST_ID IN
(SELECT top 2000 REQUEST_ID FROM enrole.REMOTE_SERVICES_REQUESTS WHERE request_id NOT IN
(SELECT cast(id AS varchar (20)) FROM enrole.ACTIVITY))
CHECKPOINT
SELECT @rsr_count = count(*) FROM enrole.REMOTE_SERVICES_REQUESTS WHERE request_id NOT IN (SELECT cast(id AS varchar (20)) FROM enrole.ACTIVITY)
END
RAISERROR ('Shrinking the database...', 0, 1, @rsr_count) WITH NOWAIT
DBCC SHRINKDATABASE(itimdb)
ALTER DATABASE itimdb SET RECOVERY FULL
GOAlso note that some events might not be properly cleaned up if they are broken and do not have a timestamp.
How to remove all non-completed processes and actions from TIM
Be aware that running the following also removes Aborted, Suspended, Scheduled and Terminated processes.
delete from enrole.PROCESSLOG where PROCESS_ID in (select ID from enrole.PROCESS where STATE <> 'C')
delete from enrole.PROCESSDATA where PROCESS_ID in (select ID from enrole.PROCESS where STATE <> 'C')
delete from enrole.WORKITEM where PROCESS_ID in (select ID from enrole.PROCESS where STATE <> 'C')
delete from enrole.PENDING where PROCESS_ID in (select ID from enrole.PROCESS where STATE <> 'C')
delete from enrole.PASSWORD_TRANSACTION where PROCESS_ID in (select ID from enrole.PROCESS where STATE <> 'C')
delete from enrole.activity_lock where PROCESS_ID in (select ID from enrole.PROCESS where STATE <> 'C')
delete from enrole.ACTIVITY where PROCESS_ID in (select ID from enrole.PROCESS where STATE <> 'C')
delete from enrole.WORKFLOW_CALLBACK where PROCESS_ID in (select ID from enrole.PROCESS where STATE <> 'C')
delete from enrole.scheduled_message where REFERENCE_ID in (select ID from enrole.PROCESS where STATE <> 'C')
delete from enrole.PROCESS where STATE <> 'C'
delete from enrole.REMOTE_SERVICES_REQUESTS where request_id not in (select cast(id as varchar (20)) from enrole.ACTIVITY)
delete from ITIML000.SIB000
delete from ITIML000.SIB001
delete from ITIML000.SIB002
delete from ITIML000.SIBCLASSMAP
delete from ITIML000.SIBKEYS
delete from ITIML000.SIBLISTING
delete from ITIML000.SIBOWNER
delete from ITIML000.SIBXACTSHow to remedy the missing processes in the table
If you get something like "com.ibm.itim.workflow.engine.AssertionFailure: CTGIMA416E The following process cannot be found in the database. Process ID: 26233666218944200000" Caused by: com.ibm.itim.workflow.model.type.MissingEntity: CTGIMA416E The following process cannot be found in the database. Process ID: 26233666218944200000 Try doing this
insert INTO PROCESS (ROOT_PROCESS_ID, ID, NAME, TYPE, DEFINITION_ID) values (26233666218944200000, 26233666218944200000, 'workaround', 'UC','NONE')
insert INTO PROCESS (ROOT_PROCESS_ID, ID, NAME, TYPE, DEFINITION_ID) values (26233666218944200000, 26233666218944200000, 'workaround', 'UC','NONE')Then investigate the missing processes further
How to analyze performance of an ITIM SQL server
You could leave a Performance Monitor (part of the SQL Server Management Studio) running overnight and then let it figure stuff out. Or you can do the following:
set showplan_all on
go
SELECT top 76 ID, ROOT_PROCESS_ID, PARENT_ID, PARENT_ACTIVITY_ID, NAME, TYPE, DEFINITION_ID, REQUESTER_TYPE, REQUESTER, REQUESTER_NAME, DESCRIPTION, PRIORITY, SCHEDULED, STARTED, COMPLETED, LASTMODIFIED, SUBMITTED, STATE, NOTIFY, REQUESTEE, REQUESTEE_NAME, SUBJECT, COMMENTS, RESULT_SUMMARY, TENANT, SUBJECT_PROFILE, SUBJECT_SERVICE, SUBJECT_ACCESS_ID, SUBJECT_ACCESS_NAME, SHORT_DETAIL, RESULT_DETAIL FROM enrole.PROCESS WHERE PARENT_ACTIVITY_ID = ######################## ORDER BY ID ASC
go
set showplan_all off
go You are interested to see how the columns of the showplan_all output that list the EstimatedIO, EstimatedCPU, and TotalSubtreeCost compare with the values seen in the sql profiler output.On a database on test servers that is very small compared to your database, for example the same query in sql profiler output shows an EstimatedIO value of 0.0046.
sqlcmd -Q "DBCC showcontig "('enrole.process') with tableresults, all_indexes" > process.out
sqlcmd -Q "DBCC showcontig "('enrole.activity') with tableresults, all_indexes" > activity.out
sqlcmd -Q "DBCC showcontig "('enrole.processlog') with tableresults, all_indexes" > processlog.out How to configure DB2 to work with TIM
The thing you have to do is enable shared memory in AIX. DB2 will not run properly if you don't. Add the following to <insthome>/sqllib/db2profile
EXTSHM=ON
export EXTSHM
Add EXTSHM=ON to the /etc/environment filename. From a DB2 command prompt, run the following command:
db2set DB2ENVLIST=EXTSHM
Add the following lines to /home/wasadmin/sqllib/db2profile
EXTSHM=ON
export EXTSHM
Reboot the machine to make the sure the changes take affect. You can reboot the machine by typing shutdown -r now from a terminal as root. After the machine reboots run
db2set DB2ENVLIST=EXTSHM
as root and
wasadmin
How to run dbpurge for ITIM AKA Database maintenance
The IBM Tivoli Identity Manager database stores data for both in-progress and completed system transactions in addition to auditing information. This database grows unbounded and should be cleaned up as your corporate policy allows. For best performance, keep as little data as necessary in the live database and use database backups for older data sets. The DBPurge utility included with the IBM Tivoli Identity Manager product automates removing entries from the database over a certain age. This utility works with all support databases and processes all time-based data, including transaction, audit, and reconciliation records.
Determining the values
- itim_home - The home directory for IBM Tivoli Identity Manager, such as /opt/IBM/itim
- os_type - The operating system time of the IBM Tivoli Identity Manager server, either win or unix.
- days_to_retain - The number of days of data to retain, any records in the database older than this value will be removed.
- purge_trans - A boolean flag indicating if transactional data older than days_to_retain will be removed during the purge. Default: true.
- purge_audit - A boolean flag indicating if the audit data older than days_to_retain will be removed during the purge. Default: true.
- purge_recon - A boolean flag indicating if reconciliation data older than days_to_retain will be removed during the purge. Default: true.
To start the database purging, run the following command all on one line:
itim_home/bin/os_type/DBpurge -age days_to_retain -workflow purge_trans -audit purge_audit -recon purge_recon
The DBPurge utility provided with ITIM 4.6 and 5.0 is used to remove completed transaction records from the ITIM database which completed before a specified date. This is done by accessing all PROCESS records which completed before a specific time, then iterating through these rows and removing related rows in child tables that reference these completed records.
- * NOTE *** DBPurge does not backup or archive records, it just removes them, so be sure to execute the proper database backup prior to executing DBPurge.
The DBPurge utility resides in the following OS specific directories:
- <ITIM_HOME>/bin/unix directory for Unix systems
- <ITIM_HOME>/bin/win directory for Windows systems
The documentation available on ITIM's DBPurge process is currently somewhat limited. The queries used in the DBPurge process should have indexes built for them to prevent full table scans.
@HowTo @ITIM
