SAP Connector > Part IV: Data Integration using SAP Table > Mappings and mapping tasks with SAP Table > Delta extraction for SAP table reader mappings
  

Delta extraction for SAP table reader mappings

When you read data from SAP tables, you can configure a mapping to perform delta extraction. With delta extraction, you can choose to read only the changed data.
The SAP table and SAP columns for which you want to perform delta extraction must be part of a change document object in SAP. For more information about creating a change document in SAP, see the SAP documentation.
The Secure Agent uses the CDHDR (Change Document Header) and CDPOS (Change Document Position) tables in SAP to extract the changed data. The CDHDR table stores the change document header information. The CDPOS table stores the new value and the old value of the changed data. The Secure Agent uses the change document number in the CDHDR table to find the latest change number in the CDPOS table.

Delta extraction behavior

When you configure a delta extraction, the Secure Agent does not fetch the change indicators marked for insert, delete, update, or data driven for delta records from SAP. Hence, the delta rows that are extracted from the source are marked for upsert by default.
When you perform a delta extraction and if the row is available in the SAP target table, the Secure Agent updates the delta records to the target table. If the row is not available, the Secure Agent inserts the records to the SAP target table.
If multiple transactions such as insert, update, or delete occurs for the same record in the SAP source, the Secure Agent fetches only one record. However, if an operation, for example, an insert for a record occurs in the SAP source table and you run the mapping, the Secure Agent fetches the inserted record. Later, if an update occurs for the same record in the SAP source table and you run the delta extraction mapping, the Secure Agent fetches the updated record.
When you perform a delta extraction for deleted records in the SAP table, the records that are marked for hard deletion in the SAP source table are not fetched. If the records are marked for soft deletion, where the isDeleted flag for the record is set to true, you can add transformations in the SAP Table Reader mapping to extract that record.

Informatica custom table /INFADI/TBLCHNGN

The Secure Agent creates and maintains an Informatica custom table in SAP named /INFADI/TBLCHNGN. The /INFADI/TBLCHNGN table captures the time that is used for delta extraction through the Informatica mappings. You can use transaction SE11 or SE16 to view the table entries.
The following image shows the /INFADI/TBLCHNGN table:
The image shows the Informatica custom table named /INFADI/TBLCHNGN.
The /INFADI/TBLCHNGN table contains the following columns:
TABLE_NAME
Specifies the SAP source table name from which data is extracted.
SESSION_ID
Specifies the unique Informatica session ID for delta extraction. The Secure Agent generates a unique session ID for each mapping run for a particular SAP table.
PREV_CHNG_NUM
Not applicable.
CURR_CHNG_NUM
Not applicable.
PREV_DAT
Indicates the date from when changes were extracted in the last delta extraction. The Secure Agent uses this information when you use the Delta Repeat option.
PREV_TIME
Indicates the time from when the changes were extracted in the last delta extraction. The Secure Agent uses this information when you use the Delta Repeat option.
LAST_UPDATED_DAT
Indicates the date up to when the changes were extracted from the source in the last delta extraction. This value also indicates the date from when the changes will be extracted for the subsequent delta extraction.
LAST_UPDATED_TIM
Indicates the time up to when the changes were extracted from the source in the last delta extraction. This value also indicates the time from when the changes will be extracted for the subsequent delta extraction.

Update modes for delta extraction

When you configure delta extraction for SAP table reader mappings, you can select the update mode that you want to use.
You can select one of the following update modes:

Full

If you select the Full option, the Secure Agent extracts all the records from an SAP table. The Secure Agent does not update any details in the Informatica custom table /INFADI/TBLCHNGN.
Use this option when you want to extract all the records from an SAP table instead of reading only the changed data.
Default is Full.

Delta initialization without transfer

If you select the Delta initialization without transfer option, the Secure Agent does not extract any data from an SAP table but records the LAST_UPDATED_DAT and LAST_UPDATED_TIM in the Informatica custom table /INFADI/TBLCHNGN for subsequent delta extractions.
The Secure Agent performs the following actions:
Use this option when you do not want to extract any data but you want to record the LAST_UPDATED_DAT and LAST_UPDATED_TIM in the Informatica custom table /INFADI/TBLCHNGN for subsequent delta extractions.
For example, you have a table named Customers that contains 5 million records. You want to read the initial set of records by using another product such as Informatica Data Replication and then write the records to a Teradata data warehouse. You then want to use SAP Connector to read only the new customer records that get added to the table. In this case, you can configure delta initialization without transfer and then subsequently run a delta update session to capture the changed data.

Delta initialization with transfer

If you select the Delta initialization with transfer option, the Secure Agent extracts all the records from an SAP table but you want to record the LAST_UPDATED_DAT and LAST_UPDATED_TIM in the Informatica custom table /INFADI/TBLCHNGN for subsequent delta extractions.
The Secure Agent performs the following actions:
Use this option when you want to extract all the records from an SAP table to build an initial set of the data and subsequently run a delta update session to capture the changed data.

Delta update

If you select the Delta update option, the Secure Agent extracts the changed data since the last data extraction.
The Secure Agent performs the following actions:
Use this option when you want to read only the data that changed since the last data extraction.
Before you run a delta update session, you must have performed a delta initialization with transfer or delta initialization without transfer. The delta initialization records the LAST_UPDATED_DAT AND LAST_UPDATED_TIM that the Secure Agent uses to run a delta update session.
Note: To avoid data loss, the current date and time is frozen before the Secure Agent runs the query. If any data enters at the time when the mapping runs, that data is extracted only when you run the next mapping.

Delta repeat

If you select the Delta repeat option, the Secure Agent repeats a previous delta update in case of errors. It returns records from the PREV_DAT and PREV_TIM values in the Informatica custom table /INFADI/TBLCHNGN to the LAST_UPDATED_DAT and LAST_UPDATED_TIM values in the Informatica custom table /INFADI/TBLCHNGN. It does not update any change numbers in the /INFADI/TBLCHNGN table.
Use this option if you encountered errors in a previous delta update and want to repeat the delta update.
Before you run a delta repeat session, you must have performed a delta update.

Parameter

If you select the Parameter option, the Secure Agent uses the update mode value from a parameter file. Define a parameter name and parameter value in a parameter file. In the SAP table reader mapping that you create for delta extraction, specify the same parameter name that you defined in the parameter file. Then, create a mapping task and specify the parameter file name in the task. Instead of updating the parameter value in the mapping every time, you can update the parameter value in the parameter file and run the mapping task again.

Configuring a parameter file for delta extraction

A parameter file is a list of user-defined parameters and their associated values. To perform a delta extraction, you can specify the update mode in a parameter file so that you do not need to edit the mapping every time you want to change the update mode.
To use a parameter file, perform the following steps:
    1. Create a parameter file in the following directory:
    <Secure Agent installation directory>/apps/Data_Integration_Server/data/userparameters
    2. In the parameter file, enter a parameter name and specify the parameter value that you want to use.
    The parameter name must start with $$ and cannot contain space characters.
    You can use one of the following parameter values based on the update mode that you want to use:
    Use the following format to specify the parameter name and parameter value:
    $$<parameter_name>=<parameter_value>
    Do not use space characters while specifying the parameter name and parameter value.
    For example, enter: $$deltaparameter=0
    3. Save the parameter file.
    4. Open the SAP table reader mapping that you want to use for delta extraction.
    5. Select Parameter from the Update Mode list.
    6. In the Parameter Name for Update Mode field, enter the parameter name that you defined in the parameter file.
    7. Create a mapping task based on the SAP table reader mapping.
    8. In the Schedule page of the mapping task, enter the parameter file name under the Advanced Options section.
    9. Run the mapping task.
    To change the update mode, update the parameter value in the parameter file, and run the mapping task again. For example, the first time you run a mapping task, you can specify the parameter value as 2 to use delta initialization with transfer. After the initial extraction is done, you might want to change the parameter value to 3 to capture only the changed data in the next mapping task run. Instead of updating the parameter value in the mapping every time, you can update the parameter value in the parameter file and run the mapping task again.

Configure a table name override for delta extraction

When you configure a SAP Table reader mapping, you can override the selected table name at runtime with the structure name to perform a delta extraction.
When you run an SAP Table reader mapping for delta extraction, the Secure Agent fetches changed records from the SAP table for entries logged for the SAP table name in the Change Document Position (CDPOS) table. If there are entries logged for the SAP table structure in the CDPOS table, you can fetch those records by overriding the table name in the Override Table Name for Delta Extraction field in the SAP Table advanced source properties.
For example, if the table name you specified as the object type in the mapping is CRMD_ORDERADM_H, to get the delta records for entries captured in the CDPOS table for the structure name, specify the structure name CRMA_ORDERADM_H in the Override Table Name for Delta Extraction field. The Secure Agent fetches records from CRMA_ORDERADM_H that has change entries logged for the table structure.
If the delta data captured in the CDPOS table does not include the structure name, keep this field blank.

Rules and guidelines for delta extraction

Consider the following rules and guidelines when you configure delta extraction for SAP table reader mappings:

Configuring delta extraction for an SAP table reader mapping

To configure delta extraction for an SAP table reader mapping, select the update mode that you want to use and optionally define a parameter in the mapping.
    1. Create a mapping to read data from an SAP table and write data to a target.
    2. Click the Source transformation in the mapping.
    3. In the Properties panel, click the Source tab.
    4. Under the advanced properties, select one of the following values from the Update Mode list:
    For information about using a parameter for delta extraction, see Configuring a parameter file for delta extraction.
    5. Save and run the mapping.

Troubleshooting delta extraction for SAP Table Reader mappings

Why do I see the error "Only Full Update Mode is supported for table {table_name} because it is not a part of any change document object in SAP"?
The error occurs because the SAP table for which you are trying to perform delta extraction is not part of a change document object in SAP.
If the SAP table and SAP columns for which you want to perform delta extraction are not part of a change document object in SAP, you cannot perform delta extraction. You can only perform a full extraction.
Why do I see the error "An error occurred while fetching the current date and time from SAP because there is no entry present in the /INFADI/TBLCHNGN table. Run a delta initialization session first."?
The error occurs when you run a delta update or delta repeat session directly without performing a delta initialization.
The delta initialization records the LAST_UPDATED_DAT and LAST_UPDATED_TIM that the Secure Agent uses to run a delta update or delta repeat session. Without delta initialization, the Secure Agent does not have access to the LAST_UPDATED_DAT and LAST_UPDATED_TIM to run a delta update or delta repeat session.
In the INFADI/TBLCHNGN table, how can I view entries corresponding to my mapping run?
You can refer to the session log to find out the session ID for your mapping. In the INFADI/TBLCHNGN table, look for the same session ID to view details about your mapping run. The Secure Agent generates a unique session ID for each mapping run for a particular SAP table. You can also sort the INFADI/TBLCHNGN table entries based on the session ID.
Why does the number of records extracted through the Full or Delta initialization with transfer option not match the number of records extracted through the Delta repeat option?
When you use the Full or Delta initialization with transfer option, the Secure Agent extracts all the records directly from the SAP table and not only the records that are captured in the change document. However, when you use the Delta repeat option, the Secure Agent extracts only the records that are captured in the change document.
Therefore, after you perform a full extraction or delta initialization with transfer, if you run a delta repeat session, the extracted records count might not match with the number of records extracted through the Full or Delta initialization with transfer option.