PROCEDURES in JCL -- Faq's

Q: How are in-stream procedures (procs) built?
A: In-stream procedures are built by coding a set of statements and placing them after the JOB statement and before the EXEC statement. In-stream procedures begin with a PROC statement and end with a PEND statement. Up to 15 in-stream procedures can be included in a single job.
Each in-stream procedures may be invoked several times within the job. In-stream procedures can use symbolic parameters in the same way as catalogued procedures.

Q: What is the difference between an in-stream procedure and a catalogued procedure?
A: An In-stream procedure is basically same as a catalogued procedure. The difference is that to execute an in-stream procedure one places it after the JOB statement and before the EXEC statement and must end it with a PEND statement. A catalogued procedure is catalogued on a procedure library and is called by specifying the procedure name on the EXEC statement. An in-stream procedure is useful to test the procedure before making it a catalogued procedure.

Q: Name some of the JCL statements that are not allowed in the procs.
A: Some of the JCL statements that are not allowed in the procs are:
1. JOB Delimiter (/*) or Null (//) statements
2. JOBLIB or JOBCAT DD statements
3. DD * or DATA statements
4. Any JES2 or JES3 control statements

Q: What parameters are good candidates to make symbolic parameters?
A: Any parameter, sub parameter, or value in a procedure that may vary each time the procedure is called is a good candidate to be coded as a symbolic parameter.

Q: Which type of override parameter requires that one know the parameters that can be overridden?
A: Regular parameters require that one know the parameters that can be overridden, such as step names within the procedure, the DDnames of the statements overridden, and the order of the DD statements.

Q: How is a symbolic parameter coded?
A: A symbolic parameter is preceded by an ampersand (&) and followed by a name (&FIRST). The first character must be alphabetic. Symbolic parameters can be coded only in the operand field of the JCL statements; they cannot appear in the name or operation field of the JCL statements. If more than one value is assigned to the symbolic parameters on a PROC or EXEC statement, only the first one is used. Symbolic parameters may be coded in any order on the PROC or EXEC statement.

Q: How are values assigned to symbolic parameter?
A: Values can be assigned to symbolic parameter on the PROC statement, on the EXEC statement, or on a SET command. Values containing special characters other than blank must be enclosed in apostrophes. The Values assigned to symbolic parameter can be of any length, but it cannot be continued onto another line.

Q: Can symbolic parameter concatenated?
A: Symbolic parameter can be concatenated with other symbolic parameters, regular parameter or with the portions of the regular parameters as follows
Symbolic/symbolic – PARM-&FIRST&LAST
Symbolic/regular – SPACE-&SPACES
Symbolic/portion – SPACE-CTRK, &PRIMARY

Q: What are some of the rules involved in overriding parameters on the EXEC statements in a procedure?
A: To override EXEC parameter one should follow these rules
1. A PGM parameter cannot be overridden.
2. The parameter for each step do not need to be coded in the same order as they appear on the procedure EXEC statement
3. To add or override a parameter on an EXEC statement, code it as follows parameter.procstepname=value.
4. If a parameter which does not exist is coded on the EXEC statement, the parameter will be added
5. All parameters in each step must be coded in order: the first step must be coded first, second step second, third step third, etc.

Q: What are some of the rules involved in overriding DD statements in procs?
A: The following rules apply when overriding a DD statement:
1. DD statement overrides precede the DDname with procstepname
2. The JCL parameter is replaced unless it does not exist on the original statement, in which case it is added. For the DCB each sub parameter can be overridden
3. DD statement overrides should carry DDnames that already exist in the step they are to effect
4. DD statement overrides must be coded preceding any added DD statement for the proc step
5. DD override statement must be listed in the order in which they are shown in the proc
6. DD override statement are only in effect for the duration of the run

Q: How are concatenated DD statements in the proc overridden?
A: Overriding concatenated DD statements requires the following:
1. To override only the first DD statement in a concatenation, code only one overriding DD statement
2. To override all DD statements in a concatenation, code an overriding DD fo each concatenated DD statement
3. The overriding concatenated DD statements must be in the same order as the concatenated DD statement
4. Code a DDname on the first overriding DD statements only. Leave the DDname blank on the following DD statements.
5. To leave a concatenated statements unchanged, code its corresponding, overriding DD statement with a blank operand field

JCL Limits

JOBNAME MAXIMUM LENGTH ACCOUNTING INFORMATION SIZE PROGRAMMER NAME LENGTH JCL PROCEDURE LIMIT NO OF EXEC STATEMENTS IN A JOB SYMBOLIC PARAMETER LENGTH STEPNAME LENGTH DPRTY MAXIMUM VALUE PERFORM PARAMETER MAX VALUE RETURN CODE MAX VALUE TIME PARAMETER MAX VALUE MAX BLOCK SIZE MAX RECORD LENGTH  MAX NO OF BUFFERS PER DATA SET DATA SET NAME LENGTH OUTPUT MESSAGE LIMITS CONCATENATED DATA SETS MAX NO OF INSTREAM PROC IN A JOB GDG MEMBER LIMIT

008 CHARS 142 CHARS 020 CHARS 255 STATEMENTS 255 100 CHARS 008 CHARS 015 999 4095 1439,59 32,760 BYTES 150 255 44 16,777,215 255 15 255

Frequently asked queries in DB2

Top 3 Salaries:

1)   select * from emp e1 where 3>(select count(distinct sal) from emp e2 where e1.sal<e2.sal) order by sal desc;

Top “N” Salaries:

2)   select * from emp e1 where &n>(select count(distinct sal) from emp e2 where e1.sal<e2.sal) order by sal desc;

least “3” Salaries:

 3) select * from emp e1 where &n>(select count(distinct sal) from emp e2 where e1.sal>e2.sal) order by sal desc

least “N” Salaries:

4) select * from emp e1 where &n>(select count(distinct sal) from emp e2 where e1.sal>e2.sal) order by sal desc

Top salary: (OR) 1^st salary

5)   select * from emp e1 where 1-1=(select count (distinct sal) from emp e2 where e1.sal<e2.sal);

To eliminate the duplicates Records from table:

6)select * from emp where rowid in (select min(rowid) from emp group by sal);

To Retrieve Only duplicates Records from table:

7)   select * from emp where rowid not in (select min(rowid) from emp group by sal);

To Change The Table name

8)   Rename OldTableName      to      NewTableName;

To Change the column name:

9)   Alter    table    TableName    Rename   column      OldColumnName to NewColumnName

All Clauses included in this Query

10)  select deptno,job,sum(sal) from emp where deptno in(10,20) group by deptno,job having sum(sal)>1000 order by sum(sal) desc

Dept  whose not having employees.

Select  dname,deptno,loc from dept d where not exists(select * from emp e where e.deptno=d.deptno);

Selecting the employees whose belongs to pirtculer manager name=’BLAKE’:

1)select empno from emp where ename=’BLAKE’;

2)    select * from emp where mgr=7698;

                           

                            OR

3)   select * from emp where mgr=(select empno from emp where ename='BLAKE');

To select the last record of the table;

select * from emp where rowid=(select max(rowid) from emp);

11) we have 200 records from theat records I want 50 th  record name,sal,job, rownum

Select * from(select ename,job,sal,rownum r from emp where rownum<14) where r=5;

12) TO GET THE FIRST AND LAST RECORD.

 Select * from emp where rowid=(select min(rowid) from emp)

                            

                                  Union

Select * from emp where rowid=(select max(rowid) from emp);

13)  TO GET WITH OUT FIRST AND LAST RECORD;

           select * from emp

                 minus

            select * from emp where rowid=(select min(rowid) from emp)

              minus

              select * from emp where rowid=(select max(rowid) from emp);

14) TO GET 7 RECORDS WITH OUT DUPLICATES.

Select * from emp e1 where 7>(select count(distinct sal) from emp e2 where e1.sal<e2.sal)

       Minus

Select * from emp where rowid not in(select min(rowid) from emp group by sal);

MATERIALIZED VIEW

15) CREATE MATERIALIZED VIEW NAGESH1 AS SELECT JOB,SUM(SAL) FROM EMP GROUP BY JOB;

16) CREATE VIEW NAGESH12 AS SELECT * FROM EMP;

17) QUARY THAT WILL DISPLAY THE TOTAL  NO OF  EMPLOYEES, AND OF  THE TOTAL NUMBER WHO WERE HIRED IN 1980,1981,82,83 ;

17)                      select Count(*),count(decode(to_char(hiredate,'yyyy'),'1980',empno))"1980" from emp;

18) Query that retrieves “2” highest paid employees from each department

Select deptno,empno,sal from emp e where 2>(select count(e1.sal) from emp e1 where e.deptno=e1.deptno and e.sal<e1.sal)order by 1,3 desc

18)                      The deptno which is having  Maximum avg sal

select deptno,avg(sal) from emp having avg(sal)=(select max(avg(sal))     rom emp group by deptno) group by deptno

19)                       Department name which is having maximum average sal

       select dname,avg(sal) from emp,dept where emp.deptno=dept.deptno having

        avg(sal)=(select max(avg(sal)) from emp,dept where emp.deptno=dept.deptno group by dname ) group by dname;

20)                      self join

select e.ename employee,e1.ename manager from emp e,emp e1 where e.mgr=e1.empno;

Change Package Life Cycle

Changeman is a version control tool used for manages and automates the process of migrating software changes or applications from a development environment to any test environment and then to the production environment.

Features of Changeman:

Ø  Provides notifications in a timely manner.

Ø  Maintains all historical information in a single repository.

Ø  Unique package concept guarantees the coordination of your entire change.

Step 1 (Create process): A Change Package contains all of the elements to be edited and installed into production, and is identified by a unique package ID automatically generated by Change Man. When a Change Package is created, the information that Change Man needs in order to track and control the package is entered. This includes the implementation instructions, whether it is a temporary or permanent change, the installation date and time, etc.

Step 2 (Checkout): The typical next step is to Checkout components from baseline. With checkout, components from your baseline libraries are copied to either a Change Man staging library or to a personal development library where changes can be made.

Step 3 (Staged): Package components are now ready to be Staged. In the stage process the user can make all the changes or modifications required to all components. For source components, staging will ordinarily run the appropriate translation procedure to create associated load modules. Components such as documentation or copy members are simply copied into the staging libraries, if they aren't there already.

Step 4 (Audit): Depending on the installation, the Change Package must pass an Audit before Freeze. The audit process ensures that no unexpected problems will occur. For example, if a component in the production library has been changed since it was checked out, Change Man alerts you to the problem by creating an out-of-synch condition for the package.

Step 5 (Freeze):  After successfully passing the audit, the next step is to Freeze the Change Package. This locks the package (prohibiting further changes), and makes the package available for the promotion and approval processes.

Step 6 (Promote): Promotion is an optional step. After successfully staging all components in the Change Package, the Change Package may then be Promoted. Promotion allows a Change Package to be moved through various levels of testing (e.g. promote from system testing to acceptance testing).

Step 7 (Approval):  Once all of the necessary Approvals have been gathered by Change Man, the package is ready to be installed. If the Manual installation method was selected when the package was created, the package will be installed immediately after the final approver has approved it. If the CMN method (Change Man's internal scheduler) was selected, Change Man will automatically install the package on the date and time specified at package creation. If an external scheduler is used (e.g. CA7, ESP, ZEKE, JOBTRAC, etc.), Change Man will convey the install information to that scheduler so that it can install the Change Package.

Step 8 (Baseline Ripple): Once the package has been installed, Change Man will perform the Baseline Ripple. Baseline Ripple is the process that Change Man executes to version all package components, i.e.: 0 becomes -1, -1 becomes -2, -2 becomes -3, etc. and the new baseline 0 version is installed.

TIPS ON EXTRACTING RECORDS FROM A FILE USING SORT JCL

OBJECTIVE:

1) To through some light on Sort card.

2) To extract particular number of records from a sequential file, which involves condition like select some records, then skip few records, then again select records and so on.

SORT UTILITY: SYNCSORT, DFSORT

We will discuss above thing by taking an example,

REQUIREMENT:

From a sequential file suppose we need to extract say first 5 records, skip next 4 records,

and again select next 5 records.

(Think this condition from the following point of view says if we have 100 conditions then what?)

For simplicity only have taken only two conditions. Assume that Input file has 20 records.

SOLUTIONS:

1.Using SKIPREC, STOPAFT parameters in SORT card.

2.Using OUTFIL in sort card.

3.Using OUTFIL with SAVE.

FIRST SOLUTIONS,

JCL would go like this,

FIRST STEP IN JCL would contain following sort card

//SYSIN      DD *

    SORT FIELDS=COPY,

    STOPAFT=5

    END

/*

Above sort card will select first five records from input file.

SECOND STEP IN JCL will contain following sort card,

 a) To skip next 5 +4 =9 records,

 b) To copy next five records starting from 10 Th record,

//SYSIN      DD *

    SORT FIELDS=COPY,

    SKIPREC=9,

    STOPAFT=5

    END

THIRD STEP:

This step is there to finally merge the files, which are created in previous two steps. Output of this merge step will be our required file.

DRAWBACK:

Here the number of condition is 2, so we needed only two steps.

Think about! What will you do if we have 100 conditions? Do we write?

100 STEPS? Surely not, it would be cumbersome.

To avoid this we have second approach,

SECOND SOLUTION:

Using OUTFIL in sort card.

Objective:

1) To solve above problem with minimum number of steps.

2) To have only one sort card for all condition.

3) And one final merge step to merge intermediate files.

Sort card that can be used would be like this,

FIRST STEP IN JCL will contain following sort card

//SYSIN      DD *

    SORT FIELDS=COPY

    OUTFIL FILES=1,STARTREC=1,ENDREC=5

    OUTFIL FILES=2,STARTREC=10,ENDREC=14

    END

/*

Above sort card will do following things,

1.      1.      One file will create which will have first five records.

2.      2.      Second file will created which will have next five records starting from 10 Th record

Make sure that you give following logical name of those intermediate files, which are created as SORTOF1,SORTOF2, SORTOF3 and so on.

SECOND STEP IN JCL,

Its a merge step, it will merge all files which are created above.

CONCLUSION:

Even 100 conditions are there, we would need only 2 steps to extract desired records from file.

DRAWBACK:

Lot of intermediate files may be created. Suppose, if 100 conditions are there, one hundred intermediate files would be created. Then again headache to merge all those 100 files in MERGE STEP.

            To avoid this we have third solution,

THIRD SOLUTION:

 It’s a general solution independent of number of conditions. Also it can do in SINGLE STEP.

JCL would be like this,

//N010053A JOB (1L17L120),XXXX.III,

...

...

...

...

//STEP11       EXEC PGM=IERRCO00,REGION=650K,RD=R

//*             PARM='VSCORE=5500K,MSG=AP,BMSG'

//SORTLIB      DD   DSN=SYS1.SORTLIB,DISP=SHR

//MODLIB       DD   DSN=SYS1.LOADLIB,DISP=SHR

//SYSOUT       DD   SYSOUT=(,),OUTPUT=(*.TOPAPER,*.TOHOLDQ)

//SYSUDUMP     DD   SYSOUT=(,),OUTPUT=(*.TOPAPER,*.TOHOLDQ)

//SORTIN       DD   DSN=T145.INPUT.FILE,

//             DISP=SHR

//SORTOF1      DD   DSN=&&TEMP1

//SORTOF2      DD   DSN=&&TEMP2

//SORTOF3      DD   DSN=T145.OUTPUT.FILE,

//             DISP=(NEW,CATLG,DELETE),UNIT=SYSALLDA,

//             SPACE=(8048,(545,2178),RLSE),

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

//SORTWK01     DD   SPACE=(CYL,(502,101),RLSE),UNIT=DASD

//SORTWK02     DD   SPACE=(CYL,(502,101),RLSE),UNIT=DASD

//SORTWK03     DD   SPACE=(CYL,(502,101),RLSE),UNIT=DASD

//SORTWK04     DD   SPACE=(CYL,(502,101),RLSE),UNIT=DASD

//DUMPDD       DD   SYSOUT=*

//DSPLD        DD   SYSOUT=*

//SYSIN      DD *

    SORT FIELDS=COPY

    OUTFIL FILES=1,STARTREC=6,ENDREC=9

    OUTFIL FILES=2,STARTREC=15,ENDREC=20

    OUTFIL FILES=3,SAVE

    END

/*

//*

Few points regarding this JCL,

1.      1.      Use of SAVE in sort card.

2.      2.      First temporary file will have four records after skipping first five records.

3.      3.      Second temporary file will have records starting from 15^th record till the last record.

4.      4.      Here the catch is file 1 and file 2 will have all those records, which we don’t want.

5.      5.      So file 3 ( T145.OUTPUT.FILE ) will have all first five records ,skipping next four records, again extracting next five records.

6.      6.      All intermediate files will be deleted at end of JOB.

CONCLUSION:

Thus, only one step is required to extract records from file. Also no merge step is required now. One more thing, in all of the above sortcard we have mentioned so far, we can cover conditions like INCLUDE, OMIT etc also.

JCL ABENDS Full Description-02

S613 Abend

A bad tape label.

S637 Abend

A bad concatenation, different types of devices were used.

An unreadable tape mark or label.

S706 Abend

The program on the library was not executable.

See linkage editor report that put the program on library.

S713 Abend

The tape was unexpired and the operator terminated the job.

S714 Abend

Labels on the tape were bad.

S722 Abend

Too many lines of print.

S804 Abend

Region too small for the program.

S806 Abend

Program not on the library. May need a JOBLIB or STEPLIB.

S80A Abend

Region too small for the program.

S813 Abend

Right tape volume, wrong dataset name.

Right dataset name, wrong tape volume.

S913 Abend

Security violation.

SA13 Abend

Label=n states the wrong number.

SB14 Abend

No space in a library directory for this member's name.

SB37 Abend

Insufficient disk space.

SD37 Abend

Insufficient disk space.

SE37 Abend

Insufficient disk space.

the maximum number of extents would be exceeded. For instance, when exceeding 16 extents of a PDS.

An E37 on tape datasets is most often caused when the number of requested volumes is exceeded. The default is 5,

therefore a request for the sixth volume will fail with a E37.

S0C1 Abend

Executing a program with an unresolved external reference.

Calling a program and the program was not included during link edit.

An uncontrolled loop moved data on top of instructions.

Reading a file that is not open

Your SORTIN DCB was not correct

Mixing compile options RES and NORES in different modules

S042Privileged Operation Abend

Read/write to unopened file

An uncontrolled loop moved data on top of instructions.

S0C4 Protection Abend

An uncontrolled loop moved data on top of instructions.

referencing a field in a record of a closed file

referencing an item in Linkage-Section when there was no PARM= in the JCL.

Calling/called programs have different length for items passed in Linkage Section

with COBOL Sort, doing a STOP RUN or GOBACK while

an input or output procedure is still running

S0C5 Addressing Abend

See reasons as for 0C4.

Falling through into an ENTRY statement

Transferring control into the middle of a SORT procedure.

S0C6 Specification Abend

Bad boundary alignment for binary data.

See reasons for 0C4

S0C7 Abend

Program attempting to do math on illegal data.

Data is not numeric, but should be.

Moving ZEROS to group item whose subordinate items

are packed-decimal

Uninitialized packed-decimal fields.

Record description is wrong. Field starts or ends in the wrong place in the record.

Find record description of creating program.

S0CB Abend

Attempting to divide by 0 and not using ON SIZE ERROR

U1002 Abend

Conflicting file attributes. See S013.

U1005 Abend

Executing with modules compiled both with RES and NORES

U1006 Abend

Subscript out of range

U1017 Abend

Missing DD statement in JCL for DISPLAY or ACCEPT verb

U1020 Abend

Problem opening or processing a file.

Check the file status.

U1026 Abend

COBOL sort failed.

U1034 Abend

Same as SB37 Abend

U1035 Abend

Conflicting DCB parameters. Same as S013.

U1037 Abend

Program control falls through the last physical statement in program,

which is not GOBACK/STOP RUN.

U1056 Abend

Program didn't close a file before ending

U1066, U1075 Abend

Conflicting DCB information for file defined as EXTERNAL

U1072, U1073, U1074 Abend

Illegal numbers in reference modification

U3000 Abend

COBOL LE intercepted the Abend. Messages in SYSDBOUT.

U4038 Abend

COBOL LE intercepted the Abend. Messages in CEEDUMP.

JCL ABENDS Full Description

S001-4 Abend
Input file record length is not equal to the length stated in the DD or the FD.
Wrong length record.
IO error, damaged tape, device malfunction.
With disk, reading a dataset that was allocated but never written to.
Writing to input file
Concatenation of files with different record lengths or record formats.

S001-5 Abend
Reading after the end of the file by non-COBOL program.
COBOL intercepts this and displays "QSAM error, status 92".
Out of space on output disk file.

S002 Abend
With variable format files used for output.
The record is larger than the track size.
The record length is greater than allowed maximum 32,768.
The wrong record length is being used on output.
The 4-byte record length indicator is wrong.
Record greater than 32,768 bytes

S013-10 Abend
A dummy file with no blocksize.

S013-14 Abend
A library has run out of space in its directory.
You have to backup, delete, and restore the library with IEBCOPY.
A dataset is sequential, but the JCL indicates that it is a library/PDS.

S013-18 Abend
A library member was specified in the JCL but was not found.

S013-20 Abend
The block size is not a multiple of record length.
Check record length in program, compare to actual record length of file

S013-34 Abend
The block size was found to be 0.
A new file is being created but block size was not in the JCL.

S013-40 Abend
Reading a file whose JCL has SYSOUT=

S106 Abend
The program on the program library was unreadable. Recompile and link.

S122 Abend
The job was canceled because it violated some restriction.
A dump was requested

S137 Abend
A tape has a bad trailer label.
Copy the file with IEBGENER, ignoring the error. The copy will be good.
Using LABEL=2 when there's only one dataset on the tape.

S213 Abend
A disk dataset was not actually on the volume stated in the VOL=SER=.
A disk dataset was not actually on the volume indicated in the catalog.

S222 Abend
The job was cancelled because it violated some restriction.
No dump was requested.

S237 Abend
The block count on a tape trailer label is wrong.
Probably caused by hardware error.
Copy the file with IEBGENER, ignoring the error. The copy will be good.
A problem with the second volume of tape or disk.

S313, 314 Abend
An Input/output error in the VTOC of a disk volume. Inform support staff.

S322 Abend
The job used more CPU time than it should have.
Either the estimate is wrong or the program is in an uncontrollable loop.

S413 Abend
A volume was needed that could not be mounted.

S422 Abend
Too many job steps.

S513 Abend
Two jobs or DDNAMES wanting same tape at same time.

S522 Abend
Job was waiting too long.