SQL Delete issue .... (warning, this is complicated!)

[EDIguru]

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).

[Sarge]

is there a chance of a record lock occurring? perhaps one or more of the records being read are not being released.

-Sarge

[EDIguru]

record locks?

We considered record locks since Process 1 is building the file at the same time, but I am not sure if a fetch in sql actually locks a records or not. I do know an sql delete does not lock a record. Also record locks would not explain why on some days a number of records do not eventually get deleted at all. You would think the process would eventually cycle and catch most if not all of the records to be deleted. Process 1 for the most part inserts records into this file in the morning and into the afternoon. In late afternoon and evening, updates begin to occur to existing records. Deletions may need to be executed at any time though.

Thanks for your response. I am looking for any ideas at this point. We have considered many things, too many to list them all. Any and all ideas are appreciated.

[rds5500]

Commitment Control?

There are 2 things I would check, one would be commitment control. Dependent upon how you are reading the file for deletes you could be getting record locks based on commitment control. The other thing would be is how are you getting to the end of file? Are you using SQL or RLA? If you are using RLA and the file reuses deleted records, I don't know if you would ever start the file over again. Just my 2 cents, hope it helps.

[EDIguru]

The process does delete records, just not all of them.
Both programs execute this at top of Main:
/Free
Exec SQL Set Option Commit = *None;


I am not sure I know, remember, what RLA is, but all file access is SQL. There is also a log file that tracks when the code reaches EOF.
Process2 Fetch loop:
Exec SQL
Declare C7 Cursor for
Select Date, Loc, Id from File7
Where Date = :Today Order by Date;

Exec SQL Open C7;
SqlStatus7 = Sqlstt;

//Check all exception records
DOW SqlStatus7 <> SqlNoData;
Exec SQL
Fetch next from C7
Into :7Date:7DateInd, :Loc:LocInd,
:ID:IDInd;
SqlStatus7 = Sqlstt;

IF SqlStatus7 = SqlYesData;
EXSR Check_RemoteFile_Flag;
ENDIF;

//Point connection back to local
EXEC Sql Set Connection :LDB_425;
ConnectStatus = Sqlstt;
RDB = LDB_425;
EXSR CheckConnection;

ENDDO;

Exec SQL Close C7;
DelayCmd = 'DLYJOB DLY(' + (RunDly) + ')';
RunCmd(DelayCmd);

//Add tracking--frequency of EOF on File7
IF TrackRestart = 'Y';
DelayStamp = %Timestamp();
Exec SQL
INSERT INTO LogFile (
JobName_425, DelayStamp_425, Text_425)
VALUES(:JobName, :7DelayStamp, :Text);
ENDIF;

[Guest.Visitor]

It sounds like record blocking. The records are cached in one process but not visible to the other program yet. I don't know if that happens with SQL but it happens with RPG file processing.

[Guest.Visitor]

... it depends on commit level. for no commit a record fetched from an updatable cursor will be locked till update or read of the next record (its special for db2/400). If the table is journaled, there might be another programm in another job locking records. with commit read stability and higher, all records ever read are locked until end of transaction and if there is no commit or rollback in this job, they will be locked until the Job ends (or a reclaim by RCLACTGRP for ILE and RCLRSC for OPM)

D*B

...but I am not sure if a fetch in sql actually locks a records or not