dynamic compound statement

[doug.partch]

I have problem where a ETL team is trying to update a column in a very
large table. They are trying to do this in single query. Also each
time they run it consumes all available space in the logs and this in
turn abends their process. We are limited on log space.

I would like to use a SQL dynamic compound statement, "basically
adding commit processing after X amount of rows". I already create a
Stored Procedure(SP) that works. However they do not wish to call an
SP. And before you ask. No they do not want to write a program,
they want it all to be in SQL!!!

I have been playing round with ATOMIC SQL but I can't seem to get it
to work.

Basic Code:
1. Set variable for how often to commit.
2. Declare cursor for update
3. open cursor
4. Loop until end or table
4a. fetch row
4b. update row based on cursor
4c. check if commit limit reached if so commit and reset counter
5. at end one last commit and close cursor.

Any ideas:

Thanks for any help....

[Serge Rielau]

do*********@db2helpdesk.com wrote:

I have problem where a ETL team is trying to update a column in a very
large table. They are trying to do this in single query. Also each
time they run it consumes all available space in the logs and this in
turn abends their process. We are limited on log space.

I would like to use a SQL dynamic compound statement, "basically
adding commit processing after X amount of rows". I already create a
Stored Procedure(SP) that works. However they do not wish to call an
SP. And before you ask. No they do not want to write a program,
they want it all to be in SQL!!!

I have been playing round with ATOMIC SQL but I can't seem to get it
to work.

SQL has an old fashioned appoach to ATOMIC's they are undividable.
So what you want to do is not possible.
The stored procedure is the way to go, unless you want to revisit your
log-policy.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab