IBM Tivoli Identity Manager Database How To

From KnowWiki
Jump to: navigation, search

Contents

[edit] How to update ITIM MS SQL JDBC driver

Here is an example of what needs to be done for MSSQLJDBC v3

  1. Stop ITIM (IBM WebSphere Application Server V7.0)
  2. Stop SQL Server Agent (MSSQLSERVER), SQL Server (MSSQLSERVER)
  3. 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"
  4. Start SQL Server Agent (MSSQLSERVER), SQL Server (MSSQLSERVER)
  5. Executed sqljdbc_3.0.1301.101\xa\xa_install.sql in the SQL Server Management studio
  6. Copy AND RENAME sqljdbc_3.0.1301.101\sqljdbc4.jar to "x:\Program Files\IBM\itim\lib\sqljdbc.jar"
  7. 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

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.

Personal tools
Namespaces

Variants
Actions
Navigation
Toolbox