DataMigrator/Reporting Server > DataMigrator Log Statistics and Tables > Custom Procedures for Managing FOCUS Logs and Statistics

Custom Procedures for Managing FOCUS Logs and Statistics

How to:

Note: You must be logged in as a Server Administrator with workspace configuration privileges to make any configuration changes, or to following either of the following procedures.

Procedure: How to Automatically Delete Rows From the ETL Log and Statistics Table

It may be useful for a Server to automate deleting rows from the ETL log and statistics tables. For example, you can delete all rows more than a week old on a daily basis.

  1. Create a stored procedure with the following lines:
    -SET &DDATE=AYMD(&YYMD,-7,'I8');
EX ETLDELLS DDATE=&DDATE, DTIME=00:00:00, SUSTART=Y, ETLSTART=Y
  2. Create a new process flow.
  3. Drag the procedure into the flow and connect it to the Start object.
  4. Drag a Schedule icon into the workspace.
  5. Double-click the Schedule icon and set a schedule for running the flow.
  6. Save the flow.

Note: The Clean utility rebuilds the ETLLOG. The FDS service has to be stopped to prevent possible file corruption during the rebuild process. Therefore, the following considerations should be taken into account:
  • No other jobs should run during an execution of the Clean utility.
  • The value of the Log Destinations property of the flow should not be set to ETLLOG.

Procedure: How to Automatically Backup and Recreate the ETL Log and Statistics Tables

The DataMigrator server can be configured so that if there is an error writing to the ETL log, one or more of the following events will occur:

  • A specified procedure is executed that will backup and recreate ETL log and statistics.
  • The message is written in to the server log (edaprint.log).
  • The email notifications are sent to a named address with a specified frequency.

The following procedure shows how to backup and recreate ETL log and statistics.

  1. Create a new process flow and name it pf_backup_recreate.
  2. Add the following stored procedures to the process flow in this exact order: st_backup, st_rename, and st_recreate.

    The following is an explanation of the stored procedures mentioned.

    st_backup

    This procedure will create the etllogbk.foc and etlstabk.foc files in the \dm\catalog\ directory. These files are copies of the etllog.foc and etlstats.foc files. Example code for st_backup is: 

    SHH CRTBACKUP
END
    st_rename

    This step is optional. This procedure prevents these files from being overwritten during the next backup process. As a result of this procedure, the etllogbk.foc and etlstabk.foc files will be renamed, with a timestamp added to their suffix. For example, etllogbk_20101026_1120258.foc. Example code for st_rename is: 

    -SET &LOGBK = c:\ibi\srv77\dm\catalog\etllogbk.foc; 	 
-SET &STABK = c:\ibi\srv77\dm\catalog\etlstabk.foc; 	 
-SET &LOGBKREN = etllogbk || '_ ' || &YYMD || '_ ' || 
EDIT(&TOD,'99$99$99') || '.foc'; 	 
-SET &STABKREN= etlstabk || '_ ' || &YYMD || '_ ' || 
EDIT(&TOD,'99$99$99') || '.foc'; 	 
!REN &LOGBK &LOGBKREN 	 
!REN &STABK &STABKREN 	 
END
    st_recreate

    This procedure deletes the original etllog.foc and etlstats.foc files, and will replace them with the new initialized files. Example code for st_recreate is: 

    EX ETLCRTLS ALL, Y, N 	 
END
  3. Run the new process flow by clicking Run or Submit one time, or scheduling the flow. You can also use Event Routing, as described in How to Send an Email Message When There Is an Error Writing to the ETL Log.

 WebFOCUS

Feedback