Re: Can't do 'UPDATE STATISTICS' bec. of corrupted 'table'

->From: john@gagme.wwa.com (John T. Fontanilla) ->Subject: Can't do 'UPDATE STATISTICS' bec. of corrupted 'table' ->Date: 30 Aug 1994 15:30:52 -0500 ->Reply-To: john@gagme.wwa.com (John T. Fontanilla) ->Organization: WorldWide Access - Chicago Area Internet Services 312-282-8605 708-367-1871 -> ->Hi, -> Hardware: AT&T 6386 computer running AT&T Unix SysV3.2 rel. 2.3 -> isql -v: 2.10.00B -> i4gl -v: 1.10.00B/2.10.00B -> c4gl -v: 2.10.00B -> sqlexec -V: 4.00.UH1 -> -> Problem: Cannot 'update statistics' on the database bec. of corrupted -> system/database table(s). -> ... details omitted ... -> -> Error message at bottom of screen: 'Cannot create sysindexes for -> d10____176'. ... more details gone ... -> -> Possible solution: -> -> 1. Unload d10 to flat file; Drop d10; reload d10; recreate indexes on -> d10. -> 2. Unload all database files; Drop database; Recreate database; -> Recreate tables; Load data (I don't like this as there are *LOT* of -> tables and we don't have the disk space) -> 3. Delete some rows in sysindexes (Kludge but I really don't want to -> do this bec. I'm not sure how it is formatted or if I still have -> to remove some records in other sys-tables) -> -> So my question is, can you suggest a way that can restore the integrity -> of the database without taking up much time? -> -> THANKS! -> ->John Hello, John, I also use some old software (isql 2.10.03F, sqlexec 2.10.03K) so I would expect my techniques to work for you, too. It looks like your best bet is a combination of solution 1 and solution 3, done with care, of course. First, unload d10, as a precaution. If the next few steps all work, then you don't need the unload file. However, ... As the owner of the database, or any other user with DBA privileges, you can manipulate the sys-tables like any other, once you understand their structure. To become familiar with the system catalogs, you can use the Info option in ISQL. Although the catalog table names will not be listed, you can type them in and then do normal queries using the Columns, Indexes, Privileges, and Status options. You can also use normal SQL to display the contents of the system catalogs. You may find this query useful for your current problem: select idxname, i.owner, tabname, t.owner, dirpath[1,20] from sysindexes i, outer systables t where i.tabid = t.tabid This query should show whether you have some incorrect indexes hanging around for table "d10". Deleting the offending rows in sysindexes should clear up your problem. First try to use the DROP INDEX command to drop the indexes found for d10. If that does not work, try: delete from sysindexes where idxname = "whatever" {found from select above} (BTW, although sysindexes references systables and syscolumns, nothing else references sysindexes.) At this point, 'update statistics for table d10' should work. If not, then drop table d10 and reload it using the data unloaded above. If d10 is not very large, then unload, drop, recreate, reload may actually be faster. However, "fiddling" (carefully!) with the system catalogs has let me avoid this process for some very large tables. Regards, and good luck! Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, SLS | / \\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\