Re: tblspace does not exist

In <22jhelINN95c@emory.mathcs.emory.edu> inf_bb@hermes1.sps.mot.com (informix users bb) writes: >sebastian, >NOTE: any and all disclaimers apply (naomi, correct me if i get this wrong). >you can get rid of this problem table by (call it tab1 for this example): >1. create a table with the exact schema as tab1 called tab2 >2. get the tabids and partnumbers for tab1 and tab2 > select tabname,partnum,tabid > from systables > where tabname in ('tab1','tab2') > into temp t1;>3. switch the partnumbers for tab1 and tab2 > update systables > set (partnum)=(select partnum from t1 where tabname = 'tab2') > where tabname = 'tab1'; > update systables > set (partnum)=(select partnum from t1 where tabname = 'tab1') > where tabname = 'tab2'; >4. drop table tab1 > drop table tab1;>5. delete from systables, syscolumns, systabauth, etc where tabid = tabid of > tab2. > delete > from systables > where tabid = (select tabid from t1 where tabname = 'tab2'); > delete > from syscolumns > where tabid = (select tabid from t1 where tabname = 'tab2'); > delete > from sysindexes > where tabid = (select tabid from t1 where tabname = 'tab2'); > delete > from systabauth > where tabid = (select tabid from t1 where tabname = 'tab2'); > etc.... >this should work and you should not have to restore from tape. >further investigate the program(s) that create/update/alter this table >to determine what is causing the error. I can't see how/why this would work. You've replaced/refreshed the syscatalogs but you've still got the old tablespace tablespace entry. If the problem was in the catalogs, wouldn't it have been better to update the offending columns directly? Isn't it more likely that the problem is in the tablespace tablespace? If this procedure does work, shouldn't it be used to compare syscatalogs before and after to find out what is different? >AGAIN : any and all disclaimers apply. I'm not surprised! >regards, >------------------------------------------------------------------------------ > | . . > Bob Baskett | ... ... > Software Engineer, DBA | ..... ..... > Business Systems Integration Group | .. ... .. > Semiconductor Products Sector | . . . > Mesa, AZ | > | Motorola, Inc >------------------------------------------------------------------------------ >Sun 690-MP >Informix Versions: > Online - 5.00.UC2 > ISQL - 4.10.UD1 > 4GL-RDS- 4.10.UC1 >------------------------------------------------------------------------------ graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.