Cognizant Mainframer's

Hi All

We had a nice article from Sindhujaa discussing DB2 V8.0 capabilities. Since .
I see a lot of Technical Gurus in our distribution list. Please Volunteer and Share your knowledge with the group.
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
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

The required report is
110/22/2007 REPORT OF EMPLOYEES PAGE 1
-
EMPLOYEE EMPLOYEE
NUMBER NAME BASE SALARY DEPARTMENT
0 1123 SUNDAR PANCH $10000.00 ACCOUNTING
DEPARTMENT TOTAL $10000.00

0 4412 ARNOLD S $12000.00 ADMINISTRATIVE DIVISION
DEPARTMENT TOTAL $12000.00

0 2012 PRABHA SUNDAR $13000.00 INFORMATION TECHNOLOGY
8647 BALAJI SUNDARAM $25000.00
DEPARTMENT TOTAL $38000.00

0 9213 MR X $20000.00 OVERSEAS TRAVEL
DEPARTMENT TOTAL $20000.00

0 FINAL TOTAL $80000.00

The file containing the department description and department id is as follows
ACCTNG ACCOUNTING
ADMIN ADMINISTRATIVE DIVISION
HR HUMAN RESOURCE
IT INFORMATION TECHNOLOGY
TRAVEL OVERSEAS TRAVEL

The Eztrieve program for the above requirement is as follows. I have highlighted the changes made from our previous version of the program

FILE PERSNL
EMPNUM 1 4 A HEADING ('EMPLOYEE' 'NUMBER')
EMPNAME 5 20 A HEADING ('EMPLOYEE' 'NAME')
DEPT 25 8 A HEADING ('EMPLOYEE' 'NAME')
SALARY 33 4 P 2 MASK ('$$$$9.99') HEADING ('BASE SALARY')

DEPARTMENT W 25 A HEADING ('DEPARTMENT')

FILE REPTOUT PRINTER
FILE DEPTTAB TABLE
ARG 1 8 A
DESC 9 25 A
JOB INPUT PERSNL NAME MYFIRST
IF SALARY <>
SEARCH DEPTTAB WITH DEPT GIVING DEPARTMENT
PRINT REPT
REPORT REPT LINESIZE 100 PRINTER REPTOUT SUMCTL TAG
SEQUENCE DEPARTMENT EMPNUM
CONTROL DEPARTMENT
SUM SALARY
TITLE 01 'REPORT OF EMPLOYEES'
LINE 01 EMPNUM EMPNAME SALARY DEPARTMENT

Lets discuss the items highlighted above
DEPT 25 8 A HEADING ('EMPLOYEE' 'NAME')
The above line is to include the field DEPT from the input file . We will be using this to retrieve the Department name

DEPARTMENT W 25 A HEADING ('DEPARTMENT')
The above line declares a Working storage variable "DEPARTMENT" Length 25 and Alpha numeric

FILE DEPTTAB TABLE
ARG 1 8 A
DESC 9 25 A
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

IF SALARY <>

SEARCH DEPTTAB WITH DEPT GIVING DEPARTMENT
Search the file DEPTTAB with key as DEPT and store the retrieved value in the working storage variable DEPARTMENT

CONTROL DEPARTMENT
This breaks the report on Department

SUM SALARY
Sums up the SALARY on Department break

Hope my above example was clear
Questions/ Suggestion/Comments are welcome

This article is from the Database Expert Sindhujaa Vaageeswaran .

Hi All

Welcome to the world of DB2 V8.

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.

As a first step many SQL limitations have been broken in V8.

Naming conventions for tables, views, synonym, function etc has been increased from 18 to 128 bytes.

Name of a column has been increased from 18 to 30 bytes.

Name of cursor created with DECALRE CURSOR WITH RETURN has been increased from 18 to 30 bytes.

The length of character constant, hexadecimal constant and the graphic string constant has been increased to 32704, 32704 and 32698 respectively.

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.

Multi row insert.

The length of a column can be altered to a greater size (but not to a smaller length) without drop/recreate of the table.

§ 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.

§ The previous data type changes are permitted even for columns that are part of an index or referenced within a view.

§ The identity column characteristics can be altered without table drop/recreate.

§ A column can be added to the end of an index without making it unavailable. Previously we need to drop/recreate the index.

§ The clustering index for a table can be changed without drop and recreate of the index.

§ 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.

We can discuss about the multi fetch statement in the following weeks.

Hi All
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

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 :))

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.
Requirement .

I have an Input file with the following data

NAME WS-TEST-RECORDWS-EMP-IDWS-NAME WS-DEPT-IDWS-SALARY
TYPE LV1 80 CHR 4 CHR 20 CHR 8 PAC 7
COLUMN(1:80) (1:4) (5:24) (25:32) (33:36)
NUMBER1 2 3 4 5
000001 1123 SUNDAR PANCH ACCTNG 10000.00
000002 2012 PRABHA SUNDAR IT 13000.00
000003 0234 PRASUNYA SUNDAR ADMIN 7000.00
000004 8647 BALAJI SUNDARAM IT 25000.00
000005 9213 MR X TRAVEL 20000.00
000006 5643 JAMES DAVID TRAVEL 5000.00
000007 2212 ROBERT STEVE HR 1000.00
000008 3241 JACKIE JOHN IT 7800.00
000009 4412 ARNOLD S ADMIN 12000.00
and I Need a beautiful report like this sorted on Employee id

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
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

FILE PERSNL
EMPNUM 1 4 A HEADING ('EMPLOYEE' 'NUMBER')
EMPNAME 5 20 A HEADING ('EMPLOYEE' 'NAME')
SALARY 33 4 P 2 MASK ('$$$$9.99') HEADING ('BASE SALARY')
FILE REPTOUT PRINTER
JOB INPUT PERSNL NAME MYFIRST
PRINT REPT
REPORT REPT LINESIZE 80 PRINTER REPTOUT
SEQUENCE EMPNUM
TITLE 01 'REPORT OF EMPLOYEES'
LINE 01 EMPNUM EMPNAME SALARY

The jcl to execute the above program is given below
//STEP030 EXEC PGM=EZTPA00,COND=(0,NE)
//*
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//PERSNL DD DSN=ADCF74C.SUN.SORTTEST,DISP=SHR
//REPTOUT DD DSN=ADCF74C.SUN.SORTTEST.OUT,DISP=(,CATLG,DELETE),
// UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE),
// DCB=(RECFM=FB,LRECL=100,BLKSIZE=0)
//SYSIN DD DSN=ADCF74C.SUN.SOURCE(EZTR1),DISP=SHR <== Your program comes here
//*

Lets go over the above program in detail

The structure of Easytrieve program is simple .
First is the Library Section - where you define the Files and Variables
Second is where all the processing takes place. This section starts with Job statement

Library Section
FILE PERSNL
FILE REPTOUT PRINTER

Our program has two files. DDNAME PERSNL is our input file and REPTOUT is our Output file .

FILE PERSNL
EMPNUM 1 4 A HEADING ('EMPLOYEE' 'NUMBER')
EMPNAME 5 20 A HEADING ('EMPLOYEE' 'NAME')
SALARY 33 4 P 2 MASK ('$$$$9.99') HEADING ('BASE SALARY')

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.
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
The HEADING parameter indicates the heading name for the fields defined in the report

Processing section

JOB INPUT PERSNL NAME MYFIRST
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

PRINT REPT
The above statement says Print the report as defined by the "REPT"

REPORT REPT LINESIZE 80 PRINTER REPTOUT
SEQUENCE EMPNUM
TITLE 01 'REPORT OF EMPLOYEES'
LINE 01 EMPNUM EMPNAME SALARY

The above Report statement defines how the report "REPT" needs to be formatted

REPORT REPT LINESIZE 80 PRINTER REPTOUT
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

SEQUENCE EMPNUM
The above line says the report should be printed in the order of EMPNUM

TITLE 01 'REPORT OF EMPLOYEES'
As you guess this defines the title of the report

LINE 01 EMPNUM EMPNAME SALARY
This actually tells the content of the report. Print the fields EMPNUM EMPNAME SALARY

Hope my explanation was clear. The above is a very simple example to explain EasyTrieve.
Lets discuss more about file handling (using multiple files), using IF THEN and some more report options in the next mail
Questions/ Suggestion/Comments are welcome

# posted by Sundar : 9:58 AM  2 Comments

Hi
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
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.

1. Allocate VSAM datasets in JCL without IDCAMS
//NEWVSAM EXEC PGM=IEFBR14
//DD1 DD DSN=BKTEST.W.CTS047.ST.CL, <>
// DISP=(,CATLG,DELETE),
// RECORG=KS,LRECL=80,
// KEYLEN=24,KEYOFF=0,UNIT=SYSDA,SPACE=(CYL,(5,5))
//SYSOUT DD SYSOUT=D
//SYSPRINT DD SYSOUT=D

२. Split a single sequential file into PDS members
//S5 EXEC PGM=IEBGENER
//SYSPRINT DD SYSOUT=*
//SYSUT1 DD *
1
END
2
END
3
/*
//SYSUT2 DD DSN=ADCF74C.TEST.IEBGENER,
// DCB=(LRECL=80,RECFM=FB,BLKSIZE=27920),
// SPACE=(TRK,(55,5,10),RLSE),BUFNO=20,
// DISP=(NEW,CATLG),UNIT=SYSDA
//SYSIN DD *
GENERATE MAXNAME=3,MAXGPS=2
MEMBER NAME=TEST01
GROUP1 RECORD IDENT=(1,'END',3)
MEMBER NAME=TEST02
GROUP2 RECORD IDENT=(1,'END',3)
MEMBER NAME=TEST03
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
Member TEST01 will contain the following 2 records
1
END
Member TEST02 will contain the following 2 records
2
END
Member TEST03 will contain the following 1 record
3

3. Never do a Sort and Repro in two different steps
I have seen some Jobs where a file is sorted and reproed to a VSAM file. This is expensive
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

//SORT EXEC PGM=SORT
//SORTIN DD DSN=F1,DISP=SHR
//SORTOUT DD DSN=V1,DISP=SHR
//SYSIN DD *
SORT FIELDS=(5,20)
/*
//SYSOUT DD SYSOUT=*


Questions/Suggestions/Comments - Please forward it to Balaji / Myself
Thanks
P Sundar

Labels: ज्क्ल टिप्स त्रिक्क्स मैन्फ्रामे व्सम इद्काम्स iebgener