=========================================================================
    IBM Security Access Manager for Enterprise Single Sign-On (ISAMESSO)
        Synchronization Data Purging Housekeeping Maintenance Script
 =========================================================================
 
	Release date 		: 04-May-2012
	Script version 		: 1.0.0
	Target IMS version 	: 8.1.x / 8.2.x
	Target Database		: DB2 9.5 / 9.7

	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.


	Choose one of the following methods to create the stored procedure:
         1.1  Using DB2 Command Shell
         1.2. Using DB2 Command Editor UI


	1.1 Using DB2 Command Shell
	----------------------------

	Enter the following commands in the DB2 command shell.
        1.1.1 connect to the target database. 
              Replace <database name>, <user id> and <password> in the following 
		  script and execute it.
		  
		  db2 connect to <database name> user <user id> using <password>
		  		
	1.1.2 set current schema to ISAMESSO schema.
              Replace <ISAMESSO schema name> and execute the following command.
	      
	      
		  db2 set current schema = '<ISAMESSO schema name>'
	
	1.1.3 Replace the <script location> and execute the following command.
	
		  db2 -td# -f <script-location>sp_purgeDelSyncDataOlderThan.sql 
	
	
	1.2 Using DB2 Command Editor UI
	--------------------------------

	1.2.1 Start DB2 control center and launch the DB2 command editor. 
              In the DB2 Control Center, select "Tools>Command Editor". 
	      
	1.2.2 Connect to the target database
	      Replace <database name>, <user id> and <password> in the following 
	      script and execute it.		  
		  
		connect to <database name> user <user id> using <password>
		  
	1.2.3 Set current schema to ISAMESSO schema
	      Replace <ISAMESSO schema name> and execute the following command 
	
		  set current schema = '<ISAMESSO schema name>'
	
	1.2.4 Open the script "sp_purgeDelSyncDataOlderThan"
	      In the command editor, Select "Selected > Open". 
	      Browse to the script "sp_purgeDelSyncDataOlderThan" and open it.
	      
	1.2.5 Change the "statement termination character" at the bottom to "#"
	
	1.2.6 Execute the script and make sure the stored procedure is created 
		  successfully.
		  
		  
 2. Verify Stored Procedure Creation
	
	Execute the following script to verify that the stored procedure is created successfully.
	
		call 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.1		       
    Database version : DB2 v9.7.400.501			     
	--------------------------------------------------------------------
	
 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 : 
	
	CALL 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:
	
		CALL sp_purgeDelSyncDataOlderThan('SIMULATE',30)
	
	2. To purge deleted sync data older than 2 weeks (14 days) from database use:
	
		CALL sp_purgeDelSyncDataOlderThan('DELETE', 14)
		
	3. To purge all deleted sync data from database use:
	
		CALL sp_purgeDelSyncDataOlderThan('DELETE', 0)