What is high water mark in Db2?

What is high water mark in Db2?

The high water mark refers to the page number of the first page in the extent following the last allocated extent. For example, if a table space has 1000 pages and an extent size of 10, there are 100 extents.

How do I reduce the size of my tablespace?

Decrease the size of the tablespace

1. Reorganize the objects in the tablespace.
2. Create a new tablespace and migrate all objects to the new tablespace. Then, drop the old tablespace.

How do I reduce the size of my Db2 database?

You can use any of the following options:

1. ALTER TABLESPACE statement with a REDUCE option. Reduces the Pending free extent.
2. ALTER TABLESPACE statement with a REDUCE option (with size specified) Reduces pending free extents and other free extents by a specified size.
3. ALTER TABLESPACE statement with REDUCE MAX option.

How would you create and alter tablespace in Db2?

You can modify a tablespace in the following ways:

1. Add a container to, or drop a container from a DMS table space; that is, a table space created with the MANAGED BY DATABASE option.
2. Modify the size of a container in a DMS table space.
3. Lower the high water mark for a DMS table space through extent movement.

How do I resize a tablespace in ASM?

Identify the datafiles for the tablespace you want to increase. SQL> alter database datafile ‘+DATA/SID/datafile/app_data. 259.669898683’ resize 30000m; Tablespace altered.

What is bottleneck in DB2?

Having any one resource constrained too much and everything slows down. You can have more CPU cycles available than you’ll ever need, but if your IO subsystem can’t keep up with the work, it becomes a bottleneck and you’ll never achieve your desired throughput.

How do I find the size of a DB2 database?

How to calculate the size of a DB2 database

1. Connect to the database for which you want to know the size and maximum capacity.
2. Execute the procedure GET_DBSIZE_INFO.
3. Close the connection.

How many types of tablespaces are there in DB2?

Each database must contain at least three tablespaces as given here: Catalog tablespace. User tablespace. Temporary tablespace.

How do I update tablespace?

The tablespace file clauses let you add or modify a datafile or tempfile. Specify ADD to add to the tablespace a datafile or tempfile specified by file_specification ….ALTER TABLESPACE

1. Take the tablespace online or offline.
2. Begin or end a backup.
3. Make the tablespace read only or read write.

How do you find the maximum size of a tablespace?

So the right query is: select TABLESPACE_NAME, sum(decode(AUTOEXTENSIBLE, ‘YES’, MAXBYTES, BYTES)) MAX_SIZE from DBA_DATA_FILES group by TABLESPACE_NAME; This has been tested on 11g but it should also work on 10g. It gives you the maximum size of each tablespace in bytes.

How can we improve performance of select query in db2?

Ways to improve query performance

1. Scan the PARTS table for every row that matches a row in the PRODUCTS table.
2. Scan the PRODUCTS table for every row that matches a row in the PARTS table.
3. Sort both tables in PROD# order; then merge the ordered tables to process the join.

How do you determine database size from OS level?

DB size can be verified from the transaction DB02. If you want to see the allocated file system size at OS level, then yes it is the same as the size allocated (used space out of total space of that particular file system) to /oracle/SID/sapdata*.

What are tablespaces in Db2?

A Db2 table space is a set of volumes on disks that hold the data sets in which tables are actually stored. Every table is stored in table space. A table space consists of a number of VSAM linear data sets. Table spaces are divided into equal-sized units, called pages .