DBEXPORT failed with *** prepare unldobj & 201 - A syntax error
--------------------------------------------
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 = 875 number of columns = 16 index size =
204 }
{ unload file name = sysco00773.unl number of rows = 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 ?
2. Is it safe to drop this system table ?
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 ?
4. If dropping is not safe ? can some one pl. help me out.
TIA.
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 = 875 number of columns = 16 index size =
> 204 }
> { unload file name = sysco00773.unl number of rows = 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 ?
A bug in the dbexport internal function that actually reads data from a
table and outputs to the unload file for that table. Looks like something
in the table definition triggered a code generation bug internally.
> 2. Is it safe to drop this system table ?
NO! Never mess with system catalog tables. There are VERY FEW things you
can do safely to catalog tables and only some of them at that. Dropping one
is not one of the safe things for any catalog table.
> 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 ?
No.
> 4. If dropping is not safe ? can some one pl. help me out.
Two options (or do both):
1- Contact IBM and see if upgrading to 9.40UC5 or later will solve the
problem, UC3 is not the latest.
2- Get my dbexport replacement utility, myexport. Its output is completely
compatible with dbimport and it provides its own compatible myimport script
as well. To use myexport/myimport you need three packages from the IIUG
Software Repository: myexport, utils2_ak (for myschema), and sqlcmd (for
sqlunload and sqlreload). Besides not choking on your DB, myexport has some
neat extra features like being able to unload/load using the iploader which
is MUCH faster then dbexport/dbimport anyway. If you try this and have any
problems building the myschema, let me know.
Art S. Kagel
> TIA.
Art: I'm not convinced that this is a system catalog.
--
Regards,
Doug Lawry
www.douglawry.webhop.org
"Art S. Kagel" <kagel@bloomberg.net> wrote in message
news:41D970A8.1080004@bloomberg.net...
> 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 = 875 number of columns = 16 index
size =
> > 204 }
> > { unload file name = sysco00773.unl number of rows = 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 ?
>
> A bug in the dbexport internal function that actually reads data from a
> table and outputs to the unload file for that table. Looks like something
> in the table definition triggered a code generation bug internally.
>
> > 2. Is it safe to drop this system table ?
>
> NO! Never mess with system catalog tables. There are VERY FEW things you
> can do safely to catalog tables and only some of them at that. Dropping one
> is not one of the safe things for any catalog table.
>
> > 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 ?
>
> No.
>
> > 4. If dropping is not safe ? can some one pl. help me out.
>
> Two options (or do both):
>
> 1- Contact IBM and see if upgrading to 9.40UC5 or later will solve the
> problem, UC3 is not the latest.
>
> 2- Get my dbexport replacement utility, myexport. Its output is completely
> compatible with dbimport and it provides its own compatible myimport script
> as well. To use myexport/myimport you need three packages from the IIUG
> Software Repository: myexport, utils2_ak (for myschema), and sqlcmd (for
> sqlunload and sqlreload). Besides not choking on your DB, myexport has some
> neat extra features like being able to unload/load using the iploader which
> is MUCH faster then dbexport/dbimport anyway. If you try this and have any
> problems building the myschema, let me know.
>
> Art S. Kagel
>
>
> > TIA.
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 = 875 number of columns = 16 index size =
> 204 }
> { unload file name = sysco00773.unl number of rows = 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 if
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 it
was. Dbimport has no problems with reserved words.
Ben.