Database

Home	Starting	Environment	DBMS	VisualPQL	Procedures	SQL	Forms	Host/API	Index

Database

Schema Modification

Dynamic Restructuring

Some changes to the schema affect the way existing data is treated. These changes include changes to the size or type of variables, additions or deletions of variables. This necessitates a restructure of existing data records that is done dynamically wherever possible. All changes except changes to the keyfields are dynamically restructured. Other changes such as changes in variable labels, value labels and security do not affect data storage at all.

Only changes that affect the index require the restructuring of the entire database. Dynamic restructuring means that the restructuring takes place as the data is used. Once the schema modification is made, the data is always seen as it is currently defined. Data that was stored under the old format is transformed into the new format every time that it is read. The record is physically restructured only when a write operation is performed. There is a small overhead involved in restructuring records dynamically each time they are read. If a series of changes had been made a simple VisualPQL program that reads and writes every record of that type, forces a physical restructure. For example:

RETRIEVAL UPDATE
. PROCESS RECORD employee
.  COMPUTE name = name
. END PROCESS REC
END RETRIEVAL

When a record schema is changed, internal tables are saved, indicating what changes have occurred and at what update levels. When a data record is accessed for a record type that has been changed, transformations are applied to bring it up to the current schema level. If the record is rewritten to the database, the restructured version is made permanent.

Database Restructure

Changes that affect the index require the restructuring of the entire database. This occurs if keys are redefined, a new record is defined with a key length greater than MAX KEY SIZE or the overall database constraints are respecified. Such a change does not take effect unless the LOCK parameter is specified on the record schema. This is because once a change has been made, the record is locked and the database must be restructured. The absence of the LOCK parameter prevents this happening unexpectedly.

A database restructure is done in steps;
make the schema modification;
run the UNLOAD utility to copy the database to an unload file;
delete the database using the PURGE SIR FILE utility;
reload the database with the RELOAD utility.

The database restructuring that takes place in an unload/reload restructures the data dictionary, the index and all of the data.

DELETE SCHEMA

DELETE SCHEMA n

Deletes the Record Schema 'n' from the database definition. DELETE SCHEMA only operates if there are no records for this record type. This is a DBA security level command.

When defining and redefining a record type, it is sometimes simpler to DELETE SCHEMA and redefine it through a complete new RECORD SCHEMA than to modify it over and over again.

When defining and testing a new database, delete all of the test data for a record type prior to deleting the schema. The following simple VisualPQL program does this.

RETRIEVAL UPDATE
PROCESS REC n
DELETE REC
END PROCESS REC
END RETRIEVAL