unloads of blobs

Hi, Has anyone seen this, or know a workaround? I'm working in Informix 5.10.uc1 on HP10.20. We are having a problem when we unload byte fields (BLOBS) that are imbedded within a table. Some of the blobs unload as NULLS, which is impossible since they are not null fields. If I unload the table, then reload it, the blobs will be lost on 20% of the records. When loading the table I get the errors: 391: Cannot insert a null into column (test_blob.dtf_val). 847: Error in load file line 7. I just used a normal unload: unload to /tmp/data select * from table; At first I thought the table was corrupted, but tbcheck -cDI shows no problems. I also discovered this happening in many other occurrences of this table on other machines. What is REALLY strange, is if I create a second table that has an identical record structure to the first and copy the records from one to the other, the blobs are fine. Using the following statement: insert into test_blob select * from table Here is the table definition: create table "informix".test_blob ( crt_ts datetime year to fraction(3) default current year to fraction(3) not null, pos_isp_trnsm_cd smallint default 0 not null, dtf_val byte not null, upd_atmp_cnt smallint default 0 not null ); revoke all on "informix".test_blob from "public"; create unique index "informix".x0testblob on "informix".test_blob (crt_ts);

This problem vexed us as well on Informix 5 up through Informix 7.20. It appears to be a problem with either the unload, the load, or both for byte BLOBs. Text BLOBs seemed to be OK. We found no workaround until we upgraded to V7.30, then all of a sudden byte BLOBs unloaded and loaded fine. Just a real nasty bug I guess. Jon On Thu, 4 Feb 1999 09:30:49 -0500, Kate_Tomchik@HomeDepot.COM wrote: > > > > >Hi, >Has anyone seen this, or know a workaround? > >I'm working in Informix 5.10.uc1 on HP10.20. We are having a problem when >we unload byte fields (BLOBS) that are imbedded within a table. Some of >the blobs unload as NULLS, which is impossible since they are not null >fields. If I unload the table, then reload it, the blobs will be lost on >20% of the records. When loading the table I get the errors: > 391: Cannot insert a null into column (test_blob.dtf_val). > 847: Error in load file line 7.> >I just used a normal unload: >unload to /tmp/data select * from table;> >At first I thought the table was corrupted, but tbcheck -cDI shows no >problems. I also discovered this happening in many other occurrences of >this table on other machines. What is REALLY strange, is if I create a >second table that has an identical record structure to the first and copy >the records from one to the other, the blobs are fine. Using the following >statement: > >insert into test_blob select * from table> >Here is the table definition: > >create table "informix".test_blob > ( > crt_ts datetime year to fraction(3) > default current year to fraction(3) not null, > pos_isp_trnsm_cd smallint > default 0 not null, > dtf_val byte not null, > upd_atmp_cnt smallint > default 0 not null > ); >revoke all on "informix".test_blob from "public";> >create unique index "informix".x0testblob on "informix".test_blob (crt_ts); > >

In article <79cbpo$kt5$1@news.xmission.com>, Kate_Tomchik@HomeDepot.COM () wrote: > > > > > Hi, > Has anyone seen this, or know a workaround? > > I'm working in Informix 5.10.uc1 on HP10.20. We are having a problem > when > we unload byte fields (BLOBS) that are imbedded within a table. Some of > the blobs unload as NULLS, which is impossible since they are not null > fields. If I unload the table, then reload it, the blobs will be lost > on > 20% of the records. When loading the table I get the errors: > 391: Cannot insert a null into column (test_blob.dtf_val). > 847: Error in load file line 7.> > I just used a normal unload: > unload to /tmp/data select * from table;> > At first I thought the table was corrupted, but tbcheck -cDI shows no > problems. I also discovered this happening in many other occurrences of > this table on other machines. What is REALLY strange, is if I create a > second table that has an identical record structure to the first and > copy > the records from one to the other, the blobs are fine. Using the > following > statement: > > insert into test_blob select * from table> > Here is the table definition: > > create table "informix".test_blob > ( > crt_ts datetime year to fraction(3) > default current year to fraction(3) not null, > pos_isp_trnsm_cd smallint > default 0 not null, > dtf_val byte not null, > upd_atmp_cnt smallint > default 0 not null > ); > revoke all on "informix".test_blob from "public";> > create unique index "informix".x0testblob on "informix".test_blob > (crt_ts); > > > The problem Blobs are they all over a certain size? There was a Informix Turbo and Plexus problem that prevented large Blobs from reloading via import/exports. There was a hard limit in the code, but the error messages I got bore no resemblance to the this. If the row was over a certain size the malloc shmget failed leaving a null to be loaded. Paul Watson # I don't suffer from WF Software Ltd. # stress, I'm just Tel. (+44) 1436 674729 # a carrier Fax. (+44) 1436 678693 # www.wfsoftware.co.uk