Formatting Output Records – DFSORT Tutorial

Q. What does one understand by the phrase – Formatting of Output Records? Why do we need to format them?

When we generate an Output using DFSORT sometimes, the records are not in a presentable format. For example, in the output records, there might not be a space between two fields. Or you might want to insert a $ sign for the currency field. You might want to re-order the fields, or display only selected fields/columns in the Output. This type of formatting of records can be done using DFSORT. You can format the records to be sorted in a particular way, prior to the sorting. This is also possible. So, for better presentation/easier understanding of the report, we re-format the records produced in Output. For re-formatting of records, there are mainly 3 tools at your disposal - BUILD - Reformat each record by specifying all of its items one by one. Here, you can add,delete or re-arrange fields. OVERLAY - It allows you to change (edit) the values in specific columns. IFTHEN - Reformats different records in different ways. The above re-formatting tools can be used with INREC, OUTREC and OUTFIL. INREC - Used to reformat the records before applying sort. OUTREC - Used to reformat the records after applying sort.

Q. How to generate a Report, which displays only specific(desired) columns in the Output?

Suppose you are given the following Input Dataset. ----+----1----+----2----+----3----+----4----+----5----+----6----+----7-- AG10001  RAMESH CHANDRA GUHA INDIA     MAHARASHT MUMBAI    01000        AG10002  QUASAR SHABBIR CHUN INDIA     MAHARASHT PUNE      02000        AG10003  VEERAMANI DARYAGANJ INDIA     MAHARASHT NAGPUR    03000        AG10004  DELPHAN FERANDAZ    INDIA     TAMIL NAD CHENNAI   04000        AG10005  PADMAVATHI SUNDARAM INDIA     TAMIL NAD SALEM     05000        AG10006  NEELESHWARI IYER    INDIA     TAMIL NAD ERNAKULAM 06000        AG10007  AJAY NANUBHAI PARSA INDIA     WEST BENG KOLKATA   07000        AG10008  MEHUL POPATLAL SUTA INDIA     WEST BENG KHARAGPUR 08000        AG10009  KRITHIKA RAMANUJAM  INDIA     WEST BENG SUNDARBAN 09000        AG10010  ANKUR CHOUDHARY     INDIA     PUNJAB    AMRITSAR  10000        AG10011  HIREN NITIN SHETH   INDIA     PUNJAB    JALANDHAR 11000        AG10012  DARSHAN SHAH        INDIA     HARYANA   CHANDIGAR 12000        In the output report, you are asked to display only the name of the Insurance Agent and the Sales Figure. Since, we want reformat records after applying SORT, we will OUTREC FIELDS Control statement. We want to have two columns/fields in the ouput - NAME   Start-Position 10    Length 20 SALES  Start-Position 60    Length 05 Thus, we can write the Batch JOB/JCL for the above requirement as follows : ----+----1----+----2----+----3----+----4----+----5----+----6----+----7-- //CAA0176J JOB A123,QUASAR,NOTIFY=&SYSUID                               //STEP01 EXEC  PGM=SORT,REGION=1024K                                    //SORTIN DD    DSN=CAA0176.DEMO.SORTIN,DISP=SHR                         //SYSPRINT DD  SYSOUT=*                                                 //SYSOUT DD    SYSOUT=*                                                 //SORTOUT DD   DSN=CAA0176.DEMO.SORTOUT,DISP=(OLD,KEEP,KEEP)            //SYSIN  DD    *                                                          SORT FIELDS=COPY -                                                      OUTREC FIELDS=(10,20,60,5)                                            //                                                                      Note that, we have written SORT FIELDS=COPY, which simply copies the Input Records to the Output, and displays NAME and SALES Columns. Upon submitting the above Batch JOB/JCL, we get the following Output : ----+----1----+----2----+ RAMESH CHANDRA GUHA 01000 QUASAR SHABBIR CHUN 02000 VEERAMANI DARYAGANJ 03000 DELPHAN FERANDAZ    04000 PADMAVATHI SUNDARAM 05000 NEELESHWARI IYER    06000 AJAY NANUBHAI PARSA 07000 MEHUL POPATLAL SUTA 08000 KRITHIKA RAMANUJAM  09000 ANKUR CHOUDHARY     10000 HIREN NITIN SHETH   11000 DARSHAN SHAH        12000 A lot more formatting is possible on the above report. Read on to know more...

Q. What if I wanted the SALES field/column to be displayed before(first) and then the NAME Field/Column? How do you re-order columns in the Output Report?

If we would like to display the SALES Column first, and then the agent-names, we can simply change the order in OUTREC FIELDS statement.   SORT FIELDS=COPY -                                                      OUTREC FIELDS=(60,5,10,20)   Upon submitting a Batch JOB/JCL with the above control statements, we get the following output : ----+----1----+----2----+ 01000RAMESH CHANDRA GUHA 02000QUASAR SHABBIR CHUN 03000VEERAMANI DARYAGANJ 04000DELPHAN FERANDAZ    05000PADMAVATHI SUNDARAM 06000NEELESHWARI IYER    07000AJAY NANUBHAI PARSA 08000MEHUL POPATLAL SUTA 09000KRITHIKA RAMANUJAM  10000ANKUR CHOUDHARY     11000HIREN NITIN SHETH   12000DARSHAN SHAH

Q. I’ve understood, how to re-order the columns, but the above output report looks like greek(Cyrilic) – there should be some space between the fields SALES and NAME. How do you insert a SPACE/SPACES?

Since the above output looks very clumsy, to make this report more readable we would like to insert 2 blank spaces between SALES and AGENT-NAME. To insert a blank space, we use X. To insert 2 blank spaces, we write 2X.   SORT FIELDS=COPY -                                                       OUTREC FIELDS=(60,5,2X,10,20)                                          Thus, upon submitting a Batch JOB/JCL with the above Control Statements, we get the following Output Report. ----+----1----+----2----+- 01000  RAMESH CHANDRA GUHA 02000  QUASAR SHABBIR CHUN 03000  VEERAMANI DARYAGANJ 04000  DELPHAN FERANDAZ   05000  PADMAVATHI SUNDARAM 06000  NEELESHWARI IYER   07000  AJAY NANUBHAI PARSA 08000  MEHUL POPATLAL SUTA 09000  KRITHIKA RAMANUJAM 10000  ANKUR CHOUDHARY    11000  HIREN NITIN SHETH  12000  DARSHAN SHAH       If you want to fill in blanks in the last field, you use the syntax c:X, where c is the column till where the padding takes place. For example, OUTREC FIELDS=(60,5,2X,10,20,140:X)

Q. What if I wanted to insert a Character string like ‘IS SALES FOR’ between the two fields? What if I wanted to insert a Date and Timestamp?

You can insert characters at any position by tying C'<Characters>'. Suppose all records have length 65 bytes. Suppose, you would like to insert at the end of each record the current date and timestamp. Then, we would insert DATE1 and TIME2 fields respectively. This is the Batch JOB/JCL for doing it. ----+----1----+----2----+----3----+----4----+----5----+----6----+----7-- //CAA0176L JOB A123,QUASAR,NOTIFY=&SYSUID                               //STEP01 EXEC  PGM=SORT,REGION=1024K                                    //SORTIN DD    DSN=CAA0176.DEMO.SORTIN,DISP=SHR                         //SYSPRINT DD  SYSOUT=*                                                 //SYSOUT DD    SYSOUT=*                                                 //SORTOUT DD   DSN=CAA0176.DEMO.SORTOUT,DISP=(OLD,KEEP,KEEP)            //SYSIN  DD    *                                                          SORT FIELDS=COPY -                                                      OUTREC FIELDS=(60,5,2X,10,20,X,DATE1(/),X,TIME2(:))                   //                                                                      Upon submitting the above Batch JOB/JCL, we get the following Output Report, with the Date and Time printed : ----+----1----+----2----+----3----+----4----+ 01000  RAMESH CHANDRA GUHA  2009/07/29 03:32 02000  QUASAR SHABBIR CHUN  2009/07/29 03:32 03000  VEERAMANI DARYAGANJ  2009/07/29 03:32 04000  DELPHAN FERANDAZ     2009/07/29 03:32 05000  PADMAVATHI SUNDARAM  2009/07/29 03:32 06000  NEELESHWARI IYER     2009/07/29 03:32 07000  AJAY NANUBHAI PARSA  2009/07/29 03:32 08000  MEHUL POPATLAL SUTA  2009/07/29 03:32 09000  KRITHIKA RAMANUJAM   2009/07/29 03:32 10000  ANKUR CHOUDHARY      2009/07/29 03:32 11000  HIREN NITIN SHETH    2009/07/29 03:32 12000  DARSHAN SHAH         2009/07/29 03:32 In the next tutorial, find out how to replace certain characters/fields, eliminate junk characters like LF, changing lower case to upper case and vice versa and much more.. Hope you’ve enjoyed!

Sort, Merge and Copy Files

Q. What is DFSORT?

DFSORT is a free software-program supplied by IBM. This powerful software can help you sort(arrange) the data, merge it, copy and analyse it, and generate different Reports. With DFSORT Tool, you could aggregate(SUM-up) data. For example, I have a list of the Employees working in the company, the Department under which they work and the salaries they are paid. Say, you want to know the breakup of the total-Salary, by each department. Just like the Auto-sum facility in Microsoft-Excel, I can run the DFSORT Tool to Sum-up the cell-values on the Salary column(15,5), by each Department(10,2) and generate a report. I just code couple of these instructions for DFSORT.   And, voila! Here’s what the report looks like.   With DFSORT Software, you can analyse data and produce different Business-reports. DFSORT is not just another sort-tool, it is a complete power-packed analysis and reporting tool from IBM.

Q. How do I run the DFSORT Tool?

To use the DFSORT Tool, you run the free-software Program SORT. The SORT Software-Program expects you to supply four Files.          When you work with the SORT Software, //SYSIN DD File(the Brain of SORT Program) is where SORT Expects to read the Processing-Instructions. These special-instructions control the processing. Hence, you need to craft the SYSIN Control-Instructions skillfully or with dexterity. You would hear Mainframe Programmers, refer to "Control-Instructions" as the SORT Control-Card. This is how I write a Job, to run the SORT Software-Program. I have set SORTIN DD Statement to point to my Input-Data File SYSADM.EMPLOYEE.LIST. This list of Employees Data is processed by the SORT Program, according to the Processing-Instructions given at SYSIN DD Statement. Just as you would keep all your Holiday Trip-photo's together in one folder, it's good to create a Library or PDS of the frequently used SORT Control-cards. My Personal Library for all SORT Control-Cards is  SYSADM.DEMO.CNTLCARD. Under it, I have stored the Control-Instructions in a Member SUMSAL. After processing the Data, the Output Records are stored in SORTOUT File which points to SYSADM.EMPLOYEE.SORT.OUT. The Log of the SORT Program is stored in SYSOUT File. To run the SORT Program, keep the above JCL in your Back-pocket as a pre-defined template. All you'd need to do, is plug-in the right SORT Control-card.

Q. What are the rules to be followed while writing a SORT Control Card?

Any Control Instructions like SORT, MERGE etc. scan be written anywhere between Columns 2-71. All SORT Control Instructions follow the same general format. They consist of an Operation like SORT or MERGE and so on, followed by operands. The operand has a Parameter - FIELDS. The FIELDS Parameter is assigned values, in the form of parameter=value or parameter=(value1,value2,...).

Q. What are the different Data-Formats in SORT Software-Program?

DFSORT supports different Data-Formats. To process data, you need to tell DFSORT what's the type of data – are they Textual Character Strings,  or is it Numeric Integer data – in DISPLAY, Binary or Packed Decimal format, or are they Floating-point numbers, are you processing Dates or Times, what is the data-format exactly? I've prepared a List of the different Data-Formats commonly used in DFSORT, and the corresponding COBOL Data-type.

Q. How do I sort the data in the File on Key Fields?

On an MS-Excel Spreadsheet, every cell named like A1, B2 and so on. Like-wise, when data is stored in a Mainframe-file each field or column is numbered. How do you tell SORT Software Program, the data in which column or Field is to be processed? How do you refer to a particular-field or column? To refer to any column, you must know the position and the Length. For example, the first-name start at Position 1, and have Length=7. Thus, the first-name column is (1,7). Like-wise the Middle-name column is (11,10). The Last-name Column would be (21,10).  The above EMPLOYEE Input Data File has First-name, Middle-name and Last-name as the Key-Fields. I want to sort the Data-Records in Alphabetical order of the Key-Fields. How to write the SORT Control-Card? To arrange(sort) the data-records, in order of the Key-Fields, you code SORT Operation. The SORT operation takes a parameter FIELDS. FIELDS Parameter specifies the Key-Fields of the Data-Record. I wrote the SORT Control Card like this - FIELDS Parameter specifies the key fields, followed by the Data-format. Since , first-name , middle-name and Last-name are Textual Character Strings, I’ve specified the format as CH. I want to arrange the data in Ascending order, so I’ve coded A. D stands for Descending. When you run the SORT Software Program, with above Control-Card, the Output Dataset looks like this -

Q. How do you Merge Datasets?

Say, you have got a List of Books present on the Physics Shelf of a Library. stored in the Mainframe File SYSADM.PHYSICS.BOOK.LIST. The Books are sorted alphabetically by the Title. The Mainframe File SYSADM.CHEM.BOOK.LIST contains the List of Books kept on the Chemistry Shelf of the Library. The Chemistry Books are arranged as well by the title. The Librarian proposes to have a new shelf for Basic Sciences, which shall house both Physics and Chemistry Books. For this , the Librarian  needs a combined list of Physics and Chemistry books, in the right alphabetical order of the Title. How do I Merge the Physics-books with the Chemistry-books, such that the Output is sorted? How to write the SORT Control-Card? To Merge-and-mix the Data Records of two Files, you code the MERGE Operation. Like the SORT Operation, the MERGE Operation has a FIELDS Parameter. The FIELDS Parameter, specifies the Key-Field or column, on which the two files are to be Merged. This is how, I've coded the SORT Control Card, to merge the Two Datasets on the Title Column - The data-records are to be merged by Alphabetical-order of the Title Field, which starts at Position 1, and is 39 Bytes wide. The Title Column is thus referred to as (1,39) in the FIELDS Parameter. The Title is Textual Character string, so the Data-Format is CH, and I want to arrange in the Ascending Order – A. When you run the SORT-Software with the above Control-Card, the Output Dataset SYSADM.BASIC.SCIENCES.MERGE.OUT looks like this - When you run the SORT-Software, to process many Files(not just 1 Dataset), say you wanna Merge 2 Files, or 3 Files, instead of coding //SORTIN DD Card, you code //SORTIN01 DD, //SORTIN02 DD, //SORTIN03 DD Cards and so on..

MVS System Catalog – Finding information about VSAM Cluster from the Catalog

Q. What’s a System Catalog? Why have one?

A Catalog is like an address book. A real-life address book stores the names of people, and their addresses(where they live/reside, where they work etc..). In a Mainframe System, there are many files(Datasets), that live in different areas of Memory(Storage). Each dataset is found in a different area/location. If you wanted to find a particular dataset, do anything with it, read it, update it etc., you would need to lookup where the dataset is. How do you know, in this vast expanse of storage space consisting several DASD Devices, each having many volumes, where your dataset lies. Its like finding a needle in a haystack. Thanks to the MVS System Catalog. I call it the Address Book of the MVS O/S. You want to access a dataset, but don’t know where it is? Ask the System Catalog. The System Catalog tells you, where in the whole world of storage space, you dataset is.. Storage Devices like DASD are just like Hard-disk Drives. Inside, there are a no. of plates(platters)/circular disks. Each disk is called a volume. Every disk volume records information in tracks. These are just like the tracks on vinyl record/compact disk. The Address Book of the MVS O/S – System Catalog stores the name of the dataset, along with the disk volume on which it resides. Have you ever wondered, on Windows OS, why can't you have 2 files with the same name in a folder? In MVS O/S, on the same lines, you can't create two datasets with the same identical names. Its pretty easy to understand why. The MVS O/S keeps track of all the datasets. It stores information about the name of a dataset, and where this dataset is present on the disk(which volume). In other words, MVS O/S records the name and disk volume of the dataset.The list of all the datasets and their locations are stored in an area called MVS System Catalog. We say, that the dataset is cataloged. If MVS, does not have any information about the whereabouts of a dataset, that is it does not know, where to look for a dataset, we say that the dataset is un-cataloged.

Q. I would like to see what the MVS System Catalog looks like? How do see what’s in there?

When you would like to see(peek in) the entries of the MVS System Catalog, you must use the LISTCAT Command of the IDCAMS utility. In case of VSAM Files, the  MVS System Catalog also stores information about the Alternate Indexed, no. of records, control interval size, free space etc. So, you can find a lot meta-information(Information about data) for VSAM Files in the MVS System Catalog. You can run LISTCAT from TSO. You must type TSO LISTCAT, and you can see the output. The LISTCAT Command can be used to find the entries of the VSAM Cluster in the Catalog. So, if you want to see the entries of your VSAM file in the Catalog, you can use the LISTCAT AMS Control Statement. The LISTCAT Command has a few options : 1) ENTRIES - This is used to filter out the entry that you want to be listed. For example, if you want to see all the catalog entries with the name CAA0176.DEMO.KSDS, you can code the ENTRIES option as ENTRIES(CAA0176.DEMO.KSDS) To code multiple files, you can simply put the continuation character : ENTRIES(CAA0176.DEMO.KSDS1,          -         CAA0176.DEMO.KSDS2,          -         CAA0176.DEMO.KSDS3) You can also make use of a generic name. Suppose, you wanted to list all entries in the catalog having the dataset names starting with CAA0176.DEMO. ENTRIES(CAA0176.DEMO.*) 2) CLUSTER - This is used to specify that only the cluster entry is to be listed. If this is not mentioned, all the index entries would also be printed. 3) ALL - This means all field of the entry are to printed. A Simple Batch JOB/JCL making use of the LISTCAT Command is shown here : ----+----1----+----2----+----3----+----4----+----5----+----6----+----7-- //LISTCAT3 JOB A123,QUASAR,NOTIFY=&SYSUID                               //STEP1  EXEC  PGM=IDCAMS                                               //SYSPRINT DD  DSN=CAA0176.DEMO.JOBLOG,DISP=(OLD,KEEP,KEEP)             //SYSIN    DD    *                                                        LISTCAT -                                                                     ENTRIES(CAA0176.DEMO.KSDS) -                                            CLUSTER -                                                               ALL                                                             /*                                                                      Upon submitting the above Batch JOB/JCL. the JOB Log shows the following information - DCAMS  SYSTEM SERVICES                                           TIME:   LISTCAT -                                                                     ENTRIES(CAA0176.DEMO.KSDS) -                                            CLUSTER -                                                               ALL                                                             CLUSTER ------- CAA0176.DEMO.KSDS                                            IN-CAT --- CATALOG.T.ICFTST03                                           HISTORY                                                                   DATASET-OWNER-----(NULL)     CREATION--------2009.208                   RELEASE----------------2     EXPIRATION------0000.000                 SMSDATA                                                                   STORAGECLASS -------WORK     MANAGEMENTCLASS---SORTMC                   DATACLASS --------(NULL)     LBACKUP ---0000.000.0000                   BWO STATUS------00000000     BWO TIMESTAMP---00000 00:00:00.0           BWO---------------(NULL)                                              RLSDATA                                                                   LOG ----------------(NULL)   RECOVERY REQUIRED --(NO)     FRLOG -        VSAM QUIESCED -------(NO)    RLS IN USE ---------(NO)                   LOGSTREAMID-----------------------------(NULL)                          RECOVERY TIMESTAMP LOCAL-----X'0000000000000000'                        RECOVERY TIMESTAMP GMT-------X'0000000000000000'                      PROTECTION-PSWD-----(NULL)     RACF----------------(NO)                 ASSOCIATIONS                                                              DATA-----CAA0176.DEMO.KSDS.DATA                                         INDEX----CAA0176.DEMO.KSDS.INDEX                                 IDCAMS  SYSTEM SERVICES                                           TIME:          THE NUMBER OF ENTRIES PROCESSED WAS:                                              AIX -------------------0                                                ALIAS -----------------0                                                CLUSTER ---------------1                                                DATA ------------------0                                                GDG -------------------0                                                INDEX -----------------0                                                NONVSAM ---------------0                                                PAGESPACE -------------0                                                PATH ------------------0                                                SPACE -----------------0                                                USERCATALOG -----------0                                                TAPELIBRARY -----------0                                                TAPEVOLUME ------------0                                                TOTAL -----------------1                                      THE NUMBER OF PROTECTED ENTRIES SUPPRESSED WAS 0               IDC0001I FUNCTION COMPLETED, HIGHEST CONDITION CODE WAS 0               IDC0002I IDCAMS PROCESSING COMPLETE. MAXIMUM CONDITION CODE WAS 0       As you might notice, the above Catalog Entry gives all information like AIX, ALIAS, CLUSTER, DATA Cluster’s name, INDEX Cluster’s Name. Moreover, it also gives all the SMS-related(where on the disk this dataset resides) information.

VSAM Tutorial – How to create a ESDS and RRDS Cluster

Q. How do you create an ESDS Object using JCL/Batch JOB?

An ESDS Cluster is a VSAM Dataset, which is similar to a sequential file. An ESDS Dataset has no Index Structure. Now, you might wonder, how to access the records of an ESDS Cluster. Well, to access a record in an ESDS Cluster, you need to know the exact address/physical location, where record lies on the Disk Volume. It’s like saying, if you had a 1000 page book, and you wanted to access the line having keyword laugh, you must know beforehand on which page no(address) laugh is. Thus, Records in ESDS Cluster are accessed by their Physical Address. This address is called Relative Byte Address(RBA). ESDS works just like a stack/pile of books. If you add records, they always get added to the top of the pile/stack, i.e. the records get appended. Since the pile is too heavy, it is not possible to insert records in between. Here, I’ve shown how to create an empty ESDS Cluster using IDCAMS, DEFINE CLUSTER Control statement. ----+----1----+----2----+----3----+----4----+----5----+----6----+----7-- //MYJOB004 JOB A123,QUASAR,NOTIFY=&SYSUID                               //STEP01 EXEC PGM=IDCAMS                                                //SYSPRINT DD DSN=CAA0176.DEMO.JOBLOG,DISP=(OLD,KEEP,KEEP)              //SYSIN  DD   *                                                           DEFINE CLUSTER(                                                      -                  NAME(CAA0176.DEMO.ESDS)                               -                  TRACKS(1 1)                                           -                  RECORDSIZE(80 80)                                     -                  NONINDEXED                                            -                  )                                                     -   DATA(                                                                -                  NAME(CAA0176.DEMO.ESDS.DATA)                          -        )                                                                /*                                                                      //                                                                      In the above example, I’ve created a new ESDS Cluster with the name CAA0176.DEMO.ESDS. This ESDS Cluster will be allocated space(extents) in terms of 1 Primary Track and 1 Secondary Track. All records in this ESDS Cluster will be of length 80 bytes. Moreover, since an ESDS Cluster does not have any key, we don’t write KEYS keyword. Also, ESDS Cluster is specified using the option NONINDEXED. Since, it has on DATA Component, only the DATA Sub-parameter has been coded. The ESDS Data component will have the name – CAA0176.DEMO.ESDS.DATA.

Q. How do you create an RRDS Object using a Batch JOB/JCL?

In a Relative Record Dataset, you must recall that each record is identified by a Relative Record Number(RRN), which is nothing but the sequence number relative to the first record. Note : As opposed to KSDS, ESDS and RRDS support only fixed length records. In RRDS, space is divided into fixed length slots. A slot can be either completely vacant(does not contain a record) or completely full(contains a record). Thus, you can add new records to empty slots, and delete existing records from slots which are filled. Let’s see a simple JCL to create an RRDS Cluster : ----+----1----+----2----+----3----+----4----+----5----+----6----+----7-- //MYJOB004 JOB A123,QUASAR,NOTIFY=&SYSUID                               //STEP01 EXEC PGM=IDCAMS                                                //SYSPRINT DD DSN=CAA0176.DEMO.JOBLOG,DISP=(OLD,KEEP,KEEP)              //SYSIN  DD   *                                                           DEFINE CLUSTER(                                                      -                  NAME(CAA0176.DEMO.RRDS)                               -                  TRACKS(1 1)                                           -                  RECORDSIZE(80 80)                                     -                  NUMBERED                                              -                  REUSE                                                 -                  )                                                     -   DATA(                                                                -                  NAME(CAA0176.DEMO.RRDS.DATA)                          -        )                                                                /*                                                                      //                                                                      RRDS is created on the same lines, like how we created an ESDS. The only difference being that since RRDS records are accessed by RRN(Sequence Number), we must code NUMBERED inside the DEFINE CLUSTER AMS Command.

Technorati Tags: KSDS Cluster,ESDS,RRDS,Entry Sequenced Datset,Key-sequenced Dataset,Relative Record Dataset,Slots,Fixed Length,Relative Record Number,RRN,Relative Byte Address,RBA,DEFINE CLUSTER,AMS,DATA,INDEX,KEYS,RECORDSIZE,TRACKS,NAME,NUMBERED,INDEXED Sequential,NONINDEXED,REUSE,CISZ,Mainframes,IBM Mainframes 360,Mainframes 360,Quasar Chunawalla,VSAM,VSAM Tutorial,How to create ESDS and RRDS,ESDS Basics,RRDS Basics

Creating a New KSDS File

Q. What is IDCAMS Software? How do you run it?

The free IBM-Software Program IDCAMS is used to create, alter, load and delete VSAM Files. I am going to elaborate, on how you write a job to run the IDCAMS Software to create a new KSDS-File. The IDCAMS Software expects you two supply two important files – //SYSPRINT File and //SYSIN File.   The skeleton of the job, that you must write to run IDCAMS Software is shown in this picture. To tell IDCAMS, what to do, you type Control Instructions or Commands, which IDCAMS Software understands in the //SYSIN DD File. Here are some of the commands, that you can issue to IDCAMS - 1. DEFINE Command to create a new Object. 2. ALTER Command to alter an existing Object. 3. DELETE Command to delete objects. 4. PRINT Command to Print the contents of an Object. 5. REPRO Command to take Backups. You must understand, how to write the Control-Card which contains the Special-Instructions for IDCAMS Software.

Q. How do you write the Control-Card to create a new KSDS File?

You type the DEFINE CLUSTER Command to create a new KSDS File. The model or format of this Command is shown in the picture below. You type IDCAMS Commands like DEFINE, ALTER, DELETE etc. beginning from Column 03. Also, if the Command spans across several lines, you must put a continuation character '-' in Column 72. NAME – The NAME Parameter specifies the name of the VSAM File to be created newly. TRACKS – The space allocation of the VSAM File is in terms of Tracks. This says, my new VSAM File will occupy 10 Tracks of Primary Space, and shall grow by 10 Tracks of secondary, when full. You may specify CYLINDERS or RECORDS as well. VOLUMES – The VOLUMES Parameter specifies one or more DASD Volumes on which the VSAM File may be created. CONTROLINTERVALSIZE – The CISZ Parameter tells the size of the Control-Interval in the VSAM File in Bytes. Generally, you would skip off this, and let AMS decide the best CI Size. It defaults to 4096 Bytes. ERASE or NOERASE – The ERASE Parameter specifies, that when the Cluster is Deleted, the space must be over-written with Binary Zeroes, prior to marking the space free for Re-use. FREESPACE(ci ca) – FREESPACE Parameter specifies the percentage of space to be left unallocated for future expansion. If no FREESPACE is specified, the Control-Intervals will be filled upto the brim. IMBED and REPLICATE – These options are now obsolete. IMBED specifies that the Sequence-Set Records are to be embedded with the Data in Data-Cluster. Without Embedding, the Sequence-Set Records are kept with the Index-Set Records in the Index Cluster. KEYS(length offset) – This is used to tell the Key Column in the VSAM File. 3 is the length of the Key, and it is at a Displacement of 0 Bytes from the Column 1. RECORDSIZE(avg max) – This tells average and Maximum length of the Logical-Reocrds in the VSAM File. For Fixed-Length records, avg=max. INDEXED, NONINDEXED and NUMBERED – This tells the Type of Cluster – KSDS(Data is accessed using Key Value), ESDS(Data is accessed using RBA), and RRDS(Data is accessed using Relative Record Number) respectively.

Introduction to VSAM Files

Q. What is VSAM? Never heard of it…!

VSAM stands for Virtual Storage Access Method. In Windows, user's data is stored in files. In a text file, on Windows or Linux, the data consists of lines/records one after the other. Such files are called Sequential Datasets (Files). VSAM is a new, improved way of storing Data. VSAM overcomes some of the limitations of conventional file systems like Sequential Files.

Q. Hold on for a sec... What does a Sequential File look like?

In the early days of computers, all the data was stored in Sequential Files(Physical Sequential Dataset). Data was stored in the form of records, one after the other. Suppose, we wanted to store the information about all the employees in our organization. Below, you’d find a find picture of a how a Sequential File/PS Dataset looks like :       As you can see, each record represents the data of a single, individual employee. This way, there would be thousands of records that make EMPDAT Sequential File.

Q. So, its pretty cool the way a Sequential File stores data. But how to get it back? How to search for a particular employee?

Well, that’s the tough part. Coz sequential datasets work more or less similar to a Cassette Tape. Yup.. an audio cassette tape. The songs recorded on the cassette tape are analogous to records in a Sequential File. If you want to play a particular song, you have to start from the beginning of the tape, travel through the entire tape, till you reach the desired song. You can’t directly jump to a song and play it. You have to read through the tape, and forward scan through it, till you reach the desired place. On the same lines, when you want to search for a particular record say Employee no. 04, you have to travel through the entire the list of records, one by one, till you reach the desired record. The longer is the Sequential File, the longer is would take to access the record. You just don’t know, where the record lies hidden in such huge list or sequential file. The records are scattered and distributed hap-hazardly in the file. So, searching or getting data records, i.e. retrieval of data in a sequential file takes a very long time.

Q. I get it.. as far as Searching  goes – Sequential Files are not very efficient. What’s VSAM got to offer?

You can use a more structured and organised way of storing this data called VSAM. Though the abbreviation is a little geeky, VSAM files are superior in comparison to ordinary sequential files. Searching and retrieving data from a VSAM File is very fast. Apart from this, there are many other advantages that VSAM has to offer : - Free space in a VSAM File is not wasted, it is reclaimed automatically. - VSAM Files are device and O/S independent, this means if you stored data in VSAM on MVS O/S on Mainframes, you can port the file, and read it from Windows O/S on an Intel Machine, without impacting the data contents.

Q. What are the types of VSAM files?

VSAM files are of 3 types - 1) Entry Sequenced file(ESDS) 2) Key Sequenced files(KSDS) 3) Relative Record file(RRDS) A VSAM file is also called a Cluster. Hence, the names ESDS Cluster, KSDS Cluster and RRDS cluster, are used interchangeably with ESDS File, KSDS File, RRDS File.

Q. What’s a Key Sequenced File(KSDS)? Can you explain in brief?

- Concept of Key : In a KSDS file, every record is identified by a unique identification key. Every single, individual employee will have a distinct and unique key value. This key could be his Employee Identification No, since it is unique for each employee. No two employees can have the same key value. - How data is stored in a KSDS File : When you first create a KSDS file, it is initially empty. You must fill data into the KSDS file. Thus, you need to populate(Load) the KSDS file with real data. Generally we do a sequential load, which means the data must be supplied in increasing(ascending) order of the key. This is because, a KSDS file stores all the data records in increasing(ascending) order of the key. - KSDS File Structure : A KSDS file contains two parts : 1) Data Part – That stores the file records(actual data) 2) Index Part – Keeps track of the location of the records in the data part. Given below is a rough sketch which will give you a big picture of what a KSDS File looks like. Of course, the details are explained at length further ahead.   - For Dummies - Concept of Memory Address :  A KSDS file has 2 parts – Index Component and Data Component. The Data Component contains the Data records. Every record is stored in 1 Storage or memory Location. Every memory location houses 1 record. Just like, the houses on a street in which people live, in Mainframe memory, in each house/cell/storage location lives 1 record. Houses on a street have a residential address by which they can be easily reached. If you know the house address, you can access the house. The same way, our houses/storage locations in the memory have unique addresses, by which they can be accessed. If you knew the location/address of a memory location you can easily access the record stored there(in much less time). - For Dummies – Comparing a Book’s Index with a KSDS Index ; How search performance improves with the help of Index Component :  Imagine, if you didn’t have an index in a book, and you wanted to find a keyword. You would have to read through the entire length of the book, page by page, till you come across the word you’ve been looking for. The Index simplifies this activity. Basically, a book index has two columns, one the keyword, and other the page no./location in the text where this keyword is located. Every page has a page-number. Let’ say you want to search the term Mainframe Computers. You look up this keyword in the Index. This is easy, because the index is sorted in Alphabetical order of the key-value. You jump to the section –'M'. Look up this term, in the index points to Page No. 373. You jump straight to page 373 and start reading about Mainframe Computers. Just as every page has a page no., every record in a KSDS Data file has an address. The KSDS Index file has an entry for every key-value(key-field). For example, employees 1, 2 and 3 each would have an entry in the index. The index also stores the memory address(offset) of this Employee record in the KSDS Data file. Like a book index is sorted alphabetically on the keyword, the KSDS index file is sorted in increasing order of the key-field. Let's assume, Employee ID as key-field. So, how does it work? Let's say you wanted to find the name of Employee No. 0004. Simple, you look up the the row of Employee, with Key-value=0004 in the KSDS Index file. This is easy, because, the index is already sorted on the Key field => Employee ID. Now, you find the address of the Storage Location(House) in the KSDS Data file, where Employee ID 04 lives. This is location no. 600. Since you know the address, you can now directly jump and fly to address 600, and access the name of the Employee. This is far quicker than you thought. The gist of this concept is, KSDS Index file stores key-values, and pointers(memory address)set to the corresponding records in the Data file. This way, Searching is faster and easier. The process of building an Index on a key-field for Data Records is called Indexing(or simply building an INDEX). Let me caution you, that the diagram above is a very crude or preliminary picture of the KSDS Index file. Don’t go by it. In reality, the KSDS Index file has an inverted-tree structure. In Computer Science, we call such a tree, a B+ Tree. If you are curious to know, what’s a B+ Tree, and how the KSDS Index file really looks, read on. If you feel, you’ve absorbed a lot, you can call it a day!

Q. How records are organized in KSDS Data file?

A KSDS file stores logical records of a file in fixed length blocks called Control Intervals(CI). In a KSDS Data file, a Control Interval holds several logical records. The logical records within each control-interval are always kept sorted by key-field.  A KSDS File could have thousands of Control Intervals. In a Control Interval, records can be of any size or length. We do not distinguish in particular between fixed-length and variable-length records. However, as a rule, all Control Intervals in KSDS file are exactly equal in size(length). When a new KSDS file is created, you must specify the size of the Control Intervals in the file. By default, the Control Intervals(CI) in a KSDS File assume a size= 4k(4096) bytes. However, the size of Control Intervals in KSDS Files can lie in the range of 512 bytes <= Control Intervals Size <= 32k When you create a new KSDS File, the control intervals in it are empty. As you load data into the KSDS file, the Control Intervals are populated with information. What follows from hereon, shall give you a picture of how Control Intervals look like in Memory. Control Interval (Very idealistic – Simplified)  Assume that, Control Intervals are 4096 bytes long. A logical record(Employee record) spans 1024 bytes. Then, No. of records per CI = 4096/1024 = 4 records/CI Thus, in this example, the Control Interval is completely full(no room for new records).   Control Interval often contains some empty/free space(Close to real model) : Assume that, Control Intervals are 4096 bytes long. The first logical record = 1000 bytes, the second logical record = 1500 bytes, the third logical record= 1,300 bytes. Logical Record 1 + Logical Record 2 + Logical Record 3 = 1000 + 500 + 1300 = 2800 bytes. Thus, the remaining space = 4096 – 2800 = 1,296 bytes is left free. This free-space can be used to accommodate a new logical record. Thus, Control Intervals may also have free-space. New logical records can be added to a Control Interval, by using the free-space in the Control Interval(CI). VSAM Control Interval Control Interval showing addition of record with key 30 Let's look at the recipe followed by VSAM, to add a new logical record to a KSDS File. 1. VSAM goes through a full-index search to locate the Control Interval(CI) in the KSDS Data file, in which the new record must be placed. (This search is exactly the same as that used to randomly retrieve a record). 2. After the index search locates the Control Interval(CI), that Control Interval(CI) is loaded into memory. VSAM then searches through the logical records in the Control Interval to determine, where the new record should go.(Recall, that a KSDS file stores all data records in increasing order of the key). 3. The new record is then inserted into the Control Interval(CI), in key sequence, re-arranging the other records, as necessary. 4. The updated Control Interval(CI) is now written back to its original location on the Disk. Control Interval also contains extra Information(Real Model) :  VSAM treats all the logical records, as if they were variable-length(even if, they are fixed-length). VSAM keeps track of the length of Logical records in a Control Interval, by using special Record-definition Field(RDF), at the end of each Control Interval. This special field that holds the length information for each logical record is 3 bytes long. Moreover, VSAM also keeps track of the amount of the free-space and its location, within a Control Interval. This meta-information is stored in a special Control Interval-definition Field(CIDF), at the end of each Control Interval. This special field that holds [amount,location] of the free-space for a Control Interval is 4 bytes long.   Control Area(CA) : A Control Area(CA) is a group of related Control Intervals.  KSDS Files are organised as Control Areas(CA) which in turn contain hundred’s of fixed-length Control Intervals(CI) filled with logical records, free-space and Control information.

Q. Can you show me a picture or visual of how KSDS Data file looks like?

A KSDS Data file is – a collection of control intervals and control areas. A CI normally holds several logical records. At the end of each CI, control information is stored. Between the logical records, and the control information, there’s free-space, where new records can be added.

Q. What does a KSDS Index file look like?

The KSDS Index file is organised in two parts – Index Set and Sequence Set. Lowest level of index entries is called the Sequence set. There is one sequence set record for each control area, in the KSDS Data file. The sequence set record for each control area, contains an entry for each control interval in that control area. The entry for a control interval stores (i) the highest key of the logical records in that CI (ii) the physical disk address(pointer to) of that CI. The CI entries within a sequence set record, are kept in increasing(ascending) order of the key. This facilitates control-intervals within a control-area to be retrieved in key-sequence, during sequential processing, irrespective of whether the actual CI’s are in key sequence within the CA. As I just said, the sequence set record for a CA, contains an entry for each control interval in that control area. In order to facilitate random processing, each CI entry has a (i) highest key of the CI (ii) vertical pointer to the Control Interval. The vertical pointer can be followed to retrieve any or all the records within that CI. In addition to vertical pointers to each CI, each sequence set record also contains a horizontal pointer, to the next sequence set record in key sequence. The horizontal pointers are followed during sequential processing. After all the records in a control area have been read, the horizontal pointer is followed to move to the next sequence set record which points to the successive control area. The Index set is organised as a tree or hierarchical structure. There is one and only one index set record at the top of the tree(that is at the root). Index searching during random processing begins at this root index set record. The root and all the other index set records consist of several entries. Each entry consists of the highest key of the next lower-level index set record, and a pointer to said index set record. The individual entries within an index set record are kept in key sequence. During Random processing, the logical record that you want to access, must be first looked up in the Index. This process proceeds as follows : 1. The root index set record is input, and the first entry greater than or equal to the key of the desired record is located. Associated with this key value, is downward pointer to next lower-level index set record. 2. The next lower level index set record is input, and the first entry >= key of desired record is located. Associated with this key value, is a downward pointer to the next lower-level index set record. 3. This process continues, until you reach a sequence set record. At this point, the first CI entry >= key of the desired record is located. Associated with this key value, is a downward pointer to the control-interval. 4. The indicated control-interval(CI) is input, and is searched for desired logical record. If the record is not in this CI, it is not in the file(and the COBOL program is notified of record-not-found condition).