Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The Following is the TCB index that I could get using a command for my DB2 database. I don't know how to analyze it. Which columns are important? What do they mean?

TCB Index Stats:

Address            TableName    IID   EmpPgDel   RootSplits BndrySplts PseuEmptPg Scans      KeyUpdates InclUpdats NonBndSpts PgAllocs   Merges     PseuDels   DelClean   IntNodSpl  
0x07000000EA695040 TAB1         21    2128       0          5          2143       191        1738530    0          7916       6016       0          1805985    1743606    26         
0x07000000EA695040 TAB1         20    357        0          2332       357        1242710    1738530    0          2584       4054       0          1812241    1652843    15         
0x07000000EA695040 TAB1         19    662        1          4293       662        16464      1907107    0          4907       7537       0          2004994    1556457    46         
0x07000000EA695040 TAB1         18    184        0          1689       184        2788625    1738530    0          1044       2235       0          1811998    1709486    6          
0x07000000EA695040 TAB1         17    4705       1          0          4990       1210257    2017860    0          14890      11525      0          2119054    2067832    71         
0x07000000EA695040 TAB1         16    5754       0          422        5923       116        1878324    0          15534      12105      0          1979092    1815142    95         
0x07000000EA695040 TAB1         15    309        0          0          309        59269      1738530    0          5403       4419       0          1809592    1667684    13         
0x07000000EA695040 TAB1         14    1          0          0          1          1964       38286      0          640        556        0          139952     121125     0          
0x07000000EA695040 TAB1         13    0          1          0          0          1025       85         0          599        540        0          16995      15319      1          
0x07000000EA695040 TAB1         12    55         0          549        55         3333550    85         0          404        787        0          103223     102424     0          
0x07000000EA695040 TAB1         11    0          1          0          0          0          85         0          667        583        0          92226      87623      1          
0x07000000EA695040 TAB1         10    0          0          0          0          2311       85         0          664        582        0          102572     88222      0          
0x07000000EA695040 TAB1         9     2119       0          0          2530       92242      1738530    0          7482       5870       0          1810986    1764093    19         
0x07000000EA695040 TAB1         8     42         0          0          46         5140       1401046    0          679        538        0          1468321    1457986    0          
0x07000000EA695040 TAB1         7     770        0          514        959        1040       1738530    0          3460       3294       0          1809636    1758940    10         
0x07000000EA695040 TAB1         6     71         0          0          71         208155     105241     0          1716       1488       0          205678     183839     3          
0x07000000EA695040 TAB1         5     881        0          5          1094       459        1738530    0          4426       3622       0          1809593    1766681    11         
0x07000000EA695040 TAB1         4     176        0          1837       172        2104       3016234    0          1068       2111       0          3119296    1655094    2          
0x07000000EA695040 TAB1         3     2056       0          0          2539       945246     1738530    0          7459       5829       0          1810990    1778688    20         
0x07000000EA695040 TAB1         2     14         0          0          14         1956       827370     0          767        606        0          914780     715791     0          
0x07000000EA695040 TAB1         1     42         0          1120       42         14577342   0          0          640        1436       0          103138     100282     2          
0x07000000E97B80C0 TAB2         22    53         0          804        52         101027     0          0          0          770        0          36098      34766      1          
0x07000000E97B80C0 TAB2         21    0          0          310        0          406873     175642     0          565        849        0          211482     147209     1          
0x07000000E97B80C0 TAB2         20    176        0          0          172        7117       3463       0          4196       4033       0          38044      38154      36         
0x07000000E97B80C0 TAB2         19    244        0          4418       238        2271510    0          2286113    0          4222       0          36098      36680      4          
0x07000000E97B80C0 TAB2         18    431        0          4543       1152       1614       2286113    0          3338       7482       0          2321909    2262963    98         
0x07000000E97B80C0 TAB2         17    46         0          697        46         121        0          0          0          669        0          36098      34754      1          
0x07000000E97B80C0 TAB2         16    23         0          394        23         6600       0          0          0          378        0          36098      30821      0          
0x07000000E97B80C0 TAB2         15    122        0          0          119        1497610    0          0          1729       1659       0          36098      35976      7          
0x07000000E97B80C0 TAB2         14    0          0          293        0          103031     86453      0          109        388        0          122249     78577      0          
0x07000000E97B80C0 TAB2         13    156        0          0          371        26611      2286113    0          3234       3103       0          2321909    2276441    13         
0x07000000E97B80C0 TAB2         12    43         0          680        43         11827      0          0          0          653        0          36098      33917      1          
0x07000000E97B80C0 TAB2         11    1          0          598        1          63852      86453      0          425        986        0          122289     85714      2          
0x07000000E97B80C0 TAB2         10    62         0          918        61         18190      0          0          0          881        0          36098      36052      1          
0x07000000E97B80C0 TAB2         9     32         0          518        32         209418     36098      0          0          496        0          72196      72112      0          
0x07000000E97B80C0 TAB2         8     13         0          0          13         6383       3463       0          222        219        0          37922      33539      1          
0x07000000E97B80C0 TAB2         7     43         0          670        43         23306      0          0          0          642        0          36098      34458      1          
0x07000000E97B80C0 TAB2         6     31         0          518        31         181547     0          0          0          495        0          36098      36044      0          
0x07000000E97B80C0 TAB2         5     32         0          519        32         209546     36098      0          0          498        0          72196      72143      0          
0x07000000E97B80C0 TAB2         4     11         0          1384       9          7003       2286113    0          23         1332       0          2321909    1000172    1          
0x07000000E97B80C0 TAB2         3     0          0          0          0          6328       0          0          211        208        0          18539      13686      0          
0x07000000E97B80C0 TAB2         2     0          0          136        0          59348      175642     0          139        266        0          211426     157453     0          
0x07000000E97B80C0 TAB2         1     31         0          483        31         8304809    0          0          0          462        0          36098      36008      0          
share|improve this question

1 Answer 1

up vote 3 down vote accepted

The report that you ran (I'm assuming it is the -tcbstats option on the db2pd command) allows you to see what indexes are applied to a table (or tables).

In your case, you can see TAB1 and TAB2 listed. For those tables, the next column is the index id (not the name of the index). You have 21 indexes assigned to TAB1 right now. Probably the biggest thing you care about is the Scans column. That shows you how often an index is used. This is important for determining the usefulness of an index. And based on what is shown above, check out your line:

Address            TableName    IID   EmpPgDel   RootSplits BndrySplts PseuEmptPg Scans
0x07000000EA695040 TAB1         11    0          1          0          0          0

This shows that index ID 11 in TAB1 is scanned/used 0 times. This means that DB2 has never found a reason to use this index yet. This index is probably a good candidate for deletion, which will help with increasing performance.

To identify, which index is associated with this ID, run the following:

db2 "SELECT INDSCHEMA, INDNAME
     FROM SYSCAT.INDEXES
     WHERE TABNAME = 'TAB1' AND IID = 11"

Add TABSCHEMA = ' ' to your where clause if you need to get more specific.

See the following article on developerworks for more information on identifying unused indexes.

And here is a link to how db2pd works, what parameters you pass it, what they mean, and what the different columns of the output report mean.

share|improve this answer
    
what role does KeyUpdates play? –  MozenRath Apr 23 '12 at 13:27
    
That is the number of updates to the key. I'll edit my answer to include a link to the DB2 product info for db2pd. The page in the link will have all the column names for the report and what they mean. –  Chris Aldrich Apr 23 '12 at 13:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.