Creating Dataviews for CA Datacom/DB Native Command Access › Rules for CA Datacom/DB Native Command Access › Cataloging the Dataview › INCLUDENILKEY

INCLUDENILKEY

When defining a key in CA Datacom/DB, there is a parameter called INCLUDE‑NIL‑KEY.

This nil value has nothing to do with the null value used in DB2 and CA Datacom/DB. In most cases, you should define 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 very 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.
• A two‑byte binary key field designated as DAYS‑LATE with INCLUDE‑NIL‑KEY=NO 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. Consider this 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 constructs a temporary index for the same reason-the key cannot be used because it does not point to all possible records that could contain the non‑key value.

• If you do 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.