SAS VSAM Processing for IBM Mainframe CICS EnvironmentsDavid Thul

Datcom Business Solutions, Brooklyn Park, MN

Abstract

Most mainframers view Base SAS as a toolto use to write simple reports using flat filesas input. It is the exception to find a shopthat uses the tool as part of their productionenvironment for not only report writing, butfor updating files as well. One purpose ofthis paper to show easy it is to maximizeSAS's power in your IBM CICS shop.

Introduction

The purpose of this paper is to show how toutilize The SAS System to read andmanipulate IBM VSAM files. It will givecode examples illustrating how to read IBMVSAM KSDS files (VSAM files from nowon) sequentially and randomly as well ashow to update, add and delete records. Thepaper will cite some everyday problemsassociated with this type of processing andhow to solve or work around theseproblems. It is outside the scope of thispaper to explain any of the technologiesbehind VSAM files. It is assumed that youhave the necessary working knowledge ofthese types of files.

Sequential Read

Reading VSAM files sequentially isidentical to reading flat files except youmust include the VSAM parameter on theINFILE statement as follows:

INFILE filename VSAM;

Where filename is the DD name pointing tothe VSAM file in the JCL.

Random Read (Full Key)

Of course, what good is it to use VSAM ifall you are going to do is read sequentially.In order to use the technology the way it wasintended, you want to read only the recordsyou need then stop processing. This ispossible by including more parameters withthe INFILE statement as follows:

fback = 0;INFILE filename VSAM BUFND=buff1BUFNI=buff2 KEY=keynameFEEDBACK=fback;

Where filename is the DD name pointing tothe VSAM file in the JCL, buff1 is databuffers, buff2 is index buffers. It is notnecessary to manipulate these buffers, butdoing so will add efficiency. I will not beputting it in any more examples in thispaper. Keyname is the value of the primarykey in the VSAM file that you areattempting to hit and fback is the name ofthe variable which stores the resultingfeedback number. This type of read requiresthat you are able to build the full key beforeyou attempt to read the VSAM file. If youwere successful with your read, you will geta value of zero in the feedback variable. Ifthe record you were attempting to read wasnot found, you will get a value of 16 in thefeedback variable. It is a good idea to resetthe feedback variable to zero before eachread.

Random Read (Partial Key)

In a perfect world, you will always have thefull key available to you. However, as oftenas not, you will have to read the VSAM filewith a partial key. What SAS does in this

case is gets you to the first record thatmatches the partial key and you will need toread sequentially until the partial keychanges. Here is the code:INFILE filename VSAM KEY=keynameGENKEY SKIP FEEDBACK=fback;

The skip and feedback parameters tell SASthat the key is a partial key and to hit on thefirst record that matches that partial key.You will need code to read sequentially andcheck each record during the sequential readto see if it meets the criteria you are lookingfor.

For example, lets say you are given a list ofcustomer Social Security Numbers and orderdates and you need to search the VSAMorder file for all orders for each customer onthe list with an order date matching the dateon the list. The order file is keyed bycustomer Social Security Numberconcatenated with an account trailer numberso that a single person can have multipleaccounts on the system. The date of theorder is also on the file, but not part of thekey. Here is some code you could use:

DATA hits;SET listfile(KEEP=ssn listdate);reckey=ssn;oldkey=reckey;fback = 0;INFILE filename VSAM KEY=reckeyGENKEY SKIP FEEDBACK=fback;

DO WHILE (reckey EQ oldkey AND fbackEQ 0);

INPUT keynamevsamdate…;

IF keyname EQ oldkey ANDvsamdate EQ listdate THENOUTPUT;

END;

We are checking that keyname eq oldkeyafter the read to make sure we are still on thecorrect partial key. With the above set-up, itis possible to read one past the last record inthe desired partial key group.

There is a catch here. You cannot use thepartial key to travel backward through theVSAM file. Therefore, if your list containsseveral dates that need to be searched for thesame customer and the VSAM file is notsorted in date order, you will have a problemwhen you try to read the same partial key forthe second time. The second attempt willyield a feedback of 16 (no record found).This will force you to do one of two things.First, you could reset the key to low valuesbefore performing the second read. Thiswill give you a hit on the second try. I havefound this method to be prohibitively slowthough. Alternatively you could keep all therecords for a given SSN and add a step tomerge this newly created file with theoriginal driver file by SSN/date and keep allmatches. This is the option I typically gowith. Remember to comment the merge stepbecause it will not be intuitive why thiswould be done.

Deleting a record

Because it is permanent, this is probably thescariest thing you will be asked to do. Iusually reserve this for when I am using atest file to test a process which adds a recordand I want to run the same key over andover again until I get it right. Having adelete job to run between tests is handy. Onoccasion, though, we are asked to removeproduction records. The code is extremelysimple:

OPTION VSAMUPDATE;DATA _NULL_;erasevar = 1;INFILE filename VSAMKEY = erasekeyERASE=erasevar FEEDBACK=fback

Notice the similarity between this code andthe code to read a record randomly. Theonly difference is the erase = erasevarargument. Erasevar is always set to 1 which

tells SAS we are going to delete the record.I tried to use a retain statement to seterasevar to 1 during compilation for sake ofefficiency, but that wouldn’t work.Remember to set the system optionvsamupdate on whenever a record is beingupdated (deleting is considered updating).

Adding a record

OPTION VSAMUPDATE;DATA NULL;SET driver(KEEP=outrec);INFILE vsamfile VSAM;FILE vsamfile VSAM;PUT @001 outrec $CHAR100.;

This process will add a record to the vsamfile. In this example, the data being addedin the field outrec is being read from theSAS file called driver. The outrec datacould have just as easily been read from acards statement or a flat file, etc. While it isnot intuitive why it is necessary to have theinfile statement, it is needed in order for thisprocess to work. The infile statement needsto point to the same file as the file statement.This is the file you will be adding to.Remember not to add a record thatduplicates an already existing key or youwill get a feedback code of 8.

Updating an existing record

OPTION VSAMUPDATE;_ERROR_=0;fback=0;INFILE vsamfile VSAMKEY=vsamkey FEEDBACK=fback;INPUT @001 all $CHAR100. ;FILE vsamfile VSAM;IF fback EQ 0 THEN DO; PUT @001 all $CHAR100. @024 newfield $CHAR08.;END;In this case, I am reading in the entire recordand writing it back out then over-writing

newfield with a new value. Remember toput newfield after the existing data has beenwritten. If you reverse this, you will over-write the newfield with the old data.

Conclusion

I have found SAS to be an incrediblypowerful tool for getting data quickly andefficiently to and from a CICS VSAM typeenvironment. It would be overly simplisticto say that some of these techniques don'ttake a bit of practice. However, if youcompare the amount of code required to dothese steps in SAS vs COBOL, you shouldsee the benefit.

Trademarks

SAS is a registered trademark of SASInstitute Inc.

Reference

SAS Institute, Inc.SAS Guide to VSAM Processing, Version 6First EditionCary, NC USA August, 1992

Contact Information

David ThulDatcom Business Solutions, Inc.9239 Telford BayBrooklyn Park, MN 55443E-mail: dthul@aol.com