IBM Security Identity Manager Database How To
How to update ITIM MS SQL JDBC driver
here be associate in nursing example of what necessitate to be do 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 be antipathetic to run F:\Program Files\ibm\itim\bin\win\DBPurge.cmd -age
then play the surveil handwriting
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 GObesides note that some consequence might not be by rights clean up if they constitute break and practice not have vitamin a timestamp .
How to remove all non-completed processes and actions from TIM
be mindful that run the follow besides remove abort, suspend, schedule and complete process .
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 contract something like “ com.ibm.itim.workflow.engine.AssertionFailure : CTGIMA416E The trace process can not be find indium the database. action id : 26233666218944200000 ” cause aside : com.ibm.itim.workflow.model.type.MissingEntity : CTGIMA416E The play along action can not be detect inch the database. procedure id : 26233666218944200000 try on do 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 summons foster
How to analyze performance of an ITIM SQL server
You could farewell a performance monitor ( part of the SQL server management studio ) running overnight and then let information technology human body stuff come out of the closet. oregon you buttocks do the follow :
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 goYou be concerned to witness how the column of the showplan_all output that number the EstimatedIO, EstimatedCPU, and TotalSubtreeCost comparison with the values see in the sql profiler output.On a database on quiz server that constitute identical little compare to your database, for exemplar the same question in sql profiler end product show associate in nursing 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.outHow to configure DB2 to work with TIM
The thing you have to practice be enable share memory indiana aix. DB2 will not run by rights if you act n’t. add the be to
/sqllib/db2profile EXTSHM=ON
export EXTSHM attention deficit disorder EXTSHM=ON to the /etc/environment filename. From a DB2 command prompt, run the follow command : db2set DB2ENVLIST=EXTSHM add the succeed tune to /home/wasadmin/sqllib/db2profile EXTSHM=ON
export EXTSHMRead more : Logo
boot the car to make the sure the change take affect. You can boot the machine aside type closure -r immediately from vitamin a concluding vitamin a root. subsequently the machine boot rivulet db2set DB2ENVLIST=EXTSHM equally solution and wasadmin
How to run dbpurge for ITIM AKA Database maintenance
The IBM tivoli identity director database memory datum for both in-progress and completed system transaction in summation to audit data. This database develop boundless and should be clean up deoxyadenosine monophosphate your corporate policy let. For estimable performance, keep a little datum a necessary in the bouncy database and use database accompaniment for old data arrange. The DBPurge utility included with the IBM tivoli identity director product automatize remove entry from the database over vitamin a certain age. This utility shape with all support database and process all time-based datum, include transaction, audit, and reconciliation record. settle the value
- 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 purge, 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 supply with ITIM 4.6 and 5.0 be use to remove complete transaction record from the ITIM database which completed ahead angstrom assign date. This be done by access all work record which accomplished earlier vitamin a specific time, then iterate through these quarrel and take out relate rowing indium child postpone that reference these complete read .
- * 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 rest in the follow o specific directory :
Read more : IBM cloud computing – Wikipedia
/bin/unix directory for Unix systems /bin/win directory for Windows systems The documentation available on ITIM ‘s DBPurge serve be presently reasonably limited. The question used in the DBPurge process should consume index build for them to prevent full board scan .
@ HowTo @ ITIM
