Unused Indexes

A few revisions of IDS 12.10 ago, HCL finally added a field to the oncheck -pt output that tells you the last time an index was used by a query. I'd like to search on that and refine it a bit. Rather than reinventing the entire wheel, has someone here sussed out if that field is in sysmaster, and if so, where? UPDATED to note: I'm on 12.10.FC14XF TIA, - TJG ------------------------------ TOM GIRSCH ------------------------------ #Informix

sysmaster:sysactptnhdr.pn_idx_acc_tm - active partitions sysmaster:sysptnhdr.idx_acc_tm - all partitions Scott Pickett IBM Informix WW Technical Sales IBM Informix WW Cloud Technical Sales IBM Informix WW Cloud Technical Sales ICIAE IBM Informix WW Informix Warehouse Accelerator Sales Boston, Massachusetts USA spickett@us.ibm.com 617-899-7549 33 Years Informix User The current Informix Roadshow presentations are here: https://community.ibm.com/community/user/hybriddatamanagement/viewdocument/informix-1410xc2-features?CommunityKey=cf5a1f39-c21f-4bc4-9ec2-7ca108f0a365&tab=librarydocuments All presentations and the agenda used by the Roadshow can be found there. The current internal ZACS Informix Page can be found here: https://w3-connections.ibm.com/wikis/home?lang=en#!/wiki/Wf58c4c538dbf_45b4_b7a7_5003d0ceb79b/page/Informix The older Internal IMAZ CTP Informix Main Page can be found here: https://w3-connections.ibm.com/wikis/home?lang=en-us#!/wiki/Info%20Mgmt%20Client%20Technical%20Professional%20Resources%20Wiki/page/Informix Website for Internet Of Things https://www.ibm.com/internet-of-things/ Website for Informix https://www.ibm.com/analytics/us/en/technology/informix/

I don't see a column in sysptnhdr that corresponds to the datetime at which an index was most recently accessed. I also don't see that column in sysactptnhdr; wondering if maybe it didn't get added until 14.10? I'm looking for the emphasized portion here: $ oncheck -pt sysadmin:ph_taskTBLspace Report for sysadmin:informix.ph_task Physical Address 1:181905 Creation date 01/31/2017 14:20:23 TBLspace Flags 902 Row Locking TBLspace contains VARCHARS TBLspace use 4 bit bit-maps Maximum row size 8709 Number of special columns 6 Number of keys 0 Number of extents 3 Current serial value 69 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 2 First extent size 8 Next extent size 32 Number of pages allocated 32 Number of pages used 23 Number of data pages 21 Number of rows 61 Partition partnum 1048840 Partition lockid 1048840 Extents Logical Page Physical Page Size Physical Pages 0 1:188302 8 8 8 1:188696 8 8 16 1:189096 16 16 Index 102_4 fragment partition chaos_root in DBspace chaos_root Physical Address 1:181906 Creation date 01/31/2017 14:20:23 TBLspace Flags 802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 8709 Number of special columns 0 Number of keys 1 Number of extents 1 Current serial value 1 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 2 First extent size 4 Next extent size 4 Number of pages allocated 4 Number of pages used 2 Number of data pages 0 Number of rows 0 Partition partnum 1048841 Partition lockid 1048840 >> Last Lookup/Scan No Record <-- THIS! Extents Logical Page Physical Page Size Physical Pages 0 1:188310 4 4 ------------------------------ TOM GIRSCH ------------------------------

sysactptnhdr.altstmp, maybe? SELECT n.tabname, ph.* FROM sysactptnhdr AS ph JOIN systabnames AS n ON n.partnum = ph.partnum WHERE n.tabname MATCHES "ix*";tabname ix_cmd_hist_01 address 42611433512 partnum 1048834 pn_flags 2050 rowsize 0 ncols 0 nkeys 1 nextns 6 pagesize 2048 created 1485872423 cur_serial4 1 cur_bigserial 1 cur_serial8 1 fextsiz 4 nextsiz 128 nptotal 236 npused 236 npdata 0 lockid 1048833 nrows 0 ninserts 554401 nupdates 0 ndeletes 544710 flags 0 ucount 0 chunk 1 offset 181899 lastidxpn 191 nextns1 6 nextns2 0 badkeys 0 altstmp 174775196 ocount 0 skstamp -871249784 pta_oldvers 0 pta_newvers 0 pta_bmpagenum 0 pta_totpgs 0 pta_opems_allocd 0 pta_opems_filled 0 glscollname en_US.819 pf_rqlock 144398 pf_wtlock 0 pf_deadlk 0 pf_dltouts 0 pf_isread 29058 pf_iswrite 29050 pf_isrwrite 0 pf_isdelete 28424 pf_bfcread 203219 pf_bfcwrite 59725 pf_seqscans 0 pf_dskreads 2570 pf_dskwrites 4103 pn_flags2 0 ------------------------------ TOM GIRSCH ------------------------------

they are integers, think you have to convert to date Scott Pickett IBM Informix WW Technical Sales IBM Informix WW Cloud Technical Sales IBM Informix WW Cloud Technical Sales ICIAE IBM Informix WW Informix Warehouse Accelerator Sales Boston, Massachusetts USA spickett@us.ibm.com 617-899-7549 33 Years Informix User The current Informix Roadshow presentations are here: https://community.ibm.com/community/user/hybriddatamanagement/viewdocument/informix-1410xc2-features?CommunityKey=cf5a1f39-c21f-4bc4-9ec2-7ca108f0a365&tab=librarydocuments All presentations and the agenda used by the Roadshow can be found there. The current internal ZACS Informix Page can be found here: https://w3-connections.ibm.com/wikis/home?lang=en#!/wiki/Wf58c4c538dbf_45b4_b7a7_5003d0ceb79b/page/Informix The older Internal IMAZ CTP Informix Main Page can be found here: https://w3-connections.ibm.com/wikis/home?lang=en-us#!/wiki/Info%20Mgmt%20Client%20Technical%20Professional%20Resources%20Wiki/page/Informix Website for Internet Of Things https://www.ibm.com/internet-of-things/ Website for Informix https://www.ibm.com/analytics/us/en/technology/informix/

@Scott Pickett The relevant columns seem to be absent from 12.10.FC14XF. ------------------------------ TOM GIRSCH ------------------------------

This was picked up from the sysmaster.sql file in $INFORMIXDIR/etc/sysmaster.sql for version 14.10.xC5. Scott Pickett IBM Informix WW Technical Sales IBM Informix WW Cloud Technical Sales IBM Informix WW Cloud Technical Sales ICIAE IBM Informix WW Informix Warehouse Accelerator Sales Boston, Massachusetts USA spickett@us.ibm.com 617-899-7549 33 Years Informix User The current Informix Roadshow presentations are here: https://community.ibm.com/community/user/hybriddatamanagement/viewdocument/informix-1410xc2-features?CommunityKey=cf5a1f39-c21f-4bc4-9ec2-7ca108f0a365&tab=librarydocuments All presentations and the agenda used by the Roadshow can be found there. The current internal ZACS Informix Page can be found here: https://w3-connections.ibm.com/wikis/home?lang=en#!/wiki/Wf58c4c538dbf_45b4_b7a7_5003d0ceb79b/page/Informix The older Internal IMAZ CTP Informix Main Page can be found here: https://w3-connections.ibm.com/wikis/home?lang=en-us#!/wiki/Info%20Mgmt%20Client%20Technical%20Professional%20Resources%20Wiki/page/Informix Website for Internet Of Things https://www.ibm.com/internet-of-things/ Website for Informix https://www.ibm.com/analytics/us/en/technology/informix/

So it looks like there's no way to get it from sysmaster in 12.10. Drat.