Using Embedded SQL on Very Large Files

From: Bruce Duty To: All

We are trying to use embedded SQL in an RPG program to update a multimillion record file. When I execute an SQL update statement in an RPG program, I get the message CPF5079 ("commitment control resource limit exceeded") and my database is not updated.

I've tried coding a WHENEVER SQLERROR CONTINUE statement with COMMIT HOLD after the UPDATE statement and looping until SQLCOD = 0, but apparently this particular error is not being recognized as an SQLERROR.

How can I limit the number of updates performed so I don't exceed the resource limit (or otherwise get around the 30,000 record limit)? I tried using WHERE COUNT(*) <=30000 in the update statement, but I got a syntax error.

From: James Coolbaugh To: Bruce Duty

Is there a reason you must use commitment control on your file update program? If not, you can compile the program with COMMIT(*NONE). This will allow your program to run without having commitment control active. Your file will still be journaled.

If you need commitment control, you will have to set up your program to read the file. The basic steps would be:

DEFINE CURSOR OPEN CURSOR FETCH (this gets the next record) UPDATE WHERE CURRENT OF... Count your records, at some point issue a COMMIT. Continue reading until end of file.

This is the general idea of how to read a file via SQL. The UPDATE WHERE CURRENT OF is how you can update the record just read. You might also specify on your SELECT statement the FOR UPDATE OF clause to indicate which fields are going to be updated.