Description

This guide to create and run WLF_srchLogRlMaintenance

Purpose :

Removes partitions from WLF_RL* tables (excluding WLF_RL_MESSAGE* tables) that were created for customer filtering before MIN_DATE_TO_KEEP parameter (including empty partitions).

Removes partitions from WLF_SRCH_LOG table which monthly interval high value is before MIN_DATE_TO_KEEP parameter.

Symptom

To housekeeping data from WLF_SRCH_LOG table.

Resolution

Below step to Run WLF_srchLogRlMaintenance :

1. Create batch script for parameter

2. Create batch script for process

3. Create Backup Table sql file, you can choose will backup on Database or on File

3.1. Create backup on Database.

3.2. Create backup on File.

4. Create Task Scheduler

5. Run Task Scheduler

Step 1 Create batch script for parameter

1. Create New text file

2. Add below :

@echo off

set DB_SERVER=localhost

set DB_WLF=WLF_APP_user

set DB_WLF_PWD=P@ssw0rd

set WL_LOG=C:\ACTIMIZE\Batch\

set AIS_RUN_UTILITY=C:\ACTIMIZE\AIS_SERVER\ais_run.exe

set WLF_SERVER=localhost

set WLF_PORT=2355

set WLF_USER=admin

set WLF_PASSWORD=password

3. Makesure the path folder and value is correct.

4. Save with name process_config.bat

Step 2 Create batch script for process

1. Create new text file

2. Add below :

@echo off

:: Purpose :

:: Removes partitions from WLF_RL* tables (excluding WLF_RL_MESSAGE* tables) that were created for customer filtering before MIN_DATE_TO_KEEP parameter (including empty partitions).

:: Removes partitions from WLF_SRCH_LOG table which monthly interval high value is before MIN_DATE_TO_KEEP parameter.

call D:\ACTIMIZE\batch\process_config.bat

rem call C:\ACTIMIZE\Batch\WLF\process_config.bat

::Parameter

set DAY_TO_KEEP=30

set OUTPUT=D:\ACTIMIZE\batch\WLF_SRCH_LOG_BAK.csv

:GetDate

::GET CURRENT DATE

ECHO %date% %time% - Information : Get Current Date Started

ECHO %date% %time% - Information : Get Current Date Started >> %WL_LOG%\SearchLog_Maintenance_%FMT_DT%.log

for /f %%A in ('sqlcmd -S%DB_SERVER% -U%DB_WLF% -P%DB_WLF_PWD% -dWLF_APP -W -Q "SET NOCOUNT ON; SELECT CONVERT(VARCHAR, GETDATE()-%DAY_TO_KEEP%, 112) AS FormattedDate;"') do set MIN_DATE_TO_KEEP=%%A 

echo %MIN_DATE_TO_KEEP%

::Choose Backup Data, rem if not used

rem goto BackupDataToDatabase

goto BackupDataToFile

:BackupDataToDatabase

ECHO %date% %time% - Information : Backup Data Started

ECHO %date% %time% - Information : Backup Data Started >> %WL_LOG%\SearchLog_Maintenance_%FMT_DT%.log

sqlcmd -S%DB_SERVER% -U%DB_WLF% -P%DB_WLF_PWD% -dWLF_APP -W -i "C:\ACTIMIZE\Batch\WLF\Backup_WLF_SRCH_LOG.sql" -v Tanggal="%MIN_DATE_TO_KEEP%"

If NOT Errorlevel 0 goto Actimize_ERROR

If Errorlevel 1 goto Actimize_Error

echo %date% %time% - Information: Backup Data Completed

echo %date% %time% - Information: Backup Data Completed >> %WL_LOG%\SearchLog_Maintenance_%FMT_DT%.log

goto RunMaintenance

:BackupDataToFile

ECHO %date% %time% - Information : Backup Data Started

ECHO %date% %time% - Information : Backup Data Started >> %WL_LOG%\SearchLog_Maintenance_%FMT_DT%.log

bcp "SELECT * FROM ACTONE..alerts WHERE alert_date < CAST('%MIN_DATE_TO_KEEP%' as datetime); " queryout "%OUTPUT%" -c -t"," -T -S %DB_SERVER%

rem bcp "SELECT * FROM WLF_APP..WLF_SRCH_LOG WHERE JOB_TIMESTAMP < CAST('%MIN_DATE_TO_KEEP%' as datetime); " queryout "%OUTPUT%" -c -t"," -U%DB_WLF% -P%DB_WLF_PWD%  -S %DB_SERVER% 

If NOT Errorlevel 0 goto Actimize_ERROR

If Errorlevel 1 goto Actimize_Error

echo %date% %time% - Information: Backup Data Completed

echo %date% %time% - Information: Backup Data Completed >> %WL_LOG%\SearchLog_Maintenance_%FMT_DT%.log

goto RunMaintenance

:RunMaintenance

echo %date% %time% - Information : Search Log Maintenance Started 

echo %date% %time% - Information : Search Log Maintenance Started >> %WL_LOG%\SearchLog_Maintenance_%FMT_DT%.log

rem %AIS_RUN_UTILITY%  -s%WLF_SERVER% -t%WLF_PORT% -u%WLF_USER% -p%WLF_PASSWORD% -eAML_EWLF_srchLogRlMaintenance -a%MIN_DATE_TO_KEEP% -d"SearchLog Maintenance"

If NOT Errorlevel 0 goto Actimize_ERROR

If Errorlevel 1 goto Actimize_Error

echo %date% %time% - Information: Search Log Maintenance Completed

echo %date% %time% - Information: Search Log Maintenance Completed >> %WL_LOG%\SearchLog_Maintenance_%FMT_DT%.log

goto Actimize_EXIT

:Actimize_ERROR

echo %date% %time% - Error in Run

echo %date% %time% - Error: Error in Run >> %WL_LOG%\SearchLog_Maintenance_%FMT_DT%.log

exit /b 1

:Actimize_EXIT

echo %date% %time% - Finish Run

echo %date% %time% - Information: Finish Run. All process successfully completed. >> %WL_LOG%\SearchLog_Maintenance_%FMT_DT%.log

exit /b 0

3. Make sure parameter is correct

4. Save file with name WLF_srchLogRlMaintenance.bat

Step 3.1 Create Backup Table sql file on Database

1. Create new file text

2. Add below :

USE WLF_APP;

IF OBJECT_ID('WLF_APP.[dbo].[WLF_SRCH_LOG_BAK]', 'U') IS NOT NULL

BEGIN

    -- [KONDISI 1] Jika tabel SUDAH ADA, gunakan INSERT INTO

    PRINT 'Tabel sudah tersedia. Menjalankan query INSERT INTO...';

    SET IDENTITY_INSERT WLF_APP.dbo.WLF_SRCH_LOG_BAK ON;

    INSERT INTO WLF_APP.dbo.WLF_SRCH_LOG_BAK

    ([ID]

      ,[JOB_ID]

      ,[JOB_NAME]

      ,[JOB_TIMESTAMP]

      ,[PARTY_KEY]

      ,[PARTY_FULL_NAME]

      ,[PARTY_IDS]

      ,[PARTY_COUNTRIES]

      ,[IS_PRE_FILTERED]

      ,[NUMBER_OF_HITS]

      ,[IS_ALERT]

      ,[ACT_ALERT_SCORE])

    SELECT 

        [ID]

      ,[JOB_ID]

      ,[JOB_NAME]

      ,[JOB_TIMESTAMP]

      ,[PARTY_KEY]

      ,[PARTY_FULL_NAME]

      ,[PARTY_IDS]

      ,[PARTY_COUNTRIES]

      ,[IS_PRE_FILTERED]

      ,[NUMBER_OF_HITS]

      ,[IS_ALERT]

      ,[ACT_ALERT_SCORE]

    FROM WLF_APP..WLF_SRCH_LOG

    WHERE JOB_TIMESTAMP < CAST('$(Tanggal)' as datetime);

END

ELSE

BEGIN

    -- [KONDISI 2] Jika tabel BELUM ADA, gunakan SELECT INTO

    PRINT 'Tabel belum tersedia. Menjalankan query SELECT INTO...';

    SELECT *

    INTO WLF_APP.dbo.WLF_SRCH_LOG_BAK

    FROM WLF_APP..WLF_SRCH_LOG

    WHERE JOB_TIMESTAMP < CAST('$(Tanggal)' as datetime);

END

3. Make sure parameter is correct

4. Save file with name Backup_WLF_SRCH_LOG.sql

Step 3.2 Create Backup Table sql file on File

1. Open File Batch Script in Step 2

2. Add below :

:BackupDataToFile

ECHO %date% %time% - Information : Backup Data Started

ECHO %date% %time% - Information : Backup Data Started >> %WL_LOG%\SearchLog_Maintenance_%FMT_DT%.log

bcp "SELECT * FROM ACTONE..alerts WHERE alert_date < CAST('%MIN_DATE_TO_KEEP%' as datetime); " queryout "%OUTPUT%" -c -t"," -T -S %DB_SERVER%

rem bcp "SELECT * FROM WLF_APP..WLF_SRCH_LOG WHERE JOB_TIMESTAMP < CAST('%MIN_DATE_TO_KEEP%' as datetime); " queryout "%OUTPUT%" -c -t"," -U%DB_WLF% -P%DB_WLF_PWD%  -S %DB_SERVER% 

If NOT Errorlevel 0 goto Actimize_ERROR

If Errorlevel 1 goto Actimize_Error

echo %date% %time% - Information: Backup Data Completed

echo %date% %time% - Information: Backup Data Completed >> %WL_LOG%\SearchLog_Maintenance_%FMT_DT%.log

goto RunMaintenance

3. Save File

Step 4 Create Task Scheduler

1. Open Task Scheduler

2. Create Task....

3, Fill Name

4. Fill Trigger : When the job running

5. Fill Action : Choose batch script WLF_srchLogRlMaintenance.bat 

6. Save

Step 5 Run The Job

1. You can try the job from Task Scheduler

2. You can try the job from command prompth