Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2002
    Posts
    76

    Unanswered: Determine dms space free percentage from SQL

    Is there a way to programmatically check a tablespace to see if dms space is running out?

    I was hoping container information is in a syscat table somewhere, but I dont see it.

    I can get the tablespace ID from syscat.tablespaces but, is there anywhere for container details besides doing list tablespace container for ## show detail ?


    Thanks
    Al

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Determine dms space free percentage from SQL

    There is no direct way ....

    If you are desperate, I hope you can consider doing a User-Defined Function ... Good Luck ...

    Cheers

    Sathyaram

    Originally posted by akratz
    Is there a way to programmatically check a tablespace to see if dms space is running out?

    I was hoping container information is in a syscat table somewhere, but I dont see it.

    I can get the tablespace ID from syscat.tablespaces but, is there anywhere for container details besides doing list tablespace container for ## show detail ?


    Thanks
    Al

  3. #3
    Join Date
    Aug 2002
    Posts
    76
    I think desparate might be the right word for it, I want to be able to sleep at night again......

    I'm thinking maybe java and parsing the results of the list tablespace container command,

    in a quick overview, what was your thought on a user defined function?

    Thanks!
    Al

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If you are happy about a means to ease your work, then do the java parsing as you think ....

    If you want the result in an SQL query (for some reason), use a java udf (no major change to the above code), define a UDF ..

    And , if you are succesful in either of the above, I'll be glad if you can share the code ...

    Cheers

    Sathyaram

    Originally posted by akratz
    I think desparate might be the right word for it, I want to be able to sleep at night again......

    I'm thinking maybe java and parsing the results of the list tablespace container command,

    in a quick overview, what was your thought on a user defined function?

    Thanks!
    Al

  5. #5
    Join Date
    Aug 2002
    Posts
    76
    Definitely will share, thanks for the help!

  6. #6
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170
    Yes if your program can call Shell script.
    Include the command List tablespces show detail in your shell script

  7. #7
    Join Date
    Apr 2003
    Location
    P.R.China
    Posts
    6
    write a shell to use "list tablespaces show detail" to get the current state,look at total pages, usable pages and used pages, then you can calculate the percentage used by tables.

  8. #8
    Join Date
    Aug 2002
    Posts
    76

    Code and Scripts

    Here we go: Below is a Java program and script to perform automated monitoring of DMS space utilization.

    The Java program takes 2 parms: 1 is the file name of a text file with output from the db2 list tablespaces show detail command and the 2nd parm is the space threshold you want to consider excessive. For example, I am also going to insert code in my program that pages my cell phone, and I can determine what percentage to trigger that. My example here in #2 uses 90 %.

    You can see this code better once you paste it into notepad or something. It is very basic java.


    #1 ) cut , paste and compile the following class: i.e. javac SpaceChecker.java

    *** Copy text , next line down to the stop copy text and paste into a file called SpaceChecker.java ***

    import java.io.* ;

    public class SpaceChecker {

    public static void main(String[] args) throws IOException {

    BufferedReader fileIn = new BufferedReader( new FileReader(args[0]));

    String recordIn = "" ;
    String tableName = "" ;
    String usable = "" ;
    String used = "" ;
    String free = "" ;
    int troubles = 0 ;
    float threshold = (float) Integer.valueOf(args[1]).intValue() ;


    while ((recordIn = fileIn.readLine()) != null) {
    if (recordIn.startsWith(" Name")) {
    tableName = recordIn.substring(40,recordIn.length());
    }

    if (recordIn.startsWith(" Useable")) {
    usable = recordIn.substring(40,recordIn.length());
    }

    if (recordIn.startsWith(" Used")) {
    used = recordIn.substring(40,recordIn.length());
    }

    if (recordIn.startsWith(" Free")) {
    free = recordIn.substring(40,recordIn.length());

    }

    if (recordIn.startsWith(" High") ) {
    if (free.startsWith("Not applicable")) {
    //bypass SMS tablespaces
    }
    else {
    int usableInt = Integer.valueOf(usable).intValue() ;
    int usedInt = Integer.valueOf(used).intValue();
    float spacePercent = ((float) usedInt / (float) usableInt) * 100 ;
    int test = usableInt + 10 ;
    int test2 = usedInt + 10 ;
    if (spacePercent > threshold) {
    System.out.println("TS:" + tableName.trim() + " PCT USED:" + (int)spacePercent + " FREE PAGES:" + free.trim());
    troubles++;
    }
    }

    }

    }

    if (troubles > 0) {
    System.out.println("Sending Page..." + troubles + " tablespaces exceeded threshold...");
    //insert page code here
    }

    }

    }


    **** stop copy 1 line above ******


    #2) Copy the following text to a batch file (space.sh for unix/linux or space.bat for windows)

    db2 connect to dbname user name using pword
    db2 list tablespaces show detail > tspace.txt
    java SpaceChecker tspace.txt 90 > space.out


    now I am going to have a cronjob run this on unix every morning or so, and I wont have to remember to manually check space.

  9. #9
    Join Date
    Sep 2002
    Posts
    41
    It's really cool script !!

    I have similar korn shell script to list DMS tablespaces free spaces and other details in summarise format.

    Copy following code between STARTS and ENDS and paste into file with spaceused.ksh.

    It's work good for Solaris. Other Unix flavoue may require slight modifications.


    ************ STARTS HERE ****************


    #!/bin/ksh
    #####################################
    # spaceused.ksh
    # show space used on dms containers
    #####################################
    dbname=`ckstr -l 8 -p "please enter database name"`

    usr=`ckstr -l 8 -p "please enter user"`

    passwd=`ckstr -l 8 -p "please enter password"`

    db2 connect to $dbname user $usr using $passwd
    RC=$?
    if [ $RC -eq 0 ]
    then
    db2 list tablespaces show detail >spacetst.out
    db2 connect reset
    else
    echo "Cannot connect to database. Please try again"
    exit
    fi
    cat spacetst.out | while read input
    do
    ###############################################
    #### get tablespace name
    ##############################################
    if echo $input |grep Name > /dev/null
    then
    tsname=`echo $input |grep Name| awk '{print $3;}' `
    echo "+++++++++++++++++++++++++++++++++++++++++++++ "
    echo "tablespace name " ${tsname}
    fi
    ###############################################
    #### get total pages allocated
    ###############################################
    if echo $input |grep "Total pages" > /dev/null
    then
    totpage=`echo $input |grep "Total pages" |awk '{print $4;}' `
    echo "totpage " ${totpage}
    fi
    ###############################################
    #### get useable pages
    ###############################################
    if echo $input |grep "Useable pages" > /dev/null
    then
    userpage=`echo $input |grep "Useable pages" | awk '{print $4;}' `
    echo "useable pages " ${userpage}
    fi
    ###############################################
    #### get pages used
    ###############################################
    if echo $input |grep "Used pages" > /dev/null
    then
    pageused=`echo $input |grep "Used pages" | awk '{print $4;}' `
    echo "used pages " ${pageused}
    fi
    ###############################################
    #### get free pages
    ###############################################
    if echo $input |grep "Free pages" > /dev/null
    then
    pagefree=`echo $input |grep "Free pages" | awk '{print $4;}' `
    echo "free pages " ${pagefree}
    freesp=`expr ${pagefree} \* 100 / ${userpage} `

    if [ ${freesp} -lt 20 ]
    then
    echo "********** Alert.. tablespace has less than 20% free space*****"
    else
    echo ${freesp} "% free space remaining"
    fi
    fi
    ###############################################
    #### get number of containers
    ###############################################
    if echo $input |grep "Number of containers" > /dev/null
    then
    containers=`echo $input |grep "Number of containers" | awk '{print $5;}' `
    echo "containers " ${containers}
    fi
    done


    *********** END HERE *********************


    Thanks
    Kuckoo

  10. #10
    Join Date
    Jul 2003
    Posts
    4

    Lightbulb Re: Determine dms space free percentage from SQL

    In the thread "How to get total/used/free pages for a tablespace via
    JDBC?", PM provided the best solution for this problem:

    -------------------------- From PM ---------------------------------------------
    If the server is v8.x

    SELECT
    TABLESPACE_NAME, TOTAL_PAGES, USABLE_PAGES, USED_PAGES, FREE_PAGES
    FROM TABLE( SNAPSHOT_TBS_CFG(CAST(NULL AS VARCHAR(255)), -2) ) A

    SNAPSHOT_CONTAINER(...) for container details
    ---------------------------------------------------------------------------------

    We all should thank PM for his/her help!

    SjehYoung

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Determine dms space free percentage from SQL

    This works in V8 only...

    Cheers
    Sathyaram


    [
    QUOTE]Originally posted by SjehYoung
    In the thread "How to get total/used/free pages for a tablespace via
    JDBC?", PM provided the best solution for this problem:

    -------------------------- From PM ---------------------------------------------
    If the server is v8.x

    SELECT
    TABLESPACE_NAME, TOTAL_PAGES, USABLE_PAGES, USED_PAGES, FREE_PAGES
    FROM TABLE( SNAPSHOT_TBS_CFG(CAST(NULL AS VARCHAR(255)), -2) ) A

    SNAPSHOT_CONTAINER(...) for container details
    ---------------------------------------------------------------------------------

    We all should thank PM for his/her help!

    SjehYoung
    [/QUOTE]
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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