To Get the Snap shot of Database Object à
Db2 “SELECT * FROM TABLE(SNAPSHOT_RANGES(‘SAMPLE’,-1))AS SNAPSHOT_RANGES “
To Get Snapshot of a Lock Informationà
SELECT * FROM TABLE(SNAPSHOT_LOCK(‘SAMPLE’,-1))AS SNAPSHOT_LOCK”
*******************************************************************
What are snapshots?
1)DB2 snapshots are point-in-time views into what DB2 is doing and how it is performing.
They are used in the performance tuning of instances, databases, and applications
running on DB2.
The information returned from a snapshot is a combination of
cumulative information and data that applies only to a single moment.
Snapshots are the only method you have to view a large amount of the behavior of
DB2
What types of snapshots are there?
Each type of snapshot captures a different set of data about DB2:
Database
Table space
Tables
Buffer pools
Locks
Database Manager
Applications
Let's look at some example snapshot commands to see how this syntax works. Here's
a command for a database snapshot:
db2 get snapshot for all databases
For Applications it is :
Db2 get snapshot for all applications
Note: This snapshot will be taken on all applications currently connected to the
instance and not just those for a particular database -- even if you are currently
connected to one.
Here's a command for a buffer pool snapshot on the fourth node in a partitioned
database:
db2 get snapshot for bufferpools on drew_db at dbpartitionnum 3
Note: The dbpartitionnum field is used to specify the partition at which the
snapshot should be taken. Even if you have connected to a partition other than 0, the
snapshot will still be taken for the 0 node unless otherwise specified.
What memory is used?
The MON_HEAP_SZ variable in the database configuration specifies how much
memory is set aside for the snapshots and event monitors.
Turning snapshots on and off
The monitoring of DB2 is not turned on by default, but instead has to be set at the
connection or instance level. There are a series of monitor switches that determine if a
type of data element is monitored. There is also a memory heap set aside for
containing the information stored for monitoring
Method 1: Setting monitor switches at the instance level
Setting the monitor switches at the instance level will affect all users connected to any
databases in that instance.
Here are the monitor switches
· DFT_MON_STMT: Statement monitor (used for dynamic SQL)
· DFT_MON_TABLE: Table monitors
· DFT_MON_LOCK: Lock monitor
· DFT_MON_BUFPOOL: Buffer pool monitors
· DFT_MON_SORT: Sort monitoring
· DFT_MON_UOW: Unit of work information
· DFT_MON_TIMESTAMP: Tracks timestamp information
The values of these switches are stored in the database manager configuration
information. Here are the database manager configuration parameters:
The monitor switches are set just like any other instance configuration parameter:
db2 update dbm cfg using monitorSwitch [ON | OFF]
Here's an example:
db2 update dbm cfg using DFT_MON_SORT ON
Scope of the snapshots
It is important to understand what information is being returned to you with the
snapshots and when the monitoring begins.
The switches can be set dynamically at
both instance and application levels, and both affect the monitoring of connections
differently.
If the monitor switches are turned on after an application has already connected, then
the information will only be captured for all actions after the switch has been turned on,
Resetting the switches
The monitor switches can be reset to null or 0 for all values by executing the following
command:
db2 reset monitor [ALL | for database databaseName] [at dbpartitionnum partitionNum]
Where databaseName is the name of the database and partitionNum is the node
number.
Here's an example:
db2 reset monitor for database drew_db
In this example, Application A has already connected to the database before the
monitor switches are turned on. When the first snapshot, Snap1, is taken, no
information is returned by the database, as the monitor switches have not been turned
on. When the second snapshot, Snap2, is taken, information on both Application A and
Application B is captured.
Resetting the switches
The monitor switches can be reset to null or 0 for all values by executing the following
command:
db2 reset monitor [ALL | for database databaseName] [at dbpartitionnum partitionNum]
Where databaseName is the name of the database and partitionNum is the node
number.
Here's an example:
db2 reset monitor for database drew_db
Database snapshots
The snapshots have been broken up into separate topics but there is still a fair amount
of information that is repeated in each. The most commonly used snapshots are the
database and dynamic SQL snapshots
The database snapshot captures summary information about the database. The
information in this snapshot contains an aggregate of almost all the data in all the other
snapshots. The snapshot's data is a summary since the monitor switches have been
reset last.
The database snapshot is the most frequently used snapshot, and most performance
problems are obvious from the information in it. It contains information on:
Connections
DB2 agents
Locks
Sorting
Total buffer pool activity
SQL activity
SQL statement volume
Log usage
Cache usage
Hash joins
Database manager snapshots
The database manager snapshot captures information specific to the instance level.
The information centers on the total amount of memory allocated to the instance and
the number of agents that are currently active on the system.
Here's the command for this type of snapshot:
db2 get snapshot for database manager
Table snapshots
The table snapshot contains information on the usage and creation of all tables. This
information is quite useful in determining how much work is being run against a table
and how much the table data changes. This information can then be used to decide
how your data should be laid out physically.
db2 get snapshot for tables on drew_db
Table space and buffer pool snapshots
The table space and buffer pool snapshots contain similar information. The table space
snapshot returns information on the layout of the table space and how much space is
being used. The buffer pool snapshot contains information on how much space is
currently allocated for the buffer pool and how much space will be allocated when the
database is next reset. Both snapshots contain a summary of the way in which data is
accessed from the database. This access could be done from a buffer pool, direct from
tables on disk, or through a direct read or write for LOBs or LONG objects.
Here are the commands for these types of snapshots:
db2 get snapshot for tablespaces on drew_db
db2 get snapshot for bufferpools on drew_db
Tablespace Information
Tablespace name = STORAGE1
Tablespace ID = 3
Tablespace Type = System managed space
Tablespace Content Type = Any data
Tablespace Page size (bytes) = 4096
Tablespace Extent size (pages) = 8
Tablespace Prefetch size (pages) = 8
Buffer pool ID currently in use = 1
Buffer pool ID next startup = 1
Tablespace State = 0x'00000000'
Detailed explanation:
Normal
Total number of pages = 159
Number of usable pages = 159
Number of used pages = 159
Minimum Recovery Time =
Number of quiescers = 0
Number of containers = 1
Bufferpool Information
Node number = 0
Tablespaces using bufferpool = 4
Alter bufferpool information:
Pages left to remove = 0
Current size = 250
Post-alter size = 250
Common Information
The common information given on data usage is very complex and is not required
unless advanced performance tuning is being performed.
Buffer pool data logical reads = 39
Buffer pool data physical reads = 14
Buffer pool data writes = 0
Buffer pool index logical reads = 63
Buffer pool index physical reads = 37
This information can be used to determine the buffer pool hit ratio, as illustrated in the
figure below:
The ratio indicates how often the data is found in the buffer pools, instead of having to
be read a table saved on disk.
Lock snapshot
The lock snapshot is very useful in determining what locks an application currently is
holding or what locks another application is waiting on. The snapshot lists all
applications on the system and the locks that each is holding. Each lock, and each
application, is given a unique identifier number.
Here's the command for this type of snapshot:
db2 get snapshot for locks on drew_db
Dynamic SQL snapshots
The dynamic SQL snapshot 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 prebound will not show up in this list.
0 comments:
Post a Comment