Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2010
    Posts
    4

    Unanswered: Tablespace Used pages remain constant

    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 ?

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Do you have the output showing the freepages before and after the mass inserts ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Oct 2010
    Posts
    4
    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

  4. #4
    Join Date
    Jan 2010
    Posts
    335
    Hi,

    Quote Originally Posted by poojithas View Post
    db2 "CREATE TABLE DEPARTMENT ( DEPTNO CHAR(3) NOT NULL, DEPTNAME VARCHAR(36) NOT NULL, PRIMARY KEY (DEPTNO) )" IN TSPC1 INDEX IN TSPC1"
    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?

  5. #5
    Join Date
    Oct 2010
    Posts
    4
    Yeah! its a typo.

    Inserted around 2500 rows

    The table space name is showing correct one.
    Last edited by poojithas; 03-30-11 at 07:02.

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by poojithas View Post
    Inserted around 2500 rows
    A lot of rows? Mass insert?
    Try some more, and I mean more more.

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Insert the 2500 rows many times and you should see your tablespace hit the limit
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Oct 2010
    Posts
    4
    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.

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    DB2 allocates multiple pages together, which is called an "extend". Also, DB2 reuses space of deleted rows.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •