ERKEY creation location
Mark Scranton asked how to place the three indexes created by ALTER TABLE ADD ERKEY on 12.10.FC12W1 in other dbspaces, since they follow the table fragments. Paul Watson suggested creating the shadow columns and indexes by hand and changing coltypes in syscolumns. Tom Girsch suggested renaming the indexes and moving them with ALTER FRAGMENT. Paul said that would be a slow alter, whereas his way is an in-place alter.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Playing with ALTER TABLE ... add ERKEY (s) and it appears that the 3 new indexes creations follow the table fragments. I haven't found any keywords letting me specify other dbspaces. One issue of course ... data dbspace page size typically default; some/many index dbspace page sizes are larger and much more efficient/smaller b-trees/less pages/easier tracking, etc. I suppose I COULD do an alter and move the new index to a different dbspace after creation but we're considering doing this with a large number of tables so mundane. Still experimenting, but does anyone have any thoughts (random or otherwise) on this topic? I'm on 12.10.FC12W1. Thanks! Mark Scranton mark@markscranton.com ------------------------------ Mark Scranton ------------------------------ #Informix
Create the columns by hand, add the relevant indices where/how you want, then populate then change the coltypes in syscolumns. Make sure they are called as per 'real' ERKeys Cheers Paul
I've not personally tested this, but I belive you can just let it create the indexes where it wants, rename them, and then move them with ALTER FRAGMENT. That way you're not having to directly muck with systables. [At least, I _think_ the ability to rename system-created indexes was around by 12.10.FC12; just ignore the leading space in the index name when issuing the rename statement] Tom Girsch Lead System Architect Auto Europe Group tgirsch@autoeurope.com Office #207-842-2139 " If you think there is something more important than a Client ... think again "
That will/should work but the adding the ERKey will be a slow alter, my approach is an IPA Cheers Paul Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC
The approach works with replcheck as well although there is no need to populate if you don't want. Not tested, but I suspect it will work with other ER cols The big advantage is it makes it it all an IPA Cheers Paul Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC