Example 9 17   Table space and buffer pool snapshots

db2 get snapshot for tablespaces on db2_emp

db2 get snapshot for bufferpools on db2_emp

The 

dynamic SQL snapshot 

(Example 9 18) is used extensively to determine how 

well SQL statements are performing. This snapshot summarizes the behavior of 

the different dynamic SQL statements that are run. The snapshot does not 

capture static SQL statements, so anything that was pre bound will not show up 

in this list. The snapshot is an aggregate of the information concerning the SQL 

statements. If a SQL statement is executed 102 times, then there will be one 

entry with the summary of the total behavior of the 102 executions.

Example 9 18   Dynamic SQL snapshot

db2 get snapshot for dynamic sql on db2_emp

Snapshot table functions

As mentioned earlier, DB2 UDB features the capability to capture snapshots 

using SQL 

table functions

. Accessing snapshot information through an SQL 

interface is very useful, because the requested information can be filtered and 

sorted, thereby presented in a more readable format. The snapshot 

table 

functions

 can be also very helpful in analyzing system utilization over a time 

period.

Most of the snapshot 

table functions

 accept two input parameters. The first is a 

string representing the database name. Entering NULL value for the database 

name parameter instructs the function to get snapshot information for all 

databases in the instance. The second parameter represents the partition 

number. To capture a snapshot for the currently connected partition, enter a 

value of  1 or a NULL.

The query in Example 9 19 uses the table function

 SNAPSHOT_TABLE()

to 

retrieve the five table names, which have the most read and write activity on 

database 

DB2_EMP

.

Example 9 19   Sample snapshot table function

db2 "select  snapshot_timestamp, table_name, rows_written, rows_read,

rows_written + rows_read as rows_accessed 

from table (SNAPSHOT_TABLE( DB2_EMP ,  1))as T 

order by rows_accessed desc 

fetch first 5 rows only"

TABLE_NAME    ROWS_WRITTEN  ROWS_READ  ROWS_ACCESSED       

EMPLOYEE                 0        256            256

 Chapter 9. Testing and tuning 

289