With the introduction of storage areas to the Progress database in version 9, the dump and load administration task of large databases can now be made more flexible. This article examines a couple of techniques that can be employed to dump and load tables in a database from a specific storage area.

An introductory article on what is a storage area and how they can be used can be found in the winter 1999 edition of the Progress User Group Newsletter, or at www.proxcom.com.

In summary, a database can have many storage areas, each storage area can have many extents. The database administrator/system designer can locate the tables of the database in different storage areas. For example, Static Data could be stored in one area, and Dynamic Data in another. It would then be possible to dump and load only the tables from a specific storage area.

i.e. The Dynamic data area will fragment over time whereas the Static Area will not.

A dump and load of the static data is not likely to provide any performance benefits, as the physical storage of the records on disk will not change very much over time. Conversely, the dynamic data is more likely to provide a performance boost following a dump and load procedure, as the creation, deletion and modification of the records over time will cause the physical storage of the records on disk to become fragmented. When a record becomes fragmented into two blocks then both blocks need to be read from the database to process the record. This degree of fragmentation can be observed using the 'proutil dbname -C tabanalys' utility by examining the fragment count and factor columns.

There is currently no easy way within the existing Progress environment to identify then dump and load the data from tables which reside in a specific storage area.

We can however achieve this by writing some code to discover which tables reside in which storage areas (Partial code to perform this is contained within this article).

To determine which tables reside in each storage area of the database, we need to examine the database metaschema tables (the tables in every Progress database beginning with an underscore "_" character).

The tables we are concerned with are:

• _Area To obtain the area name
• _StorageObject To allow us to determine the area a table resides in
• _File To obtain the table name

The example code in figure A will populate a combo box with the storage areas that contain database tables, and a listbox will be populated with the table names. The listbox will display only the tables that are located in the currently selected storage area.

Figure A
FOR EACH _StorageObject WHERE _StorageObject._Object-Type = 1 AND
_StorageObject._Object-Number > 0 AND
_StorageObject._Area-Number > 5 NO-LOCK
BREAK BY _StorageObject._Area-Number:

IF FIRST-OF(_StorageObject._Area-Number) THEN DO:
FIND _Area WHERE _Area._Area-Number = _StorageObject._Area-Number NO-LOCK.
chSAreaCombo:CSComboBox:AddItem( _Area._Area-Name ).
END.

FIND _File WHERE _File._File-Number = _StorageObject._Object-Number NO-LOCK.
chListFrame:ListBox:Additem(_File._File-Name).


The database administrator can then select all or some of the tables displayed in the list box. The selected tables are those required for the dump and load. From these tables, we will generate the '.d' dump files using the Progress 'EXPORT' format.

The following code extract (figure B) can be found within the sample dump and reload program. This routine uses a dynamic query to access the data within each table and then exports the data to the appropriate dump file. By using the dynamic query the program will work on any database without having to re-compiled:



Figure B
c-dump = c-outDir + _File._Dump-Name + ".d".
CREATE BUFFER bh FOR TABLE _File._File-Name.
CREATE QUERY qh.
qh:SET-BUFFERS(bh).
qh:QUERY-PREPARE("FOR EACH " + _File._File-Name).
qh:QUERY-OPEN.

OUTPUT TO VALUE(c-dump).
REPEAT:
qh:GET-NEXT().
IF qh:QUERY-OFF-END THEN LEAVE.

DO i = 1 TO bh:NUM-FIELDS:
hField = bh:BUFFER-FIELD(i).
IF hField:DATA-TYPE = "character" THEN
PUT UNFORMATTED "~"" hField:BUFFER-VALUE() "~" ".
ELSE
PUT UNFORMATTED hField:BUFFER-VALUE " ".
END.
PUT SKIP.

END.
OUTPUT CLOSE.

qh:QUERY-CLOSE().
DELETE OBJECT qh.
DELETE OBJECT bh.

We will however run into problems using the above code if we attempt to dump a large database table. The Progress 'bulkloader' utility will only directly access a file with a size of less than 2 Giga Bytes. To overcome this, the code could be modified to create multiple '.d' output files. For example, if we where processing a table with a date field, we could process all records upto a certain date into one '.d' file, and the remaining data into a second '.d' file. Alternatively we could keep track of the number of bytes we are exporting and switch to a new file every 2 Giga Bytes.

The Progress 'bulk loader' will be used to load the dumped data back into the database. So we do not need to create any load programs.

The complete program which the example code in this article is taken from, is also capable of generating batch (or script) files, containing a series of 'proutil' commands for performing a binary dump and load operation. This dump and load method may be preferable for some systems.

Now that we have dumped out the data, we could simply delete the existing data and reload it into the database. This is not the best option available to us, the record management mechanism within the storage area will have many blocks with free space. However, If we perform some re-structuring of the database, for example, completely remove the storage area from the database, and then re-create it. Then all fragmentation will be removed, because the storage area has been re-created from scratch, only empty blocks will be left, not free blocks (A discussion of the Progress database block content will appear in an article in a future PUG Newsletter).

Alternatively, we could have used the online proutil tablemove utility. This will also remove the fragmentation. But, this method may not be the best method because of the following potential drawback is:

• You may need more than three times the size of the table size to be available on disk for your BI file, as the whole tablemove operation is performed as a single transaction for recovery purposes.
• The table being moved has an exclusive lock on the entire table - access by other processes may get incorrect results because of changes to the indexes.
• The process may take a long time to complete (dependant on table size)
• We may not want to change the storage area for the tables

So, the steps to completely dump and load a storage area, and remove fragmentation are:

1. Dump all data from a storage area
2. Create a DF file for the effected tables
3. Delete the tables from the database
4. Truncate the BI file
5. Truncate the storage area (Proutil database-name -C truncate area)
6. Run "prostrct remove database-name d area-name" once for each extent of the storage area
7. Recreate (and adjust if required) the storage areas and extents for the tables
8. Edit the DF file to change the "AREA" field of the add table and add index sections to the name of the new storage area if you have changed them.
9. Load the DF
10. Load the data
11. Re-index where required (i.e. if the Progress bulkloader is used).

The above examples are only some of the possibilities for dumping and loading a version 9 Progress database. As always, there are other methods that could be used instead, with different methods suiting different database structures and sizes. The utility from which the above code extracts are taken should provide a good starting point / inspiration for managing the dump and load of Version 9 databases.

The complete utility including source code, from which the above code examples are taken, can be freely downloaded from www.proxcom.com. © Copyright Proxcom Limited 2000 - All Rights Reserved
[Top]