Informix

 View Only
Expand all | Collapse all

How to detect if table has old-fashioned attached indexes (index pages in non-fragmented table partition)

  • 1.  How to detect if table has old-fashioned attached indexes (index pages in non-fragmented table partition)

    Posted Thu March 10, 2022 11:18 AM

    Hello,

    does any one knows, if there is a way how to detect tables with attached indexes (in old meaning – index pages are together with data pages in one partition).

    Curently system catalogs have such indexes, but we can configure such indexes for user tables via DEFAULT_ATTACH environment variables.

    Thank you.

     

    Obsah obrázku text, klipart Popis byl vytvořen automaticky

    Milan Rafaj

    www.kyndryl.com


    #Informix


  • 2.  RE: How to detect if table has old-fashioned attached indexes (index pages in non-fragmented table partition)

    IBM Champion
    Posted Thu March 10, 2022 12:02 PM
    oncheck -pT
    #Informix


  • 3.  RE: How to detect if table has old-fashioned attached indexes (index pages in non-fragmented table partition)

    Posted Thu March 10, 2022 12:09 PM
    Thank you Khaled,

    yes it is a way, but requires some parsing and takes long time. I am thinking about some sysmaster table queries.

    Regards,
    Milan

    ------------------------------
    Milan Rafaj
    Infrastructure Architects and Specialists Team Leader
    Kyndryl
    +420737264248
    ------------------------------

    Original Message


  • 4.  RE: How to detect if table has old-fashioned attached indexes (index pages in non-fragmented table partition)

    IBM Champion
    Posted Thu March 10, 2022 12:54 PM

    Milan:

    No problem. If the index has an entry in sysmaster:systabnames then it is detached. An attached index will be stored within the parent table's partition and not have an entry there. That's the easiest way. Example, the attached index on the systables table on tabid:

    > select * from systabnames where dbsname='art' and tabname = 'tabid';

    No rows found.

    Versus a detached index:
    > select * from systabnames where dbsname='sysmaster'  and tabname = 'flags_text_ix1';

    partnum  1049505
    dbsname  sysmaster
    owner    informix
    tabname  flags_text_ix1
    collate  en_US.819
    dbsnum   1

    1 row(s) retrieved.

    Within the database's catalog you can figure this out as well. If you query sysfragments for the parent table's tabid and a fragtype = 'I' and indexname = 'name-of-the-index' there will be no rows for an attached index while a detached index will have at least one sysfragments entry. Example, the attached index on the systables table on tabid:

    > select * from sysfragments where tabid = 1 and fragtype = 'I' and indexname = 'tabid';

    No rows found.

    Versus a detached index:
    > select *

    > from sysfragments

    > where tabid = 245 and fragtype = 'I' and indexname = 'flags_text_ix1';

    fragtype    I
    tabid       245
    indexname   flags_text_ix1
    colno       0
    partn       1049505
    strategy    I
    location    L
    servername   
    evalpos     0
    exprtext     
    exprbin     <BYTE value>
    exprarr     <BYTE value>
    flags       0
    dbspace     rootdbs
    levels      3
    npused      42.00000000000
    nrows       17.00000000000
    clust       27.00000000000
    partition   rootdbs
    version     1
    nupdates    0.00
    ndeletes    0.00
    ninserts    343.0000000000

    1 row(s) retrieved.






    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 5.  RE: How to detect if table has old-fashioned attached indexes (index pages in non-fragmented table partition)

    IBM Champion
    Posted Thu March 10, 2022 12:59 PM
    Oh, also, if you have myschema, it will print out attached indexes with the storage clause "IN TABLE" when you report on its parent table:

    CREATE UNIQUE INDEX "informix".tabname ON "informix".systables (
           tabname ASC,
           owner ASC
    ) USING btree IN TABLE;





    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 6.  RE: How to detect if table has old-fashioned attached indexes (index pages in non-fragmented table partition)

    IBM Champion
    Posted Thu March 10, 2022 01:25 PM

    To find all attached indexes in a database besides those on the catalog tables, use this query:

    select tabname, idxname
    from systables st
    join sysindexes si
      on st.tabid = si.tabid
    left outer join sysfragments sf
      on st.tabid = sf.tabid
       and sf.fragtype = 'I'
     where sf.tabid is null
       and st.tabid > 99;

    tabname  no_privs
    idxname  no_privs_ak1

    1 row(s) retrieved.

    So this table:

    $ myschema -d art -t no_privs -q
    CREATE TABLE "art".no_privs (
           one SERIAL(1) NOT NULL
    ) IN datadbs_1 EXTENT SIZE 16 NEXT SIZE 16 LOCK MODE PAGE;

    {
           Please review extent sizing and adjust to allow for growth.
    }

    REVOKE ALL ON "art".no_privs FROM public AS "art";
    CREATE INDEX "art".no_privs_ak1 ON "art".no_privs (
           one ASC
    ) USING btree IN TABLE;





    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 7.  RE: How to detect if table has old-fashioned attached indexes (index pages in non-fragmented table partition)

    Posted Fri March 11, 2022 11:18 AM

    Thank you Art,

    So trick with sysfragments works both for catalogs and user tables.

    Can I have one more  question? Step further – is there an easy and quick way how to get number of index pages for attached index to avoid using oncheck -pT?

    An idea is to get list all indexes for all tables ordered by number of index pages

    System-rowid indexes I can get from sysfragments as well – as they are not listed in sysindexes – all are named system-rowid.

     

    Regards,

    Obsah obrázku text, klipart Popis byl vytvořen automaticky

    Milan Rafaj

    www.kyndryl.com

     

     




    Original Message


  • 8.  RE: How to detect if table has old-fashioned attached indexes (index pages in non-fragmented table partition)

    IBM Champion
    Posted Fri March 11, 2022 11:28 AM
    You could use my printfreeB utility from my utils2_ak package:
    $ printfreeB art systables
    Looking at DB: art, Table: systables.

    Report for table: art:systables in dbspace #4: datadbs_1.

       Table partition header reports that table has:  
           500% free
           64 pages allocated in 2048 extents
           41 pages used
           304 rows of data in 21 data pages
       Sysptnext reports:   64 pages in 4 extents.
       Bitmap scan reports:
           Unused pages:                   24.
           Bitmap pages:                    1.
           Unused blob pages:               0.
           Partial data pages:              1.
           Partial blob pages:              0.
           Small data pages:                0.
           Half full blob pages:            0.
           Full data pages:                20.
           Full index pages:               18.
           Full blob pages:                 0.
                                   -----------
           Total pages reported:           66.





    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 9.  RE: How to detect if table has old-fashioned attached indexes (index pages in non-fragmented table partition)

    Posted Fri March 11, 2022 11:41 AM

    Hello, nice,

    similar to oncheck -pT in summary part, I was thinking of number of index pages per attached index, if table has more attached indexes than one.

    Probably there is no summary number per index as there are for npused and npdata in sysptnhdr for a partition

     

    Obsah obrázku text, klipart Popis byl vytvořen automaticky

    Milan Rafaj

    Infrastructure Architects & Specialists Teamleader

    TC&IS

    +420 737 264 248

    www.kyndryl.com

     

    Kyndryl Česká republika, spol. s r. o.

    Sídlo: Praha 4, Chodov, V Parku 2294/4, PSČ: 148 00,

    IČ: 14890992

    Zapsaná v obchodním rejstříku, vedeném Městským soudem v Praze (oddíl C, vložka 339277)

    Registered address: Prague 4, Chodov, V Parku, 2294/4, Zip code: 148 00

    Company ID: 14890992

    Entered in the Commercial Register maintained by the Municipal Court in Prague (Part C, Entry 339277)




    Original Message


  • 10.  RE: How to detect if table has old-fashioned attached indexes (index pages in non-fragmented table partition)

    IBM Champion
    Posted Fri March 11, 2022 11:46 AM
    Correct. The index pages for all attached indexes are interleaved with data pages. If the index was created by a version before 7.30 they are interleaved page-by-page. If created in a newer version than that, the index would be "semi-attached" and index pages would be in their own extents. But there is no way to know which extent belongs to which index. PrintfreeB reads the page headers and the flags there indicate page type, so it knows that a page is data or index or overhead/bitmap or unused but nothing else. Even oncheck -pT can't figure that one out.

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 11.  RE: How to detect if table has old-fashioned attached indexes (index pages in non-fragmented table partition)

    IBM Champion
    Posted Thu March 10, 2022 05:52 PM
    select trim(dbsname)||":"||trim(owner)||"."||trim(tabname)
    from sysptnhdr h, systabnames n
    where h.partnum = n.partnum
    and h.lockid=h.partnum
    and h.nkeys > 0
    and tabname not like "sys%";

    ------------------------------
    Andreas Legner
    ------------------------------

    Original Message


  • 12.  RE: How to detect if table has old-fashioned attached indexes (index pages in non-fragmented table partition)

    Posted Fri March 11, 2022 11:20 AM
    Hello Andreas,

    nice query as well, thank you.

    ------------------------------
    Milan Rafaj
    Infrastructure Architects and Specialists Team Leader
    Kyndryl
    +420737264248
    ------------------------------

    Original Message