Table space is a database object where actually the table data gets stored. It is a like child of the database, where there can be multiple table spaces within a database. Each table space contains the ‘containers’. There can be one or more containers per table space. Containers can be added/dropped to TS to increase the size, if it is a DMS managed TS.
Table space will be represented as pages and data will be stored in the form of pages. more than one table can be stored in the table space. page contains all the table rows in which order they inserted into the table. the rows from different tables can be stored in one page. there is no order defined to store the data on the pages. CREATE TABLESPACE is used to create the table space. There are different types of table spaces depending upon their usage and management type.
Syntax
CREATE TABLESPACE tablespace-name
IN database-name
USING STOGROUP stored-group-name
PRIQTY priqty-value
SECQTY secqty-value
ERASE {NO/YES}
FREEPAGE freepage-value
PCTFREE pctfree-value
COMPRESS {YES/NO }
BUFFERPOOL bufferpool-value
LOCKSIZE {ANY/locksize-value}
LOCKMAX {SYSTEM/lockmax-value}
CLOSE {YES/NO}
Example
Below example is to create MFTHTS table space in MTH1DB under the storage group MFTCHP.
CREATE TABLESPACE MFTHTS
IN MTH1DB
USING STOGROUP MFTCHP
PRIQTY 250
SECQTY 100
ERASE NO
FREEPAGE 10
PCTFREE 10
COMPRESS NO
BUFFERPOOL MF
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
If you have any simple table spaces in your database, alter them to a preferred type of table space with the ALTER TABLESPACE statement. If a simple table space contains only one table, alter it to a universal table space.
You cannot create simple table spaces, but you can alter data, update data, or retrieve data from simple table spaces. If you implicitly create a table space or explicitly create a table space without specifying the SEGSIZE, NUMPARTS, or MAXPARTITIONS options, DB2 creates a segmented table space instead of a simple table space. By default, the segmented table space has a SEGSIZE value of 4 and a LOCKSIZE value of ROW.
A simple table space is neither partitioned nor segmented. The creation of new simple table spaces is not supported in DB2® 10. However, DB2 can still use existing simple table spaces.
Simple table spaces are found mostly in older DB2 applications. A simple table space can contain one or more tables. When multiple tables are defined to a simple table space, a single page can contain rows from all the tables defined to the table space.
Prior to DB2 V2.1, most DB2 table spaces were defined as simple table spaces because the only other option was a partitioned table space. However, most subsequent applications use segmented table spaces because of their enhanced performance and improved methods of handling multiple tables.
If an application must read rows from multiple tables in a predefined sequence, however, mixing the rows of these tables together in a single simple table space can prove to be beneficial. The rows should be mixed together on the page in a way that clusters the keys by which the rows will be accessed. This can be done by inserting the rows using a "round robin " approach, switching from table to table, as follows -
NO 1 − Create a simple table space; this is accomplished by issuing the CREATE TABLESPACE statement without specifying either the SEGSIZE or NUMPARTS clause.
NO 2 − Create the two tables (for example, Table1 and Table2), assigning them both to the simple table space you just created.
NO 3 − Sort the input data set of values to be inserted into Table1 into key sequence order.
NO 4 − Sort the input data set of values to be inserted into Table2 into sequence by the foreign key that refers to the primary key of Table1.
NO 5 − Code a program that inserts a row into Table1 and then immediately inserts all corresponding foreign key rows into Table2.
NO 6 − Continue this pattern until all of the primary keys have been inserted.
When the application reads the data in this predefined sequence, the data from these two tables will be clustered on the same (or a neighboring) page. Great care must be taken to ensure that the data is inserted in the proper sequence. Keep in mind that any subsequent data modification ( INSERT s, UPDATE s that increase row size , and DELETE s) will cause the data to get out of sequence ”and then performance will suffer. For this reason, this approach is more useful for static data than it is for dynamic, changing data.
Also, remember that mixing data rows from multiple tables on the same table space page adversely affects the performance of all queries, utilities, and applications that do not access the data in this manner. Be sure that the primary type of access to the data is by the predefined mixing sequence before implementing a simple table space in this manner. Unless data-row mixing is being implemented, define no more than one table to each simple table space. Also, consider defining all your non-partitioned table spaces as segmented instead of simple.
Types of Table Spaces
When designing DB2 databases, DBAs can choose from four types of table spaces, each one useful in different circumstances. The four types of table spaces are -
Simple table spaces
Segmented table spaces
Partitioned table spaces
LOB table spaces