Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2013
    Posts
    34

    Unanswered: space creation from unused pages of tablespace

    Hi,

    My db2 database version is "DB2 v9.1.0.10" Fix Pack 10.

    In our production server there is huge table which is partitioned. The table is partitioned by month.

    Means every month we are adding the tablespace to the table(partition by means 1st day of month to last day) .

    My question is how to free unused space of the tablespace which is no longer use.

    e.g the tablespace name is ABCD_TS whose total pages are 16034000 and now free pages are 1215264.

    So how to free these 1215264 pages?

    Thanks in advance.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    alter tablespace .. resize...
    see infocenter for detailed command
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Sep 2013
    Posts
    34
    Got the solution thanks.

    But how to calculate the space is not clear to me.

    Do we need to consider just some more space than used pages or is there any proper formula for this?

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    It's possible to arrange for each new range-partition to occupy a dedicated tablespace, easy with automatic storage.
    This has the advantage of simpler space recovery following rollout of the oldest ranges (detach, archive, drop table, drop tablespace).

  5. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    what's the result of the following query?
    Code:
    SELECT 'ALTER TABLESPACE '||p.TBSP_NAME||' REDUCE '
    ||
    CASE t.TBSP_USING_AUTO_STORAGE
      WHEN 1 THEN RTRIM(CHAR(p.tbsp_usable_pages-p.tbsp_page_top))
      ELSE '(ALL '||RTRIM(CHAR((p.tbsp_usable_pages-p.tbsp_page_top)/nullif(p.tbsp_num_containers, 0)))||')'
         ||' ON DBPARTITIONNUM ('||RTRIM(CHAR(P.DBPARTITIONNUM))||')'
    END
    FROM SYSIBMADM.SNAPTBSP_PART P
    JOIN SYSIBMADM.SNAPTBSP T ON P.TBSP_NAME=T.TBSP_NAME AND P.DBPARTITIONNUM=T.DBPARTITIONNUM
    WHERE T.TBSP_TYPE='DMS' 
    AND P.TBSP_NAME='ABCD_TS';
    Regards,
    Mark.

Posting Permissions

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