types of tablespaces - Brief explanation of each typesimple- a simple tablespace can contain multiple table.A simple table space can contain more than one table, but the rows of different tables are not kept separate.segmented-A segmented table space is can hold more than one table. The available space is divided into groups of pages called segments each the same size. Each segment contains rows from only one table. Partitioned-the available space is divided into separate units of storage called partitions, each containing a part of one table.universal-it is the combination of both segmented and partitioned tablespace partition by range segsize and numparts parameter is specified in order to �create this TS. partition by growth - segsize and Maxpartition parameter is specified in order to create this TS.LOB this type of TS is used to store large object like graphics, video etc�XML stores xml data�Adantages of Segmented tablespacesConcurrent access is possible as each segment stores only one tableReorg is not necessary when a table is dropped.Adantages of Simple tablespacesCan contain multiple related table.Disadantages of Simple tablespacesTo find all the rows of one table can require scanning the entire table space.If a table is dropped, its rows are not deleted. The space occupied by the rows does not become available until the table space is reorganized.Why does a partitioned tablespace contain only 1 tableSo that multiple utility can be applied on different partition of the same tableDifference between Index controlled partitioning & table controlled partitioningNPIDPSI and its advantagesWhat is a clustering indexCan I create a table with indexes of which none of them are clustering ? justifyhow to decide the bufferpool of a tablespace?It depends on the page size of the tablespace. whether it is 4k,8k,16k,32k What is a identity column
Identity column is the column in the table which generates unique value whenevera new row is inserted in the table.GENERATED: Specifies that DB2 generates values for the column.-ALWAYS: Specifies that DB2 will always generate a value for the column when a row is inserted into the table-BY DEFAULT : Specifies that DB2 will generate a value for the column when a rowis inserted into the table unless a value is specifiedWhat is a sequenceIt is a DB2 object which is used to generate unique values for the respective column in the table.Difference between a identity colulmn and a sequenceIf a table is dropped, identity column is also dropped but sequence remains as it is.