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

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. Milan Rafaj www.kyndryl.com #Informix

oncheck -pT #Informix

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

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

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

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

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

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, Milan Rafaj www.kyndryl.com

Hello Andreas, nice query as well, thank you. ------------------------------ Milan Rafaj Infrastructure Architects and Specialists Team Leader Kyndryl +420737264248 ------------------------------

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

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

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