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, or update 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 the deleted records in the SAP table, the Secure Agent fetches only the key value of the deleted delta records to the target table.
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 /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
This column does not apply in delta extraction mappings.
CURR_CHNG_NUM
This column does not apply in delta extraction mappings.
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:
•Sets the values for LAST_UPDATED_DAT and LAST_UPDATED_TIM columns.
•Initializes and sets the values for the PREV_DAT and PREV_TIM columns.
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:
•Sets the LAST_UPDATED_DAT and LAST_UPDATED_TIM columns.
•Initializes the PREV_DAT and PREV_TIM.
•Extracts all the data present in the SAP table.
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:
•Extracts records from the columns LAST_UPDATED_DAT AND LAST_UPDATED_TIM to the current date and time.
•Moves the values from columns LAST_UPDATED_DAT and LAST_UPDATED_TIM to PREV_DAT and PREV_TIM, respectively.
•Updates the values in columns LAST_UPDATED_DAT and LAST_UPDATED_TIM to the current date and time.
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 need to perform 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 need to perform 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:
1Create a parameter file in the following directory:
2In 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:
- 0. Use for Full.
- 1. Use for Delta initialization without transfer.
- 2. Use for Delta initialization with transfer.
- 3. Use for Delta update.
- 4. Use for Delta repeat.
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
3Save the parameter file.
4Open the SAP table reader mapping that you want to use for delta extraction.
5Select Parameter from the Update Mode list.
6In the Parameter Name for Update Mode field, enter the parameter name that you defined in the parameter file.
7Create a mapping task based on the SAP table reader mapping.
8In the Schedule page of the mapping task, enter the parameter file name under the Advanced Options section.
9Run 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:
•You can perform delta extraction only for a single SAP table. You can't use native joins to join data from two or more SAP tables.
•You can't configure delta extraction to look up data from SAP tables.
•You can't configure delta extraction with partitioning.
•You can't configure delta extraction for multiple pipelines within the same mapping.
•When the Secure Agent performs a delta extraction, it does not retrieve the records in the same order in which they were inserted or updated in the SAP table. For example, record 10 might have been updated first in the SAP table before record 20. However, while extracting the data, the Secure Agent might fetch record 20 first and then record 10.
•The Secure Agent does not print any information in the session log to indicate whether the records extracted through delta extraction were part of an insert or update operation in SAP.
•During delta extraction, if there are multiple entries for a key between the current and last updated date and time, the Secure Agent fetches only the latest entry for the key.
For example, consider that a record was inserted into a Customer table in SAP with the customer name set to John. The record was later updated and the name was changed to Bill. The Secure Agent fetches the name value as only Bill.
•You can't use the QUAN and CURR data types in delta extraction mappings.
•You can't configure delta extraction in data transfer tasks.
•To fetch the inserted or updated delta records to the target table, select Upsert as the operation and Update Else Insert as the update mode in the Target transformation. Otherwise, data corruption occurs in the target.
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.
1Create a mapping to read data from an SAP table and write data to a target.
2Click the Source transformation in the mapping.
3In the Properties panel, click the Source tab.
4Under the advanced properties, select one of the following values from the Update Mode list:
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.
