=========================================================================
    IBM Security Access Manager for Enterprise Single Sign-On (ISAMESSO)
                 Cached Wallet Maintenance Script
 =========================================================================
 
	Release date 		: 02 Aug 2012
	Script version 		: 1.0.4
	Target IMS version 	: 8.1.x / 8.2.x
	Target Database		: DB2 9.7

	The cached wallet maintenance script removes expired/duplicated cached 
	wallets based on the creation host name with the condition that it must be
    greater than 30 days from the creation time.
	
	In order to improve the efficiency of the maintenance script, 'SIMULATE' and 
	'DELETE' modes are designed to work with random sets of users when <max users> 
	is greater than 0. This leads to possibilities of different execution results that 
	shares the same parameter value of <max users>.


 1. Creating a stored procedure

	Create a stored procedure in the ISAMESSO schema.
	
	Make sure the database login account that uses the following tasks have a
	current schema that points to the ISAMESSO schema and with the following privileges:
	 - Create,drop,execute on a stored procedure
	 - Create,drop,select,insert,delete,update on a session temporary table
	 - Select,insert,delete,update on ISAMESSO database tables.
	 
	Choose either 1.1 or 1.2 steps to create a stored procedure.

	1.1 Using the DB2 Command Shell
	----------------------------
	1.1.1 Replace <database name>, <user id> and <password> in the following 
		  script and execute it to connect to the target database.
		  
		  db2 connect to <database name> user <user id> using <password>
		  		
	1.1.2 Replace the <script location> and execute the following command.
	
		  db2 -td# -f <script-location>sp_pruneCachedWallet.sql 
	
	
	1.2 Using the DB2 Command Editor UI
	--------------------------------
	1.2.1 Start DB2 control center, select "Tools \ Command Editor" from 
	      the top menu to launch the DB2 command editor. 
	      
	1.2.2 Replace <database name>, <user id>, and <password> in the following 
		  script and execute it to connect to the target database.
		  
		  connect to <database name> user <user id> using <password>
		  
	1.2.3 Select "Selected \ Open" from the top menu and browse to the
	      "sp_pruneCachedWallet.sql" script location to open it.
	      
	1.2.4 Change the "statement termination character" at the bottom to "#"
	
	1.2.5 Execute the script and make sure the stored procedure is created 
		  successfully.
		  
		  
 2. Verifying the creation of a stored procedure
	
	Once the stored procedure is created successfully, execute the following 
	command to verify the script creation:
	
	call sp_pruneCachedWallet('ABOUT','',0)
	
	You must get the similar output as the following if the script is created successfully.
	------------------------------------------------------
	-  ISAMESSO Cached Wallet Maintenance Script 		 -
	-   - Database Name    : IMS_DB						 -
	-   - Schema Name      : IMS_SCHEMA					 -	
	-	- Script version   : 1.0.0						 -
    -	- Database version : DB2 v9.7.400.501			 -
	------------------------------------------------------
	
	
 3. Using the maintenance script

	Before executing the script make sure of the following:
	  * The current schema points to the ISAMESSO schema.
	    To set the current schema to ISAMESSO schema, use the following command:

		set current schema = '<SAMESSO schema name>'

	  * The database account that is used has the following privileges:
	      - Select, insert, delete, update data in the ISAMESSO schema
	      - Execute stored procedure 
	 
	Maintenance Script Syntax: 
	
	call sp_pruneCachedWallet('<del mode>', '<retain date>', <max users>)
	
	where:
	
    	<del mode> - Mandatory, text, case sensitive. Replace <del mode> with one of the
    	 		     following values:
			'ABOUT'      Displays script information
			'DELETE'     Performs actual delete 
			'HELP'       Displays script syntax and example
			'SIMULATE'   Simulates deletion without actual delete
			  
		<retain date> 	- Optional, text, date in YYYYMMDD format
						- Process only cached wallets created before the specified date
						- Leave blank to process all wallets
			   
		<max users> - Mandatory, numeric, maximum number of IMS users to be processed
					  Replace <max users> with one of the following values:
           			0		Process all the ISAMESSO users.
					X   	Process 'X' number of ISAMESSO users. 		
						

	
 4. Examples

	4.1 Simulate deletion of up to 5K random users' dangling cached wallets without performing 
		actual delete. 
	
		call sp_pruneCachedWallet('SIMULATE','',5000)
	
	4.2 Delete up to 10K random users' dangling cached wallets created
	    before 1st Jan 2011.
	
		call sp_pruneCachedWallet('DELETE','20110101',10000)
	
	4.3 Simulate deletion of all users' dangling cached wallets without performing actual 
	    delete. 
	
		call sp_pruneCachedWallet('SIMULATE','',0)
		
		
 5. OPTIONAL: Advanced Performance Tuning
 
 	Consult with your Administrator before doing the performance tuning task.
 
 	 5.1 Script execution
 	 		
 	 		You can disable or remove the following database triggers 
 	 	so that the maintenance script execution performance in IMS 8.1. is improved.
 	 		
			- IMSIdentityAttributeDelete
			- IMSIdentityUniqueAttributeDelete
			- IMSSociAttributeDelete

 	 5.2 Database access performance
 	 		
 			Consult your database Administrator whether it is necessary to perform DB2 table, index
 		REORG and RUNSTATS on the following tables after a huge number of cached wallet records are being
 	 	deleted from the database.
 	 	
 	 		- IMSSOCI
			- IMSSOCIATTRIBUTE
			- IMSSOCIUNIQUEATTRIBUTE
			- IMSCERTIFICATE
 	 
 	 		
 	 5.3 Script optimization
 	 
 	 		Adjust the IMS users process per batch that defaults to 200. Look for the following 
 	 	line in the "sp_pruneCachedWallet.sql" and replace it with the desired number
 	 	before recreating the store procedure.
 	 	
 	 		DECLARE @imsid_per_batch INTEGER DEFAULT 200;