Chapter Contents

Previous

Next
SAS/ACCESS Interface to IMS-DL/I Software

Performance and Efficient View Descriptors

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.


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

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.  [cautionend]

View Descriptor WHERE Expression

Include a WHERE expression in your view descriptor by using a SUBSET statement when you want to

Selection criteria stored in a view descriptor can be protected with a password as well as operating system security. If an application specifies additional subset criteria, it is combined with the view descriptor selection criteria and treated as an AND search argument.

Application WHERE Expression

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.

DATA Step IF Statement

Use a subsetting IF statement in a DATA step execution when you

The IMS-DL/I engine generates SSAs only when all of the conditions in a WHERE expression meet eligibility requirements. The DATA step IF statement allows you to perform filtering that does not meet SSA eligibility requirements, while using a view descriptor WHERE expression or application WHERE expression to obtain the performance benefits from SSAs.

Combination of Methods

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:

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.

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:


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.


Chapter Contents

Previous

Next

Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.