syntax error when running dbschema

[DAW...@mi.usxchange.net]

I am using Informix-SQL 5.1.UD2. When I run the dbschema utility
("dbschema -t all -d mydatabase /tmp/dbschema.txt") the program ends
with the error message "A syntax error has occurred." It writes the
dbschema for roughly one third of the tables in the database. I would
run dbschema on each table to find which is producing the error but
there are more than two hundred. Unfortunately, dbschema does not
output the table information alphabetically or I would simply scratch
table names off a list until I got to the bad apple. What causes this
to occur? Am I off-track assuming it is a problem with a table? I am
running the utility as user "informix." I get the same error when
running the utility as "root."

[Sean Kelsey]

I have had a problem in the past using 'dbschema -t all' as it didn't
produce a list of all of the tables. There can be instances when Informix
has references to a table that doesn't actually exist !!

Instead, I wrote a series of SQL scripts the generated a shell script of
dbschema commands for each table within the database ( except system catalog
tables ). The script goes something like:-

output to "<script_name.sh>"
select "dbschema -d mydatabase -t ", tabname||" > "||tabname||".sql;"
from systables
where tabid > 100
order by 2;

then just run the <script_name.sh> script which creates the appropriate sql
files for every table. Obviously, substitute whatever SQL script file you
want the output to go to for each table.

The error should then just create an empty sql file and identify the
erroneous table.

Hope this helps.

Sean

DAW...@mi.usxchange.net wrote in message
<366980F9...@mi.usxchange.net>...

[June Tong]

> I am using Informix-SQL 5.1.UD2. When I run the dbschema utility
> ("dbschema -t all -d mydatabase /tmp/dbschema.txt") the program ends
> with the error message "A syntax error has occurred." It writes the
> dbschema for roughly one third of the tables in the database. I would
> run dbschema on each table to find which is producing the error but
> there are more than two hundred. Unfortunately, dbschema does not
> output the table information alphabetically or I would simply scratch
> table names off a list until I got to the bad apple.

Don't know what your problem is, but I think it outputs the tables in
order of tabid.

June
--
jun...@hotmail.com
Grounded in Palo Alto, living on chocolate chip cookies

[Steve Williamson]

It may not be neat but it works in Informix SE 4.*. Load isql and:

update statistics

Wait for SQL to report the (first) missing table. Repeat until done.

In article <36707AF1...@hotmail.com>, June Tong
<jun...@hotmail.com> writes

>> I am using Informix-SQL 5.1.UD2. When I run the dbschema utility
>> ("dbschema -t all -d mydatabase /tmp/dbschema.txt") the program ends
>> with the error message "A syntax error has occurred." It writes the
>> dbschema for roughly one third of the tables in the database. I would
>> run dbschema on each table to find which is producing the error but
>> there are more than two hundred. Unfortunately, dbschema does not
>> output the table information alphabetically or I would simply scratch
>> table names off a list until I got to the bad apple.
>

--
Steve Williamson

[Bill Marshall]

I have not written it, but it seems you could write a select against
systables then
put it trough a foreach cursor and then write a text file which would be a db
schema in the order you want.

Just a thought

[Jon Strayer]

Bill Marshall wrote:
>
> I have not written it, but it seems you could write a select against
> systables then
> put it trough a foreach cursor and then write a text file which would be a db
> schema in the order you want.
>
> Just a thought

There is a 4gl example program in the documemtation that does just
that. Not that I can remember the name or anything.

Jon Strayer