dbexport problem
A user on IDS 7.31 (Solaris 2.8) hit "*** prepare unldobj / 201 - A syntax error has occurred" while running dbexport -ss, failing on a table whose columns included names like datetime, ref, code and text; dbschema worked fine. Suggestions were reserved-word column names (notably 'ref', with a rename-export-edit-rename workaround), using onmode -I 201 or SQLDEBUG/sqliprint to trap the offending statement, and mismatched dbexport versions. No definitive fix is recorded: a repeat run and another database exported successfully, and the poster suspected a transient disk problem.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Have had the following error exporting a database (using -ss into a
directory):
create table "me".tigs
(
seq serial not null ,
name char(30),
address char(40),
loc char(20),
pr1 money(16,2),
pr2 money(16,2),
pr3 money(16,2),
pr4 money(16,2),
datetime char(20),
ref char(20),
inhref char(20),
code char(10),
text char(20)
);
revoke all on "me".tigs from "public";
*** prepare unldobj
201 - A syntax error has occurred.
Tried:
dbschema -t all -s all -p all -f all -d mydb -ss schema.sql
This works fine, was exporting the database in case upgrading Informix
caused a problem.
Current version is 7.31.UD6 and it's running on Solaris 2.8. TAPEDEV
and LTAPEDEV are both set to /dev/null, there is bags of free room in
the file system I was exporting to, and it's not the 2GB problem - had
that one earlier, unloaded the table in two parts & dropped &
recreated it.
Any ideas, please?
On 17 Aug, 12:25, Cats <ramwa...@uk2.net> wrote:
> Have had the following error exporting a database (using -ss into a
> directory):
>
> create table "me".tigs
> (
> seq serial not null ,
> name char(30),
> address char(40),
> loc char(20),
> pr1 money(16,2),
> pr2 money(16,2),
> pr3 money(16,2),
> pr4 money(16,2),
> datetime char(20),
> ref char(20),
> inhref char(20),
> code char(10),
> text char(20)
> );
> revoke all on "me".tigs from "public";>
> *** prepare unldobj
> 201 - A syntax error has occurred.
>
> Tried:
> dbschema -t all -s all -p all -f all -d mydb -ss schema.sql>
> This works fine, was exporting the database in case upgrading Informix
> caused a problem.
>
> Current version is 7.31.UD6 and it's running on Solaris 2.8. TAPEDEV
> and LTAPEDEV are both set to /dev/null, there is bags of free room in
> the file system I was exporting to, and it's not the 2GB problem - had
> that one earlier, unloaded the table in two parts & dropped &
> recreated it.
>
> Any ideas, please?
Certainly 'datetime' and 'text' are reserved words, and possibly
'code'. And different platform releases of the same versions, let
alone different versions, behave differently with reserved words!
We had a case where 'ref', which is not listed as a reserved word,
caused a 4GL compilation failure.
HTH
Malc
On Aug 17, 12:46 pm, mal...@btinternet.com wrote:
> On 17 Aug, 12:25, Cats <ramwa...@uk2.net> wrote:
>
>
>
>
>
> > Have had the following error exporting a database (using -ss into a
> > directory):
>
> > create table "me".tigs
> > (
> > seq serial not null ,
> > name char(30),
> > address char(40),
> > loc char(20),
> > pr1 money(16,2),
> > pr2 money(16,2),
> > pr3 money(16,2),
> > pr4 money(16,2),
> > datetime char(20),
> > ref char(20),
> > inhref char(20),
> > code char(10),
> > text char(20)
> > );
> > revoke all on "me".tigs from "public";>
> > *** prepare unldobj
> > 201 - A syntax error has occurred.
>
> > Tried:
> > dbschema -t all -s all -p all -f all -d mydb -ss schema.sql>
> > This works fine, was exporting the database in case upgrading Informix
> > caused a problem.
>
> > Current version is 7.31.UD6 and it's running on Solaris 2.8. TAPEDEV
> > and LTAPEDEV are both set to /dev/null, there is bags of free room in
> > the file system I was exporting to, and it's not the 2GB problem - had
> > that one earlier, unloaded the table in two parts & dropped &
> > recreated it.
>
> > Any ideas, please?
>
> Certainly 'datetime' and 'text' are reserved words, and possibly
> 'code'. And different platform releases of the same versions, let
> alone different versions, behave differently with reserved words!
> We had a case where 'ref', which is not listed as a reserved word,
> caused a 4GL compilation failure.
>
I can see where you are coming from, but we've successfully dbexported
it earlier this year and the table 'tigs' was present with the same
column name (it was created in 2004) so I suspect that's not the
problem. :( (wish it was - drop the table, dbexport, recreate &
reload the table).
Well if you have the instance all for yourself you could do a onmode -
I 201
then rerun dbexport
the engine barfs an assert with the problem statement....
or use
export SQLDEBUG=2:<somefs>/<somefile>
and use sqliprint <somefs>/<somefile>* to find the 201.....
Superboer.
On 17 aug, 13:57, Cats <ramwa...@uk2.net> wrote:
> On Aug 17, 12:46 pm, mal...@btinternet.com wrote:
>
>
>
> > On 17 Aug, 12:25, Cats <ramwa...@uk2.net> wrote:
>
> > > Have had the following error exporting a database (using -ss into a
> > > directory):
>
> > > create table "me".tigs
> > > (
> > > seq serial not null ,
> > > name char(30),
> > > address char(40),
> > > loc char(20),
> > > pr1 money(16,2),
> > > pr2 money(16,2),
> > > pr3 money(16,2),
> > > pr4 money(16,2),
> > > datetime char(20),
> > > ref char(20),
> > > inhref char(20),
> > > code char(10),
> > > text char(20)
> > > );
> > > revoke all on "me".tigs from "public";>
> > > *** prepare unldobj
> > > 201 - A syntax error has occurred.
>
> > > Tried:
> > > dbschema -t all -s all -p all -f all -d mydb -ss schema.sql>
> > > This works fine, was exporting the database in case upgrading Informix
> > > caused a problem.
>
> > > Current version is 7.31.UD6 and it's running on Solaris 2.8. TAPEDEV
> > > and LTAPEDEV are both set to /dev/null, there is bags of free room in
> > > the file system I was exporting to, and it's not the 2GB problem - had
> > > that one earlier, unloaded the table in two parts & dropped &
> > > recreated it.
>
> > > Any ideas, please?
>
> > Certainly 'datetime' and 'text' are reserved words, and possibly
> > 'code'. And different platform releases of the same versions, let
> > alone different versions, behave differently with reserved words!
> > We had a case where 'ref', which is not listed as a reserved word,
> > caused a 4GL compilation failure.
>
> I can see where you are coming from, but we've successfully dbexported
> it earlier this year and the table 'tigs' was present with the same
> column name (it was created in 2004) so I suspect that's not the
> problem. :( (wish it was - drop the table, dbexport, recreate &
> reload the table).
On Aug 17, 12:57 pm, Cats <ramwa...@uk2.net> wrote:
> On Aug 17, 12:46 pm, mal...@btinternet.com wrote:
>
>
>
>
>
> > On 17 Aug, 12:25, Cats <ramwa...@uk2.net> wrote:
>
> > > Have had the following error exporting a database (using -ss into a
> > > directory):
>
> > > create table "me".tigs
> > > (
> > > seq serial not null ,
> > > name char(30),
> > > address char(40),
> > > loc char(20),
> > > pr1 money(16,2),
> > > pr2 money(16,2),
> > > pr3 money(16,2),
> > > pr4 money(16,2),
> > > datetime char(20),
> > > ref char(20),
> > > inhref char(20),
> > > code char(10),
> > > text char(20)
> > > );
> > > revoke all on "me".tigs from "public";>
> > > *** prepare unldobj
> > > 201 - A syntax error has occurred.
>
> > > Tried:
> > > dbschema -t all -s all -p all -f all -d mydb -ss schema.sql>
> > > This works fine, was exporting the database in case upgrading Informix
> > > caused a problem.
>
> > > Current version is 7.31.UD6 and it's running on Solaris 2.8. TAPEDEV
> > > and LTAPEDEV are both set to /dev/null, there is bags of free room in
> > > the file system I was exporting to, and it's not the 2GB problem - had
> > > that one earlier, unloaded the table in two parts & dropped &
> > > recreated it.
>
> > > Any ideas, please?
>
> > Certainly 'datetime' and 'text' are reserved words, and possibly
> > 'code'. And different platform releases of the same versions, let
> > alone different versions, behave differently with reserved words!
> > We had a case where 'ref', which is not listed as a reserved word,
> > caused a 4GL compilation failure.
>
> I can see where you are coming from, but we've successfully dbexported
> it earlier this year and the table 'tigs' was present with the same
> column name (it was created in 2004) so I suspect that's not the
> problem. :( (wish it was - drop the table, dbexport, recreate &
> reload the table).
And to update, I've just successfully exported the other database (in
a separate instance) which also contained 'tigs'...
On Aug 17, 3:29 pm, Cats <ramwa...@uk2.net> wrote:
> On Aug 17, 12:57 pm, Cats <ramwa...@uk2.net> wrote:
>
>
>
>
>
> > On Aug 17, 12:46 pm, mal...@btinternet.com wrote:
>
> > > On 17 Aug, 12:25, Cats <ramwa...@uk2.net> wrote:
>
> > > > Have had the following error exporting a database (using -ss into a
> > > > directory):
>
> > > > create table "me".tigs
> > > > (
> > > > seq serial not null ,
> > > > name char(30),
> > > > address char(40),
> > > > loc char(20),
> > > > pr1 money(16,2),
> > > > pr2 money(16,2),
> > > > pr3 money(16,2),
> > > > pr4 money(16,2),
> > > > datetime char(20),
> > > > ref char(20),
> > > > inhref char(20),
> > > > code char(10),
> > > > text char(20)
> > > > );
> > > > revoke all on "me".tigs from "public";>
> > > > *** prepare unldobj
> > > > 201 - A syntax error has occurred.
>
> > > > Tried:
> > > > dbschema -t all -s all -p all -f all -d mydb -ss schema.sql>
> > > > This works fine, was exporting the database in case upgrading Informix
> > > > caused a problem.
>
> > > > Current version is 7.31.UD6 and it's running on Solaris 2.8. TAPEDEV
> > > > and LTAPEDEV are both set to /dev/null, there is bags of free room in
> > > > the file system I was exporting to, and it's not the 2GB problem - had
> > > > that one earlier, unloaded the table in two parts & dropped &
> > > > recreated it.
>
> > > > Any ideas, please?
>
> > > Certainly 'datetime' and 'text' are reserved words, and possibly
> > > 'code'. And different platform releases of the same versions, let
> > > alone different versions, behave differently with reserved words!
> > > We had a case where 'ref', which is not listed as a reserved word,
> > > caused a 4GL compilation failure.
>
> > I can see where you are coming from, but we've successfully dbexported
> > it earlier this year and the table 'tigs' was present with the same
> > column name (it was created in 2004) so I suspect that's not the
> > problem. :( (wish it was - drop the table, dbexport, recreate &
> > reload the table).
>
> And to update, I've just successfully exported the other database (in
> a separate instance) which also contained 'tigs'...
And further still a second attempt succeeded! The engine hasn't been
restarted, it was just one of those horribly mysterious things that I
hate happening.
Cats wrote:
>> And to update, I've just successfully exported the other database (in
>> a separate instance) which also contained 'tigs'...
>
> And further still a second attempt succeeded! The engine hasn't been
> restarted, it was just one of those horribly mysterious things that I
> hate happening.
>
Just a wild guess... is there any chance that you have tried to use a dbexport
from another IDS version?
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
On Aug 17, 10:09 pm, Fernando Nunes <s...@domus.online.pt> wrote:
> Cats wrote:
> >> And to update, I've just successfully exported the other database (in
> >> a separate instance) which also contained 'tigs'...
>
> > And further still a second attempt succeeded! The engine hasn't been
> > restarted, it was just one of those horribly mysterious things that I
> > hate happening.
>
> Just a wild guess... is there any chance that you have tried to use a dbexport
> from another IDS version?
No, two databases but just one INFORMIXDIR on the box.
Cats wrote:
> Have had the following error exporting a database (using -ss into a
> directory):
>
> create table "me".tigs
> (
> seq serial not null ,
> name char(30),
> address char(40),
> loc char(20),
> pr1 money(16,2),
> pr2 money(16,2),
> pr3 money(16,2),
> pr4 money(16,2),
> datetime char(20),
> ref char(20),
> inhref char(20),
> code char(10),
> text char(20)
> );
> revoke all on "me".tigs from "public";>
> *** prepare unldobj
> 201 - A syntax error has occurred.
>
> Tried:
> dbschema -t all -s all -p all -f all -d mydb -ss schema.sql>
> This works fine, was exporting the database in case upgrading Informix
> caused a problem.
>
> Current version is 7.31.UD6 and it's running on Solaris 2.8. TAPEDEV
> and LTAPEDEV are both set to /dev/null, there is bags of free room in
> the file system I was exporting to, and it's not the 2GB problem - had
> that one earlier, unloaded the table in two parts & dropped &
> recreated it.
>
> Any ideas, please?
The column name 'ref' is definitely the problem. I think later versions
of IDS are OK with this as I used to get this problem a lot in our
databases but the column is still there and I don't any longer.
This may or may not work for you:
Run SQL statement: rename column tigs.ref to ref1;
Do the dbexport
Edit the file and replace ref1 with ref - dbimport doesn't mind 'ref'.
Correct the original database with: rename column tigs.ref1 to ref;
Regards, Ben.
On Aug 20, 8:36 am, Ben Thompson <b...@nomonitorsoftspam.com> wrote:
> Cats wrote:
> > Have had the following error exporting a database (using -ss into a
> > directory):
>
> > create table "me".tigs
> > (
> > seq serial not null ,
> > name char(30),
> > address char(40),
> > loc char(20),
> > pr1 money(16,2),
> > pr2 money(16,2),
> > pr3 money(16,2),
> > pr4 money(16,2),
> > datetime char(20),
> > ref char(20),
> > inhref char(20),
> > code char(10),
> > text char(20)
> > );
> > revoke all on "me".tigs from "public";>
> > *** prepare unldobj
> > 201 - A syntax error has occurred.
>
> > Tried:
> > dbschema -t all -s all -p all -f all -d mydb -ss schema.sql>
> > This works fine, was exporting the database in case upgrading Informix
> > caused a problem.
>
> > Current version is 7.31.UD6 and it's running on Solaris 2.8. TAPEDEV
> > and LTAPEDEV are both set to /dev/null, there is bags of free room in
> > the file system I was exporting to, and it's not the 2GB problem - had
> > that one earlier, unloaded the table in two parts & dropped &
> > recreated it.
>
> > Any ideas, please?
>
> The column name 'ref' is definitely the problem. I think later versions
> of IDS are OK with this as I used to get this problem a lot in our
> databases but the column is still there and I don't any longer.
>
> This may or may not work for you:
>
> Run SQL statement: rename column tigs.ref to ref1;
> Do the dbexport
> Edit the file and replace ref1 with ref - dbimport doesn't mind 'ref'.
> Correct the original database with: rename column tigs.ref1 to ref;
>
> Regards, Ben.
Ben, not sure how your theory squares with my now having managed to
export two databases containing tigs.ref. My own theory now is a
transient disk problem.