Re: DBEXPORT failed with *** prepare unldobj & 201 - A syntax error

#1 (**permalink**) 04-20-2008, 07:22 AM

--0__=09BBE512DFF2022A8f9e8a93df938690918c09BBE512DF F2022A
Content-type: multipart/alternative;
Boundary="1__=09BBE512DFF2022A8f9e8a93df938690918c 09BBE512DFF2022A"

--1__=09BBE512DFF2022A8f9e8a93df938690918c09BBE512DF F2022A
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: quoted-printable

Hi,

9.40.UC7 on Solaris

create database checkdb;
create table tab1(case int);
create table tab2(ref int);

I did a dbexport, dropped the database and did dbimport. No problems. I=
do
not believe that such problem can be platform specific so it would be f=
are
to say that the problem has been fixed in latest version.

Thanks and Regards,
Gaurav

=

Ben Thompson =

<ben@nomonitorsof =

tspam.com> =
To
Sent by: informix-list@iiug.org =

owner-informix-li =
cc
st@iiug.org =

Subj=
ect
Re: DBEXPORT failed with *** =

01/04/2005 06:18 prepare unldobj & 201 - A syntax=

AM error =

=

=

Please respond to =

Ben Thompson =

=

=

Hari Gupta wrote:
> --------------------------------------------
> A VERY HAPPY NEW YEAR - 2005 TO ALL OF YOU
> --------------------------------------------
>
> IDS 9.40.UC3
> RHL 7.3
>
> dbexport is failing over with following error:
>
>
-----------------------------------------------------------------------=
--------

> { TABLE "auth".syscolformats row size =3D 875 number of columns =3D 1=
6 index
size =3D
> 204 }
> { unload file name =3D sysco00773.unl number of rows =3D 0 }
>
> create table "auth".syscolformats
> (
> owner char(32),
> tabname char(32),
> colname char(32),
> extowner char(32),
> priority smallint,
> typeface char(64),
> fontsize smallint,
> fontstyle smallint,
> fontcolor integer,
> aidfa char(30),
> formatmask char(256),
> format4gl char(128),
> align char(1),
> case char(1),
> ruletype char(1),
> checktext char(256)
> ) extent size 16 next size 16 lock mode row;
> revoke all on "auth".syscolformats from "public";
>
> *** prepare unldobj
> 201 - A syntax error has occurred.
>
-----------------------------------------------------------------------=
-------

>
> dbexport by:
>
> dbexport -d <database name> -ss
>
> - There is no row in this table (syscolformats).
> - Last night onchecks did not report any thing unusual.
>
> Question:
>
> 1. Root cause of this error and can it be prevented ?

I have reproduced your problem by creating a test database and creating=

this table in it. The problem is because you have a column called 'case=
'
which appears to be a reserved word. There is a bug in dbexport where i=
f
there is a column name that is a reserved word it will fail like this. =
A
favourite of mine is 'ref' - if you have a column of this name it will
fail as well.

I don't think I have raised this bug with technical support as I can
work around it (see below) but if anyone is listening and would like to=

fix it, then it's easy to reproduce and I would be grateful.

> 2. Is it safe to drop this system table ?

Is this a system table? I know its name starts with 'sys' but it doesn'=
t
exist in any database I have. Can anyone else verify this?

> 3. If yes, should it be dropped first and continue dbexport; recreate=
it
after
> dbexport is over ?
> OR
> Drop and recreate the table and then start dbexport ?

Rename the column case to case1 as follows, provided you are sure this
is NOT a system table - do not take my word for it:
rename column syscolformats.case to case1;
Do the dbexport.
Rename it back using a similar SQL statement:
rename column syscolformats.case1 to case;
Edit the dbexport unload file and change the column name back to what i=
t
was. Dbimport has no problems with reserved words.

Ben.
=

--1__=09BBE512DFF2022A8f9e8a93df938690918c09BBE512DF F2022A
Content-type: text/html; charset=US-ASCII
Content-Disposition: inline
Content-transfer-encoding: quoted-printable

<html><body>
Hi, 
 
9.40.UC7 on Solaris 
 
create database checkdb; 
create table tab1(case int); 
create table tab2(ref int); 
 
I did a dbexport, dropped the database and did dbimport. No problems. I=
do not believe that such problem can be platform specific so it would =
be fare to say that the problem has been fixed in latest version. 
 
Thanks and Regards, 
Gaurav 
<img src=3D"cid:10__=3D09BBE512DFF2022A8f9e8a93df938@us .ibm.com" width=3D=
"16" height=3D"16" alt=3D"Inactive hide details for Ben Thompson <be=
n@nomonitorsoftspam.com>">Ben Thompson <ben@nomonitorsoftspam.com=
> 
 
 

<table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">=

<tr valign=3D"top"><td style=3D"background-image:url(cid:20__=3D09BBE51=
2DFF2022A8f9e8a93df938@us.ibm.com); background-repeat: no-repeat; " wid=
th=3D"40%">
<ul>
<ul>
<ul>
<ul>Ben Thompson <ben@nomonitorsoftspam.com><=
/font> 
Sent by: owner-informix-list@iiug.org
01/04/2005 06:18 AM
<table border=3D"1">
<tr valign=3D"top"><td width=3D"168" bgcolor=3D"#FFFFFF"><div align=3D"=
center">Please respond to 
Ben Thompson</div></td></tr>
</table>
</ul>
</ul>
</ul>
</ul>
</td><td width=3D"60%">
<table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">=

<tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3=
0__=3D09BBE512DFF2022A8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"=
1" width=3D"58" alt=3D""> 
<div align=3D"right">To</div></td><td width=3D"=
100%"><img src=3D"cid:30__=3D09BBE512DFF2022A8f9e8a93df938@us .ibm.com" =
border=3D"0" height=3D"1" width=3D"1" alt=3D""> 
informix-list@iiug.org</td></tr>

<tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3=
0__=3D09BBE512DFF2022A8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"=
1" width=3D"58" alt=3D""> 
<div align=3D"right">cc</div></td><td width=3D"=
100%"><img src=3D"cid:30__=3D09BBE512DFF2022A8f9e8a93df938@us .ibm.com" =
border=3D"0" height=3D"1" width=3D"1" alt=3D""> 
</td></tr>

<tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3=
0__=3D09BBE512DFF2022A8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"=
1" width=3D"58" alt=3D""> 
<div align=3D"right">Subject</div></td><td widt=
h=3D"100%"><img src=3D"cid:30__=3D09BBE512DFF2022A8f9e8a93df938@us .ibm.=
com" border=3D"0" height=3D"1" width=3D"1" alt=3D""> 
Re: DBEXPORT failed with *** prepare unldobj & 20=
1 - A syntax error</td></tr>
</table>

<table border=3D"0" cellspacing=3D"0" cellpadding=3D"0">
<tr valign=3D"top"><td width=3D"58"><img src=3D"cid:30__=3D09BBE512DFF2=
022A8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt=
=3D""></td><td width=3D"336"><img src=3D"cid:30__=3D09BBE512DFF2022A8f9=
e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt=3D""><=
/td></tr>
</table>
</td></tr>
</table>
 
<tt>Hari Gupta wrote: 
> -------------------------------------------- 
> A VERY HAPPY NEW YEAR - 2005 TO ALL OF YOU 
> -------------------------------------------- 
> 
> IDS 9.40.UC3 
> RHL 7.3 
> 
> dbexport is failing over with following error: 
> 
> ------------------------------------------------------------------=
------------- 
> { TABLE "auth".syscolformats row size =3D 875 number of =
columns =3D 16 index size =3D 
> 204 } 
> { unload file name =3D sysco00773.unl number of rows =3D 0 } 
> 
> create table "auth".syscolformats 
>   ( 
>     owner char(32), 
>     tabname char(32), 
>     colname char(32), 
>     extowner char(32), 
>     priority smallint, 
>     typeface char(64), 
>     fontsize smallint, 
>     fontstyle smallint, 
>     fontcolor integer, 
>     aidfa char(30), 
>     formatmask char(256), 
>     format4gl char(128), 
>     align char(1), 
>     case char(1), 
>     ruletype char(1), 
>     checktext char(256) 
>   )  extent size 16 next size 16 lock mode row; 
> revoke all on "auth".syscolformats from "public&quo=
t;; 
> 
> *** prepare unldobj 
> 201 - A syntax error has occurred. 
> ------------------------------------------------------------------=
------------ 
>   
> dbexport by: 
> 
> dbexport -d <database name> -ss 
> 
> - There is no row in this table (syscolformats). 
> - Last night onchecks did not report any thing unusual. 
>     
> Question: 
> 
> 1. Root cause of this error and can it be prevented ? 
 
I have reproduced your problem by creating a test database and creating=
 
this table in it. The problem is because you have a column called 'case=
' 
which appears to be a reserved word. There is a bug in dbexport where i=
f 
there is a column name that is a reserved word it will fail like this. =
A 
favourite of mine is 'ref' - if you have a column of this name it will =
 
fail as well. 
 
I don't think I have raised this bug with technical support as I can <b=
r>
work around it (see below) but if anyone is listening and would like to=
 
fix it, then it's easy to reproduce and I would be grateful. 
 
> 2. Is it safe to drop this system table ? 
 
Is this a system table? I know its name starts with 'sys' but it doesn'=
t 
exist in any database I have. Can anyone else verify this? 
 
> 3. If yes, should it be dropped first and continue dbexport; recre=
ate it after 
>    dbexport is over  ? 
>    OR 
>    Drop and recreate the table and then start dbexport ?=
 
 
Rename the column case to case1 as follows, provided you are sure this =
 
is NOT a system table - do not take my word for it: 
rename column syscolformats.case to case1; 
Do the dbexport. 
Rename it back using a similar SQL statement: 
rename column syscolformats.case1 to case; 
Edit the dbexport unload file and change the column name back to what i=
t 
was. Dbimport has no problems with reserved words. 
 
Ben. 
</tt> 
</body></html>=

--1__=09BBE512DFF2022A8f9e8a93df938690918c09BBE512DF F2022A--

--0__=09BBE512DFF2022A8f9e8a93df938690918c09BBE512DF F2022A
Content-type: image/gif;
name="graycol.gif"
Content-Disposition: inline; filename="graycol.gif"
Content-ID: <10__=09BBE512DFF2022A8f9e8a93df938@us.ibm.com>
Content-transfer-encoding: base64

R0lGODlhEAAQAKECAMzMzAAAAP///wAAACH5BAEAAAIALAAAAAAQABAAAAIXlI+py+0PopwxUbpu
ZRfKZ2zgSJbmSRYAIf4fT3B0aW1pemVkIGJ5IFVsZWFkIFNtYX J0U2F2ZXIhAAA7

--0__=09BBE512DFF2022A8f9e8a93df938690918c09BBE512DF F2022A
Content-type: image/gif;
name="pic06350.gif"
Content-Disposition: inline; filename="pic06350.gif"
Content-ID: <20__=09BBE512DFF2022A8f9e8a93df938@us.ibm.com>
Content-transfer-encoding: base64

R0lGODlhWABDALP/AAAAAK04Qf79/o+Gm7WuwlNObwoJFCsoSMDAwGFsmIuezf///wAAAAAAAAAA
AAAAACH5BAEAAAgALAAAAABYAEMAQAT/EMlJq704682770RiFMRinqggEUNSHIchG0BCfHhOjAuh
EDeUqTASLCbBhQrhG7xis2j0lssNDopE4jfIJhDaggI8YB1sZe ZgLVA9YVCpnGagVjV171aRVrYR
RghXcAGFhoUETwYxcXNyADJ3GlcSKGAwLwllVC1vjIUHBWsFil KQdI8GA5IcpApeJQt8L09lmgkH
LZikoU5wjqcyAMMFrJIDPAKvCFletKSev1HBw8KrxtjZ2tvc3d 5VyKtCKW3jfz4uMKmq3xu4N0nK
BVoJQmx2LGVOmrqNjjJf2hHAQo/eDwJGTKhQMcgQEEAnEjFS98+RnW3smGkZU6ncCWav/4wYOnAI
TihRL/4FEwbp28BXMMcoscQCVxlepL4IGDSCyJyVQOu0o7CjmLN50OZl qWmyFy5/6yBBuji0AxFR
M00oQAqNIstqI6qKHUsWRAEAvagsmfUEAImyxgbmUpJk3IklNU tJOUAVLoUr1+wqDGTE4zk+T6FG
uQb3SizBCwatiiUgCBN8vrz+zFjVyQ8FWkOlg4NQiZMB5QS8QO 3mpOaKnL0Z2EKvNMSILEThKhCg
zMKPVxYJh23qm9KNW7pArPynMqZDiErsTMqI+LRi3QAgkFUbXp uFKhSYZALd0O5RKa2z9EYKBbpb
qxIKsjUPRgD7I2XYV6wyrOw92ykExP8NW4URhknC5dKGE4v4NE NQj2jXjmfNgOZDaXb5glRmXQ33
YEWQYNcZFnrYcIQLNzyTFDQNkXIff0ExVlY4srziQk43inZgL4 rwxxINMvpFFAz1KOODHiu+4aEw
NEjFl5B3JIKWKF3k6I9bfUGp5ZZcdunll5IA4cuHvQQJ5gcsoC WOOUwgltIwAKRxJgbIkJAQZEq0
2YliZnpZZ4BH3CnYOXldOUOfQoYDqF1LFHbXCrO8xmRsfoXDXJ 6ChjCAH3QlhJcT6VWE6FCkfCco
CgrMFsROrIEX3o2whVjWDjoJccN3LdggSGXLCdLEgHr1lyU3O3 QxhgohNKXJCWv8JQr/PDdaqd6w
2rj1inLiGeiCJoDspAoQlYE6QWLSECehcWIYxIQES6zhbn1iIm THEQyqJ4eIxJJoUBc+3CbBuwZE
V5cJPPkIjFDdeEabQbd6WgICTxiiz0f5dBKquXF6k4senwEhYG nKEFJeGrxUZy8dB8gmAXI/sPvH
ESfCwVt5hTgYiqQqtdRNHQIU1PJ33ZqmzgE90OwLaoJcnMop1W iMmgkPHQRIrwgFuNV90A3doNKT
mrKIN07AnGcI9BQjhCBN4RfA1qIZnMqorJCogKfGQnxSCDilTV IA0yl5ciTovgLuBDKFUDE9aQcw
9SA+rjSNf9/M1gxrj6VwDTS0IUSElMzBfsj0NFXR2kwsV1A5IF1grLgLL/r1R40BZEnuBWgmQEyb
jqRwSAt6bqMCOFkvKFN2GPPkUzIm/SCF8z8pVzpbjVnMsy0vOr1hw3SaSRUhpY09v0z0J1FnwzPl
fmh+xl4WtR0zGu24I4KbMQm3lnVu2oNWxI9W/lcyzA+mCKF4DBikxb/+UWtOGRiFP8qEwAayIgIA
Ow==

--0__=09BBE512DFF2022A8f9e8a93df938690918c09BBE512DF F2022A
Content-type: image/gif;
name="ecblank.gif"
Content-Disposition: inline; filename="ecblank.gif"
Content-ID: <30__=09BBE512DFF2022A8f9e8a93df938@us.ibm.com>
Content-transfer-encoding: base64

R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7

--0__=09BBE512DFF2022A8f9e8a93df938690918c09BBE512DF F2022A--

sending to informix-list

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode