dbimport 9.40 hates tables without owner names?

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 isthe 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.