There is a potential problem when using the multiple server resilience software reading data in tables that may be fetched from more than one server via secondary indexes.
Internally every record on a database is allocated a unique identifier (sometimes referred to as a row id). This identifier is added as an extra invisible key component to every index entry. Whenever a record is fetched from a DP4 database the row id of the record is remembered. If the next operation is a fetch(NEXT) or fetch(PREV) the saved row id is used as part of the key. Normally this ensures that if a program is reading through a table on a secondary index each record will be returned exactly once, even if there are several records all with apparently the same key.
The saved row id is not used if the index actually used to read the data is the primary index for the table (which will be the case if the record is fetched on the main index, or a secondary index for which the key is a leading subset of the primary key). In this case the database manager sets the row id to a suitable value to ensure that the correct record is found.
If a table is accessed on more than one server this algorithm may break down when reading a table on a secondary index. For example suppose a customer table contains the following data:
Suppose a customer table contains the following data:
| Number | Name | Row ID on server 1 | Row ID on server 2 |
|---|---|---|---|
| 1 | ALBERT | 1 | 6 |
| 2 | JONES | 2 | 2 |
| 3 | JONES | 3 | 3 |
| 4 | JONES | 4 | 5 |
| 5 | JONES | 5 | 4 |
| 6 | SMITH | 6 | 1 |
and that the application program attempts to search through the data using a secondary index with the customer name as key.
If all the fetches are directed at server 1, the orders are returned in the order 1,2,3,4,5,6. If all the fetches are directed at server 2 the orders are returned in the order 1,2,3,5,4,6. However, if the fetches are divided up so that the first fetch is directed to server 2, the second to server 1, and the third and others to server 2 again the records will be returned in this order 1,2,6. Records 3,4,5 will be skipped, because when server 2 sees its second fetch it has a saved row_id of 6 which is greater than the row ID for any of the JONES records. In other scenarios it is possible to find the same record being fetched twice - once from one server and once from the other.
Previously this page advised that non-unique secondary keys should be modified so that they are in fact unique. Usually this involves adding the primary key as additional keys on the secondary index. In fact this does not solve the problem, although it certainly reduces it to some extent. The DP4 database manager should recognise such indexes as a special case and ignore the stored row id in the same way as for primary indexes. However, at the time of writing no such enhancement has been made.
In 4.621 and in patch releases to 4.619/4.620 AUXDISTR no longer switches between servers in the same way, and so the problem cannot arise, except in the event of a server going off line. If the table can be read locally the problem will never arise with 4.621 (because the local server cannot go offline).
If the table is not accessed locally the only complete solution to this problem is to switch tables to be read on a secondary index to use one server only. If the index is unique passing the value NO_FINDLIST as an additional flag to fetch will cure the problem at the cost of some performance degradation. However the DP4 database manager should be enhanced to automatically determine if the saved row id is valid, especially in view of the fact that the NO_FINDLIST flag is not available in QAB.