tag:blogger.com,1999:blog-54380445698386081122024-02-20T00:08:09.781-08:00Cognizant Mainframer'sSundarhttp://www.blogger.com/profile/15077357211314095499noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-5438044569838608112.post-50063981837761541802007-10-22T09:04:00.000-07:002007-10-22T09:08:40.153-07:00Dinosaurs - Let's Continue to Learn EasyTrieve<p><br /><span style="font-family:courier new;font-size:85%;">Hi All<br /></span></p><p><span style="font-family:courier new;font-size:85%;">We had a nice article from Sindhujaa discussing DB2 V8.0 capabilities. Since .<br />I see a lot of Technical Gurus in our distribution list. Please Volunteer and Share your knowledge with the group.<br />Lets continue with our discussion on Eztrieve. I will be using the same example that I used in my previous discussion. This article will be posted in http://ctsdinos.blogspot.com<br />The report requirement got little bit trickier this time. The report should now list only the employees with a salary > 10000 and summarize the salary by department. It should also display the Department description which is in a different file<br /></p></span><p><span style="font-family:courier new;font-size:85%;">The required report is<br />110/22/2007 REPORT OF EMPLOYEES PAGE 1<br />-<br />EMPLOYEE EMPLOYEE<br />NUMBER NAME BASE SALARY DEPARTMENT<br />0 1123 SUNDAR PANCH $10000.00 ACCOUNTING<br /></span><span style="font-family:courier new;font-size:85%;">DEPARTMENT TOTAL $10000.00<br /></span></p><p><span style="font-family:courier new;font-size:85%;">0 4412 ARNOLD S $12000.00 ADMINISTRATIVE DIVISION<br /></span><span style="font-family:courier new;font-size:85%;">DEPARTMENT TOTAL $12000.00<br /></span></p><p><span style="font-family:courier new;font-size:85%;">0 2012 PRABHA SUNDAR $13000.00 INFORMATION TECHNOLOGY<br />8647 BALAJI SUNDARAM $25000.00<br />DEPARTMENT TOTAL $38000.00<br /></span></p><p><span style="font-family:courier new;font-size:85%;">0 9213 MR X $20000.00 OVERSEAS TRAVEL<br />DEPARTMENT TOTAL $20000.00<br /></span></p><p><span style="font-family:courier new;font-size:85%;">0 FINAL TOTAL $80000.00<br /><br />The file containing the department description and department id is as follows<br />ACCTNG ACCOUNTING<br />ADMIN ADMINISTRATIVE DIVISION<br />HR HUMAN RESOURCE<br />IT INFORMATION TECHNOLOGY<br />TRAVEL OVERSEAS TRAVEL<br /></span></p><p><span style="font-family:courier new;font-size:85%;">The Eztrieve program for the above requirement is as follows. I have highlighted the changes made from our previous version of the program<br /></span></p><p><span style="font-family:courier new;font-size:85%;">FILE PERSNL<br />EMPNUM 1 4 A HEADING ('EMPLOYEE' 'NUMBER')<br />EMPNAME 5 20 A HEADING ('EMPLOYEE' 'NAME')<br />DEPT 25 8 A HEADING ('EMPLOYEE' 'NAME')<br />SALARY 33 4 P 2 MASK ('$$$$9.99') HEADING ('BASE SALARY')<br /><strong></strong></span></p><p><span style="font-family:courier new;font-size:85%;"><strong>DEPARTMENT W 25 A HEADING ('DEPARTMENT')<br /></strong></span></p><p><span style="font-family:courier new;font-size:85%;">FILE REPTOUT PRINTER<br /><strong>FILE DEPTTAB TABLE<br />ARG 1 8 A<br />DESC 9 25 A<br /></strong>JOB INPUT PERSNL NAME MYFIRST<br /><strong>IF SALARY <><br />SEARCH DEPTTAB WITH DEPT GIVING DEPARTMENT<br />PRINT REPT<br />REPORT REPT LINESIZE 100 PRINTER REPTOUT SUMCTL TAG<br />SEQUENCE DEPARTMENT EMPNUM<br />CONTROL DEPARTMENT<br />SUM SALARY<br />TITLE 01 'REPORT OF EMPLOYEES'<br /><strong>LINE 01 EMPNUM EMPNAME SALARY DEPARTMENT<br /></p></strong></span><p><span style="font-family:courier new;font-size:85%;">Lets discuss the items highlighted above<br />DEPT 25 8 A HEADING ('EMPLOYEE' 'NAME')<br />The above line is to include the field DEPT from the input file . We will be using this to retrieve the Department name<br /></span></p><p><span style="font-family:courier new;font-size:85%;">DEPARTMENT W 25 A HEADING ('DEPARTMENT')<br />The above line declares a Working storage variable "DEPARTMENT" Length 25 and Alpha numeric<br /></span></p><p><span style="font-family:courier new;font-size:85%;">FILE DEPTTAB TABLE<br />ARG 1 8 A<br />DESC 9 25 A<br />The file DEPTTAB (DD Name) is referenced as a table .This file contains the Department ID and Description. The ARG and DESC are the EZTRIEVE words. The ARG is the key to the table and DESC is that value that will be retrieved<br /><br />IF SALARY <></p><p><span style="font-family:courier new;font-size:85%;">SEARCH DEPTTAB WITH DEPT GIVING DEPARTMENT<br />Search the file DEPTTAB with key as DEPT and store the retrieved value in the working storage variable DEPARTMENT<br /></span></p><p><span style="font-family:courier new;font-size:85%;">CONTROL DEPARTMENT<br />This breaks the report on Department<br /></span></p><p><span style="font-family:courier new;font-size:85%;">SUM SALARY<br />Sums up the SALARY on Department break<br /></span></p><p><span style="font-family:courier new;font-size:85%;">Hope my above example was clear<br />Questions/ Suggestion/Comments are welcome<br /></p></span><br /></span></strong>Sundarhttp://www.blogger.com/profile/15077357211314095499noreply@blogger.com1tag:blogger.com,1999:blog-5438044569838608112.post-90207975857601120812007-10-15T12:47:00.000-07:002007-10-15T12:50:43.434-07:00Dinosaurs-Let's know DB2 V8<strong>This article is from the Database Expert <em>Sindhujaa Vaageeswaran </em>.</strong><br /><br /> Hi All<br /><br />Welcome to the world of DB2 V8.<br /><br />Now that we are in the process of migrating to DB2 V8, let’s start this session with the new features in V8.Though there are 10 significant features identified by the analysts, we shall concentrate on the tips which will be more relevant to the application developers.<br /><br /> As a first step many SQL limitations have been broken in V8.<br /><br />Naming conventions for tables, views, synonym, function etc has been increased from 18 to 128 bytes.<br /><br />Name of a column has been increased from 18 to 30 bytes.<br /><br />Name of cursor created with DECALRE CURSOR WITH RETURN has been increased from 18 to 30 bytes.<br /><br />The length of character constant, hexadecimal constant and the graphic string constant has been increased to 32704, 32704 and 32698 respectively.<br /><br />Multi row fetch. That is more than 1 row (or a row set) can be got through a single fetch which is not possible in earlier versions.<br /><br />Multi row insert.<br /><br />The length of a column can be altered to a greater size (but not to a smaller length) without drop/recreate of the table.<br /><br />§ The data type of a column can be switched within character data types (CHAR, VARCHAR); within numeric data types (SMALLINT, INTEGER, FLOAT, REAL, FLOAT, DOUBLE, DECIMAL); and within graphic data types (GRAPHIC, VARGRAPHIC). But you cannot change character to numeric or graphic; numeric to character or graphic; or graphic to numeric or character.<br /><br />§ The previous data type changes are permitted even for columns that are part of an index or referenced within a view.<br /><br />§ The identity column characteristics can be altered without table drop/recreate.<br /><br />§ A column can be added to the end of an index without making it unavailable. Previously we need to drop/recreate the index.<br /><br />§ The clustering index for a table can be changed without drop and recreate of the index.<br /><br />§ Changes can be done to partitioned table spaces and indexes that were previously not allowed. For example, the partitioning index can be dropped, A table can be created without a partitioning index, and add a partition can be added to the end of a table to extend the limit key value, partitions can be rotated , and we can even automatically re-balance the partitions.<br /><br />We can discuss about the multi fetch statement in the following weeks.Sundarhttp://www.blogger.com/profile/15077357211314095499noreply@blogger.com0tag:blogger.com,1999:blog-5438044569838608112.post-8554731172096128982007-10-09T09:58:00.000-07:002007-10-09T10:03:55.266-07:00Dinosaurs - Let's Learn EasyTrieve<p><br /><span style="font-size:85%;">Hi All<br />Thought of sharing my knowledge on Easytrieve this time...I just have beginner's knowledge in it. So if anybody finds any mistakes in my mail please feel free to correct me. As usual this article is published in https:\\ctsdinos.blogspot.com<br /></span></p><p><span style="font-size:85%;">Easytrieve is a powerful and easy to learn programming language. With few lines of code you could create powerful reports. This is very helpful for any adhoc work or any application development .It also adds value to your Resume :))<br /></span></p><p><span style="font-size:85%;">I am not going put everything in one mail.. I will just start with a very simple example like a "Hello World" program and then over the subsequent weeks , we will go through in detail.<br />Requirement .<br /></span></p><p><span style="font-size:85%;">I have an Input file with the following data<br /></span></p><p><span style="font-size:85%;"><span style="font-family:courier new;">NAME WS-TEST-RECORDWS-EMP-IDWS-NAME WS-DEPT-IDWS-SALARY<br />TYPE LV1 80 CHR 4 CHR 20 CHR 8 PAC 7<br />COLUMN(1:80) (1:4) (5:24) (25:32) (33:36)<br />NUMBER1 2 3 4 5<br />000001 1123 SUNDAR PANCH ACCTNG 10000.00<br />000002 2012 PRABHA SUNDAR IT 13000.00<br />000003 0234 PRASUNYA SUNDAR ADMIN 7000.00<br />000004 8647 BALAJI SUNDARAM IT 25000.00<br />000005 9213 MR X TRAVEL 20000.00<br />000006 5643 JAMES DAVID TRAVEL 5000.00<br />000007 2212 ROBERT STEVE HR 1000.00<br />000008 3241 JACKIE JOHN IT 7800.00<br />000009 4412 ARNOLD S ADMIN 12000.00<br /></span>and I Need a beautiful report like this sorted on Employee id</span></p><p><br /><span style="font-size:85%;">Just think of writing a small Cobol program for the above requirement . I bet you would end up with at least 100 lines of code and up to 3 hours. You also need a sort step to sort the input on Employee ID<br />With Easytrieve all you need is a few lines of code and no extra sort step is required. The code given below is all it takes for the above requirement<br /></span></p><p><span style="font-size:85%;"><span style="font-family:courier new;">FILE PERSNL<br />EMPNUM 1 4 A HEADING ('EMPLOYEE' 'NUMBER')<br />EMPNAME 5 20 A HEADING ('EMPLOYEE' 'NAME')<br />SALARY 33 4 P 2 MASK ('$$$$9.99') HEADING ('BASE SALARY')<br />FILE REPTOUT PRINTER<br />JOB INPUT PERSNL NAME MYFIRST<br />PRINT REPT<br />REPORT REPT LINESIZE 80 PRINTER REPTOUT<br />SEQUENCE EMPNUM<br />TITLE 01 'REPORT OF EMPLOYEES'<br />LINE 01 EMPNUM EMPNAME SALARY<br /></span><br />The jcl to execute the above program is given below<br /></span><span style="font-size:85%;"><span style="font-family:courier new;">//STEP030 EXEC PGM=EZTPA00,COND=(0,NE)<br />//*<br />//SYSOUT DD SYSOUT=*<br />//SYSPRINT DD SYSOUT=*<br />//PERSNL DD DSN=ADCF74C.SUN.SORTTEST,DISP=SHR<br />//REPTOUT DD DSN=ADCF74C.SUN.SORTTEST.OUT,DISP=(,CATLG,DELETE),<br />// UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE),<br />// DCB=(RECFM=FB,LRECL=100,BLKSIZE=0)<br />//SYSIN DD DSN=ADCF74C.SUN.SOURCE(EZTR1),DISP=SHR <== Your program comes here<br />//* </span><br /><br />Lets go over the above program in detail<br /><br />The structure of Easytrieve program is simple .<br />First is the Library Section - where you define the Files and Variables<br />Second is where all the processing takes place. This section starts with Job statement<br /></span></p><p><strong><span style="font-size:85%;">Library Section<br /></span></strong><span style="font-family:courier new;font-size:85%;">FILE PERSNL<br />FILE REPTOUT PRINTER<br /></span></p><p><span style="font-size:85%;">Our program has two files. DDNAME PERSNL is our input file and REPTOUT is our Output file .<br /></span></p><p><span style="font-family:courier new;font-size:85%;">FILE PERSNL<br />EMPNUM 1 4 A HEADING ('EMPLOYEE' 'NUMBER')<br />EMPNAME 5 20 A HEADING ('EMPLOYEE' 'NAME')<br />SALARY 33 4 P 2 MASK ('$$$$9.99') HEADING ('BASE SALARY') </span></p><span style="font-size:85%;"><p><br />From the input file we need Employee number , Name and salary. The above lines contains the definition of the field. First column is the Field-name , Starting position, Length , type (Alphanumeric or Packed). For the packed field salary you can see additional parameters. "2" indicates decimal position, " MASK ('$$$$9.99')" indicates how the field should be formatted in the report.<br />You dont need to define all the fields in the file like you do for a Cobol program . For example we don't need department id from the input file and hence they can be ignored<br />The HEADING parameter indicates the heading name for the fields defined in the report<br /></p><p><strong>Processing section</strong></p><p><br /><span style="font-family:courier new;">JOB INPUT PERSNL NAME MYFIRST<br /></span>Job statement marks the begging of the processing section . The above statement indicates use PERSNL as input and the name of this program is MYFIRST<br /></p><p><span style="font-family:courier new;">PRINT REPT<br /></span>The above statement says Print the report as defined by the "REPT"<br /></p><p><span style="font-family:courier new;">REPORT REPT LINESIZE 80 PRINTER REPTOUT<br />SEQUENCE EMPNUM<br />TITLE 01 'REPORT OF EMPLOYEES'<br />LINE 01 EMPNUM EMPNAME SALARY<br /></span></p><p>The above Report statement defines how the report "REPT" needs to be formatted<br /></p><p><span style="font-family:courier new;">REPORT REPT LINESIZE 80 PRINTER REPTOUT<br /></span>This line indicates Define report "REPT", and length of the report is 80 bytes . The file name where the report needs to be printed is the REPTOUT. The words "REPORT" "LINESIZE" "PRINTER" are all EasyTrieve words</p><p><br /><span style="font-family:courier new;">SEQUENCE EMPNUM<br /></span>The above line says the report should be printed in the order of EMPNUM<br /></p><p><span style="font-family:courier new;">TITLE 01 'REPORT OF EMPLOYEES'<br /></span>As you guess this defines the title of the report<br /></p><p><span style="font-family:courier new;">LINE 01 EMPNUM EMPNAME SALARY<br /></span>This actually tells the content of the report. Print the fields EMPNUM EMPNAME SALARY<br /><br />Hope my explanation was clear. The above is a very simple example to explain EasyTrieve.<br />Lets discuss more about file handling (using multiple files), using IF THEN and some more report options in the next mail<br />Questions/ Suggestion/Comments are welcome</span></p>Sundarhttp://www.blogger.com/profile/15077357211314095499noreply@blogger.com2tag:blogger.com,1999:blog-5438044569838608112.post-52386354944339819712007-10-01T06:55:00.000-07:002007-10-01T06:58:05.732-07:00Dinosaurs - JCL - Allocate a VSAM in JCL without IDCAMS<span style="font-size:85%;">Hi<br />This week I have just compiled a few JCL tips and tricks. You can find a copy of this article in http://ctsdinos.blogspot.com<br />Also I would like to remind you that , this is an open forum . Everybody is welcome to discuss any topic on Mainframes. You can also use this forum to discuss any issues that you are currently facing in the project.<br /></span><br /><span style="font-size:85%;">1. Allocate VSAM datasets in JCL without IDCAMS<br /><span style="font-family:courier new;">//NEWVSAM EXEC PGM=IEFBR14<br />//DD1 DD DSN=BKTEST.W.CTS047.ST.CL, <<new>><br />// DISP=(,CATLG,DELETE),<br />// RECORG=KS,LRECL=80,<br />// KEYLEN=24,KEYOFF=0,UNIT=SYSDA,SPACE=(CYL,(5,5))<br />//SYSOUT DD SYSOUT=D<br />//SYSPRINT DD SYSOUT=D<br /></span></span><br /><span style="font-size:85%;">२. Split a single sequential file into PDS members<br /><span style="font-family:courier new;">//S5 EXEC PGM=IEBGENER<br />//SYSPRINT DD SYSOUT=*<br />//SYSUT1 DD *<br />1<br />END<br />2<br />END<br />3<br />/*<br />//SYSUT2 DD DSN=ADCF74C.TEST.IEBGENER,<br />// DCB=(LRECL=80,RECFM=FB,BLKSIZE=27920),<br />// SPACE=(TRK,(55,5,10),RLSE),BUFNO=20,<br />// DISP=(NEW,CATLG),UNIT=SYSDA<br />//SYSIN DD *<br />GENERATE MAXNAME=3,MAXGPS=2<br />MEMBER NAME=TEST01<br />GROUP1 RECORD IDENT=(1,'END',3)<br />MEMBER NAME=TEST02<br />GROUP2 RECORD IDENT=(1,'END',3)<br />MEMBER NAME=TEST03<br /></span>The above JCL will allocate a PDS ADCF74C.TEST.IEBGENER and Will split the Input data based on the occurence of character END and will put the data in three new members TEST01, TEST02 and TEST03<br />Member TEST01 will contain the following 2 records<br />1<br />END<br />Member TEST02 will contain the following 2 records<br />2<br />END<br />Member TEST03 will contain the following 1 record<br />3<br /><br />3. Never do a Sort and Repro in two different steps<br />I have seen some Jobs where a file is sorted and reproed to a VSAM file. This is expensive<br />Sort can be used in place of IDCAMS for Repro .So if you want to sort file F1 and load to a VSAM file V1 do it in the same step<br /><span style="font-family:courier new;"></span></span><br /><span style="font-size:85%;"><span style="font-family:courier new;">//SORT EXEC PGM=SORT<br />//SORTIN DD DSN=F1,DISP=SHR<br />//SORTOUT DD DSN=V1,DISP=SHR<br />//SYSIN DD *<br />SORT FIELDS=(5,20)<br />/*<br />//SYSOUT DD SYSOUT=*<br /></span><br /><br />Questions/Suggestions/Comments - Please forward it to Balaji / Myself<br />Thanks<br />P Sundar</span>Sundarhttp://www.blogger.com/profile/15077357211314095499noreply@blogger.com0tag:blogger.com,1999:blog-5438044569838608112.post-24105517668896137482007-09-18T12:29:00.000-07:002007-09-22T06:33:22.472-07:00Dinosaurs - Build records without a programThe topic for this week's discussion is the utility IEBDG . I did not know about this utility for long. We had to build millions of records to volume test an application . I always hate to write a program as it takes time to construct and test it(still might have some errors). I looked for an Utility that could build records without any input and IEBDG helped me out<br /><br />To illustrate, below is an example code that builds 100 different records without any input<br /><br /><strong>JCL<br /></strong><span style="font-family:courier new;font-size:85%;">//IEBDG EXEC PGM=IEBDG<br />//SYSPRINT DD SYSOUT=*<br />//SEQOUT DD DSN=ADCF74C.SUN.SORTTEST,DISP=(CATLG,DELETE), <<your>><br />// UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE),DCB=(RECFM=FB,LRECL=170,BLKSIZE=0)<br />//SYSIN DD *<br />DSD OUTPUT=(SEQOUT)<br />FD NAME=F1,LENGTH=10,STARTLOC=1,FORMAT=ZD,INDEX=5<br />FD NAME=F2,LENGTH=1,STARTLOC=12,FORMAT=AL,ACTION=RP<br />FD NAME=F3,LENGTH=10,STARTLOC=22,PICTURE=6,'SAMPLE',FILL=X'40'<br />CREATE QUANTITY=100,NAME=(F1,F2,F3),FILL=X'40'<br />END<br />/*<br /></span><br /><span style="font-size:85%;">Output<br /><span style="font-family:courier new;">0000000001 A SAMPLE<br />0000000006 B SAMPLE<br />0000000011 C SAMPLE<br />0000000016 D SAMPLE<br />0000000021 E SAMPLE<br />0000000026 F SAMPLE<br />0000000031 G SAMPLE<br />0000000036 H SAMPLE<br />0000000041 I SAMPLE<br />0000000046 J SAMPLE</span><br /></span><br /><strong><em>Comments</em></strong><br />Let me explain the Sysin statements here<br /><br />DSD statement should be the first line and it should contain the definition of the Input/Output files defined in the JCL. In this example we just have one output file defined by DD name SEQOUT<br /><br />FD indicates field definition.<br /><strong>FD NAME=F1,LENGTH=10,STARTLOC=1,FORMAT=ZD,INDEX=5</strong><br />The above statement means Define field F1 of length 10 ,the starting location of this field in the output file is 1 and the format is a zoned decimal. Increment this field by 5(Index = 5)<br /><br /><strong>FD NAME=F2,LENGTH=10,STARTLOC=12,FORMAT=AL,ACTION=RP<br /></strong>The above statement means Define field F2 of length 1 ,the starting location of this field in the output file is 12 and the format is alpha. Increment this field in every record<br /><br /><strong>FD NAME=F3,LENGTH=10,STARTLOC=22,PICTURE=6,'SAMPLE',FILL=X'40'</strong><br />The above statement means Define field F3 of length 10 ,the starting location of this field in the output file is 22 and it is a string"SAMPLE" of fixed length<br /><br /><strong>CREATE QUANTITY=100,NAME=(F1,F2,F3),FILL=X'40'</strong><br />Create statement actually creates the records using the fields defined above. The " FILL=X'40' " indicates any unused bytes in the file to be filled with spaces. The QUANTITY=100 indicates number of records to be created<br /><br /><br />Sometime you might have to build a master file with LRECL say 1000. In this case you may not be able to define each and every field.<br /><br />For example you have a master file which is 100 bytes in length. You want to build say 10,000 records , but you may just want a unique Account number in each records and all the other fields in the record can have the same data. In this case we can use a template record with all the fields defined except account number . We can use this record to generate 100 records with different account numbers alone<br /><br />Example<br />Template record<br /><span style="font-family:courier new;font-size:85%;">xxx ABCD<br /></span><br />Your required output<br /><span style="font-family:courier new;font-size:85%;">001 ABCD<br />002 ABCD<br />.<br />.<br />..<br />100 ABCD<br /></span><br />JCL<br /><span style="font-family:courier new;font-size:85%;">//IEBDG EXEC PGM=IEBDG<br />//SYSPRINT DD SYSOUT=*<br />//IN1 DD DSN=ADCF74C.SUN.SORTTEST,DISP=SHR <<template>><br />//OUT1 DD DSN=ADCF74C.SUN.SORTEST,DISP=(,CATLG,DELETE), <<output>><br />// UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE),DCB=(RECFM=FB,LRECL=7,BLKSIZE=0)<br />//SYSIN DD *<br />DSD OUTPUT=(OUT1),INPUT=(IN1)<br />FD NAME=F1,LENGTH=3,STARTLOC=1,FORMAT=ZD<br />FD NAME=F2,LENGTH=4,FROMLOC=5,INPUT=IN1<br />CREATE QUANTITY=10,NAME=(F1,F2),INPUT=IN1<br />END<br />/*<br /></span><br />The field Definition for F2 indicates that the positions 5 -8 is copied from the input file<br /><br />The above examples are simple to help understand the utility. You could create data in any format(packed, binary etc)You could do any number of things with IEBDG. The manual for IEBDG is available in Mainframe Quick help.<br /><br />If anybody has questions or any specific problems on using the above utility please let me know. Also if anybody has used this utility before in a different way, please share your code.<br /><br />If anybody has joined new in your team who would be interested to join our group please let me know<br /><br />Questions/Suggestions/Comments are always welcome<br /><br />Thanks<br />P SundarSundarhttp://www.blogger.com/profile/15077357211314095499noreply@blogger.com14