sysmaster/systabnames not refreshing

Hi all, We have a strange situation here. Out admins constantly are monitoring the systabnames table for tables in the instances with many extents. We ran into the extent warning a couple of days ago for a temporary database which was not created correctly, resulting in a table with > 150 extents. The database has been dropped in the meantime, but the check still finds the table record in systabnames. dbname _tmp_xxxx_xxxx_rxxxxxos tabname _temptable num_extents 166 For some reason, the data is not erased, but the database is dropped. There could be an issue with the underscore in table name or database name from my point of view. Anybody encountered this ? What can we do (other than recreating sysmaster ?) IDS 14.10FC3 on Linux x86 MARCUS HAARMANN

There are some situations where temp tables are not physically deleted from storage when they are dropped. In those cases bouncing the engine will cause the engine to clean them up during fast recovery. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Recreating sysmaster most likely will not change much in this picture, as this information you're seeing there is not "stored in sysmaster" - sysmaster only provides the interface for looking at parts of the instance. From its name, it looks like a real temp table, created by a sessions working in this database, and somehow not cleaned up after the session completed - or does the session possibly still exist, is there a session ID in sysactptnhdr.sid for this table? As Art mentioned, this would be cleaned up at next instance start. If not a temp table: Can we assume that these 166 extents will also appear in an 'oncheck -pe' extent listing, under that same name? Can we further assume 'oncheck -pt <partnum>' will not show a valid name for it, i.e. show it, yet without a name and instead "Unknown:Inknown.<partnum>"? I.e. its partnum can't be found in any database ... What would be the table's creation date and flags in this output? If the table survives an engine restart and doesn't belong any more to any database, then it likely got orphaned, and probably already before that database got dropped. HTH, Andreas ------------------------------ Andreas Legner ------------------------------

Andreas, thank you for your input. oncheck -pt shows indeed an unknown:unknown location.and gives an ISAM error: no record found. It was created recently (Jan 26, 09:42) and seems to be a real table and not a temp table. According to the sysmaster entry, it belonged to a database which was already dropped in the meantime. I contacted the guy who probably constructed the database at Jan 24. It was a simple dbimport, but the schema does not contain such a table, The interesting thing I found here is that the dbimport was obviously done with a dbimport 12.10 binary. Can it be that such a table is created while dbimport is running (or maybe in the old version) ? MARCUS HAARMANN

So what you're describing sounds like, despite its name, this is the partition of a regular table created in said database, yet well after the dbimport. The only logical explanation for such partition surviving the drop of the containing database, in my view, is: it wasn't part of the database's catalog, namely systables/sysfragments, at the time of "drop database" which in turn would indicate a manual deletion. The logical logs between the partition's create date/time and database drop probably would contain what's left of the truth. BR, Andreas ------------------------------ Andreas Legner ------------------------------