indexes in smi table
The sysmaster:systabnames and sysmaster:systabinfo provide information across all databases however not whether the object is an index or not. Is it possible to find this information within the smi interface? Ie without using sysindexes. Informix 11.7 XC8
I've been trying to figure this one out for years. AFAIK you have to refer back to <datrabase>:sysindices (or sysindexes) to verify this. I suppose you could look at the individual data page headers in the extents for the object and check the flag in the header to see if it is an index page, but that strikes me as scratching your left ear with your right elbow and sysindices is easier to deal with. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sun, Aug 9, 2015 at 1:07 PM, ALI SHAHNAZI <ali@datasync.com.au> wrote: > The sysmaster:systabnames and sysmaster:systabinfo provide information > across > all databases however not whether the object is an index or not. Is it > possible to find this information within the smi interface? Ie without > using > sysindexes. > Informix 11.7 XC8 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bdc05f22c4c31051ce4186e
Hi, Try this: SELECT tb.tabname AS obj_name, tb.owner AS owner, tb.dbsname AS database, CASE WHEN SUM(ph.nkeys) = 0 THEN 'T' WHEN SUM(ph.nkeys) > 0 AND SUM(ph.npdata) > 0 THEN 'B' ELSE 'I' END AS obj_type, FROM sysmaster:systabnames tb, sysmaster:sysptnhdr ph WHERE tb.partnum=ph.partnum AND bitval(ph.flags,4) = 0 --System Catalog Table AND bitval(ph.flags,8192) = 0 --Permanent System created Table AND bitval(ph.flags,32) = 0 --System created Temp Table AND bitval(ph.flags,64) = 0 --User created Temp Table AND bitval(ph.flags,128) = 0 --Sort File AND bitval(ph.flags,131072) = 0 --Hash Table AND bitval(ph.flags,16384) = 0 --Special Function Temp Tables GROUP BY 1, 2, 3; Double check the result. Cheers, On Sun, 9 Aug 2015 at 18:08 ALI SHAHNAZI <ali@datasync.com.au> wrote: > The sysmaster:systabnames and sysmaster:systabinfo provide information > across > all databases however not whether the object is an index or not. Is it > possible to find this information within the smi interface? Ie without > using > sysindexes. > Informix 11.7 XC8 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b450b1082c4cc051ce4f8ce
Yeah, that's supposed to work, and it did in v10, 11.10, and 11.50 but they broke it in v11.70 and 12.10. nkeys is often zero for indexes and even using rowsize which used to be zero for indexes isn't always any more. So, this is not a reliable method. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sun, Aug 9, 2015 at 2:23 PM, Ricardo Henriques < ricardoaireshenriques@gmail.com> wrote: > Hi, > > Try this: > SELECT > > tb.tabname AS obj_name, > > tb.owner AS owner, > > tb.dbsname AS database, > > CASE > > WHEN SUM(ph.nkeys) = 0 THEN 'T' > > WHEN SUM(ph.nkeys) > 0 AND SUM(ph.npdata) > 0 THEN 'B' > > ELSE 'I' > > END AS obj_type, > FROM > > sysmaster:systabnames tb, > > sysmaster:sysptnhdr ph > WHERE > > tb.partnum=ph.partnum > > AND bitval(ph.flags,4) = 0 --System Catalog Table > > AND bitval(ph.flags,8192) = 0 --Permanent System created Table > > AND bitval(ph.flags,32) = 0 --System created Temp Table > > AND bitval(ph.flags,64) = 0 --User created Temp Table > > AND bitval(ph.flags,128) = 0 --Sort File > > AND bitval(ph.flags,131072) = 0 --Hash Table > > AND bitval(ph.flags,16384) = 0 --Special Function Temp Tables > GROUP BY 1, 2, 3; > > Double check the result. > > Cheers, > > On Sun, 9 Aug 2015 at 18:08 ALI SHAHNAZI <ali@datasync.com.au> wrote: > > > The sysmaster:systabnames and sysmaster:systabinfo provide information > > across > > all databases however not whether the object is an index or not. Is it > > possible to find this information within the smi interface? Ie without > > using > > sysindexes. > > Informix 11.7 XC8 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --047d7b450b1082c4cc051ce4f8ce > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0111c016431820051ceb31e9
Thanks Art and Ricardo