Considerations for CA Datacom/DB Native Access › INCLUDE-NIL-KEY

INCLUDE-NIL-KEY

When defining a key in CA Datacom/DB, there is a parameter called INCLUDE-NIL-KEY. This nil value is not related to the null value. In most cases, you should define the INCLUDE-NIL-KEY as YES. When this parameter is set to NO, an alphanumeric key value of spaces or a numeric key value of binary zeroes is considered nil and is not included in the index. Although CA Ideal has no control over the processing that takes place when this parameter is used, its improper use can be the culprit of poor performance in CA Ideal applications.

You might assume that you should use INCLUDE-NIL-KEY=NO whenever keys have a high occurrence of the nil value to free up index space. In fact, you should limit the use of this parameter to specific situations:

• Use INCLUDE-NIL-KEY when it is not necessary to access records that have an alphanumeric key value of spaces or a numeric key value of binary zeroes.
• Use two-byte binary key field designated as DAYS-LATE with
  INCLUDE-NIL‑KEY=NO. This keeps non-delinquent accounts with zero days late (a nil value) from being indexed. If only a small portion of the table contains delinquent accounts, a considerable savings results by eliminating both the cost of indexing and storing index entries for non-delinquent accounts.

Be aware of the consequences when choosing to use the INCLUDE-NIL-KEY=NO with CA Ideal. If the range of the search condition of a WHERE clause specifies the key field that has INCLUDE-NIL-KEY=NO and that range includes the nil value, then Compound Boolean Selection is forced to do a full file traversal to retrieve the data. You cannot use the key for access because the key cannot access all possible rows that satisfy the request.

Example

FOR ACCOUNTS   
   WHERE DAYS-LATE LE 0   
   . . .  
ENDFOR

Because the nil value is included in the key range, CBS cannot use this key to access the data records. All records must be read to return the correct data to the program.

Adding a non-keyed field to the WHERE causes the construction of a temporary index for the same reason-you cannot use the key because it does not point to all possible records that could contain the non-key value.

If you define a key as INCLUDE-NIL-KEY=NO, be sure that it reflects the way the data is accessed and that the CA Ideal programmer is aware of this key and the proper coding techniques.