Posted: Wed Dec 21, 2005 7:50 am Post subject: db2 program needed
Hi All,
I have a requierement where in I have to replace an existing PROC which generates a Form Report by a new program / the same to be achieved by ICETOOL if possible.Can any body help me out which one would be an efficient way of doing it.
My conclusions:
1.If i extract / unload the query results to a dataset in step one and in the next step execute the new program which generates a formated report for which the newly extracted dataset becomes the input and i can generate the new formatted report to a new dataset.
2.If i do not use unload step ,i directly code in my new program the query with using cursors and then from the results retreived i can generate the new formatted report.
3.Can this be done by ICETOOL ?
Existing QMF PROC Form Report looks like this
Code:
SALARY REPORT FOR MALES FOR MONTH NOV
DEPT DIV DEPTNAME GENDER EMPNO SALARY
---- ---- -------- ------ ------ --------
ABCD PQ MAINFRAME M 100001 3456.50
M 100002 678.54
M 765421 2176.90
M 876667 6700.00
.
.
.
.
.
M 999911 126.90
M 999912 4500.00
=========
56890.93
TEST.QUERY1 USERID.EMPLOYEE
My query looks like:
Code:
SELECT DEPT,DIV,DEPTNAME,GENDER,EMPNO,SALARY
FROM USERID.EMPLOYEE
WHERE DEPT IN ('ABCD') AND DIV = 'PQ' AND DEPTNAME = 'MAINFRAME' AND GENDER = 'M'
ORDER BY DEPT,DIV,DEPTNAME,GENDER,EMPNO
My new Program assumptions:
1.Read file record by record.
2.Add up salary record by record.
3.Display the total.
Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
Posted: Wed Dec 21, 2005 2:26 pm Post subject:
mfuser,
You can unload the table first and then run a sort to give you the summary details. Show us the column definitions and I will show you an example.
If you want the totals displayed you can use the following query
Code:
SELECT DEPT
,DIV
,DEPTNAME
,GENDER
,EMPNO
,SUM(SALARY)
FROM USERID.EMPLOYEE
WHERE DEPT = 'ABCD'
AND DIV = 'PQ'
AND DEPTNAME = 'MAINFRAME'
AND GENDER = 'M'
GROUP BY DEPT
,DIV
,DEPTNAME
,GENDER
,EMPNO
//STEP010 ---unload the data to a new dataset say X
//STEP020 ---PGM=SORT
//SORTIN ---x
//SORTOUT ---Y
//SYSIN DD *
OR
Code:
//STEP010 ---unload the data to a new dataset say X
//STEP020 ---PGM=NEWPGM
//INPUT ---X
//OUTPUT ---Y
I am not clear from your answer when you say unload the data first and run a sort.If we unload the data to a dataset and then read record by record adding up the salary will give us total salary.In your query you had mentioned as grouping by EMPNO as EMPNO is unique can it be grouped ? Please suggest me further to acheive the desired result.If i were to acheive the same by using a new program how should i go ahead ?
Code:
SELECT DEPT
,DIV
,DEPTNAME
,GENDER
,EMPNO
,SUM(SALARY)
FROM USERID.EMPLOYEE
WHERE DEPT = 'ABCD'
AND DIV = 'PQ'
AND DEPTNAME = 'MAINFRAME'
AND GENDER = 'M'
GROUP BY DEPT
,DIV
,DEPTNAME
,GENDER
,EMPNO
The code worked fine and i am able to acheive the results as well as i am able to understand the sequence of steps in which you came up with the solution and it was good learning experience for me.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum