Bizarre Errors using dbaccess to load data

These errrors I cannot understand ..... Just upgrading from 7.2 to 7.3 For safety did dbexport from 7.2 Used dbimport to get unl files into 7.3. Most load without a problem .... It looks like the big ones have a problem (not always but mostly) Out of 47 tables 5 would not load via dbimport giving Number of values not matching columns. So I loaded removed these 5 from the dbimport sql file and loaded all OK. After I used dbaccess to load them using as an example the following load from 'conta00159.unl' delimiter '|' insert into contacts Would load approx 6-7000 records (130,000 total) and bomb out with either character to numeric conversion OR number of values in load file not equal error. SO - I checked the file - everything was Okay so I Deleted the lines that had already been loaded and restarted the process with the same file but the so called error line now as line 1. Load process would then continue without a problem for another 6-7000 records, and sure enough would fall over again......I repeated the process of deleting the loaded lines and restarted .... Is there a size setting or something that I'm hitting that would cause these weird errors because there is absolutely nothing wrong with the file ... Your comments and advice would be welcome, as you can imagine this load is taking all Easter Monday to complete !! And no the data does not have any | signs in there.... Either reply to group or direct to mikeb@terratech.co.uk... Thanks in advance :-)

Check if your data itself has the delimiter character '|'. That would throw off the columns while loading. If you do, then you may have to load the data into temporary staging tables and then load them to the production tables using concatenation and inserts. To circumvent this issue, set the DBDELIMITER to something other than '|'; I've always used '^' and it's safer. Good luck, Arun MB wrote: > These errrors I cannot understand ..... > > Just upgrading from 7.2 to 7.3 > > For safety did dbexport from 7.2 > > Used dbimport to get unl files into 7.3. > > Most load without a problem .... It looks like the big ones have a problem > (not always but mostly) > > Out of 47 tables 5 would not load via dbimport giving Number of values not > matching columns. > > So I loaded removed these 5 from the dbimport sql file and loaded all OK. > > After I used dbaccess to load them using as an example the following > > load from 'conta00159.unl' delimiter '|' insert into contacts> > Would load approx 6-7000 records (130,000 total) and bomb out with either > character to numeric conversion OR > number of values in load file not equal error. > > SO - I checked the file - everything was Okay so I > Deleted the lines that had already been loaded and restarted the process > with the same file > but the so called error line now as line 1. > > Load process would then continue without a problem for another 6-7000 > records, > and sure enough would fall over again......I repeated the process of > deleting the loaded lines > and restarted .... > > Is there a size setting or something that I'm hitting that would cause these > weird errors because > there is absolutely nothing wrong with the file ... > > Your comments and advice would be welcome, as you can imagine this load is > taking all Easter Monday > to complete !! > > And no the data does not have any | signs in there.... > > Either reply to group or direct to mikeb@terratech.co.uk... > > Thanks in advance :-)

You misunderstand me - The Delimiter symbol '|' is not in the data itself, the data came from a 7.2 database using dbexport !! Once I have deleted the first 6-7000 lines - which loaded OK, I restart without any problems and the data loads OKay. Arun Shastry wrote in message <7eammu$4tb$1@autumn.news.rcn.net>... >Check if your data itself has the delimiter character '|'. That would throw off > >the columns while loading. If you do, then you may have to load the data into >temporary staging tables and then load them to the production tables using >concatenation and inserts. > >To circumvent this issue, set the DBDELIMITER to something other than '|'; I've >always used '^' and it's safer. > >Good luck, >Arun >

Look for a backslash sign just before a pipe symbol or look for missmatched qutoes. Are you loading with "load" or with "dbload"? DBLOAD may be better, as it doesn't die on error rows. MB (mike.bradbeer@btinternet.com) wrote: : You misunderstand me - The Delimiter symbol '|' is not in the data itself, : the data came from a 7.2 database using dbexport !! : Once I have deleted the first 6-7000 lines - which loaded OK, I restart : without : any problems and the data loads OKay. : Arun Shastry wrote in message <7eammu$4tb$1@autumn.news.rcn.net>... : >Check if your data itself has the delimiter character '|'. That would : throw off : > : >the columns while loading. If you do, then you may have to load the data : into : >temporary staging tables and then load them to the production tables using : >concatenation and inserts. : > : >To circumvent this issue, set the DBDELIMITER to something other than '|'; : I've : >always used '^' and it's safer. : > : >Good luck, : >Arun : > -- --------------------------------------------------------------------------- Joe Lumbley(jlumbley@netcom.com) author of: "INFORMIX DBA Survival Guide" The DBA Survival Guide, Second Edition will cover NT, 7.X through 7.3, graphical utilities, more survival hints. Publish date: 12/16/98 FINALLY! ---------------------------------------------------------------------------

Yep Thought of that - Using load - I'll use dbload in the morning and see how I get on can't remember the syntax so I need the manual's. Another new error - During a load of a table unload file (450,000 records) sometimes the serial ID that is in the file gets used by the previously loaded record eg. ... 12000|Mr|Joe|Bloggs|UK|0|0| 12001|Mr|Bill|Baggins|UK|0|0| ... In this example the load command errors on the second line complaining about UNIQUE Record on Serial ID I check the table and 12001 is used by the record containing 12000 data. So the table gives me 12001|Mr|Joe|Bloggs|UK|0|0|. I move the 12001 line to the end of the unl file and change the ID and continue - everything continues as normal for a while , then it will do it again ! Bizarre or what.... Joe Lumbley wrote in message ... >Look for a backslash sign just before a pipe symbol or look for missmatched qutoes. > >Are you loading with "load" or with "dbload"? DBLOAD may be better, as >it doesn't die on error rows. > > >