The runtime performance of the DB applications is very significantly affected by the storage structure of the database. In order to achieve optimum results, you must match the storage structure to time-critical applications by modifying the SSL clauses. This can bring about considerable improvements in DB application runtimes even if the schema and programs remain unchanged.
Even if the old SSL is used, it must be recompiled since otherwise BGSIA uses the default values of the SSL clauses.
The following modifications to the storage structure are allowed:
at record type level
relocation of DBTTs to other realms
definition of PLACEMENT OPTIMIZATION
redefinition of location, type and number of pages for the purpose of reorganization in SEARCH key tables
compression or decompression of records (with restrictions)
at set level
modification of MODE clause (with restrictions)
relocation of pointer arrays or lists of a set
linking the owners of a set via pointer arrays or lists, or cancelling an existing link
changing storage space in which the tables of the set occurrences of a set are created or enlarged
definition of the number of pages for the reorganization of tables of a set
definition of location, type and number of pages for the purpose of reorganization in SEARCH key and sort key tables
linking members with their appropriate owner or cancelling an existing link
All SSL clauses are optional, i.e. they can be specified for the first time, or omitted. BGSIA assumes the default value for every clause omitted.
When restructuring the clauses of the Schema DDL and of the SSL are subject to the same rules as during database definition (see the "Design and Definition" manual). If Schema DDL clauses have been modified, all related clauses in the SSL must be adapted to comply with these rules.
The following is a detailed description of possible modifications to the storage structure and their effects on the stored data.
Schema entry
|
schema-name
Must be the same as the name in the schema entry of the new Schema DDL.
Record entry
|
record-name
Must designate a record type of the new Schema DDL.
|
integer-1
The number of DBTT entries can be changed using the BREORG utility routine for example. BALTER ignores any change to the size specification.
realm-name-1
Can be modified.
Effects on stored data:
BALTER relocates the DBTT to the specified realm.
|
Any modification is allowed.
Effects on stored data:
BALTER has no effect on the position of records already stored. New records are stored in the database by the DBH in accordance with the new specifications noted by BALTER.
If this clause is specified for the first time for a record type stored in a direct hash area, BALTER creates an indirect hash area but does not change the position of the records concerned; instead it simply converts the CALC pages of the former hash area into normal data pages.
|
integer
The size of a hash area can be changed using the BREORG utility routine. BALTER ignores any change of the size specification.
Exception
BALTER must create the hash area anew in the event of a change:
from direct to indirect hash area or vice versa
of hash routine
of composition of the CALC key
of realm in which the hash area is to be located.
BALTER uses integer-2 in order to calculate the new size of the hash area.
|
PLACING IS ...
SEARCH key tables can be relocated to other realms.
TYPE IS ...
The structure of the SEARCH key tables can be modified by changing the TYPE clause to DATABASE-KEY-LIST or REPEATED-KEY.
DYNAMIC REORGANIZATION
The number of pages for reorganization purposes can be redefined as required.
Effects on stored data:
Changing the number of pages does not have an immediate effect during restructuring. It is not until later during database operation when storing records that the change takes effect.
|
It is permissible to store in compressed format records which were previously stored without compression and vice versa, subject to certain restrictions.
Changing from ’non-compressed’ to ’compressed’ format:
BALTER does not compress records which have already been stored and therefore does not remove empty data items from data records. Instead it merely adds a 4-byte compression item to the set connection data (SCD) of the records of the record type concerned.
In so doing BALTER lengthens each record of the record type by 4 bytes and must therefore transfer to other pages the records which have no more space in the pages previously occupied by this record type.Changing from ’compressed’ to ’non-compressed’ format:
The change depends on how the stored records of the record type in question have been entered:
If the records have been entered in their full length (e.g. with BINILOAD), BALTER deletes the compression item in the SCD of the records. Since each record is thereby shortened by 4 bytes, BALTER pushes the records together in each page, thus releasing space.
If the records are compressed, i.e. not entered in their full length, BALTER abnormally terminates restructuring!
BALTER cannot process compressed records even if database changes do not concern compression:
Neither the user section nor the system information in the records may be altered. Tables or hash areas which contain items of this record type as keys cannot be created by BALTER.
Set entry
|
set-name
Must be the same as the name of a set in the new Schema DDL.
|
The MODE clause should be considered in relation to the ORDER clause of the Schema DDL (see the section on changing the ORDER clause on "Modifying the Schema DDL").
If, in the ORDER clause of the new Schema DDL
SORTED[INDEXED] has been specified, the linkage method specified in the MODE clause may be changed as required;
SORTED[INDEXED] has not been specified, changes of the linkage method are prohibited if records of the member record type have already been stored.
When a distributable list (MODE IS LIST) is changed to an address list (MODE IS POINTER-ARRAY), the member records remain in the realm in which they were currently stored in the list. It may be necessary to adjust the access logic of the application programs.
When a change is made in MODE IS LIST, a list is built. Records of the record type affected may possibly be relocated to another realm.
ATTACHED/DETACHED
Changing ATTACHED to DETACHED or vice versa is allowed with the restriction that records stored in a list must not be relocated to another realm.
Effects on stored data:
Existing tables are reallocated by BALTER only if it is necessary to relocate them to another realm on the basis of a change in the WITHIN realm-name-1 specification.
The location of a list without a DETACHED WITHIN clause is determined by the position of the owner. Consequently records can be relocated in the event of a change from pointer array to list, and existing programs with direct access (FIND4) must be adjusted.
WITHIN realm-name-1
A pointer array may be relocated to another realm. Except in the case of distributable lists, a list may be relocated only when no member records are stored.
In the case of distributable lists realm-name-1 implicitly determines the location of the table part (level 1 through level N pages) of the list and the location of an indirect CALC area in the case of LOCATION MODE IS CALC if the location is not specified explicitly in the SSL of the MODE IS LIST statement.
The table pages can be relocated to another realm even if member records are stored in the distributable list.
Effects on stored data:
The list and any CALC area will be recreated. In the process the member records will be distributed approximately evenly over the realms involved.
WITH PHYSICAL LINK
A pointer array or list which is stored separate from the owner can also be linked to the associated owner for the first time, or an existing link can be cancelled.
Effects on stored data:
If an additional pointer is set up, BALTER adds the physical address of the highest level of the table to the set connection data (SCD) of the owner records. Since the owner records are thereby lengthened, BALTER relocates, within the database, the records for which no more space is available in the occupied pages.
- If the additional pointer is removed, BALTER deletes the physical address of the highest level of the table from the SCD of the owner records.
|
integer-1
Can be altered as required. BALTER takes this specification into account together with a corresponding FILLING WITH POPULATION statement. A modification also has an effect when saving owner records or deleting member records.
BALTER does not modify existing tables.
integer-2
Can be altered as required. Any change has no effect until table extensions become necessary when storing new member records.
|
The number of pages for the reorganization of the set tables can be altered as required.
|
The usage of the INDEX clause for set entry is analogous to that of the INDEX clause for record entry (see the section on changing the INDEX clause above).
ATTACHED/DETACHED
Changing ATTACHED to DETACHED or vice versa is allowed without restriction.
Effects on stored data:
Changes have the same effects as in the MODE clause (see the section on changing the MODE clause above).
WITHIN realm-name-2
Can be added or omitted, or realm-name-2 can be changed.
Effects on stored data:
In the event of a change, BALTER relocates the tables to the specified realm.
|
Additional linking of the member records to the associated owner record can be requested for the first time or cancelled.
Effects on stored data:
If the clause is specified for the first time, BALTER adds to the SCD of the member records the physical pointer to the associated owner record. Since the member records are thereby lengthened, BALTER relocates, within the database, the records for which no more space is available in the occupied pages.
If the clause is removed from the SSL, BALTER removes the physical pointer to the associated owner record from the SCD of the member records.