Newsgroups: comp.databases.informix Subject: Space each table occupies From: rovkg426@glen-ellyn.iit.edu (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: cwakins@leia.alloys.rmc.com (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 cwakins@leia.alloys.rmc.com | |________________________________________________________________| From: melin@hermes1.sps.mot.com (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. | rp29560@email.sps.mot.com Phone:(602) 655-3627 | | | melin@hermes1.sps.mot.com 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 KB C-Compiled (i4gl) : ./tblusage.4gi 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 # # - TURBO or OnLine database. # # - 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 : # Return Parameters : sql_ok - sql status. # Impact Globals : g_db_nam - database name. # g_dsp_typ - report display type. # Perform Screen : # # 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 : # Return Parameters : sql_ok - sql status. # Impact Globals : g_db_nam - database name passed on the command line. # Perform Screen : # # 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 : # Return Parameters : # Impact Globals : # Perform Screen : # # Author Date Modification Description # PTC 06/15/90 Create. ############################################################################# FUNCTION dsp_usage_msg () DISPLAY "" DISPLAY " Usage: tbl_usage " DISPLAY "" DISPLAY " - valid TURBO or OnLine database." DISPLAY "" DISPLAY " - 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 : # Return Parameters : # Impact Globals : g_tbl_info - table information record. # Perform Screen : # # 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 : # Return Parameters : # Impact Globals : g_dsp_typ - display type format specifier. # gr_tot - grand totals. # gr_tbl_info - current table information. # Perform Screen : # # 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 : # Return Parameters : # Impact Globals : # Perform Screen : # # 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 : # Impact Globals : # Perform Screen : # # 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 : # Return Parameters : # Impact Globals : # Perform Screen : # # 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 : # Impact Globals : # Perform Screen : # # 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, "", 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 #include 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 ---------------