Problem with DB2_USE_ALTERNATE_PAGE_CLEANING
in [DB2]
|
Prev: Perl DBD DB2
Next: Instance Crash
From: memmerto@yahoo.com on 18 Sep 2006 15:21 Hemant, > Thanks for all your help, I will tyr to make the recommended changes, but > I am still not clear about the 2 initial problems: > > 1) Why do I get -964 when DB2_USE_ALTERNATE_PAGE_CLEANING is set to YES. Using DB2_USE_ALTERNATE_PAGE_CLEANING (APC) changes how dirty pages are written to disk. Generally, APC is more intelligent about which pages it writes to disk, and is more aggresive in it's actions. However, it is more sensitive to the configuration of the system, and can impact logging in certain cases. Generally speaking, the page cleaners write dirty (modified) pages to disk after the associated transaction has committed. If you have a lot of pages being modified (such as when you are inserting many new rows into a table), this consumes a lot of log space and increases the amount of pages that need to be written to disk by the cleaners. With circular logging enabled, once the transaction log fills up, DB2 starts over with the first log file. However, if there are dirty pages in bufferpool that are associated with the transaction from the first log file, DB2 cannot re-use the log file and you will get a -964. Because you are modifying a lot of pages, and you are committing infrequently (COMMITCOUNT 5000, although DB2 may be committing internally more frequently), the page cleaners are unable to write out the modified pages quick enough. This means that when DB2 attempts to re-use the first log file, it cannot because there are dirty pages associated with that log file and you get a -964 error. To rectify this, you can do one of the following: 1) Disable APC and use "normal" page cleaners 2) Increase LOGPRIMARY and/or LOGFILSIZ, which will increase the amount of transaction log available to your application 3) Decrease SOFTMAX, which will make the page cleaners write pages to disk more quickly > 2) If my last commit was at 30000th row how did the extra rows get commited? Take note that the explicit commits done by the utilities (as specified by COMMITCOUNT) are not the only commits that will be done. DB2 will issue internal commits in certain situations. This is why you get 32,000 rows in the table instead of the 30,000 that you expect. Note that when DB2 commits internally a message is not displayed -- the messages you see are from the utilities doing the explicit commits every 5000 rows. -- Matt Emmerton
From: Hemant Shah on 19 Sep 2006 09:52 While stranded on information super highway memmerto(a)yahoo.com wrote: > Hemant, > Matt, Thanks for the detailed explaination. > >> 2) If my last commit was at 30000th row how did the extra rows get commited? > > Take note that the explicit commits done by the utilities (as specified > by COMMITCOUNT) are not the only commits that will be done. DB2 will > issue internal commits in certain situations. This does not make sense. Why would DB2 commit the data without explicit commit, what happens when a process encounters error and does a roll back (which my application did when it received -964)? In my case I end up with extra rows that I did not expect. In this case it did not make difference because I can start loading the table again, but it could be disasterous in OLTP environment. I have never encountered this problem with DB2 before, I have been working with DB2 UDB since V2. > > This is why you get 32,000 rows in the table instead of the 30,000 that > you expect. Note that when DB2 commits internally a message is not > displayed -- the messages you see are from the utilities doing the > explicit commits every 5000 rows. > > -- > Matt Emmerton > -- Hemant Shah /"\ ASCII ribbon campaign E-mail: NoJunkMailshah(a)xnet.com \ / --------------------- X against HTML mail TO REPLY, REMOVE NoJunkMail / \ and postings FROM MY E-MAIL ADDRESS. -----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------ I haven't lost my mind, Above opinions are mine only. it's backed up on tape somewhere. Others can have their own.
From: memmerto@yahoo.com on 20 Sep 2006 01:01 Hemant Shah wrote: > >> 2) If my last commit was at 30000th row how did the extra rows get commited? > > > > Take note that the explicit commits done by the utilities (as specified > > by COMMITCOUNT) are not the only commits that will be done. DB2 will > > issue internal commits in certain situations. > > This does not make sense. Why would DB2 commit the data without explicit > commit, what happens when a process encounters error and does a roll back > (which my application did when it received -964)? > > In my case I end up with extra rows that I did not expect. In this case > it did not make difference because I can start loading the table again, > but it could be disasterous in OLTP environment. Please show me the full LOAD command that you are using, and I can explain more. -- Matt Emmerton
From: Hemant Shah on 20 Sep 2006 14:08 While stranded on information super highway memmerto(a)yahoo.com wrote: > > Hemant Shah wrote: >> >> 2) If my last commit was at 30000th row how did the extra rows get commited? >> > >> > Take note that the explicit commits done by the utilities (as specified >> > by COMMITCOUNT) are not the only commits that will be done. DB2 will >> > issue internal commits in certain situations. >> >> This does not make sense. Why would DB2 commit the data without explicit >> commit, what happens when a process encounters error and does a roll back >> (which my application did when it received -964)? >> >> In my case I end up with extra rows that I did not expect. In this case >> it did not make difference because I can start loading the table again, >> but it could be disasterous in OLTP environment. > > Please show me the full LOAD command that you are using, and I can > explain more. I am not using LOAD command. My application loops through a file and reads data in proprietory format, decodes it and runs INSERT command. It executes COMMIT every 5000th row. If it encounters error, it does ROLLBACK and exits. Last commit was on 30000th row, and it gets -964 after that, application does ROLLBACK and exits. I should not have more than 30000 rows in the table. Again, this only happens if DB2_USE_ALTERNATE_PAGE_CLEANING is set to YES. > > -- > Matt Emmerton > -- Hemant Shah /"\ ASCII ribbon campaign E-mail: NoJunkMailshah(a)xnet.com \ / --------------------- X against HTML mail TO REPLY, REMOVE NoJunkMail / \ and postings FROM MY E-MAIL ADDRESS. -----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------ I haven't lost my mind, Above opinions are mine only. it's backed up on tape somewhere. Others can have their own.
From: memmerto@yahoo.com on 27 Sep 2006 11:12
Hemant Shah wrote: > While stranded on information super highway memmerto(a)yahoo.com wrote: > > > > Hemant Shah wrote: > >> >> 2) If my last commit was at 30000th row how did the extra rows get commited? > >> > > >> > Take note that the explicit commits done by the utilities (as specified > >> > by COMMITCOUNT) are not the only commits that will be done. DB2 will > >> > issue internal commits in certain situations. > >> > >> This does not make sense. Why would DB2 commit the data without explicit > >> commit, what happens when a process encounters error and does a roll back > >> (which my application did when it received -964)? > >> > >> In my case I end up with extra rows that I did not expect. In this case > >> it did not make difference because I can start loading the table again, > >> but it could be disasterous in OLTP environment. > > > > Please show me the full LOAD command that you are using, and I can > > explain more. > > I am not using LOAD command. My application loops through a file and > reads data in proprietory format, decodes it and runs INSERT command. > > It executes COMMIT every 5000th row. If it encounters error, it does > ROLLBACK and exits. > > Last commit was on 30000th row, and it gets -964 after that, application > does ROLLBACK and exits. I should not have more than 30000 rows in the table. > > Again, this only happens if DB2_USE_ALTERNATE_PAGE_CLEANING is set to YES. What type of interface does your application use to talk to DB2? CLI? Static C? JDBC? -- Matt Emmerton |