Informix

 View Only
  • 1.  ERKEY creation location

    Posted Tue January 04, 2022 02:58 PM

    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
    ------------------------------


  • 2.  RE: ERKEY creation location

    Posted Tue January 04, 2022 03:07 PM

    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

     






  • 3.  RE: ERKEY creation location

    IBM Champion
    Posted Wed January 05, 2022 08:44 AM

    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

    image003.jpg@01D5C181.C81D19F0

    " If you think there is something more important than a Client ... think again "

     






  • 4.  RE: ERKEY creation location

    Posted Wed January 05, 2022 08:52 AM
    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





  • 5.  RE: ERKEY creation location

    Posted Wed January 05, 2022 08:54 AM
    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

    On Jan 5, 2022, at 07:51, Paul Watson <paul@oninit.com> wrote:

    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