I was attempting to activate indexes using the "idxbuild" option. This appears to be the only approach that performs well when dealing with large tables. Here is the KB : https://knowledgebase.progress.com/articles/Article/000041377
Adding an index normally takes hours but this takes minutes. It seems to make the most sense for a busy dba, as long as they are willing to re-establish OE replication afterwards.
The only problem is that I get a weird error on my first attempt. See below.
It says "Schema cache key, xref, or key field count incorrect". If I simply re-run the command again then it works the second time (and all subsequent times after that).
I was able to recreate the issue on a different copy of the same database. The first time the command fails with a scary message, but every time after that it works.
These are old databases (initially created pre-version-9) so I'm assuming there is some cruft in the table that needs to be repaired. Despite the fact that everything comes out OK in the end, it concerns me that the failed attempts to do idxbuilds are actually modifying the database in some way - even though they say they are failing. I do see that the first execution of the command says it is truncating BI, but the failure happened after that point in the command output ... so I assume there is yet another change that was made in conjunction with the display of the error?!?
Is anyone familiar with that particular error message or how to decipher it? I suppose it's probably not something I should dwell on, I've seen scarier messages from OE that didn't really end up meaning anything.
I've never seen a (996) error when building indexes. There are a couple of KB articles related to this error, one about adding a default index and the other is specific to Progress 9.x. Neither one is applicable to your situation so I'd suggest opening a case with TS.
On an unrelated note, I'd also suggest adding -pfactor 80 to your parameters as the default of 100 is too high. It will result in a lot of post-build block splits as you do creates and updates.
Yes, I'll open with TS. Thanks for the feedback. I did find the KB articles too, but they didn't seem to apply. I'm wondering if there is some health checkup option in proutil that will verify the state of the database (that all the upgrades over the years were done "cleanly").
We should probably just dump and reload our data but we got out of the habit of that (for better or worse) after moving to type 2 storage areas.
> I'm wondering if there is some health checkup option in proutil that will verify the state of the database (that all the upgrades over the years were done "cleanly").
If you do proutil db -C updatevst and -C updateschema, that will ensure any previously missed system updates have been applied.
You can also try proutil db -C idxfix, which gives you several options. I'd suggest running it on a test DB to get a feel for what it does and how long it takes on your DB/hardware.
OpenEdge Release 11.7.5 as of Fri Jun 7 08:29:03 EDT 2019
Index Fix Utility
1. Scan records for missing index entries with index block validation.
2. Scan indexes for invalid index entries.
3. Both 1 and 2 above.
4. Cross-reference check of multiple indexes for a table.
5. Build indexes from existing indexes.
6. Delete one record and its index entries.
8. Scan records for missing index entries.
q. Quit.
Enter selection:
If you want to improve index utilization, you can run proutil db -C idxcompact, online or offline. A dump and load is likely worthwhile if it has been many years since your last one and if you can afford the downtime.
> I was attempting to activate indexes using the "idxbuild" option.
Were there any active indexes of the table?
The error 996 seemed to be rised before any index was deactivated by idxbuild, in other words, before idxbuild read any index keys or records of the table.
>> On an unrelated note, I'd also suggest adding -pfactor 80 to your parameters as the default of 100 is too high.
Thanks this is very helpful. I'm not that familiar with idxbuild but I like the performance of it.
>> Were there any active indexes of the table?
Yes, there were several other active indexes. I am using "idxbuild" to create new indexes on a large table. For large tables it seems to be much faster than any other alternative approaches.
I reported the issue to Progress tech support and hopefully they will know what that means. I'll be sure to follow up here.