I have a really difficult issue that I am trying to solve. I am not sure I can fully explain the processes here well enough for others to understand, but I thought I would give it a shot. I basically have 2 processes involved, one seems fine and the other is problematic.
Process 1 creates a file in RPG by sql union of file5 and file6 to create file 7. It does inserts and updates to file 7 all week long, starting with a new date at midnight each night. File 7 has a constraint for date, location, id number. File 7 is also triggered on insert/update to 2 other summary files. This seems to work fine.
Process 2 is another RPG sql program which is a delete process. It fetches File 7 all day long in a loop, till eof, pauses, and cycles back thru it again. It looks up other detail data on a remote system (via sql package), and if remote file flag = ‘Y”, this process then issues an sql delete of the record in File 7 and File 6 in that order.
The problems:
1. Not all of the records get deleted all the time, despite evidence that they were flagged ‘Y’ in the remote file.
2. Sometimes they delete hours later, or not at all even though Process 1 cycles thru to eof on File 7 many times per hour.
3. It also misses the deletion of more data for the 2nd sql delete issued in Process 2, no matter which file I delete from first.
I have watched this process not delete a record on an id number known to be flagged as a Y and yet on every cycle, it still does not delete. There are too many records in the file to catch any particular one in debug mode. We have watched the I/O on the remote file and we are hitting the file, no connection issues.
This process can delete 99% of records on one day, which can number in the 10s of 1,000s, and then strangely miss a much larger volume the next day.
We have considered several things with this process already, and thrown several ideas up on the wall, but they all fall off . . . . if anyone has had similar problems with sql deletes, I’m all ears (and yes, it uses commit = none, not a rollback issue either).
Process 1 creates a file in RPG by sql union of file5 and file6 to create file 7. It does inserts and updates to file 7 all week long, starting with a new date at midnight each night. File 7 has a constraint for date, location, id number. File 7 is also triggered on insert/update to 2 other summary files. This seems to work fine.
Process 2 is another RPG sql program which is a delete process. It fetches File 7 all day long in a loop, till eof, pauses, and cycles back thru it again. It looks up other detail data on a remote system (via sql package), and if remote file flag = ‘Y”, this process then issues an sql delete of the record in File 7 and File 6 in that order.
The problems:
1. Not all of the records get deleted all the time, despite evidence that they were flagged ‘Y’ in the remote file.
2. Sometimes they delete hours later, or not at all even though Process 1 cycles thru to eof on File 7 many times per hour.
3. It also misses the deletion of more data for the 2nd sql delete issued in Process 2, no matter which file I delete from first.
I have watched this process not delete a record on an id number known to be flagged as a Y and yet on every cycle, it still does not delete. There are too many records in the file to catch any particular one in debug mode. We have watched the I/O on the remote file and we are hitting the file, no connection issues.
This process can delete 99% of records on one day, which can number in the 10s of 1,000s, and then strangely miss a much larger volume the next day.
We have considered several things with this process already, and thrown several ideas up on the wall, but they all fall off . . . . if anyone has had similar problems with sql deletes, I’m all ears (and yes, it uses commit = none, not a rollback issue either).
Comment