Re: dbimport 9.40 hates tables without owner names?
"Andrew Hamm" <ahamm@mail.com> wrote in message news:<2piguiFl5ndnU1@uni-berlin.de>...
> Madison Pruet wrote:
> > Well --- I haven't been able to reproduce the problem.
> >
> > I guess the main question that I have is where did the dbexport x.sql
> > script come from? We should be generating the sql script with the
> > owner in both the schema definition and the table definition. The
> > only way that I can think of that you would be getting the duplicate
> > error is for a non-ansi database to have been created without the
> > table's owner name in one place and the owner in the other?
> >
> > When you get a case open on this let us know.
> >
> > By the way --- I noticed in your other emails that some of this
> > involved migration and/or [ANSI?]
>
> Yes - export from Pyramid 7.3ish, to Solaris 9 with 9.40.FC4.
>
> In brief:
>
> No matter where I go, I find random table owners. This place is no
> exception. If the opportunity arises for an export/import (ie upgrade or
> something) then I simply run a little scriptlet which removes the owner
> names from the .sql file in the .exp directory. The dbimport will then load
> all the tables and objects with an owner that is the userid of the account
> used to perform the dbimport.
>
> This practice has worked for me since the day when OTC was still only a
> MIME, knee-high to Marcel Marceu.
>
> In this instance, I'm doing a dbexport from a 7.3ish engine (running on a
> Pyramid, but since the export goes to ASCII it barely matters), then
> applying the usual cleanup of the .sql file and performing an import on a
> 9.40.FC4 engine into Solaris.
>
> At this point it barfed with the error message I mentioned.
>
> The source database is not ANSI. The target database is not ANSI. However I
> do not see anything in the dbexport which would MARK the export as being
> from an ANSI (oh, ok - unique table names would come from "owner".table)
>
> anyway, the source DB is not ANSI so there is no potentially conflicting
> table names.
>
> The dbimport is not performed using the -ansi flag so it it not going to
> ansi.
>
> The table it barfs on is the very first table.
>
> Putting a suitable "owner". into the { TABLE part of the .sql file was
> sufficient to allow the import to proceed.
>
> An opportunity to do another export/import is coming soon, so I'll try to
> build a firmer test case and see what other interesting variations has any
> effect.
>
> As far as I can see, it must be an error in dbimport; if you can't reproduce
> it then it must be subtle :-)
or a bug in your "sciptlet" :o)
can you mail me a copy of the actual .sql file that is failing?
scottishpoet wrote:
>
> or a bug in your "sciptlet" :o)
it only removes the "random_owner". part. Since I started using Perl -0777
to read the entire file in one string, it's even been able to catch the
"random_owner". parts which are occasionally split across multiple lines
(common in view definitions)
My first posting showing the two alternatives is a straight cut-n-paste from
the file. I fail to see the syntax error, especially when I manually used vi
to re-insert some specialised owner names:
:%s/{ TABLE /{ TABLE "dbowner"./
one thing I haven't mentioned is that I've also inserted up the front of the
script the following lines which have also worked "forever":
grant dba to "dbowner";
grant resource to "bruce";
grant resource to "shiela";
grant connect to "public";
alter table systables next size 256;
alter table syscolumns next size 256;......
The grants are a straight replacement of the exported list of random db
priviledges that have also accumulated (so no surprises in the grants)
The alters are so that the big sys tables stay well-ordered - otherwise I
found that during a large import with large tables, the sys tables ended up
quite scattered with their extents separated by hundreds of megs of
intervening extents; this does affect query speed.
If there's something in these statements (and I didn't try removing them
since adding an "dbowner". fixed the problem) then once again I would call
it a bug in the engine or dbimport. I can't see how poking the next size of
sys tables should somehow provoke a "100 - ISAM error: duplicate value for
a record with unique key." on the create table statement.
To reiterate:
<sample>
{ database whatever is in this line - i don't recall from memory }
grant dba to "dbowner";
grant resource to "bruce";
grant resource to "shiela";
grant connect to "public";
alter table systables next size 256;.....
{ TABLE cgmcmndd row size = 83 number of columns = 5 index size = 21 }
{ unload file name = cgmcm00102.unl number of rows = 104 }
create table cgmcmndd</sample>
is a failure, and
<sample>
{ database whatever is in this line - i don't recall from memory }
grant dba to "dbowner";
grant resource to "bruce";
grant resource to "shiela";
grant connect to "public";
alter table systables next size 256;.....
{ TABLE "dbowner".cgmcmndd row size = 83 number of columns = 5 index size =
21 }
{ unload file name = cgmcm00102.unl number of rows = 104 }
create table cgmcmndd</sample>
is a complete success. The only thing that makes it work is adding
"dbowner".
summary:
Yes, I am modifying the export's .sql file. Yes, I've done it successfully
for years. Now, this peculiar error has surfaced, with an equally peculiar
work-around. I do not believe that any of my changes are intrinsically
"illegal" although strictly speaking, the "language" of dbimport has never
been "published"... If there was a clear syntax error saying "must have an
owner name on every object" then I could understand. But as you can see,
there isn't even a consistent requirement or a sensible error message.
During the import, I am logged in as "dbowner" (to be precise, "informix" at
the request of the customer; since they are such a small IT staff I've just
shrugged rather than try to talk them out of it)
> can you mail me a copy of the actual .sql file that is failing?
in a few days - I'm back at my desk today (oh how sweet familiar
surroundings are) E-mail directly to you? it's going to be too big to post
to the ng, that's for sure.
please email the .sql file direct to my yahoo address.
I'll then set up my own dbaccess test, export a 9.40 stores_demo
database, try and edit the .sql as per the changes I see in your .sql
file and see if it imports into 9.40 and 9.30. this should be quite a
simple test case.
have you tried this yourself?
have you discussed this issue with your Informix support provider as
per madisson's earlier post?
"Andrew Hamm" <ahamm@mail.com> wrote in message news:<2pnbhtFn6t01U1@uni-berlin.de>...
> scottishpoet wrote:
> >
> > or a bug in your "sciptlet" :o)
>
> it only removes the "random_owner". part. Since I started using Perl -0777
> to read the entire file in one string, it's even been able to catch the
> "random_owner". parts which are occasionally split across multiple lines
> (common in view definitions)
>
> My first posting showing the two alternatives is a straight cut-n-paste from
> the file. I fail to see the syntax error, especially when I manually used vi
> to re-insert some specialised owner names:
>
> :%s/{ TABLE /{ TABLE "dbowner"./
>
> one thing I haven't mentioned is that I've also inserted up the front of the
> script the following lines which have also worked "forever":
>
> grant dba to "dbowner";
> grant resource to "bruce";
> grant resource to "shiela";
> grant connect to "public";>
> alter table systables next size 256;
> alter table syscolumns next size 256;> ......
>
> The grants are a straight replacement of the exported list of random db
> priviledges that have also accumulated (so no surprises in the grants)
>
> The alters are so that the big sys tables stay well-ordered - otherwise I
> found that during a large import with large tables, the sys tables ended up
> quite scattered with their extents separated by hundreds of megs of
> intervening extents; this does affect query speed.
>
> If there's something in these statements (and I didn't try removing them
> since adding an "dbowner". fixed the problem) then once again I would call
> it a bug in the engine or dbimport. I can't see how poking the next size of
> sys tables should somehow provoke a "100 - ISAM error: duplicate value for
> a record with unique key." on the create table statement.
>
> To reiterate:
>
> <sample>
> { database whatever is in this line - i don't recall from memory }
>
> grant dba to "dbowner";
> grant resource to "bruce";
> grant resource to "shiela";
> grant connect to "public";>
> alter table systables next size 256;> .....
> { TABLE cgmcmndd row size = 83 number of columns = 5 index size = 21 }
> { unload file name = cgmcm00102.unl number of rows = 104 }
> create table cgmcmndd> </sample>
>
> is a failure, and
>
> <sample>
> { database whatever is in this line - i don't recall from memory }
>
> grant dba to "dbowner";
> grant resource to "bruce";
> grant resource to "shiela";
> grant connect to "public";>
> alter table systables next size 256;> .....
> { TABLE "dbowner".cgmcmndd row size = 83 number of columns = 5 index size =
> 21 }
> { unload file name = cgmcm00102.unl number of rows = 104 }
> create table cgmcmndd> </sample>
>
> is a complete success. The only thing that makes it work is adding
> "dbowner".
>
> summary:
>
> Yes, I am modifying the export's .sql file. Yes, I've done it successfully
> for years. Now, this peculiar error has surfaced, with an equally peculiar
> work-around. I do not believe that any of my changes are intrinsically
> "illegal" although strictly speaking, the "language" of dbimport has never
> been "published"... If there was a clear syntax error saying "must have an
> owner name on every object" then I could understand. But as you can see,
> there isn't even a consistent requirement or a sensible error message.
> During the import, I am logged in as "dbowner" (to be precise, "informix" at
> the request of the customer; since they are such a small IT staff I've just
> shrugged rather than try to talk them out of it)
>
> > can you mail me a copy of the actual .sql file that is failing?
>
> in a few days - I'm back at my desk today (oh how sweet familiar
> surroundings are) E-mail directly to you? it's going to be too big to post
> to the ng, that's for sure.