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

# posted by Sundar : 9:04 AM