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

estimate log size of "insert into...select * from..."

P: n/a
How to estimate the size of log space when I need to run "INSERT INTO
tgt_tbl SELECT * FROM src_tbl WHERE..." ?
What is the difference of before image between the above INSERT stmt and the
following:
begin transaction
INSERT INTO tgt_tbl VALUES (...);
...
INSERT INTO tgt_tbl VALUES (...);
end transaction;
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a

"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message
news:EF***************@newssvr31.news.prodigy.com. ..
How to estimate the size of log space when I need to run "INSERT INTO
tgt_tbl SELECT * FROM src_tbl WHERE..." ?
What is the difference of before image between the above INSERT stmt and the following:
begin transaction
INSERT INTO tgt_tbl VALUES (...);
...
INSERT INTO tgt_tbl VALUES (...);
end transaction;


A rough estimate of how much log space will be needed is:
log space needed (in 4K pages) = number_of_rows * row_length (in bytes) *
1.05 (for log record overhead) / 4096

--
Matt
Nov 12 '05 #2

P: n/a

"Matt Emmerton" <ma**@gsicomp.on.ca> wrote in message
news:pt********************@rogers.com...

"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message
news:EF***************@newssvr31.news.prodigy.com. ..
How to estimate the size of log space when I need to run "INSERT INTO
tgt_tbl SELECT * FROM src_tbl WHERE..." ?
What is the difference of before image between the above INSERT stmt and the
following:
begin transaction
INSERT INTO tgt_tbl VALUES (...);
...
INSERT INTO tgt_tbl VALUES (...);
end transaction;


A rough estimate of how much log space will be needed is:
log space needed (in 4K pages) = number_of_rows * row_length (in bytes)

* 1.05 (for log record overhead) / 4096

--
Matt

Thanks, Matt. I thought the formula is for my second case. AFAIK, the first
one will use smaller log space than the second one. But I can't find any
reference from the db2 documentation. Do you know what the difference is
between the before&after image when db2 log both transactions?
Nov 12 '05 #3

P: n/a

"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message
news:QQ*******************@newssvr19.news.prodigy. com...

"Matt Emmerton" <ma**@gsicomp.on.ca> wrote in message
news:pt********************@rogers.com...

"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message
news:EF***************@newssvr31.news.prodigy.com. ..
How to estimate the size of log space when I need to run "INSERT INTO
tgt_tbl SELECT * FROM src_tbl WHERE..." ?
What is the difference of before image between the above INSERT stmt
and the
following:
begin transaction
INSERT INTO tgt_tbl VALUES (...);
...
INSERT INTO tgt_tbl VALUES (...);
end transaction;
A rough estimate of how much log space will be needed is:
log space needed (in 4K pages) = number_of_rows * row_length (in

bytes) *
1.05 (for log record overhead) / 4096

--
Matt

Thanks, Matt. I thought the formula is for my second case. AFAIK, the

first one will use smaller log space than the second one. But I can't find any
reference from the db2 documentation. Do you know what the difference is
between the before&after image when db2 log both transactions?


If both transactions do the same amount of work (ie, same number of inserts,
same data), then the amount of log space used will be the same.

--
Matt Emmerton
Nov 12 '05 #4

P: n/a

"Matt Emmerton" <ma**@gsicomp.on.ca> wrote in message
news:qo********************@rogers.com...

"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message
news:QQ*******************@newssvr19.news.prodigy. com...

"Matt Emmerton" <ma**@gsicomp.on.ca> wrote in message
news:pt********************@rogers.com...

"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message
news:EF***************@newssvr31.news.prodigy.com. ..
> How to estimate the size of log space when I need to run "INSERT INTO > tgt_tbl SELECT * FROM src_tbl WHERE..." ?
> What is the difference of before image between the above INSERT stmt and the
> following:
> begin transaction
> INSERT INTO tgt_tbl VALUES (...);
> ...
> INSERT INTO tgt_tbl VALUES (...);
> end transaction;

A rough estimate of how much log space will be needed is:
log space needed (in 4K pages) = number_of_rows * row_length (in
bytes)
*
1.05 (for log record overhead) / 4096

--
Matt

Thanks, Matt. I thought the formula is for my second case. AFAIK, the

first
one will use smaller log space than the second one. But I can't find any
reference from the db2 documentation. Do you know what the difference is
between the before&after image when db2 log both transactions?


If both transactions do the same amount of work (ie, same number of

inserts, same data), then the amount of log space used will be the same.

--
Matt Emmerton

Hi, Matt,
I did a test (maybe) two months ago - I build a table A and load a couple of
records, and copy the ddl and data to tableB.
The "delete from tableA" and empty tableA and then "import ... into tableA"
without COMMITCOUNT failed as "log full". But not "insert into tableA select
* from tableB".
I will double check if I can reproduce the scenario.
Thanks,
FRX
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.