Hi folks

dbimport from 9.40 used like this:

dbimport dbname # ie REALLY boring

is dying IMMEDIATELY on the first table with this error message:

{ TABLE cgmcmndd row size = 83 number of columns = 5 index size = 21 }
{ unload file name = cgmcm00102.unl number of rows = 104 }
create table cgmcmndd
(
m_name char(8),
m_order smallint,
m_lang char(3),
m_label char(20),
m_help char(50)
) in data30dbs extent size 16 next size 16;
100 - ISAM error: duplicate value for a record with unique key.

When I put an owner name in:

{ TABLE "sombody".cgmcmndd row size = 83 number of columns = 5 index size =
21 }

it is happy and the load proceeds. WHAT THE? It doesn't appear to need an
owner on the CREATE TABLE etc statements.

Am I failing to understand or even discover something fantastically
brilliant in 9.40 which mandates this behaviour?

Solaris 5.9, IDS 9.40.FC4, late at night, wanna go home.

Andrew Hamm wrote:
> dbimport from 9.40 used like this:
>
> dbimport dbname # ie REALLY boring
>
> is dying IMMEDIATELY on the first table with this error message:
>
> { TABLE cgmcmndd row size = 83 number of columns = 5 index size = 21 }
> { unload file name = cgmcm00102.unl number of rows = 104 }
> create table cgmcmndd
> (
> m_name char(8),
> m_order smallint,
> m_lang char(3),
> m_label char(20),
> m_help char(50)
> ) in data30dbs extent size 16 next size 16;
> 100 - ISAM error: duplicate value for a record with unique key.
>
> When I put an owner name in:
>
> { TABLE "sombody".cgmcmndd row size = 83 number of columns = 5 index size =
> 21 }
>
> it is happy and the load proceeds. WHAT THE? It doesn't appear to need an
> owner on the CREATE TABLE etc statements.
>
> Am I failing to understand or even discover something fantastically
> brilliant in 9.40 which mandates this behaviour?
>
> Solaris 5.9, IDS 9.40.FC4, late at night, wanna go home.


If the owner name is mandatory, as far as I am concerned, it is a bug.

(And it would be nice if DB-Export had an option to omit the owner
names, too -- ditto DB-Schema).

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

"Andrew Hamm" <ahamm@mail.com> wrote in message
news:2p39saFg86o6U1@uni-berlin.de...
> Hi folks
>
> dbimport from 9.40 used like this:
>
> dbimport dbname # ie REALLY boring
>
> is dying IMMEDIATELY on the first table with this error message:
>
> { TABLE cgmcmndd row size = 83 number of columns = 5 index size = 21 }
> { unload file name = cgmcm00102.unl number of rows = 104 }
> create table cgmcmndd
> (
> m_name char(8),
> m_order smallint,
> m_lang char(3),
> m_label char(20),
> m_help char(50)
> ) in data30dbs extent size 16 next size 16;
> 100 - ISAM error: duplicate value for a record with unique key.
>
> When I put an owner name in:
>
> { TABLE "sombody".cgmcmndd row size = 83 number of columns = 5 index size
=
> 21 }
>
> it is happy and the load proceeds. WHAT THE? It doesn't appear to need an
> owner on the CREATE TABLE etc statements.
>
> Am I failing to understand or even discover something fantastically
> brilliant in 9.40 which mandates this behaviour?
>
> Solaris 5.9, IDS 9.40.FC4, late at night, wanna go home.
>
>

Is there an ANSI database somewhere in the picture?

Jonathan Leffler wrote:
>
> If the owner name is mandatory, as far as I am concerned, it is a bug.

I've failed to come up with a reason why the lack of owner names causes the
duplicate error. Although I didn't mention it, the first time I tried to fix
it, I put the owner on both the TABLE and the create table. Then I began to
wonder which was the critical one...

> (And it would be nice if DB-Export had an option to omit the owner
> names, too -- ditto DB-Schema).

yes indeedy. And I wish that dbexport would also emit the "next value" for
all serial fields. Right now we have to sniff around in the output of
oncheck (traditional) or the new-fangled system tables. If there are
concerns about compatibility, perhaps yet another flag can be added. That
makes more sense for dbschema actually since it's not necessarily supposed
to contain any data in the schema.

Assuming you can reproduce the problem, can I leave it in your culpable i
mean capable hands as a bug report?

Madison Pruet wrote:
>
> Is there an ANSI database somewhere in the picture?

no - that was my 1st or 2nd thought too. But no. Perhaps dbimport or one
little corner of it is mis-reading the is_ansi flag now?

Andrew Hamm wrote:
> Jonathan Leffler wrote:
>>If the owner name is mandatory, as far as I am concerned, it is a bug.
>
> I've failed to come up with a reason why the lack of owner names causes the
> duplicate error. Although I didn't mention it, the first time I tried to fix
> it, I put the owner on both the TABLE and the create table. Then I began to
> wonder which was the critical one...

I've not investigated.

>>(And it would be nice if DB-Export had an option to omit the owner
>>names, too -- ditto DB-Schema).
>
> yes indeedy. And I wish that dbexport would also emit the "next value" for
> all serial fields. Right now we have to sniff around in the output of
> oncheck (traditional) or the new-fangled system tables.

'Next serial' is (a) volatile and (b) non-obvious. Sequences are
worse, if it is any consolation. Amongst other tricks, in an ER
setup, one machine can be using serial number 1, 11, 21, 31, ... while
another is using 2, 12, 22, 32. Suppose the maximum value in the
current server is 32; what's the next serial number? Answer - it
depends on which server generates the number.

Additionally, if you actually import the data (DB-Import), then the
number will be set for you, pretty much. If one row gets the value
13001, then the next value will be 13002 or 13011, depending...

> If there are concerns about compatibility, perhaps yet another flag
> can be added. That makes more sense for dbschema actually since
> it's not necessarily supposed to contain any data in the schema.

True (DB-Schema doesn't dump any data). Handling sequences and
generating a 'next value' for DB-Schema or DB-Export also consumes
that value in the original database -- and there isn't an easy way to
avoid that.

> Assuming you can reproduce the problem, can I leave it in your culpable i
> mean capable hands as a bug report?

I've not even attempted to reproduce it - no, please you get it
entered as a bug through Tech Support. It then has a customer case
and can't be rejected NFI-NCC (no fix intended, no customer case).

OTOH, if you run into resistance, you can quote me in support of your
case for making it a bug, or set the poor innocent Tech Support
engineer in my direction, asking them to contact me...

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Jonathan Leffler wrote:
>
> 'Next serial' is (a) volatile and (b) non-obvious. Sequences are
> worse, if it is any consolation. Amongst other tricks, in an ER
> setup, one machine can be using serial number 1, 11, 21, 31, ... while
> another is using 2, 12, 22, 32. Suppose the maximum value in the
> current server is 32; what's the next serial number? Answer - it
> depends on which server generates the number.
>
> Additionally, if you actually import the data (DB-Import), then the
> number will be set for you, pretty much. If one row gets the value
> 13001, then the next value will be 13002 or 13011, depending...

Not if the table with serial is subject to deletion of rows. Especially when
used as a poor-mans sequence generator from way back. Ignoring dbschema for
now, my belief is that a dbexport represents the true state of the database
as it is. When reimported it should represent the same state, therefore the
sequence number of the serials (or indeed the next sequence number of any
sequences) should be reproducible too. As you note, if the last known serial
happens to be in the database, then the upload of the row will fix the
serial number. However, there are a few tables where that is not necessarily
true, especially for one specific table which never has any rows except
transiently as it is used to generate a unique number. Where a number has
been allocated from the new SEQUENCE type, it sounds like the problem is
even worse since there's no bump-up happening when rows are inserted in any
tables.

> True (DB-Schema doesn't dump any data). Handling sequences and
> generating a 'next value' for DB-Schema or DB-Export also consumes
> that value in the original database -- and there isn't an easy way to
> avoid that.

That's not entirely true. The traditional method I've implied is to do an
oncheck -pT (I think it's pT) and amongst all the interesting information is
the next serial number. This is a copy of the secret integer, and it has not
caused a new number to be allocated. I'm under the impression that there are
new sysmaster tables which now also represent the secret hidden serial
number and do not cause it to bump just because you access it. I might be
wrong on that one - perhaps I'm just being hopeful. Anyway, I know the
oncheck trick works.

Summary - my attitude is, a dbexport is a standalone representation of the
database at that moment in time, and therefore the import should ensure that
serials and sequences are primed to the state they were in at the time of
the export, regardless of whether any table actually contains that last
number in it's current set of rows. If ER etc is involved, I don't think
it's relevant - you either have to re-sync your ER anyway, or you have
presumably stopped the ER participants (necessary before dbexport can get
the exclusive DB lock), performed the exports on both instances and will
upload them as a team at some later date.

As a matter of fact, at this customer site, this is exactly what we will be
doing. We are porting from a Pyramid/Siemens into Solaris, and as far as I
know they are definitely not binary compatible so we cannot transfer via
ontape. dbexport and dbimport are therefore the obvious candidate. The
Siemens is part of an ER pair with an existing Solaris, so we only need to
silence that machine for the duration, re-direct the replicants and in
theory should be up and running with the new server.

> I've not even attempted to reproduce it - no, please you get it
> entered as a bug through Tech Support. It then has a customer case
> and can't be rejected NFI-NCC (no fix intended, no customer case).

Fair enough. To reproduce would probably only require a very simple dbexport
and then edit the generated sqls (as you alluded to, I'm stripping all the
random user names that have accumulated over the years). But, the newsgroup
is not The Proper Channel. Thanks for the offer to wave you round like a war
hammer. I don't think i'll need to though, local support is very good at
listening.

On Thu, 26 Aug 2004 00:47:42 -0400, Andrew Hamm wrote:

Not to plug the product, especially since I don't get any $$ from it 8-(, but
myschema has options to output dbimport compatible schema files without owner
names and with the correct serial value initialized in the CREATE TABLE
statement.

Art S. Kagel

> Jonathan Leffler wrote:
>>
>> If the owner name is mandatory, as far as I am concerned, it is a bug.
>
> I've failed to come up with a reason why the lack of owner names causes the
> duplicate error. Although I didn't mention it, the first time I tried to fix
> it, I put the owner on both the TABLE and the create table. Then I began to
> wonder which was the critical one...
>
>> (And it would be nice if DB-Export had an option to omit the owner names,
>> too -- ditto DB-Schema).
>
> yes indeedy. And I wish that dbexport would also emit the "next value" for
> all serial fields. Right now we have to sniff around in the output of
> oncheck (traditional) or the new-fangled system tables. If there are
> concerns about compatibility, perhaps yet another flag can be added. That
> makes more sense for dbschema actually since it's not necessarily supposed
> to contain any data in the schema.
>
> Assuming you can reproduce the problem, can I leave it in your culpable i
> mean capable hands as a bug report?

Art S. Kagel wrote:
>
> Not to plug the product, especially since I don't get any $$ from it
> 8-(, but myschema has options to output dbimport compatible schema
> files without owner names and with the correct serial value
> initialized in the CREATE TABLE statement.

Have you noticed anything on 9.40.FC4 (and friends, I guess) which matches
my experience?

Also, can you explain to the forum where you are getting the serial number
from? Are you using the old oncheck trick, or is there a newer way via
sysmaster?

Finally, what about the new SEQUENCE object.... They seem to have a fairly
complex state, although I haven't used them yet.

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
"Andrew Hamm" <ahamm@mail.com> wrote in message
news:2p58kbFh8o3bU1@uni-berlin.de...
> Madison Pruet wrote:
> >
> > Is there an ANSI database somewhere in the picture?
>
> no - that was my 1st or 2nd thought too. But no. Perhaps dbimport or one
> little corner of it is mis-reading the is_ansi flag now?
>
>