Resolving error 37 of the Vsam file

Resolving error 37 of the Vsam alternate index

Whenever we define Vsam Alternate Index File we might face error 37 when we use to process the file via JCL first time:

The solution mentioned to resolve this is to initialize Vsam file once before directly opening in the program. But still many times you must have faced same error again and again whenever you try to read alternate index vsam file via a cobol program and through JCL.

Whenever we define VSAM alternate index file we have following components:

Cluster:          OLDMF.DATA.KSDALT

Data:              OLDMF.DATA.KSDALT.DAT

INDEX:            OLDMF.DATA.KSDALT.INX
AIX :               OLDMF.DATA.KSDALT1
PATH:             OLDMS.DATA.KSDALT.PATH

This filename and alternate index is used in the JCL to read the Cobol program and in JCL it is placed with its logical name as:


//OLDMF     DD   DSN= OLDMF.DATA.KSDALT,DISP=OLD

//OLDMFD    DD   DSN=OLDMF.DATA.KSDALT1,DISP=OLD



The problem is in the way we write our logical name. Even if you try to initialize file and you may still face the error. It will be resolved by the way we write logical name for the alternate index in the JCLs.
Correct declaration:

//OLDMF     DD   DSN= OLDMF.DATA.KSDALT,DISP=OLD


//OLDMF1    DD   DSN=OLDMF.DATA.KSDALT1,DISP=OLD




Always the logical name will be appended with the next sequence number starting from 1.

SYNCSORT or DFSORT - PART 3

Problem Statement

//*********************************************************************

//* I have two files. 
//* FILEA contains only timestamp X(26).
//* FILEB contains Office X(3), Account X(6), timestamp X(26) and Identity number X(3)
//* I want all records of FILEB for timestamp values not present in FILEA

//*********************************************************************

SORT JCL Used:

Step 1: Sorted FILEB with timestamp as first field.

//SORT01   EXEC PGM=SORT

//SORTIN   DD  DSN= REC.FILE, ---FILE B(WITH LAYOUT GIVEN)

//             DISP=SHR

//SORTOUT  DD  DSN=OUTOUT.FILE,

//             DISP=(OLD,CATLG,DELETE),

//             SPACE=(CYL,(10,50),RLSE),VOL=(,,,40),

//             DCB=(RECFM=FB,BLKSIZE=0,LRECL=80)

//SYSOUT   DD  SYSOUT=*

//SYSPRINT DD  SYSOUT=*

//SORTLIST DD  SYSOUT=*

//SYSIN    DD  *

        INREC FIELDS=(10,26,1,9,36,3)

        SORT FIELDS=(1,26,CH,A)

/*

Step 2 Remove/Discard duplicates on from the sorted file and the FILE A which is sorted on timestamp.

//SORT02   EXEC PGM=ICETOOL

//INPUT    DD  DSN=OUTOUT.FILE,DISP=OLD      

//         DD  DSN=TMSTMP.FILE,DISP=OLD --FILE A(TIMESTAMPS)

//OUTPUT   DD  DSN=FILE.DISCARD,

//             DISP=(OLD,CATLG,DELETE),

//             SPACE=(TRK,(1,5),RLSE),

//             DCB=(RECFM=FB,BLKSIZE=0,LRECL=80)

//FINAL1   DD  DSN=FINAL.FILE,

//             DISP=(OLD,CATLG,DELETE),

//             SPACE=(TRK,(1,5),RLSE),

//             DCB=(RECFM=FB,BLKSIZE=0,LRECL=80)

//TOOLMSG  DD  SYSOUT=*

//DFSMSG   DD  SYSOUT=*

//TOOLIN DD *

  SELECT FROM(INPUT) TO(OUTPUT) ON(1,26,CH) ALLDUPS DISCARD(FINAL1)

/*

 ICETOOL is used here to sort on timestamp and remove dupicate timestamp records.

Step 3 Sorted as per layout in FILE B.

//SORT03   EXEC PGM=SORT

//SORTIN   DD  DSN=FINAL.FILE,

//             DISP=SHR

//SORTOUT  DD  DSN=FINAL1.FILE,

//             DISP=(OLD,CATLG,DELETE),

//             SPACE=(CYL,(10,50),RLSE),VOL=(,,,40),

//             DCB=(RECFM=FB,BLKSIZE=0,LRECL=80)

//SYSOUT   DD  SYSOUT=*

//SYSPRINT DD  SYSOUT=*

//SORTLIST DD  SYSOUT=*

//SYSIN    DD  *

        INREC FIELDS=(27,9,1,26,36,3)

        SORT FIELDS=(1,26,CH,A)

/*
Sorted again in original file form.

Please refer PART1 and PART2 here.

JCL System Abend: SOC 1 and SOC 7

SYSTEM ABENDS

I am sure everyone is aware of system ABENDS. I have tried to list ABENDS for all the possible reasons. Hope you all find it useful.

SOC 1 

This abend can be faced in following situations. Rule out following possibilities:

• Any file doesn't have open statement and directly trying to read it
• The subscript it may be out of boundary for a defined COBOL table
• Review JCL for missing or misspelled DD name
• Problem in parm passed to subroutine related to addressing mode or Data mode
• Recording mode may be wrong 
• Sub program called is not found problem in dynamic link or static link
• Check load module there can be a possibility if subroutine program id and program name not same. This means bad load module.
• An uncontrolled loop moved data on top of instructions. 
• Problem can be same name for array and subroutine  

    SOC 7 

This is a data exception and can only occur when decimal (packed) instructions are used. 

• When alphanumeric data moved to packed decimal data (COMP 3).
• When Programmer use a variable, which is not defined in working storage.
• Period missing after imperative statements within AT END clause.
• Binary field in an arithmetic operation is not large enough to accept result.
• Failure to initialize a counter.
• Invalid incoming data (e.g., blanks, decimal points, or commas in a numeric field).
• Exceeding a table via a subscript (index) error, causing a reference to invalid data.
• Moving zeroes or low-values to a group field defined as numeric.
• An omitted or erroneous usage clause.
• Passing parameters between programs in the wrong order.

Submit JCL through REXX

The following REXX tool explains how JCL can be submitted through REXX.

Step 1: To get inputs from the user

/*REXX*/

/* TRACE I */

USERID = SYSVAR('SYSUID')

 "EXEC 'SYS2.CMDPROC(CLRSCRN)' CLIST"

ARG  ID FILEDT SQLINR

SAY  ID

SAY  FILEDT

SAY  SQLINR

IF ID = ' ' THEN

    DO

      SAY 'ENTER ID REQUIRED FOR INQUIRY'

      PULL IDID

    END

IF FILEDT = ' ' THEN

    DO

      SAY 'ENTER FILE DT REQUIRED FOR INQUIRY IN MM/DD/YYYY FORMAT '

      PULL FILEDT

    END

IF SQLINR = ' ' THEN

    DO

      SAY 'ENTER THE REGION FROM WHERE ID IS REQUIRED'

      PULL SQLINR

    END

IF IDID = 'END' | FILEDT = 'END' | SQLINR = 'END' THEN

    DO

      SAY 'COMMAND ENDED. THANK YOU'

      EXIT

    END

   INTLL  = LEFT(USERID(),3)

/* */

Step: 2: To allocate file where JCL will be returned with the user id automatically appended 

  "ALLOC FI(JCLFILE) DA('"USERID()".PABCB.N.TEMP.ABCD') OLD CATALOG  REUSE",

      "SPACE(1,1) TRACKS LRECL(80) BLKSIZE(3120) RECFM(F,B)"

Step 3: To create JCL

   IF ID <> "END" THEN DO

      JCL.1   = "//#ABC"INTLL"C JOB (00011100000"USERID()"),'X',"

      JCL.2   = "//             CLASS=X,"

      JCL.3   = "//             MSGCLASS=0,"

      JCL.4   = "//             REGION=4M,"

      JCL.5   = "//             NOTIFY="USERID()

      JCL.6   = "/*JOBPARM S="ABCV

      JCL.7   = "//*"

      JCL.8   = "//STEP01S  EXEC PGM=IKJEFT01"

      JCL.9   = "//STEPLIB   DD  DSN=DB2E.PSYSB.P.ABCD.SDSNLOAD,DISP=SHR"

      JCL.10  = "//SYSTSPRT  DD  SYSOUT=*"

      JCL.11  = "//DETAILS   DD  SYSOUT=*"

      JCL.12  = "//FREELST   DD  SYSOUT=*"

      JCL.13  = "//SYSTSIN   DD  *"

 JCL.14 = " EXEC 'ISPS.PABCB.P.ABC.EXEC(PVDREQR)' '"ID" "FILEDT" "SQLINR"'"

    END

      "EXECIO * DISKW JCLFILE (STEM JCL. FINIS)"

      "FREE FI(JCLFILE)"

Step 4: To submit JCL

      "SUBMIT '"USERID()".PABCB.N.TEMP.ABCD'"

After this, you can see the JCL in spool by checking the jobname #ABC*

MQPUT Through JCL

JCL to LOAD the Data into MQ(Message Queue) channels from a sequential data set.

With CSQUTIL MQPUT can be done in batch.

//MQLOAD1  JOB (ABCDEFGH),'ABCDEFG',

//         MSGCLASS=T,SCHENV=DB2DBJB,

//         NOTIFY=&SYSUID,TIME=90

//*

//MKCMDS   EXEC PGM=CSQUTIL,PARM='Queue Manger Name'

//STEPLIB   DD DSN=MQM.M***.SCSQAUTH,DISP=SHR

//INFILE   DD DISP=SHR,DSN=TEST.RSS.XCI0500.STRGTEST.INPUT

//SYSPRINT DD SYSOUT=*

//CSQUOUT  DD SYSOUT=*

//SYSIN    DD *

LOAD QUEUE ( PK01) DDNAME(INFILE)

/*

JCL to Alter and Define Queues

Commands can be executed in JCL to alter/define MQ

Here I am altering INITQ for Local Queue PK01.

//PRODSTEP EXEC PGM=CSQUTIL,PARM='QueueManger Name'        <===== QMGR ?????

//SYSPRINT DD SYSOUT=* 

//STEPLIB DD DSN=MQM.SCSQLOAD,DISP=SHR 

//SYSIN DD *

COMMAND DDNAME(CMDGET)

/*

//CMDGET DD *

ALTER QLOCAL(PK01) INITQ(CICS1.INITQ)                                 

JCLs to Alter Lock parameter of DB2 table

   

                         JCLS TO ALTER LOCK PARAMETERS OF ANY TABLE (DB2 V 9)

LOCKPART

JCL to alter tablespace lockpart parameter (only for partitioned) to NO in DB2

//PRODDB2  EXEC PGM=IKJEFT01,DYNAMNBR=30                

//STEPLIB  DD DSN=SYS1.DSN900.SDSNLOAD,DISP=SHR         

//SYSTSPRT DD SYSOUT=X                                  

//SYSTSIN  DD *                                         

    DSN SYSTEM(DB2PROD)                                    

          RUN PROGRAM(DSNTEP) PLAN(DB2PLAN) -         

          LIBRARY('DSN.DB2PROD.RUNLIB.LOAD')            

      END                                               

//SYSPRINT DD SYSOUT=*                                  

//SYSUDUMP DD SYSOUT=*                                  

//SYSIN    DD *                                         

ALTER TABLESPACE PRDX1.TSSMG                            

LOCKPART NO;                                            

COMMIT;                                                 

This will give problem as tablespace is not stopped. it will give following error:

PAGE    1                                                                      

***INPUT STATEMENT:                                                             

 ALTER TABLESPACE PRDX1.TSSMG                                                   

 LOCKPART NO;                                                                  

SQLERROR ON   ALTER     COMMAND, EXECUTE   FUNCTION                            

 RESULT OF SQL STATEMENT:                                                      

 DSNT408I SQLCODE = -626, ERROR:  THE ALTER STATEMENT IS NOT EXECUTABLE BECAUSE  THE PAGE SET IS NOT STOPPED 

 DSNT418I SQLSTATE   = 55015 SQLSTATE RETURN CODE                               

 DSNT415I SQLERRP    = DSNXIATS SQL PROCEDURE DETECTING ERROR                  

 DSNT416I SQLERRD    = 155  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION          

 DSNT416I SQLERRD    = X'0000009B'  X'00000000'  X'00000000'  X'FFFFFFFF'  X'000

          INFORMATION                                                          

PAGE    1                                                                      

***INPUT STATEMENT:                                                            

 COMMIT;                                                                        

 RESULT OF SQL STATEMENT:                                                      

 DSNT400I SQLCODE = 000,  SUCCESSFUL EXECUTION                                 

 DSNT418I SQLSTATE   = 00000 SQLSTATE RETURN CODE                              

 DSNT416I SQLERRD    = 0  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION            

 DSNT416I SQLERRD    = X'00000000'  X'00000000'  X'00000000'  X'FFFFFFFF'  X'000

SYNCSORT or DFSORT: Part 2

SORT Function and Examples

SORT

SORT Fields: The syntax is given below:


SORT FIELDS=(p1,l1,f1,o1,p2,l2,f2,o2,.....) ¦         

FIELDS=(p1,l1,o1,p2,l2,o2...),FORMAT=x 

FIELDS={(} COPY {)} ¦                             
{,CKPT}                                           
{,DYNALLOC{=(d ¦ ,n ¦ d,n ¦ OFF)}}                
{,EQUALS ¦ ,NOEQUALS}                             
{,FILSZ={n ¦ En ¦ Un} }                           
{,SIZE={n ¦ En ¦ Un} }                            
{,SKIPREC=n}                                      

SYNCSORT or DFSORT: Part 1

The sort program is used to sort data into a desired sequence based on  business requirement. Also, it can be used to merge many files into one single file.

Features of the SYNCSORT utility

• Copy Function
• Merge function capable of merging up to 32 data sets into one sequence provided those data sets are sorted. Latest versions allow up to 100 data sets to be merged.
• Sort facility which can sort single or concatenated data sets into one data set.
• Conversion of the packed decimal fields to printable formats.

JCL TO ALTER or DEFINE Queues

Code snippets for defining or altering Message Queues through JCL

Here I am changing INITQ for Local Queue PK01.

//PRODSTEP EXEC PGM=CSQUTIL,PARM='QueueManger Name'              

//SYSPRINT DD SYSOUT=*                                                 

//STEPLIB DD DSN=MQM.SCSQLOAD,DISP=SHR                                

//SYSIN DD *