=========================================================================
    IBM Security Access Manager for Enterprise Single Sign-On (ISAMESSO)
                 Audit Log Housekeeping Maintenance Script
 =========================================================================
 
	Release date 		: 16-Jan-2012
	Script version 		: 1.0.1
	Target IMS version 	: 8.1.x / 8.2.x
	Target Database		: DB2 9.5 / 9.7

  The audit log housekeeping maintenance script removes audit logs older than a specified 
	number of days.

 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_pruneImsLogOlderThan.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_pruneImsLogOlderThan"
	      In the command editor, Select "Selected > Open". 
	      Browse to the script "sp_pruneImsLogOlderThan" 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_pruneImsLogOlderThan('ABOUT',0)
	
	If the stored procedure is created successfully, you will get an output similar to:

	------------------------------------------------------
	SAMESSO Audit Log 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:

		set current schema = '<ISAMESSO schema name>'


	  * 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_pruneImsLogOlderThan('<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 delete 
			'HELP'       Displays script syntax and example
			'SIMULATE'   Simulates deletion without actual delete
				 
    	<days> - Mandatory, numeric. Replace <days> with one of the following values
           		0 	     Deletes all the log records.
			X      	     Deletes logs older than 'X' number of days. 
  
 4. Examples

	1. To simulate deletion of audit logs older than 30 days without performing 
	   an actual delete. Use:
	
			call sp_pruneImsLogOlderThan ('SIMULATE',30)		
		
	2. To delete audit log older than 2 weeks (14 days) from database use:
	
			call sp_pruneImsLogOlderThan ('DELETE', 14)
		
	3. To delete all audit logs from database use: 
	
		        call sp_pruneImsLogOlderThan ('DELETE', 0)