Re: Space each table occupies
On Mon, 26 Jun 1995, Clem Akins wrote: > 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 | > |________________________________________________________________| > 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 <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. #