UDB DB2 ON 08 09 snapshots BUFFER HIT RATIO

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.

Migration In DB2 UDB..

Glance of Steps and Important Points while Performing The MIGRATION In DB2 UDB.

1.First Most thing to be in mind is migration is possible from only Version 6.x and Version 7.x

 
 
 
 
 
 
2.Migration is in Four Steps Overview:

• Prerequisites
• Pre-Migration Tasks
• Migration Task
• Post-Migration Tasks                          

 

Most Often Commands But V forget thesee...

When issuing the DB2 CLP commands from the OS command prompt on Linux/UNIX systems, it

may be necessary to put double quotes around the CLP command. For example, when issuing

db2 describe output select * from sales, you need to put double quotes around the

describe command, e.g. db2 “describe output select * from sales”. These double

quotes are necessary because the ‘*’ is a special OS command line character. The double quotes

will allow DB2 to parse the statement properly.

ACTIVATE DATABASE

Description: Activates the specified database and starts up all necessary database services, so that the database is available for connection and use by any application.

Authorization: sysadm/sysctrl/sysmaint

Windows/Linux/UNIX Examples

Example 1:

To activate database sample to eliminate any application time spent on database initialization:

activate database sample

Example 2:

To activate database sample with user ID db2user and password abc123:

activate database sample user db2user using abc123

Notes

• Databases initialized by ACTIVATE DATABASE can be shut down using the DEACTIVATE

DATABASE command.

• The application issuing the ACTIVATE DATABASE command cannot have an active database connection to any database.

ADD CONTACT

Description: Adds a contact to the contact list which can be either defined locally on the system or in a global list. Contacts are users to whom processes such as the Scheduler and Health Monitor send messages.

The setting of the Database Administration Server (DAS) contact_host

Configuration parameter determines whether the list is local or global.

Authorization: None

Windows/Linux/UNIX Examples

Example 1:

Add Mahesh as the contact person to receive event notifications via email at

dangm@ca.ibm.com:

add contact Mahesh type email address dangm@ca.ibm.com

Example 2:

Add Mahesh as the contact person to receive event notifications via page at Mahesh@page.com.

Include a description of the contact person.

add contact Mahesh type page address Mahesh@page.com description ‘DB2 Support for IBM’

Example 3:

Add Mahesh as the contact person to receive event notifications via page at Mahesh@page.com.

Specify the 250 character message-limit restriction on the paging service:

add contact Mahesh type page maximum page length 250 address Mahesh@page.com

Notes

The notification system uses the SMTP protocol to send the notification to the mail server specified by the DB2 Administration Server configuration parameter smtp_server.

It is the responsibility of the SMTP server to send the e-mail or call the pager.

ADD CONTACTGROUP

Description: Adds a new contact group to the list of groups defined on the local system.

A contact group is a list of users and groups to whom monitoring processes such as the Scheduler and Health Monitor can send messages.

Authorization: None

Windows/Linux/UNIX Examples

Example 1:

Create a new contact group “instance_monitor” and add Mahesh and Sylvia as contact members:

add contactgroup instance_monitor contact Mahesh, contact Sylvia

Example 2:

Create a new contact group “instance_monitor” and add Mahesh and Sylvia as contact members. Include a description of the group:

add contactgroup instance_monitor contact Mahesh, contact Sylvia description ‘Instance down monitor group’

Example 3:

Create a new contact group “instance_monitor” and add the group “dba_group” to it. Include a description of the group.

add contactgroup instance_monitor group dba_group description ‘Instance

down monitor group’

Notes

• Local execution only: this command cannot be used with a remote connection.

• Contact group name must be unique among the set of groups on the system.

ADD DBPARTITIONNUM

Description: Adds a new database partition server to the partitioned database environment. This command also creates a database partition for all databases on the new database partition server. The user can specify the source database partition server for the definitions of any system temporary table spaces to be created with the new database partition, or specify that no system temporary table spaces are to be created.

Authorization: sysadm/sysctrl

Windows/Linux/UNIX Examples

Example 1:

Using the following db2node.cfg:

0              ServerA              0

1              ServerA              1

2              ServerA              2

Example 1:

To add a fourth partition (node 3), set “export db2node=3”

add dbpartionnum

Example 2:

To add a fourth partition (node 3), you want the new partition’s system temporary table space to be the same as node 2. Set “export db2node=3”

add dbpartitionnum like dbpartitionnum 2

Example 3:

To add a fourth partition (node 3), you do not want system temporary table space containers to be created. Set “export db2node=3”

add dbpartitionnum without tablespaces

Notes

• This command must be issued from the database partition server that is being added.

• For compatibility with versions earlier than Version 8, use the keyword NODE instead of DBPARTITIONNUM.

ARCHIVE LOG

Description: Closes and truncates the active log file for a recoverable database. If user exit is enabled, an archive request is issued.

Authorization: sysadm/sysctrl/sysmaint/dbadm

Windows/Linux/UNIX Examples

Using the following db2node.cfg:

0              ServerA              0

1              ServerA              1

2              ServerA              2

Example 1:

You want to copy all log files for a sample database on all partitions to a development sample database at a particular timestamp. Truncate the logs at a particular time with:

archive log for database sample on all dbpartitionnums

Example 2:

The following command is the same as example 1.

Archive logs for database on all dbpartitionnums is the default.

archive log for database sample

Example 3:

To archive all log files for sample database on all partitions except partition two:

archive log for db sample on all dbpartitionnums except dbpartitionnum(2)

Example 4:

You want to archive all log files for sample database on partition 0 only.

archive log for db sample on dbpartitionnum (2)

Example 5:

The following command is the same as example 4.

archive log for db sample on all dbpartitionnums except dbpartitionnums(1 to 2)

Example 6:

You want to archive all logs files for sample database on partitions 0 and 2.

archive log for db sample on dbpartitionnums(0, 2)

Notes

•This command can only be executed when the invoking application or shell does not have a database connection to the specified database, otherwise sqlcode SQL1490N will be returned.

•This command can only be executed on a recoverable database; otherwise sqlcode SQL2417N will be returned.

•For compatibility with versions earlier than Version 8, use the keyword NODE instead of DBPARTITIONNUM.

ATTACH

Description: Enables an application to specify the instance at which instance-level commands

(CREATE DATABASE and FORCE APPLICATION, for example) are to be executed. This instance may be the current instance, another instance on the same workstation, or an instance on a remote workstation.

Authorization: None

Windows/Linux/UNIX

Examples

Catalog two remote nodes: (See CATALOG TCPIP NODE command for information on cataloging remote nodes.)

catalog tcpip node node1 remote freedom server 50000

catalog tcpip node node2 remote flash server 50000

Example 1:

Attach to the first node, force all users, and then detach.

attach to node1

force application all

detach

Example 2:

Attach to the second node, and see who is on.

attach to node2

list applications

After the command returns agent IDs 1, 2 and 3, force 1 and 3, and then detach.

force application (1, 3)

detach

Example 3:

Attach to the current instance (not necessary, will be implicit), force all users, then detach (AIX only).

attach to $DB2INSTANCE

force application all

detach

Example 4:

Attach to instance db2inst1 using user ID user1 and password.

attach to db2inst1 user user1 using abc123

Example 5:

Attach to instance db2inst1 using user ID user1 and desire to change the password. You will be prompted with for the old password, a new password and a confirmation of the new password.

attach to db2inst1 user user1 change password

Example 6:

Attach to instance db2inst1 using user ID user1 and change the password to newpw1.

attach to db2inst1 user user1 using abc123 new newpw1 confirm newpw1

Notes

“VERIFY” parameter will transfer the backup image over the network before doing the

consistency check.

Important Interview Questions of Diffrent Companies..

What is sortheap ?



Defines the maximum number of private memory pages to be used for private sorts, or the maximum number of shared memory pages to be used for shared sorts

What is online Reorg/offline?


Both online and offline are there, online reorg allows other transaction to access the tables. REORG with ALLOW NO/READ/WRITE ACCESS

Which utilities do a sort? If you create an index , it will sort the corresponding column

If you are given with a query, how you tune it?

Does runstats do a sort? reorg does ? nope

Build phase of load utility does a sort ? yes

Have you done server installation? Nope ;-0 first level IBM Team only does it !!

Disaster Recovery? HADR

Increamental /Delta Back up ?

What are the db cfg parms which affect the back up strategy?

Load utility, if I want to load data with out any chance of taking the tablespace to back up pending state? LOAD with NORECOVERY option

Whats the difference between Load and import? Load is faster than import, import is like bulk insert, every thing goes to the transaction log.

Would you load data to the production tables directly? Nope .. Use staging tables

If application team is contacting you telling the application is running very slow , what all things you will check out ?

- Check for any transactions that are in lock wait state, list in doubt transactions

Table DB snap shop, Application snapshot,sql snap shopt , cpu utilization using top,check is there any stale connection( connections that very old )


- Check if any background process is running there using list utilities

- Check the number of active connections to the DB server


- Force the application/transactions which are in lock wait state

What is the difference between creating buffer pool with immediate option and default option??

How do you get tickets in your projects?

Whats star schema? Vertical portioned tables

Whats snow flow schema??


----------------------------------------------These are from First Round of CTS

HIGH WATER MARK REDUCTION

For DMS, the highwater mark is the current "end" of the tablespace address space. In other words, the page number of the first free extent following the last allocated extent of a table space. Note that this is not really a "high water mark", but rather a "current water mark", since the value can decrease. For SMS, this is not applicable.

In determining whether a REORG or RUNSTAT would allow this parameter to be reset, consider whether the REORG is in place or not (for example, whether or not a temporary tablespace is being used).

• If the REORG is using a temporary tablespace then the REORGed object will be copied over the original object, and will use the same extents (the object size may decrease, stay the same, or increase, depending on the data in the table).
• If it is an in-place REORG then another object will need to be created in the tablespace. This will be populated by the REORG, the other object will be removed, and we can now point to this new object. If there was not enough free space below the highwater mark to hold this new object then a REORG might actually cause the highwater mark to rise.

• Utilize the /LHWM and /DHWM options in the DB2DART tool.

This document provides troubleshooting information for the situation where you have deleted many records in a table and then try to resize the table space to take advantage of the freed space. After performing a REORG against the table, the ALTER TABLESPACE command fails to resize the table space to a smaller size. It returns an SQL20170N (There is not enough space in the table space ... for the specified action) error.

The ALTER TABLESPACE command returns SQL20170N when the size of the table space is being reduced such that the amount of space being removed is greater than the amount of space above the high water mark (hwm).

You can lower the high water mark of a table space that is being held by a data extent by performing the REORG TABLE command against the table space. This operation may not be successful in lowering the high water mark, however, if the high water mark is being held by a Space Map Pages (SMP) extent that cannot be moved by REORG commands.

The REORG TABLE command will not lower the high water mark of a table space if any of the following conditions are true:

• The table's page0 (the starting page of a table object) is at the highwater mark position.
• Internal space management pages (such as SMP or Extent Map Pages (EMP)) are at the highwater mark position.
• An in-place table reorganization is executed and a temporary table space is not specified. This reorganized table might result in a higher high water mark and the table will not be copied back to overwrite the original one.
• Variable size records might result in worse padding within pages after a table reorganization, which must following index clustering. Padding can result in the table size INCREASING rather than decreasing after the REORG, which means the hwm will go up, not down

Solution:

The high-water mark of a tablespace affects how small the tablespace can be shrunk during a redirected restore (or via ALTER TABLESPACE in Version 8). This document offers some hints and tips for lowering that value.

The high-water mark is a DMS tablespace attribute. To determine what the value is for a particular tablespace, use the following command and look for the corresponding entry for that tablespace.

LIST TABLESPACES SHOW DETAIL

The high-water mark is expressed in pages and represents the highest allocated page in the tablespace. This is not the same as the "Used pages" value since unused extents/pages under the highest allocated page do not affect the high-water mark (for example, if the very last page in the tablespace is in use but the rest of the tablespace is empty, the high-water mark will still point to this last page).

The high-water mark plays a major role during a redirected restore (a restore in which in the tablespace containers are redefined). Because the containers are being redefined, the number of containers can change and their sizes can change. As a result, the total number of pages in the tablespace and the total number of useable pages in the tablespace can change. For the redirected restore to be successful, the resulting number of useable pages must be at least as large as the high-water mark. This is an important point to remember.

In Version 8, the same can be said when trying to remove space from a tablespace using the REDUCE, RESIZE, or DROP options of ALTER TABLESPACE. The resulting number of pages must be greater than or equal to the high-water mark or the ALTER TABLESPACE statement will fail.

This can sometimes be a problem. For example, a tablespace is full, most of the data is then removed from it, and then there is a need to shrink the size of that tablespace. It is possible that there are data pages scattered throughout the tablespace holding the high-water mark at some high-value.

Various operations can be performed on the objects within the tablespace that may move the objects' extents around. However, it is difficult to determine what operations to do, what objects to do them on, and what the exact result of doing them will be.

Two high-water mark options were added to the db2dart tool to assist with this. These options became available in the following releases:

• V5: FixPak 12
• V6: FixPak 3
• V7: GA (original release)
• or later

The first option is /DHWM. This will display a map of the tablespace and high-water mark information. The tablespace ID must be provided when using this option (it can be specified using the /TSI option or it will be prompted for if this option is not used).

For example, to display this information for a tablespace with an ID of 4 within database TESTDB:

db2dart testdb /tsi 4

The output will be found in the file <dbname>.RPT (TESTDB.RPT in this case).

This information can be used to determine what object is holding up the high-water mark.

The second option is /LHWM. This will make suggestions about ways to lower the high-water mark for a given tablespace. The tablespace ID must be provided along with a desired high-water mark. The /TSI and /NP options can be used to specify these values, otherwise they will be prompted for. To get suggestions on lowering the high-water as much as possible, use a value of 0 as the desired high-water mark. Note that even an empty tablespace has used pages so it will be impossible to actually lower it down to 0.

For example, to display suggestions on lowering the high-water mark as much as possible for tablespace 6 in database TESTDB:

db2dart testdb /tsi 6 /np 0

The output will be found in the file <dbname>.RPT (TESTDB.RPT in this case).

Some of the operations that are suggested include REORG, EXPORT/LOAD, and DROP/EXPORT/CREATE/LOAD.

Some things to note about the suggestions:

• The suggestions are based on rules and assumptions about what will move by the operation, what the resulting size of the object will be after doing the operation, etc. This isn't always predictable and therefore there may be times when the set of steps recommended do not result in the same state as predicted by db2dart. Consider running db2dart with this option after every operation to ensure that the steps are suggested as accurately as possible.
• db2dart works on a per node/partition basis so the suggestions do not take into account all of the other nodes in a multi-node database. Some of the operations work on all nodes at once (REORG for example) so an operation on one node may affect the placement of data on others as well.

If the high-water mark is being held up by an SMP extent (space map extent) that maps no used extents then it is not possible to reduce the high-water mark through DDL or database commands.

Note: Never run db2dart while the database is up and running.

The db2dart Utility

Syntax:

  db2dart <DB Alias> <option> ...

Help:

  /H   Help. This help text.

Inspect Action:

  /DB  (default) Inspects entire database.

  /T   Inspects a single table. (See notes 1, 3)

  /TSF  Inspects only the tablespace files and containers.

  /TSC  Inspects a tablespace's constructs (but not its tables).

  /TS  Inspects a single tablespace and its tables.
      (/TSC and /TS require a tablespace id. See notes 1, 2)

  /ATSC Inspect constructs of all tablespaces (but not their tables).

Data Format Action:

  /DD  Dumps formatted table data. (See notes 1, 4)

  /DM  Dumps formatted block map data (See notes 1, 4)

  /DI  Dumps formatted index data. (See notes 1, 4)

  /DP  Dumps pages in hex format.  (See notes 1, 7)

  /DTSF Dumps formatted tablespace file information.

  /DEMP Dumps formatted EMP information for a DMS table. (See notes 1, 3)

  /DDEL Dumps formatted table data in delimited ASCII format.

  /DHWM Dumps highwater mark information. (See notes 1, 2)

  /LHWM Suggests ways of lowering highwater mark. (See notes 1, 8)

  /RHWM Hidden Function: Reduce highwater mark by deleting empty SMP pages

Repair Action: (MAKE SURE DATABASE IS OFFLINE)

  /MI  Mark index as invalid (make sure db is offline) (See notes 1, 5)

  /MT  Mark table with drop-pending (unavailable) state.
       (See notes 1, 6, 9. Make sure db is offline.)

  /IP  Initialize data page of a table as empty.
       (See note 9. Make sure db is offline.)

  /ETS  Extends the table limit in a 4K DMS tablespace, if possible.
      (/ETS requires a tablespace id. See notes 1, 2)

Change State Action: (MAKE SURE DATABASE IS OFFLINE)

  /CHST Change a state of the database.

Input values options:

  /OI object-id    Object ID.

  /TN table-name    Table name.

  /TSI tablespace-id  Tablespace ID.

  /ROW sum       (2) Check LF/LOB descriptors
             (1) Check control info of varying types in rows.
             (see note 10)

  /PS number      Page number to start with.
             (suffix page number with 'p' for pool relative)

  /NP number      Number of pages.

  /V Y/N        Y or N for verbose option.

  /PW password     Contact DB2 service for valid password.

  /RPT path      Optional path to place report output file.

  /RPTN file-name   Optional name for report file.

  /SCR Y/M/N      (Y) normal output produced to screen.
             (M) minimize output to screen.
             (N) no output to screen.

  /RPTF Y/E/N     (Y) normal output produced to report file.
             (E) only error information to report file.
             (N) no report file output.

  /ERR Y/N/E      (Y) normal information log DART.INF file.
             (N) minimize output to information log
               DART.INF file.
             (E) minimize DART.INF file output, minimize screen
             output, only error information to report file.
             Default (Y).

  /WHAT DBBP OFF/ON  Database backup pending state.
             (OFF) off state.
             (ON) on state.
 

Notes:

1- For actions that require additional input values for identifying the data
  to act on, the input values can be specified as arguments along with the
  action, or if not specified then you will be prompted for input values.
  This does not apply for actions /DDEL and /IP, the input values required
  will be prompted for.

2- Actions /TSC, /TS, /ETS, and /DHWM require 1 input value - the
  tablespace ID.

3- Actions /T and /DEMP require two input values consisting of
  tablespace ID, and either of table object ID or table name.

4- Actions /DD, /DM and /DI require five input values consisting of either
  table object ID or table name, tablespace ID, page number to start with,
  number of pages, and verbose choice.

5- Action /MI requires two input values consisting of tablespace ID and
  index object ID.

6- Action /MT requires three input values consisting of either table object ID
  or table name, tablespace ID, and password.

7- Action /DP requires three input values consisting of tablespace ID (DMS
  tablespace only), page number to start with, and number of pages.

8- Action /LHWM requires a tablespace ID and the number of pages for the
  desired highwater mark after lowering it.

9- For password required by some actions, please contact DB2 service.

10- For value options where unique values identify different choices for
  the option, sum up the values to get the combination of choices.

11- Default location for report output file is the current directory in a
  non-MPP environment, and in the diagnostic directory in a MPP environment.

12- The scope of db2dart is single node.

13- In a MPP environment, you can use db2_all to invoke db2dart at all DB2
  logical nodes in a single invocation.

Suggestions to reduce tablespace High Water Mark

1. enter an DB2 environment
2. connect to a database
3. execute command ...
   db2 list tablespace
   note the id of the tablespace of which you want to reduce the high water mark, then disconnect
4. stop all applications using the database
5. deactivate the database using ...
   db2 deactivate database <db>
6. backup the database you want to practice the HWM reduction
7. start the db2dart utility
   db2dart <db> /LHWM /TSI <ts id> /np 0
8. analyze output file DART0000/<db>.RPT stored in the DB2 diagnose directory ...
• follow the instructions given, for example ...
  Step #60: Object ID = 3856
   => Offline REORG of this table using the LONGLOBDATA option (do not
    specify a temporary tablespace).
• always execute the suggested reorg utilitiy in „in-place“ mode, do not specify a temporary tablespace
• sometimes the suggestion is to export the table, drop, create und import the table data.
• use LONGLOBDATA option to include long LOB columns which are excluded by default. If the table contains LOB columns that do not use the COMPACT option, the LOB DATA storage object can be significantly larger following table reorganization. This can be a result of the order in which the rows were reorganized, and the types of table spaces used (SMS or DMS).
9. backup the database again
10. start your applications

Suggestions to reduce „index“-space High Water Mark

To reduce th high water mark in tablespace containing indexes, called „index“-spaces in this document, use db2dart or an alternative method.

• For indexes db2dart suggests: Drop all indexes for parent table of this object, disconnect, reconnect, and then recreate them.
• If this is not possible REORG the indexes stored in the tablespaces. Try following!
• determine indexes stored in a tablespace using SQL:
  SELECT DISTINCT TBCREATOR, TBNAME
  FROM  SYSIBM.SYSINDEXES
  WHERE TBSPACEID = n ;
• Reorg the indexes:
  REORG INDEXES ALL FOR TABLE tbcreator.tbname [CLEANUP ONLY ALL]
  You may alternatively omit the CLEANUP option to improve object performance. But then you should into account take an increasing tablespace size.
  The CLEANUP ONLY ALL option will free committed pseudo empty pages, as well as remove committed pseudo deleted keys from pages that are not pseudo empty. This option will also try to merge adjacent leaf pages if doing so will result in a merged leaf page that has at least PCTFREE free space on the merged leaf page, where PCTFREE is the percent free space defined for the index at index creation time. The default PCTFREE is ten percent. If two pages can be merged, one of the pages will be freed. The number of pseudo deleted keys in an index, excluding those on pseudo empty pages, can be determined by running runstats and then selecting the NUMRIDS DELETED from SYSCAT.INDEXES. The ALL option will clean the NUMRIDS DELETED and the NUM EMPTY LEAFS if they are determined to be committed.
• The CLEANUP option only merges and frees empty index pages but does not move index pages to lower the high water mark. This can only be achived with a DROP or REORG command.
  Next appy following command to reduce the high water mark:
  REORG INDEXES ALL FOR TABLE tbcreator.tbname

Ressources

• IBM DB2 V8.2 Command Reference, db2dart utility: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/c0010412.htm
• IBM Support Note „Lowering the High-Water Mark of a Tablespace“ (2003): http://www-1.ibm.com/support/docview.wss?rs=71&context=SSEPGG&q1=high+water+mark&uid=swg21006526&loc=en_US&cs=utf-8&lang=en
• IBM Support Note „Highwater mark in the list tablespaces show detail not being RESET after a RUNSTATS or REORGS“ (2003): http://www-1.ibm.com/support/docview.wss?rs=71&context=SSEPGG&q1=high+water+mark&uid=swg21007334&loc=en_US&cs=utf-8&lang=en
• IBM Support Note „SQL20170N when decreasing the size of a table space“ (2006): http://www-1.ibm.com/support/docview.wss?rs=71&context=SSEPGG&q1=high+water+mark&uid=swg21234267&loc=en_US&cs=utf-8&lang=en
• SAP Support Portal: Note number 543897, 152531 and 486559