How would you create and alter tablespace in Db2?

You can modify a table space 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 you create a table space in Db2?


  1. The CREATETS privilege for the database.
  2. DBADM, DBCTRL, or DBMAINT authority for the database.
  3. SYSADM or SYSCTRL authority.
  4. System DBADM.
  5. Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)

What is DMS tablespace in Db2?

In a DMS (database managed space) table space, the database manager controls the storage space. Unlike SMS table spaces, storage space is pre-allocated on the file system based on container definitions that you specify when you create the DMS table space.

What are Db2 tablespaces?

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 .

How do you create space in a table?

Use the CREATE TABLESPACE statement to create a tablespace, which is an allocation of space in the database that can contain schema objects. A permanent tablespace contains persistent schema objects. Objects in permanent tablespaces are stored in datafiles.

What is alias in Db2?

An alias is a substitute for the three-part name of a table or view. An alias can be defined at a local server and can refer to a table or view that is at the current server or a remote server. The alias name can be used wherever the table name or view name can be used to refer to the table or view in an SQL statement.

What is Segsize in Db2?

SEGSIZE will be used to define the table space as segmented. If the SEGSIZE value provided during the declaration of table space, then the table space is segmented table space.

What is difference between tablespace and datafile?

An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database’s data. Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.

How do I add a datafile to tablespace?

To add datafiles to a tablespace, use either the Add Datafile dialog box of Enterprise Manager/GUI, or the SQL command ALTER TABLESPACE. You must have the ALTER TABLESPACE system privilege to add datafiles to a tablespace.

What does the create tablespace statement do?

The CREATE TABLESPACE statement defines a new table sypace within the database, assigns containers to the table space, and records the table space definition and attributes in the catalog.

How do I change the name of a table in DB2?

Use DB2® Control Center, DB2 Administration Tool for z/OS®, or catalog queries to determine the dependent objects: views, authorization, plans, packages, synonyms, triggers, referential integrity, and indexes. Drop the original table. Rename the new table to the name of the old table using RENAME TABLE. Re-create all dependent objects.

What is a table space in DMS?

For a DMS table space, identifies one or more containers that will belong to the table space and in which the table space data will be stored. The type of the container (either FILE or DEVICE) and its size (in PAGESIZE pages) are specified.

How to create a user temporary table space in a database?

No user temporary table spaces exist when a database is created. To allow the definition of created temporary tables or declared temporary tables, at least one user temporary table space should be created with appropriate USE privileges. Names the table space. This is a one-part name.