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

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

set DAY_TO_KEEP=90

: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%

goto BackupData

:BackupData

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

:RunMaintenance

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

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

%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 Create Backup Table sql file 

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 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