RE: ISAM error 151 on UNLOAD

If you mean by selecting individual columns for the bad rowid's, I can't get that to work either. I still get the same errors. When I run oncheck -cD I get this: TBLspace data check for eagle:root.h_ac_job WARNING: data page 0x35309 in tablespace 0x4065bd appears to be more or less full than is indicated in the bitmap. Bitmap mode: 0xc, Calculated mode: 0x4. Reset the bitmap mode for this page? y WARNING: The bitmap mode for page 0x35309 has been reset from 0xc to 0x4. WARNING: Modifying TBLspace statistics(pn_nrows): OLD 980183 NEW 980200 Reset partition data? Y But that doesn't seem to fix anything. I still have the same errors. Thanks for the help, Sean. From: Art Kagel [mailto:art.kagel@gmail.com] Sent: Friday, May 27, 2011 11:21 AM To: Sean Baker Cc: informix-list@iiug.org Subject: Re: ISAM error 151 on UNLOAD You should be able to extract the data for columns that were not damaged. You can try running the oncheck -cD again to see if it can repair the other damaged rows. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com<http://www.advancedatatools.com>) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, May 27, 2011 at 2:14 PM, Sean Baker <SBaker@moneymailer.com<mailto:SBaker@moneymailer.com>> wrote: Thanks, Jacques, that worked. The table I tried it on (I have several that are giving me the same error) lost 49 rows out of 414,761. I was able to get the rowid of the first bad from the log, which gave me the error: 09:46:06 Assert Failed: read_record: decompress_row, source = 0x0x779da818, target = 0x0x1694ff0a8 09:46:06 IBM Informix Dynamic Server Version 11.50.FC6 09:46:06 Who: Session(17063, sbaker@venus, 17850, 0x15e5b8640) Thread(26304, sqlexec, 15e57b010, 3) File: rsread.c Line: 3634 09:46:06 Results: Record not read 09:46:06 Action: Please notify IBM Informix Technical Support. 09:46:06 stack trace for pid 1919 written to /opt/IBM/informix/tmp/af.6aa8d54d 09:46:06 See Also: /opt/IBM/informix/tmp/af.6aa8d54d 09:46:08 read_record: decompress_row, source = 0x0x779da818, target = 0x0x1694ff0a8 09:46:08 read_record: invalid rowid = 0x6b4301, partnum = 0x4004f5 I can use "partnum = 0x4004f5" to verify the table I'm working with, and use "invalid rowid = 0x6b4301" to get the first bad rowid. Since the rowids are not necessarily sequential, it took a while to get past the bad records. Finally, I dropped and re-created the table, loaded the data and re-created the indexes. I did this all on our development system, I still need to take care of it in production. Is there any way to get the data for those bad records so I can try to fix the data and re-insert? Thanks, Sean. -----Original Message----- From: informix-list-bounces@iiug.org<mailto:informix-list-bounces@iiug.org> [mailto:informix-list-bounces@iiug.org<mailto:informix-list-bounces@iiug.org>] On Behalf Of jrenaut Sent: Friday, May 27, 2011 9:00 AM To: informix-list@iiug.org<mailto:informix-list@iiug.org> Subject: Re: ISAM error 151 on UNLOAD On May 27, 10:00 am, Sean Baker <SBa...@moneymailer.com<mailto:SBa...@moneymailer.com>> wrote: > I'm stuck trying to do DBA tasks because our DBA was laid off ... > > I'm getting the following error when I try to UNLOAD one of the tables in our IDS 11.50.FC6 database: > > 244: Could not do a physical-order read to fetch next row. > 151: ISAM error: Illegal value in varchar length field> > This error is also causing dbexport to fail. I ran oncheck -cd on the table and it "fixed" something, but I still can't unload the table or select some rows in the table. > > Is there a way to fix this? Is the data lost? > > This table does not have any indexes or unique keys. There are about 940,000 rows, and I can only unload about 866,000 of them. The schema is: > > create table thetable > ( > trx_serial serial not null , > trx_type char(1) not null , > trx_datetime datetime year to minute > default current year to minute, > trx_user varchar(20) > default user, > ac_job_id integer, > request_number smallint, > product_item_enm varchar(30), > art_item_enm varchar(30), > center_enm varchar(30,1), > submit_datetime datetime year to minute, > priority_enm varchar(30), > ac_status_enm varchar(30), > r_day_zero_rule_id integer, > r_due_date_rule_id integer, > completed_date date, > artist varchar(20), > instructions lvarchar(4095), > prior_request_number smallint, > prior_product_item varchar(30,1), > derived_panel_enm varchar(30), > derived_templateid integer, > derived_prod_code char(2), > derived_fold_enm varchar(30), > derived_is_window smallint, > derived_color_count smallint, > derived_size_enm varchar(30), > derived_points smallint, > derived_day_zero date, > derived_due_date date, > derived_fof_date date, > derived_is_late smallint, > is_online_order smallint, > is_canceled smallint, > requester_mm_net_login varchar(20), > orientation_enm varchar(30) > );> > Thanks, > > Sean. It does sound like you have at least 1 corrupted row that will be lost, but if the table is not fragmented you should be able to use the rowid concept to unload the rows passed the bad one. So for example if you do select rowid, * from thetable you should get output which would contain the rowid of the last good row the engine could get. Now this rowid column will be in decimal format, but what you can do is convert the number of hex. Then the 4 byte hex representation of the rowid has the following meaning: 0xLLLLLLSS where 0xLLLLLL is the logical page address of the table, and SS is the physical slot on the data page. (note this is both why a single table fragment is limited to 0xffffff pages, and why you can have a max of 255 rows even if they are really tiny rows or really big page sizes) So once you have the last good rowid, the bad rowid will either be +1 if the next row is on that same page, or it will be on the next page so the bad rowid would then be 0xLLLLLL(+1)01 (so the 1st slot on the next logical page). But basically once you find the actual rowid of the bad row, you should then be able to start increasing the rowid value of 1 until you can start unloading rows again by putting the rowid column in the where clause of the unload select statement. For example: unload to thetable.unl select * from thetable where rowid > 513; I think the rowid value you use has to be decimal. So in my example above 513 is = 0x201 Which is then the 1st slot on logical page address 2. So I'd then try to grab everything in the ta