Dynamic Columns in TE Views


In pages 3, 4 and 5, we collected the results from monitoring table function, then ran some workload and then collected the results again to analyze the difference. This can easily be done by using dynamic columns in Technology Explorer. Dynamic columns gives you the deltas in column values. This delta is the 'change' in column value. Values are collected after certain time interval specified by the user.

Dynamic column calculates the changes in values and displays these values as rates. It looks like X/n where 'X' is the change in base value for 'n' period of time. Lets go back to our SQL statement:

SELECT APPLICATION_HANDLE, 
       APPLICATION_NAME,
	   TOTAL_CPU_TIME, 
	   TOTAL_ACT_TIME, 
	   ACT_COMPLETED_TOTAL, 
	   TOTAL_WAIT_TIME, 
	   CLIENT_IDLE_WAIT_TIME
	FROM TABLE(MON_GET_CONNECTION(NULL, -1)) as t;

In this query, TOTAL_CPU_TIME column is cumulative and will keep on increasing. If we can get the rate out of this column, then it will show the amount of CPU an application has consumed in last n seconds.

Let us add a dynamic column in the view we created in previous pages. Open testTableDef.xml, you should get a similar XML profile as shown on the right pane. To add a dynamic column in this view, we need to add following code in this XML profile.

<col type="dynamiccolumn" name="totalIndexReadRate"/>
<dynamicColumn name="commitRate" type="DELTA_NORMALIZED" column="commit_sql_stmts" title="Commit Rate"/>
<column name="TOTAL_INDEX_READS">
	<c_sql_name>TOTAL_INDEX_READS</c_sql_name>
	<c_title>Total index reads</c_title>
	<c_presentation>n</c_presentation>
	<c_drill>n</c_drill>
</column>

Define a primary key :