Light append partition after onpload

Hi all. The other day I ran into trouble at a customer site running 10.00.TC6 on Windows Server 2003. The database had been migrated and reorganized using onpload with overall success. At this point, an ontape level 0 was taken to file and then the customer started running some batches on their new shiny 10.00.TC6 installation. After the first set of batches, another ontape level 0 was taken before next set of batches were to be run. However, the second set of batches stopped on -242/-197 on one essential table. I tried a few other tables, and they returned the same error on INSERT/UPDATE actions. When I've seen this error in conjunction with onpload, it is because the express-mode load has used Light Appends to create new extents for the loaded data. To make these new extents updateable a level 0 backup must be taken. This did not work in this case: I tried multiple level 0 backups combined with forcing logical log file switches and checkpoints just to make sure. No luck. Finally we gave up and restored to the first backup after the onpload. I checked the tables and had no trouble doing INSERT/UPDATE on them. We have not yet had the time to rerun the first set of batches to see if the problem reoccurs or not (as it is now I would be more happy if the same batch run fails in the same way...) Just to be clear: there were no onpload activities whatsoever after the first backup. The batches did insert a lot of data but this was done through Stored Procedures doing regular INSERT/UPDATE statements. I took a look at the information in sysmaster:sysptnhdr and noticed that many tables are still flagged as "Light Append Partition" (both before and after the first batch set run) and I have concluded that this flag is set when doing an express-mode load on a table. My questions: is there anything that should be done to tables after an express-mode load apart from taking a level 0 backup? Does anyone know if the LAP flag affects table behaviour in any way? Can it be cleared by some operation? thanks for listening.

Hi, Jonah, I don't have a complete information, so I can just give some generic advices. 1. 10.0xC6 is a very problematic version of IDS. Avoid using it. Upgrade to xC7 or downgrade to xC5 (if you don't use HDR) or xC4. With xC6, make sure, that you have ' VP_MEMORY_CACHE_KB 0' in your $ONCONFIG (or have it set to something like 20000, if you are familiar with this) 2. If you have a 'Passport Advantage' support contract, you are entitled to call IBM 24x7 'Advanced Support / System Down group'. In my opinion, this can be classified as 'system down' situation. Guys from Advanced Support can login into the system, make all the possible diagnostics, and, possibly, even perform binary fix of Informix disk structures. 3. Run oncheck's on problematic tables (-cc, -cI, -cD); dump 'oncheck -pT' 4. Verify, that you are doing Logical log backups properly 5. Check 'online.log' for any helpful information -Alexey -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of JOHAN BACKLUND Hi all. The other day I ran into trouble at a customer site running 10.00.TC6 on Windows Server 2003. The database had been migrated and reorganized using onpload with overall success. At this point, an ontape level 0 was taken to file and then the customer started running some batches on their new shiny 10.00.TC6 installation. After the first set of batches, another ontape level 0 was taken before next set of batches were to be run. However, the second set of batches stopped on -242/-197 on one essential table. I tried a few other tables, and they returned the same error on INSERT/UPDATE actions. When I've seen this error in conjunction with onpload, it is because the express-mode load has used Light Appends to create new extents for the loaded data. To make these new extents updateable a level 0 backup must be taken. This did not work in this case: I tried multiple level 0 backups combined with forcing logical log file switches and checkpoints just to make sure. No luck. Finally we gave up and restored to the first backup after the onpload. I checked the tables and had no trouble doing INSERT/UPDATE on them. We have not yet had the time to rerun the first set of batches to see if the problem reoccurs or not (as it is now I would be more happy if the same batch run fails in the same way...) Just to be clear: there were no onpload activities whatsoever after the first backup. The batches did insert a lot of data but this was done through Stored Procedures doing regular INSERT/UPDATE statements. I took a look at the information in sysmaster:sysptnhdr and noticed that many tables are still flagged as "Light Append Partition" (both before and after the first batch set run) and I have concluded that this flag is set when doing an express-mode load on a table. My questions: is there anything that should be done to tables after an express-mode load apart from taking a level 0 backup? Does anyone know if the LAP flag affects table behaviour in any way? Can it be cleared by some operation? thanks for listening. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!

Inserts are allowed to a table which has been appended to if a successful archive has been taken after you done a light appended. Error -197 is only used once and does indicate that the server believes a light append has occurred after the last level 0 archive on this tables. If you are using fragmentation it can be that a single fragment's dbspace was not backup and this would generate the error for the entire table. Once the server turns on the append flag it is on for the life of this table, it will never be cleared. Now the trick is how does the server tell if this archive has occurred?? It does this by looking at the partition's created time and comparing that two the last level 0 archive time. The partition's created time is updated in two cases. 1. The time the table and/or index was created 2. The last time a light append was done to the table/fragment The way to get all the creation time of all fragments for the table in questions is to use the oncheck -pt command. Do NOT use the time from systables, you must get the creation time from the partition page See example below: oncheck -pt stores_demo:customer | grep Creation Creation date 01/04/2008 01:01:50 Creation date 01/04/2008 01:01:50 Creation date 01/09/2008 17:27:59 Creation date 01/04/2008 01:01:50 Now you just need to get the time of the last level 0 for the dbspace(s) which the table occupies. The best way to do this is to run the command oncheck -pr. Look at the dbspace page for the "Real Time" the archivebegan. This is the time the server uses to compare with the partitions created time. DO NOT look at any other archive time use the time from oncheck -pr because this is the time the server uses. oncheck -pr ( The DBSPACE PAGE ) DBspace archive status Archive Level 0 Real Time Archive Began 01/19/2008 15:39:12 Time Stamp Archive Began 6244474 Logical Log Unique Id 149 Logical Log Position 0x4a4018 After getting these two pieces of information you can compare the times just like the server would. If the time of the archive is before than the time of the partition creation time and the partitions append flag is on, you will get error 197. So the real question might be why is your archive time incorrect??? Did someone mess with the computers current time setting. Is the archive time not being updated after a successful level 0 backup. NOTE: level 1 or level 2 backup will not suffice, it must be a level 0. Hope this explains what is happening and gives you the information you need to track this down. John ids-bounces@iiug.org wrote on 01/19/2008 04:31:50 AM: > Hi all. The other day I ran into trouble at a customer site running 10.00.TC6 > on Windows Server 2003. The database had been migrated and reorganized using > onpload with overall success. At this point, an ontape level 0 was taken to > file and then the customer started running some batches on their new shiny > 10.00.TC6 installation. > > After the first set of batches, another ontape level 0 was taken before next > set of batches were to be run. However, the second set of batches stopped on > -242/-197 on one essential table. I tried a few other tables, and they > returned the same error on INSERT/UPDATE actions. > > When I've seen this error in conjunction with onpload, it is because the > express-mode load has used Light Appends to create new extents for the loaded > data. To make these new extents updateable a level 0 backup must be taken. > This did not work in this case: I tried multiple level 0 backups > combined with > forcing logical log file switches and checkpoints just to make sure.No luck. > > Finally we gave up and restored to the first backup after the onpload. I > checked the tables and had no trouble doing INSERT/UPDATE on them. > We have not > yet had the time to rerun the first set of batches to see if the problem > reoccurs or not (as it is now I would be more happy if the same batch run > fails in the same way...) > > Just to be clear: there were no onpload activities whatsoever after the first > backup. The batches did insert a lot of data but this was done through Stored > Procedures doing regular INSERT/UPDATE statements. > > I took a look at the information in sysmaster:sysptnhdr and noticed that many > tables are still flagged as "Light Append Partition" (both before and after > the first batch set run) and I have concluded that this flag is set > when doing > an express-mode load on a table. > > My questions: is there anything that should be done to tables after an > express-mode load apart from taking a level 0 backup? Does anyone know if the > LAP flag affects table behaviour in any way? Can it be cleared by some > operation? > > thanks for listening. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!!

Thanks for the quick response! > 1. 10.0xC6 is a very problematic version of IDS. Avoid using it. > Upgrade to xC7 or downgrade to xC5 (if you don't use HDR) or xC4. > With xC6, make sure, that you have ' VP_MEMORY_CACHE_KB 0' in your > $ONCONFIG (or have it set to something like 20000, if you are familiar > with this) This version is used because of requirements from an applicationt this customer is using. An upgrade to TC7 has been estimated to involve 4-5 months of testing. Since they don't want to delay the migration from 7.31 we just have to play with what we've got. However, I would be more than interested in some of the worst problems with xC6 because then I could try and navigate the customer past these problems. I've seen comments that the VP_MEMORY_CACHE_KB bug wasn't present in the Windows version, but one test did started doing excessive allocation of segments until it hit SHMTOTAL - but with some 40-50% free space in each segment. Next week I will run same tests with VP_MEMORY_CACHE_KB=0 and see if it makes a difference. > 2. If you have a 'Passport Advantage' support contract, you are entitled > to call IBM 24x7 'Advanced Support / System Down group'. In my opinion, > this can be classified as 'system down' situation. Guys from Advanced > Support can login into the system, make all the possible diagnostics, > and, possibly, even perform binary fix of Informix disk structures. Unfortunately, the Informix support goes through the application supplier, and although they can escalate to IBM, there is a long turnaround time before a competent answer is returned. And I am contractor from a small company, so that's why I'm knocking on this forums door. Anyways, the customer is still in the testing phase, so none of the problems I've mentioned have affected production systems. However, planned migration date is only like 4 weeks away... > 3. Run oncheck's on problematic tables (-cc, -cI, -cD); dump 'oncheck -pT' Ran all the onchecks I could think of without any problems. I'm not sure that the oncheck -pT output is still available, but if it is I will add it later. However, I looked through quite carefully when it happended but could not see anything strange - apart maybe from the fact that the table flags did NOT mention anything about Light Append Partition. This flag was only present in the sysmaster:sysptnhdr table. > 4. Verify, that you are doing Logical log backups properly hmm, this could be interesting. So far, all the testing has occurred with logical log backups disabled (LTAPEDEV set to NUL). Can this really have caused the problem? > 5. Check 'online.log' for any helpful information online.log reported nothing bad. thanks again, Johan

That was some really interesting information, John! Yes, when they are doing their tests they are messing with the dates to be able to reproduce results from the real production runs and they occurred before the whole migration process. I can't be sure that they changed the date before the backup, but it is definitely worth checking first thing monday. Thanks!

Hi, If your customer insists in using the 10.0xC6 version, you should minimum upgrade to the latest W pack of 10.0 (I think it is W5). We have found most of the problems we had were solved in this release. I am not sure about the VP_MEMORY_CACHE_KB bug (we did not run into that one ...). This version is a bugfix release, which is recommended, because you can get into serious problems (stability !) with the unpatched 10.00xC6 release. Read the known defects/bugfix history for details. I would say you are much more save using minimum the C6W5 version. We did not encounter any functional differences between 10.00xC6 and 10.00xC6W5. But of course, the decision is up to you. Marcus -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Sent: Sunday, January 20, 2008 10:24 AM To: ids@iiug.org Subject: Re: RE: Light append partition after onpload [11016] Thanks for the quick response! > 1. 10.0xC6 is a very problematic version of IDS. Avoid using it. > Upgrade to xC7 or downgrade to xC5 (if you don't use HDR) or xC4. > With xC6, make sure, that you have ' VP_MEMORY_CACHE_KB 0' in your > $ONCONFIG (or have it set to something like 20000, if you are familiar > with this) This version is used because of requirements from an applicationt this customer is using. An upgrade to TC7 has been estimated to involve 4-5 months of testing. Since they don't want to delay the migration from 7.31 we just have to play with what we've got. However, I would be more than interested in some of the worst problems with xC6 because then I could try and navigate the customer past these problems. I've seen comments that the VP_MEMORY_CACHE_KB bug wasn't present in the Windows version, but one test did started doing excessive allocation of segments until it hit SHMTOTAL - but with some 40-50% free space in each segment. Next week I will run same tests with VP_MEMORY_CACHE_KB=0 and see if it makes a difference. > 2. If you have a 'Passport Advantage' support contract, you are > entitled to call IBM 24x7 'Advanced Support / System Down group'. In > my opinion, this can be classified as 'system down' situation. Guys > from Advanced Support can login into the system, make all the possible > diagnostics, and, possibly, even perform binary fix of Informix disk structures. Unfortunately, the Informix support goes through the application supplier, and although they can escalate to IBM, there is a long turnaround time before a competent answer is returned. And I am contractor from a small company, so that's why I'm knocking on this forums door. Anyways, the customer is still in the testing phase, so none of the problems I've mentioned have affected production systems. However, planned migration date is only like 4 weeks away... > 3. Run oncheck's on problematic tables (-cc, -cI, -cD); dump 'oncheck -pT' Ran all the onchecks I could think of without any problems. I'm not sure that the oncheck -pT output is still available, but if it is I will add it later. However, I looked through quite carefully when it happended but could not see anything strange - apart maybe from the fact that the table flags did NOT mention anything about Light Append Partition. This flag was only present in the sysmaster:sysptnhdr table. > 4. Verify, that you are doing Logical log backups properly hmm, this could be interesting. So far, all the testing has occurred with logical log backups disabled (LTAPEDEV set to NUL). Can this really have caused the problem? > 5. Check 'online.log' for any helpful information online.log reported nothing bad. thanks again, Johan ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!

Just to check back in: the import and the first backup was taken in current time, while the first test run and the second backup was taken with the date turned back a couple of weeks. By making sure that the last backup is taken in current time, the problem is avoided. Thanks for your help! Finding the cause of this error was important to the upcoming production migration.

I will certainly take this information back to the customer and their application vendor who are the ones that make the final decision. Thanks a lot for your input.

"10.0xC6 is a very problematic version of IDS. Avoid using it." Yikes! How problematic????? We're about to go into a major upgrade with it... --- Alexey Sonkin <alexeys@cidc.com> wrote: > Hi, Jonah, > > I don't have a complete information, so I can just > give some generic > advices. > > 1. 10.0xC6 is a very problematic version of IDS. > Avoid using it. > Upgrade to xC7 or downgrade to xC5 (if you don't use > HDR) or xC4. > With xC6, make sure, that you have ' > VP_MEMORY_CACHE_KB 0' in your> $ONCONFIG (or have it set to something like 20000, > if you are familiar > with this) > > 2. If you have a 'Passport Advantage' support > contract, you are entitled > to call IBM 24x7 'Advanced Support / System Down > group'. In my opinion, > this can be classified as 'system down' situation. > Guys from Advanced > Support can login into the system, make all the > possible diagnostics, > and, possibly, even perform binary fix of Informix > disk structures. > > 3. Run oncheck's on problematic tables (-cc, -cI, > -cD); dump 'oncheck > -pT' > > 4. Verify, that you are doing Logical log backups > properly > > 5. Check 'online.log' for any helpful information > > -Alexey > > -----Original Message----- > From: ids-bounces@iiug.org > [mailto:ids-bounces@iiug.org] On Behalf Of > JOHAN BACKLUND > > Hi all. The other day I ran into trouble at a > customer site running > 10.00.TC6 > on Windows Server 2003. The database had been > migrated and reorganized > using > onpload with overall success. At this point, an > ontape level 0 was taken > to > file and then the customer started running some > batches on their new > shiny > 10.00.TC6 installation. > > After the first set of batches, another ontape level > 0 was taken before > next > set of batches were to be run. However, the second > set of batches > stopped on > -242/-197 on one essential table. I tried a few > other tables, and they > returned the same error on INSERT/UPDATE actions. > > When I've seen this error in conjunction with > onpload, it is because the > > express-mode load has used Light Appends to create > new extents for the > loaded > data. To make these new extents updateable a level 0 > backup must be > taken. > This did not work in this case: I tried multiple > level 0 backups > combined with > forcing logical log file switches and checkpoints > just to make sure. No > luck. > > Finally we gave up and restored to the first backup > after the onpload. I > > checked the tables and had no trouble doing > INSERT/UPDATE on them. We > have not > yet had the time to rerun the first set of batches > to see if the problem > > reoccurs or not (as it is now I would be more happy > if the same batch > run > fails in the same way...) > > Just to be clear: there were no onpload activities > whatsoever after the > first > backup. The batches did insert a lot of data but > this was done through > Stored > Procedures doing regular INSERT/UPDATE statements. > > I took a look at the information in > sysmaster:sysptnhdr and noticed that > many > tables are still flagged as "Light Append Partition" > (both before and > after > the first batch set run) and I have concluded that > this flag is set when > doing > an express-mode load on a table. > > My questions: is there anything that should be done > to tables after an > express-mode load apart from taking a level 0 > backup? Does anyone know > if the > LAP flag affects table behaviour in any way? Can it > be cleared by some > operation? > > thanks for listening. > > ************************************************************************ > > ******* > Forum Note: Use "Reply" to post a response in the > discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas > City), Kansas > http://www.iiug.org/conf > Registration Now Open!! > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the > discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas > City), Kansas > http://www.iiug.org/conf > Registration Now Open!! > ________________________________________________________________________________ ____ Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs

Jerry Hamilton wrote: > "10.0xC6 is a very problematic version of IDS. Avoid > using it." > > Yikes! How problematic????? We're about to go into a > major upgrade with it... > > --- Alexey Sonkin <alexeys@cidc.com> wrote: > Suffice it to say you REALLY want to be on .xC7 or later. Art S. Kagel Oninit ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ ===========