Issues with SAP tools during migration/Unicode conversion
We raised some issues with the SAP migration tools during a replatforming project including Unicode conversion from SUN/Oracle to Windows SQL Server.
Our migration went well and all the log files from the technical migration reported no error, but during the application testing the customer claimed some missing data in a couple of tables.
These tables were splitted in at least four parts from the R3TA tool and exported and imported without any errors. After a detailed investigation, we found that some of the packages a empty:
0 rows exported and 0 rows imported.
A cross check inside the source SAP system with the select patterns out of the WHR files showed the missing entries. The select statement with the select criteria out of the HWR files executed directly on Oracle using sqlplus resulted with 0 rows again.
After tracing the selection of SE16 with ST05, we found the issue:
The WHERE condition inside SE16 was like
- SELECT COUNT(*) from "/BIC/ADC_CNT200" WHERE "CRM_OHGUID" <= '4909283A9B121CF6E10000000A31EA29' AND "CRM_ITMGUI" = ' ';
Result: 238656 rows
The WHERE clause with the conditions of the WHR files was:
- SELECT COUNT(*) from "/BIC/ADC_CNT200" WHERE "CRM_OHGUID" <= '4909283A9B121CF6E10000000A31EA29' AND "CRM_ITMGUI" = '';
Result: 0 rows
The difference is the blank in the second condition! So the error is caused by the '' in the where condition created by the R3TA tool.
After changing the WHR file and re-export/re-import the table, we got the same row count on both sides.
Now the customer raised the concern about the consistency of the migrated data. Therefore we checked the row count for all tables on the source and the target system and compared these values with the information recorded in the *.toc (export) and *.log files (import) to be sure we didn't lost any data on the way from Oracle to SQL.
Thereby we found two additional strange phenomena:
- The cluster table REGUC had app. 18.000 additional rows on the target side, but from the application point of view it was consistent.
- The row count information from the *.toc and *.log files was not consistent with the row count in the source and target DB
To 1.: The phenomena with REGUC was raised by the data-growth during the UNICODE conversion. The table is a table cluster resp. A physical cluster table with a VARDATA field containing compressed raw data. The content of the VARDATA field is extracted and converted during the migration/Unicode conversion. The length of the field (3.800 bytes) doesn't change - however after the Unicode conversion some data doesn't fit in one record only so you have more records on the target. So a continuation record is created and the table has more records after the migration. This could be verified with the following select statement, which needs to show the same values on the source and target:
- Select count(*) from REGUC where PAGENO=0;
(table records without continuation records)
To 2.: For some transparent tables (e.g. PA0000 in the HCM system) we had the same row count information in the *.toc (export) and *.log (import) files. Both files showed 145.189 rows, but a select count(*) on the source and target DB showed 142.615.
We reported all the issues to SAP. The issue with the R3TA is already corrected in version 7.20 and higher and will be back-ported to earlier versions (7.0x and 7.1x), too. The issue with the inconsistent row count is still under investigation from SAP. I will update the blog, if we get an response from SAP
Don't trust the log files of the SAP migration tools, you have to verify the consistency of the databases by additional checks.