IBM Tivoli Identity Manager Database How To
| IBM Security Identity Manager |
|---|
| ITIM How To's |
| General · Database · JavaScript · Pre-5.0 · Workflows · Setup |
| General ITIM Topics |
| How To · Troubleshooting · Technical Notes |
| Related technologies |
| Security Access Manager · Security Directory Integrator · Tivoli Directory Server |
[edit] 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
[edit] 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 (get it here)
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 GO
 |
Note: The events are trimmed by the date of the last modification, not the date of submission, so you might find some audit events older than two years, in case that an action on these events was performed within the two year timeframe. You can see the detailed timestamps on actions on audit events by clicking on it in the ITIM audit log. |
Also note that some events might not be properly cleaned up if they are broken and do not have a timestamp.
[edit] How to remove all non-completed processes and actions from TIM
Be aware that running the following also removes Aborted, Suspended, Shceduled 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.SIBXACTS
[edit] How 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
[edit] 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
[edit] 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
[edit] 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.
|
|||||||||||
