Hi,
This is a problem I've been trying to solve sometime and finally found a solution.
When I have to do an update ( or delete) on many records, I was limited in how to go about it in a live production environment. Updating all the records in one transaction was out of the question, as it would block all other processes.
Using the loop dataset function in Toad was another solution, but as it would commit after every record, this was also a waste of time and resources.
The next best solution was to use a select to fetch the first X rows and updating them. This improved the process some.
Another improvement was when I did the update on a selected range of IDs. I would start using a variable in Toad Automation based on the the min (id) that met the criteria and then incrementing the value in every loop.
The first simple addition in DB2 v12 is the ablitiy to write "FETCH FIRST X ROWS" in an update or delete statement. The downside is that I have to query the DB for matching criteria every statement.
The real improvement is the addition of the LISTAGG which enbles me to specify an X amount of IDs in every recordset. This allows me to select hundreds of thousands of records having each row contain a set number of IDS that I can use in the UPDATE or DELETE activity using loop data set.
I'll try to copy an example later on ( MANYANA).
Sincerely,
Shimon