How to find BW Indexes not used in a long time

[Former Member]

Hello all,

I am attempting to find out which indexes have not been used in the last 2 years in a effort to eliminate indexes from our BWA environment. Is there a way I can query our BW environment to figure this out? Is there a table, or group of tables in combination, that might be able to help me out here?

Thanks to you all.

[Former Member · Accepted solution]

HI,

Check out the below link, this could be useful to you.

http://www.insiderlearningnetwork.com/dr_berg/blog/2011/04/07/top_10_transactions_for_bwa_management

Regards,

Venkatesh.

[Former Member · Accepted solution]

Thanks for such a quick response.

I am assuming the following about the table :

- Column u201CCALDAYu201D contains a date value for each day the Index is active.

- Column u201CBIA_USEDu201D contains a value that is iterated for the given day each time the Index is used.

- Column u201CBIA_NOTPOSSIBLEu201D contains a value representing how many times a query could not access this Index.

- Column u201CNO_BIAu201D tells if a BWA Index exists at all on any of my BWA Blades

- Column u201CBIA_TECHINAu201D ????????

- Column u201CBIA_INAu201D ???????

- Column u201CDB_FALLBACKu201D ???????

I am not sure if my understanding of the column is correct. Can you tell me they are? The columns with u201C???????u201D mean I have no solid understanding of how to use these columns at all.

Thanks

[Former Member]

Hi,

create a program to read the count (*) per partprov from RSDDSTAT_DM view where calday is <whatever range you enter - say monthly stats> and aggregate like '%X'. ( You can simulate this w/o program by going to se11 )

With that you can show the number of times each BWA index was hit, which users, queries etc are using and see the trends over the time.

e.g. SELECT CALDAY PARTPROV COUNT( * ) AS COUNT

INTO TABLE W_TABLE

FROM RSDDSTAT_DM

WHERE CALDAY IN W_CALDAY AND AGGREGATE LIKE '%$X'

Regards,

Durgesh.

[Former Member]

Ahhh, yes. Let me give my ABAP skills a go! I will let you know if it works for me.

Thanks.