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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fan Ruo Xin - 23 Nov 2004 01:48 GMT

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;

Reply to this Message

Matt Emmerton - 25 Nov 2004 16:51 GMT

> How to estimate the size of log space when I need to run "INSERT INTO
> tgt_tbl SELECT * FROM src_tbl WHERE..." ?
[quoted text clipped - 5 lines]
> 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

Reply to this Message

Fan Ruo Xin - 26 Nov 2004 23:18 GMT

> > How to estimate the size of log space when I need to run "INSERT INTO
> > tgt_tbl SELECT * FROM src_tbl WHERE..." ?
[quoted text clipped - 13 lines]
> --
> 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?

Reply to this Message

Matt Emmerton - 28 Nov 2004 18:05 GMT

> > > How to estimate the size of log space when I need to run "INSERT INTO
> > > tgt_tbl SELECT * FROM src_tbl WHERE..." ?
[quoted text clipped - 19 lines]
> 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

Reply to this Message

Fan Ruo Xin - 30 Nov 2004 03:42 GMT

> > > > How to estimate the size of log space when I need to run "INSERT INTO
> > > > tgt_tbl SELECT * FROM src_tbl WHERE..." ?
[quoted text clipped - 28 lines]
> --
> 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

Reply to this Message