The utilization is a measure of how much unused space exists in the file.
An UPDATE can have the impact of lowering the utilization ratio IF the row has to be relocated during the update. That would most often occur when NOTE fields are present and grow in size. A table that had nothing but integer fields, for instance, or any other datatype where R:BASE allocates a fixed amount of storage, would not show this behavior. This would be the case whether STATICDB is ON or OFF. The STATICDB question has to do with CREATE and DROP. With STATICDB OFF, a CREATE / DROP is going to leave a hole in file 2. When the table is created, space is allocated in file 2. When the table is dropped, that space is marked unused. So someone who uses a lot of temporary tables should do one of two things: a) run with STATICDB ON, or 2) be certain to always use CREATE TEMPORARY TABLE. Both of these actions will result in the table being created in a scratch file, not in file 2. With STATICDB ON there will still be drops in utilization on deletes - this is unavoidable - but the problem created by dropped tables is avoided. You can also have a look at the use of SET RECYCLE ON. What this does is invoke a routine when writing a new row to the database. If the current block allocated to the table is full and new space must be allocated, the routine looks to see if there is free space somewhere in file 2 that could be allocated to the table. It must be further down in file 2 than the current last block for the table, and must be large enough to accommodate the allocation. If such space is found, it will be allocated rather than a new block at the end of file 2, which causes the file to grow. Thus, the file 2 utilization ratio can be improved. This works well with regular use of PACK TABLE since that action will remove all dead space within a table's allocated blocks, freeing a whole block or blocks if possible. In the case of a table that was dropped, PACK cannot be used since the table is gone. The limitations with SET RECYCLE ON are that a) all workstations must issue the command before connecting to the database (so it is best deployed as an RBENGINE76.CFG command), b) it will not work with versions below 7.6, and c) there is a slight performance penalty for the new routine, but that routine will only be called when a new block is required for the tablespace. Emmitt Dove Manager, DairyPak Business Systems Evergreen Packaging, Inc. [EMAIL PROTECTED] [EMAIL PROTECTED] (203) 643-8022 -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Bill Eyring Sent: Thursday, July 10, 2008 6:00 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Turbo V-8 Database Utilization Question Emmitt, I need clarification on this. Are you saying that updating a table will drop utilization in rx2 from 98% to 39% without STATICDB on ? Or are you saying utilization will NOT drop if STATICDB is on ? In this particular database there are a lot of updates, appending data from one table to another, creating temp tables for searches and very little deleting of data. The rx2 file size is just 19MB when utilization is 100%. What concerns me is that whether or not staticdb is On or Off, there should NOT be such a dramatic drop in utilization. Do you have any recommendations for the above scenario ? Thanks, Bill -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Emmitt Dove Sent: Thursday, July 10, 2008 12:25 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Turbo V-8 Database Utilization Question Any number of activities can cause the utilization to drop. INSERTing rows should not - new rows get appended to the end of the file. UPDATEing rows can, especially if you have NOTE fields on the tables. Any use of CREATE TABLE and DROP TABLE, when not in STATICDB ON mode or using CREATE TEMPORARY TABLE, will cause the same issue I outlined previously. Emmitt Dove Manager, DairyPak Business Systems Evergreen Packaging, Inc. [EMAIL PROTECTED] [EMAIL PROTECTED] (203) 643-8022 -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Bill Owens Sent: Thursday, July 10, 2008 12:30 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Turbo V-8 Database Utilization Question Thanks Emmitt for your response on the first part of my question. I still don't understand why the utilization drops so drastically from daily use as I outlined below. Bill -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] Behalf Of Emmitt Dove Sent: Thursday, July 10, 2008 8:22 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Turbo V-8 Database Utilization Question Bill, Any time you add a column to a table, R:BASE creates a copy of the table with the new column and removes the original. (If you do a LIST before and after such an activity, you'll note that the table has moved in the table list.) That creates the unused space in file 2 that you are seeing. It is perfectly normal behavior. Emmitt Dove Manager, DairyPak Business Systems Evergreen Packaging, Inc. [EMAIL PROTECTED] [EMAIL PROTECTED] (203) 643-8022 -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Bill Owens Sent: Thursday, July 10, 2008 10:00 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Turbo V-8 Database Utilization Question I have a V8 database I just reloaded. The file #2 utilization shows 100%. If I then add a column (text 8) to a table and do an autochk full the file #2 utilization falls to 78.329%. I have also noticed on the same database that after a few days of use on my network (There are only adds to tables, no deletes) the file #2 utilization falls dramatically (In 1 instance it fell to 39% from 98%) What causes this dramatic drop and how can I prevent it? Has anyone else noticed this situation? Bill
