tomthor

Corrupted Data Extraction Files / Duplicate Records

Discussion created by tomthor on Feb 16, 2012
Latest reply on Feb 23, 2012 by Chris_Hackett
Hi,

Using CA PPM ONDEMAND 12.1.0.5840 8002083_01_EBF 001

We are encountering corrupted data extraction files in our production environment and I was wondering if any of you have encountered this before or might have a clue as to what could cause this?

Our configuration is roughly as follows:

There are multiple CSV files that are generated to the CA SFTP server through scheduled jobs referencing DataExtraction names. Theses jobs run both daily and every 15 minutes. No jobs overlap and they do not run concurrently. The files that are created range from 1 mb to 100 mb.

For the last few days, we have noticed errors in several of the output files. Let me describe what such an error would look like in a CSV file.

Valid record row ("value", "value", "value", "value", "value", "value", "value", "value", "value", "value", "value", "value", )
Valid record row ("value", "value", "value", "value", "value", "value", "value", "value", "value", "value", "value", "value", )
Valid record row ("value", "value", "value", "value", "value", "value", "value", "value", "value", "value", "value", "value", )
Invalid/incomplete/corrupt record - just a part of a record
Duplicate record row ("value", "value", "value", "value", "value", "value", "value", "value", "value", "value", "value", "value", )
Duplicate record row ("value", "value", "value", "value", "value", "value", "value", "value", "value", "value", "value", "value", )
Duplicate record row ("value", "value", "value", "value", "value", "value", "value", "value", "value", "value", "value", "value", )

Here is an actual error
"100760","2012-03-14 00:00:00.0","8.000000","Open","","PRJ000748","5000820","CP","5140361","2012-03-18 00:00:00.0","2012-03-12 00:00:00.0","","2012-01-31 11:13:35.0","100760","1",""
"100760","2012-03-15 00:00:00.0","8.000000","Open","","PRJ000748","5000820","CP","5140361","2012-03-18 00:00:00.0","2012-03-12 00:00:00.0","","2012-01-31 11:13:35.0","100760","1",""
"100760","2012-03-16 00:00:00.0","8.000000","Open","","PRJ000748","5000820","CP","5140361","2012-03-18 00:00:00.0","2012-03-12 00:00:00.0","","2012-01-31 11:13:35.0","100760","1",""
er - Real Food Advertising","5062621","2012-02-05 00:00:00.0","2012-01-30 00:00:00.0","","2012-02-06 13:55:34.0","EUK01311","0",""
"100760","2012-03-14 00:00:00.0","8.000000","Open","","PRJ000748","5000820","CP","5140361","2012-03-18 00:00:00.0","2012-03-12 00:00:00.0","","2012-01-31 11:13:35.0","100760","1",""
"100760","2012-03-15 00:00:00.0","8.000000","Open","","PRJ000748","5000820","CP","5140361","2012-03-18 00:00:00.0","2012-03-12 00:00:00.0","","2012-01-31 11:13:35.0","100760","1",""
"100760","2012-03-16 00:00:00.0","8.000000","Open","","PRJ000748","5000820","CP","5140361","2012-03-18 00:00:00.0","2012-03-12 00:00:00.0","","2012-01-31 11:13:35.0","100760","1",""

At first I though that the corrupt record might contain diacritic characters which would explain what appears to look like a corrupt record row, however this is not true. A file with the described error can be perfectly created 10 times and then appear corrupted and then again can be created without any errors.

I cannot replicate this issue in our test environment.

Theories I have looked at but not had any luck with

1. Locks. I'm wondering if this could be caused by a system/database lock? There is nothing in the PRLOCK table that suggests that there are locks.
2. I've checked our development environment, test environment to see whether or not there are other jobs outputting to the production environment, there aren't any.
3. I've checked the scm_sch_jobs (?) table to see whether the application is creating the output files with these duplicate records/errors but it appears that the system is correcting the files with the right record count
4. the SQL that generates the output is also solid, nothing wrong there.

Have you guys seen anything like this?

Thanks,
Tomas

Outcomes