Re: Corrupt record in sysmaster: systabnames

On Fri, 14 Nov 2003 20:59:32 -0500, Informix User wrote: > Hello, > > Please advise: > > We are having a very strange problem that is causing dbexport to fail. > DBEXPORT always fails when extracting a schema for a particular table. The > error message > is: > > > ================= > table definition is OK ....(Not shown here ) > > revoke all on "informix".dx_accountSumm from "public";> > -284 - A subquery has returned not exactly one row. ====================== > > When looking at the systmaster.systabnames there are two records with the same > tablename, same owner name and same database. We have no idea why or how. We > are however able to update/insert records into this table properly. The error > occurs only during dbexport causing the dbexport process to abort with > finishing the rest of the tables. It stops after exporting 87 out 115 tables. > > Can I simply delete the record from systabnames that points to the > non-existent table using partnum. NNNNOOOOOOO!!!!!!!! The systabnames SMI table is a view into the tablespace tablespace records on disk and not a real table! I'm not sure the engine would even let you delete a 'row' from that 'table' but you REALLY should not do so. You can try to get tech support to poke in and change the tablename on the tablespace tablespace page. Then I'd dbexport the whole database, drop it, and recreate it clean. You COULD try my dbexport replacement utility myexport which uses different logic to locate the catalog information and so may not get confused (no guarantee). Myexport is its own package in the IIUG Software Repository but also requires my dbschema replacement utility myschema from the utils2_ak package and the sqlunload and sqlreload utilities from Jonathan Leffler's sqlcmd package. Both of these are also available for download from the Repository. Art S. Kagel