How to get available tablespaces

I am working on one assignment, which checks and provides total number of available tablespaces in informix. I don't need command line utilities. I need to fire query and get results. Facing some problem in getting exact results I fire query: 1. SELECT unique dbinfo("DBSPACE",partnum) dbspace FROM sysdatabases; It returns tablespaces created by informix administrator. 2. Select name, sum(nfree * 2/1024) from sysdbspaces d, syschunks c where d.dbsnum = c.dbsnum and (nfree * 2/1024) > 3000 group by 1 order by 1; It returns total tablespaces along with space available in them. My main motive is to get total number of tablespaces created by informix administrator and their free available disk space. Please help me how to get it in one go

Try this........ #!/bin/ksh # # dbs : displays table / dbspace information. # # WARNING - This script assumes the size of a BLOBPAGE is 16 usage() { echo " USAGE : `basename $0` -d <dbname> [-t <table> | -s <dbspace>] `basename $0` -d database -dbspace information for all spaces `basename $0` -d database -t table name -stats for the space where the specified table resides `basename $0` -d database -s dbspace name -table details for the specified dbspace " exit 1 } _BLOBPAGE_=16 #set -vx onstat - >/dev/null 2>&1 if [ $? -ne 5 ] then echo "Database not in Online mode, exiting..." exit fi while getopts :d:t:s: _OPT_ do case ${_OPT_} in d) _DB_=$OPTARG ;; t|s) _NAME_=$OPTARG ;; *) usage ;; esac done if [[ $# -ne 2 && $# -ne 4 ]] then usage fi if [ ${_NAME_} ] then dbaccess ${_DB_} <<EOF >/tmp/dbs1.$$ 2>&1 SELECT "XXXXX",tabname,DBINFO('DBSPACE',partnum) FROM systables WHERE tabname MATCHES "${_NAME_}" AND tabtype='T' AND partnum != 0; SELECT "YYYYY",DBINFO('DBSPACE',partnum) FROM systables WHERE tabtype='T' AND partnum != 0 GROUP BY 1,2 UNION SELECT "YYYYY",sf.dbspace FROM systables st, sysfragments sf WHERE st.tabtype = 'T' AND st.partnum = 0 AND st.tabid = sf.tabid AND sf.fragtype = 'T'; EOF is_tab=`cat /tmp/dbs1.$$ | grep XXXXX | wc -l` is_tab_dbs=`cat /tmp/dbs1.$$ | grep XXXXX | awk '{print $3}'` is_dbs=`cat /tmp/dbs1.$$ | grep YYYYY | grep ${_NAME_} | wc -l` rm -f /tmp/dbs1.$$ if [ $is_tab -gt 1 ] then echo "\\n\\"${_NAME_}\\" is not a unique table/dbspace name.\\n" exit 1 fi if [ $is_tab -eq 1 ] then dbs_filter="AND dbspace=\\"$is_tab_dbs\\" GROUP BY 1, 2" else dbs_filter="" if [ $is_dbs -gt 0 ] then dbaccess ${_DB_} <<EOF 2>/dev/null SELECT tabname table, ti_nrows rows, ti_nptotal pg_tot, ti_npused pg_usd, ti_nextns xtnts FROM systables, sysmaster:systabinfo WHERE tabtype='T' AND partnum=ti_partnum AND DBINFO('DBSPACE',partnum) = "${_NAME_}" AND partnum != 0 ORDER BY ti_nextns DESC, tabname;EOF exit else echo "\\n\\"${_NAME_}\\" is not a valid table/dbspace.\\n" exit 1 fi fi else dbs_filter="GROUP BY 1 , 2 UNION SELECT name[1,18], tables, SUM(chksize), SUM(nfree), ROUND(((SUM(nfree)/SUM(chksize))*100),2) FROM sysmaster:sysdbspaces, sysmaster:syschunks, OUTER dbs_temp1 WHERE sysmaster:syschunks.dbsnum = sysmaster:sysdbspaces.dbsnum AND sysmaster:syschunks.is_blobchunk = 0 AND dbspace = name AND NOT EXISTS (SELECT dbspace FROM dbs_temp1 WHERE dbspace = name) GROUP BY 1, 2 UNION SELECT name[1,15], tables, ROUND(SUM(chksize/${_BLOBPAGE_}),0), SUM(nfree), ROUND(((SUM(nfree)/SUM(chksize/${_BLOBPAGE_}))*100),2) FROM sysmaster:sysdbspaces, sysmaster:syschunks, OUTER dbs_temp1 WHERE sysmaster:syschunks.dbsnum = sysmaster:sysdbspaces.dbsnum AND sysmaster:syschunks.is_blobchunk = 1 AND dbspace = name AND NOT EXISTS (SELECT dbspace FROM dbs_temp1 WHERE dbspace = name) GROUP BY 1, 2" fi dbaccess ${_DB_} <<EOF 2>/dev/null CREATE TEMP TABLE dbs_temp1 (dbspace CHAR (18), tables DECIMAL(4,0)) WITHNO LOG; INSERT INTO dbs_temp1 SELECT DBINFO('DBSPACE',partnum), COUNT(*) FROM systables WHERE tabtype='T' AND partnum != 0 GROUP BY 1; INSERT INTO dbs_temp1 SELECT sf.dbspace, COUNT(*) FROM systables st, sysfragments sf WHERE st.tabtype = 'T' AND st.partnum = 0 AND st.tabid = sf.tabid AND sf.fragtype = 'T' GROUP BY 1; SELECT dbspace, tables, SUM(chksize) pages_total, SUM(nfree) pages_free, ROUND(((SUM(nfree)/SUM(chksize))*100),2) pct_free FROM sysmaster:syschunks sc, sysmaster:sysdbspaces ss, dbs_temp1 dt WHERE sc.dbsnum=ss.dbsnum AND name=dt.dbspace $dbs_filter ORDER BY 5; EOF exit I obtained it some time back from a previous employer and added the ability to provide db name. It doesn't handle fragmented tables very well but is good for giving a nice list of dbspaces and their freespace Regards Colin There are 10 types of people in the world, those that understand binary and those that don't >From: "inpreet" <inpreet@gmail.com> >To: informix-list@iiug.org >Subject: How to get available tablespaces >Date: 27 Mar 2006 03:42:59 -0800 > >I am working on one assignment, which checks and provides total number >of available tablespaces in informix. I don't need command line >utilities. I need to fire query and get results. Facing some problem in >getting exact results >I fire query: >1. SELECT unique dbinfo("DBSPACE",partnum) dbspace FROM sysdatabases; >It returns tablespaces created by informix administrator. > >2. Select name, sum(nfree * 2/1024) from sysdbspaces d, syschunks c >where d.dbsnum = c.dbsnum and (nfree * 2/1024) > 3000 group by 1 order >by 1; > >It returns total tablespaces along with space available in them. > >My main motive is to get total number of tablespaces created by >informix administrator and their free available disk space. Please help >me how to get it in one go > >_______________________________________________ >Informix-list mailing list >Informix-list@iiug.org >http://www.iiug.org/mailman/listinfo/informix-list