| Tip of the Month
Efficient Access Using Table
Keys, Part 2
In the prior article, we learned that data access in both NOMAD and UltraQuest may be performed with greater efficiency
through the use of keys. We found that, in NOMAD, keys may be discovered by using the SLIST command and used in a SELECT
command. In UltraQuest, we learned that Properties in the tree view and Attributes in the Guided Tour will
tell you which columns are keys, and that these columns may be used to efficiently filter data in the Build Expression dialog.
In this article, we will find out what types of data filters are most efficient, as well as how to filter data efficiently
in tables with multiple keys. First, let's find out the properties of an efficient data filter.
An efficient data filter consists of 3 components:
- a key item (column)
- one of the following operators: EQ, GT, GE, LT, LE, BETWEEN, AMONG
- a constant variable, value, or expression
For example, let's use the Employees table in the ED database, where EMP_NO is the key. The following data filters are efficient:
EMP_NO EQ '1108'
EMP_NO BETWEEN (&EMP_NO_1, &EMP_NO_2)
EMP_NO AMONG ( '1102', '1104', ('11' CAT '06') )
In comparison, the following data filters are not efficient:
EMP_NAME EQ 'NANCY MANN' EMP_NAME: not a key
EMP_NO NE '1113' NE: inefficient operator
SEND LT SSTART SSTART: not a constant
A data filter remains efficient if it is joined to another data filter by the AND operator. However, the efficiency is lost
if data filters are joined by the OR operator.
The above rules apply to accessing VSAM and 4GL BTREE data. For more information, refer to the Smart SELECT Technique section
in Chapter 8 of the UltraQuest and NOMAD Reference Manual.
Similar rules apply to IMS access. The most significant difference is for an HDAM key, where the only efficient operator is EQ.
For the complete rules for efficiently accessing IMS databases, refer to the UltraQuest and NOMAD Interface Guide for IMS.
Access to DB2, Oracle, and Microsoft SQL Server is most efficient when rules like the above are applied to either
table keys or SQL index keys. A secondary level of efficiency may also be achieved when using the NOT, NE, LIKE, and CONTAINS
operators. For the complete rules for efficiently accessing SQL tables, refer to the Data Screening Efficiencies section in
Chapter 3 of the UltraQuest and NOMAD Interface Guide for DB2.
Now, let's find out how to filter data efficiently in tables with multiple keys. We will use the following sample table, ACCOUNTS:
ACCT_CTL: control information
ACCT_NO: the account number
ACCT_NAME: the account name
ACCT_TYPE: the type of account: 'A'=active,
'I'=inactive
Suppose you want information about accounts Larry (account number 111), Moe (222), and Curly (333), where the control key is 'A',
but only if the accounts are active.
The first step to creating efficient filtering criteria is to find the keys. This may be found in NOMAD by using the SLIST command:
slist accounts
MASTER ACCOUNTS TYPE=VSAM DDNAME=ACCOUNTS
INSERT=KEYED(ACCT_CTL,A,ACCT_NO,A);
...
In UltraQuest, as noted in the prior article, the keys may be found either on the main UltraQuest panel or in the Guided Tour.
For example, this is how the Properties for ACCOUNTS would appear from the main UltraQuest panel:
We now know that ACCT_CTL and ACCT_NO are the keys for ACCOUNTS. With the rules for efficient filtering, this means that our
request will be most efficient if our data filtering criteria:
- use the ACCT_NO key (rather than the non-key ACCT_NAME)
- use the AMONG operator to select multiple accounts (rather than using multiple OR operators)
The third portion of the desired data filter—selecting only active accounts—involves the non-key ACCT_TYPE. However,
this poses no problem. The reason is that as long as an efficient data filter is connected with another data filter by the
AND operator, the efficiency is not lost.
This means that in NOMAD, we can retrieve the desired account information by issuing the following command:
SELECT ACCT_CTL EQ 'A'
AND ACCT_NO AMONG (111, 222, 333)
AND ACCT_TYPE EQ 'A'
This same restriction may be added in UltraQuest by the following steps. First, select the 3 desired fields in the Data Filtering
dialog of the Guided Tour:
After clicking on Build expression, we define the first part of our filtering criteria in the Build Expression dialog:
In the above dialog, we restrict the control key to 'A'. Notice that our desired joining operator, AND, is selected by default.
Thus, we may click on Next to build the next portion of our filtering expression. After entering the desired data, the dialog
appears as follows:
We decided to use the AMONG operator for the account number, so we selected the AMONG operator. After a text box appeared,
we entered the 3 desired account numbers. Then, since AND is again selected by default, we click on Next
and define the last portion of our filtering criteria:
After typing in 'A' to view only active accounts, we may press the Done button, completing the process of creating
our desired data filtering criteria.
Note that this process may be made easier for both NOMAD and UltraQuest users in situations where the first key(s) have a fixed
value for a given application. In this situation, the value of the first key(s) may be restricted by a NOMAD programmer or
UltraQuest administrator including an RPROC in the database's profile procedure. For example, if the control key in the ACCOUNTS
table should always have the value 'A' in a given application, the following may be added to the ACCOUNTS database profile:
MODIFY ACCOUNTS RPROC ACCT_CTL EQ 'A'
We have now seen how data may be filtered more efficiently using one or more table keys and following specific guidelines
in the creation of the filtering criteria.
|
