Defining Keys

Keys are recommended for use in the JOIN operation or as selection criteria.

Adding Keys

Each table should have at least one unique key defined for it.

No two keys in the same table should have the same value for the KEY-ID. These tables would be duplicated in a FIND.

Equivalent keys in different tables should be defined similarly. That is, the order, length, and type of constituent columns should be the same.

Naming Keys

Equivalent columns or keys in different tables should be named the same to avoid confusion and to facilitate relating tables together. Make the entity-occurrence names unique within each table. Do not give a column the same name as a key in the same table. CA Dataquery selects the first entity-occurrence it finds with the requested name and ignores any other entity-occurrence with that name. Do not use CA Dataquery reserved words as entity-occurrence names. If it is necessary to do so, you can define aliases to use in accessing the data through CA Dataquery. See the CA Dataquery User Guide or CA Dataquery Reference Guide for a list of reserved words.

Nil Value Columns

When using nil valued columns in a key, users must be aware of which keys have been defined with INCLUDE-NIL-KEY=NO and what effect it has on their requests. If the value range of a Compound Boolean Selection Facility traversal key candidate includes either blanks or binary zeros, the key is not used because it cannot access all possible rows. This can adversely affect performance.

Signed Data

When predicates in a FIND statement see columns containing signed numeric data of packed or zoned decimal format, it is important to be as specific as possible in the CA Datacom Datadictionary TYPE-NUMERIC attribute about what sign codes the columns contain. This allows CA Dataquery to pass this information to the Compound Boolean Selection Facility, which in turn allows the Compound Boolean Selection Facility to evaluate the selection criteria more efficiently because it restricts the traversal key value range.

For example, if the data contains only positive values with the X'C' sign code, then specify P for this attribute. This attribute must agree with the actual data or incorrect results will be obtained.

Removing Keys

Be careful when you remove keys because existing queries may cease to function.

Note: You can use the DBUTLTY CXXMAINT ALTER CBSUSE to disable use of a key by the Compound Boolean Selection Facility. This enables you to check the impact of deleting a key prior to actually deleting it. For details, see the CA Datacom/DB Database and System Administration Guide and the CA Datacom/DB DBUTLTY Reference Guide.