Browsing and Updating IMS Data with the SQL Procedure

Retrieving and Updating Data with the SQL Procedure

Inserting and Deleting Data with the SQL Procedure

Using the SQL Procedure

The SQL procedure enables you to retrieve and update data from IMS databases. You can retrieve and browse IMS 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 specified view descriptor can access data from only one IMS database path. You must use a PCB that provides you with the appropriate level of access (insert, replace, delete, or all) for the segments that you want to update before you can edit the IMS data.

The following list summarizes these SQL procedure statements:

SELECT

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

DELETE

deletes segments from an IMS database.

INSERT

inserts segments in an IMS database.

UPDATE

updates the data values in an IMS database.

If you want to use the SQL procedure to join or access more than one IMS 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 is displayed in the SAS Output window in the SAS windowing environment 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 Data with the SQL Procedure

Note: The following PROC SQL examples assume that the AcctDBD database has not been updated by the earlier SAS/FSP examples.

You can use the SELECT statement to browse IMS data that is described by a view descriptor. The query in the following example retrieves all the observations in the IMS AcctDBD database that are described by the Vlib.CustInfo view descriptor.

options linesize=132;

proc sql;
   title2 'IMS 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. The following output displays the query's output. Note that PROC SQL displays labels, which are the IMS item names. In Version 7 and later, the item names are also the SAS variable names, as shown in the following output:

Results of Retrieving IMS Data with a PROC SQL Query

                                                        The SAS System       
                                               
                                         IMS 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 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. The following output displays the customers of the Richmond branch who are described by Vlib.CustInfo.

Results of Retrieving IMS Data Using a WHERE Statement

                                                      The SAS System       
                                                  
                                   IMS 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

Updating Data with the SQL Procedure

You can use the UPDATE statement to update the data in an IMS database as was done earlier in this section using the FSEDIT procedure. Remember that when you reference a view descriptor in an SQL procedure statement, you are updating the IMS data that is described by the view descriptor, not the view descriptor itself. Use the WHERE clause to position the IMS 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 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 the following output. (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.)

Results of Updating IMS Data with the UPDATE Statement

                                                      The SAS System       
                                                  
                                       Updated Data in IMS 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 Data with the SQL Procedure

You can use the INSERT statement to add segments to an IMS database or use the DELETE statement to remove segments from an IMS database, as you did earlier in this section 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 that describes 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 are also deleted.

options linesize=132;

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

   title2 'Observation Deleted from IMS 
           ACCTDBD Database';
   select *
     from vlib.custinfo;

The SELECT statement then displays the data for Vlib.CustInfo in the following output.

Results of Deleting IMS Data with the DELETE Statement

                                                      The SAS System       
                                                  
                                    Observation Deleted from IMS 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 database. If the view descriptor describes only the root segment, the entire database is deleted.

For more information about the SQL procedure, see the SAS SQL Procedure User's Guide.

Updating Data with the MODIFY Statement

The MODIFY statement extends the capabilities of the DATA step by enabling you to modify IMS data that is 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 define Update privileges in the PCB associated with the view, even if your program does not 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 sample database AcctDBD. See Example Data for the code used to generate this view descriptor and the access descriptor MyLib.Account. The updates made to Vlib.CustInfo are 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, SAS 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 is 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 that is defined by the view is 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.

To print the PhoneUpd data file to see the SOC_SEC_NUMBER items that were updated, submit the following statements.

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

The results are shown in the following output:

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

To print the NoSSNumb data set to see the SOC_SEC_NUMBER items that were not updated submit the following statements.

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

The results produced are shown in the following output:

Contents of the NoSSNumb 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