469,954 Members | 1,685 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,954 developers. It's quick & easy.

COMMIT and DGTTs

Friends:

I've written a stored procedure that, among other things, writes
several million rows to a declared global temporary table (DGTT)
declared as NOT LOGGED using a (cursor) FOR loop. My rows-inserted
throughput starts out lightning-fast, but eventually plateaus to about
1 row per second. On what I thought was a lark, I decided to alter the
loop's cursor to WITH HOLD (the DGTT is, of course, declared to
preserve rows on COMMIT) and added a COMMIT to the INSERTing FOR loop
every 2000 records. Performance is now MUCH improved.

Questions:

1. If the query the cursor is based on is FOR READ ONLY WITH UR, and if
DGTT's, as private session objects, don't acquire locks and the one
I've declared is not logged, why did the COMMITing help so much? There
should be no locks to release nor accumulated log buffer entries to
flush.

2. Conventional wisdom says that COMMITs are expensive and are thus
recommended to be used sparingly, yet they made things better for my
processing, not worse. Why?

3. Does a COMMIT--in addition to releasing locks and causing log buffer
entries to be written--also force all dirty pages in the buffer pool to
be written to disk?

Details: IBM DB2 UDB 8.2.3 on AIX 5.2

Thank you for your considered replies.

--Jeff

Jun 27 '06 #1
1 1549
3) for your answer: Yes almost, not forced but: as the page is dirty, it can
be externalized like any other dirty page in the bp. Since you do not
commit often, then DB2 has to rely on finding space for new pages coming in.
Without more info, I'd look at first the user temp space defined for the
DGTT.
Is its buffer poll shared with everything or not. If as you say there are
nillions of rows and you don't commit often, then it's possible you end up
with having to externalize uncommitted pages to the tempspace for the dgtt.
DB2 is trying to bring in the pages for the query and does not find clean
pages to replace. It has to externalize dirty pages to make room.

A snapshot, with the buffer pool switch on, could tell you what is doing the
writing. Cleaners for threshold hit or dirty page steal cleans.
HTH, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"jefftyzzer" <je********@sbcglobal.net> a écrit dans le message de news:
11**********************@r2g2000cwb.googlegroups.c om...
Friends:

I've written a stored procedure that, among other things, writes
several million rows to a declared global temporary table (DGTT)
declared as NOT LOGGED using a (cursor) FOR loop. My rows-inserted
throughput starts out lightning-fast, but eventually plateaus to about
1 row per second. On what I thought was a lark, I decided to alter the
loop's cursor to WITH HOLD (the DGTT is, of course, declared to
preserve rows on COMMIT) and added a COMMIT to the INSERTing FOR loop
every 2000 records. Performance is now MUCH improved.

Questions:

1. If the query the cursor is based on is FOR READ ONLY WITH UR, and if
DGTT's, as private session objects, don't acquire locks and the one
I've declared is not logged, why did the COMMITing help so much? There
should be no locks to release nor accumulated log buffer entries to
flush.

2. Conventional wisdom says that COMMITs are expensive and are thus
recommended to be used sparingly, yet they made things better for my
processing, not worse. Why?

3. Does a COMMIT--in addition to releasing locks and causing log buffer
entries to be written--also force all dirty pages in the buffer pool to
be written to disk?

Details: IBM DB2 UDB 8.2.3 on AIX 5.2

Thank you for your considered replies.

--Jeff


Jun 27 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by DP | last post: by
11 posts views Thread by Markus Breuer | last post: by
reply views Thread by Fan Ruo Xin | last post: by
3 posts views Thread by Mark | last post: by
2 posts views Thread by janet | last post: by
9 posts views Thread by Anurag | last post: by
5 posts views Thread by jefftyzzer | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.