Hi ,
I'm using db2 9.7 on Ubuntu Linux. In spite of lot of data rows insertion, my table space used pages is showing same value. Actually I want to restrict the table space size and I have used below commands to create database & associate tablespace & tables.

db2 "CREATE DATABASE mydb ON /home/pooji/db2/dbpath1, /home/pooji/db2/dbpath2"

db2 "CREATE TABLESPACE TSPC1 MANAGED BY DATABASE USING (FILE 'home/pooji/db2/tspc1file' 2 M) AUTORESIZE YES INCREASESIZE 2 M MAXSIZE 4 M NO FILE SYSTEM CACHING DROPPED TABLE RECOVERY OFF"

db2 "CREATE TABLE DEPARTMENT ( DEPTNO CHAR(3) NOT NULL, DEPTNAME VARCHAR(36) NOT NULL, PRIMARY KEY (DEPTNO) )" IN TSPC1 INDEX IN TSPC1"

Used "db2 list tablespaces show detail" to get the used pages count before and after mass inserts into department table; The page count remains same. Any problem in above declarations ?

Do you have the output showing the freepages before and after the mass inserts ?

BEFORE
=========================================
Tablespace ID = 34
Name = TSTEST1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 512
Useable pages = 480
Used pages = 160
Free pages = 320
High water mark (pages) = 160
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1

AFTER
============================================
Tablespace ID = 34
Name = TSTEST1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 512
Useable pages = 480
Used pages = 160
Free pages = 320
High water mark (pages) = 160
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1

Hi,

Is quotation mark a typo in your post? Have you the checked the tablespace for your table department?
db2 "select tbspace, index_tbspace from syscat.tables where tabname='DEPARTMENT'"
Maybe you where checking the wrong tablespace.
What dou you mean by "lots of data rows"? 10, 1000 or 1000000 Rows?

Yeah! its a typo.

Inserted around 2500 rows

The table space name is showing correct one.

A lot of rows? Mass insert?
Try some more, and I mean more more.

Insert the 2500 rows many times and you should see your tablespace hit the limit

Finally, after 5000 rows insertion, used pages changed to 224

What I understood from this:
When a table is created in a tablespace; db2 allocates few pages for data. Once these initial pages consumed, it uses more.

Thanks, Solved the problem.

DB2 allocates multiple pages together, which is called an "extend". Also, DB2 reuses space of deleted rows.