IBM Support

Technique for removing pending transactions for an ISVG IM 10.x or ISIM 6.x, 7.x system

Question & Answer


Question

What steps are required to remove all pending transactions for an ISVG-IM 10.x or an ISIM 6.x, 7.x system?

Cause

For a variety of reasons, particularly during testing, it may be necessary or desirable to remove all pending transactions.

Answer

NOTE: Following this procedure, ALL pending requests from ISIM will be removed. Before proceeding, please ensure that no underlying problems need to be addressed (e.g., Websphere, database, LDAP, performance, etc.). Removing pending transactions may not resolve the issue if there are underlying issues.


1. BACK UP YOUR DATABASE BEFORE MAKING ANY OF THE FOLLOWING CHANGES.

2. Stop the Websphere appserver (if single-server environment) or clusters and nodeadents (if clustered environment). Stop "Identity Manager" server from home page of any/all ISVG-IM/ISIM appliances in cluster.

3. Stop and restart the ISIM database to ensure the exclusive lock on the SIBOWNER table(s) is released.

4. Delete pending rows from the ISIM database using the following SQL statements, substituting your table owner for 'itimuser' as needed.

(Note: SQL statements may be adapted for use with other supported databases to other supported databases)

db2 connect to <your itimdb name> user <yourid> using <password>

db2 "delete from itimuser.WORKITEM where PROCESS_ID in (select ID from itimuser.PROCESS where STATE in ('I','R'))"

db2 "delete from itimuser.activity_lock where PROCESS_ID in (select ID from itimuser.PROCESS where STATE in ('I','R'))"

db2 "delete from itimuser.PROCESSLOG where PROCESS_ID in (select ID from itimuser.PROCESS where STATE in ('I','R'))"

db2 "delete from itimuser.PROCESSDATA where PROCESS_ID in (select ID from itimuser.PROCESS where STATE in ('I','R'))"

db2 "delete from itimuser.PENDING where PROCESS_ID in (select ID from itimuser.PROCESS where STATE in ('I','R'))"

db2 "delete from itimuser.PASSWORD_TRANSACTION where PROCESS_ID in (select ID from itimuser.PROCESS where STATE in ('I','R'))"

db2 "delete from itimuser.ACTIVITY where PROCESS_ID in (select ID from itimuser.PROCESS where STATE in ('I','R'))"

db2 "delete from itimuser.WORKFLOW_CALLBACK where PROCESS_ID in (select ID from itimuser.PROCESS where STATE in ('I','R'))"

db2 "delete from itimuser.SYNCH_POINT where PROCESS_ID in (select ID from itimuser.PROCESS where STATE in ('I','R'))"

db2 "delete from itimuser.AUDIT_MGMT_PROVISIONING where EVENT_ID in (select ID from itimuser.AUDIT_EVENT where WORKFLOW_PROCESS_ID IN (select ID from itimuser.PROCESS where STATE in ('I','R')))"

db2 "delete from itimuser.AUDIT_MGMT_TARGET where EVENT_ID in (select ID from itimuser.AUDIT_EVENT where WORKFLOW_PROCESS_ID IN (select ID from itimuser.PROCESS where STATE in ('I','R')))"

db2 "delete from itimuser.AUDIT_MGMT_DELEGATE where EVENT_ID in (select ID from itimuser.AUDIT_EVENT where WORKFLOW_PROCESS_ID IN (select ID from itimuser.PROCESS where STATE in ('I','R')))"

#BEGIN DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704 will occur if your version of ITIM is less than 6.0.0.2.

db2 "delete from itimuser.AUDIT_MGMT_PARTICIPANT where EVENT_ID in (select ID from itimuser.AUDIT_EVENT where WORKFLOW_PROCESS_ID IN (select ID from itimuser.PROCESS where STATE in ('I','R')))"

db2 "delete from itimuser.AUDIT_MGMT_ACTIVITY where EVENT_ID in (select ID from itimuser.AUDIT_EVENT where WORKFLOW_PROCESS_ID IN (select ID from itimuser.PROCESS where STATE in ('I','R')))"

db2 "delete from itimuser.AUDIT_MGMT_MESSAGE where EVENT_ID in (select ID from itimuser.AUDIT_EVENT where WORKFLOW_PROCESS_ID IN (select ID from itimuser.PROCESS where STATE in ('I','R')))"

db2 "delete from itimuser.AUDIT_MGMT_ACCESS_REQUEST where EVENT_ID in (select ID from itimuser.AUDIT_EVENT where WORKFLOW_PROCESS_ID IN (select ID from itimuser.PROCESS where STATE in ('I','R')))"

db2 "delete from itimuser.AUDIT_MGMT_LEASE where EVENT_ID in (select ID from itimuser.AUDIT_EVENT where WORKFLOW_PROCESS_ID IN (select ID from itimuser.PROCESS where STATE in ('I','R')))"

db2 "delete from itimuser.AUDIT_MGMT_OBLIGATION_ATTRIB where EVENT_ID in (select ID from itimuser.AUDIT_EVENT where WORKFLOW_PROCESS_ID IN (select ID from itimuser.PROCESS where STATE in ('I','R')))"

db2 "delete from itimuser.AUDIT_MGMT_OBLIGATION_RESOURCE where EVENT_ID in (select ID from itimuser.AUDIT_EVENT where WORKFLOW_PROCESS_ID IN (select ID from itimuser.PROCESS where STATE in ('I','R')))"

db2 "delete from itimuser.AUDIT_MGMT_OBLIGATION where EVENT_ID in (select ID from itimuser.AUDIT_EVENT where WORKFLOW_PROCESS_ID IN (select ID from itimuser.PROCESS where STATE in ('I','R')))"

#END DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704 will occur if your version of ITIM is less than 6.0.0.2.


db2 "delete from itimuser.AUDIT_EVENT where WORKFLOW_PROCESS_ID in (select ID from itimuser.PROCESS where STATE in ('I','R'))"

db2 "delete from itimuser.SCHEDULED_MESSAGE where REFERENCE_ID in (select ID from itimuser.PROCESS where STATE in ('I','R'))"

db2 "delete from itimuser.LCR_INPROGRESS_TABLE where CHILD_ID in (select ID from itimuser.PROCESS where STATE in ('I','R'))"

db2 "delete from itimuser.PROCESS where STATE in ('I','R')"

db2 "delete from itimuser.REMOTE_SERVICES_REQUESTS"

db2 "delete from itimuser.CONCURRENT_LOCKS"

db2 commit;


5. Delete all rows from the SIB tables.

Note: A system administrator account should be used for Oracle databases to perform the deletes from the SIB tables.

Note: Every ISVG-IM/ISIM appliance is a cluster, even if it is a single appliance.

  • For a single-server configuration:

    db2 connect to <your itimdb name> user <yourid> using <password>
    db2 "delete from ITIML000.SIB000"
    db2 "delete from ITIML000.SIB001"
    db2 "delete from ITIML000.SIB002"
    db2 "delete from ITIML000.SIBCLASSMAP"
    db2 "delete from ITIML000.SIBKEYS"
    db2 "delete from ITIML000.SIBLISTING"
    db2 "delete from ITIML000.SIBOWNER"
    db2 "delete from ITIML000.SIBOWNERO"
    db2 "delete from ITIML000.SIBXACTS"
    db2 commit

    For a clustered configuration:

    db2 connect to <your itimdb name> user <yourid> using <password>
    db2 "delete from ITIML000.SIB000"
    db2 "delete from ITIML000.SIB001"
    db2 "delete from ITIML000.SIB002"
    db2 "delete from ITIML000.SIBCLASSMAP"
    db2 "delete from ITIML000.SIBKEYS"
    db2 "delete from ITIML000.SIBLISTING"
    db2 "delete from ITIML000.SIBOWNER"
    db2 "delete from ITIML000.SIBOWNERO"
    db2 "delete from ITIML000.SIBXACTS"
    db2 "delete from ITIML001.SIB000"
    db2 "delete from ITIML001.SIB001"
    db2 "delete from ITIML001.SIB002"
    db2 "delete from ITIML001.SIBCLASSMAP"
    db2 "delete from ITIML001.SIBKEYS"
    db2 "delete from ITIML001.SIBLISTING"
    db2 "delete from ITIML001.SIBOWNER"
    db2 "delete from ITIML001.SIBOWNERO"
    db2 "delete from ITIML001.SIBXACTS"
    db2 "delete from ITIML002.SIB000"
    db2 "delete from ITIML002.SIB001"
    db2 "delete from ITIML002.SIB002"
    db2 "delete from ITIML002.SIBCLASSMAP"
    db2 "delete from ITIML002.SIBKEYS"
    db2 "delete from ITIML002.SIBLISTING"
    db2 "delete from ITIML002.SIBOWNER"
    db2 "delete from ITIML002.SIBOWNERO"
    db2 "delete from ITIML002.SIBXACTS"
    db2 "delete from ITIMS000.SIB000"
    db2 "delete from ITIMS000.SIB001"
    db2 "delete from ITIMS000.SIB002"
    db2 "delete from ITIMS000.SIBCLASSMAP"
    db2 "delete from ITIMS000.SIBKEYS"
    db2 "delete from ITIMS000.SIBLISTING"
    db2 "delete from ITIMS000.SIBOWNER"
    db2 "delete from ITIMS000.SIBOWNERO"
    db2 "delete from ITIMS000.SIBXACTS"
    db2 commit

    Note: If you have more than three application server nodes, repeat the following for each additional node above the third.

    <schema> will be "ITIML00n" where n=node number minus 1
    example: node 4 in a cluster will have schema ITIML003, etc.

    db2 "delete from <schema>.SIB000"
    db2 "delete from <schema>.SIB001"
    db2 "delete from <schema>.SIB002"
    db2 "delete from <schema>.SIBCLASSMAP"
    db2 "delete from <schema>.SIBKEYS"
    db2 "delete from <schema>.SIBLISTING"
    db2 "delete from <schema>.SIBOWNER"
    db2 "delete from <schema>.SIBOWNERO"
    db2 "delete from <schema>.SIBXACTS"


6. Delete the "log1" and "log2" files located under both tranlog and partnerlog directories for each server and on each node.

tranlog and partnerlog directories may be located within:
 
  $WAS_HOME/profiles/<your_profile>/tranlog/<cell>/<node>/<server>/transaction
Note: For ISIM 7.x, starting with version 7.0.1, the transaction logs can be cleared via the following command line interface (CLI) commands:
  isim > logs > clear_tranlog
Note: For ISVG-IM 10.x, the transaction logs can be cleared via the following command line interface (CLI) commands:
  im > logs > clear_tranlog

7. Restart the Websphere environment or Identity Managers servers on ISVG-IM 10.x or ISIM 7.x appliances.
 

[{"Line of Business":{"code":"LOB24","label":"Security Software"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSRMWJ","label":"IBM Security Identity Manager"},"ARM Category":[{"code":"a8m0z000000cxl5AAA","label":"Identity Manager-\u003EAdministering"}],"Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"All Versions","Type":"MASTER"}]

Product Synonym

ITIM;TIM;ISIM;SIM;ISVG IM

Document Information

Modified date:
14 February 2024

UID

swg21380384