Mass data update on SQL table

INTRODUCTION

It is a tedious job to perform a mass update of a huge database by plain UPDATE statement since it will eat a lot of time. It is not advisable, a situation like executing, on production deployment since the deployment time window is minimal.

I was facing the same situation on one of my projects needs huge data update in a SQL table. So here I am providing step by step guide to do mass table update.

Consider a Table (Employee) with Column

emp_id INTEGER, emp_name VARCHAR(), emp_address NCLOB(), emp_mob INTEGER;

ISSUE

The emp_address contains data like 'chennai', 'andhra' which needs to be changed to 'madras', 'telangana' respectively

then, we prefer to go with traditional approach of:

UPDATE employee set emp_address = REPLACE(emp_address, 'chennai', 'madras') where emp_address like '%chennai%'; UPDATE employee set emp_address = REPLACE(emp_address, 'andra', 'telangana') where emp_address like '%andra%';

Though it's a correct one, but it is not suitable updating large volume (1+ crore) of data.

SOLUTION

To overcome this situation,  one of the best approaches would be copying the whole table and do the necessary modification/transform on it.

Step 1

First, alter the session

ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;

Step 2

Drop the new table if exists. if may throw an error if not exists, but it's fine.

DROP TABLE employee_new;

Step 3

Copy the existing table with necessary Modification.

CREATE TABLE employee_new AS SELECT emp_id, emp_name, CASE  WHEN emp_address LIKE '%chennai%'  THEN REPLACE(emp_address,'chennai','madras')  WHEN emp_address LIKE '%andra%'  THEN REPLACE(emp_address,'andra','telangana')  ELSE field_values  END field_values, emp_mob FROM employee NOLOGGING;

Step 4

Apply necessary post table creation like NOCOMPRESS, NOCACHE, NOPARALLEL, MONITORING

ALTER TABLE employee_new NOCOMPRESS; / ALTER TABLE employee_new NOCACHE; / ALTER TABLE employee_new NOPARALLEL; / ALTER TABLE employee_new MONITORING; /

6. Step 5

1. Apply necessary comments for the column if any
2. Create index if there is anything on 'employee' table
3. Create Constraints if there is anything on 'employee' table.

ALTER TABLE employee_new LOGGING; /

Step 6

If required, do necessary Validation(whether the new employee table has correct data copied from employee table)

CREATE OR REPLACE PROCEDURE EMPUPDATE AS  chennai_count    NUMBER := 0;  andra_count    NUMBER := 0; BEGIN --if the new table has 0 records for the replaced value, then we can assume replace is the success. SELECT COUNT(emp_id) INTO chennai_count FROM employee_new WHERE emp_address LIKE '%|chennai|%'; SELECT COUNT(emp_id) INTO chennai_count FROM employee_new WHERE emp_address LIKE '%|andra|%';

Step 7

Drop existing table, only if all the values are replaced successfully. (i.e: chennai_count and andra_count are 0)

IF chennai_count = 0 AND andra_count =0 THEN  --Rename the actual employee table into employee_old which can be deleted manually once all the update was proper.  EXECUTE IMMEDIATE 'ALTER TABLE employee RENAME TO employee_old';  --Rename new employee table to employee table.  EXECUTE IMMEDIATE 'ALTER TABLE employee_new RENAME TO employee'; ELSE  raise_application_error (-20002, 'ERROR on Updating employee table. - ' || SQLCODE || ' -ERROR- ' || SQLERRM ); END IF; EXCEPTION WHEN OTHERS THEN  raise_application_error (-20001, 'AN ERROR WAS ENCOUNTERED - ' || SQLCODE || ' -ERROR- ' || SQLERRM ); END EMPUPDATE; / EXECUTE EMPUPDATE;

Quartz Scheduler Listener Example

INTRODUCTION

Quartz Scheduler

• It is a library supports scheduling automated Jobs to run in Java applications
• Support for a simple standalone Java application to large scale enterprise applications.
• Jobs scheduling configuration can be done through XML Configuration or through the Java program itself.
• It is free & an open source library.

This post will explain the implementation of Quartz Scheduler Listener. Listener helps to monitor the various jobs running through the Quartz Scheduler.

SOFTWARES & TOOLS

1. Eclipse (Mars2)
2. Quartz Library.

COMPONENTS

1. Scheduler - will pick the job and start the process.
2. Job - defined with what should be executed.
3. Listener - will monitor the job.

EXAMPLE

First, Create a Job (MyJob.java)

import org.quartz.Job; import org.quartz.JobDataMap; import org.quartz.JobExecutionContext; import org.quartz.JobExecutionException; public class MyJob implements Job {  @Override  public void execute(JobExecutionContext jobExecutionContext) throws JobExecutionException {    // Write/call your actual business functions.    for (int i = 0; i <= 1000; i++) {      // just to show some delay for job.    }    System.out.println("****My Details*******");    JobDataMap jobDetails = jobExecutionContext.getJobDetail().getJobDataMap();    System.out.println("First Name: " + jobDetails.getString("FirstName"));    System.out.println("Last Name: " + jobDetails.getString("LastName"));    System.out.println("City Name: " + jobDetails.getString("City"));  } }

Create a Job Constants (MyJobConstants.java)

public interface MyJobConstants {  int JOB_REPEAT_COUNT = 3;  int JOB_REPEAT_INTERVAL = 2; }

Now, Create a Scheduler(MyScheduler.java)

import org.quartz.JobBuilder; import org.quartz.JobDetail; import org.quartz.JobKey; import org.quartz.Scheduler; import org.quartz.SimpleScheduleBuilder; import org.quartz.Trigger; import org.quartz.TriggerBuilder; import org.quartz.TriggerKey; import org.quartz.impl.StdSchedulerFactory; public class MyScheduler {  // Set Job details  public void scheduleJob() throws Exception {    JobKey jobKey = new JobKey("RanjithJob", "RanjithGroup");    JobDetail jobDetail = JobBuilder.newJob(MyJob.class).withIdentity(jobKey).build();    /**     * Set required values to the JobDetail object, so that it can be retrieved     * when executing the job.     */    jobDetail.getJobDataMap().put("FirstName", "Ranjith");    jobDetail.getJobDataMap().put("LastName", "Sekar");    jobDetail.getJobDataMap().put("City", "Chennai");        // Setting the start time of the job.    MyJobListener.jobStartTime = System.currentTimeMillis();    // Trigger the Job    triggerJob(jobDetail);  }  // Trigger the job  private void triggerJob(JobDetail jobDetail) throws Exception {    TriggerKey triggerKey = new TriggerKey("MyTriggerKey", "MyTriggerGroup"        + System.currentTimeMillis());    /**     * Create Trigger and mention schedule details like: when it should trigger     * and how many times it should execute     */    Trigger trigger = TriggerBuilder        .newTrigger()        .withIdentity(triggerKey)        .withSchedule(            SimpleScheduleBuilder.repeatSecondlyForTotalCount(MyJobConstants.JOB_REPEAT_COUNT,                MyJobConstants.JOB_REPEAT_INTERVAL)).build();    // Create Scheduler    Scheduler scheduler = new StdSchedulerFactory().getScheduler();    scheduler.getListenerManager().addJobListener(new MyJobListener());    scheduler.start();    scheduler.scheduleJob(jobDetail, trigger);  } }

Create a Listener (MyJobListener.java)

import org.quartz.JobExecutionContext; import org.quartz.JobExecutionException; import org.quartz.JobListener; import org.quartz.SchedulerException; public class MyJobListener implements JobListener {  static int completedJobCount;  public static long jobStartTime;  @Override  public String getName() {    // define your own job name.    return "MYJOB";  }  @Override  public void jobExecutionVetoed(JobExecutionContext jobExecutionContext) {    // Not sure user of this method.    System.out.println("Entering - jobExecutionVetoed()");  }  @Override  public void jobToBeExecuted(JobExecutionContext jobExecutionContext) {    // Entry point for every Job    System.out.println("\nEntering - jobToBeExecuted()");    System.out.println("Job Name: " + jobExecutionContext.getJobDetail().getKey().getName());    System.out.println("Exiting - jobToBeExecuted()");  }  @Override  public void jobWasExecuted(JobExecutionContext jobExecutionContext, JobExecutionException jobExecutionException) {    // Exit point for every job    System.out.println("Entering - jobWasExecuted()");    String jobName = jobExecutionContext.getJobDetail().getKey().getName();    System.out.println("Job Name: " + jobName);    // Stop the scheduler once its completes all the scheduled jobs.    completedJobCount = completedJobCount + 1;    System.out.println("Job: " + jobName + " completed in " + (System.currentTimeMillis() - jobStartTime)        + " milliseconds.");    if (completedJobCount == MyJobConstants.JOB_REPEAT_COUNT) {      try {        jobExecutionContext.getScheduler().shutdown();      } catch (SchedulerException e) {        // TODO Auto-generated catch block        e.printStackTrace();      }    }    System.out.println("Exiting - jobWasExecuted()");  } }

Finally, Create a Client (TestMyJob.java) to test the Quartz Scheduler.

public class TestMyJob {  public static void main(String[] args) throws Exception {    MyScheduler myScheduler = new MyScheduler();    myScheduler.scheduleJob();  } }

OUTPUT

Entering - jobToBeExecuted() Job Name: RanjithJob Exiting - jobToBeExecuted() ****My Details******* First Name: Ranjith Last Name: Sekar City Name: Chennai Entering - jobWasExecuted() Job Name: RanjithJob Job: RanjithJob completed in 113 milliseconds. Exiting - jobWasExecuted() Entering - jobToBeExecuted() Job Name: RanjithJob Exiting - jobToBeExecuted() ****My Details******* First Name: Ranjith Last Name: Sekar City Name: Chennai Entering - jobWasExecuted() Job Name: RanjithJob Job: RanjithJob completed in 2005 milliseconds. Exiting - jobWasExecuted() Entering - jobToBeExecuted() Job Name: RanjithJob Exiting - jobToBeExecuted() ****My Details******* First Name: Ranjith Last Name: Sekar City Name: Chennai Entering - jobWasExecuted() Job Name: RanjithJob Job: RanjithJob completed in 4004 milliseconds. Exiting - jobWasExecuted()