MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

db2 program needed

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
mfuser
Banned


Joined: 01 Mar 2005
Posts: 105
Topics: 58

PostPosted: Wed Dec 21, 2005 7:50 am    Post subject: db2 program needed Reply with quote

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.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose

PostPosted: Wed Dec 21, 2005 2:26 pm    Post subject: Reply with quote

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


Hope this helps....

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
mfuser
Banned


Joined: 01 Mar 2005
Posts: 105
Topics: 58

PostPosted: Wed Dec 21, 2005 2:49 pm    Post subject: Reply with quote

Kolusu,

The table definations are :

Code:

DEPT      CHAR(4)
DIV       CHAR(2)
DEPTNAME  VARCHAR(15)
GENDER    CHAR(1)
EMPNO     CHAR(6)
SALARY    DECIMAL(7,2)


My understanding from your answer is :

Code:

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

Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose

PostPosted: Thu Dec 22, 2005 8:48 am    Post subject: Reply with quote

mfuser,

Here is a sample job

Code:

//STEP0100 EXEC PGM=IKJEFT01               
//SYSTSPRT DD  SYSOUT=*,DCB=BLKSIZE=121     
//SYSPRINT DD  SYSOUT=*                     
//SYSTSIN  DD  *                           
 DSN SYSTEM(xxxx)                           
 RUN  PROGRAM(DSNTIAUL) -                   
      PLAN(DSNTIAUL)    -                   
      PARMS('SQL')      -                   
      LIB('DB2P.RUNLIB.LOAD')               
//SYSREC00 DD DSN=UNLOAD OF TABLE DATASET,     
//            DISP=(NEW,CATLG,DELETE),     
//            UNIT=SYSDA,                   
//            SPACE=(CYL,(X,Y),RLSE)
//SYSPUNCH DD SYSOUT=*                     
//SYSIN    DD  *                   
  SELECT DEPT
        ,DIV
        ,DEPTNAME
        ,GENDER
        ,EMPNO
        ,SALARY
    FROM USERID.EMPLOYEE
    WHERE DEPT     =  'ABCD'
      AND DIV      =  'PQ'
      AND DEPTNAME = 'MAINFRAME'
      AND GENDER   = 'M'     
    ORDER BY DEPT
            ,DIV
            ,DEPTNAME
            ,GENDER
            ,EMPNO
            ;
//*         
//STEP0200 EXEC PGM=SORT               
//SYSOUT   DD SYSOUT=*
//SORTIN   DD DSN=UNLOAD OF TABLE DATASET,
//            DISP=SHR
//SORTOUT  DD DSN=YOUR TOTAL REPORT DATASET,
//            DISP=(NEW,CATLG,DELETE),
//            UNIT=SYSDA,
//            SPACE=(CYL,(X,Y),RLSE)
//SYSIN    DD *
  SORT FIELDS=(1,6,CH,A,9,22,CH,A)
  SUM FIELDS=(30,4,PD)
  OUTFIL REMOVECC,
  TRAILER1=(25:C'=========='/,
            25:TOT=(30,4,PD,EDIT=(TTTTTTTT)))
//*


Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
mfuser
Banned


Joined: 01 Mar 2005
Posts: 105
Topics: 58

PostPosted: Thu Dec 22, 2005 11:00 am    Post subject: Reply with quote

Thanks Kolusu,

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
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


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group