Hello All,

This problem has been discussed here and I do not know why I am still facing problems on that.
The problem should update a table with about 3,000,000 rows using a controlled flow which needs to commit the data every 10,000 rows, may be more but, not enough to run on long transactions or lock table overflow.

Errors like:
exception : looking for handler
SQL error = -255 ISAM error = 0 error string = = ""
exception : no appropriate handler
or
SQL Error =-535 have been occurring on the following procedure.

CREATE PROCEDURE update_t1();
DEFINE count INT;
DEFINE my_rowid INT;
LET count = 0;
LET my_rowid = 0;
FOREACH cur_su WITH HOLD FOR
SELECT rowid INTO my_rowid FROM t1 WHERE 1=1
IF count = 10000 THEN
COMMIT WORK;
BEGIN WORK;
LET count = 1;
ELSE
UPDATE t1
SET col1=10.00, col2=9.34
WHERE rowid = my_rowid;
LET count = count + 1;
END IF
END FOREACH
END PROCEDURE;

Any help will be very appreciate.Thanks

I see only one mistake...
where is the last commit?
If you have 10.001 records, the foreach will finish on this record and any commit will be executed...
You need to create a transaction control, like this:

If your error not have any with this situation... my suggestion is use a range of key field to execute this update. I believed will be faster too.
To know how much records are for each key , you can use the update statistics histogram (dbschema -hd option). But, keep your statistics updated for this!

Thanks for you prompt reply Ceinma,
Although, I have implemented your recommendatios, I am still facing problems. Can you take a look into the log file?

update ed_ticket set
(col1) = (10.00),
(col2) = (9.34)
where (= rowid, my_rowid);
expression+ count, 1)
evaluates to 2000
let count = 2000
select cursor iteration.
select cursor returns 73222
expression= count, 2000)
evaluates to t

commit work;
exception : looking for handler
SQL error = -255 ISAM error = 0 error string = = ""
exception : no appropriate handler

So, 2,000 rows have been updated but not the rest of the table.

Thank you,
Ed

Hum... i think you will need the IBM Support...

You shall set ''Begin work" statement inside foreach cursor in order to set beginning of your job, so you can set one check as following:
IF count = 0 THEN
BEGIN WORK;
END IF;