PURPOSE: The Record Retrieval form of the GET statement is used to retrieve records from an auxiliary Data Set (DS). This can be done directly or with the aid of a domain from a Data Index (DI). Records can be retrieved sequentially or randomly. The different forms of retrieval are sequential, key matching, key range, and direct access.
GET FROM designator [/USE/ DOMAIN name] [retrieval method [HUSH]
[IF; UNLESS clause]
|
FROM designator |
specifies the name related to a DS or DI by the RELATE declaration. FROM designator may appear immediately after the GET or before the HUSH. |
|
[/USE/ DOMAIN name] |
specifies the name of the domain to be used when a DI is related. If there are multiple domains and this clause is not specified, the first domain of the DI is used. This option may appear immediately before or after the FROM designator clause. This option allows the GET statement to easily access the DS using multiple domains. Records can be retrieved in multiple sequences and by different key fields. The logical order of the DS may be changed by merely changing the domain. When the domain is changed, ACCENT R retrieves records starting from the beginning of the logical order of the new domain. |
|
retrieval method |
specifies the method ACCENT R should use to retrieve records. See forms 1-4 on the following pages. The methods allowed are: |
|
FORM 1: |
Sequential Access - A record is sequentially retrieved. |
|
FORM 2: |
Key Field Matching - A record is retrieved based on a key field value. |
|
FORM 3: |
Direct Access - A record is retrieved directly by its record number or address. |
|
FORM 4: |
Key Range Access - A record is retrieved by its key field value. If a record with the specified key field value is not found, a record with the closest key field value is retrieved. |
NOTES: The value in the system field @AUX reflects the results of the last GET statement attempted. If the GET statement succeeds, the system field @AUX is set to "YES". The specified record is retrieved and its fields are available for processing.
If ACCENT R cannot retrieve the specified record, @AUX is set to "MISSI". If the DS does not contain any records, @AUX is set to "EMPTY". If another user has the record locked, @AUX will be set to "BUSY".
@AUX may be set to any of the following values:
@AUX = YES
if the operation was successful.
@AUX =
MISSI
if the record does not exist or the current domain is empty.
@AUX =
EMPTY
if the data set has no records.
@AUX =
TYPE
if the record read is not a valid record type.
@AUX =
QUOTA
if queue quota has been exceeded.
@AUX =
BUSY
if another user has the record locked.
@AUX =
CLOSE
if the data set has not been opened yet.
@AUX =
VALID
if this operation is not valid for this data set.
@AUX =
MULT
if a GET multiple is followed by a GET without MULTIPLE without
a FREE statement being executed first.
@AUX =LOCK
if a PUT is executed on a data set related for simultaneous update and the GET did not specified
LOCKED or NOT LOCKED.
After the GET has failed, the record available for processing is completely arbitrary; therefore the system field @AUX should always be checked to be sure the desired record was retrieved.
If the DS is related for INPUT, any changes to the field values will not affect the stored record in the DS.
GET FROM designator {FIRST; LAST; NEXT; PRIOR} [/USE/DOMAIN name]
[NOT INDEXED] [HUSH] [IF; UNLESS clause]
|
FIRST |
causes ACCENT R to retrieve the first record of the Data Set (DS) or, if the Data Index (DI) is related, the first logical record of the specified domain. |
|
LAST |
causes ACCENT R to retrieve the last record of the DS or, if a DI is related, the last logical record of the specified domain. |
|
NEXT |
causes ACCENT R to sequentially retrieve the next record from the DS. If a DI is related, the order of the records is controlled by the logical order of the specified domain. If no previous GET has been used for the DS or if the domain has changed, GET FIRST is performed. |
|
PRIOR |
causes ACCENT R to sequentially retrieve the previous record from the DS. If a DI is related, the order of the records is controlled by the logical order of the specified domain. If no previous GET has been used for the DS or if the domain has changed, this option fails with @AUX set to MISSI. |
|
NOT INDEXED |
causes ACCENT R to retrieve the specified record, ignoring the related DI. This option is only valid if a DI is related. If a subsequent GET is performed using the related DI, retrieval will continue from the last GET statement using the DI. A GET using the NOT INDEXED option does not re-position the DI. This is useful if you have header records followed by several detail or trailer records. You can index only the header records in the index and read the trailer records with this option. |
Figure 13-17 Examples of Sequential Access
Example 1 illustrates which record is retrieved if a GET PRIOR or a GET NEXT is executed.
Example 2 illustrates which record is retrieved if a GET FIRST or a GET LAST is executed.
NOTES: GET LAST and GET PRIOR can not be used for non-indexed access to a DS having multiple record types. ACCENT R cannot determine the length of a record unless all records are the same length or ACCENT R begins reading a record at its starting position. Reading a record incorrectly can cause erroneous results and the loss of data.
GET FROM designator {FIRST; LAST; NEXT; PRIOR} MATCH BY \\values\\
[/USE/ DOMAIN name] [HUSH] [IF; UNLESS clause]
|
MATCH |
causes ACCENT R to retrieve a record with the specified key field values. ACCENT R searches for a record that has the same key field values as those specified in the BY \\values\\ clause. For non-indexed retrieval, where multiple records have the same key field value, ACCENT R retrieves an arbitrary record with the specified key field value. For indexed retrieval, the first record of a group of matching records is always retrieved. |
|
FIRST |
causes ACCENT R to retrieve the first record of a group of records with the same specified key field values. FIRST MATCH is the default for indexed retrieval. |
|
LAST |
causes ACCENT R to retrieve the last record of a group of records with the same specified key field values. |
|
NEXT |
causes ACCENT R to sequentially retrieve the next record from the DS that has the specified key field values. If a DI is related, the order of the records is controlled by the logical order of the specified domain. GET FIRST MATCH is performed if the last GET statement failed, no previous GET has been used for the DS, or the domain has changed. |
|
PRIOR |
causes ACCENT R to sequentially retrieve the previous record that has the specified key field value. If a DI is related, the order of the records is controlled by the logical order of the specified domain. GET LAST MATCH is performed if the last GET statement failed, no previous GET has been used for the DS, or the domain has changed. |
|
BY values |
specifies the key field values to be used in all matching operations. \\values\\ may contain a list of literals, constants, fields, expressions, or functions, as long as they evaluate to the appropriate field types. When a DI is related, the values must correspond to the fields specified in the ON \\fields\\ clause of the specified domain or a left subset. For non-indexed access, a DS or SD is related. The values must correspond to the fields specified in the ON \\fields\\ clause of the RELATE declaration. A value must be given for each field in the corresponding ON \\fields\\ clause. The order of the values must be identical. For non-indexed access, ACCENT R uses a binary search to quickly and efficiently locate the specified record. Therefore, the DS must be sorted in the order of the ON \\fields\\ clause in the corresponding RELATE declaration. This sort order must be maintained. The fields referenced in the ON \\fields\\ clause of the corresponding RELATE declaration should not be modified. Care must be taken when adding new records. Modification of these fields and the addition of new records can destroy the sort order of the DS. This will make it impossible to correctly perform future retrievals. (This is not a problem if a DI is related. The DI is dynamically updated to reflect the new logical order of the altered or added records.) |
Figure 13-18 Example of Key Matching Access
The example illustrates which record is retrieved if a GET statement is executed with a value of 5. For non-indexed retrieval, ACCENT R retrieves an arbitrary record if the GET FIRST or GET LAST option is not specified. The record ACCENT R retrieves can be altered by merely changing the number of records in the Data Set. For indexed retrieval, a FIRST option is the default if not specified.
NOTES: For non-indexed access, all records of the DS must have the same length. This is because ACCENT R uses binary search to locate the position of the record. Form 2 of the GET statement cannot be used for non-indexed access to a DS having multiple record types.
For efficiency, the FIRST and LAST options should not be used if all field values are unique or if it is unimportant which record of a group of matching records is retrieved. This is because the FIRST and LAST options require ACCENT R to read all records until an unmatched record is found, then read back the last matched record.
Index and matching keys will be affected by the status of the DATATRIM set by the ENABLE/DISABLE statements.
GET FROM designator {/LOW/; FIRST /LOW/; LAST /LOW/; HIGH; FIRST HIGH;
LAST HIGH} FIT BY \\values\\ [/USE/ DOMAIN name] [HUSH]
[IF; UNLESS clause]
|
LOW |
causes ACCENT R to retrieve a record with the specified key field value. If a record with the specified key field value does not exist, ACCENT R sequentially retrieves the first prior record whose key field value is less than the specified key field value. ACCENT R searches for a record whose key field value is less than or equal to those specified in the BY \\values\\ clause. |
|
FIRST LOW |
causes ACCENT R to retrieve the first record of a group of records with the same key field value or the next lower key value. FIRST /LOW/ FIT is the default for indexed retrieval. |
|
LAST LOW |
causes ACCENT R to retrieve the last record of a group of records with the same key field value. Or the next lower key value. |
|
HIGH |
causes ACCENT R to retrieve a record with the specified key field value; if a record with the specified key field value does not exist, ACCENT R sequentially retrieves the first subsequent record whose key field value is greater than the specified key field value. ACCENT R searches for a record whose key field value is greater than or equal to those specified in the BY \\values\\ clause. |
|
FIRST HIGH |
causes ACCENT R to retrieve the first record of a group of records with the same key field value. Or the next higher key value if no match is found. |
|
LAST HIGH |
causes ACCENT R to retrieve the last record of a group of records with the same key field value. Or the next higher key value if no match is found. |
|
BY\\values\\ |
specifies the key field values to be used in all matching operations. \\values\\ may contain a list of literals, constants, fields, expressions, or functions, as long as they evaluate to the appropriate field types. When a DI is related, the values must correspond to the fields specified in the ON \\fields\\ clause of the specified domain or a left subset. |
This table shows the resulting record based on the key value in the KEY VALUE column and the retrieval method shown in the left most column. A GET…FIT will not generate an error if no key value match is found.
|
Illustration |
Record if index |
Record if not index |
Key Value |
|||
|
LAST FIT |
5 |
5 |
E |
Record |
Key |
|
|
FIRST FIT |
2 |
2 |
D |
1 |
A |
|
|
FIT |
3 |
3, 4 or 5 |
E |
2 |
C |
|
|
FIT |
2 |
2 |
D |
3 |
E |
|
|
LAST FIT |
5 |
5 |
E |
4 |
E |
|
|
LAST FIT |
2 |
2 |
D |
5 |
E |
|
|
FIRST LOW FIT |
3 |
3 |
E |
6 |
H |
|
|
FIRST LOW FIT |
3 |
3 |
G |
7 |
I |
|
|
LOW FIT |
3 |
3, 4 or 5 |
E |
|||
|
LOW FIT |
5 |
5 |
G |
|||
|
LAST LOW FIT |
5 |
5 |
E |
|||
|
LAST LOW FIT |
5 |
5 |
G |
|||
|
FIRST HIGH FIT |
5 |
5 |
E |
|||
|
FIRST HIGH FIT |
3 |
3 |
D |
|||
|
HIGH FIT |
5 |
3, 4 or 5 |
E |
|||
|
HIGH FIT |
3 |
3 |
D |
|||
|
LAST HIGH FIT |
5 |
5 |
E |
|||
|
LAST HIGH FIT |
5 |
5 |
D |
NOTES: When a DS is related for non-indexed retrieval and multiple records have the same key field value, ACCENT R retrieves an arbitrary record with the specified key field value unless the FIRST or LAST option is specified.
For efficiency, the FIRST and LAST options should not be used if all records have unique key field values or if it is unimportant which record of a group of matching records is retrieved.
If FIRST or LAST is specified for a non-indexed data set, ACCENT R must first find a matching record and then read forward or backward until an unmatched record is located, then reposition to the previous matched record.
For any keyed non-indexed operations, the records must be in the order of the key field list.
GET FROM designator {ADDRESS; RECORD} ae [HUSH] [IF; UNLESS clause]
|
ADDRESS ae |
causes ACCENT R to retrieve the record whose address is equal to ae (arithmetic expression - may contain literals, constants, fields, expressions, or functions, as long as it evaluates to an integer or numeric value). The system field @ADDRESS can be used to obtain the address of the current record after the GET is performed. @ADDRESS should be used to obtain the address of the current record. The record address is a byte address. If a subsequent GET is performed using a related DI, retrieval continues from the last GET statement using the DI. A GET using the ADDRESS ae option does not re-position the DI. ACCENT R checks the address given to see if it is a multiple of the record length and returns "MISSI" in @AUX if not. In order to have ACCENT R ignore the check you can use the following statement. DISABLE ADDRESS CHECK. |
|
RECORD ae |
causes ACCENT R to retrieve the record whose record number is equal to ae. The specified record is located by its position in the DS. ACCENT R calculates the record’s position by multiplying the record size by ae. Since the DS is not sequentially read, records that have been deleted “in-place” are not recognized. If a DS has records that have been deleted “in-place,” @RECORD does not necessarily equal ae. RECORD ae cannot be used when the data set is related with a DI. |
PURPOSE: The GET statement is used to retrieve records from a Data Set (DS) for use in a Process Module (PM). Under the Concurrent Queued environment, which allows multiple-user update of a given DS, a record may be retrieved for shared use or exclusive use. A record that is retrieved for shared use is said to be not locked. Locked records may be concurrently accessed by others for input, but are not available to another user who wants to update. A record that is retrieved for exclusive use is said to be locked. The GET statement must put a temporary lock on a record during the time that it is being updated, to prevent any other user from accessing it, for either input or update. In other words, for the period during which the record is locked it is not available to any other user for update. However, all other records in the DS are still available for both input and update.
A second option of GET that is available only under simultaneous update is the MULTIPLE option. It allows the user to lock more than one record at a time.
GET FROM designator [MULTIPLE] [NOT] [LOCKED] [/USE/ DOMAIN name]
retrieval method [HUSH] [IF; UNLESS clause]
|
MULTIPLE |
allows more than one record from a related DS to be locked at the same time. The record will remain locked until a FREE statement is executed or until the PM terminates. The MULTIPLE option is generally used only when the PM needs to hold on to a group of retrieved records for use (such as for subtotaling) before they are released. However, use of the MULTIPLE option can quickly exhaust the user’s quota of resources available for queuing. It is almost always preferable to design an application so it does not use MULTIPLE, if that is possible. GET MULTIPLE and GET are mutually exclusive ways to retrieve records. These two ways may not be used at the same time using the same designator. A FREE statement must be specified before changing from GET to GET MULTIPLE or vice versa. |
|
LOCKED |
specifies exclusive control of a record. It cannot be accessed by any other user for either input or update while it is locked. A GET statement must specify the LOCKED option in order to subsequently PUT or DELETE the record. It is important to note that ACCENT R locks at a block level not at the record level. A locked record is released from control when it is rewritten by a PUT statement, removed by a DELETE statement, or when another GET is executed on the same designator (assuming that MULTIPLE is not in effect). If the PUT or DELETE statement fails, then the record will not automatically be freed. In this case, the record can be freed in one of the following ways:
If a GET statement queues the last record of a DS for shared access (i.e., without the LOCK option), a subsequent CREATE statement in the PM for the DS will fail. The CREATE statement will be unable to queue the DS for exclusive access. This can be avoided by:
|
The following four examples illustrate record reserving and locking using the MULTIPLE and LOCKED options. (The examples are for illustration only, and do not necessarily reflect exactly how ACCENT R buffering is handled.) Assume the GET statement retrieves records sequentially (from record 1 to 2, to 3, etc.). In this example, the third record has just been retrieved. These examples assume two records per block (i.e., records 1 & 2 in one block, 3 & 4 in another block, etc.).
The following chart shows several combinations of GET, LOCK, and FREE statements, and the results for each combination.
|
Previous GET |
Current Statement |
Explanation Error Type |
|
none |
FREE |
No error |
|
none |
GET LOCKED |
No error |
|
GET |
FREE |
No error |
|
GET |
GET LOCKED |
Error |
|
GET |
GET MULTIPLE |
Error, @AUX = “MULT” |
|
GET |
PUT |
Error, @AUX = “LOCK” |
|
GET |
GET LOCKED |
No error, ** |
|
GET LOCKED |
GET |
No error, ** |
|
GET MULTIPLE |
GET |
Error, @AUX = “MULT” |
|
GET MULTIPLE |
GET MULTIPLE LOCKED |
Error, @AUX = “MULT” |
**This is expensive in terms of resource usage because the block containing the record must be read from disk, even if it is the same block just read by the preceding GET.
NOTES: An error message and the value of @AUX is displayed if a GET is followed by a GET MULTIPLE.
GET NEXT FROM designator FAILED AT PM LINE nn MULT
Record level queuing actually occurs on a block level. A GET LOCKED on a record actually locks all records in the same block.
If @RECORD_WAIT seconds elapse while the GET statement waits for a busy record, the GET fails, @AUX is set to “BUSY,” and the PM continues. It is suggested that PM’s executing in the Concurrent Queued environment regularly check for the value of @AUX to determine if a record has been successfully retrieved.
The MULTIPLE and LOCKED options may only be used under the Concurrent Queued environment; that is, only when record-level queuing has been established by specifying operations in an ALLOWING clause in the RELATE statement.
The new NOT LOCKED option for GET allows access to records that are locked by another user for updating. This is called optimistic locking and provides flexibility in accessing records. Records accessed in this manner can be read (used for a report), but cannot be updated. The syntax is:
GET FROM designator NOT LOCKED…
Other variations of this statement are:
GET FROM designator LOCKED…
GET FROM designator…
The LOCKED option is used to gain exclusive access to a record and to let other programs know that changes are being made to the record. Neither NOT LOCKED nor LOCKED is used if waiting for the prior user to finish making changes before accessing the record. All other clauses applicable to the GET statement can be issued along with the NOT LOCKED clause even though they are not shown.
PUT can now be used on a record that was retrieved by GET with the NOT LOCKED option. In this case, ACCENT R re-reads and locks the record for the PUT process. ACCENT R keeps a copy of the original record read. A comparison is done between the user's original copy and the DS copy from disk to see if other users have made changes since the last time the record was read. If no changes have been made, ACCENT R writes the current user's updated version of the record, then unlocks it. If other users have made changes to the record, @AUX is set to "CHANG". The record is unlocked without writing the current user's changes. The record will have to be reprocessed so the prior changes can be acknowledged before the record can be changed again.
This feature allows flexibility in access and faster processing. It provides what is known as optimistic locking. Record blocks are only locked during the actual update. Optimistic locking can be used in applications where it is unlikely that any two users will try to access the same record at the same time.
Previously, a block of records had to be locked when the file was accessed. This procedure needlessly prevented other users from accessing records in that block. Processing was delayed until the locked block became available.
