IBM Support

Technique for removing pending transactions for an ISIM 6.0 or 7.0 system

Question & Answer


Question

What steps are required to remove all pending transactions for a ISIM 6.0 or 7.0 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 will remove ALL pending requests from ISIM. Please be certain before proceeding that there are no underlying problems that need to be addressed (e.g.Websphere, database, ldap, performance, etc.). If there are underlying issues, removing pending transactions may not resolve the issue.


1. BACKUP YOUR DATABASE PRIOR TO MAKING ANY OF THE FOLLOWING CHANGES.

2. Stop the Websphere appserver (if single-server environment) or clusters and nodeadents (if clustered environment).

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

4. Delete pending rows from the ITIM 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: For Oracle databases a system administrator account should be used
to performed the deletes from the SIB tables.

  • 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, you will need to 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.0, starting with version 7.0.1, the transaction logs can be cleared via the following command line interface (CLI) commands:

    • isim > logs > clear_tranlog





  •  

7. Restart the Websphere environment
 

[{"Business Unit":{"code":"BU008","label":"Security"},"Product":{"code":"SSRMWJ","label":"IBM Security Identity Manager"},"Component":"Server","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"6.0;7.0","Edition":"Edition Independent"}]

Product Alias/Synonym

ITIM;TIM;ISIM;SIM

Document Information

Modified date:
08 January 2019

UID

swg21380384