Re: dbexport - syntax error
On Wed, 27 Sep 2000, Mark D. Stock wrote:
> Friedrich Lobenstock wrote:
> >
> > fl@fl.priv.at wrote:
> > > Hi all,
> >
> > > I have a problem with dbexport. When I want to run it like
> > > dbexport -c DATABASE> > > all seems fine at first, BUT after about 200Megas of files
> > > written to disc I get an ominous "syntax error".
> >
> > Now I know more about the system:
> > INFORMIX-OnLine 5.03.UC2
> > running on
> > SCO UNIX System V/386 Release 3.2
> >
> > All I want to do is a complete ('-c') export.
>
> The -c option does not mean 'complete', it means 'continue on errors'.
>
> That is a strange error to get from dbexport. Are you sure you are
> running the Informix executable and not some other script? Have you
> tried:
>
> $INFORMIXDIR/bin/dbexport DATABASE
>
host> [146]: id
uid=201(informix) gid=100(informix)
host> [147]: pwd
/tmp/export
host> [148]: type -p dbexport
dbexport is /usr/informix/bin/dbexport
host> [149]:
With "files written to disc" (my first mail above) I mean
real looking files, one with sql statements which create
the tables and references to the datafile and the mentioned
datafiles (seperator '|'). Here the begin of this file:
{ DATABASE hasy delimiter | }
{ Encoded Optical BLOB descriptor }
grant dba to "hasy";
grant dba to "root";
{ TABLE "hasy".xmenu row size = 1971 number of columns = 7 index size = 27 }
{ unload file name = xmenu__100.unl number of rows = 210 }
create table "hasy".xmenu
(
id_mfa integer not null,
cod_sprache smallint not null,
nam_menu char(12) not null, txt_menu char(1620),
cod_menu char(108) not null,
cod_return char(216) not null,
dat_aend datetime year to fraction(2) not null
);
revoke all on "hasy".xmenu from "public";
and so on...
MfG / Regards
Friedrich Lobenstock
Friedrich Lobenstock <fl@fl.priv.at> wrote:
> Here the begin of this file:
> { DATABASE hasy delimiter | }
> { Encoded Optical BLOB descriptor }
> grant dba to "hasy";
> grant dba to "root";
> { TABLE "hasy".xmenu row size = 1971 number of columns = 7 index size = 27 }
> { unload file name = xmenu__100.unl number of rows = 210 }
And here the last lines where the error occures:
create unique index "hasy".odbcean_1 on "hasy".odbcean (id_artikel,firma desc,filiale desc,abteilung desc);
create unique index "hasy".odbcean_2 on "hasy".odbcean (num_artikel,firma desc,filiale desc,abteilung desc);
{ TABLE "hasy".odbcliefart row size = 69 number of columns = 14 index size = 46 }
{ unload file name = odbcli3165.unl number of rows = 2 }
create table "hasy".odbcliefart
(
firma smallint not null,
filiale smallint not null,
abteilung smallint not null,
num_liefer integer not null,
num_artikel char(15) not null,
num_liefart char(15) not null,
preiseinheit char(3),
anz_preisfaktor smallint not null,
bet_ekfpreis decimal(12,2) not null,
stz_rabatt1 decimal(4,2) not null,
stz_rabatt2 decimal(4,2) not null,
stz_rabatt3 decimal(4,2) not null,
id_adresse integer not null,
id_artikel integer not null
);
revoke all on "hasy".odbcliefart from "public";
create unique index "hasy".odbcliefart_1 on "hasy".odbcliefart (id_adresse,id_artikel);
create index "hasy".odbcliefart_2 on "hasy".odbcliefart (num_liefart);
A syntax error has occurred.
ANY clues?
MfG / Regards
Friedrich Lobenstock
Friedrich Lobenstock <fl@fl.priv.at> wrote:
> create unique index "hasy".odbcean_1 on "hasy".odbcean (id_artikel,firma desc,filiale desc,abteilung desc);
> create unique index "hasy".odbcean_2 on "hasy".odbcean (num_artikel,firma desc,filiale desc,abteilung desc);
> { TABLE "hasy".odbcliefart row size = 69 number of columns = 14 index size = 46 }
> { unload file name = odbcli3165.unl number of rows = 2 }
> create table "hasy".odbcliefart
> (
> firma smallint not null,
> filiale smallint not null,
> abteilung smallint not null,
> num_liefer integer not null,
> num_artikel char(15) not null,
> num_liefart char(15) not null,
> preiseinheit char(3),
> anz_preisfaktor smallint not null,
> bet_ekfpreis decimal(12,2) not null,
> stz_rabatt1 decimal(4,2) not null,
> stz_rabatt2 decimal(4,2) not null,
> stz_rabatt3 decimal(4,2) not null,
> id_adresse integer not null,
> id_artikel integer not null
> );
> revoke all on "hasy".odbcliefart from "public";
> create unique index "hasy".odbcliefart_1 on "hasy".odbcliefart (id_adresse,id_artikel);
> create index "hasy".odbcliefart_2 on "hasy".odbcliefart (num_liefart);
> A syntax error has occurred.
A dump of this table:
1|0|0|9999999|9100|9100|STK|1|0,0|0,0|0,0|0,0|6835|1328|
1|0|0|9999999|default|default|ST|1|0,0|0,0|0,0|0,0|6835|1|
Where the heck is the problem with this table?
PS: Sorry if all the info just comes bit by bit, but I have to
export to this database and I know basic sql, but I don't know
Informix sql.
MfG / Regards
Friedrich Lobenstock
On 28 Sep 2000 21:55:05 GMT, Friedrich Lobenstock <fl@fl.priv.at> wrote:
>Friedrich Lobenstock <fl@fl.priv.at> wrote:
>> Here the begin of this file:
>
>> { DATABASE hasy delimiter | }
>
>> { Encoded Optical BLOB descriptor }
>
>> grant dba to "hasy";
>> grant dba to "root";>
>> { TABLE "hasy".xmenu row size = 1971 number of columns = 7 index size = 27 }
>> { unload file name = xmenu__100.unl number of rows = 210 }
>
>
>And here the last lines where the error occures:
>
>create unique index "hasy".odbcean_1 on "hasy".odbcean (id_artikel,firma desc,filiale desc,abteilung desc);
>create unique index "hasy".odbcean_2 on "hasy".odbcean (num_artikel,firma desc,filiale desc,abteilung desc);
>{ TABLE "hasy".odbcliefart row size = 69 number of columns = 14 index size = 46 }
>{ unload file name = odbcli3165.unl number of rows = 2 }
>
>create table "hasy".odbcliefart
> (
> firma smallint not null,
> filiale smallint not null,
> abteilung smallint not null,
> num_liefer integer not null,
> num_artikel char(15) not null,
> num_liefart char(15) not null,
> preiseinheit char(3),
> anz_preisfaktor smallint not null,
> bet_ekfpreis decimal(12,2) not null,
> stz_rabatt1 decimal(4,2) not null,
> stz_rabatt2 decimal(4,2) not null,
> stz_rabatt3 decimal(4,2) not null,
> id_adresse integer not null,
> id_artikel integer not null
> );
>revoke all on "hasy".odbcliefart from "public";>
>create unique index "hasy".odbcliefart_1 on "hasy".odbcliefart (id_adresse,id_artikel);
>create index "hasy".odbcliefart_2 on "hasy".odbcliefart (num_liefart);
>A syntax error has occurred.
>
>ANY clues?
>
>
>MfG / Regards
>Friedrich Lobenstock
Try to make schema of that table and unload of that table.
dbschema -d hasy -t odbcliefartdbaccess hasy -
unload to xxx.unl select * from odbcliefart;
^D
If that works the problem is not in table odbcliefart.
When DBEXPORT works it unloads tables in systables.tabid order. I think that for
table odbcliefart tabid=3165 (check that in systables). So, find the names of
few next tables:
select tabid,tabname from systables where tabid>3165 order by tabid;
For every table try dbschema+unload and you can find which table is making
problems.
If table odbcliefart is "the last" table (with the biggest tabid) than the
problem is in constraints because after unloading tables DBEXPORT is writing
constraints definitions in hasy.sql. If that is true you have to make dbschema
for every table in your database and find the "guilty" one.
Hope that will help.
Nebojsa