MVSFORUMS.com

mfuser · Banned Joined: 01 Mar 2005 Posts: 105 Topics: 58

Hi All,

I have a requirement where in i have to replacing an existing Proc with a pgm / icetool if possible whichever is best suited in terms of performance.Actually every employee takes in exam in some / all subjects and gets the marks which are summed up as total to get the report.The .... indicates that like values. My assumption is that the report can be produced first by

Code:

//STEP1.UNLOADING THE DATA TO A FILE
//STEP2.EXECUTE A PGM WHICH WILL READ THE FILE AND PRODUCE THE REPORT.

Can anybody help me out as how should be my approach to get the desired report ?

The query which is being used to generate the existing report looks like this

Code:

SELECT DEPT,SUBDEPT,MONTH,EMPLOYEENO,MONTH,MARKS,SUM(MARKS) FROM EMPLOYEE
WHERE DEPT = 'ABC' AND SUBDEPT IN ('A1','A2','B1','B2')
GROUP BY DEPT, SUBDEPT, MONTH, EMPLOYEENO, MARKS
ORDER BY DEPT, SUBDEPT, MONTH, EMPLOYEENO, MARKS

PROC:

Code:

NUM COLUMN HEADING USAGE INDENT WIDTH EDIT SEQ
--- ---------------------------------------- ------- ------ ----- ----- ---
1 DEPT    BREAK1 2 3 C 1
2 SUBDEPT       BREAK1 2 2 C 2
3 MONTH       BREAK1 2 3 C 3
4 EMPLOYEENO GROUP 2 4 C 4
5 MARKS ACROSS 2 5 L 5
6 COL1 SUM 2 10 L2 6
*** END ***

Morever i am not clear with what is the meaning of the terms BREAK1,ACROOS ,GROUP in the USAGE column and what is the significance of the terms L,L2 in the EDIT column ? Can anybody help me out where do i get the information on these following terms.

Code:

REPORT LOOKS LIKE THIS:
DEPT SUBDEPT MONTH EMPLOYEENO SUB1 SUB2 SUB3 SUB4 SUB5 SUM
--- ------- ----- ---------- --- ---- ---- --- ---- ..... -----
ABC A1 JAN 1001 -10 -20 40 60 80 150
1002 -10 -20 40 60 50 120
1003 20 -5 -10 -10
A2 JAN 2001 .... ... ... .. .. 200
2002 300
....
FEB 3001
3002
....
B1 JAN ....
XXXX
XXXX
XXXX
....
FEB XXXX
XXXX

B2 JAN XXXX
XXXX
XXXX
FEB XXXX
XXXX
XXXX
XXXX
MAR XXXX
XXXX

kolusu · Posted: Tue Jan 03, 2006 7:54 am Post subject:

mfuser,

Please use meaningful topic title. This is your second topic with the same title. An approriate title would be "Summary Report in DB2" or "Convert QMF proc to a DB2 pgm or Sort utility"

The report you have shown does NOT match the Query you have shown. ex: sub1 thru sub5 columns

mfuser · Banned Joined: 01 Mar 2005 Posts: 105 Topics: 58

Kolusu,

If i were to achieve the results using SORT utility ,how can i achive the results as we are having ACROSS as one of the option for the marks for different subjects.Can you please guide me how do i achive the results using SORT utility as my knowledge on using SORT is very limited.

Can you guide me about how the pgm flow should be done to get the results as in the dataset created by the DB2 data does not contain grouped data ?