By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,778 Members | 1,074 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,778 IT Pros & Developers. It's quick & easy.

dynamic compound statement

P: n/a
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....
Nov 19 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
Nov 20 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.