When you have corrupt data, delta loads may fail in the source system during extraction. To address the complex problem of corrupt data, the most common solution is to do a complete re-initialization of the data. Completely re-initializing the data, though clean, has several disadvantages, such as it requires a significant amount of time and effort and system downtime. This downtime has financial repercussions in terms of data availability as well as the inability to post transactions during lengthy downtimes.

Consider the following scenario: A developer is working on the logistics extractor 2LIS_02_ITM. The developer is enhancing the extractor structure and wants to add fields. The daily delta loads are already scheduled in the production system. The 2LIS_02_ITM extractor is set to extract on queued delta mode (Figure 1).

Figure 1

Extractor resides on the queued delta

The developer transports changes to the extract structure to the production environment and forgets to clear the LUWs residing in the extraction queue or transaction LBWQ. Consequently, the LUWs in the extraction queue reside as per the old extract structure even though the structure has been now changed due to the transport. As a result, the background (V3) jobs fail, prompting the developer to employ a hash solution (as described in SAP Note 834897) to force the V3 job to complete successfully and pass these records to the delta queue.

However, the delta loads fail due to corruption of the delta queue. Records in the delta queue can become corrupt for a variety of reasons, including the following:

• Changes to extract structure: Data in the delta queue becomes corrupted due to the changes in the extract structure. Say you have data residing in the extraction queue (LBWQ, in this example) and a developer sends a transport request that modifies the extract structure without clearing the extraction queue. In this case, the data in the extraction queue is no longer aligned to the new extract structure and the data becomes corrupted.
• Lack of memory space: Data in the delta queue becomes corrupted when a delta load fails due to lack of memory space for the job to complete the extraction. For example, when many LUWs are collected in the outbound queue, such as a logistics extractor, the system may not have enough memory for the job to complete and, consequently, the data becomes corrupt.

In our scenario, the LUWs that were in the extraction queue prior to the transport are corrupt. Only the records passed to the queue after the change in the extract structure would be correct as they would reconcile to the new extract structure.

A Better Solution: Use the Transaction ID (TID)

A quicker and less resource-intense solution to address corrupt data is to use the information the TIDs supply. (If you are unfamiliar with TIDs, refer to the sidebar “Understanding the TID.”) Because every purchase order has a corresponding TID, you can identify and segregate the corrupted purchase order data by displaying the delta queue using transaction RSA7 (Figure 2). Identify the corrupted records by looking up the contents of the delta queue. Change the display variants to select Host ID, Process ID, Time, and Counter fields.

Figure 2

Use the delta queue to identify the corrupted records

After identifying the corrupted records, concatenate the Host ID, Process ID, Time, and Counter fields. This logs the TIDs for the corrupted records. When you have the list of TIDs for the corrupted purchase order documents, you can delete the TID from the outbound queue (transaction SMQ1) and then delete the corresponding purchase order from the delta queue. Deleting the TIDs from the outbound queue cleans the corrupted records from queue and enables delta processing to resume. Follow these steps to delete the necessary TIDs:

Step 1. Go to the outbound queue (transaction SMQ1). Enter the DataSource name and click the execute icon.

Step 2. Display the output and note the TID of the corrupt purchase order. Double-click the DataSource in the output or click the Display button.

Step 3. Search for the TID in the outbound queue by finding the TID of the corrupted purchase order in the output screen. Select the TID and go to Edit > Delete LUW. Deleting the TID removes the corrupted purchase order document from the delta queue. Repeat this step to delete all the corrupted purchase order documents from the delta queue.

Alternatively, you can delete the records using the standard ABAP program RSTRFCQDS. Specify the Queue Name and choose Display to see the output (Figure 3). The Parallel check box should not be checked because the system will delete the data when you run the program. The queue name that you need to enter corresponds to the name of the affected DataSource. The user can find the queue name in the transaction SMQ1 output. Once all corrupted records are removed, you can resume deltas.

Figure 3

Delete records using transaction RSTRFCQDS

Restore Your Data

Now you must load all the purchase order records that you removed from the delta queue back into SAP NetWeaver BW.

To reload the purchase order records, perform the following steps: 

Step 1. Delete the setup tables for purchasing via transaction LBWG by entering the application and clicking the execute icon (Figure 4). In our example, we entered 02 for purchasing data.

Figure 4

Delete the purchasing setup tables via transaction LBWG

Step 2. Run the statistical set-up job via transaction SBIW for each purchase order identified in step 2 of the “A Better Solution: Using the TIDs to Delete Only the Corrupt Data” section. The set-up job fills the set-up tables with data from the source system application tables. To access the statistical set-up screen in transaction SBIW, follow menu path Settings for application specific datasources (PI) > Logistics > Managing extract structures > Initialization > Filling in the set up table > Application-specific setup of statistical data > Perform setup – Purchasing (Figure 5).

Figure 5

Run transaction SBIW for purchase orders

Step 3. Perform a selective deletion of these purchase orders in the data targets to ensure that no data related to these purchase orders already exists in the data targets. To do this, access the data target in the Administrator Workbench (transaction RSA1) and click DeleteSections.

Step 4. Run the Repair Full Request InfoPackage by following menu path Menu > Scheduler > Repair Full Request in the Infopackage. Load data in the data target (Figure 6). This restores all the purchase orders that you deleted from the delta queue.

Figure 6

Restore the purchase orders deleted from the delta queue

Once the data is loaded, reconcile your SAP NetWeaver BW data with SAP ERP by running your reports. The data now reconciles perfectly.

Check the Integrity of Your Data

Deleting data selectively from the delta queue and restoring it does not adversely affect any other extractor in the system, nor does it affect any data in the delta queue related to this extractor. To verify this, examine the content of the extractor before and after deletion by performing the following steps:

Step 1. Use transaction SMQ1 to determine how many LUW entries reside in the outbound queue and note the number. You use this number to check the integrity of data and to confirm that no other data has been deleted. The example in Figure 7 has 2,254 LUW entries in outbound queue 2LIS_02_ITM.

Figure 7

Determine how many LUW entries reside in the outbound queue

Step 2. Determine how many entries are in the delta queue for a purchase order. In this example, purchasing document 4201138358 has the two entries shown in Figure 8. You use this number later to compare records in the delta queue before and after deletion.

Figure 8

Determining the number of entries in the delta queue

Step 3. Obtain the corresponding TID for the two entries you found in the previous step (Figure 9). Note that in this example, the two TID entries correspond to one LUW — the TID is the same for both entries.

Figure 9

Determine the TID for the entries in the delta queue

Step 4. Determine the number of entries in the delta queue (transaction RSA7). The delta queue shows the actual number of records in the delta queue corresponding to the number of LUWs in the outbound queue (transaction SMQ1). The actual number of entries in the delta queue in our example is 10,028 (Figure 10). This corresponds to the 2,254 LUW entries in the outbound queue.

Figure 10

Determine the number of entries in the delta queue in transaction RSA7

Step 5. Delete the two records for purchase order 4201138358 by following the process outlined in the “A Better Solution: Use the Transaction ID (TID)” section using the derived TID.

Step 6. Check the outbound queue (transaction SMQ1). Figure 11 shows that the LUW count has decreased by 1 to 2,253, which corresponds to the two records for purchase order 4201138358. It also shows that the LUW count for the other extractors remains unaffected, which proves that no other extractors were affected and data integrity is protected.

Figure 11

The number of entries for 2LIS_02_ITM is reduced by one

Step 7. Go to transaction RSA7 and notice that the count in the delta queue has declined by two (Figure 12). This corresponds to the two records associated with the purchase order that we deleted.

Figure 12

Confirm that the erroneous records are deleted

Other Uses for Checking the Data Integrity

You can apply the process of checking the integrity of your data to other situations, including the following:

• The delta data load fails due to a computation error in the extractor (BCD_OVERFLOW). In this case, you can identify the particular record causing the overflow and selectively remove it from the delta queue using the above procedure.
• Memory overflow dumps. A large number of LUWs may collect in the delta queue, such as when a delta data load is not run for several days and the system keeps adding data to the delta queue, increasing its size. When executed, the delta extractor job runs into insufficient memory to process this large amount of data and causes memory overflow dumps. In this case, you can identify specific records and delete them from the delta queue. For example, you could remove all records that belong to CALMONTH 05/2009. The delta then resumes and data pertaining to 05/2009 would be loaded back into the system.
• You can identify a record written to the delta queue with a type mismatch error by its TID and delete it to resume deltas

This method works best with LO extractors, but you can use it for other extractors as well when you encounter memory dumps. For example, in an SAP ERP HCM extractor, three LUWs might correspond to 300 records — 100 records per LUW based on CALDAY. You can still delete data for one of the LUWs, which deletes the 100 records corresponding to it. This clears some of the data so deltas can resume.

Abhijit Ghate

Abhijit Ghate has a bachelor’s degree in electronics engineering and more than 10 years of SAP experience in BI and ABAP, encompassing various industry verticals, spanning several implementation and support projects. He is an SAP-certified solution consultant and is ITIL V3 Foundation-certified as well.

You may contact the author at abhijit.ghate@gmail.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.

Shreekant W. Shiralkar

Shreekant W. Shiralkar is a senior management professional with experience on leading and managing business functions as well as technology consulting. He has authored best selling books and published many white papers on technology. He also holds patents for innovations. Presently he is global head of the SAP Analytics Centre of Excellence at Tata Consultancy.

You may contact the author at s-shiralkar@yahoo.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.