Can we create a VSAM file with IEFBR14 ?

The Answer is 'Yes', we can, but with some limitations. We all know or used to IDCAMS  when it comes to VSAM creation. But even IEFBR14 can create VSAM with some advantages and disadvantages!

Let have the JCL for our first run:
//STEP1    EXEC PGM=IEFBR14                    
//SYSPRINT DD SYSOUT=*                         
//DD1      DD DISP=(,CATLG),DSN=IXXXX.TEST.VSAM
//         LRECL=80,KEYLEN=5,KEYOFF=0,RECORG=KS,
//         SPACE=(CYL,(5,5)
/*
And here you go with the Output for KSDS VSAM:

Lets look for the parameters used in the JCL
LRECL=80  Defines the maximum record length of the VSAM in bytes
RECORG  Is the important parameter to note. Its value determines the type of VSAM dataset the job will create.
Here in the example we have
RECORG=KS which made it as a KSDS
RECORG=ES  will create a ESDS
RECORG=ES  will create Linear data set
RECORG=RR will create RRDS
'KEYLEN=' will define the length of the KSDS key
'KEYOFF='  will define the key offset length. Here the value of '0' indicates the first byte.
'SPACE='  will allocate the space .Here it is in cylinders, the primary and secondary being 5 each.
However we can also use SPACE=(TRK,(50,20))
We can use the AVGREC parameter along with the SPACE parameter like below.The value of AVGREC has some significance.
AVGREC=U  implies primary and secondary space numbers are  number of records
AVGREC=K  implies primary and secondary space numbers are  number of records in thousands(multiplied by1024)
AVGREC=M  implies primary and secondary space numbers are  number of records in Million(multiplied by 1024*1024)
Re writing the Above JCL once more
//STEP1    EXEC PGM=IEFBR14                    
//SYSPRINT DD SYSOUT=*                         
//DD1      DD DISP=(,CATLG),DSN=I15122.TEST.VSAM
//         LRECL=80,KEYLEN=5,KEYOFF=0,RECORG=KS,
//         SPACE=(60,(300,200)),AVGREC=U
/*
So here, the primary space large enough for  300*60=18000 bytes and secondary for 1200 bytes.
Disadvantage of using IEFBR14:
1. We cant specify CI Size
2. No Shareoptions
3.Seperate data and index component parameters

 Hope this helps a bit! :)

DB2 Plan +Package+ and Collection ID ? Frequently Asked questions in interview

Having Covered the Basic steps in a cobol-db2 compilation process, lets have a bit of more clarification & brain storming to check our understanding :)

Q 1: Syntax Checking of the SQL statements are done in Precompilation time or Bind time?

Both. Let me explain it in details a bit. We all are aware of DCLGENS and we use INCLUDE statements in our cobol module to include the DCLGENS. Precompilation process only uses DCLGEN as the reference for table description (provided we use DCLGEN).It does not go and check the DB2 catalog for the table structure. If no DCLGEN is used, no synatx checking is done in precompilation time.
But,  During BIND time, the check is more rigorous and thorough because the DB2 catalog is accessed to check the table structure along with other information like access permission and others

Q2: Can 1 package have more than 1 DBRM ?
From 1 program, we get one DBRM and then we bind the DBRM into a package; in the sense, 1 package is the DB2-optimized version of the that single  DBRM. Each Package is nothing but a single bound DBRM. There is direct one to one relationship with application program and package.
So, 1 DBRM=1 PACKAGE

Q3: Are packages executable?
No. In DB2, only plan is the executable module.

Will keep on adding as and when i come across!!..  suggestions most welcome!

VSAM I/O RETURN CODE IS 108 - RPLFDBWD

Few Errors encountered while playing with VSAMs:

 ACTION ERROR ON TEST.VSAM
** VSAM I/O RETURN CODE IS 108 - RPLFDBWD = X'8408006C'
NUMBER OF RECORDS PROCESSED WAS 0
FUNCTION TERMINATED. CONDITION CODE IS 12

Explanation:You have defined your VSAM with Record length ,say (15 15)
You are trying to do REPRO from a dataset with Different LRECL

Solution: 
Create a flat file with the same LRECL as the VSAM file and use it in REPRO command

Reading/Interpret the LISTCAT command output in VSAM

When we create a VSAM,all its details are recorded in the catalog table in MVS. LISTCAT is a versatile command meaning 'List Catalog'. LISTCAT  command can be fired from command  shell (option 6 from ISPF menu ) or it can run in batch as a SYSIN parameter to the IDCAMS.

To run it from the command shell , go to option 6 and give 
LISTCAT ENT ('TST.MYVSAM.NAME') all
We will get the information's being displayed on the screen page by page as we hit enter.

To use it in batch we need the IDCAMS utility . Below is the sample JCL for LISTCAT command 
//STEP0010 EXEC PGM=IDCAMS,REGION=0M,COND=(0,NE)
//SYSPRINT DD SYSOUT=*                          
//SYSIN    DD *                                    
     LISTCAT ENT ('TST.MYVSAM.NAME') ALL 

Interpreting the LISTCAT command

Interpreting the listcat command

As we can see here the name of the cluster is TST.MYVSAM.DEPT.As we go through the spool we will see the information of  STORAGECLASS,MANAGEMENTCLASS, but those things are not of that much importance to us now. Go on and have a look down for


It implies that this VSAM is not password protected. Also the ASSOCIATIONS parameter will list down all the datasets with the same name pattern and we can see that it shows the data component and INDEX component. So our VSAM is probably a KSDS.
Look in the ATTRIBUTES Section.
ATTRIBUTES section will give a more clear picture on the VSAM definition, ie how it is defined .
So, from these statistics we can somewhat guess the definition.Like, for the above VSAM, the key is of length 16 and offset of the key position being 20.
((RKP parameter defines the relative position  of the key field in the base cluster.) )
The AVGLRECL and MAXLRECL will give us the  idea about the record size parameter used in definition which in this case is  191 and 1500 respectively.
SPEED and NOERASE are also keywords in VSAM world.
One more 'NONSPANNED' , specifies that the data in the component can not span across CIs
So from this screenshot lets note down what all information we collected
NAME(TST.MYVSAM.NAME)
SHR(2 3)
RECORDSIZE(191 01500)
KEYS(00016 00020)
SPEED
NOERASE
If we look a little right on the mainframe screen we will get the information about the CI size .

 So, the CISZ parameter will be of length 18432.  CISZ(18432).
Also , watch the parameter NOREUSE and UNORDERED. So three more features added to our cart for the vsam statistics; CISZ(18432) ,NOREUSE and UNORDERED
The CI/CA in VSAM indicates the number of CI in each CA .Thus the above information tell us that there are 45 CIs in each CA.
Go on for few more details in the STATISTICS section
This Section really gives us all the details regarding the statistics of the vsam in that moment. Will see more in details later. As of now check out the FREESPACE parameter % CI and % CA. Those are the parameters with which the vsam has been defined.
Ohh..!! one more i got in the Allocation section.
SPACE-TYPE ----CYLINDER
SPACE-PRI    -------60
SPACE-SEC   -------10
This is more or less self explanatory, ie, the allocation quantity is CYLLNDER and primary and secondary allocations are 60 and 10 respectively.
So few more added in our cart.
FREESPACE(025  008)
CYL(00000060  00000010) 
Some other useful information from STATISTICS section
1. (( FREESPC --16386048 : This indicates space in bytes in completely free CIs. Do not mix them up unused space in in CIs) )
2. REC-TOTAL :Total number of logical records in the data component in that instance listcat was run.
3. REC-INSERTED: Total number of logical records inserted in data component ,since the vsam was last created or re-organized.
4. REC-RETRIEVED : total logical records read from data part since its creation or reorganized.It takes into account  reads for update during REWRITE processing.
5. REC-UPDATED : Total number of logical records UPDATED or REWRITTEN back into the data component since its last creation or last it was reorganized. Does not include DELETED records.However if  a records is updated, and may be got deleted later ,is counted here.
6. SPLITS-CA /SPLITS-CI : Total number of CI and CA splits that happened since it was last created or reorganized.
Having covered all these till now, if we look into the VSAM parameters which we collected till now  ( highlighted in blue above), we can some what figure out the structure of the VSAM which will be like:
DEFINE CLUSTER (          -
   NAME(TST.MYVSAM.NAME) -
   SHR(2 3)               -
   RECORDSIZE(191 01500)  -
   NOREUSE                -
   INDEXED                -
   NOERASE                -
   UNORDERED              -
   NONSPANNED             -
   SPEED                  -
      DATA               -
       (                           -
       NAME(TST.MYVSAM.NAME.DATA) -
       VOLUMES(XXXX)               -
       CISZ(18432)                 -
       KEYS(00016 00020)           -
       FREESPACE(025  008)         -
       CYL(00000060  00000010)     -
        )                          -
       INDEX                       -
       (                            -
       NAME(TST.MYVSAM.NAME.INDEX) -
       VOLUMES(XXXXX)              -
       )