tag:blogger.com,1999:blog-26348399174276491182024-03-19T02:58:34.478+05:30IBM MAINFRAME FORUMThis blog intends to write on IBM Mainframe Technologies and cover key concepts in Cobol,DB2,CICS,JCL,VSAM and more..Anonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.comBlogger92125tag:blogger.com,1999:blog-2634839917427649118.post-1036269817593194952017-10-06T02:37:00.003+05:302017-10-06T02:37:58.694+05:30Interpreting TSO ISRDDN command in mainframe ISPFThe TSO ISRDDN command is a useful tool that lets you browse/view your active TSO session libraries. From any session, type TSO ISRDDN and the below widow will open up.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRiyg_2WemTiuCxNxMu67svibgqqo4UAM3xyAl7jFYASI1hUeG6H6icFlFlreDP07MU06PQKDN9cjrqy2qsr8LKdMcBQVdyFtYHkTvxJTCHjaAfpJIUYnT200pSqoC6k_SqxAAtlzCdP-g/s1600/isrddn.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="703" data-original-width="765" height="366" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRiyg_2WemTiuCxNxMu67svibgqqo4UAM3xyAl7jFYASI1hUeG6H6icFlFlreDP07MU06PQKDN9cjrqy2qsr8LKdMcBQVdyFtYHkTvxJTCHjaAfpJIUYnT200pSqoC6k_SqxAAtlzCdP-g/s400/isrddn.png" width="400" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
On the right side of the display is a list of DDNAME(in white colour) and their associated data sets ( (in Green colour) currently allocated to the user session.<br />
**Dataset names in green are blurred on purpose<br />
<br />
The left side of the display contains columns of information about individual data sets like the volume information and all. When you scroll right (using F11)or left(or F10) , the left side of the screen changes. Initially, the left side of the screen contains the volume name and disposition,if the dataset is SMS managed or not ,etc . If the disposition is red, there are other jobs waiting to use this data set as shown. <br />
<br />
<b>Generally the dataset associated with sysproc ddname contains the pds for the clist macros available in the system.</b><br />
<b><br /></b>
Try issuing the command LINKLIST or LPA. Either command will add both the system LINKLIST and LPA libraries to the display.The LINKLIST and LPALIB (Link Pack Area LIBrary) are related concepts. The LINKLIST is a collection of libraries, established during the IPL, that contain system and user load modules. Any program in the LINKLIST does NOT require a JOBLIB or STEPLIB to point to the load library -- the system can find such programs without any JCL statements. The LPALIB basically contains read-only programs (which may system or user load modules) that are reenterable.Anonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com22tag:blogger.com,1999:blog-2634839917427649118.post-21092745295435573432016-07-30T01:17:00.000+05:302016-07-30T22:59:16.121+05:30Zoned Decimal and packed Decimal (COMP- 3) fields. How Does COMP 3 saves storage space?Two computer codes which are used for internal representation of data are EBCDIC and ASCII.<br />
Extended Binary coded decimal interchange(EBCDIC) code is a 8 bit character encoding standard used in IBM environment.<br />
Most of the other Non IBM machines use ASCII standard.<br />
Below is the EBCDIC representation of the alphabets and numbers followed in IBM environment.<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhU-QcHYtKiZmAdU1pLRcbkX3JUR4r9rK1CBFRWD8cWG8GJtzejUGOV_oxmVS03cbEa7vQu7ZA95ZyeTcyJKKXy5VnCI8EhWqIsihgZKwqq5BFZTCqyAtbqVYahghXd3NPhnC8VmBXx4UUC/s1600/ebcdic.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="221" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhU-QcHYtKiZmAdU1pLRcbkX3JUR4r9rK1CBFRWD8cWG8GJtzejUGOV_oxmVS03cbEa7vQu7ZA95ZyeTcyJKKXy5VnCI8EhWqIsihgZKwqq5BFZTCqyAtbqVYahghXd3NPhnC8VmBXx4UUC/s400/ebcdic.png" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">EBCDIC represenation of Aphabets and Numerals</td></tr>
</tbody></table>
<br />
Following the chart, we can see 'a' (2nd row in above chart) is represented in binary in 1000000001 and has the hex value of 81.<br />
<br />
All characters & numbers are represented in 8 bits. These 8 bits can be broken into two 4 bit segments. For historical reasons, the high order 4 bits are called <b>zone part</b> and low order 4 bits are called <b>digits part.</b><br />
Each of these 4 bits are represented by one hexadecimal character. Thus each 8 bit EBCDIC can be represented by two character hexadecimal value. The high order 4 bits are used to represent whether the value stored is a letter,positive or negative number. Low order 4 bits represents numbers 0-9.<br />
<br />
Let us see how the letter D is stored internally.(Map with the chart above)<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcDfvS4Alrd_J01XqoQ-LSf48N3I8Xhn-BrCO9uN71uYuT26RWbU1eDtr8aFekRWv3oGyNAHHUbIEtBdUDi-gUFtYxXa-7HBraP-KLehr4zTuwUhhRsradtNXtHYd28lMJHjjeW2UGObJB/s1600/LetterD.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="50" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcDfvS4Alrd_J01XqoQ-LSf48N3I8Xhn-BrCO9uN71uYuT26RWbU1eDtr8aFekRWv3oGyNAHHUbIEtBdUDi-gUFtYxXa-7HBraP-KLehr4zTuwUhhRsradtNXtHYd28lMJHjjeW2UGObJB/s200/LetterD.png" width="200" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
In similar fashion, we can see how the letter 8 will be stored in the system( Map with the chart above)<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCfrwxBQwn8VbP1nOz9ckBEW6UBvqQ6PhHDIFYrcgtqb65D-F6WuS51A5K63wggciUzQ4T-SXg1r4HWP-ezUSVMDBa6bn2XhBlLuodMckRSTjyC1WQVsSNSZmv27Yd3H3niO1m1q-nD5sy/s1600/numer8.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="58" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCfrwxBQwn8VbP1nOz9ckBEW6UBvqQ6PhHDIFYrcgtqb65D-F6WuS51A5K63wggciUzQ4T-SXg1r4HWP-ezUSVMDBa6bn2XhBlLuodMckRSTjyC1WQVsSNSZmv27Yd3H3niO1m1q-nD5sy/s200/numer8.png" width="200" /></a></div>
Thus we see that in zoned decimal each byte represents one digit.<br />
<br />
So, how will the positive number 62587 be stored in the system?<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivaL3BOX2-Goa8_ZiEmCO4IVIYYmyi2oHqvuag2YK68MgZ4wD5GNxuV5Mr9UaLQraAh7x5LQUMwCUIA75chSzJEmlPCOaLvA19HQ6UCHbieyod6X-52557maD8Zp8dEU0j1InBp8HSxB3d/s1600/5+digit+numb.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="97" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivaL3BOX2-Goa8_ZiEmCO4IVIYYmyi2oHqvuag2YK68MgZ4wD5GNxuV5Mr9UaLQraAh7x5LQUMwCUIA75chSzJEmlPCOaLvA19HQ6UCHbieyod6X-52557maD8Zp8dEU0j1InBp8HSxB3d/s320/5+digit+numb.png" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Zoned decimal number in EBCDIC repreentaion</td></tr>
</tbody></table>
In zoned decimal, the zone portion of the rightmost byte represents the sign of the number.<br />
For positive number, the zone portion is always 1111.<br />
For negative number the zone portion becomes 1101.<br />
Thus -6 can will be represented as: 1101(Zone part) 0110(Digit part)<br />
<br />
<b><span style="color: #990000;">Packed Decimal fields: How does it save spaces? Let's see how it works.</span></b><br />
<b><span style="color: #990000;"><br /></span></b>
In packed decimal format, the zone portion is stripped from each byte, so that two digits can be packed together in one byte.This way the <span style="color: #cc0000;">space for zone portion of each byte</span> can be used to represent another digit. Thus two digits are represented in single byte there by saving the space.<br />
Only the zone portion of the low order or the rightmost byte is kept for storing the sign of the field.<br />
Considering the same example, see how the number 62587 will be stored in packed decimal format.<br />
Space utilized will be reduced from 5 bytes to 3 bytes.<br />
<br />
Number : 6 2 5 8 7<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidW0NZ3ET3C_MyPw2xSKBGIFOn8gVciEeipJER3DlgkzD6m9tN9cGF3qeGTBxwmtZCaIp_QtE1RLMB6Ff8vawsMHmkCStHTEr4XARvAJJTt6iQF4eHO3fCVmQWYmeJT2ryhkg4Dci0nZ_9/s1600/packed.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="140" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidW0NZ3ET3C_MyPw2xSKBGIFOn8gVciEeipJER3DlgkzD6m9tN9cGF3qeGTBxwmtZCaIp_QtE1RLMB6Ff8vawsMHmkCStHTEr4XARvAJJTt6iQF4eHO3fCVmQWYmeJT2ryhkg4Dci0nZ_9/s320/packed.png" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Packed decimal representation</td></tr>
</tbody></table>
We can see that two digits are packed in single byte by stripping of the zone portion of all the bytes except the last byte (marked in brown).<br />
<div>
Had it been a negative number the last byte would have contained 1101 in place of 1111.<br />
<br />
This way, a packed decimal number saves space by packing two digits in one byte.<br />
<br />
<br /></div>
Anonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com21tag:blogger.com,1999:blog-2634839917427649118.post-89601249898486341712016-06-24T23:46:00.000+05:302016-07-11T23:32:54.915+05:30 COALESCE function in DB2 . Alternative to NULL indicator variable in DB2 ; SQLCODE - 305.If a table column is <span style="color: #660000;"><b>not</b></span> defined with NOT NULL, then it implies that the column value can be NULL sometimes. For such columns,if we do not use null handling technique, then the application program will give sqlcode -305.<br />
To avoid that we use indicator variables.However , we can also use the DB2 COALESCE function.<br />
<br />
COALESCE is a function that allows you to substitute any numeric or character value for a null, based on the column data type.<br />
Suppose in DEPT table, i have one column for optional subject(<b>optsubj</b>). It can have a value of 'Y'or 'N'. However since the column is not defined with NOT NULL keyword,it can fetch null values.<br />
If we simply use the query below, our application program will return sqlcode -305.<br />
<blockquote class="tr_bq">
SELECT fname<br />
,lname<br />
,optsubj<br />
into :hv-fname<br />
:hv-lname<br />
:hv-optsubj<br />
from DEPT</blockquote>
<br />
However, we can tweak the same query in the below way using COALESCE function to get rid of -305<b> without using indicator variable</b><br />
<blockquote class="tr_bq">
<br />
SELECT fname<br />
,lname<br />
,coalesce(optsubj,space(1))<br />
into :hv-fname<br />
:hv-lname<br />
:hv-optsubj<br />
from DEPT</blockquote>
<br />
What it will do is, it will replace NULL value with space of 1 byte whenever the column optsubj fetches a null value for any record;<br />
<div>
This function can be used in many ways to get desired results. </div>
Anonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com2tag:blogger.com,1999:blog-2634839917427649118.post-87478195004304677552016-06-16T00:45:00.000+05:302017-03-26T01:56:01.431+05:30RENT compiler option in cobol - Understanding reentrant programmingFor understanding RENT compiler option, we need to know what is a re-entrant program.<br />
A re-entrant is a program that doesn’t change itself. “How can a program change itself?” Does the word "Multi threading " looks familiar? Reentrant and mutithreading are related but somewhat different topics.<br />
We can divide our program(logically) into three parts:<br />
1. Program code – the instructions that are executed when a program runs.<br />
2. Constants – constants used in a program. These are set when the program is written, and never change.<br />
3.Working Storage area – or variables. This area changes when different user fires the same program.<br />
<br />
<span style="color: #990000;">"A reentrant Cobol program gives each user <b>their own copy of working storage. " </b></span><br />
For every user,MVS supplies the GETMAIN and STORAGE macros to support this and they can be<br />
used by subsystems such as CICS or IMS or whatever to support reentrant programs.<br />
<br />
For each user the copy of the working storage is dynamically acquired and initialized from the original copy. When a user interacts with the program , he only modifies his copy of the variables.<br />
<br />
When the program is interrupted to give another user a turn to use the program, information about the data area associated <b><span style="color: #cc0000;">with that user is saved</span></b>.<br />
<br />
<b><span style="color: #660000;">So, what will happen if we write a CICS program as non-renetrant?</span></b><br />
When one transaction waits, a second transaction can come and modify the working storage variables, and thus providing unstable or undesirable results.<br />
<br />
Thus, Re-entrant programs allow a single copy of a program or routine to be used concurrently by two or more processes.<br />
<br />
<br />
For cobol, we use the compiler option RENT to generate reentrant object program.Anonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com8tag:blogger.com,1999:blog-2634839917427649118.post-87470238447985382932016-03-27T02:42:00.001+05:302016-03-27T23:29:27.248+05:30FETCH FIRST N ROWS Vs OPTIMIZE FOR N ROWS in DB2<span style="font-family: "verdana" , sans-serif;">Very Often we use these two statements in our SQL query,ie, OPTIMIZE FOR N ROWS and FETCH FIRST N ROWS ONLY.</span><br />
<span style="font-family: "verdana" , sans-serif;">Although it may sound similar,but both of them have specific usage when it comes to the optimizer. This post we will try to see difference between the two.</span><br />
<br />
<span style="font-family: "verdana" , sans-serif;">As the name suggests <span style="color: #660000;">FETCH FIRST 5 ROWS ONLY,</span> it actually puts a limitation on the number of rows the query is returning.I am assuming here n=5. So, here <span style="color: #990000;"><b>user gets only 5 rows even though there can be 100 qualifying rows for that query</b></span>.</span><br />
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<span style="font-family: "verdana" , sans-serif;"><span style="color: #660000;">OPTIMIZE FOR 5 ROWS</span> will straight way influence the optimizer.<span style="color: #990000;"><b>It does not limit the number of rows SQL will return</b></span>. So using this , we will get all the qualifying rows,may be incrementally.Intent here is to give priority to retrieve first few rows.Once optimizer understands this ,it will give preference to access plan that will minimize the response time.So,DB2 will send the rows in a single block. Consider the below scenario where this will come handy.</span><br />
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<span style="font-family: "verdana" , sans-serif;">You have an online application where the screen can hold details of 5 customers at a time. Suppose you have the below query to fetch the customer details</span><br />
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<span style="color: #20124d; font-family: "verdana" , sans-serif;"><i>Select Fname, Lname, Dept, Sal,Rollno</i></span><br />
<span style="color: #20124d; font-family: "verdana" , sans-serif;"><i> From Dept table </i></span><br />
<span style="color: #20124d; font-family: "verdana" , sans-serif;"><i>Order by Sal Desc;</i></span><br />
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<span style="font-family: "verdana" , sans-serif;">We are assuming the index to be defined on Rollno. If a descending index also exists on SAL column, its likely to have low cluster ratio.</span><br />
<span style="font-family: "verdana" , sans-serif;">Without using the OPTIMIZE clause, DB2 will do a <span style="color: #990000;">full table space scan</span> and <span style="color: #660000;">sort on sal column</span> to fetch you the details.</span><br />
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<span style="font-family: "verdana" , sans-serif;">Now on Adding '<span style="color: #660000;">ÓPTIMIZE FOR 5 ROWS</span>' to the query, DB2 will use SAL index directly because it knows you need the details of 5 highest paid employees.</span><br />
<span style="font-family: "verdana" , sans-serif;">So, it displays 5 rows to the screen and depending on user input will process the next 5 or do something else.Here DB2 encourage <span style="color: #990000;">matching index scan and would not prefer list or sequential prefetch. </span></span><span style="font-family: "verdana" , sans-serif;">Access plan selected for 5 records may not be good for 100 rows.</span><br />
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<span style="font-family: "verdana" , sans-serif;">Thus, this OPTIMIZE clause comes handy for queries where you can process incrementally.We can use it to get rows directly from table without going through any buffering operations like sorting. </span>Anonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com17tag:blogger.com,1999:blog-2634839917427649118.post-18700320006620245212016-03-19T00:46:00.003+05:302016-05-17T23:59:23.699+05:30DB2 unload utility using the program DSNTIAUL<div>
<span style="font-family: "verdana" , sans-serif;">IBM provides two methods of unloading data from a DB2 table. One is DSNTIAUL program that is run under IKJEFT01 or IKJEFT1A, or 1B and the other being DSNUTILB program. DSNUTILB is a fairly new and is considered IBM’s Fast Unload Utility. Each has unique functionality that you may want to explore prior to choosing one. </span><br />
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div>
<span style="font-family: "verdana" , sans-serif;">Outlined below are functions of DSNTIAUL process. </span></div>
<div>
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div>
<span style="font-family: "verdana" , sans-serif;">1. DSNTIAUL Program: The DSNTIAUL program allows two type of unloads to be invoked against a DB2 table. When invoking DSNTIAUL you may specify an entire table unload or a selective SQL unload. The key DD statements that are being used are the SYSIN and the SYSTSIN. Both types of unloads are being run under IKJEFT1B.</span></div>
<div>
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div>
<span style="font-family: "verdana" , sans-serif;">Basic JCL example:</span><br />
<span style="font-family: "verdana" , sans-serif;">//UNLOAD EXEC PGM=IKJEFT1B, DYNAMNBR=20</span><br />
<span style="font-family: "verdana" , sans-serif;">//STEPLIB <span class="Apple-tab-span" style="white-space: pre;"> </span>DD DSN=SYSDB2.DQA0.SDSNLOAD, DISP=SHR</span><br />
<span style="font-family: "verdana" , sans-serif;">// <span class="Apple-tab-span" style="white-space: pre;"> </span>DD DSN=SUBSYS.DB2.DSNEXIT, DISP=SHR</span><br />
<span style="font-family: "verdana" , sans-serif;">//*************************************************************</span><br />
<span style="font-family: "verdana" , sans-serif;">//SYSPRINT DD SYSOUT=*</span><br />
<span style="font-family: "verdana" , sans-serif;">//SYSTSPRT DD SYSOUT=*</span><br />
<span style="font-family: "verdana" , sans-serif;">//SYSUDUMP DD SYSOUT=*</span><br />
<span style="font-family: "verdana" , sans-serif;">//SYSOUT DD SYSOUT=*</span><br />
<span style="font-family: "verdana" , sans-serif;">//SYSREC00 DD DSN=userid.tablerecords.dataset,</span><br />
<span style="font-family: "verdana" , sans-serif;">// <span class="Apple-tab-span" style="white-space: pre;"> </span> DISP=(,CATLG,DELETE),</span><br />
<span style="font-family: "verdana" , sans-serif;">// <span class="Apple-tab-span" style="white-space: pre;"> </span> UNIT=SYSDA,SPACE=(CYL,(300,300),RLSE)</span><br />
<span style="font-family: "verdana" , sans-serif;">//*</span><br />
<span style="font-family: "verdana" , sans-serif;">//SYSPUNCH DD DSN=user-id.tablelayout.dataset,</span><br />
<span style="font-family: "verdana" , sans-serif;">// <span class="Apple-tab-span" style="white-space: pre;"> </span> UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE),DISP=(,CATLG,DELETE)</span><br />
<span style="font-family: "verdana" , sans-serif;">//SYSTSIN DD *</span><br />
<span style="font-family: "verdana" , sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span> SYSTEM (SUBSYS) RUN PROGRAM (DSNTIAUL) PLAN (DSNTIAUL) -</span><br />
<span style="font-family: "verdana" , sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span> LIBRARY (‘SUBSYS.DB2.RUNLIB.LOAD’)</span><br />
<span style="font-family: "verdana" , sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span> END</span><br />
<span style="font-family: "verdana" , sans-serif;">//SYSIN DD *</span><br />
<span style="font-family: "verdana" , sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>DBCREATOR.TABLENAME </span><br />
<span style="font-family: "verdana" , sans-serif;">/*</span><br />
<span style="font-family: "verdana" , sans-serif;">//*</span><br />
<span style="font-family: "verdana" , sans-serif;"></span><br />
<br /></div>
<div>
</div>
<div>
<div style="background-color: white;">
<span style="font-family: "verdana" , sans-serif;">For selective Unload, like when you are using a where clause or using select statements (like SELECT * from user_table;) we need to use <b>PARM('SQL')</b> in SYSTSIN statement; else you may endup with SQLCODE -104.</span><br />
<span style="font-family: "verdana" , sans-serif;">Your SYSTSIN would look like</span><br />
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<span style="font-family: "verdana" , sans-serif;">//SYSTSIN DD *</span><br />
<span style="font-family: "verdana" , sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span> SYSTEM (SUBSYS) RUN PROGRAM (DSNTIAUL)</span><br />
<span style="font-family: "verdana" , sans-serif;"> PLAN (DSNTIAUL) -</span><br />
<span style="font-family: "verdana" , sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span> LIBRARY (‘SUBSYS.DB2.RUNLIB.LOAD’) <span style="color: #990000;">PARM('SQL')</span></span><br />
<span style="font-family: "verdana" , sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span> END </span><br />
<span style="font-family: "verdana" , sans-serif;">//SYSIN DD *</span><br />
<span class="Apple-tab-span" style="font-family: "verdana" , sans-serif; white-space: pre;"> </span><span style="font-family: "verdana" , sans-serif;">SELECT * FROM schema.Tablename ;</span><br />
<div>
<br /></div>
</div>
</div>
Anonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com0tag:blogger.com,1999:blog-2634839917427649118.post-5144777419583587292016-02-18T22:51:00.000+05:302016-02-19T22:48:30.113+05:30TSO WHOHAS code in REXXSometimes we face job abends, or job waiting for resource owing to the fact that some datasets get into contention with some other jobs or some user is using that particular file. Most of the organizations use TSO WHOHAS command to check the user holding the dataset. In this post we will see the piece of code in REXX which makes the command work.<br />
<br />
<blockquote class="tr_bq">
/* REXX*/<br />
DSNAME = ARG(1)<br />
IF DSNAME = ' ' THEN DO<br />
SAY 'ENTER DATASET NAME'<br />
PARSE UPPER EXTERNAL DSNAME<br />
END<br />
ADDRESS TSO<br />
IF SYSDSN("'"DSNAME"'") <> 'OK' THEN DO<br />
SAY "DATASET DOES NOT EXIST"<br />
ADDRESS ISREDIT<br />
EXIT<br />
END<br />
ADDRESS TSO "ISRDDN E ' "DSNAME" ' "<br />
EXIT</blockquote>
Simple piece of code and it uses ISRDDN utility. It is an IBM utility which lists all the allocated DDNAME for your current TSO session.<br />
<br />
Without the rexx code also , we even can execute the command<br />
<b><span style="color: #990000;">TSO ISRDDN ENQ 'Data-set Name'</span></b><br />
and it will show the corresponding information<br />
<br />Anonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com0tag:blogger.com,1999:blog-2634839917427649118.post-28193024926100760532016-01-12T22:57:00.000+05:302016-01-12T22:57:47.456+05:30How to find the plan and package name in DB2 from program name<div style="background-color: white; font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif; font-size: small;">
Suppose we have a cobol-db2 module named 'PGM-ABC'</div>
<div style="background-color: white; font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif; font-size: small;">
We want to bind it and run the program. But we are not sure about the package and plan details.So where do we find it from? SYSIBM tables will give us the details we need.Lets check it out.</div>
<div style="background-color: white; font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif; font-size: small;">
With the program name we can query SYSIBM.SYSPACKAGE table giving the name of the program(PGM-ABC) against the column 'NAME'. We can use DB2 file-aid tool or use the below query in SPUFI.</div>
<div style="background-color: white; font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif; font-size: small;">
<br /></div>
<div style="background-color: white;">
<i><span style="color: blue; font-family: Trebuchet MS, sans-serif;">Select location, collid,name ,contoken,timestamp,bindtime from <b>SYSIBM.SYSPACKAGE</b> where NAME = 'PGM-NAME' ;</span></i></div>
<div style="background-color: white; font-size: small;">
<i><span style="color: blue; font-family: Trebuchet MS, sans-serif;"><br /></span></i></div>
<div style="background-color: white; font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif; font-size: small;">
Once we get the COLLID,we need to query the SYSIBM.SYSPACKLIST table to get the plan name</div>
<div style="background-color: white; font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif; font-size: small;">
<br /></div>
<div style="background-color: white;">
<span style="color: blue; font-family: Trebuchet MS, sans-serif;"><i>SELECT PLANNAME,LOCATION,COLLID FROM <b>SYSIBM.SYSPACKLIST</b> WHERE COLLID='colld-id-name-we-got-above';</i></span></div>
<div style="background-color: white; font-size: small;">
<span style="color: blue; font-family: Trebuchet MS, sans-serif;"><i><br /></i></span></div>
<div style="background-color: white; font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif; font-size: small;">
From experience, we should try to select the latest rows from the tables checking the TIMESTAMP fields; or check what other ppl have used in recent times.</div>
Anonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com1tag:blogger.com,1999:blog-2634839917427649118.post-58999422246839233202016-01-06T01:34:00.000+05:302016-01-06T23:32:33.026+05:30SAS in Mainframes(z/Os) - Connecting to DB2 from SAS in mainframes<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">This post is all about connecting DB2 from SAS in mainframe environment and using the result set for further processing. To call and connect to DB2 session we need the SQL <b>proc</b> to be called first using the statement <b>PROC SQL</b>. When used in a program we need to begin the code with PROC SQL and end it with <b>QUIT</b>; In Between these two we can write our SQL codes to fetch the required data.<br />Without wasting much time lets check the first JCL used to connect to db2.</span><span style="font-family: Trebuchet MS, sans-serif;"><br /></span><br />
<br />
<div>
<div style="background-color: white;">
<span style="font-family: Verdana, sans-serif;">//TTYYTST JOB(TEST),CLASS=I,MSGCLASS=X,MSGLEVEL=(1,1)</span></div>
<div style="background-color: white;">
<span style="font-family: Verdana, sans-serif;">// NOTIFY=&SYSUID</span><br />
<span style="font-family: Verdana, sans-serif;">//STEP01 EXEC PROC=SAS,OPTIONS=' '</span><br />
<span style="font-family: Verdana, sans-serif;">//WORK DD SPACE=(CYL,(,500)),VOL=(,,,59)</span><br />
<span style="font-family: Verdana, sans-serif;">//OUTFILE DD DSN=TEST.OUTPUT.SAS,</span><br />
<span style="font-family: Verdana, sans-serif;">// DISP=(,CATLG,DELETE),</span><br />
<span style="font-family: Verdana, sans-serif;">// DCB=(RECFM=FB,LRECL=122,BLKSIZE=000),</span><br />
<span style="font-family: Verdana, sans-serif;">// UNIT=SYSDA,SPACE=(TRK,(500,500),RLSE)</span><br />
<span style="font-family: Verdana, sans-serif;">//SYSIN DD *</span><br />
<span style="color: blue; font-family: Verdana, sans-serif;">OPTION NOCENTER;</span><br />
<span style="color: blue; font-family: Verdana, sans-serif;">OPTION SORTLIB;</span><br />
<span style="color: #990000; font-family: Verdana, sans-serif; font-weight: bold;">PROC SQL</span><span style="color: blue; font-family: Verdana, sans-serif;"><b>; <== </b>Starts here</span><br />
<span style="color: #990000; font-family: Verdana, sans-serif;">CONNECT TO DB2 (SSID=DQAT); <== DQAT is the db2 subsystem here </span><br />
<span style="color: #660000; font-family: Verdana, sans-serif;"> CREATE TABLE <i>test</i> AS <== <i>test is the sas datset here.</i></span><br />
<span style="color: blue; font-family: Verdana, sans-serif;"> SELECT * </span><br />
<span style="color: blue; font-family: Verdana, sans-serif;"> FROM CONNECTION TO DB2 (</span><br />
<span style="color: blue; font-family: Verdana, sans-serif;">select fname,</span><br />
<span style="color: blue; font-family: Verdana, sans-serif;"> lastname,</span><br />
<span style="color: blue; font-family: Verdana, sans-serif;"> dept_name, </span><br />
<span style="color: blue; font-family: Verdana, sans-serif;">from test01.dept</span><br />
<span style="color: blue; font-family: Verdana, sans-serif;">where student_id = 1 fetch first 5 rows only) ;</span></div>
<div>
<div>
<span style="color: blue; font-family: Verdana, sans-serif;"><br /></span></div>
</div>
</div>
<div>
<span style="color: blue; font-family: Verdana, sans-serif;">%PUT SQLXRC=&SQLXRC;</span></div>
<div>
<span style="color: blue; font-family: Verdana, sans-serif;">%PUT SQLMSG=&SQLMSG;</span></div>
<div>
<span style="color: #990000; font-family: Verdana, sans-serif;"><b>QUIT;</b></span></div>
<div>
<span style="color: blue; font-family: Verdana, sans-serif;"><br /></span></div>
<div>
<span style="color: blue; font-family: Verdana, sans-serif;">DATA STEP1;</span></div>
<div>
<span style="color: blue; font-family: Verdana, sans-serif;">SET <i>TEST</i>;</span></div>
<div>
<span style="color: blue; font-family: Verdana, sans-serif;">FILE OUTFILE DLM=',' ;</span></div>
<div>
<span style="color: blue; font-family: Verdana, sans-serif;">IF _N_=1 THEN DO;</span></div>
<div>
<span style="color: blue; font-family: Verdana, sans-serif;">PUT 'FIRST NAME,'</span></div>
<div>
<span style="color: blue; font-family: Verdana, sans-serif;"> 'LAST NAME,'</span></div>
<div>
<span style="color: blue; font-family: Verdana, sans-serif;"> 'DEPT NAME,'</span></div>
<div>
<span style="color: blue; font-family: Verdana, sans-serif;">;</span></div>
<div>
<span style="color: blue; font-family: Verdana, sans-serif;">END;</span></div>
<div>
<span style="color: blue; font-family: Verdana, sans-serif;">PUT FNAME</span></div>
<div>
<span style="color: blue; font-family: Verdana, sans-serif;"> LASTNAME</span></div>
<div>
<span style="color: blue; font-family: Verdana, sans-serif;"> DEPT_NAME</span></div>
<div>
<span style="color: blue; font-family: Verdana, sans-serif;">;</span></div>
<div>
<span style="font-family: Verdana, sans-serif;">//SYSPRINT DD SYSOUT=*</span></div>
<div>
<span style="font-family: Verdana, sans-serif;">//*</span></div>
<div>
<br /></div>
<div>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">JCL is self explanatory. %PUT SQLXRC and SQLMSG are the macros used to capture the DB2 return codes and error messages if any.</span></div>
Anonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com3tag:blogger.com,1999:blog-2634839917427649118.post-48963318627366797492016-01-02T00:07:00.001+05:302016-01-02T00:07:34.581+05:30Invoke CA7 commands from Batch terminal - use of SASBSTR<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
CA7 is a product of Computer Associates and is a scheduling tool for batch jobs in mainframe. When the number of jobs to be executed are astronomical, it is difficult to manage the jobs manually. So we need a tool like CA7 to manage the same. Manipulation of jobs, predecessor and triggers are done through CA7 panels. But sometimes we need to define hundreds of jobs to CA7, it is advisable to use batch terminal to do the same. Also batch terminals are less prone to errors. Batch terminals are a set of CA7 commands in a member of a partitioned dataset which is input to a CA7 batch program and submitted using a JCL</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
<br /></div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
The program which executes the CA7 commands in batch is called <span style="color: #990000;"><b>SASSBSTR</b></span>. The list of commands are put into a member of a partitioned dataset and given as an input to the program through SYSIN DD statement in the JCL. When executing the SASSBSTR program, the SYSIN input statements are copied into the input dataset called CA7. Communication dataset informs CA7 that there are commands waiting in the input dataset. These are read and processed by CA7. Any output is written to the output dataset. When all the commands have been processed, SASSBSTR copies the contents of the output dataset to SYSPRINT. Have a look into the below diagram</div>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgmNzfqJzT1CdpgEavB24Y3bIXiZaAPG-Vm6qIc331SsGWNsq13OA8lqE94ILpr089x9sHZZrW-h8Lz5-frdYZncTDAqRJ6YT1RdzNefguZSHCbAgWAKooVI9PVslGQrTtjgrMfR2Vy2S2_/s1600/CA7_SASBSTR.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="197" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgmNzfqJzT1CdpgEavB24Y3bIXiZaAPG-Vm6qIc331SsGWNsq13OA8lqE94ILpr089x9sHZZrW-h8Lz5-frdYZncTDAqRJ6YT1RdzNefguZSHCbAgWAKooVI9PVslGQrTtjgrMfR2Vy2S2_/s320/CA7_SASBSTR.png" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">CA7 commands from batch terminal</td></tr>
</tbody></table>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: small;">
<br /></div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: small;">
<br /></div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;">Now, lets have the JCL to execute the CA7 from batch terminal.</span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;"><br /></span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;">//@SASBSTR EXEC PGM=<b>SASBSTR</b></span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;">//UCC7CMDS DD DSN=TTOS.NCA7A01.COMMDS,DISP=SHR</span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;">//SYSDUMP DD SYSOUT=*</span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;">//SYSPRINT DD SYSOUT=*</span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;">//SYSOUT DD SYSOUT=*</span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;">//SYSIN DD *</span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="color: #990000; font-family: Verdana, Arial, Helvetica, sans-serif;">LJOB,JOB=SJABCD7,LIST=ALL <== CA7 command goes here</span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;">/*</span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;">//*</span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;"><br /></span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
All relevant information for the particular job SJABCD7 will be listed in SYSOUT since we gave SYSPRINT DD SYSOUT=*.</div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;"><br /></span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;">Check the commonly used <span style="color: #cc0000;">CA7 commands</span> in this <a href="http://mframes.blogspot.in/2015/11/frequently-used-ca7-commands.html" target="_blank"><span style="color: #cc0000;">page</span></a>. We can use these commands in the SYSIN DD* statements.</span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;"><br /></span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<b>Explanation of JCL:</b></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
SASSBSTR is the program which executes the batch terminal</div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
UCC7CMDS DD statement is a communication dataset which is used to read the CA7 commands from SYSIN DD Statements into the input dataset.</div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<br /></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
The CA7 commands are embedded within the SYSIN DD statement.All valid commands can be used here. Usually the programs are written in a cataloged procedure and the batch terminal is given as an input to the program. The output can be stored in a dataset.</div>
</div>
Anonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com2tag:blogger.com,1999:blog-2634839917427649118.post-21033800951388017622015-12-30T00:09:00.001+05:302015-12-30T23:05:58.264+05:30Passing Data from JCL to COBOL program<div style="background-color: white;">
<span style="font-family: Verdana, sans-serif;">We pass data from JCL to cobol program in two ways:</span></div>
<div style="background-color: white;">
<span style="font-family: Verdana, sans-serif;">1. Using the PARM keyword </span><br />
<span style="font-family: Verdana, sans-serif;">2. Using SYSIN DD in JCL</span><br />
<span style="font-family: Verdana, sans-serif;">The basic difference between these two ways of passing are as follows:</span></div>
<div style="background-color: white;">
<span style="font-family: Verdana, sans-serif;">A. Using 'PARM=' on the exec keyword, we can pass only 100 characters of data. Also in cobol we need linkage section to take the values in.</span></div>
<div style="background-color: white;">
<span style="font-family: Verdana, sans-serif;">B. When we pass data through SYSIN, we need to have the accept keyword in COBOL. For each row in SYSIN, we need to have corresponding ACCEPT verb in cobol.</span></div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
<br /></div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
<span style="color: #990000;"><u>Sample cobol program accepting PARM value through linkage</u></span><br />
...<br />
......<br />
LINKAGE SECTION.</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
01 LS-TEST-PARM.</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
05 LS-TEST-LENGTH PIC S9(04) USAGE COMP.</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
05 LS-VAR1 PIC 9(02).</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
05 LS-VAR2 PIC 9(02).</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
<br /></div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
PROCEDURE DIVISION USING LS-TEST-PARM.</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
DISPLAY LS-VAR1.</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
DISPLAY LS-VAR2.</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
DISPLAY LS-TEST-LENGTH.</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
STOP RUN.</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
<br /></div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
<span style="color: #990000;"><u>Have a look into the corresponding JCL.</u></span></div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
//TTYYTST JOB(TEST),CLASS=I,MSGCLASS=X,MSGLEVEL=(1,1)</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
// NOTIFY=&SYSUID</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
//*</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
//JSTEP01 EXEC PGM=SAMPLE3,PARM='1211212'</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
//STEPLIB DD DSN=TEST.LOAD.LIB1,DISP=SHR</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
//SYSPRINT DD SYSOUT=*</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
//SYSOUT DD SYSOUT=*</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
<br /></div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
<b>RESULT:</b></div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
12</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
11</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
007</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
<br /></div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
<span style="color: #990000;"><u>Sample program accepting data from SYSIN .</u></span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;"><span style="background-color: white; line-height: 18px;">...</span></span><br />
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;"><span style="background-color: white; line-height: 18px;">....</span></span><br />
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;"><span style="background-color: white; line-height: 18px;">DATA DIVISION.</span></span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;"><span style="background-color: white; line-height: 18px;">WORKING-STORAGE SECTION.</span></span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;"><span style="background-color: white; line-height: 18px;">01 WS-TOTAL</span></span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;"><span style="background-color: white; line-height: 18px;"> 05 WS-VAR1 PIC X(02).</span></span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;"><span style="background-color: white; line-height: 18px;"> 05 WS-VAR2 PIC X(02).</span></span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">PROCEDURE DIVISION.</span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">INITIALIZE WS-TOTAL.</span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">ACCEPT WS-VAR1.</span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">ACCEPT WS-VAR2.</span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">DISPLAY WS-VAR1.</span></div>
<div style="font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;"><span style="background-color: white;">DISPLAY WS-VA22 .</span></span></div>
<div style="background-color: white; font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;"><br /></span></div>
<div style="background-color: white; font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="color: #990000; font-family: Verdana, Arial, Helvetica, sans-serif;"><u>Corresponding JCL:</u></span></div>
<div style="background-color: white; font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<div style="font-family: Verdana, Arial, Helvetica, sans-serif;">
//TTYAATST JOB(TEST),CLASS=I,MSGCLASS=X,MSGLEVEL=(1,1)</div>
<div style="font-family: Verdana, Arial, Helvetica, sans-serif;">
// NOTIFY=&SYSUID</div>
<div style="font-family: Verdana, Arial, Helvetica, sans-serif;">
//*</div>
<div style="font-family: Verdana, Arial, Helvetica, sans-serif;">
//JSTEP01 EXEC PGM=SAMPLE1</div>
<div style="font-family: Verdana, Arial, Helvetica, sans-serif;">
//STEPLIB DD DSN=TEST.LOAD.LIB1,DISP=SHR</div>
<div style="font-family: Verdana, Arial, Helvetica, sans-serif;">
//SYSPRINT DD SYSOUT=*</div>
<div style="font-family: Verdana, Arial, Helvetica, sans-serif;">
//SYSOUT DD SYSOUT=</div>
</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
//sysin dd*</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
12</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
31</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
/*</div>
<div style="background-color: white; font-family: Verdana, Arial, Helvetica, sans-serif;">
//*</div>
<div style="background-color: white; font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;"><b>Result:</b></span></div>
<div style="background-color: white; font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;">12</span></div>
<div style="background-color: white; font-family: 'Default Sans Serif', Verdana, Arial, Helvetica, sans-serif;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;">31</span><br />
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;"><br /></span></div>
<div style="background-color: white;">
<span style="font-family: Verdana, Arial, Helvetica, sans-serif;"><span style="font-family: Default Sans Serif, Verdana, Arial, Helvetica, sans-serif;">If we do not have any data to be passed we can make SYSIN DD DUMMY. I tried passing blank values via </span>SYSIN. Was<span style="font-family: Default Sans Serif, Verdana, Arial, Helvetica, sans-serif;"> Expecting an abend, but it did not. It displayed spaces in SYSOUT.</span></span></div>
Anonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com6tag:blogger.com,1999:blog-2634839917427649118.post-13686481763520832592015-11-25T00:54:00.000+05:302016-02-20T13:34:29.743+05:30COBOL DB2 Program from scratch - step by step guide This post is just to help/guide to a new programmer in writing a cobol db2 program from scratch.<br />
Let us write a cobol program to read the EMPLOYEE table and get the details of the employee with name 'RYAN'.<br />
I am assuming the table is already created in the user database like below:.<br />
<br />
EMPLOYEE<br />
<span style="font-family: "verdana" , sans-serif;">EMPID EMPNAME DEPARTMENT SALARY DESIGNATION<br />1000 XXXXXXX XX 10000 SE<br />1001 YYYYYYY YY 9000 SE<br />1002 ZZZZZZZ ZZ 20000 TL</span><br />
<br />
<span style="color: #990000;"><b>STEP1</b></span>: We need to declare the Table structure in the Working Storage section. Ideally we should DCLGEN Tool to generate the structure for our DB2 table. The option to go to our DCLGEN tool depends on the ISPF settings. Generally it can be invoked using 'D' option on the ispf menu to display DB2I Default pannel.<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7NOpLQoR0guSdVWIBabdgNnyzuxrRdZ_DmobNPm7wh3xK5HTYub__3nWuEeeViaXD3W07Taubn1iCiQHMqiCYsbS-MQz87bIXoIqAn0qV2xtKioHx0xB-B3GbLqTz8rOXJQ5i6LKONi3m/s1600/dclgen+Screen.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="167" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7NOpLQoR0guSdVWIBabdgNnyzuxrRdZ_DmobNPm7wh3xK5HTYub__3nWuEeeViaXD3W07Taubn1iCiQHMqiCYsbS-MQz87bIXoIqAn0qV2xtKioHx0xB-B3GbLqTz8rOXJQ5i6LKONi3m/s1600/dclgen+Screen.png" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">DCLGEN Screen </td></tr>
</tbody></table>
On pressing ENTER, the DCLGEN will be generated and will look like below.<br />
<br />
<blockquote class="tr_bq">
<span style="color: #3d85c6;">*****************************************************************<br />EXEC SQL DECLARE DEV.EMPLOYEE TABLE<br /> ( EMPID CHAR(10) NOT NULL,<br /> EMPNAME CHAR(30) NOT NULL,<br /> DEPARTMENT CHAR(2) NOT NULL,<br /> SALARY DECIMAL(10,2) NOT NULL,<br /> DESIGNATION CHAR(4) NOT NULL )<br />)<br />END-EXEC.<br />*************** COBOL DECLARATION FOR TABLE DEV.EMPLOYEE *********<br />01 EMPOYEE-RECORD.<br /> 05 HV-EMPID PIC X(10).<br /> 05 HV-EMPNAME PIC X(30).<br /> 05 HV-DEPARTMENT PIC X(2).<br /> 05 HV-SALARY PIC S9(8)V99 COMP-3.<br /> 05 HV-DESIGNATION PIC CHAR(4).<br />*********** THE NUMBER OF COLUMNS IN THIS DECLARATION IS 5 *****</span></blockquote>
This DCLGEN needs to be included into the Working Storage Section of our cobol program in the following way:<br />
<blockquote class="tr_bq">
<span style="color: #3d85c6;">EXEC SQL<br />INCLUDE EMPLOYEE<br />END-EXEC.</span></blockquote>
Also, the most important copybook SQLCA needs to be included . Apart from this we wont be able to capture the SQL Return codes<br />
<br />
<blockquote class="tr_bq">
<span style="color: #3d85c6;">EXEC SQL<br />INCLUDE SQLCA<br />END-EXEC.</span></blockquote>
Also since our query in the program might return more than single row, we need cursors in our program. Read About cursor programming <a href="http://mframes.blogspot.in/2014/05/normal-0-false-false-false.html" target="_blank">here</a><br />
I am not going into the details of cursor programming here, since those are there in other posts.<br />
Once the program is ready and compiled , we need to bind it to a plan in the test region. Once the bind is successful, we can run the program using the IKJEFT01 utility as below.<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqPDX7FA2jSSEo0bbB6fwe-IgRKiOQ108AZ3WxVWMm3uixA3dDzfLg6tzd-WMJaJzVisp0EpBqLRiswUIwshyphenhyphenvxJT_dA784qCh1dKlQnCBRsHQYyAbqxzxH2fffzdBhXX2oyJe_Al7-yDq/s1600/run+JCL.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="248" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqPDX7FA2jSSEo0bbB6fwe-IgRKiOQ108AZ3WxVWMm3uixA3dDzfLg6tzd-WMJaJzVisp0EpBqLRiswUIwshyphenhyphenvxJT_dA784qCh1dKlQnCBRsHQYyAbqxzxH2fffzdBhXX2oyJe_Al7-yDq/s1600/run+JCL.png" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">cobol db2 run jcl</td></tr>
</tbody></table>
<br />Anonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com6tag:blogger.com,1999:blog-2634839917427649118.post-15688022399470550042015-11-18T01:52:00.003+05:302015-11-18T23:24:58.502+05:30Frequently used CA7 Commands<div class="tr_bq">
To Define Jobs in CA7:</div>
Each Job in CA7 must first be defined in its database; the easiest way to do this is through the command '<span style="color: #990000;">JOB</span>'. Just type the command <span style="color: #990000;">JOB</span> or '<span style="color: #990000;">DB'</span> in the CA7 Scheduler and it will show the list of available options we have to add the jobs.<br />
<br />
Lets have look into some of the day-to-day commands which we use frequently to check the job schedules and other details.<br />
<br />
1. LJOB,JOB=jobname,LIST=ALL<br />
It will show you all the details of the job once you press enter.<br />
<br />
We can use the below values with the LIST parameter<br />
LIST=NODD. It will show the TRIGGERED BY JOS and SUCCESSOR jobs<br />
LIST=RQMT to get the Requirement and Network connections<br />
LIST=SCHD to know many sch id are assigned to a particular job using the below command<br />
LIST=TRIG to know only the triggering jobs<br />
<br />
2. Get the list of jobs in tree structure. Use the command<br />
FRJOB,JOB=jobname<br />
<br />
3. Get the tree structure of the downstream job. Use the command<br />
FSTRUC,JOB=jobname<br />
<br />
4. Get the JCL in the pannel using the below command<br />
LJCL,JOB=jobname<br />
<br />
5.List the active jobs using the command<br />
LACT<br />
<br />
6. Want to see the member names in any PDS. Use the below command<br />
LISTDIR,DSN=pds-nameAnonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com4tag:blogger.com,1999:blog-2634839917427649118.post-71586674476719526682015-11-18T01:52:00.002+05:302016-01-01T23:30:32.401+05:30CA7 Guide for beginner's<span style="background-color: white;"><span style="font-family: inherit;">CA7 is a product of Computer Associates and is a scheduling tool for batch jobs in mainframe.When the number of jobs to be executed is huge in number and jobs need to be executed depending on several parameters (like time dependency, availability of data sets from other jobs, </span>successful<span style="font-family: inherit;"> completion of other jobs), a tool like CA7 comes into play. </span></span><br />
Once a job is scheduled /triggered or manually added in CA7, it will start a process of moving through various QUEUES in CA7. CA7 will manage it until its completion.<br />
Below is the diagram which shows in brief the lifecyle of the job inside a CA7 Scheduler.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgb0v50mXUf4dPk5LXNwNAH89yMuCwfZs2FBYdrqinHw2q44G2Q-BwhWnuV2wvF4Q0upvkQ5hKkmsx2kfKkpc0y_2uSLx0KNf9aWm75RO6PJ6VNCrHXQbDXaNTed_Duqn4y2fdvz5vEooyb/s1600/wp_20151118_00_36_35_pro.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="222" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgb0v50mXUf4dPk5LXNwNAH89yMuCwfZs2FBYdrqinHw2q44G2Q-BwhWnuV2wvF4Q0upvkQ5hKkmsx2kfKkpc0y_2uSLx0KNf9aWm75RO6PJ6VNCrHXQbDXaNTed_Duqn4y2fdvz5vEooyb/s400/wp_20151118_00_36_35_pro.jpg" width="400" /></a></div>
<br />
Lets have a quick look into the Queues:<br />
<b><span style="color: #660000;">Request Queue</span></b>: When a job enters CA7, it will reside in the request queue until all its requirements have been satisfied. Examples of requirements can be cited like :<br />
Predecessor job<br />
User holds<br />
Submit time<br />
Data set requirements<br />
<br />
<b><span style="color: #660000;">Ready Queue</span></b>: When a job's requirement have been satisfied, it will move to the ready queue. It will wait until initiators are available and any virtual resource or workload manager requirement is satisfied to enable it to execute.<br />
<br />
<b><span style="color: #660000;">Active Queue</span></b>: When job can run it is moved to the active Q where it will remain whilst the job is executing within JES2. Once the job has completed successfully on JES2 the job will be marked completed within CA7 and is removed from the active Q.<br />
<br />
<b><span style="color: #660000;">Failure list</span></b>: If the job fails the it will be moved back to the requirement queue but will need correction prior to rerun. JCL errors are returned to ready Q and other failures are moved to request Q.<br />
<br />
<b><span style="color: #660000;">How to login to CA7.</span></b><br />
Differnet systems use different methods to get into CA7. Some system allow the users to access the CA7 from ISPF menu while others use varied methods. Once we go inside the interface all remains the same.<br />
<br />
<b>Refer to This link for list of <i><a href="http://mframes.blogspot.com/2015/11/frequently-used-ca7-commands.html" target="_blank">CA7 Commands</a></i>.</b>Anonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com19tag:blogger.com,1999:blog-2634839917427649118.post-13317652867880711632015-10-29T23:08:00.000+05:302015-11-09T23:00:07.185+05:30REUSE option in VSAM and IDCAM. Drawbacks of Reuse.I have seen the REUSE parameter in the REPRO statement using IDCAMS. Backtracking the purpose of using it, came to know several features of this keyword which i am sharing here.<br />
Some applications might need temporary files, which can be deleted after the program run is over.You may need these files in the form of a KSDS,ESDS or RRDS. Thus VSAM allows to create a Reusable cluster or files using this keyword in the definition of the cluster.<br />
<br />
<b><span style="color: #660000;">Lets see what happens if we do not use the REUSE parameter ? </span></b><br />
In this case, we can reload the VSAM only once.Only way to reload the data is to DELETE DEFINE the vsam file and repro the data.Should we reload it again, we need to delete/define it again. In order to remove this repeated DELETE DEFINE steps, we can define the vsam with REUSE parameter.<br />
Do we remember the term HURBA ? Check the <a href="http://mframes.blogspot.in/2014/08/reading-listcat-output-in-vsam.html" target="_blank">listcat</a> section once how we can know about it.<br />
HURBA stands for --> <b>H</b>ighly <b>U</b>sed <b>R</b>elative <b>B</b>yte <b>A</b>rea.<br />
When we delete define a VSAM, this HURBA remains as zero. It increments as we add records. Ideally this HURBA indicates the offset of the last byte in the data set.<br />
When we use reuse option, this HURBA is reset back to zero. Logically we are deleting all the records thereby.<br />
Thus,the REUSE parameter allows us to reload the vsam as if it were empty.<br />
<br />
<b><span style="color: #660000;">Although it sounds simple, but there are certain drawbacks for using REUSE parameter.</span></b><br />
<br />
We know VSAM performance depends on the CI/CA splits. With this resue option, the CI/CA splits remain in place. Over the period of time the performance degrades.<br />
<br />
Few points to remember :<br />
1. We cant use REUSE in the repro when the cluset is not defined with REUSE option.<br />
However we can Alter the VSAM definition to make it resuable.<br />
<br />
<pre class="pre codeblock" style="background-color: white; font-size: 12px;"><code> //STEPA EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=A
//SYSIN DD *
ALTER -
TEST.VSAMDATASET -
REUSE</code></pre>
2. Cluster defined with Reuse keyword can not have alternate index defined on it.<br />
<br />Anonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com4tag:blogger.com,1999:blog-2634839917427649118.post-1277060215840225152015-09-26T10:44:00.000+05:302015-09-26T10:53:39.138+05:30Including Date field in the output file using SORTIncluding Date field in the output file:<br />
<br />
Many a times it is required to include the date in the output file. This can be done using DATE parameter in SORT.<br />
There are 3 DATE parameter option available, DATEn, DATEn(c) and DATEnP where n=1, 2 or 3.<br />
<br />
Consider the input file INFILE,<br />
1111111111111111111111111111<br />
1111111111111111111111111111<br />
1211111111111111111111111111<br />
1311111111111111111111111111<br />
1411111111111111111111111111<br />
1511111111111111111111111111<br />
1611111111111111111111111111<br />
1711111111111111111111111111<br />
1811111111111111111111111111<br />
1911111111111111111111111111<br />
2011111111111111111111111111<br />
2111111111111111111111111111<br />
The output date is in Zoned Decimal format.<br />
The DATE1 occupies 10 bytes and gives the date in YYYYMMDD format.<br />
The DATE2 occupies 6 bytes and gives the date in YYYYMM format.<br />
The DATE3 occupies 7 bytes and gives year and Julian date (JDT) as YYYYJDT format.<br />
The JCL’s below show the use of DATEn parameter. The current date August 26, 2010(Julian Date 238)<br />
<blockquote class="tr_bq">
//STEP01 EXEC PGM=SORT<br />//SORTIN DD DSN=INFILE,DISP=SHR<br />//SORTOUT DD DSN=OUTFILE1,DISP=SHR<br />//SYSIN DD *<br />SORT FIELDS=COPY<br />OUTREC FIELDS=(1,29,&DATE1)<br />//</blockquote>
<br />
The contents of OUTFILE are as below,<br />
OUTFILE:<br />
1111111111111111111111111111 20100826<br />
1111111111111111111111111111 20100826<br />
1211111111111111111111111111 20100826<br />
............<br />
<br />
On using DATE1 we have the current date in YYYYMMDD format.<br />
<blockquote class="tr_bq">
//STEP01 EXEC PGM=SORT<br />//SORTIN DD DSN=INFILE,DISP=SHR<br />//SORTOUT DD DSN=OUTFILE2,DISP=SHR<br />//SYSIN DD *<br />SORT FIELDS=COPY<br />OUTREC FIELDS=(1,29,&DATE2)<br />//</blockquote>
The contents of OUTFILE2 are as below,<br />
1111111111111111111111111111 201008<br />
1111111111111111111111111111 201008<br />
1211111111111111111111111111 201008<br />
.........<br />
On using DATE2 we have the output date in YYYYJDT format<br />
<br />
<blockquote class="tr_bq">
//STEP01 EXEC PGM=SORT<br />//SORTIN DD DSN=INFILE,DISP=SHR<br />//SORTOUT DD DSN=OUTFILE2,DISP=SHR<br />//SYSIN DD *<br />SORT FIELDS=COPY<br />OUTREC FIELDS=(1,29,&DATE3)<br />//</blockquote>
The contents of the OUTFILE2 is as below,<br />
1111111111111111111111111111 2010238<br />
<br />
Using the DATEn(c) parameter:<br />
On using the DATEn(C) parameter, the output date appears in formatted way wherein a character ‘/’ is placed between the year month and date fields.<br />
DATE1(c) occupies 10 bytes and the format is YYYY/MM/DD.<br />
DATE2(c) occupies 7 bytes and the format is YYYY/MM.<br />
DATE3(c) occupies 7 bytes and the format is YYYY/JDT.<br />
<br />
Using the DATEnP parameter:<br />
On using DATEnP the output appears in Packed decimal format. So the number of bytes occupied is lesser than DATEn parameter. Other than this there is no difference between DATEn and DATEnP.<br />
The DATE1 occupies 5 bytes and gives the date in YYYYMMDD format.<br />
The DATE2 occupies 4 bytes and gives the date in YYYYMM format.<br />
The DATE3 occupies 4 bytes and gives year and Julian date (JDT) as YYYYJDT format.<br />
1111111111111111111111111111 2010238<br />
<br />
<br />
Retrieving Information on records having older dates:<br />
Consider a case wherein we need to retrieve records that have yesterday’s date.<br />
The JCL is as below,<br />
<blockquote class="tr_bq">
//STEP01 EXEC PGM=SORT<br />//SORTIN DD DSN=INFILE1,DISP=SHR<br />//SORTOUT DD DSN=OUTFILE3,DISP=SHR<br />//SYSIN DD *<br />SORT FIELDS=COPY<br />INCLUDE COND=(30,8,CH,EQ,&DATE1-1)<br />OUTREC FIELDS=(1,40)<br />//</blockquote>
Anonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com0tag:blogger.com,1999:blog-2634839917427649118.post-34195801232730584842015-09-03T00:41:00.000+05:302015-09-16T00:55:46.642+05:30Splitting Input Files using Sort. Use of SPLIT ,SPLITBY,SPLIT1R commmandsSPLIT command spits the output records one record at a time among output datasets. This happens until all the output records are written. The split happens in rotation among the datasets mentioned in the OUTFIL.<br />
The First record from the output records is written to first dataset mentioned in the OUTFIL group, the Second record from the output records gets written to the second dataset mentioned in the OUTFIL group and so on.<br />
When each OUTFIL dataset has 1 record, the rotation starts again with the dataset mentioned first in the OUTFIL group.<br />
The records are not contiguous in the OUTFIL datasets.<br />
The Below JCL splits the data in INFILE and copies to OUTFILE1 and OUTFILE2 as mentioned above.<br />
<div>
<br /></div>
Consider the contents of Input File - INFILE as below:<br />
<br />
1111111111111111111111111111<br />
1211111111111111111111111111<br />
1311111111111111111111111111<br />
1411111111111111111111111111<br />
1511111111111111111111111111<br />
1611111111111111111111111111<br />
1711111111111111111111111111<br />
1811111111111111111111111111<br />
1911111111111111111111111111<br />
2011111111111111111111111111<br />
2111111111111111111111111111<br />
Let us use the commands and see the outputs.<br />
<br />
The Below JCL splits the data in INFILE and copies to OUTFILE1 and OUTFILE2 as mentioned above.<br />
<blockquote class="tr_bq">
//STEP01 EXEC PGM=SORT<br />
//SORTIN DD DSN=INFILE,DISP=SHR<br />
//SORTOUT1 DD DSN=OUTFILE1,DISP=SHR<br />
//SORTOUT2 DD DSN=OUTFILE2,DISP=SHR<br />
//SYSIN DD *<br />
SORT FIELDS=COPY<br />
OUTFIL FNAMES=(SORTOUT1,SORTOUT2),SPLIT<br />
/*</blockquote>
<br />
The contents of OUTFILE1 and OUTFILE2 would be as below,<br />
<span style="color: #0b5394;"><b>OUTFILE1</b></span><br />
1111111111111111111111111111<br />
1311111111111111111111111111<br />
1511111111111111111111111111<br />
1711111111111111111111111111<br />
1911111111111111111111111111<br />
2111111111111111111111111111<br />
<b><span style="color: #0b5394;">OUTFILE2</span></b><br />
1211111111111111111111111111<br />
1411111111111111111111111111<br />
1611111111111111111111111111<br />
1811111111111111111111111111<br />
2011111111111111111111111111<br />
OUTFILE1 dataset contains records 1, 3, 5…so on.<br />
OUTFILE2 dataset contains records 2, 4, 6…so on.<br />
Note that the records in the output datasets are not contiguous.<br />
<br />
<b><span style="color: #990000;">SPLITBY Command:</span></b><br />
<b><span style="color: #990000;"><br /></span></b>
SPLITBY splits the output records M records at a time in rotation among the datasets mentioned in the OUTFIL. This happens until all the output records are written.<br />
The First Set of records from the output records gets written to first dataset mentioned in the OUTFIL group, the Second Set of records from the output records gets written to the second dataset mentioned in the OUTFIL group and so on.<br />
When each OUTFIL dataset has the specified set of records, the rotation starts again with the dataset mentioned first in the OUTFIL group.<br />
The syntax is SPLITBY=M, where M=1,2,3…so on<br />
The records are not contiguous in the OUTFIL datasets.<br />
SPLITBY=1 is equivalent to SPLIT.<br />
The below JCL splits the data in INFILE and copies to OUTFILE3 and OUTFILE4 as mentioned above.<br />
<blockquote class="tr_bq">
//STEP01 EXEC PGM=SORT<br />
//SORTIN DD DSN=INFILE,DISP=SHR<br />
//SORTOUT1 DD DSN=OUTFILE3,DISP=SHR<br />
//SORTOUT2 DD DSN=OUTFILE4,DISP=SHR<br />
//SYSIN DD *<br />
SORT FIELDS=COPY<br />
OUTFIL FNAMES=(SORTOUT1,SORTOUT2),SPLITBY=3<br />
/*</blockquote>
The contents of OUTFILE3 and OUTFILE4 would be as below,<br />
<b><span style="color: #0b5394;">OUTFILE3</span></b><br />
1111111111111111111111111111<br />
1211111111111111111111111111<br />
1311111111111111111111111111<br />
1711111111111111111111111111<br />
1811111111111111111111111111<br />
1911111111111111111111111111<br />
<b><span style="color: #0b5394;">OUTFILE4</span></b><br />
1411111111111111111111111111<br />
1511111111111111111111111111<br />
1611111111111111111111111111<br />
2011111111111111111111111111<br />
2111111111111111111111111111<br />
OUTFILE3 contains records (1, 2, 3), (7, 8, 9).<br />
OUTFILE4 contains records (4, 5, 6), (10, 11).<br />
Note that the records in the output datasets are not contiguous.<br />
<br />
SPLIT1R splits output records M records at a time in one rotation among the datasets mentioned in the OUTFIL. This happens until all the records are written. In SPLIT1R the rotation happens only once among the OUTFIL datasets.<br />
If on reaching the last OUTFIL, more than M records from the output records is left, all of those would be move to last OUTFIL.<br />
If the input has only M records, then all input records will get moved to the first OUTFIL. The remaining OUTFIL datasets will be empty.<br />
The syntax is SPLIT1R=M, where M=1, 2, 3…so on.<br />
The records are contiguous among the OUTFIL datasets.<br />
The below JCL’s splits the data in INFILE,<br />
JCL1:<br />
<blockquote class="tr_bq">
//STEP01 EXEC PGM=SORT<br />
//SORTIN DD DSN=INFILE,DISP=SHR<br />
//SORTOUT1 DD DSN=OUTFILE5,DISP=SHR<br />
//SORTOUT2 DD DSN=OUTFILE6,DISP=SHR<br />
//SYSIN DD *<br />
SORT FIELDS=COPY<br />
OUTFIL FNAMES=(SORTOUT1,SORTOUT2),SPLIT1R=5</blockquote>
<br />
The output files contents are shown below,<br />
<span style="color: #0b5394;"><b>OUTFILE5</b></span>:<br />
1111111111111111111111111111<br />
1211111111111111111111111111<br />
1311111111111111111111111111<br />
1411111111111111111111111111<br />
1511111111111111111111111111<br />
<b><span style="color: #0b5394;">OUTFILE6</span></b>:<br />
1611111111111111111111111111<br />
1711111111111111111111111111<br />
1811111111111111111111111111<br />
1911111111111111111111111111<br />
2011111111111111111111111111<br />
2111111111111111111111111111<br />
There are two output files, and M=5. The input INFILE contains 11 records.<br />
The OUTFILE5 contains records 1, 2, 3, 4, 5.<br />
The dataset OUTFILE6 contains records 6, 7, 8, 9, 10, 11(i. e all the remaining records)<br />
<br />
JCL2:<br />
<br />
<blockquote class="tr_bq">
//STEP01 EXEC PGM=SORT<br />
//SORTIN DD DSN=INFILE,DISP=SHR<br />
//SORTOUT1 DD DSN=OUTFILE7,DISP=SHR<br />
//SORTOUT2 DD DSN=OUTFILE8,DISP=SHR<br />
//SYSIN DD *<br />
SORT FIELDS=COPY<br />
OUTFIL FNAMES=(SORTOUT1,SORTOUT2),SPLIT1R=11<br />
//</blockquote>
The output file contents are shown below:<br />
<br />
<b><span style="color: #0b5394;">OUTFILE7</span></b>:<br />
1111111111111111111111111111<br />
1211111111111111111111111111<br />
1311111111111111111111111111<br />
1411111111111111111111111111<br />
1511111111111111111111111111<br />
1611111111111111111111111111<br />
1711111111111111111111111111<br />
1811111111111111111111111111<br />
1911111111111111111111111111<br />
2011111111111111111111111111<br />
2111111111111111111111111111<br />
<br />
<b><span style="color: #0b5394;">OUTFILE8</span></b><br />
empty as expected.Anonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com0tag:blogger.com,1999:blog-2634839917427649118.post-84281635316335802082015-09-03T00:08:00.001+05:302015-09-03T23:32:57.919+05:30Convert VB file to FB and Convert FB file to VB using SORTThe below JCL copies the VB file to FB file.<br />
<br />
<blockquote class="tr_bq">
//STEP01 EXEC PGM=SORT<br />//SORTIN DD DSN=INPUTVBFILE,DISP=SHR<br />//SORTOUT DD DSN=OUTPUTFBFILE,DISP=SHR<br />//*<br />//SORTWK01 DD SPACE=(CYL,10),UNIT=SYSDA<br />//SYSOUT DD SYSOUT=*<br />//SYSIN DD *<br />SORT FIELDS=(5,76,CH,A)<br />OUTFIL FNAMES=SORTOUT,VTOF,BUILD=(5,76)<br />/*</blockquote>
<br />
The INPUTVBFILE is a VB file with record length 80.<br />
The OUTPUTFFBFILE is a FB file of record length 76.<br />
Before executing the JCL it is assumed that both the SORTIN and SORTOUT datasets exists.<br />
SORT FIELDS=(5,76,CH,A) sorts the input VB file.<br />
VTOF will handle copying the VB file to FB file.<br />
It is essential to give BUILD or OUTREC parameter when VTOF parameter is used.<br />
<br />
<b><span style="color: #990000;">Below JCL will Convert FB file to VB</span></b><br />
<br />
<blockquote class="tr_bq">
//STEP01 EXEC PGM=SORT<br />//SORTIN DD DSN=INPUTFBFILE,DISP=SHR<br />//SORTOF01 DD DSN=OUTPUTVBFILE,<br />// DISP=(NEW,CATLG,DELETE),<br />// UNIT=SYSDA,<br />// DCB=(LRECL=80,RECFM=VB,BLKSIZE=84),<br />// SPACE=(TRK,(3000,2000),RLSE)<br />//SYSIN DD *<br />SORT FIELDS=COPY<br />OUTFIL FNAMES=SORTOF01,FTOV</blockquote>
<br />
It is not essential to give BUILD or OUTREC parameter when FTOV parameter is used.Anonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com1tag:blogger.com,1999:blog-2634839917427649118.post-42886864392319219952015-08-04T00:55:00.000+05:302016-02-20T13:51:37.164+05:30Can we delay the execution of a mainframe job?OPSWAIT is an option<div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;">Have you ever tried to keep a job or certain steps in a job to wait for sometime and then execute without using any scheduler ?</span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;"><br /></span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;">When would it be useful to ask mainframes to sleep like this?</span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;"><br /></span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;">Few days back ,came across a specific scenario when it was needed to put a job step on hold for sometime and then execute it.</span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;"><br /></span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;">Say for unit testing you want to execute two jobs in certain instance to check dataset conflict or table conflict.</span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;">Suppose we have a job with 3 steps. We want to execute step1 an then wait for sometime, say 1 minute, and then execute the remaining 2 steps. How do we do it ?</span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;"><br /></span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;">OPSWAIT is the option which can help us in this scenario. (We need CA tool to be installed in our environment).</span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;">The OPSWAIT function suspends processing of the program or rule for a specified period in an OPS/REXX program or REQ rule.This function can be used in OPS/REXX, AOF rules, TSO/E REXX, or CLIST.</span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;"><br /></span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;">//Jobcard..</span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;">//PSTEP01 EXEC PGM=TSTPGM</span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;">...</span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;">//PSTEP02 EXEC PGM=OPSWAIT,PARM='FOR(01:00)'</span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;">//PSTEP03 EXEC PGM=TSTPGM2</span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;">....</span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;">Once you submit this JCL, it till execute the step PSTEP01,then it will cause the mainframe to sleep for 1 min and then execute step 2.</span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;">We can try this command going into TSO command (option 6) and typing OPSWAIT 01:00.(01=mins,00=seconds)</span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;"><br /></span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;">We can also use this JCL to put the program on hold.</span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;">//SLEEP EXEC PGM=IKJEFT01 </span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;">//SYSPRINT DD SYSOUT=* </span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;">//SYSTSPRT DD SYSOUT=* </span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;">//SYSTSIN DD * </span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;">OPSWAIT FOR(01:10) </span></div>
<div>
<span style="font-family: "verdana" , "arial" , "helvetica" , sans-serif;">//* </span></div>
</div>
Anonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com4tag:blogger.com,1999:blog-2634839917427649118.post-44966172870293451452015-08-01T15:34:00.000+05:302015-08-01T21:59:32.636+05:30Date functions in cobol. Why and when do we use IGZEDT4 ,CEELOCT , CURRENT-DATE ?We come across certain instance when we need to get the time/date in certain formats in cobol.<br />
We all know that we can get the dates in cobol using ACCEPT verb in the following way.<br />
ACCEPT WS-DATE FROM DATE<br />
But we would not get it in the format we prefer to use ,say in the format YYYYMMDD.<br />
DATE has the picture clause of PIC 9(6). So when we use the above cobol statement, we would get the date in the format like below when read sequentially from left to right:<br />
<span style="color: blue;">First 2 digits for the year of the century, next 2 for the month and next 2 digits with the date.</span><br />
Example: This if the current date is 25-JUL-1994, we would get as 940725.<br />
<br />
VS cobol II does not support the date format giving results in YYYY format. However the results can be interpolated in many ways to get our desired result.Some of the utilities are described below.<br />
<br />
<b><u>For NON LE Cobol Environment </u></b><br />
<b>1</b>.IBM has offered a non-LE callable interface known as <span style="color: #990000;"><b>IGZEDT4</b></span> which can be used to achieve our<br />
desired function. When we call it , it will give the desired result in the YYYYMMDD format.<br />
<br />
<blockquote class="tr_bq">
01 WS-YYYYMMDD PIC 9(08).<br />
01 WS-DATE-IGZEDT4 PIC X(08) VALUE 'IGZEDT4'.<br />
*<br />
CALL WS-IGZEDT4 USING WS-YYYYMMDD</blockquote>
<br />
<b>2</b>. Another way to do this is to use the LE callable function <b><span style="color: #990000;">CEELOCT</span></b>. We need to check with the mainframe infrastructure team to know if LE is optionally installed in our LPAR. If yes, then we can call this function which returns the result in the format of YYYYMMDD. CEELOCT returns the result in 3 formats.<br />
a: Lilian Date<br />
b.Lilian Seconds<br />
c. Gregorian character strings.<br />
It also returns the feedback code(CEE000 if call is successful) which decides whether the function executed successfully or not.<br />
<br />
<blockquote class="tr_bq">
<span style="color: red;">WORKING-STORAGE SECTION</span>.<br />
01 WS-LILIAN PIC S9(9) COMP.<br />
01 WS-XSECONDS PIC S9(18) COMP.<br />
01 WS-GREGORN PIC X(17).<br />
01 WS-FC.<br />
03 CEEIGZCT-RC PIC X.<br />
88 CEE000 VALUE LOW-VALUE.<br />
<span style="color: red;">PROCEDURE DIVISION</span>.<br />
MAIN-SECTION.<br />
CALL 'CEELOCT' USING WS-LILIAN<br />
WS-XSECONDS<br />
WS-GREGORN<br />
WS-FC<br />
IF CEE000 OF WS-FC<br />
DISPLAY 'Time: ' WS-GREGORN<br />
ELSE<br />
DISPLAY 'CEELOCT error' UPON CONSOLE<br />
END-IF</blockquote>
<br />
<b><u>For LE enabled environment</u></b><br />
<b><u><br /></u></b>
<b>3</b>. <b><span style="color: #990000;">CURRENT-DATE </span></b> FUNCTION has been introduced after cobol II, which makes life much simpler. It is a 21 byte alphanumeric value which contains the below fields when read from left to right .<br />
<br />
<blockquote class="tr_bq">
01 WS-CURRENT-DATE-FIELDS.<br />
05 WS-CURRENT-DATE.<br />
10 WS-CURRENT-YEAR PIC 9(4).<br />
10 WS-CURRENT-MONTH PIC 9(2).<br />
10 WS-CURRENT-DAY PIC 9(2).<br />
05 WS-CURRENT-TIME.<br />
10 WS-CURRENT-HOUR PIC 9(2).<br />
10 WS-CURRENT-MINUTE PIC 9(2).<br />
10 WS-CURRENT-SECOND PIC 9(2).<br />
10 WS-CURRENT-MS PIC 9(2).<br />
05 WS-DIFF-FROM-GMT PIC S9(4).</blockquote>
We need to use the cobol code like below and use the appropriate values as per our requirement.<br />
<blockquote class="tr_bq">
MOVE FUNCTION CURRENT-DATE TO WS-CURRENT-DATE-FIELDS</blockquote>
Anonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com0tag:blogger.com,1999:blog-2634839917427649118.post-65730150550858873572015-07-01T01:32:00.003+05:302015-07-01T23:25:47.626+05:30What is SMS in mainframe ? SMS stands for Storage Management Subsystem <b><span style="color: blue;">Introduction:</span></b><br />
The Storage Management Subsystem (SMS) automates the use of storage for data
sets. The z/OS , system is not aware of how much space a dataset will need, where it will be stored(Disk or Tape) ,what will be its record format and other details. Z/oS storage team need to decide whether to backup the large files and also need to recall it when necessary. All these data management activities can be done either manually or
through the use of automated processes which is nothing but <b><span style="color: #990000;">SMS</span></b>. With SMS activated , the z/OS programmer
or storage admin team may, for example, create model data definitions for typical data
sets, so that SMS automatically assigns those attributes to data sets when they are created.<br />
The
data sets allocated through SMS are called <span style="color: #990000;">system-managed data sets or SMS-managed
data sets</span>.
For example, suppose you want to create a new data set named DATA.LIST. If SMS is active,
you could use JCL like this:<br />
<blockquote class="tr_bq">
//NEWDS DD DSN=myid.test.dataset1,<br />
// DISP=(NEW,CATLG),<br />
// DATACLAS=DCSL002,<br />
// STORCLAS=SRTCL00</blockquote>
<br />
In this case, z/OS can use characteristics from predefined data and storage classes when it
creates the DATA.LIST data set.<br />
<br />
If SMS is <span style="color: #cc0000;">not active or not in use,</span> you need to manually specify the space requirements and
storage location for the new data set, and your JCL would look like this :<br />
<blockquote class="tr_bq">
//NEWDS DD DSN=myid.test.dataset1,<br />
// DISP=(NEW,KEEP),<br />
// SPACE=(CYL,(1,1)),<br />
// UNIT=SYSDA,<br />
// VOL=SER=SHARED </blockquote>
<br />
<b><span style="color: blue;">The advantages associated with an SMS environment.</span></b><br />
• The user is relieved of making decisions about resource allocation of data sets,
since it is done by SMS.<br />
• SMS provides the capability of concatenating data sets of unlike devices. For
example, a tape data set can be concatenated with a disk data set. This capability is
not available in a non-SMS environment.<br />
• SMS managed data sets cannot be deleted unless they are first uncataloged. Due
to this extra step, erroneous deletion of data sets is minimized.<br />
• Additional features are available in the use of IDCAMS in an SMS environment. For
example, the ALTER command can be used to increase the limit of the number of
generation data sets in a GDG. This feature is not available in a non-SMS system.<br />
• VSAM data sets created in an SMS environment offer more flexibility that those
created through JCL in a non-SMS environment.<br />
<br />
Read about the <a href="http://mframes.blogspot.com/2015/07/an-overview-of-sms-parameters.html" target="_blank">SMS Parameters</a> in the next postAnonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com0tag:blogger.com,1999:blog-2634839917427649118.post-85986687048928594642015-07-01T01:32:00.002+05:302015-07-01T01:32:32.105+05:30An overview of SMS parameters: STORCLAS,DATACLAS,MGMTCLAS,RECORG<b><span style="color: blue;">1. <u>The STORCLAS Parameter:</u></span></b><br />
STORCLAS is a keyword parameter. It is used to assign a data set to an SMS defined
class.<span style="color: #cc0000;"> </span><br />
<span style="color: #444444;"><b>These parameters discused below have significance only if SMS is active, otherwise it is ignored. </b></span><br />
<span style="color: #cc0000;"><b>Here’s
the syntax: </b></span><br />
<span style="color: #cc0000;"><b>STORCLAS = class</b></span><br />
Where class is an installation defined name and can be one to eight characters long.
Use of this parameter results in the data set defined in the DD statement within that job
being SMS-managed.
The VOLUME and UNIT parameters can be omitted, since these values are now SMS
supplied.<br />
As we can see in the below example, VOLUME and UNIT
parameters are omitted. These parameters will be installation defined for the class SMS1.<br />
<blockquote class="tr_bq">
//JOB1 JOB (A123), ‘Ryan’<br />
//STEP1 EXEC PGM=PROGRAM1<br />
//DD1 DD DSN=myid.test.dataset1,<br />
// DISP=(NEW, CATLG, DELETE),<br />
// SPACE=(CYL, (1,1), RLSE),<br />
// LRECL=80,<br />
// RECFM=FB,<br />
// STORCLAS=SMS1 </blockquote>
<br />
<b><span style="color: blue;">2. <u>The DATACLAS Parameter:</u></span></b><br />
DATACLAS is a keyword parameter. It is used to define any or all of the following
parameters for a data set.(Marked in blue)<br />
<span style="color: blue;">LRECL, RECORG , RECFM, RETPD or EXPDT ,VOLCOUNT (coded on the VOL parameter) , </span><br />
<span style="color: blue;">SPACE , AVGREC</span><br />
Volcount is coded on VOL parameter. It is used to specify the number of tape
volumes that can be mounted when a tape data set is being created or expanded.<br />
For SMS managed <b>VSAM </b>data sets, DATACLAS can be used to define the following
parameters:
<span style="color: blue;">CISIZE, IMBED ,REPLICATE, SHAREOPTIONS ,FREESPACE </span><br />
<span style="color: blue;"><br /></span>
Here’s the syntax of the DATACLAS parameter:<br />
<b><span style="color: #cc0000;">DATACLAS = class</span></b><br />
Class is installation defined with predefined values for any of the above parameters<br />
<br />
Suppose a class named GENERAL contains the following definitions:<br />
SPACE = (CYL, (1,1), RLSE),<br />
DCB= (RECFM=FB, LRECL=80)<br />
My job uses the Class GENERAL in JCL<br />
<br />
<blockquote class="tr_bq">
//JOB1 JOB (A123), ‘Ryan’
/<br />
/STEP1 EXEC PGM=PROGRAM1<br />
//DD1 DD DSN=myid.test.dataset1,<br />
// DISP=NEW, CATLG, DELETE),<br />
// UNIT=SYSDA,<br />
// DATACLAS=GENERAL </blockquote>
<b><u><span style="color: blue;"><br /></span></u></b>
<b><span style="color: blue;">3. <u>The MGMTCLAS Parameter </u></span></b><br />
This parameter is used to provide a management class for the associated data set coded in
the DD statement. A management class is used to control the migration of data sets, the
frequency of back ups, the number of backups versions, and the retention criteria of
backup versions.<br />
Here’s the syntax:<br />
<span style="color: #990000;"><b>MGMTCLAS = class</b></span><br />
It can be one to eight characters long. Parameters defined for this class
can not be overridden. Example:<br />
//JOB1 JOB (A123), 'Ryan’<br />
//STEP1 EXEC PGM=PROGRAM1<br />
//DD1 DD DSN=myid.test.dataset2<br />
// DISP=(NEW, CATLG, DELETE),<br />
// DATACLAS=GENERAL,<br />
// MGMTCLAS=ARCHIVE
<br />
<br />
<b><span style="color: blue;">4. <u>The LIKE Parameter:</u></span></b><br />
This parameter is used to copy attributes from an existing cataloged data set to a new data
set. The following attributes can be copied over:
SPACE
RECFM
AVGREC
LRECL<br />
<br />
Here’s the syntax:<br />
LIKE = model.dataset<br />
Where model.dataset identifies the name of the model data set.<br />
Example:<br />
<br />
<blockquote class="tr_bq">
//JOB1 JOB (A123), ‘Ryan’<br />
//STEP1 EXEC PGM=PROGRAM1<br />
//DD1 DD DSN=myid.test.dataset3,<br />
// DISP=(NEW, CATLG, DELETE),<br />
// LIKE=TEST.MODEL</blockquote>
In this example, the attributes of the data set TEST.MODEL is copied to the new data
set called TEST.DATA2. Anonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com0tag:blogger.com,1999:blog-2634839917427649118.post-42290258844965776032015-06-24T01:10:00.002+05:302015-06-24T22:48:16.579+05:30Identity Column in DB2In order to maintain uniqueness in the values entered in db2, we use the UNIQUE constraint for one of the column in db 2. Creation of unique index, primary key are also there in the list by which we can maintain data uniqueness. However there are methods to generate unique values in the columns.<br />
<br />
1. Creating IDENTITY column while defining the table (introduced in DB2 version 7.1 and later)<br />
2. SEQUENCE (from DB2 version 7.2 onward)<br />
<br />
Lets check about IDENTITY column in this post.<br />
Identity columns offer us the possibility to guarantee uniqueness of a column and to be able to automatically generate unique value. An identity column is a Numeric column, either SMALLINT, INTEGER, or DECIMAL with a scale of zero, or a user defined distinct type based on any of these data types, which is UNIQUE and NOT NULL by definition.<br />
As such there are no rules when to use identity columns. Still we can consider the below scenario when we can use IDENTITY column.<br />
<br />
<b><span style="color: #990000;">When can we use IDENTITY COLUMN</span></b><br />
Suppose we have a multi-column primary key(composite key) and the table has several dependent tables, you have to ‘copy’ many columns to the dependent tables to build the foreign keys. This makes the keys very long. Having many columns in the index also makes the index grow quite large. Instead of using the long key as the primary and foreign key, you can use an artificial unique identifier for the parent table and use that generated value as a primary key and foreign key for the dependent table.<br />
Another use for identity columns is when you just need a generated unique column for a table. If we do not need to reference the rows by this column, then there is no need to even create an index for it, and uniqueness is guaranteed by the system for generated values<br />
<br />
The Identity Columns can be used in table in two ways :<br />
1. The value for identity column is always generated by the DB 2.(GENERATED ALWAYS keyword in the column declaration ; see below for syntax)<br />
2. The value is inserted explicitly by user. And if used don't specify any value then the value is generated by the DB2.(GENERATED BY DEFAULT)<br />
<div>
<br /></div>
<div>
<span style="color: #990000;"><b>Syntax :</b></span></div>
<div>
<br /></div>
<div>
<div>
CREATE TABLE <table_name></div>
<div>
(</div>
<div>
<column1> datatype</div>
<div>
GENERATED ALWAYS/GENERATED BY DEFAULT</div>
<div>
AS IDENTITY</div>
<div>
(</div>
<div>
START WITH <numeric constant>,</div>
<div>
INCREMENT BY <numeric constant>,</div>
<div>
NOMINVALUE / MINVALUE <integer constant>,</div>
<div>
NOMAXVALUE / MAXVALUE <integer constant> </div>
<div>
NOCYCLE / CYCLE,</div>
<div>
NOCACHE / CACHE <integer constant>,</div>
<div>
NOORDER / ORDER</div>
<div>
),</div>
<div>
<br /></div>
<div>
<column2> datatype,</div>
<div>
<column3> datatype,</div>
<div>
...............................</div>
<div>
...............................</div>
<div>
)</div>
</div>
<div>
<br /></div>
<div>
<span style="color: #990000;"><b>Running a query to create Identity column:</b></span></div>
<div>
<br /></div>
<div>
<div>
<span style="color: blue;">CREATE TABLE PROD_DEAL</span></div>
<div>
<span style="color: blue;"><span class="Apple-tab-span" style="white-space: pre;"> </span>( ROW_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY </span></div>
<div>
<span style="color: blue;"> (START WITH 1, INCREMENT BY 1, NO CACHE), </span></div>
<div>
<span style="color: blue;"><span class="Apple-tab-span" style="white-space: pre;"> </span> ORDER_NO CHAR(6), </span></div>
<div>
<span style="color: blue;"><span class="Apple-tab-span" style="white-space: pre;"> </span> INV_COUNT INT WITH DEFAULT 0 </span></div>
<div>
<span style="color: blue;"><span class="Apple-tab-span" style="white-space: pre;"> </span>);</span></div>
</div>
<div>
<br /></div>
<div>
<div>
Suppose we have run the INSERT QUERY 5 times for five different ORDER_NO.</div>
<div>
<br /></div>
<blockquote class="tr_bq">
INSERT INTO PROD_DEAL(ORDER_NO,INV_COUNT) VALUES('A00001',11);<br />
INSERT INTO PROD_DEAL(ORDER_NO,INV_COUNT) VALUES('A00002',22);<br />
INSERT INTO PROD_DEAL(ORDER_NO,INV_COUNT) VALUES('A00003',33);<br />
INSERT INTO PROD_DEAL(ORDER_NO,INV_COUNT) VALUES('A00004',44);<br />
INSERT INTO PROD_DEAL(ORDER_NO,INV_COUNT) VALUES('A00005',55);</blockquote>
</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<blockquote class="tr_bq">
Output:<br />
ROW_ID ORDER_NO INV_COUNT<br />
------------------------------------------------<br />
11000001 A00001 11<br />
11000002 A00002 22<br />
11000003 A00003 33<br />
11000004 A00004 44<br />
11000005 A00005 55</blockquote>
<br />
We can see the ROW_Id generating unique values each time the insert query ran.</div>
Anonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com1tag:blogger.com,1999:blog-2634839917427649118.post-833047175096405042015-06-17T00:01:00.000+05:302015-06-17T00:01:01.958+05:30Copy empty Vsam file using SORT without error - Use of parameter VSAMEMT=YES in SORTOnce came across a scenerio where we had to copy a vsam file to a flat file and then process the flat file in subsequent steps.This is pretty simple and can be achived with a SORT step.But Once the same job abended when the VSAM file was emprty.<br />
Came across this parameter VSAMEMT=YES which can be used with sort to handle this scenerio.<br />
<br />
<blockquote class="tr_bq">
//STEP3 EXEC PGM=SORT,PARM=’VSAMEMT=YES’<br />//*<br />//SYSOUT DD SYSOUT=*<br />//SORTIN DD DSN=XXX.TEST.VSAM,DISP=SHR<br />//SORTOUT DD DSN=TEST.FLATFILE.COPY,DISP=MOD<br />//SYSIN DD *<br />SORT FIELDS=COPY<br />/*</blockquote>
Anonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com0tag:blogger.com,1999:blog-2634839917427649118.post-637517480239516692015-04-20T01:34:00.001+05:302015-04-20T01:47:19.684+05:30COMP-3 variable in cobol. Calculate the number of bytes for COMP-3 variable.COMP3 in cobol enables a computer to store 2 digits in each storage position, except for the rightmost position where the sign is stored. Each digit takes half a byte.<br />
<br />
<b><span style="color: red;">Point to remember</span></b>: Even if we specify 'S' or not in the variable declaration, the sign is stored.<br />
<br />
Suppose we move the digit 1265875 into a field 9(7). In display mode, cobol will occupy 7 bytes, ie, 7 storage positions.<br />
If we use COMP-3 in the variable declaration, it will take only 4 Bytes like below.<br />
<br />
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr style="height: 17.5pt; mso-yfti-firstrow: yes; mso-yfti-irow: 0; mso-yfti-lastrow: yes;">
<td style="border: solid black 1.0pt; height: 17.5pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 49.95pt;" valign="top" width="67"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: #ed7d31; mso-themecolor: accent2;">12<o:p></o:p></span></div>
</td>
<td style="border-left: none; border: solid black 1.0pt; height: 17.5pt; mso-border-alt: solid black .5pt; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 49.95pt;" valign="top" width="67"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: #ed7d31; mso-themecolor: accent2;">65<o:p></o:p></span></div>
</td>
<td style="border-left: none; border: solid black 1.0pt; height: 17.5pt; mso-border-alt: solid black .5pt; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 49.95pt;" valign="top" width="67"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: #ed7d31; mso-themecolor: accent2;">87<o:p></o:p></span></div>
</td>
<td style="border-left: none; border: solid black 1.0pt; height: 17.5pt; mso-border-alt: solid black .5pt; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 49.95pt;" valign="top" width="67"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: #ed7d31; mso-themecolor: accent2;">5C<o:p></o:p></span></div>
</td>
</tr>
</tbody></table>
<br />
<div>
Had it been a negative number, then The rightmost digit would store 'D' in place of C.</div>
<div>
<br /></div>
<div>
<b style="color: #990000;">How many bytes does COMP-3 take ?</b></div>
<div>
<span class="postbody">To calculate the byte-length of a comp-3 field,
start with the total number of digits and divide by 2 giving a result
(discarding the remainder if any), then add 1 to the result.</span></div>
<div>
<span class="postbody"> </span></div>
<div>
<span style="color: red;"><b>Note:</b> </span>For "normal" processing the maximum number of digits is 18 . If we use compiler option as the ARITH(EXTEND) , the compiler will allow us to extend it to 31 bytes</div>
<div>
<br />
Just to share, if we use Odd number of digits for packed decimal, the processing becomes 5-20 % faster than what the speed would be if we use even number of digits!. (Source : Some Informative journal in comp-3 in google )</div>
<div>
<br /></div>
Anonymoushttp://www.blogger.com/profile/14326227685841827094noreply@blogger.com0