Recovering from an ISIM Reconciliation interruption

Answer

PROBLEM:
--------
A recon is fired on a resource (eg, SQL Server) which has many records (about 2000 for example). While the recon is running, ISIM server is killed and thereafter, restart ISIM. The recon will be shown in running state in the Pending request queue and will continue to be in the same state.

SOLUTION:
---------
After a lengthy investigation, it was concluded that a recovery procedure be provided in the unlikely event that this would occur. This problem will only arise when the ISIM server is abruptly terminated while a long-pending recon is running. Hence, the customer may need to execute the serviceunlock.ddl file provided. The .ddl file (serviceunlock.ddl), which contains a database script, should be executed on all DB platforms (ie DB2, Oracle and SQL Server). The script should be preferably run by the Administrator who has complete access to all the tables in the database. This DB script will unlock the service by resetting the appropriate DB columns of the RESOURCE_PROVIDERS table, which will enable the user to execute all the service related operations successfully.
*** NOTE ***
This script also updates the "LOCK_SERVICE" attribute, setting it to zero as well, but this is not necessary for unlocking a reconcilation. This field is the database representative of the "Lock Service During Reconciliation" check box on the Service form, Reconcilation, Schedule page. If you leave the "LOCK_SERVICE = 0" part in the update script this will deselect the mentioned check box and the respective Service will not be 'locked' during reconciliation. If you want the "Lock Service During Reconciliation" check box to remain selected for the Service, then simply update the script by removing the ", LOCK_SERVICE = 0" portion.

Also, in the steps below, the <database owner> is found in the enRoleDatabase.properties file, the database.db.owner parameter.

Common Step for All Platforms :-
--------------------------------
Open the serviceunlock.ddl file in a text editor and copy the Activity Id of the Reconciliation process, which can be obtained in the 'Audit Log' tab by selecting the Request Details icon and Paste it within the '<ACTIVITY_ID>' of the DB script.
The DB Script is specified as:
update <database owner>.RESOURCE_PROVIDERS set RECON_STATUS = 0, LOCK_SERVICE = 0 where REQUEST_ID = '<ACTIVITY_ID>'
An Example test run :-
update <database owner>.RESOURCE_PROVIDERS set RECON_STATUS = 0, LOCK_SERVICE = 0 where REQUEST_ID = '1931045012747657131'

SQL Server:
-----------
Use the Microsoft SQL Server "Query Analyzer" tool, or a supported SQL client to execute the ddl script.
In the Initial Dialog "Connect to SQL Server", specify:
User: <database owner>
Password: <your configured password>
Select menu "File" | "Open..."
File name: <ITIM_HOME>\config\rdbms\mssql\serviceunlock.ddl
Select menu "Query" | "Change Database..."
Select your database name (default is "itim")
Select "OK".
Select menu "Query" | "Execute"
Select "File" | "Exit".

Oracle:
-------
Run the Oracle sqlplus program. Specify your database user password for the enrole user in place of "PASSWORD", your Oracle ITIM instance name in place of "ITIMDB", and your <ITIM_HOME> location.
sqlplus <database owner>/PASSWORD@ITIMDB
> @<ITIM_HOME>\config\rdbms\oracle\serviceunlock.ddl
> quit

DB2:
----
Type db2cmd to get the db2 prompt and on the prompt type the following:-
db2 connect to <dbname> user <database owner> using <password>
db2 -tvf $<ITIM_HOME>/config/rdbms/db2/serviceunlock.ddl

The content of the "serviceunlock.ddl" file is this very simple database 'update' statement:
UPDATE enrole.RESOURCE_PROVIDERS set RECON_STATUS = 0, LOCK_SERVICE = 0 where REQUEST_ID = '<ACTIVITY_ID>'
This "serviceunlock.ddl" file is also attached to this tech note for convenience and reference.