|SAS/ACCESS Interface to IMS-DL/I Software|
To create an access descriptor using the ACCESS procedure, you must first enter the database definition. IMS-DL/I does not store descriptive information about databases in a dictionary or database. After you have created an access descriptor, you can select variables from one path of data when you create a view descriptor. The IMS-DL/I engine is designed to get its information to build its own SSAs from the view descriptors and any supplied WHERE clause; these views are based on access descriptors that define the DL/I databases. The IMS-DL/I engine uses the information stored in the view descriptor to generate IMS-DL/I calls and to format the results of those calls into SAS observations. By design, view descriptors cannot access IMS/ESA control region message queues. Therefore, the IMS-DL/I engine interface is not able to access the message queues if it is executing in a BMP region.
The IMS-DL/I engine sequentially processes database data in order to flatten IMS records when no WHERE criteria exist. All data in the path specified by the view descriptor are returned in the order in which they were stored in the database when you use unqualified Get-Next (GN) processing. Therefore, the IMS-DL/I engine uses qualified segment search arguments (SSAs) to navigate the database path and maintain proper positioning, basing all qualified Get calls on the results of the previous call. You can use SAS WHERE statements to perform some level of direct access to a database.
You can see an example of this process by using the view descriptor VLIB.CHKDEB, which describes the CUSTOMER, CHCKACCT, and CHCKDEBT segments in the ACCTDBD database. First, the IMS-DL/I engine issues an unqualified Get Unique (GU) call to position itself at the beginning of the database. If the CUSTOMER segment were the only segment in the ACCTDBD database, the IMS-DL/I engine would then issue qualified Get Next (GN) calls for CUSTOMER until it reached the end of the database. However, because the ACCTDBD database is a multilevel database and the view descriptor defines more than the root segment, the processing is more difficult. To obtain the dependent segment, the IMS-DL/I engine must use the value returned in the I/O area for the field designated as the key in order to build a qualified SSA for the parent segment (in this case the root segment).
Next, the IMS-DL/I engine issues a Get Next Within Parent (GNP) call by concatenating the qualified SSA for the root segment with an unqualified SSA for the next level down in the hierarchy. The engine then takes the value of the field designated as the key field of that segment (as defined originally in the access descriptor) from the I/O area to generate a qualified SSA for that level. The next database call is a GNP with the two qualified SSAs concatenated with an unqualified SSA for the next level down in the hierarchy. The engine continues to combine qualified SSAs with an unqualified SSA for the next lowest level down the hierarchy until the lowest level (as defined in the view descriptor) is retrieved, or until a status of GE is returned; GE indicates no segment occurrence.
ACCTDBD Segments Described by VLIB.CHKDEB shows the segments described by the view descriptor, VLIB.CHKDEB, and the order in which the segments are accessed by IMS-DL/I. The calls generated by the IMS-DL/I engine to navigate the database are also described. Note that one SAS observation is made up of one complete path of data. If there is no child segment, the IMS-DL/I engine passes missing values in the fields for that segment to the SAS System.
ACCTDBD Segments Described by VLIB.CHKDEB
Shown below is the call output that is generated by the IMS-DL/I engine when it navigates the database (based on ACCTDBD Segments Described by VLIB.CHKDEB). It is printed to the SAS log by using the SAS system IMSDEBUG=Y. It shows how the IMS-DL/I engine uses the *U command code to maintain parentage in cases where no key field has been defined for one or more hierarchical levels in the view descriptor. See Using the *U Command Code for more information.
GU gets CUSTOMER 1 Status Code= GNP CUSTOMER*U-(SSNUMBEREQ667-73-8275) gets CHCKACCT 4 CHCKACCT*-- Status Code= GNP CUSTOMER*U-(SSNUMBEREQ667-73-8275) gets CHCKDEBT 8 CHCKACCT*U-(ACNUMBEREQ345620145345) CHCKDEBT*-- Status Code= GNP CUSTOMER*U-(SSNUMBEREQ667-73-8275) gets CHCKDEBT 9 CHCKACCT*U-(ACNUMBEREQ345620145345) CHCKDEBT*-- Status Code= GNP CUSTOMER*U-(SSNUMBEREQ667-73-8275) CHCKACCT*U-(ACNUMBEREQ345620145345) CHCKDEBT*-- Status Code=GE GNP CUSTOMER*U-(SSNUMBEREQ667-73-8275) gets CHCKACCT 5 CHCKACCT*-- Status Code= GNP CUSTOMER*U-(SSNUMBEREQ667-73-8275) gets CHCKDEBT 10 CHCKACCT*U-(ACNUMBEREQ345620154633) CHCKDEBT*-- Status Code= GNP CUSTOMER*U-(SSNUMBEREQ667-73-8275) gets CHCKDEBT 11 CHCKACCT*U-(ACNUMBEREQ345620154633) CHCKDEBT*-- Status Code= GNP CUSTOMER*U-(SSNUMBEREQ667-73-8275) CHCKACCT*U-(ACNUMBEREQ345620145345) CHCKDEBT*-- Status Code=GE GNP CUSTOMER*U-(SSNUMBEREQ667-73-8275) gets CHCKACCT 6 CHCKACCT*-- Status Code= GNP CUSTOMER*U-(SSNUMBEREQ667-73-8275) CHCKACCT*U-(ACNUMBEREQ345620180723) CHCKDEBT*-- Status Code=GE GNP CUSTOMER*U-(SSNUMBEREQ667-73-8275) CHCKACCT*-- Status Code=GE GN CUSTOMER*-- gets CUSTOMER 2 Status Code= GNP CUSTOMER*U-(SSNUMBEREQ434-62-1234) gets CHCKACCT 7 CHCKACCT*-- Status Code= GNP CUSTOMER*U-(SSNUMBEREQ434-62-1234) CHCKACCT*U-(ACNUMBEREQ345620104732) CHCKDEBT*-- Status Code=GE GNP CUSTOMER*U-(SSNUMBEREQ434-62-1234) CHCKACCT*-- Status Code=GE GN CUSTOMER*-- gets CUSTOMER 3 Status Code= GNP CUSTOMER*U-(SSNUMBEREQ436-42-6394) CHCKACCT*-- Status Code=GE GN CUSTOMER*-- Status Code=GB
Note: The data retrieval process
for GSAM databases is somewhat different. After issuing an initial close call
(CLSE) to establish position at the beginning of the database, the IMS-DL/I
engine uses unqualified GN calls to retrieve all the data in the database.
There are many ways to subset data in the SAS System by using
These all use SAS WHERE statement syntax. You do not have to use IMS-DL/I SSA syntax with the IMS-DL/I engine that runs under Version 7 of the SAS System.
The IMS-DL/I engine attempts to build SSAs from the WHERE conditions that you enter; condition refers to the expression(s) in the WHERE statement, clause, command, or option. The engine uses these SSAs to qualify each call to the database. Therefore, IMS-DL/I returns to the SAS System only those observations that meet your conditions. However, if the IMS-DL/I engine cannot convert the WHERE condition into SSAs, it passes all database segments referenced by the view descriptor to the SAS System, which then subsets and processes the data. Because it uses more resources to have the SAS System process WHERE conditions, you should try to use WHERE conditions that can be turned into valid SSAs when resources are a concern.
To specify WHERE conditions that the IMS-DL/I engine can use to generate SSAs, use one of the operators supported by IMS-DL/I. In the access descriptor, define search field names from the DBD for all the variables included in your WHERE condition when possible. See Writing Efficient WHERE Statements for a list of the operators IMS-DL/I supports.
Note: IMS-DL/I SSAs do
not support OR'd conditions from two different segments.
The engine uses the search field names that are entered in the view descriptor for the field names in the SSAs. Therefore, if you use a SAS variable in a WHERE condition for which you do not have a search field defined, the IMS-DL/I engine cannot generate SSAs for that WHERE condition.
If the WHERE statement or clause contains multiple conditions and any one of the conditions cannot generate a qualified SSA, then no qualified SSA is generated from the statement or clause.
If the IMS-DL/I engine can handle a WHERE condition, it uses the SEARCH= argument in the ITEM= statement to generate a qualified SSA. If possible, the engine combines the qualified SSAs that it generated to navigate the database with any WHERE condition SSAs. If both SSAs involve the same field, only the WHERE SSA is used to avoid a mutually exclusive situation. The engine then issues a path call to obtain the segments in the hierarchy down to the lowest level with an item specified in the WHERE condition. All segments in the path are retrieved and passed to the SAS System. Therefore, if you use a WHERE condition from which the IMS-DL/I engine can generate SSAs, the Program Specification Block (PSB) specified in that view descriptor must allow path calls for the segments in the hierarchy above and including segments with variables in the WHERE condition.
For example, if you enter the WHERE condition
WHERE CHCKACCT = 345620145345
the IMS-DL/I engine passes the following SSAs to IMS-DL/I:
The IMS-DL/I engine uses the results of this call to generate SSAs to navigate the database further and to flatten out the IMS record as defined in the view descriptor. The engine combines these navigational SSAs with the SSA that it generated from the WHERE condition for the CHCKACCT segment. The engine continues processing and retrieves the view descriptor's lowest level segment (CHCKDEBT), which is a child of the CHCKACCT segment. CHCKACCT has an ACNUMBER value that is equal to 345620145345 until the engine does not find another CHCKDEBT segment (status code GE).
To improve the efficiency of using a WHERE condition to subset your data, use the operators supported by IMS-DL/I. Enter the search field names of all variables in the WHERE condition so that the IMS-DL/I engine can pass only a subset of data to the SAS System for further processing. Use the SAS system option IMSDEBUG=Y to see whether your WHERE condition is generating SSAs directly.
For GSAM databases, the IMS-DL/I engine always
clauses to the SAS System for processing.
|Data Retrieval by Using a Secondary Index|
The SAS/ACCESS interface enables you to take advantage of secondary indexes in IMS-DL/I. A secondary index enables a SAS application to:
Because IMS-DL/I stores root segments in the sequence of their key fields, an application that accesses the data in another order would be inefficient. A database administrator (DBA), in conjunction with the SAS application user analyst/programmer, determines if a secondary index is needed and assists in laying out the secondary index. By using secondary indexing, IMS-DL/I can go directly to a segment based on a field that is not the key field.
You can define your access descriptors and view descriptors so that they can access secondary indexes, as described in this section.
In IMS-DL/I, when an application requests that a segment be returned based on the database call and SSA combination, the segment that is returned is called the target segment. If an application requests only one segment, that segment becomes the target segment. If a sequence of SSAs is used, then the lowest level segment retrieved in the hierarchy is the target segment. If you issue a path call for multiple segments, all the segments are returned to the I/O area.
To use secondary indexes with SAS applications, you have to assign certain IMS-DL/I parameters and use certain arguments when you create an access descriptor. The PCB that you use must specify the PROCSEQ parameter, which causes IMS-DL/I to use the secondary index. You also may need to use the PCBINDX= argument when you create a view descriptor so that the correct PCB is used by the engine. The secondary index is transparent to the application because it is automatically accessed when these parameters are assigned.
To create a secondary index, the DBD for the database must contain XDFLD statements to
One XDFLD statement is required for each secondary index
This section lists ways in which target and source segments can be related and, therefore, how you should define your descriptors in order to access the IMS-DL/I data through a secondary index.
The source segment supplies the field(s) values that comprise the secondary index. The secondary index stores these values in order with other information that specifies where the target segment is located for any value of the secondary index.
The XDFLD statement contains the NAME= value that is used in the SEARCH= argument, because doing so gives the secondary index the same name as will be used in the application's SSAs.
If the target segment is not the root segment, the database is conceptually restructured. The DBA and the SAS applications analyst/programmer lay out how the database will look conceptually. Physically, the database is still the same. This causes the SAS application to access the data by using the secondary index data structure of the database. For this case, in addition to the scenario described in (1), the entire access descriptor definition must describe the secondary index data structure and not the primary structure.
In this case, there is no secondary index data structure because the target segment is the root segment. However, the target and source segments are separate segments in the database.
In order for you to create an access descriptor using separate segments, you must add a dummy field to the end of the root segment. This dummy field must contain a length that matches the field(s) length for the target segment key value. In addition, the SEGLNG value for the entire root segment must be increased in the DBD to allow for this additional field. Any valid SAS name can be assigned to this dummy field, but the SEARCH= value must be the XDFLD name for the field from the DBD.
In essence, the dummy field is a virtual field in the access descriptor definition for the root. It does not physically exist there, but a SAS application can submit SSA references for the target (in this case the root) that is qualified on this field.
For example, consider a SAS application that uses
WHERE sasname EQ value
where sasname is the SAS variable name for the virtual field and value is a value for the field in the source segment. The IMS-DL/I engine properly builds a SSA for the target (root) that is qualified by using the XDFLD name for the field and the value from the WHERE clause.
This is the most complicated. It combines the scenarios described in (2) and (3). The same dummy field must be added to the target segment as in (3). In addition, the entire access descriptor must map to or define the secondary data structure that results from the target not being the root.
|Data Modification Processing|
Modifying a hierarchical database such as IMS-DL/I can be complicated. Therefore, you need to know how the IMS-DL/I engine operates in order to perform database modifications.
If you plan to use a view descriptor to update the database, the IMS-DL/I engine requires that you designate one search field as a key (that is, one key field) for each hierarchical level in the database. You designate the key field when you create the access descriptor on which the view descriptor is based. The key fields must be selected in the view descriptor.
Note: The search field that you designate as the key must be defined in the DBD as
a key field; otherwise, updating results
may be unpredictable. In addition, you cannot skip hierarchical levels in
a view descriptor that you want to use to update the database. Because the
IMS-DL/I engine uses path calls to perform most updates, no ROLB (ROLLBACK)
calls are required. If a path call fails, the engine returns an error to
the SAS System and no update is performed.
The engine, by default, issues checkpoints at the beginning and end of the update process. You can use the AUTOSAVE option with SAS/FSP software to increase the frequency of issuing checkpoints. Your update PSB must allow path call processing, and an I/O PCB must be included for checkpoint calls.
The only time an update is performed with multiple IMS-DL/I
calls is when you request both an update and an insert. For example, you
could use the FSEDIT procedure to update a CUSTOMER segment and, on the same
display, enter information to insert a new CHCKACCT segment under the CUSTOMER
parent segment you just modified. In this case, if the insert call fails
after the engine has processed the modification, the IMS-DL/I engine issues
another update call that replaces the modified parent segment with the original
data in that segment. This process uses fewer resources than a ROLB call.
(See Using the SAS/ACCESS Interface to IMS-DL/I DATA Step Interface for information on ROLB
and other non-database access calls.)
You can delete only the lowest existing segment defined in the view descriptor.
For example, if your view includes the CUSTOMER and CHCKACCT segments only and you delete a CHCKACCT segment, any CHCKDEBT segments under CHCKACCT are also deleted even though they are not defined in the view descriptor.
If your view descriptor includes all the hierarchical levels but a particular segment has no children, the lowest existing segment is deleted. For example, if a CUSTOMER segment occurrence has no CHCKDEBT segments under a CHCKACCT segment, issuing the DELETE command deletes the CHCKACCT segment. If you then have only a CUSTOMER segment and you issue the DELETE command, the CUSTOMER segment is deleted.
Note: You cannot delete segments in a GSAM database.
There are three ways to insert new data in an IMS-DL/I database by using SAS/FSP software:
Note that you can add segments only at the end of a GSAM database.
You can also use the APPEND procedure, DATA step MODIFY
statement, or an INSERT statement in the SQL procedure to add data to an IMS-DL/I
database. To insert a path of data, use a view descriptor that describes
the entire path to be inserted. To insert child segments under a parent segment,
enter the key field value of the parent segment. The new data will be inserted
under the existing parent.
The IMS-DL/I engine compares the data you entered to the data that are stored in the I/O area from the last call. If you change any data in a path of data, the engine replaces only the segments that have changed in the path. If you change the key field defined in the view descriptor, the IMS-DL/I engine inserts a twin segment occurrence under the current parent segment.
You cannot update segments in a GSAM database.
Top of Page
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.