Informix

 View Only

  • 1.  Unused Indexes

    IBM Champion
    Posted Mon January 25, 2021 11:33 AM
    Edited by System Fri January 20, 2023 04:37 PM

    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


  • 2.  RE: Unused Indexes

    Posted Mon January 25, 2021 11:50 AM
    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:
     
     
    The current internal ZACS Informix Page can be found here:
     
     
     



    Original Message


  • 3.  RE: Unused Indexes

    IBM Champion
    Posted Mon January 25, 2021 12:09 PM
    Edited by System Fri January 20, 2023 04:30 PM

    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_task



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

    Original Message


  • 4.  RE: Unused Indexes

    Posted Mon January 25, 2021 02:49 PM
    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:
     
     
    The current internal ZACS Informix Page can be found here:
     




    Original Message


  • 5.  RE: Unused Indexes

    IBM Champion
    Posted Mon January 25, 2021 02:57 PM
    @Scott Pickett The relevant columns seem to be absent from 12.10.FC14XF.
    ​

    ------------------------------
    TOM GIRSCH
    ------------------------------

    Original Message


  • 6.  RE: Unused Indexes

    Posted Mon January 25, 2021 03:25 PM
    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:
     
     
    The current internal ZACS Informix Page can be found here:
     
     
     



    Original Message


  • 7.  RE: Unused Indexes

    IBM Champion
    Posted Mon January 25, 2021 04:33 PM

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

     




    Original Message


  • 8.  RE: Unused Indexes

    IBM Champion
    Posted Mon January 25, 2021 12:16 PM
    Edited by System Fri January 20, 2023 04:43 PM

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

    Original Message