Problem with DB2_USE_ALTERNATE_PAGE_CLEANING
in [DB2]
|
Prev: Perl DBD DB2
Next: Instance Crash
From: Mark A on 13 Sep 2006 01:19 "Hemant Shah" <shah(a)typhoon.xnet.com> wrote in message news:ee6emh$a3o$1(a)new7.xnet.com... > Mark, > > 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. > I don't know for sure, but it probably has something to do with having a small number of 32K pages in each 32K bufferpool. You probably have some indexes and small tables where the etire object fits in one 4K page, and you are wasting a lot of resources. > 2) If my last commit was at 30000th row how did the extra rows get > commited? I don't remember the exact scenario that you are describing, nor do I wish to revisit that issue. When I see someone using a large number of very poor configuration parameters in DB2, then I think that should be addressed first, and then if everything works after fixing them, I don't worry about exactly whey it did not work previously.
From: Bernard Dhooghe on 14 Sep 2006 04:59 In the DB2 online support (http://www-306.ibm.com/software/data/db2/udb/support/) searching on DB2_USE_ALTERNATE_PAGE_CLEANING gives a number of APAR's, one is: " IY58576: ADM1822W messages in the db2diag.log when using DB2_USE_ALTERNATE_PAGE_CLEANING Problem has been fixed in V8.2 FP8 (s041221) " But FP10 looks to be installed. If the FP is indeed on this machone, seems the problem is still not cured in all cases. Bernard Dhooghe Hemant Shah wrote: > Folks, > > I spent better part of morning debugging a problem and it turned out to be > DB2_USE_ALTERNATE_PAGE_CLEANING registry variable. > > I am running DB2 8.2 on AIX 5.3 system: > > # oslevel > 5.3.0.0 > > # db2level > DB21085I Instance "db2test" uses "32" bits and DB2 code release "SQL08023" > with level identifier "03040106". > Informational tokens are "DB2 v8.1.1.96", "s050811", "U803920", and FixPak > "10". > Product is installed at "/usr/opt/db2_08_01". > > > I had set DB2_USE_ALTERNATE_PAGE_CLEANING to see if it made any difference > in performance, and forgot about it because aour test environment was not > being used for several weeks. Today I tried to load large amount of data into > the database using a C program. It reads a file in propriety format and > INSERTs data into the table. It COMMITS data every 5000 rows and if it gets > error it will do ROLLBACK and exit. > > Some of the smaller tables loaded without any problem, but larger tables > got SQL -964 error (transaction log full). > > Couple things happened when I had set DB2_USE_ALTERNATE_PAGE_CLEANING to YES: > > 1) I would get -964 after inserting 30000 rows, I only tested this with one > file and it always got -964 somewhere between 30000 and 35000 rows. > > 2) My last commit is done at 30000th row, then after that I get -964 my > program does ROLLBACK and exits. When I run SELECT COUNT(*) on the table > I have little more than 32000 rows. > How did the extra rows got commited? The table should only have 30000 rows. > I start out with an empty table. > > > > When I unset DB2_USE_ALTERNATE_PAGE_CLEANING registry variable, everything > works fine. > > Is this a bug or a feature? > > The DB2 documentation on DB2_USE_ALTERNATE_PAGE_CLEANING does not clarify > this. > > Here is what I found on IBM site: > > DB2_USE_ALTERNATE_PAGE_CLEANING: Default=not set: Values: ON, OFF > > Specifies whether DB2 uses the alternate method of page cleaning algorithms > instead of the default method of page cleaning. When this variable is set to > "ON," DB2 uses a proactive method of page cleaning, writing changed pages to > disk, keeping ahead of LSN_GAP, and proactively finding victims. Doing this > allows the page cleaners to better utilize available disk I/O bandwidth. > > When this variable is set to "ON," the chngpgs_thresh database configuration > parameter is no longer relevant because it does not control page cleaner > activity. > > > Thanks for you help. > > -- > 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: Hemant Shah on 14 Sep 2006 13:19 Yes, FP10 is installed on the system but instfix says that APAR is not installed: # instfix -i -k IY58576 There was no data for IY58576 in the fix database. While stranded on information super highway Bernard Dhooghe wrote: > In the DB2 online support > (http://www-306.ibm.com/software/data/db2/udb/support/) searching on > DB2_USE_ALTERNATE_PAGE_CLEANING gives a number of APAR's, one is: > > " > IY58576: ADM1822W messages in the db2diag.log when using > DB2_USE_ALTERNATE_PAGE_CLEANING > > Problem has been fixed in V8.2 FP8 (s041221) > " > > But FP10 looks to be installed. > > If the FP is indeed on this machone, seems the problem is still not > cured in all cases. > > Bernard Dhooghe > > Hemant Shah wrote: >> Folks, >> >> I spent better part of morning debugging a problem and it turned out to be >> DB2_USE_ALTERNATE_PAGE_CLEANING registry variable. >> >> I am running DB2 8.2 on AIX 5.3 system: >> >> # oslevel >> 5.3.0.0 >> >> # db2level >> DB21085I Instance "db2test" uses "32" bits and DB2 code release "SQL08023" >> with level identifier "03040106". >> Informational tokens are "DB2 v8.1.1.96", "s050811", "U803920", and FixPak >> "10". >> Product is installed at "/usr/opt/db2_08_01". >> >> >> I had set DB2_USE_ALTERNATE_PAGE_CLEANING to see if it made any difference >> in performance, and forgot about it because aour test environment was not >> being used for several weeks. Today I tried to load large amount of data into >> the database using a C program. It reads a file in propriety format and >> INSERTs data into the table. It COMMITS data every 5000 rows and if it gets >> error it will do ROLLBACK and exit. >> >> Some of the smaller tables loaded without any problem, but larger tables >> got SQL -964 error (transaction log full). >> >> Couple things happened when I had set DB2_USE_ALTERNATE_PAGE_CLEANING to YES: >> >> 1) I would get -964 after inserting 30000 rows, I only tested this with one >> file and it always got -964 somewhere between 30000 and 35000 rows. >> >> 2) My last commit is done at 30000th row, then after that I get -964 my >> program does ROLLBACK and exits. When I run SELECT COUNT(*) on the table >> I have little more than 32000 rows. >> How did the extra rows got commited? The table should only have 30000 rows. >> I start out with an empty table. >> >> >> >> When I unset DB2_USE_ALTERNATE_PAGE_CLEANING registry variable, everything >> works fine. >> >> Is this a bug or a feature? >> >> The DB2 documentation on DB2_USE_ALTERNATE_PAGE_CLEANING does not clarify >> this. >> >> Here is what I found on IBM site: >> >> DB2_USE_ALTERNATE_PAGE_CLEANING: Default=not set: Values: ON, OFF >> >> Specifies whether DB2 uses the alternate method of page cleaning algorithms >> instead of the default method of page cleaning. When this variable is set to >> "ON," DB2 uses a proactive method of page cleaning, writing changed pages to >> disk, keeping ahead of LSN_GAP, and proactively finding victims. Doing this >> allows the page cleaners to better utilize available disk I/O bandwidth. >> >> When this variable is set to "ON," the chngpgs_thresh database configuration >> parameter is no longer relevant because it does not control page cleaner >> activity. >> >> >> Thanks for you help. >> >> -- >> 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. > -- 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: Mark A on 14 Sep 2006 14:02 "Hemant Shah" <shah(a)typhoon.xnet.com> wrote in message news:eec2ud$1ml$1(a)new7.xnet.com... > > Yes, FP10 is installed on the system but instfix says that APAR is not > installed: > > # instfix -i -k IY58576 > There was no data for IY58576 in the fix database. As I previously mentioned, if you set CHNGPGS_THRESH to 20% or below, that will do approximately the same thing (speed up page cleaning) as using DB2_USE_ALTERNATE_PAGE_CLEANING. When setting CHNGPGS_THRESH to 20% that means is that DB2 will start cleaning pages (writing them to disk) as soon as 20% of them are dirty (updated), instead of waiting until 60% of them are dirty (60% is the default). So forget about DB2_USE_ALTERNATE_PAGE_CLEANING and any bugs it may have.
From: Hemant Shah on 15 Sep 2006 10:57
While stranded on information super highway Mark A wrote: > "Hemant Shah" <shah(a)typhoon.xnet.com> wrote in message > news:eec2ud$1ml$1(a)new7.xnet.com... >> >> Yes, FP10 is installed on the system but instfix says that APAR is not >> installed: >> >> # instfix -i -k IY58576 >> There was no data for IY58576 in the fix database. > > As I previously mentioned, if you set CHNGPGS_THRESH to 20% or below, that > will do approximately the same thing (speed up page cleaning) as using > DB2_USE_ALTERNATE_PAGE_CLEANING. > > When setting CHNGPGS_THRESH to 20% that means is that DB2 will start > cleaning pages (writing them to disk) as soon as 20% of them are dirty > (updated), instead of waiting until 60% of them are dirty (60% is the > default). > > So forget about DB2_USE_ALTERNATE_PAGE_CLEANING and any bugs it may have. > I have change CHNGPGS_THRESH to 20%, but if there is a bug in DB2_USE_ALTERNATE_PAGE_CLEANING then I was thinking of opening PMR to get the problem fixed. Thanks for all you help on this. > -- 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. |