473,327 Members | 2,012 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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 1703
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: DP | last post by:
Is there any command exists as "commit transaction" ? begin transaction < DML1 stmts > begin transaction < DML2 stmts > rollback transaction commit transaction which set of DML will be...
11
by: Markus Breuer | last post by:
I have a question about oracle commit and transactions. Following scenario: Process A performs a single sql-INSERT into a table and commits the transaction. Then he informs process B (ipc) to...
0
by: Fan Ruo Xin | last post by:
Nothing got wrong with DB2 COMMIT, db2 cfg, ... Even you did an "insert some_id+1 ..." immediately after you did "select max(some_id) from .... " in session1. This will not block the operations...
3
by: Mark | last post by:
If a java applicaiton using the type 4 driver calls a DB2 stored procedure, does the stored procedure need to do its own commit when updates are completed? If the stored procedure does a commit or...
2
by: janet | last post by:
HI, I had a question on auto-commit in DB2 EEE V8 on AIX V5. Here is my example.. there are two script A.sh , B.SQL A.sh is following:
9
by: Anurag | last post by:
ENVIRONMENT: ============ (1) AIX 5.2 ML 8 and AIX 5.3 TL 4. (2) DB2 ESE on some servers and DB2 Connect Enterprise Edition on others. QUESTION: ========= I upgrade DB2 on some 15 Production...
5
by: jefftyzzer | last post by:
Friends: Anyone know how I can EXPLAIN a dynamic SQL statement that SELECTs from DGTT's in a stored procedure? I don't want to create permanent versions of the DGTTs and run an explain...
5
by: jefftyzzer | last post by:
Friends, Three questions for you: 1. Are CREATE INDEX statements against DGTTs logged? 2. While running the SP recently, it died with a "transaction log filled" (SQL0964C) message while it...
0
by: Jim Kennedy | last post by:
ALL DDL does a commit. Hence Drop Table movies; issues a commit. True you don't issue a commit and the driver does not issue a commit, but the server does for all DDL. That is probably where...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.