Have you ever wished that an UltraQuest or 4GL LIST request finished in less time?
Have you ever found that different requests accessing the same tables vary widely in the time
they take to complete? Do you ever get complaints from your systems programmers that you
are using excessive system resources? If so, this article may be of interest to you.
The time required to perform an UltraQuest or 4GL LIST request may often be improved.
One of the most important methods of improving their efficiency is to apply screening criteria
that use keys. This is important for accessing all types of tables, but especially for tables
of type VSAM, 4GL BTREE, IMS, DB2, SQL Server, and Oracle. The techniques described
in this article refer to accessing keyed tables, when you can restrict access based on key values.
As an example, I will use the ED database, which is included in both the UltraQuest and NOMAD
installations, and described in Appendix B of the
UltraQuest and NOMAD Formatted Output Guide.
Suppose your goal is to find out all information about an employee. Suppose further that you know the
employee’s name, Norman Bass, and his employee number, 1108.
To efficiently retrieve information about Norman Bass, you will want to screen the Employees table
using its key. So your first step is to discover the Employee table’s key.
In UltraQuest, there are two methods for discovering a table’s key. One method is to use the Guided
Tour. From the UltraQuest menu bar, select File > New > Guided Tour.
Choose Database ED and Table EMPLOYEES, and click the Next button.
In the displayed Columns dialog, you may find out which column in a table is a key by
selecting the table in the Table column text box and looking at the
Attributes field near the bottom of the dialog to look for the keyname attribute;
if necessary, use the scrolling arrow keys or the Home and End keys. After you select
EMPLOYEES in the Table column text
box and scroll the Attributes field, you will see:
In the above dialog, notice that the Attributes field includes the following attribute:
This tells you that EMP_NO is the key for the Employees table.
Before moving on to the next step, notice that the Attributes field also tells you the type of table being
accessed. In this case, you may determine that the Employees table is a standard 4GL table from the
The other method of discovering a table’s key in UltraQuest is to use the Tree View. In the Databases
panel of the main UltraQuest screen, right-click on ED, select Open, right-click on
Employees, and select Properties. The keyname (and
segtype) properties may be viewed here:
A NOMAD user may discover a table’s key by using the 4GL SLIST command:
Now you know that the employee number, EMP_NO, is a key whereas the employee name,
EMP_NAME, is not. Thus, to make your screening efficient, you should perform data screening using
EMP_NO rather than EMP_NAME. Now you are ready to create the data screen.
If you are creating an UltraQuest request to find out information about Norman Bass, use the Guided
Tour’s Data Screening dialog to restrict the request to employee number 1108. In the Data Screening
dialog, select Table column EMP_NO, click the Add button,
click the Build expression button to bring up
the Data Screening / Build Expression dialog, and type 1108 into the text field:
In NOMAD, you may restrict access to employee Norman Bass by using the 4GL SELECT command:
select emp_no eq '1108'
You have seen how to create more efficient requests by finding the key of a table being accessed and
using it to screen data. In next month’s newsletter, more details on efficient screening and the use of
multiple keys will be discussed.
Take a Peek at UltraQuest Reporter 6.0
video gives a short overview of UltraQuest Reporter 6.0.
If you've never seen Reporter before, this is a great way to
see what it can do for you! Click
here to watch now.
Sign up now to see a deep dive video of UltraQuest 6.0
See our "Peek at Reporter 6.0" plus how to do an
extract, joins, job scheduling, the browser-based
UltraQuest library for reports and dashboards, and a
sample UltraQuest web application. For more information,
please contact Sales at
Check Us Out On
We would love for you to connect with us by clicking on any of
the social network links.
Click here to check out and see our postings on our Social
Did you know???
Select Business Solutions has a team of consultants that can help
you with anything from NOMAD/UltraQuest application support,
configuration, efficiency improvements to z/VM and z/OS systems
programming, installs, and IT configuration/tuning.
If you want to know more, please email
or call 203-383-4600 ext 2.
Select Business Solutions has always based product development
and enhancements on the feedback we get from our customers.
Features like the interfaces to Oracle and SQL Server
and the UltraQuest Scheduler were based directly on requests from
our customer base. We pride ourselves on being responsive
to our customers’ needs and the changing IT environment, and we
want to hear from you. If there is a product feature you
would like to see added, or if you have any suggestions on how to
make our products better, please email them to us at
3 Corporate Drive, Suite 216
Shelton, CT 06484
US Phone: 1.888.472.7347
UK Phone: 44.20.3642.0532
Outside US & UK: 1.203.383.4600
If you do not wish to receive future emails from
Select Business Solutions, please email: