| ||
| Database | | Schema Modification |
MODIFY SCHEMA
command to make specific changes or edit a text version
of the schema and re-submit the entire schema.All changes to a database, including schema changes, are recorded on the journal file (provided that journalling is on). Each schema modification run increments the update level of the database.
MODIFY SCHEMA can be used for virtually all modifications. The exceptions are changing a variable's data type to numeric from character (and vice versa) or deleting variables. This is because existing, unchanged parts of the schema are not checked, and a change in variable type could invalidate existing compute or boolean statements. Resubmit the whole definition to make these changes.To change the data type of a variable from string to numeric (or vice versa) or delete a variable, re-execute the complete record schema definition.
If you do not have the source code to edit for the record definition,
the WRITE SCHEMA
utility generates the text.
Deletion of variables takes place automatically. If an existing variable
does not appear on the DATA LIST (or VARIABLE
LIST) statement of the amended schema, it is deleted from the
database.
All changes to record schemas can be made by resubmitting the complete schema.
SIR2002 recognises the changes and only locks the record if necessary. The
disadvantage to resubmitting record schema to make changes is that each
resubmission takes space on the codebook file. This can cause problems when
making multiple changes to a record type (particularly one with a large number
of value labels). Modify Schema avoids this problem and only changes specific
items. An
UNLOAD /
RELOAD
recovers the codebook space.
Modification of a variable's data type should be done carefully. The following
table indicates the result of changing data types.
Var
When transferring values, ranges, missing values, valid values and all schema
definitions are checked and appropriately handled. Strings are truncated if they
cannot fit in the new definition. Floating point numbers are truncated if they
are converted to unscaled integers.
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:
A database restructure is done in three steps; once the schema modification
which forced the restructure has been done, run the
The database restructuring that takes place in an unload/reload restructures the
data dictionary, the index and all of the data.
The following commands are allowed in a
If this is a redefinition of a variable with existing missing values,
be aware of the way missing values are handled. For example, suppose 9 was
previously defined as missing values and this is changed, making 8 missing
instead of 9. Missing values are not physically stored as the input values (they
are stored as Missing Value 1, Missing Value 2, etc.). Records input under the
previous definition as 9 have missing value 1. Any existing records with an 8
are dynamically restructured to missing value 1 when they are accessed. These
records are now indistinguishable in that they are both Missing Value 1.
Examples:
Deletes the Record Schema 'n' from the database definition.
When defining and redefining a record type, it is sometimes simpler to
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.
Modifying Data Types
When modifying data types for variables which already have data stored in the
database, the data must be reformatted. There are restrictions in MODIFY
SCHEMA which disallow certain types of changes. Resubmit the whole
definition using RECORD SCHEMA if these changes are necessary.
T - Transfer value
C - Convert to new type
X - Convert to numeric if
string is a valid number
U - Convert to undefined To
String Cat Date Time Integer Real
From String T C C C X X
Catvar C T U U T C
Date C U T U T C
Time C U U T T C
Integer C T T T T C
Real C C C C C T
Dynamic Restructuring
Some changes to the schema affect the way in which existing data is treated.
These include modifications to the size or type of variables, additions or
deletions of variables. This necessitates a restructure of existing data
records which 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.
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.UNLOAD utility to copy the database to a single binary sequential file;
second delete the database using the
PURGE SIR FILE utility;
third reload the database with the
RELOAD utility or the
reload procedure.
MODIFY SCHEMA
MODIFY SCHEMA rectype [,newname] /
[LOCK/]
[NOOLD/]
[NONEW/]
This is a DBA only command.
MODIFY SCHEMA starts the block of commands to modify the
schema and END SCHEMA ends the block. Within this block, a
number of commands change the schema.
The options on the command are:RECTYPENEWNAME LOCKLOCK keyword is omitted and a lock condition occurs. If the
record type is locked, an
UNLOAD /
RELOAD is required. Lock
conditions occur when:NOOLDNONEWMODIFY SCHEMA run.
Those with asterisks are unique to MODIFY SCHEMA. Each of the
commands specifies the portion of the schema to modify.ACCEPT REC IFACCEPT REC IF conditions.CAT VARS*CLEAR BOOLEANSACCEPT/REJECT conditions.*CLEAR COMPUTES varname1,... | ALL*CLEAR RECODES varname1,... | ALL*CLEAR VALUE LABELS varname1,... | ALL*CLEAR VAR LABELS varname1,... | ALLCOMPUTECLEAR COMPUTES command to delete old
COMPUTE definitions.DATA LISTDATA
LIST. If existing variables are referenced, their definitions are
altered according to the format specified. Incompatible changes
(string-numeric) are not allowed.DATE VARSDOCUMENT*EDIT LABELSVALUE LABELS command. Any new
values are added to the list, any existing values that are referenced are
updated. Existing value labels that are not referenced are not altered.
VALUE LABELS completely redefines all value labels
for a variable. EDIT LABELS adds new values and redefines
existing values.IFIF definitions which are added to the
old definitions.INPUT FORMAT/VARIABLE LISTVARIABLE LIST can reference new or existing
variable names. If new variables are defined, they are added to the schema
according to the type and length indicated by the INPUT
FORMAT. If existing variables are referenced, their definitions are
altered according to the format specified. Incompatible changes (such as
string-numeric) are not allowed and result in an error. The
INPUT FORMAT command defines formats for all variables referenced in the
variable list.INTEGER*nKEY FIELDSKEY FIELDS for the
record type. Note: Changing the KEY FIELDS for a record with
data already stored, results in the record type being locked and requires a
restructure.MISSING VALUESRECODERECODE definitions. These are added to
the old definitions. Use the CLEAR RECODE command to delete
existing definitions.REC SECURITYREJECT REC IFREJECT REC IF.SCALED VARSSEQUENCE CHECKTIME VARSVALID VALUESVALUE LABELSEDIT LABELS updates existing value labels.)VAR RANGESVAR SECURITYVARIABLE LISTVARIABLE LIST can reference new or existing
variable names. If new variables are referenced, they are added to the schema
according to the type and length on the INPUT FORMAT. If
existing variables are referenced, their definitions are altered according to
the format specified. Incompatible changes (such as string-numeric) are not
allowed. The INPUT FORMAT command defines formats for all
variables referenced in the variable list.VAR LABELSVAR LABELS defines the complete variable label
for specified variables.
MODIFY SCHEMA 1
EDIT LABELS JOBCODE (21) Salesperson
(22) Senior Salesperson
(23) Sales Manager/
VAR RANGES JOBCODE (1 23)
The following example adds two variables to the Employee record type.
MODIFY SCHEMA EMPLOYEE
DATA LIST ETYPE 70 (I)/
PHONE 71 - 80 (A)
VAR RANGES ETYPE (1 3)
VAR LABELS ETYPE 'Employee Type'/
PHONE 'Home Phone Number'/
END SCHEMA
The following example modifies the variable label for Position and the variable
label for value 5 of Rating in the Review record type.
MODIFY SCHEMA REVIEW
VAR LABELS POSITION 'Job Code'
EDIT LABELS RATING (5) 'Excellent'
END SCHEMA
DELETE SCHEMA
DELETE SCHEMA n DELETE
SCHEMA only operates if there are no records for this record type.
This is a DBA security level command.DELETE SCHEMA and redefine it through a complete new
RECORD SCHEMA than to modify it over and over again.RETRIEVAL UPDATE
PROCESS REC n
DELETE REC
END PROCESS REC
END RETRIEVAL