Create your own indexes on TDW 2.1 and increase your SPEED.

Did you know that the WPA, Warehouse Proxy Agent, creates indexes on the tables it creates? Suppose you want your own or you are a "power user" and know that you could get better performance by indexing, but you don't know how.

Well, it does. The default index for the RAW data are based on Server_Name, WRITETIME and TMZDIFF in that order and in ASCENDING order. The summarization tables have two indexes on them, the first is by Server_Name and WRITETIME. The second index includes TMZDIFF, SHIFTPERIOD and VACATIONPERIOD.

This is all goodness, but indexes are good if your queries use the data in the index. Example, using select * from NT_PROCESSOR where Server_Name like '%BOB' is not good. Using a "like" statement forces additional evaluation on each row of data. Using a select * is not good either. Using a select Server_Name, Available_Bytes from NT_MEMORY where Server_Name = BOB is a much better query and will finish MUCH faster.

What if you want to create your own indexes? Maybe you frequently query for Available_Bytes in the NT_MEMORY table.

Well here is an example:

db2> create index ntmemory_idx on "NT_Memory" ("Available_Bytes")

If you disect this command, this is creating a new index called ntmemory_idx on table NT_Memory that consists of the Available_Bytes column.

Enjoy