=========================================================================
    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		: Oracle 10g / 11g

  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.


	1.1 Using Oracle SQLPlus 
	----------------------------
	1.1.1 connect to the target database. 
              Replace <user id> and <password> in the following 
		  script and execute it.
		  
		  sqlplus <user id>@<connect_identifier>
		  		
	1.1.2 Enter the login password for the user id when prompted.
	
	1.1.3 set current schema to ISAMESSO schema.
              Replace <ISAMESSO SCHEMA> and execute the following command.
	 
	      ALTER SESSION SET CURRENT_SCHEMA = <ISAMESSO SCHEMA>
	
	1.1.4 Create the stored procedure.
	      Replace <script-location> and execute the following command. 
	      "Procedure created." will be displayed once the stored procedure is created
	      successfully.
	      
	      @<script-location>sp_pruneImsLogOlderThan.sql 
		
	
 2. Verify Stored Procedure Creation
	
	Execute the following script in the same sqlplus session to verify that the stored procedure is created successfully.
	
	SET SERVEROUTPUT ON;
	exec sp_pruneImsLogOlderThan('ABOUT',0);
	
	If the stored procedure is created successfully, you will get an output similar to:
	--------------------------------------------------------------------------
	  ISAMESSO Audit Log Housekeeping Maintenance Script 					 
	  Database Name    : IMS_DB								                
	  Schema Name      : IMS_SCHEMA								        	
          Script version   : 1.0.0						 					 
          Database version : Oracle Database 10g Express Edition 10.2.0.1.0	 
	--------------------------------------------------------------------------
	
	
 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:

		ALTER SESSION 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 
	
	
	  * To make sure sqlplus will display the output of the script on console, execute 
		"SET SERVEROUTPUT ON" in every sqlplus session before running any stored procedure.
	
	Maintenance Script Syntax :  
	
	exec 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. Example 

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