The REORGANIZE-SET statement can be used to reorganize set constructs (LIST, CHAIN, POINTER ARRAY) and any SEARCH KEY USING INDEX on set level or record type level. The reorganization of a SEARCH KEY USING INDEX on record type level occurs via the associated implicit set.
You can reorganize
all set occurrences
individual set occurrences which you preset via the RSQ of the owner
areas of set occurrences which you define via an area specification of the owner RSQ
You can reorganize the following tables with the REORGANIZE-SET function to set levels:
non-indexed and indexed pointer arrays
non-indexed and indexed lists
indexed sort key tables
chains
indexed SEARCH key tables (also duplicates tables)
You can reorganize indexed SEARCH key tables (also duplicates tables) with the REORGANIZE-SET function to record levels. The name of the implicit set must be specified.
BREORG updates all address references in the specified tables that were stored as probable position pointers. It can also optionally recreate the tables with a different occupancy level. BREORG also updates all probable position pointers in chains.
If you want to reorganize several tables in a BREORG session, you should first carry out the functions that relocate the data records. You should then update the probable position pointers which still refer to the old position of the data records.
REORGANIZE-SET | ||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
SET-NAME = <set-name>
Name of the set or implicit set to be reorganized. The name of the implicit set is constructed by combining SYS_ and record-name.
The underscore (_) must be entered as a hyphen (-) sign!
OWNER-SELECTION = *ALL
All set occurrences are reorganized.
OWNER-SELECTION = list-poss(30): <integer 1..2147483647> / *RANGE(...)
Owner and set occurrences are reorganized.
<integer 1..2147483647>
The owners with the specified RSQs are reorganized.
*RANGE (...)
All set occurrences whose owner RSQs lie within the specified ranges are reorganized.
FROM-RSQ = <integer 1..2147483647>
RSQ of the first owner whose SET and/or TABLE OCCURRENCE/S is/are to be reorganized.
TO-RSQ = <integer 1..2147483647>
RSQ of the last owner whose SET and/or TABLE OCCURRENCE/S is/are to be reorganized.
KEY-SELECTION = *ALL
Every SEARCH KEY USING INDEX and the set construct are reorganized.
KEY-SELECTION = list-poss(30): <integer 0..32767>
Every SEARCH KEY USING INDEX for which a KEY REF is specified is reorganized. The KEY REF can be determined from the BPSIA log.
If the set constructs CHAIN, LIST and POINTER ARRAY were not defined with a SORTED INDEXED BY specification, no KEY REF will have been entered in the BPSIA log. If this is the case, the value 0 must be specified for the KEY REF (only permitted in this situation). The set is then reorganized.
FILLING = *UNCHANGED
Only the probable position pointers (PPP) are updated in the tables or set constructs.
FILLING = <integer 1..100>
The tables are reorganized with the specified filling ratio.
The REORGANIZE-SET function reorganizes tables (ASC/DESC KEY, SEARCH KEY) and chains. In this case, reorganization means that BREORG updates the probable position pointers (PPP)
in the sort key table entries and the SEARCH key table entries,
in the SCD (set connection data) of records in chains (forwards/backwards chaining), and
in the SCD of data pages with owner links (PYSICALLY LINKED TO OWNER)
in the SCD of owner records with table links (WITH PHYSICAL LINK)
or sets up new tables.
The linkage of a table to the owner in ... WITH PHYSICAL LINK ... is an Act-key.
If FILLING = integer... has been specified, BREORG reorganizes all tables and fills the new table pages with the available updated entries up to the specified percentage. In multi-level tables, the specified percentage applies only to the main level (level 0). On level 1, 95% of the table is filled; on every other level, one table entry is left free. In addition, BREORG updates the entries in the DBTT of the owner record type (column number >0).
In the case of an ASC or DESC key table of a set with MODE IS LIST, the records themselves are in the table pages, i.e. the records are relocated when such a table is created. In this case BREORG also updates the DBTT entries in column 0 of the DBTT of the record type concerned.
The following overview shows which probable position pointers (PPP) and tables can be reorganized using the REORGANIZE SET function (see the “Design and Definition” manual).
DDL and SSL statements | Probable Position Pointer (PPP) | Table | |||
Explanation | Updating possible | Type | Restructu ring possible | ||
MODE IS CHAIN | ORDER IS FIRST/NEXT/PRIOR SORTED | Owner record includes PP of 1st member record in chain 1 | yes | - | - |
Forward chaining of member records with RSQ and PPP 1 | yes | ||||
ORDER IS LAST or LINKED TO PRIOR | Owner record includes PPP of last member record in chain | yes | |||
LINKED TO PRIOR | Backward chaining of member records with RSQ and PPP | yes | |||
ORDER IS SORTED INDEXED BY DEFINED KEYS... ASC/DESC KEY IS | Every table entry includes PPP of member record | yes | multi-level sort key table | yes | |
ORDER IS SORTED INDEXED BY DATABASE-KEY | Every table entry includes PPP of member record | yes | multi-level sort key table | yes | |
MODE IS POINTER-ARRAY | ORDER IS FIRST/LAST/NEXT/PRIOR | Every table entry includes PPP of member record | yes | single-level pointer array | yes |
ORDER IS SORTED INDEXED BY DEFINED KEYS... ASC/DESC KEY IS... | Every table entry includes PPP of member record | yes | multi-level pointer array | yes | |
ORDER IS SORTED | Every table entry includes PPP of member record | yes | multi-level pointer array | yes | |
MODE IS LIST | ORDER IS FIRST/LAST/NEXT/PRIOR | PPP not included | - | single-level list | yes |
ORDER IS SORTED INDEXED (DB key or ASC/DESC key) | PPP not included | - | multi-level list | yes | |
SEARCH KEY ..USING INDEX | TYPE IS REPEATED-KEY | Every table entry includes PPP of member record | yes | multi-level SEARCH key table | yes |
TYPE IS DATABASE-KEY-LIST | PPP not included | - | duplicates table | yes | |
MEMBER IS PHYSICALLY LINKED TO OWNER | Member record includes pointer to owner record (PPP) | yes | - | - | |
1These PPPs are standard with MODE IS CHAIN.
If an entire table is contained in one page, BREORG does not set up a new table, even if FILLING... has been specified.
When setting up a new table with multiple table pages on level 0, BREORG inserts at least two entries in each table page.
When a database is reorganized, DBTT extents may be created independently of the activation of online DBTT extension. An increase in the size of the DBTT due to BREORG is implemented in DBCOM and DBDIR by enlarging the existing DBTT. In the user realm, the DBTT is enlarged by BREORG by means of DBTT extents if the target DBTT has a total size greater than 128 PAM pages. If the target DBTT is smaller than or equal to 128 PAM pages then BREORG always implements it as a DBTT base, i.e. as a single unit. The corresponding messages inform you of the results of increases or reductions in the size of DBTTs.
Execution messages
On executing a REORGANIZE-SET statement, the results of the reorganization of a set or a table are output as follows:
| |
|
|
|
|
|
|
set-name
Name of the set
number table occurrences
Number of tables processed in the set occurrences
number of actualized ppps
Number of updated probable position pointers (PPP)
number physical io
Number of physical input and output operations