Professional Web Applications Themes

How to get total/used/free pages for a tablespace via JDBC? - IBM DB2

Our application needs to monitor the amount of free spaces in DB2 tablespaces via JDBC. Since we cannot submit the DB2 command "list tablespaces show detail" via JDBC, and we couldn't find out system tables/views that contain such information, how can we do it without using a C function? A stored procedure would be acceptable. But, we are trying to avoid C as we need to support many different OS platforms. Thank you! SjehYoung PS: I will also send this message to [email]db2pwdtsus.ibm.com[/email] and will let you what they say. -- Posted via [url]http://dbforums.com[/url]...

  1. #1

    Default How to get total/used/free pages for a tablespace via JDBC?


    Our application needs to monitor the amount of free spaces in DB2
    tablespaces via JDBC. Since we cannot submit the DB2 command
    "list tablespaces show detail" via JDBC, and we couldn't find out
    system tables/views that contain such information, how can we do
    it without using a C function? A stored procedure would be acceptable.
    But, we are trying to avoid C as we need to support many different
    OS platforms.

    Thank you!

    SjehYoung

    PS: I will also send this message to [email]db2pwdtsus.ibm.com[/email] and will
    let you what they say.

    --
    Posted via [url]http://dbforums.com[/url]
    SjehYoung Guest

  2. #2

    Default Re: How to get total/used/free pages for a tablespace via JDBC?


    That's great!

    It worked, except that the page_size column we added to the query
    returned null values for some reason so we had to use a join with
    syscat.tablespaces.

    As to the table syscat.tables suggested by Powell, it should work but
    didn't. The columns npages and fpages have the value -1 for all our
    tablespaces. It looks like something is not quite right with our
    database setup.

    Thank y'all very much for the help!

    S'jeh

    --
    Posted via [url]http://dbforums.com[/url]
    SjehYoung Guest

  3. #3

    Default Re: How to get total/used/free pages for a tablespace via JDBC?

    For Version 8.1 after missing the tablespace info in the Control Center,
    I create a view like this:

    select substr(TABLESPACE_NAME,1, 18) as TABLESPACE_NAME,
    case when TABLESPACE_TYPE = 0
    then 'DMS'
    else 'SMS'
    end as TBS_TYPE,
    int(TOTAL_PAGES) as TOTAL_PAGES,
    int(TOTAL_PAGES*PAGE_SIZE/1024/1024) as TOTAL_MB,
    int(USED_PAGES) as USED_PAGES,
    int(USED_PAGES*PAGE_SIZE/1024/1024) as USED_MB,
    int(FREE_PAGES) as FREE_PAGES,
    int(FREE_PAGES*PAGE_SIZE/1024/1024) as FREE_MB,
    ((USED_PAGES * 100) / TOTAL_PAGES) as PERC_USED
    from table (snapshot_tbs_CFG(cast(null as varchar(20)), -2 )) as x
    ;

    Nice to have for a first look...

    Regards, Joachim

    "SjehYoung" <member32894dbforums.com> schrieb im Newsbeitrag
    news:3091739.1057774442dbforums.com...
    >
    > Our application needs to monitor the amount of free spaces in DB2
    > tablespaces via JDBC. Since we cannot submit the DB2 command
    > "list tablespaces show detail" via JDBC, and we couldn't find out
    > system tables/views that contain such information, how can we do
    > it without using a C function? A stored procedure would be acceptable.
    > But, we are trying to avoid C as we need to support many different
    > OS platforms.
    >
    > Thank you!
    >
    > SjehYoung
    >
    > PS: I will also send this message to [email]db2pwdtsus.ibm.com[/email] and will
    > let you what they say.
    >
    > --
    > Posted via [url]http://dbforums.com[/url]

    Joachim Müller Guest

Similar Threads

  1. Index, Tablespace and performance
    By alexandre::aldeia digital in forum PostgreSQL / PGSQL
    Replies: 0
    Last Post: January 2nd, 10:43 PM
  2. Replies: 0
    Last Post: June 10th, 10:52 PM
  3. # Pages used in tablespace for index
    By Erik Hendrix in forum IBM DB2
    Replies: 4
    Last Post: July 1st, 12:57 AM
  4. Replies: 11
    Last Post: January 9th, 07:46 PM
  5. Create tablespace in 9i
    By Timmy Sin in forum Oracle Server
    Replies: 0
    Last Post: December 24th, 06:07 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139