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)
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> 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] On Behalf Of jrenaut
> Sent: Friday, May 27, 2011 9:00 AM
> To: informix-list@iiug.org
> Subject: Re: ISAM error 151 on UNLOAD
>
> On May 27, 10:00 am, Sean Baker <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 table passed the
> 1st slot in logical page two. Using this rowid concept works because
> of how sequential scans work, which is by starting at logical page 0
> in the table and going all the way out to number of pages used in the
> table. So you can use the rowid concept to tell the scan to skip over
> pages in the table. Which you need to do to get over the corrupted
> row (or rows) to get as much of the data in your table as possible.
>
> If you can do this yourself, and you have support you could also
> contact support and they should be able to help you figure this out.
>
> Jacques Renaut
> IBM Informix Advanced Support
> APD Team
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiu