Sunday, 14 June 2015

Resume from the last checkpoint.

Recover Strategy for Session Task:

For session the Informatica provides three options of recovery as following:
·         Resume from the last checkpoint.
·         Restart task.
·         Fail task and continue workflow.          

Default Option: Fail Task and Continue workflow

An example for Resume for the last checkpoint recovery option:
In order to understand this option, I created a mapping to load to data from flat file to an oracle table and created a session task with below properties

Nature of my source and target:
Source File: Contains two columns namely FirstName, LastName
My source file contains some duplicate records.
Total Record count is 6009
Target Table: Contains three columns namely FirstName, LastName , T.
FirstName is the primary key on my target table.

When I run the workflow, the session task after loading 5040 records get failed due to the unique key constraint violation. Since I have set the recovery strategy as recovery from last checkpoint, Informatica created below tables in the target schema to save the details of the last checkpoint

[ Recovery TablesWhen you enable recovery for a session that writes to a relational target, the Integration Service creates two tables at the target database, PM_RECOVERY and PM_TGT_RUN_ID. The Integration Service updates these tables during regular session runs with status information about the target load. When the Integration Service runs a failed session in recovery mode, it uses the information in the recovery tables to determine where the previous session failed, and then continues the session from that point. Do not edit or deleteinformation in the recovery tables.PM_RECOVERYThis table records the target load information during the session run per target instance per partition. In previous releases, this table was OPB_SRVR_RECOVERY. PM_RECOVERY saves the following information:

  • Repository ID. This allows multiple repositories to use the same recovery table.

  • Workflow ID

  • Folder ID

  • Session instance ID

  • Workflow run ID

  • Target instance ID. The Integration Service creates one row per target instance

  • Partition ID. The Integration Service creates one row per partition

  • Row count. Stores the last committed number of rows

  • Check point. Used with GMD sources only.PM_TGT_RUN_ID
  • PM_TGT_RUN_ID is for internal use. It stores the latest unique number the Integration Service uses to identify the target instance.This table has a single field, LAST_TGT_RUN_ID. ]
The column T of my target holds the sessionstarttime value, for the first 5040 records the values for same.

After few minutes, I dropped the primary key constraint in my target table and recovered the session task.
Based on the PM_RECOVERY table, Informatica recovered the session task from its last check point. 

The target table has 6009 records like below:


  1. Hi Ashif Ahamed ,

    Really it is very helpfull.Everyone knows about this property but Today I got cleared how it work. Thanks alot....

  2. Thanks for sharing this information.