The potential to store data in a non-sequential order in HDAM, DEDB, and PHDAM databases causes complications when dealing with ranges of data. Ideally, if searching for values between valueA and valueB, the search starts at valueA and issues Get Next calls until valueB. This sort of processing is possible only in an indexed sequential format. Where the keys are not in sequential order, positioning on valueA might be beyond some candidate segments. In this case, the result set will be incomplete. The following example shows the problem:
Select * from IMSSC1.IMSMP1.IMSDEMOS Where KEY >= 'A' and KEY <= 'D'
In this example, the randomizer has placed the records physically in the following key sequence:
C A D E B H F
Positioning on the first value, in this case A, then issuing Get Next calls until the end value of D would return only A and D, with candidate value C physically residing in the data set before A, and B after D.
The same issue arises for SELECT statements such as:
Select * from IMSSC1.IMSMP1.IMSDEMOS Where KEY = 'D' or KEY = 'B' or KEY = 'A'
In this case, three GU requests are needed instead of one GN request with a range (>='B' and <='D'). In case of the range request, IMS positions on the 'B' segment and reads by means of the twin pointer forward and misses the 'C' and 'D' segment because they are physically stored before the 'B' segment. The number of GU requests depends on the number of predicates in the query.
Therefore, if a range is requested of a HDAM, DEDB or PHDAM database, PowerExchange is forced to process the complete database sequentially, selecting all the required records as they are found.