=========================================================================
    IBM Security Access Manager for Enterprise Single Sign-On (ISAMESSO)
        Synchronization Data Purging Housekeeping Maintenance Script
 =========================================================================
 
	Release date 		: 20 Mar 2011
	Script version 		: 1.0.0
	Target IMS version 	: 8.1.x / 8.2.x
	Target Database		: Ms SQL Server 2005/2008

	The sync data housekeeping maintenance script removes sync data that are marked
  	as deleted, such as user, machine, and system scope policies with a specified number 
  	of days.

  	AccessAgent refers to information marked as "deleted" to delete cached Wallet data. 
  	It is a suggested practice to purge deleted sync data that are at least 14 days old.   
  
 1. Creating the stored procedure

	Create this stored procedure in the ISAMESSO schema.
	
	Make sure the database login account that you use in the following tasks has: 
	 * Current schema set to ISAMESSO schema. 
         * Privileges to 
	   - create,drop,execute on stored procedure
	   - select,insert,delete,update on ISAMESSO database tables.


	1.1 Using Microsoft SQL Server Management Studio
	-------------------------------------------------

	1.1.1 Open the script "sp_purgeDelSyncDataOlderThan"
	      In the Microsoft SQL Server Management Studio, Select "File > Open". 
	      Browse to the script "sp_purgeDelSyncDataOlderThan" and open it. 

	1.1.2 Replace <TARGET DATABASE NAME>, <ISAMESSO SCHEMA OWNER> and Insert the 
	      following command at the first line in "sp_purgeDelSyncDataOlderThan.sql"
		  ----------------------------
		  USE <TARGET DATABASE NAME>
		  GO
		  EXECUTE AS USER = '<ISAMESSO SCHEMA OWNER>';
		  GO
		  ----------------------------
	1.1.3 Execute the script and make sure "Command(s) completed successfully." is displayed.
		  
 2. Verify Stored Procedure Creation
	
	Execute the following script to verify that the stored procedure is created successfully.
	
	----------------------------
	USE <TARGET DATABASE NAME>
	GO
	EXECUTE AS USER = '<ISAMESSO SCHEMA OWNER>';
	GO
	EXEC sp_purgeDelSyncDataOlderThan 'ABOUT',0
	----------------------------
	
	If the stored procedure is created successfully, you will get an output similar to:
	--------------------------------------------------------------------
	ISAMESSO Sync Data Housekeeping Maintenance Script					   
	Database Name    : IMS_DB						 			   
	Schema Name      : IMS_SCHEMA					 			   	
    Script version   : 1.0.0									   
    Database version : Microsoft SQL Server 2005 - 9.00.4035.00  
	--------------------------------------------------------------------
	
 3. Using the Maintenance Script

 	Before Executing the script make sure the following 
	  * current schema is pointing to ISAMESSO schema.
	    To set the current schema to ISAMESSO schema use the following command:

		EXECUTE AS USER = '<ISAMESSO SCHEMA OWNER>';


	  * Database account that is used has the following privileges:
	      - select, insert, delete, update data in ISAMESSO schema
	      - execute stored procedure 
	
	Maintenance Script Syntax : 
	
	EXEC sp_purgeDelSyncDataOlderThan '<del mode>',<days> 

         where:
 
    	<del mode> - Mandatory, text, case sensitive. Replace <del mode> with one of the following values
			'ABOUT'      Displays script information
			'DELETE'     Performs actual purging 
			'HELP'       Displays script syntax and example
			'SIMULATE'   Simulates purging without actual removal
				 
    	<days> - Mandatory, numeric. Replace <days> with one of the following values
           		0 	     Purges all deleted sync data records.
			X      		 Purges deleted sync data older than 'X' number of days. 

	
 4. Example 

	1. To simulate purging of deleted synchronization data older than 30 days without performing 
	   an actual purging. Use:
	
		EXEC sp_purgeDelSyncDataOlderThan 'SIMULATE',30 
	
	2. To purge deleted sync data older than 2 weeks (14 days) from database use:
	
		EXEC sp_purgeDelSyncDataOlderThan 'DELETE', 14 
		
	3. To purge all deleted sync data from database use:
	
		EXEC sp_purgeDelSyncDataOlderThan 'DELETE', 0