|SAS/ACCESS Interface to IMS-DL/I Software|
When you create and use view descriptors, follow these guidelines to minimize the use of IMS-DL/I and OS/390 system resources and to reduce the time IMS-DL/I takes to access data.
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
... 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|
If a view descriptor describes a large IMS-DL/I database and you will use the temporary or permanent view descriptor many times, it may be more efficient to extract the data and place them in a SAS data file. Under the following circumstances you should probably extract data:
|Deciding How to Subset Your Data|
There are many reasons why you may want to subset or filter the data being returned from a database path defined by a view descriptor. The main benefit is performance. Retrieving a portion of the data in the database path is more efficient than retrieving all of the data in the path. Another reason is to enforce security measures, such as restricting users of view descriptors to certain subsets of 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
Use an application WHERE expression (SAS WHERE statement, clause, or data set option) when the guidelines specified in the previous section do not apply and you
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;
For all methods, it is possible that a change in criteria can cause an application that once produced SSAs to no longer produce them and resort to using a sequential access method. You can prevent this from happening with the SAS system option IMSWHST=Y. IMSWHST= is an invocation option that can be placed in the restricted options table so that it cannot be changed or overridden. Should the engine detect that no SSAs can be generated when this option is in effect, it will issue a message to the SAS log and terminate the executing task.
|Writing Efficient WHERE Statements|
Specifying a WHERE statement from which the IMS-DL/I engine can generate SSAs improves performance. The IMS-DL/I engine returns to the SAS System only those database segments that meet your selection criteria. If the IMS-DL/I engine cannot generate SSAs, all segment occurrences for each IMS record (as defined by the path of segments in the view descriptor) are returned to the SAS System for further processing.
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:
|=||= 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|
When your view descriptor
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|
The following examples are SAS WHERE conditions that are passed to the SAS System for further processing.
where c1=c4*3 where c4-c5
where c1 where (c1=c2)*20
where lastname=*'SMITH' where lastname like 'D_A%'
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.
Top of Page
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.