Just scratching my head...

We are currently working with a contractor and a consortium of several states on a large project. Each state is free to choose its own database. (We are using Informix.) The contractor has past experience with Oracle. One of the contractor's on-site staff was quite surprised today, after encountering defects (-201 error) with dbimport. We were attempting to import into R10 a database exported from R11.5. (The problem was the dbimport being unable to use the *.sql file produced by dbexport). I explained to him how we deal with the problem (usually just pulling out the table DDL to use with dbimport, while using dbaccess to run the remainder. I told him that the situation is pretty common in our experience, and that IBM has know about it at least since about 5 years ago when we had (an unresolved) tech support case on it. The contractor staff person was mystified that such a basic utility could be defective. I admit that I, too, am quite annoyed with it, every time I encounter the problem. I heard him later on the phone talking to his firm. I don't think Informix won any gold stars today. DG

davidegrove@gmail.com wrote: > We are currently working with a contractor and a consortium of several > states on a large project. Each state is free to choose its own > database. (We are using Informix.) > > The contractor has past experience with Oracle. > > One of the contractor's on-site staff was quite surprised today, after > encountering defects (-201 error) with dbimport. We were attempting > to import into R10 a database exported from R11.5. (The problem was > the dbimport being unable to use the *.sql file produced by > dbexport). I explained to him how we deal with the problem (usually > just pulling out the table DDL to use with dbimport, while using > dbaccess to run the remainder. I told him that the situation is > pretty common in our experience, and that IBM has know about it at > least since about 5 years ago when we had (an unresolved) tech support > case on it. The contractor staff person was mystified that such a > basic utility could be defective. I admit that I, too, am quite > annoyed with it, every time I encounter the problem. > > I heard him later on the phone talking to his firm. I don't think > Informix won any gold stars today. > > DG I could be here all night talking about solved issues... But I believe that is not the point, and that I may not deserve the audience trust taking into account my employer. I keep hearing about problems in dbexport, and I've seen reported bugs, but I rarely had one problem with it... I remember once, but I did what many of us forget: the file.sql is not a simple SQL file. Put a space on hit and you'll see a lot of strange errors. In your particular case, have you checked the cause of 201? 201 is syntax error, and you're importing a v11.50 export into a v10 export. As we all know, v11.50 supports a lot of stuff that v10 will not... did you check? Another common problem is the use of reserved words. It's very interesting to see that many times you'll be able to use reserved words. The engine will understand many of them given the context. But this sometimes isn't consistent across all versions (as we've seen yesterday in another post). Final point. Sometimes I have unsolved cases. But this happen when I don't have enough info and they can't be reproduced. Had one just a few days ago. A process that runs daily caused an issue. The sames steps could be done successfully... and were run for months without problems.... Nothing much support could do except to recommend further information collecting for next time... But usually everything is solved. I wouldn't accept it otherwise, and I already had my share of issues... Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

davidegrove@gmail.com wrote: > We are currently working with a contractor and a consortium of several > states on a large project. Each state is free to choose its own > database. (We are using Informix.) > > The contractor has past experience with Oracle. > > One of the contractor's on-site staff was quite surprised today, after > encountering defects (-201 error) with dbimport. We were attempting > to import into R10 a database exported from R11.5. (The problem was > the dbimport being unable to use the *.sql file produced by > dbexport). I explained to him how we deal with the problem (usually > just pulling out the table DDL to use with dbimport, while using > dbaccess to run the remainder. I told him that the situation is > pretty common in our experience, and that IBM has know about it at > least since about 5 years ago when we had (an unresolved) tech support > case on it. The contractor staff person was mystified that such a > basic utility could be defective. I admit that I, too, am quite > annoyed with it, every time I encounter the problem. > > I heard him later on the phone talking to his firm. I don't think > Informix won any gold stars today. > > DG What version?

Uhm... you said: " We were attempting to import into R10 a database exported from R11.5." It is one thing to be backwards compatible. Meaning R11.5 reading a R10 export, but its completely different to expect that an R10 can read R11.5 without any problems. If this confuses your contractor, ask him about going from Oracle 10g back to 9i and see if there are any problems. ;-) -G > From: davidegrove@gmail.com > Subject: Just scratching my head... > Date: Thu, 30 Oct 2008 16:56:27 -0700 > To: informix-list@iiug.org > > We are currently working with a contractor and a consortium of several > states on a large project. Each state is free to choose its own > database. (We are using Informix.) > > The contractor has past experience with Oracle. > > One of the contractor's on-site staff was quite surprised today, after > encountering defects (-201 error) with dbimport. We were attempting > to import into R10 a database exported from R11.5. (The problem was > the dbimport being unable to use the *.sql file produced by > dbexport). I explained to him how we deal with the problem (usually > just pulling out the table DDL to use with dbimport, while using > dbaccess to run the remainder. I told him that the situation is > pretty common in our experience, and that IBM has know about it at > least since about 5 years ago when we had (an unresolved) tech support > case on it. The contractor staff person was mystified that such a > basic utility could be defective. I admit that I, too, am quite > annoyed with it, every time I encounter the problem. > > I heard him later on the phone talking to his firm. I don't think > Informix won any gold stars today. > > DG > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ You live life beyond your PC. So now Windows goes beyond your PC. http://clk.atdmt.com/MRT/go/115298556/direct/01/

Thank you for your comments. I have a lot of experience with dbimport/dbexport failing. IBM has acknowledged a problem in the past. They just have never fixed it. The 201 error, of course, displays as a syntax error. But it is not. (And even if it were that would be problematic because I det it even when going from same version to same version.) My usual fix, which worked today, is just to break the *.sql file into two pieces and run each separately. Worked just fine. DG "Fernando Nunes" <domusonline@gmail.com> wrote in message news:gedjpi$hum$1@registered.motzarella.org... > davidegrove@gmail.com wrote: >> We are currently working with a contractor and a consortium of several >> states on a large project. Each state is free to choose its own >> database. (We are using Informix.) >> >> The contractor has past experience with Oracle. >> >> One of the contractor's on-site staff was quite surprised today, after >> encountering defects (-201 error) with dbimport. We were attempting >> to import into R10 a database exported from R11.5. (The problem was >> the dbimport being unable to use the *.sql file produced by >> dbexport). I explained to him how we deal with the problem (usually >> just pulling out the table DDL to use with dbimport, while using >> dbaccess to run the remainder. I told him that the situation is >> pretty common in our experience, and that IBM has know about it at >> least since about 5 years ago when we had (an unresolved) tech support >> case on it. The contractor staff person was mystified that such a >> basic utility could be defective. I admit that I, too, am quite >> annoyed with it, every time I encounter the problem. >> >> I heard him later on the phone talking to his firm. I don't think >> Informix won any gold stars today. >> >> DG > > I could be here all night talking about solved issues... But I believe > that is not the point, and that I may not deserve the audience trust > taking into account my employer. > > I keep hearing about problems in dbexport, and I've seen reported bugs, > but I rarely had one problem with it... I remember once, but I did what > many of us forget: the file.sql is not a simple SQL file. Put a space on > hit and you'll see a lot of strange errors. > > In your particular case, have you checked the cause of 201? 201 is syntax > error, and you're importing a v11.50 export into a v10 export. As we all > know, v11.50 supports a lot of stuff that v10 will not... did you check? > Another common problem is the use of reserved words. It's very interesting > to see that many times you'll be able to use reserved words. The engine > will understand many of them given the context. But this sometimes isn't > consistent across all versions (as we've seen yesterday in another post). > > Final point. Sometimes I have unsolved cases. But this happen when I don't > have enough info and they can't be reproduced. Had one just a few days > ago. A process that runs daily caused an issue. The sames steps could be > done successfully... and were run for months without problems.... Nothing > much support could do except to recommend further information collecting > for next time... > > But usually everything is solved. I wouldn't accept it otherwise, and I > already had my share of issues... > > Regards. > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently...

Exporting from 11.5, importing into 10.0. Using very "plain-vanilla" features only. Fixed by usual method of breaking *.sql into two pieces and running each separately. Thank you for your interest. DG "John Carlson" <jwcarlson1@yahoo.com> wrote in message news:AqsOk.54378$kh2.22016@bignews3.bellsouth.net... > davidegrove@gmail.com wrote: >> We are currently working with a contractor and a consortium of several >> states on a large project. Each state is free to choose its own >> database. (We are using Informix.) >> >> The contractor has past experience with Oracle. >> >> One of the contractor's on-site staff was quite surprised today, after >> encountering defects (-201 error) with dbimport. We were attempting >> to import into R10 a database exported from R11.5. (The problem was >> the dbimport being unable to use the *.sql file produced by >> dbexport). I explained to him how we deal with the problem (usually >> just pulling out the table DDL to use with dbimport, while using >> dbaccess to run the remainder. I told him that the situation is >> pretty common in our experience, and that IBM has know about it at >> least since about 5 years ago when we had (an unresolved) tech support >> case on it. The contractor staff person was mystified that such a >> basic utility could be defective. I admit that I, too, am quite >> annoyed with it, every time I encounter the problem. >> >> I heard him later on the phone talking to his firm. I don't think >> Informix won any gold stars today. >> >> DG > > What version?

Using only very basic Informix. Worked like a charm when I split the *.sql file. I do the tables first, then everything else. DG "Ian Michael Gumby" <im_gumby@hotmail.com> wrote in message news:mailman.237.1225415605.874.informix-list@iiug.org... Uhm... you said: " We were attempting to import into R10 a database exported from R11.5." It is one thing to be backwards compatible. Meaning R11.5 reading a R10 export, but its completely different to expect that an R10 can read R11.5 without any problems. If this confuses your contractor, ask him about going from Oracle 10g back to 9i and see if there are any problems. ;-) -G > From: davidegrove@gmail.com > Subject: Just scratching my head... > Date: Thu, 30 Oct 2008 16:56:27 -0700 > To: informix-list@iiug.org > > We are currently working with a contractor and a consortium of several > states on a large project. Each state is free to choose its own > database. (We are using Informix.) > > The contractor has past experience with Oracle. > > One of the contractor's on-site staff was quite surprised today, after > encountering defects (-201 error) with dbimport. We were attempting > to import into R10 a database exported from R11.5. (The problem was > the dbimport being unable to use the *.sql file produced by > dbexport). I explained to him how we deal with the problem (usually > just pulling out the table DDL to use with dbimport, while using > dbaccess to run the remainder. I told him that the situation is > pretty common in our experience, and that IBM has know about it at > least since about 5 years ago when we had (an unresolved) tech support > case on it. The contractor staff person was mystified that such a > basic utility could be defective. I admit that I, too, am quite > annoyed with it, every time I encounter the problem. > > I heard him later on the phone talking to his firm. I don't think > Informix won any gold stars today. > > DG > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list ------------------------------------------------------------------------------ You live life beyond your PC. So now Windows goes beyond your PC. See how

On 31 Oct, 01:46, "DGPretzel" <d...@gci.net> wrote: > Using only very basic Informix. > > Worked like a charm when I split the *.sql file.  I do the tables first, then everything else. > > DG > So, you have a "reproducible" test case, and IBM Informix Support did not get a defect / APAR logged and fixed? i.e. you have the dbexport directory with the created sql, which is you split it works? You could just put the bit of the dbname.sql where you get the -201 error here, and "someone" could log a defect based on that :o) But more appropriately, just give the exact versions where you are coming from / going to and the dbname.sql generated and log a PMR and say "This is a defect". Cold potato (No E's)

What is the APAR number of this unresolved issue that you have previously reported? Or if its been a long time, perhaps the original Informix defect number.

DG, Thats correct. I think that the point I and others are trying to make is that you and your "consultant" are coming to a wrong conclusion. While the basic DDL hasn't changed, there are changes/improvements/evolutions made in 11.5 that are not in 10. So when you're going from 11.5 to 10, the odds are that there will be problems unless you just focus on the very basic table definition pieces. So to say "Informix gets a black eye" because of this... Its a hollow argument. Does this make sense? From: deg@gci.net Subject: Re: Just scratching my head... Date: Thu, 30 Oct 2008 17:46:56 -0800 To: informix-list@iiug.org Using only very basic Informix. Worked like a charm when I split the *.sql file. I do the tables first, then everything else. DG "Ian Michael Gumby" <im_gumby@hotmail.com> wrote in message news:mailman.237.1225415605.874.informix-list@iiug.org...Uhm... you said: " We were attempting to import into R10 a database exported from R11.5." It is one thing to be backwards compatible. Meaning R11.5 reading a R10 export, but its completely different to expect that an R10 can read R11.5 without any problems. If this confuses your contractor, ask him about going from Oracle 10g back to 9i and see if there are any problems. ;-) -G > From: davidegrove@gmail.com > Subject: Just scratching my head... > Date: Thu, 30 Oct 2008 16:56:27 -0700 > To: informix-list@iiug.org > > We are currently working with a contractor and a consortium of several > states on a large project. Each state is free to choose its own > database. (We are using Informix.) > > The contractor has past experience with Oracle. > > One of the contractor's on-site staff was quite surprised today, after > encountering defects (-201 error) with dbimport. We were attempting > to import into R10 a database exported from R11.5. (The problem was > the dbimport being unable to use the *.sql file produced by > dbexport). I explained to him how we deal with the problem (usually > just pulling out the table DDL to use with dbimport, while using > dbaccess to run the remainder. I told him that the situation is > pretty common in our experience, and that IBM has know about it at > least since about 5 years ago when we had (an unresolved) tech support > case on it. The contractor staff person was mystified that such a > basic utility could be defective. I admit that I, too, am quite > annoyed with it, every time I encounter the problem. > > I heard him later on the phone talking to his firm. I don't think > Informix won any gold stars today. > > DG > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list You live life beyond your PC. So now Windows goes beyond your PC. See how _________________________________________________________________ When your life is on the go—take your life with you. http://clk.atdmt.com/MRT/go/115298558/direct/01/

> > So, you have a "reproducible" test case, and IBM Informix Support did > not get a defect / APAR logged and fixed? I did at one time (several years ago). IBM acknowledged it. The tech support guy sent me some (internal?) stuff that said something about a bug that was not fully understood, but was thought maybe to be associated with stored procedures appearing successively, with a certain kind of white space (I think it was related to tab characters) separating them, and when that pattern ocurred, then this 201 "prepare SQL object" error was exhibited. I don't really remember, it was too long ago. It was the tech, I believe, that suggested the trick of breaking the SQL file into two or more pieces. So, then, I think, IBM's position was that it will be fixed in a future version, and we have provided a work around now, so just live with it. Somethin like that. > > i.e. you have the dbexport directory with the created sql, which is > you split it works? Actually, I usually split the table DDL out from all the rest. That's where the "trickyness" in the dbexport/dbimport <dbname>.sql file is anyway (with the metadata in the comments, etc.). The rest of the sql file is pretty normal, and I just run it through dbaccess (after I run dbimport using the table portion of the sql file). > > You could just put the bit of the dbname.sql where you get the -201 > error here, and "someone" could log a defect based on that :o) In the past, I have spent many hours trying to isolate just where, PRECISELY, the problem was. The error message does not appear where the error is (buffering issue perhaps?). I have long given up trying to find the error(s). The one time I did work on it, when I thought I found something and tried to fix it, the same error popped up again in a different place. I have long since resigned myself to the fact that I just can't use dbexport/dbimport as they are intended. I have to split the file, run table DDL through dbimport, and the rest through dbaccess, and that works. It's just that it's embarassing having to explain to a contractor that it doesn't really work as advertised, and "Oh, here's the secret spell to make it work." DG

<davidegrove@gmail.com> wrote in message news:2faba2e7-359a-4dc8-924c-9d45a67a0f58@w24g2000prd.googlegroups.com... > > >> So, you have a "reproducible" test case, and IBM Informix Support did >> not get a defect / APAR logged and fixed? > > I did at one time (several years ago). IBM acknowledged it. The tech > support guy sent me some (internal?) stuff that said something about a > bug that was not fully understood, but was thought maybe to be > associated with stored procedures appearing successively, with a > certain kind of white space (I think it was related to tab characters) > separating them, and when that pattern ocurred, then this 201 "prepare > SQL object" error was exhibited. I don't really remember, it was too > long ago. It was the tech, I believe, that suggested the trick of > breaking the SQL file into two or more pieces. Could it be this?: we had a PMR, 52108,019,866, where dbimport failed on IDS 11.5. The Tech Support analysis was that it was a known problem and that the dbimport SQL "be split the script in different blocks for example 8000 rows then execute the first block then force IDS to remove the session control block to clean all the memory so just close the dbaccess and then start an new dbaccess that will execute the next block and so on". This was in fact effective but the customer preferred to go with version 10 instead. It's said to be fixed in 11.5xC2.

On Oct 31, 4:11 am, scottishpoet <drybur...@yahoo.com> wrote: > What is the APAR number of this unresolved issue that you have > previously reported? Or if its been a long time, perhaps the original > Informix defect number. I have to dig through a bunch of old (paper) files to try to find this. I'll make a "first level" attempt to find it and will post, if successful. But, a search of cdi will reveal past discussions on this problem with dbexport/dbimport. I know I started one thread. As I recall, other posters also voiced either the same, or similar, experiences. Point is, our situation is not unique. Others have found same or similar difficulties. I just think that IBM should have fixed it long ago. Until they do, I know how to work around it, but it caught someone who didn't by surprise. DG

On Oct 31, 8:19 am, "Neil Truby" <neil.tr...@ardenta.com> wrote: > > Could it be this?: we had a PMR, 52108,019,866, where dbimport failed on IDS > 11.5.  The Tech Support analysis was that it was a known problem and that > the dbimport SQL "be split the script in different blocks for example 8000 > rows then execute the first block  then force IDS to remove the session > control block to clean all the memory so just close the dbaccess and then > start an new dbaccess that will execute the next block and so on". > > This was in fact effective but the customer preferred to go with version 10 > instead. > > It's said to be fixed in 11.5xC2. VERY INTERESTING. Could be. Certainly sounds very like it. I think we don't see the problem with a small instance (say only a few hundred MB). But, we were experiencing it back with 9.4. Maybe even 9.21. Don't really recall, we've been on 10.0 for a long time. (Point is it has been defective for years.) Anyway, we are planning to go to 11.5 before end of year. DG

On Oct 31, 5:26 pm, davidegr...@gmail.com wrote: > On Oct 31, 4:11 am, scottishpoet <drybur...@yahoo.com> wrote: > > > What is the APAR number of this unresolved issue that you have > > previously reported? Or if its been a long time, perhaps the original > > Informix defect number. > > I have to dig through a bunch of old (paper) files to try to find > this.  I'll make a "first level" attempt to find it and will post, if > successful. > > But, a search of cdi will reveal past discussions on this problem with > dbexport/dbimport.  I know I started one thread.  As I recall, other > posters also voiced either the same, or similar, experiences.  Point > is, our situation is not unique.  Others have found same or similar > difficulties. > > I just think that IBM should have fixed it long ago.  Until they do, I > know how to work around it, but it caught someone who didn't by > surprise. > > DG a search of CDI will probably return a number of bugs, all with similar symptoms, some of which will be fixed Could it be possible the 201 error you saw several years ago has been fixed and this is a different problem with similar symptoms

On 31 Oct, 01:45, "DGPretzel" <d...@gci.net> wrote: > Exporting from 11.5, importing into 10.0.  Using very "plain-vanilla" > features only. > > Fixed by usual method of breaking *.sql into two pieces and running each > separately. > > Thank you for your interest. > > DG > > "John Carlson" <jwcarls...@yahoo.com> wrote in message > > news:AqsOk.54378$kh2.22016@bignews3.bellsouth.net... > > > > > davidegr...@gmail.com wrote: > >> We are currently working with a contractor and a consortium of several > >> states on a large project.  Each state is free to choose its own > >> database.  (We are using Informix.) > > >> The contractor has past experience with Oracle. > > >> One of the contractor's on-site staff was quite surprised today, after > >> encountering defects (-201 error) with dbimport.  We were attempting > >> to import into R10 a database exported from R11.5.   (The problem was > >> the dbimport being unable to use the *.sql file produced by > >> dbexport).  I explained to him how we deal with the problem (usually > >> just pulling out the table DDL to use with dbimport, while using > >> dbaccess to run the remainder.  I told him that the situation is > >> pretty common in our experience, and that IBM has know about it at > >> least since about 5 years ago when we had (an unresolved) tech support > >> case on it.  The contractor staff person was mystified that such a > >> basic utility could be defective.  I  admit that I, too, am quite > >> annoyed with it, every time I encounter the problem. > > >> I heard him later on the phone talking to his firm.  I don't think > >> Informix won any gold stars today. > > >> DG > > > What version?- Hide quoted text - > > - Show quoted text - Which version 10? FC9 or earlier? If you can reproduce it then have you sent a dbschema -ss to informix or worst case a level 0 archive of the instance?