Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2007
    Posts
    246

    Unanswered: select statements

    Hi

    Trying to create a script which will generate alter statements to reduce the space

    db2 -x "select 'alter tablespace '||tbsp_name||' reduce (all '||rtrim(char(tbsp_total_pages))||' - '||rtrim(char(tbsp_page_top))||' k ) ; ' from sysibmadm.tbsp_utilization where tbsp_name = 'USERSPACE1'

    output

    alter tablespace userspace1 reduce (all 4096 - 1824 k ) ;

    tried like this but not working '||char(tbsp_total_pages) - char(tbsp_page_top)||'

    but want to get the final figure

    alter tablespace userspace1 reduce (all 2272 k) ;


    Regards
    Paul

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

    I use 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='USERSPACE1'
    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
  •