Job Control

Job Control is a process within Informatica that facilitates incremental data extraction. (You can use Job Control if your source is a database; if your source is a flat file, you can use File Wait instead.) To specify the job control session, open the Mapping Designer and select the Source Qualifier properties tab, SQL filter, job control selection criteria.

When data is written to the ETL staging tables, a Date/Time stamp must be appended to each record. The JOB_CONTROL table stores a record containing the beginning (FROM_DATE) and end (TO_DATE) Date/Time stamps that are used to identify the range of ETL staging table records that have not been extracted during previous data loads.

For example, assume that all records written to the ETL staging tables were loaded on Dec 5, 2000 and each record is stamped with this Date/Time. If the Phase 1 mappings are executed on Dec 6, 2000, the JOB_CONTROL table is automatically updated and shows the following entry (see Table1. JOB_CONTROL Table Before First Data Load).

Table 1 - JOB_CONTROL Table Before First Data Load

This tells Informatica to take only the ETL staging table records whose Date/Time stamp falls between these dates.

After completion of the first data load, additional records are inserted into the staging tables on Dec 6, 2000. If the Phase 1 mappings are executed on Dec 7, 2000, the mappings update the FROM_DATE with the first data load’s TO_DATE, and then they overwrite the first data load’s TO_DATE with the database sysdate (current date/time). The entry now looks as follows (see Table2. JOB_CONTROL Table Before Second Data Load).

Table 2 - JOB_CONTROL Table Before Second Data Load

Now the Phase 1 mappings will take only those records that were inserted into the staging tables since the completion of the first data load.

The Informatica Server Manager can be used either to schedule or to manually execute the Phase 1 mappings. A post-session command, configured in the session that executes the Phase 1 mappings, can be used to execute the Phase 2 mappings automatically upon completion of the Phase 1 mappings.