Alter Tablespace in DB2

RESIZE TABLE SPACE SIZE:

 

]$ db2 "ALTER TABLESPACE TSPNAME1 RESIZE(FILE '/home/itlokam/TSP1.dat' 9000)"

REDUCE TABLE SPACE SIZE:

]$ db2 "ALTER TABLESPACE TSPNAME1 REDUCE (FILE '/home/itlokam/TSP1.dat' 4096)"

CONTAINERS:

            A container is a physical storage device. It can be identified by a directory name, a device name or a file name.
            A container is assigned to a table space. All databases and table data is assigned to tablespaces.
            A single table space can span many containers. but each container belong to only one table space.
            A Conatainer can only be added to a DMS table space by using ALTER TABLESPACE statement.. Conatainers cannot be added to an SMS table space.
 

Container SIZE = (Extent SIZE in pages * n) + 1

           Where Extent size is the size of each extent for the table space.

           'n' is the number of extents you want to store in the container.
Note: Minimum of 5 extents for a DMS table space.

ADDING CONTAINERS TO DMS TABLE SPACE:

              You can add a containers to an existing table space to increase its storage capacity with the ALTER TABLESPACE statement.
              You should check how full the containers are by using the LIST TABLESPACES and LIST TABLESPACE CONTAINERS commands.

ex:  ALTER TABLESPACE TSPNAME1 ADD (FILE '/home/itlokam/TSP1.dat' 2000)

         PREFETCHSIZE 16 BUFFERPOOL BPCERT4K

No. of DB2 Commands applying on Particular Database:

]$ db2 describe table syscat.tablespace

]$ db2 describe table syscat.tables // It display the system tables and user defined tables.
]$ db2 list tablespaces // It display the TSPID, Name, Type, Contents, State.

]$ db2 list tablespaces show detail
// The above statement displays the TSPID, Name, Type, Contents, State, Total pages, Usable pages, Used pages, Free pages, High water mark (pages), Pagesize(bytes), Extent size (pages), Number of containers.

 ]$ db2 list tablespace containers for <TableSpaceId>

//The above statement displays the COntainerID, Name(it will show full path), Type.

 ]$ db2 list tablespace containers for <TableSpaceId> show detail

//The above statement displays the ContainerID, Name(it will show full path), Type, Total pages, Usable pages, Accessible.

]$ db2 get snapshot for tablespaces on sample

// The above statement displays the all information tablespaces related to particular database.

EXTENTSIZE:

An extent is a unit of space with in a container of a table space. Database objects are stored in pages with in DB2( except for LOBs and long varchars). These pages are grouped into all allocation units called extents.

The extent size is defined at table space level. Once the extent size is established for the table space, it cannot be altered. The db cfg parameter is DFT_EXTENT_SIZE, range is 2 to 256 pages. (for ex: 8-1048 KB for 84 KB pages (or) 16-2048 KB for 8 KB pages). This figure can be overridden by using the EXTENTSIZE parameter in the CREATE TABLESPACE statement. Although this parameter can be specified in Kilobytes (or) megebytes,

Note: Extent Size = (whole number of pages by taking the floor of the number of

bytes)/Pagesize.

 

PAGESIZE:

Table data are organized into blocks called pages. Pages can be four sizes 4, 8, 16 and 32 KB.

The maximum number of table space that uses 4 KB pages is 500 columns; for 8, 16, 32 KB pages is 1012 columns.

PAGESIZE(in kb) = 4096 bytes = 4kb Note: 1KB = 1024 Bytes