"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