Subject: Space each table occupies
Newsgroups: comp.databases.informix

From: [email-masked] (VINAY GIDWANI)
Date: 23 Jun 1995 17:53:11 GMT

Is there a informix utility which can let me know the space each table
occupies?

Also from what I understand, in a table there may be some spaces which are
blank. Is it possible to know how much of the space of the table has 
actual data.

Thanks

Vinay.

From: [email-masked] (Clem Akins)
Date: 26 Jun 1995 15:08:05 -0400

You can use "tbcheck -cI -pI dbname > filename" to print a report of space
usage by table.  The interesting things for you are the "number of pages
used, allocated" rows.  RTFM about this command, there are lots of options.

A pet peeve of mine is how people worry about reclaiming "unused space".
IMHO, a table should occupy a fairly stable space, fluctuating between
purge cycles.  These cycles should be defined and planned for at the
onset of the project, not added in at year-end by the new DBA whose first
job is to get the database back up from its "unable to allocate next
extent" error.  Whew!

     __________________________________________________________________
     |         Clem Akins                 Standard Disclaimers Apply  |
     |Reynolds Metals Co, Alloys Plant     "Climb High, Cave Deep!"   |
     |  Muscle Shoals, Alabama USA        [email-masked] |
     |________________________________________________________________|

From: [email-masked] (Darin Melin)
Date: 26 Jun 1995 17:53:03 -0400

I had received an application from informix pertaining to determining
the table spaces allocated and sizing.  The code is listed below:

+----------------------------------------------------------------------------+
|                 |                                                          |
|     .     .     |                                                          |
|    ...   ...    | Darin Melin                                              |
|   ..... .....   | Software Engineer, DBA                                   |
|  ..   ...   ..  | Business Systems Integration Group                       |
| .      .      . | Semiconductor Products Sector - LATG                     |
|                 | 2200 West Broadway Road, MD M300                         |
|                 | Mesa, AZ 85202                                           |
|  Motorola, Inc. | [email-masked]        Phone:(602) 655-3627    |
|                 | [email-masked]        FAX:  (602) 655-2820    |
|                 | Director - Arizona Informix Users Group                  |
+----------------------------------------------------------------------------+
|                  'connectionLESS IS MORE' -- Data Broker                   |
+----------------------------------------------------------------------------+

			TBLUSAGE DOCUMENTATION

Consists of 3 files:

	FILENAME		DESC
	--------		----
	mk_runner		Builder for making the fglgo executable
	tblusage.4gl		Source code for tblusage program
	fgiusr.c		Source code for making the 4gl runner
	tbstat.c		Source code for reading "tbstat" data

Build notes
-----------

There is one change required within the source code.  The "tblusage.4gl"
program uses a variable "LET G_PAGE_SIZE".  Set this to your system
default page size (ie Sun 4.1.3 = 2048).  

!!!!	FAILURE TO SET THIS CORRECTLY WILL RESULT IN ERRONEOUS REPORTS  !!!!

Interpreted Build Instructions
------------------------------

	1-  Execute the mk_runner shell script
	2-  Use R4GL and compile the "tblusage.4gl" file as runable.

C-Compile Build Instructions
------------------------------

	1-  Compile the "tblusage.4gl" file as runable and link in tbstat.c.

Running Instructions
--------------------

RDS (r4gl):			./fglgo tblusage.4gi <dbname> KB

C-Compiled (i4gl) :		./tblusage.4gi <dbname> KB

Source Code
----------

---------- Cut here for START of "mk_runner" shell script ---------------

cfglgo fgiusr.c tbstat.c  -o fglgo -lm

---------- Cut here for START of "tblusage.4gl" shell script ---------------

##############################################################################
#  Module       :  tbl_usage.4gl
#  Description  :  The following functions allow a INFORMIX-TURBO or OnLine
#                  partition report to be generated.  The report displays 
#                  information to monitor the partition space usage for each 
#                  table within the database specified on the command line.  
#                  The data can be viewed in either pages or kilobytes.
#
#  Usage        :  tbl_usage <database name> <measurement display type> 
# 
#                      <database name>             - TURBO or OnLine database.
#
#                      <measurement display type>  - kb : kilobytes (default)
#                                                    pg : pages
#
#  Functions    :  get_cmd_args
#                  dsp_usage_msg
#                  chk_db_nam
#                  partition_drv 
#                  convert_info 
#                  update_statistic 
#                  partition_open 
#                  partition_close 
#                  partition_rpt 
#
#  SCCS Id No   :  %W%
#  Author       :  Pete Carpino (INFORMIX SOFTWARE, INC.)
#  Modified		:  JEM 7/13/94
#  Date         :  %H% %T%
#  Path         :  %P%
##############################################################################
 
GLOBALS
 
    DEFINE 
           gr_tbl_info  RECORD
                            tbl_id              INTEGER,
                            tbl_nam             CHAR(30),
                            part_num            INTEGER,
                            num_rows            INTEGER,
                            row_size            INTEGER,
                            init_ext_size       INTEGER,
                            next_ext_size       INTEGER,
                            num_page            INTEGER,
                            num_page_data       INTEGER,
                            num_page_used       INTEGER,
                            num_extent          INTEGER,
			    db_space_num	INTEGER
                        END RECORD,
 
           gr_tot       RECORD
                            nrows               INTEGER,
                            npage               INTEGER,
                            npage_data          INTEGER,
                            npage_used          INTEGER
                        END RECORD,
 
           G_PAGE       INTEGER,
           G_KBYTE      INTEGER,
           G_PAGE_SIZE  INTEGER,
           G_SIZE_TYPE  CHAR(10),
 
           g_db_nam     CHAR(20),
           g_dsp_typ    INTEGER,
 
           g_sel_stmt   CHAR(2048)
 
END GLOBALS

MAIN
 
    /* LET G_PAGE_SIZE = 4096           # Page size for the Sequent. */
    LET G_PAGE_SIZE = 2048           # Page size for the Sun.
 
    LET G_KBYTE = 1                   
    LET G_PAGE  = 2
 
    IF ( get_cmd_args () ) THEN
        CALL partition_drv ()
    ELSE
        EXIT PROGRAM ( 1 )
    END IF
 
END MAIN
 
#############################################################################
#  Function     :  get_cmd_args
#  Description  :  Checks the command line for a valid database name and
#                  sets the database as current.
#
#  Incoming Parameters  : <none>
#  Return Parameters    : sql_ok    -  sql status.
#  Impact Globals       : g_db_nam  -  database name.
#                         g_dsp_typ -  report display type.
#  Perform Screen       : <none>
#
#  Author      Date       Modification Description
#  PTC         05/01/90   Create.
#############################################################################
 
FUNCTION get_cmd_args ()
 
    DEFINE sql_ok        INTEGER,
           num_cmd_args  INTEGER,
           dsp_typ       CHAR(2)
 
    LET sql_ok = TRUE
    LET num_cmd_args = num_args ()
 
    IF ( num_cmd_args = 0 ) THEN
        LET sql_ok = FALSE
        CALL dsp_usage_msg ()
    ELSE
        LET g_dsp_typ = G_KBYTE           # Default display type to kilobytes.
        LET G_SIZE_TYPE = "KBytes"
 
        IF ( num_cmd_args = 2 ) THEN
            LET dsp_typ = arg_val ( 2 )
 
            IF ( UPSHIFT ( dsp_typ ) = "PG" ) THEN
                LET g_dsp_typ = G_PAGE    # Set display type to pages.
                LET G_SIZE_TYPE = "Pages"
            END IF
        END IF
 
        LET g_db_nam = arg_val ( 1 )
        LET sql_ok = chk_db_nam ()
    END IF
 
    RETURN sql_ok
 
END FUNCTION
 
#############################################################################
#  Function     :  chk_db_nam
#  Description  :  checks for the existance of a valid TURBO database.
#
#  Incoming Parameters  : <none>
#  Return Parameters    : sql_ok   -  sql status.
#  Impact Globals       : g_db_nam - database name passed on the command line.
#  Perform Screen       : <none>
#
#  Author      Date       Modification Description
#  PTC         06/15/90   Create.
#############################################################################
FUNCTION chk_db_nam ()
 
    DEFINE sql_ok   INTEGER,
           msg      CHAR(80)
 
    LET sql_ok = TRUE
 
    WHENEVER ERROR CONTINUE
    DATABASE g_db_nam
    WHENEVER ERROR STOP
 
    IF ( SQLCA.SQLCODE != 0 ) THEN
        LET sql_ok = FALSE
        LET msg = err_get ( SQLCA.SQLCODE )
        DISPLAY msg
    END IF
 
    RETURN sql_ok
 
END FUNCTION
 
#############################################################################
#  Function     :  dsp_usage_msg
#  Description  :  displays the command usage message to the screen.
#
#  Incoming Parameters  : <none>
#  Return Parameters    : <none>
#  Impact Globals       : <none>
#  Perform Screen       : <none>
#
#  Author      Date       Modification Description
#  PTC         06/15/90   Create.
#############################################################################
FUNCTION dsp_usage_msg ()
 
    DISPLAY ""
    DISPLAY " Usage: tbl_usage <database name> <measurement type>"
    DISPLAY ""
    DISPLAY "          <database name>     - valid TURBO or OnLine database."
    DISPLAY ""
    DISPLAY "          <measurement type>  - kb : kilobytes (default)"
    DISPLAY "                                pg : pages"
 
END FUNCTION
 
#############################################################################
#  Function     :  partition_drv
#  Description  :  selects all application tables from the current database
#                  and for each table, gathers the appropriate information
#                  and calls the report function.
#
#  Incoming Parameters  : <none>
#  Return Parameters    : <none>
#  Impact Globals       : g_tbl_info - table information record.
#  Perform Screen       : <none>
#
#  Author      Date       Modification Description
#  PTC         06/01/90   Create.
#############################################################################
 
FUNCTION partition_drv ()
 
    LET gr_tot.npage      = 0
    LET gr_tot.nrows      = 0
    LET gr_tot.npage_data = 0
    LET gr_tot.npage_used = 0
 
    # Call removed because of excessive time required under OnLine. If run
    # in the end-of-day script, place after "update statistics" is done. 
 
    # CALL update_statistic () 
 
    DECLARE systbl_curs CURSOR FOR
        SELECT   tabid, tabname, partnum, nrows, rowsize, fextsize, 
		 nextsize, 0, 0, 0, 0, 0
        FROM     systables
        WHERE    ( systables.tabid > 99 )
        AND      ( systables.tabtype = "T" )
        ORDER BY systables.tabname
 
    START REPORT partition_rpt 
 
    FOREACH systbl_curs INTO gr_tbl_info.*
 
        CALL partition_open ( gr_tbl_info.tbl_nam )
 
        # 'C' routine to attach to shared memory and fetch the below
        # returning parameters.
 
        CALL partition_info ( gr_tbl_info.part_num ) 
        RETURNING gr_tbl_info.num_page, gr_tbl_info.num_page_used, 
                  gr_tbl_info.num_page_data, gr_tbl_info.num_extent,
		  gr_tbl_info.db_space_num
 
        CALL partition_close ()
        CALL convert_info ()
 
        OUTPUT TO REPORT partition_rpt ( gr_tbl_info.* )
 
    END FOREACH
 
    FINISH REPORT partition_rpt 
 
END FUNCTION
 
#############################################################################
#  Function     :  convert_info
#  Description  :  Converts the appropriate columns to page or kilobyte
#                  measurement based upon the value of the global variable
#                  'g_dsp_typ'.   Totals are computed following conversion.
#
#  Incoming Parameters  : <none>
#  Return Parameters    : <none>
#  Impact Globals       : g_dsp_typ     -  display type format specifier.
#                         gr_tot        -  grand totals.
#                         gr_tbl_info   -  current table information.
#  Perform Screen       : <none>
#
#  Author      Date       Modification Description
#  PTC         06/01/90   Create.
#############################################################################
FUNCTION convert_info ()
 
    IF ( g_dsp_typ = G_PAGE ) THEN           # Display in PAGE format.
 
        # Convert initial & next extent to pages since the original values
        # are retrieved in kilobyte format.
 
        LET gr_tbl_info.init_ext_size = gr_tbl_info.init_ext_size / 2
        LET gr_tbl_info.next_ext_size = gr_tbl_info.next_ext_size / 2
 
    ELSE                                    # Display in KILOBYTE format.
 
        # Convert number of pages, number of data pages, & number of pages 
        # used to kilobytes since the original values are retrieved in 
        # page format.
 
        LET gr_tbl_info.num_page      = gr_tbl_info.num_page * 2
        LET gr_tbl_info.num_page_data = gr_tbl_info.num_page_data * 2
        LET gr_tbl_info.num_page_used = gr_tbl_info.num_page_used * 2
    END IF
 
    # Compute grand totals.
 
    LET gr_tot.nrows      = gr_tot.nrows      + gr_tbl_info.num_rows
    LET gr_tot.npage      = gr_tot.npage      + gr_tbl_info.num_page
    LET gr_tot.npage_data = gr_tot.npage_data + gr_tbl_info.num_page_data
    LET gr_tot.npage_used = gr_tot.npage_used + gr_tbl_info.num_page_used
 
END FUNCTION
 
#############################################################################
#  Function     :  update_statistic
#  Description  :  Performs the SQL command UPDATE STATISTICS so that the
#                  number of rows column (nrows) in the table "systables"
#                  will have the correct value.
#
#  Incoming Parameters  : <none>
#  Return Parameters    : <none>
#  Impact Globals       : <none>
#  Perform Screen       : <none>
#
#  Author      Date       Modification Description
#  PTC         05/01/90   Create.
#############################################################################
 
FUNCTION update_statistic ()
 
    DEFINE sql_stat  INTEGER,
           cmd       CHAR(80)
 
    LET cmd = "echo \"UPDATE STATISTICS;\" | isql ", g_db_nam CLIPPED,
              " - 2> /dev/null" 
 
    RUN cmd RETURNING sql_stat
 
END FUNCTION
 
#############################################################################
#  Function     :  partition_open
#  Description  :  Opens the current table so that TURBO will place the
#                  partition information in shared memory.
#
#  Incoming Parameters  : tbl_nam - table name to open.
#  Return Parameters    : <none>
#  Impact Globals       : <none>
#  Perform Screen       : <none>
#
#  Author      Date       Modification Description
#  PTC         05/01/90   Create.
#############################################################################
 
FUNCTION partition_open ( tbl_nam )
 
    DEFINE tbl_nam   CHAR(30),
           not_used  INTEGER
 
    LET g_sel_stmt = "SELECT ROWID FROM ", tbl_nam CLIPPED, 
                    " WHERE  ( ROWID > 0 )"
 
    PREPARE pre_tbl FROM g_sel_stmt
    DECLARE open_tbl_curs CURSOR FOR pre_tbl
 
    OPEN open_tbl_curs
 
END FUNCTION    
 
#############################################################################
#  Function     :  partition_close
#  Description  :  Close the previously opened cursor.
#
#  Incoming Parameters  : <none>
#  Return Parameters    : <none>
#  Impact Globals       : <none>
#  Perform Screen       : <none>
#
#  Author      Date       Modification Description
#  PTC         05/01/90   Create.
#############################################################################
 
FUNCTION partition_close ()
 
   CLOSE open_tbl_curs
 
END FUNCTION
 
#############################################################################
#  Function     :  partition_rpt
#  Description  :  Prints the TURBO/OnLine partition usage report.
#
#  Incoming Parameters  : pr_tbl_info - current table information.
#  Return Parameters    : <none>
#  Impact Globals       : <none>
#  Perform Screen       : <none>
#
#  Author      Date       Modification Description
#  PTC         05/01/90   Create.
#############################################################################
 
REPORT partition_rpt ( pr_tbl_info )
 
    DEFINE pr_tbl_info  RECORD
                            tbl_id              INTEGER,
                            tbl_nam             CHAR(30),
                            part_num            INTEGER,
                            num_rows            INTEGER,
                            row_size            INTEGER,
                            init_ext_size       INTEGER,
                            next_ext_size       INTEGER,
                            num_page            INTEGER,
                            num_page_data       INTEGER,
                            num_page_used       INTEGER,
                            num_extent          INTEGER,
			    db_space_num	INTEGER
                        END RECORD,
 
           pct_usage    DECIMAL(10,5)
 
    OUTPUT
        LEFT MARGIN   0
        RIGHT MARGIN  0
        TOP MARGIN    0
        BOTTOM MARGIN 0
        PAGE LENGTH   7
 
    FORMAT
        FIRST PAGE HEADER
            PRINT 
            PRINT COLUMN  1, "OnLine Table Spaces Usage (",
                              G_SIZE_TYPE CLIPPED, ") - ", 
                             TODAY USING "mm/dd/yy"
            PRINT 
            PRINT 
                  COLUMN 27, " No",
                  COLUMN 36, "Initial",
                  COLUMN 45, "  Next",
                  COLUMN 55, "Number",
                  COLUMN 65, "Number",
                  COLUMN 74, "Number",
                  COLUMN 83, "Num",
                  COLUMN 88, "%",
		  COLUMN 94, "DB",
		  COLUMN 99, "Record"

            PRINT COLUMN  1, "Database",
                  COLUMN 12, "Table Name",
                  COLUMN 26, " Rows",
                  COLUMN 37, "Extent",
                  COLUMN 45, " Extent",
                  COLUMN 53, "Allocated",
                  COLUMN 66, "Data",
                  COLUMN 75, "Used",
                  COLUMN 83, "Ext",
                  COLUMN 87, "Full",
		  COLUMN 93, "Space",
		  COLUMN 99, "Length"

            PRINT COLUMN  1, "----------",
                  COLUMN 12, "-----------",
                  COLUMN 24, "----------",
                  COLUMN 35, "---------",
                  COLUMN 45, "-------",
                  COLUMN 53, "---------",
                  COLUMN 65, "-------",
                  COLUMN 73, "--------",
                  COLUMN 83, "---",
                  COLUMN 87, "----",
		  COLUMN 93, "-----",
		  COLUMN 99, "------"
 
        ON EVERY ROW
 
            IF ( pr_tbl_info.num_page != 0 ) THEN
                LET pct_usage = ( pr_tbl_info.num_page_used /
                                  pr_tbl_info.num_page ) * 100 
            ELSE
                LET pct_usage = 0
            END IF
 
            { PRINT COLUMN  1, pr_tbl_info.tbl_nam[1,11],
                  COLUMN 12, pr_tbl_info.num_rows        USING "#,###,##&",
                  COLUMN 22, pr_tbl_info.init_ext_size   USING "###,##&",
                  COLUMN 30, pr_tbl_info.next_ext_size   USING "###,##&",
                  COLUMN 38, pr_tbl_info.num_page        USING "#,###,##&",
                  COLUMN 48, pr_tbl_info.num_page_data   USING "#,###,##&",
                  COLUMN 58, pr_tbl_info.num_page_used   USING "#,###,##&",
                  COLUMN 68, pr_tbl_info.num_extent      USING "###&",
                  COLUMN 73, pct_usage                   USING "##&.&&&",
                  COLUMN 83, pr_tbl_info.db_space_num	 USING "##&"
            PRINT COLUMN  1, g_db_nam[1,3],":",
           }
            PRINT COLUMN  1, g_db_nam[1,10],
                  COLUMN 12, pr_tbl_info.tbl_nam[1,11],
                  COLUMN 23, pr_tbl_info.num_rows        USING "###,###,##&",
                  COLUMN 35, pr_tbl_info.init_ext_size   USING "#,###,##&",
                  COLUMN 45, pr_tbl_info.next_ext_size   USING "###,##&",
                  COLUMN 53, pr_tbl_info.num_page        USING "#,###,##&",
                  COLUMN 63, pr_tbl_info.num_page_data   USING "#,###,##&",
                  COLUMN 73, pr_tbl_info.num_page_used   USING "#,###,##&",
                  COLUMN 83, pr_tbl_info.num_extent      USING "#&",
                  COLUMN 86, pct_usage                   USING "##&.&",
                  COLUMN 94, pr_tbl_info.db_space_num	 USING "#&",
                  COLUMN 97, pr_tbl_info.row_size	 USING "#,##&"
 
        ON LAST ROW
            PRINT COLUMN  1, "--------",
                  COLUMN 23, "-----------",
                  COLUMN 52, "----------",
                  COLUMN 62, "----------",
                  COLUMN 72, "----------"
            PRINT COLUMN  1, "<Totals>",
                  COLUMN 23, gr_tot.nrows        USING "###,###,##&",
                  COLUMN 52, gr_tot.npage        USING "##,###,##&",
                  COLUMN 62, gr_tot.npage_data   USING "##,###,##&",
                  COLUMN 72, gr_tot.npage_used   USING "##,###,##&"
END REPORT

---------- Cut here for START of "tbstat.c" shell script ---------------

/*
 * MODULE
 *   tbstat_get.c
 *
 * FUNCTIONS
 *  int  partition_info();
 *
 * DESCRIPTION
 *   Executes the "tbstat -t" command and scans the output for the number of
 *   pages allocated, number of pages used, number of data pages used, and 
 *   the number of extents for a given table.
 *
 * NOTES
 *   The function partition_info() is intended to be called from Informix-4GL 
 *   programs, and therefore accept a single parameter (number of argument 
 *   values "passed" by the calling program) and return an integer value (number
 *   of values returned to the calling program).
 *
 * AUTHOR
 *   Pete Carpino (INFORMIX SOFTWARE, INC.)
 *
 * DATE
 *   July 01 1990
 */
 
#include <stdio.h>
#include <errno.h>
 
int    partition_num;
int    curr_partition;
 
int    num_extn;
int    num_page;
int    num_page_used;
int    num_page_data;
int    db_space_num;
FILE  *popen();
FILE  *tbstat_out;
 
char  *fgets();
char  buf[81];
 
/*******************************************************************************
 *  NAME
 *    partition_info
 *
 *  DESCRIPTION
 *    partition_info() executes the "tbstat -t" command in order to retrieve
 *    information for the current table partition.  The number of extensions, 
 *    number of pages allocated, number of pages used, and number of data 
 *    pages used and the db space are returned to the 4gl calling function.
 ******************************************************************************/
 
partition_info( nargs )
int nargs;
{
    short  cont_loop = 1;
 
    num_extn = (int)0;
    num_page = (int)0;
    num_page_used = (int)0;
    num_page_data = (int)0;
 
    if( nargs == 1 )
    {
       poplong( &partition_num );               /* Get the partition number. */
 
       if( ( tbstat_out = popen( "tbstat -t","r" ) ) == (FILE *)NULL )
            printf( "Cannot execute tbstat command.\n" );
       else
       {
            while( fgets(buf,81,tbstat_out) != (char *)NULL && cont_loop )
            {
                sscanf( buf,"%*s%*s%*s%*s%lx%*s%ld%ld%ld%*s%ld", 
                        &curr_partition, &num_page, &num_page_used, 
                        &num_page_data, &num_extn );
 
                if( partition_num == curr_partition )
                    cont_loop = 0;
            }
        }
        pclose( tbstat_out );
    }
    retint( num_page );
    retint( num_page_used );
    retint( num_page_data );
    retint( num_extn );
    db_space_num = (partition_num >> 24) & 0xff;
    retint( db_space_num );
 
    return( 5 );
}

---------- Cut here for START of "fgiuser.c" shell script ---------------

/***************************************************************************
 *
 *  Title:	fgiusr.c
 *  Sccsid:	@(#)fgiusr.c	7.2	7/8/90  13:50:19
 *  Description:
 *		definition of user C functions
 *
 ***************************************************************************
 */

/***************************************************************************
 *
 * This table is for user-defined C functions.
 *
 * Each initializer has the form:
 *
 *	"name", name, nargs,
 *
 * Variable # of arguments:
 *
 *	set nargs to -(maximum # args)
 *
 * Be sure to declare name before the table and to leave the
 * line of 0's at the end of the table.
 *
 * Example:
 *
 *	You want to call your C function named "mycfunc" and it expects
 *	2 arguments.  You must declare it:
 *
 *		int mycfunc();
 *
 *	and then insert an initializer for it in the table:
 *
 *		"mycfunc", mycfunc, 2,
 *
 ***************************************************************************
 */

#include "fgicfunc.h"

partition_info();

cfunc_t usrcfuncs[] = 
    {
    "partition_info",partition_info,1,
    0, 0, 0
};

---------- Cut here for END of "fgiuser.c" shell script ---------------