Chapter Contents |
Previous |
Next |
SAS/ACCESS Interface to IMS-DL/I Software |
General Information |
Select only the items your program needs. Selecting unnecessary fields adds extra processing time.
Sorting data can be resource-intensive, even if
it is done using the SORT procedure. You should sort data only when sorted
data are needed for your program. Note that IMS-DL/I does not support the
ORDER BY clause or a BY statement in an application, such as
PROC PRINT
... BY
variable
...;
. If you have an IMS-DL/I
database that does not have an index and you want to use a SAS procedure that
requires the data to be sorted, you must first extract the data to sort them.
If you have an IMS-DL/I database that does have an index and you want to
use a BY variable other than an index key, you must also extract the data
to sort them before executing the SAS procedure.
Where possible, specify selection criteria that can be converted into SSAs to subset the amount of data IMS-DL/I returns to the SAS System.
Extracting Data Using a View |
Deciding How to Subset Your Data |
Once you determine that your application can benefit from using a subset of data, there are several ways that you can subset data in the SAS System. Use the following guidelines to determine when to use a view descriptor WHERE expression, an application WHERE expression, a DATA step subsetting IF statement, and when to use a combination of the methods.
Note: Regardless of the method that you choose, for
performance reasons you should always attempt to choose selection criteria
that can be converted by the engine into SSAs. If the engine cannot build
SSAs for your data request, then a sequential access method is used to retrieve
all path data defined by the view descriptor.
Include a WHERE expression in your view descriptor by using a SUBSET statement when you want to
For a more detailed description of how the WHERE expressions
work, see WHERE Statement Processing.
Use a subsetting IF statement in a DATA step execution when you
There are some comparison operators in the SAS System that cannot be incorporated into SSAs for DL/I function calls and that cannot be used with the DATA step IF statement. In these cases, you will have to evaluate the impact of a sequential retrieval to see if that method is acceptable. If it is not, then you can extract a subset of view descriptor data into a SAS data set (or define a DATA step view) using eligible selection criteria, then subset the data set using an application task to achieve the desired performance gains.
If needed, you can mix all of the filtering methods. For example,
data work.subset; set vlib.imsview; /*View can contain subset criteria*/ where (additional eligible conditions for IMS SSAs); if (ineligible criteria that would not generate SSAs); run;
Writing Efficient WHERE Statements |
To determine whether SSAs are being generated by your WHERE statement, set the option IMSDEBUG=Y or set the number of calls for which you want debugging information.
To ensure that your WHERE statements generate SSAs, do the following:
Operator | Alternate Form |
---|---|
= | = or EQ |
> | > or GT |
< | < or LT |
>= | => or GE |
<= | => or LE |
¬= | =¬ or NE |
& | * or AND (dependent AND) |
| | + or OR (logical OR) |
*Pad the =, >, and < operators with blanks on the right or left. |
The ability of the IMS-DL/I engine to generate SSAs also depends on the database type and on the operators that you use in your WHERE expression.
where partnum > 1000 where partnum > 1000 and orddate = '31JAN94'd
For a more detailed description of how WHERE statements work, see WHERE Statement Processing.
Identifying Inefficient SAS WHERE Conditions |
the view descriptor forces the IMS-DL/I engine to reposition itself to the beginning of the IMS-DL/I database for each value.
In this example, the WHERE statement tries to find two checking account records in the ACCTDBD database.
where chckacct = '345620145345' or chckacct = '345620134663';
Because the CUSTOMER segment is the root segment and the CHCKACCT segment is a child of CUSTOMER, the IMS-DL/I engine must issue a GU call for each checking account number that it wants to find. It does this in order to reposition itself at the start of the database. If it used GN calls, it might pass by one of the records because they are not in sequential order.
Specifying multiple values for a search field in a WHERE statement for HDAM IMS-DL/I databases permits the IMS-DL/I engine to create a WHERE key list. The IMS-DL/I engine issues calls that use, at a minimum, the first segment level SSA with a WHERE key list value. When no more data are retrieved from the IMS-DL/I database for a WHERE key list value, a GU call is used to reposition to the beginning of the database and the next WHERE key list value is used. Processing stops when all WHERE key list values have been used.
The following conditions do not allow the IMS-DL/I engine to generate SSAs. They cause all data from the IMS-DL/I database as defined by the view descriptor to be returned to the SAS System for further processing:
where custcode in ('24589689' '29834248') | state in ('CA' 'VA');
Identifying SAS WHERE Conditions That Are Not Acceptable to IMS-DL/I |
where c1=c4*3 where c4-c5
where c1 where (c1=c2)*20
where c2=D2||D3
.
where lastname=*'SMITH' where lastname like 'D_A%'
where cl=:abc
.
where ctime= '12:00't where ctime= '01jan60:12:00'dt
where name>'A' where ssn<='251-09-7384'
where stmtdate>'01JAN01'D
. STMTDATE has a DB Content of MMDDYY6.
where stmtdate = .(numeric) where name = (character)
where name='Smith' or stmtamt>0
. In this example, the NAME field is in the root segment, and the STMTAMT
field is in a child segment.
where var1<200
.
Chapter Contents |
Previous |
Next |
Top of Page |
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.