Archives of the TeradataForum

Message Posted: Mon, 06 Dec 2004 @ 17:17:33 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

We've used Join Indices for a number of years now. Clay Barrineau can probably quote bible and verse from an application standpoint.

Is it possible that you archived and restored the database? I haven't seen this error otherwise.

If so, then you are stuck. I have raised this issue for several years now. When you archive a table that participates in a join Index and retore it, one of two errors occurs:

1. Databse level Archive/Restore - you get that 2641 error when collecting statistics. Some flag in the table header is never cleared out. The fix is to recreate the table, copy over the data , dorp the old table, and rename the new on. Unfortunately, it is impossible to tell from the restored database that the join index was ever on that table. When asked, NCR suggested dropping the join indices before the backup and restoring them once the backup had finished. We started using a process that loads a table with all database/tablenames that have ever had a join index associated (dbc.indices.indextype) with them. This table gets carried ocver to DR and our procedures are to rebuild those tables. We require all join indices to be built through Macros (we could also use Stored Procedures now). This way, you never lose the definition.

2. Table Level Archive/Restore. "DON'T DO THIS", I do not remember the error that you get. I seem to remember that you could only access the data through an ACCESS LOCK. We couldn't drop or rename the table. Deleting the databse didn't work and of course you can't drop the database. The GSC had to drop the original table for us.

One other thing to remember about Join Indices. Drop the join index before deleting data from the table. It takes forever to delete data from a table with a Join Index. It takes even longer to rollback (forever squared) if you abort the delete.

Those are the only issues (from a DBA standpoint) that we have encountered with Join Indices.

Glen

	<Prev	Next>		<<First	<Prev	Next>	Last>>