SAS/ACCESS Interface to IMS-DL/I Software

Browsing and Updating with the SQL Procedure

The SQL procedure enables you to retrieve and update data from IMS-DL/I databases. You can retrieve and browse IMS-DL/I data by specifying a view descriptor in the SQL procedure's SELECT statement.

To update the data, you can specify view descriptors in the SQL procedure's INSERT, DELETE, and UPDATE statements. The view descriptor specified can access data from only one IMS-DL/I database path. You must use a PCB that allows you the appropriate level of access (insert, replace, delete, or all) for the segments that you want to update before you can edit the IMS-DL/I data.

The following list summarizes these SQL procedure statements:

SELECT retrieves, manipulates, and displays data from IMS-DL/I databases. A SELECT statement is usually referred to as a query because it queries the database for information.

DELETE deletes segments from an IMS-DL/I database.

INSERT inserts segments in an IMS-DL/I database.

UPDATE updates the data values in an IMS-DL/I database.

If you want to use the SQL procedure to join or access more than one IMS-DL/I database, you must use a PSB in your view descriptors that includes a PCB for each database to be accessed. Each view descriptor to be joined must use the same PSB. If you join two view descriptors that reference different paths in the same database, each view descriptor must reference in the PSB (that refers to the same database) a different PCB by using the PCB Index field. That is, to access the same database using different view descriptors in any SAS procedure, you must include multiple PCBs for that database.

When using PROC SQL, notice that the data are displayed in the SAS OUTPUT window in display manager mode and written to the SASLIST DDname in batch mode, interactive line mode, and noninteractive mode. This procedure displays output data automatically without the PRINT procedure and executes without a RUN statement when an SQL procedure statement is submitted.

Retrieving and Updating with the SQL Procedure

Note: The following PROC SQL examples assume the ACCTDBD database has not been updated by the earlier SAS/FSP examples. [cautionend]

You can use the SELECT statement to browse IMS-DL/I data described by a view descriptor. The query in the following example retrieves all the observations in the IMS-DL/I ACCTDBD database that are described by the VLIB.CUSTINFO view descriptor.

options linesize=132;

proc sql;
title2 'IMS-DL/I Data Retrieved by a PROC SQL query';
select * /* An asterisk means select all variables */
   from vlib.custinfo;

The OPTIONS statement is used to reset the default output width to 132 columns. IMS-DL/I Data Retrieved by a PROC SQL Query displays the query's output. Note that PROC SQL displays labels, which are the IMS-DL/I item names. In Version 7, the item names are also the SAS variable names, as shown here.

IMS-DL/I Data Retrieved by a PROC SQL Query

The SAS System IMS-DL/I Data Retrieved by a PROC SQL query SOC_SEC_ NUMBER CUSTOMER_NAME ADDR_LINE_1 ADDR_LINE_2 CITY STATE COUNTRY ZIP_CODE HOME_PHONE OFFICE_PHONE ---------------------------------------------------------------------------------------------------------------------- 667-73-8275 WALLS, HOOPER J. 4525 CLARENDON RD RAPIDAN VA USA 22215-5600 803-657-3098 803-645-4418 434-62-1234 SUMMERS, MARY T. 4322 LEON ST. GORDONSVILLE VA USA 26001-0670 803-657-1687 436-42-6394 BOOKER, APRIL M. 9712 WALLINGFORD PL. GORDONSVILLE VA USA 26001-0670 803-657-1346 434-62-1224 SMITH, JAMES MARTIN 133 TOWNSEND ST. GORDONSVILLE VA USA 26001-0670 803-657-3437 178-42-6534 PATTILLO, RODRIGUES 9712 COOK RD. ORANGE VA USA 26042-1650 803-657-1346 803-657-1345 156-45-5672 O'CONNOR, JOSEPH 235 MAIN ST. ORANGE VA USA 26042-1650 803-657-5656 803-623-4257 657-34-3245 BARNHARDT, PAMELA S. RT 2 BOX 324 CHARLOTTESVILLE VA USA 25804-0997 803-345-4346 803-355-2543 667-82-8275 COHEN, ABRAHAM 2345 DUKE ST. CHARLOTTESVILLE VA USA 25804-0997 803-657-7435 803-645-4234 456-45-3462 LITTLE, NANCY M. 4543 ELGIN AVE. RICHMOND VA USA 26502-3317 803-657-3566 234-74-4612 WIKOWSKI, JONATHAN S. 4356 CAMPUS DRIVE RICHMOND VA USA 26502-5317 803-467-4587 803-654-7238

You can specify a WHERE clause as part of the SQL procedure's SELECT statement to retrieve a subset of the database data. The following example displays a list of customers who have accounts with the Richmond branch of the bank:

title2 'IMS-DL/I Data Retrieved by a WHERE Statement';
   select *
      from vlib.custinfo
      where city='RICHMOND';

Notice that the PROC SQL statement is not repeated in this query. With the SQL procedure, you do not need to repeat the PROC SQL statement unless you submit another SAS procedure, a DATA step, or a QUIT statement between PROC SQL statements. IMS-DL/I Data Retrieved Using a WHERE Statement displays the customers of the Richmond branch who are described by VLIB.CUSTINFO.

IMS-DL/I Data Retrieved Using a WHERE Statement

The SAS System IMS-DL/I Data Retrieved Using a WHERE Statement SOC_SEC_ NUMBER CUSTOMER_NAME ADDR_LINE_1 ADDR_LINE_2 CITY STATE COUNTRY ZIP_CODE HOME_PHONE OFFICE_PHONE ---------------------------------------------------------------------------------------------------------------------- 456-45-3462 LITTLE, NANCY M. 4543 ELGIN AVE. RICHMOND VA USA 26502-3317 803-657-3566 234-74-4612 WIKOWSKI, JONATHAN S. 4356 CAMPUS DRIVE RICHMOND VA USA 26502-5317 803-467-4587 803-654-7238

You can use the UPDATE statement to update the data in an IMS-DL/I database as was done earlier in this chapter using the FSEDIT procedure. Remember that when you reference a view descriptor in an SQL procedure statement, you are updating the IMS-DL/I data described by the view descriptor, not the view descriptor itself. Use the WHERE clause to position the IMS-DL/I engine on the database segment to be updated by specifying values for the key fields of parent segments.

The following UPDATE statements update the values that are contained in the last observation of VLIB.CUSTINFO:

   update vlib.custinfo
      set zip_code = '27702-3317'
      where soc_sec_number = '234-74-4612';

   update vlib.custinfo
      set addr_line_2 = '151 Knox St.'
      where soc_sec_number = '234-74-4612';
         
   title2 'Updated Data in IMS-DL/I ACCTDBD Database';
   select *
     from vlib.custinfo
     where soc_sec_number = '234-74-4612';

The SELECT statement in this example retrieves and displays the updated data in IMS-DL/I Data Updated with the UPDATE Statement. (Because you are referencing a view descriptor, you use the SAS names for items in the UPDATE statement; the SQL procedure displays the variable labels as stored in the view.)

IMS-DL/I Data Updated with the UPDATE Statement

The SAS System Updated Data in IMS-DL/I ACCTDBD Database SOC_SEC_ NUMBER CUSTOMER_NAME ADDR_LINE_1 ADDR_LINE_2 CITY STATE COUNTRY ZIP_CODE HOME_PHONE OFFICE_PHONE ---------------------------------------------------------------------------------------------------------------------- 234-74-4612 WIKOWSKI, JONATHAN S. 151 Knox St. RICHMOND VA USA 27702-3317 803-467-4587 803-654-7238

Inserting and Deleting with the SQL Procedure

You can use the INSERT statement to add segments to an IMS-DL/I database or use the DELETE statement to remove segments from an IMS-DL/I database, as you did earlier in this chapter with the FSEDIT procedure. When inserting children under a parent segment, you must indicate the key values of the parent segments in the SET= statement. Use a view descriptor describing the entire path of data down to the lowest segment to be inserted. In the following example, the root segment that contains the value 234-74-4612 for the SOC_SEC_NUMBER variable is deleted from the ACCTDBD database. Note that any child segments that exist under the parent segment in this example will also be deleted.

options linesize=132;

  proc sql;
  delete from vlib.custinfo
     where soc_sec_number = '234-74-4612';

  title2 'Observation Deleted from IMS-DL/I 
           ACCTDBD Database';
  select *
     from vlib.custinfo;

The SELECT statement then displays the data for VLIB.CUSTINFO in IMS-DL/I Data with an Observation Deleted.

IMS-DL/I Data with an Observation Deleted

The SAS System Observation Deleted from IMS-DL/I ACCTDBD Database SOC_SEC_ NUMBER CUSTOMER_NAME ADDR_LINE_1 ADDR_LINE_2 CITY STATE COUNTRY ZIP_CODE HOME_PHONE OFFICE_PHONE ---------------------------------------------------------------------------------------------------------------------- 667-73-8275 WALLS, HOOPER J. 4525 CLARENDON RD RAPIDAN VA USA 22215-5600 803-657-3098 803-645-4418 434-62-1234 SUMMERS, MARY T. 4322 LEON ST. GORDONSVILLE VA USA 26001-0670 803-657-1687 436-42-6394 BOOKER, APRIL M. 9712 WALLINGFORD PL. GORDONSVILLE VA USA 26001-0670 803-657-1346 434-62-1224 SMITH, JAMES MARTIN 133 TOWNSEND ST. GORDONSVILLE VA USA 26001-0670 803-657-3437 178-42-6534 PATTILLO, RODRIGUES 9712 COOK RD. ORANGE VA USA 26042-1650 803-657-1346 803-657-1345 156-45-5672 O'CONNOR, JOSEPH 235 MAIN ST. ORANGE VA USA 26042-1650 803-657-5656 803-623-4257 657-34-3245 BARNHARDT, PAMELA S. RT 2 BOX 324 CHARLOTTESVILLE VA USA 25804-0997 803-345-4346 803-355-2543 667-82-8275 COHEN, ABRAHAM 2345 DUKE ST. CHARLOTTESVILLE VA USA 25804-0997 803-657-7435 803-645-4234 456-45-3462 LITTLE, NANCY M. 4543 ELGIN AVE. RICHMOND VA USA 26502-3317 803-657-3566

CAUTION:: Use a WHERE clause in a DELETE statement in the SQL procedure. If you omit the WHERE clause from the DELETE statement in the SQL procedure, you delete the lowest level segment for each database path that is defined by the view descriptor in the IMS-DL/I database. If the view descriptor describes only the root segment, the entire database will be deleted.

For more information on the SQL procedure, see the SAS Guide to the SQL Procedure: Usage and Reference.

Updating Data with the MODIFY Statement

The MODIFY statement extends the capabilities of the DATA step by enabling you to modify IMS-DL/I data accessed by a view descriptor or a SAS data file without creating an additional copy of the file. To use the MODIFY statement with a view descriptor, you must have update privileges defined in the PCB associated with the view, even if your program doesn't intend to modify the data.

You can specify either a view descriptor or a SAS data file as the data set to be opened for update by using the MODIFY statement. In the following example, the data set to be opened for update is the view descriptor VLIB.CUSTINFO, which describes data in the IMS-DL/I sample database ACCTDBD. See Appendix 2 for the code used to generate this view descriptor and the access descriptor MYLIB.ACCOUNT. The updates made to VLIB.CUSTINFO will be used to change the data in the ACCTDBD database. In order to update VLIB.CUSTINFO, you create a SAS data set, MYDATA.PHONENUM, to supply transaction information.

The MODIFY statement updates the ACCTDBD database with data from the MYDATA.PHONENUM data set in the following example:

data vlib.custinfo
     work.phoneupd (keep=soc_sec_number home_phone 
          office_phone)
     work.nossnumb (keep=soc_sec_number home_phone 
          office_phone);
  modify vlib.custinfo mydata.phonenum;
  by soc_sec_number;
  select (_iorc_);
     when (%sysrc(_sok))        
  /* soc_sec_number found in ACCTDBD        */
       do;
         replace vlib.custinfo;
         output phoneupd;
       end;
     when (%sysrc(_dsenmr))     
  /* soc_sec_number not found in ACCTDBD    */
       do;
         _error_=0;
         output nossnumb;
  /* stores misses in NOSSNUMB      */
       end;
     otherwise
  /* traps unexpected outcomes      */
       do;
         put 'Unexpected error condition: 
               _iorc_ = ' _iorc_;
         put 'for SOC_SEC_NUMBER=' soc_sec_number 
             '. Data step continuing.';
         _error_=0;
       end;
  end;
run;

For each iteration of the DATA step, the SAS System attempts to read one observation (or record) of the ACCTDBD database as defined by VLIB.CUSTINFO, based on SOC_SEC_NUMBER values supplied by MYDATA.PHONENUM. If a match on SOC_SEC_NUMBER values is found, the current segment data in ACCTDBD are replaced with the updated information in MYDATA.PHONENUM, then SOC_SEC_NUMBER, HOME_PHONE and OFFICE_PHONE are stored in the PHONEUPD data file. If the SOC_SEC_NUMBER value supplied by MYDATA.PHONENUM has no match in VLIB.CUSTINFO, the transaction information is written to the data file NOSSNUMB.

To further understand this type of processing, be aware that for each iteration of the DATA step (that is, each execution of the MODIFY statement), MYDATA.PHONENUM is processed sequentially. For each iteration, the current value of SOC_SEC_NUMBER is used to attach a WHERE clause to a request for an observation from VLIB.CUSTINFO as defined by the view. The engine then tries to generate a retrieval request with a qualified SSA from the WHERE clause. If the engine generates a qualified SSA, a GET UNIQUE call is issued, and data defined by the view are accessed directly. If the engine cannot generate a qualified SSA from the WHERE clause, a sequential pass of the database is required for each transaction observation in MYDATA.PHONENUM.

Print the PHONEUPD data file to see the SOC_SEC_NUMBER items that were updated. The output is shown in Contents of the PHONEUPD Data File:

/*  Print data set named phoneupd  */
proc print data=work.phoneupd nodate;
  title2 'SSNs updated.';
run;

Contents of the PHONEUPD Data File

The SAS System SSNs updated. SOC_SEC_ OBS NUMBER HOME_PHONE OFFICE_PHONE 1 667-73-8275 703-657-3098 703-645-4418 2 434-62-1234 703-645-441 3 178-42-6534 703-657-1346 703-657-1345 4 156-45-5672 703-657-5656 703-623-4257 5 657-34-3245 703-345-4346 703-355-5438 6 456-45-3462 703-657-3566 703-645-1212

Print the NOSSNUMB data set to see the SOC_SEC_NUMBER items that were not updated. The output produced by the following code is shown in Contents of the NOSSUNUMB Data File:

/*  Print data set named nossnumb  */
proc print data=work.nossnumb nodate;
   title2 'SSNs not updated.';
run;

Contents of the NOSSUNUMB Data File

The SAS System SSNs not updated. SOC_SEC_ OBS NUMBER HOME_PHONE OFFICE_PHONE 1 416-41-3162 703-657-3166 703-615-1212

Chapter Contents
Previous
Next
Top of Page

`SELECT`	retrieves, manipulates, and displays data from IMS-DL/I databases. A SELECT statement is usually referred to as a query because it queries the database for information.
`DELETE`	deletes segments from an IMS-DL/I database.
`INSERT`	inserts segments in an IMS-DL/I database.
`UPDATE`	updates the data values in an IMS-DL/I database.