Purge ODI Logs through Database

If you generate a lot of logs in ODI, purging through ODI built-in mechanism can be very slow. A lot faster to do it through Database, but you have to respect foreign keys. Here is a sample plsql script to do so.

Here is a simple script with one parameter which is the number of days of log you want to keep, it will there retrieve session number and delete in the logs table following the dependencies.

DECLARE
  -- Parameter :  Number of days to keep
  nb_days_to_keep NUMBER := 20;
  
  -- Parameter : Commit after each delete (FALSE) or bulk commit (TRUE);
  b_bulk_commit boolean := FALSE;
  
  no_sess         NUMBER;
  nb_rows         NUMBER;
  
  DEBUG CONSTANT INTEGER:=5;
  INFO CONSTANT INTEGER:=3;
  WARNING CONSTANT INTEGER:=2;
  ERROR CONSTANT INTEGER:=1;
  
  -- Param debug level
  v_debugLevel INTEGER := DEBUG;
  -- basic logging
  PROCEDURE log_msg (msg_in IN VARCHAR2, loglevel IN INTEGER) 
   IS 
      PRAGMA AUTONOMOUS_TRANSACTION; 
   BEGIN 
      IF (v_debugLevel > loglevel) THEN
		DBMS_OUTPUT.put_line(msg_in);
	  END IF;
   END; 
  
BEGIN
  --Retrieve sess_no to delete to
  --Sess_no are sequential (next value stored into SNP_ID table
  SELECT nvl (MAX(sess_no),0)
  INTO no_sess
  FROM SNP_SESSION
  WHERE sess_end < sysdate - nb_days_to_keep;
  log_msg('Sess_No : ' || no_sess, INFO);
  
  log_msg('Delete  Step 1/9 : SNP_SESS_TASK_LS', INFO);  
  DELETE /* Step 1/9 */
  FROM SNP_SESS_TASK_LS stl WHERE sess_no < no_sess ;
  nb_rows := SQL%ROWCOUNT;
  IF (NOT b_bulk_commit) THEN COMMIT; END IF;
  log_msg(' Done - Deleted ' || nb_rows || ' rows', INFO);
  
  log_msg('Delete  Step 2/9 : SNP_SESS_TASK_LOG', INFO);  
  DELETE /* Step 2/9 */
  FROM SNP_SESS_TASK_LOG stl WHERE sess_no < no_sess ;
  nb_rows := SQL%ROWCOUNT;
  IF (NOT b_bulk_commit) THEN COMMIT; END IF;
  log_msg(' Done - Deleted ' || nb_rows || ' rows', INFO);
  
  log_msg('Delete  Step 3/9 : SNP_SESS_STEP_LV', INFO);  
  DELETE /* Step 3/9 */
  FROM SNP_SESS_STEP_LV sl WHERE sess_no < no_sess ;
  nb_rows := SQL%ROWCOUNT;
  IF (NOT b_bulk_commit) THEN COMMIT; END IF;
  log_msg(' Done - Deleted ' || nb_rows || ' rows', INFO);
  
  log_msg('Delete  Step 4/9 : SNP_STEP_LOG', INFO);  
  DELETE /* Step 4/9 */
  FROM SNP_STEP_LOG sl WHERE sess_no < no_sess ;
  nb_rows := SQL%ROWCOUNT;
  IF (NOT b_bulk_commit) THEN COMMIT; END IF;
  log_msg(' Done - Deleted ' || nb_rows || ' rows', INFO);
  
  log_msg('Delete  Step 5/9 : SNP_PARAM_SESS', INFO);  
  DELETE /* Step 5/9 */
  FROM SNP_PARAM_SESS sl WHERE sess_no < no_sess ;
  nb_rows := SQL%ROWCOUNT;
  IF (NOT b_bulk_commit) THEN COMMIT; END IF;
  log_msg(' Done - Deleted ' || nb_rows || ' rows', INFO);
  
  log_msg('Delete  Step 6/9 : SNP_SESSION_DBG', INFO);  
  DELETE /* Step 6/9 */
  FROM SNP_SESSION_DBG sl WHERE sess_no < no_sess ;
  nb_rows := SQL%ROWCOUNT;
  IF (NOT b_bulk_commit) THEN COMMIT; END IF;
  log_msg(' Done - Deleted ' || nb_rows || ' rows', INFO);
  
  log_msg('Delete  Step 7/9 : SNP_SESSION', INFO);  
  DELETE /* Step 7/9 */
  FROM SNP_SESSION WHERE sess_no < no_sess ;
  nb_rows := SQL%ROWCOUNT;
  IF (NOT b_bulk_commit) THEN COMMIT; END IF;
  log_msg(' Done - Deleted ' || nb_rows || ' rows', INFO);
  
  -- Handle reports tables
  SELECT MAX(SCEN_RUN_NO)
  INTO no_sess
  FROM SNP_SCEN_REPORT
  WHERE sess_end < sysdate - nb_days_to_keep;
  
  log_msg('Delete Step 8/9 : SNP_STEP_REPORT', INFO);  
  DELETE /* Step 8/9 */
  FROM SNP_STEP_REPORT sl WHERE SCEN_RUN_NO < no_sess ;
  nb_rows := SQL%ROWCOUNT;
  IF (NOT b_bulk_commit) THEN COMMIT; END IF;
  log_msg(' Done - Deleted ' || nb_rows || ' rows', INFO);
  
  log_msg('Delete  Step 9/9 : SNP_SCEN_REPORT', INFO);  
  DELETE /* Step 9/9 */
  FROM SNP_SCEN_REPORT WHERE SCEN_RUN_NO < no_sess ;
  nb_rows := SQL%ROWCOUNT;
  COMMIT;
  log_msg(' Done - Deleted ' || nb_rows || ' rows', INFO);
  
  EXCEPTION   
      WHEN OTHERS THEN
		ROLLBACK;
        log_msg(' Rollback due to Exception : ' || SQLERRM, ERROR);
  
END;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s