[USflag] The American Programmer [USflag]


Home
Books on Mainframe Programming
Mainframe Manuals and Tutorials
System Abend codes, Sqlcodes, VSAM/QSAM codes
Everything about the IBM AS/400 Midrange Computer
Everything about CICS
Everything about COBOL
Everything about DB2 and SQL
  DB2/SQL Singleton Select embedded in a COBOL program
  DB2/SQL Add Change Delete program
  DB2/SQL program to read table with cursor
  DB2/SQL load table program
  DB2/SQL program to read a table randomly
  DB2/SQL bare bones skeleton program
  DB2/SQL program to insert a row into a table
  DB2/SQL program to update a row
  DB2/SQL program for handling variable length column
  DB2/SQL program for handling column with null indicator
  Embedded SQL. Short tutorial.
  Examples of DCLGEN. Short tutorial.
  Examples of SQL Joins. Short tutorial.
  Examples of DB2 View. Short tutorial.
  Just Enough SQL/QMF/SPUFI To Be Dangerous
  Sample tables STAFF, ORG, EMP and DEPT
  QMF Procedures Written in REXX
  Using REXX Subroutines with the QMF Calc panel
  Using QMF to Execute Your SQL
  QMF Cheat Sheet. Edit codes. QMF Commands, Text Variables, Usage Codes
  Executing SQL in a batch job
  Using Spufi to Execute Your SQL
  Books on DB2 and SQL
  DB2 and SQL, Structured Query Language manuals
  SQLCODES and Their Causes
Everything about IMS
Everything about Java and JavaScript
Everything about JCL and JES
Everything about REXX
Everything about zOS, VSAM, Tivoli, Assembler
Everything about TSO, ISPF, Spufi
Site Map and Site Search

           Home   > DB2   > Just Enough QMF CALC with REXX

Using REXX Subroutines with the QMF Calc panel

The purpose of this tutorial is to get you started with QMF/REXX on the Calc panel when there is no other way 
- you don't have time to go to a class, don't want to buy a complete book on it and 
try to find the things you need to know.

This will show how to use REXX subroutines on the QMF Calc panel. 
The primary emphasis is on QMF running under TSO/ISPF 
on an IBM Mainframe computer with the MVS operating system.

You may create custom-made subroutines in REXX that you can utilize in your QMF reports.  
With these subroutines you can tailor your report in ways that QMF is not capable of doing.  
You can perform calculations on your data, make decisions based on the data, 
and use any of REXX's string handling capabilities.

By using the FORM.CALC panel in QMF, you can specify just which REXX subroutine is to be used.  
The effect of the subroutine will appear in your report just where you want it.

If you already know some REXX and some QMF you will be able to use this.


Summary									12
 
What assumptions did I make about the reader?

I assume you are familiar with:
	IBM Mainframe family of computers running the MVS operating system
	MVS TSO/ISPF or VM/CMS and REXX programming	
	Running SQL under QMF to produce reports from DB2 or SQL/DS tables (databases)
	
So I won't explain those things here. 

In other words, I'm thinking you are a typical Mainframe programmer who wants to add spice to his/her 
QMF reporting by using REXX subroutines.


 
Requirements for the REXX Subroutine

The REXX program must be written as a REXX subroutine.  Figure 1 below shows an example of a REXX subroutine 
used to "translate" QMF data from one specific format to another.  

Here, in brief, are the characteristics of a REXX subroutine:
 	It must start with an ARG statement.  
		The ARG statement receives the data that QMF will pass to it.  
		In Figure 1 below, the ARG statement receives the job data from QMF.

	It must end with a RETURN statement that passes the result of its processing back to QMF.  
	In Figure 1 below, the RETURN statement passes back the job after it has been processed by the subroutine.

Under TSO, your subroutine must have been placed in a library or PDS (Partitioned Data Set) 
that is allocated to the symbolic file name SYSPROC or SYSEXEC.  
	The name by which you refer to it on the FORM.CALC panel will be its member name in its library.  
Under CMS, it must be found on your A disk or on any other disk that is accessible to you.  
	Its CMS file name will be the name by which you refer to it on the FORM.CALC panel.  
Its CMS file type will be EXEC. 
 

     /*REXX FUNCTION/SUBROUTINE "FIXJOB"*/		
     ARG JOB   
     IF JOB = "MGR" THEN JOB = "MANAGER"
     RETURN JOB             

Figure 1. Example of REXX subroutine that can be used with QMF.

 
Contrasting REXX on the Calc panel and REXX on the Proc panel

REXX can be used in the Proc panel as well as here.  It is the same REXX language in both places, 
but there are important differences:


Calc Panel					Proc Panel


Written as REXX subroutine			Not a REXX subroutine

Created with TSO/CMS editor		Created on Proc panel 

Stored on TSO PDS/CMS A disk		Stored in QMF database

Executed by QMF while producing report	Executed by PF2 on Proc panel or RUN PROC     

Initial environment is TSO			Initial environment is QRW

May not run a REXX Proc panel program	May run a query containing a Calc panel 
							with a REXX subroutine



 
Telling QMF the Name of the REXX Subroutine


Before you do anything with REXX under QMF, you must inform QMF about the REXX subroutine.  
You must tell QMF its name and tell QMF which data to pass to the REXX program.  
This is done on the FORM.CALC panel.  An example of the FORM.CALC panel is shown at the bottom of Figure 2.  
In this figure, the REXX subroutine "FIXJOB" is the one we are going to use, the same one shown in Figure 1.


TELLING QMF WHICH COLUMN. THE DATA COMES FROM

    In Figure 2 we are telling QMF to pass the data from the third column that was SELECTed (JOB) 
to our REXX subroutine, by means of the QMF variable &3.  
Our subroutine will receive job information, change MGR to MANAGER, but leave other jobs unchanged 
and give the job back to QMF.  QMF will use the new job on the report.


TELLING QMF THE CALC ID

    Notice the number "1" under "ID" on the FORM.CALC shown in Figure 2.  You have to number the subroutines 
that you list on the FORM.CALC panel.  Since FIXJOB is the first REXX subroutine listed on the FORM.CALC panel, 
we have numbered it "1."  This CALC ID will be used on other QMF panels to tell QMF to pass data to the 
REXX subroutine with this CALC ID. 
 
          
     +-----------------------------------------+  
     ¦SQL Query                                ¦          
     ¦                                         ¦        
     ¦  SELECT NAME, DEPT, JOB, SALARY         ¦
     ¦  FROM Q.STAFF                           ¦ 
     ¦  WHERE DEPT  IN (10, 84)                ¦ 
     ¦  ORDER BY DEPT, JOB                     ¦ 
     ¦                                         ¦ 
     ¦                                         ¦ 
     ¦1=Help 2=Run 3=Exit 4=    5=   6=Draw    ¦ 
     ¦7=Backward 8=Forward                     ¦ 
     ¦Okay, QUERY is displayed.                ¦ 
     ¦===>                                     ¦ 
     +-----------------------------------------+ 

      

      ¦                                                                       
      ¦   FORM.CALC                                                           
      ¦                                     FOR &CALCID ONLY     
      ¦                                                                       
      ¦                                                          
      ¦   ID  CALCULATION EXPRESSION        WIDTH      EDIT                   
      ¦   --- -------------------------     -----      ----      
      ¦                                                                       
      ¦     1 FIXJOB(&3)                                         
 CALC ¦                                                          
 ID---+-----^   ^     ^ 
      ¦         ¦     +----DATA COLUMN                           
 REXX-+---------+ 
 SUBR ¦   1=Help     2=Check    3=End    4=Show     5=Chart      6=Query 
 NAME ¦   7=Backward 8=Forward  9=      10=Insert  11=Delete    12=Report 
      ¦ 
      ¦  COMMAND ===>                                        SCROLL ===> PAGE 

                                        
Figure 2.  Specifying the name of the CALC identifier, of the REXX subroutine, and the QMF column.

    After identifying our REXX subroutine on the FORM.CALC panel, we can now refer to the 
REXX subroutine on other QMF panels.  (See Figure 3.)  You can refer to it as &CALC1 in text lines, 
in the same way you might use &3 in text lines.  The next section explains this further.
 
Using Subroutine Results in Text lines



QMF will substitute the result of the subroutine for the CALC identifier.  
Anytime you use the CALC identifier ("&CALC1" in our example) in a text line, 
QMF will execute your REXX subroutine.  
The result that the subroutine passes back will be printed in the report instead of the word &CALC1.  
Figure 3 shows &CALC1 being used in a break total line.  Figure 4 shows the report that would be produced. 


     +-----------------------------------------+                          
     ¦SQL Query                                ¦                          
     ¦                                         ¦                          
     ¦  SELECT NAME, DEPT, JOB, SALARY         ¦
     ¦  FROM Q.STAFF                           ¦                          
     ¦  WHERE DEPT  IN (10, 84)                ¦                          
     ¦  ORDER BY DEPT, JOB                     ¦                         
     ¦                                         ¦                          
     ¦                                         ¦                          
     ¦1=Help 2=Run 3=Exit 4=    5=   6=Draw    ¦                          
     ¦7=Backward 8=Forward                     ¦                          
     ¦Okay, QUERY is displayed.                ¦                          
     ¦===>                                     ¦                          
     +-----------------------------------------+                          
Created by http://theamericanprogrammer.com. You may copy this document provided this notice is attached.
 

    +------------------------------------------------------------------------+
    ¦                                                                        ¦ 
    ¦      FORM.MAIN                                                         ¦ 
    ¦                                                                        ¦ 
    ¦  COLUMNS:         Total width of report columns: 44                    ¦ 
    ¦   NUM COLUMN HEADING                     USAGE   INDENT WIDTH EDIT SEQ ¦ 
    ¦   --- ---------------------------------- ------- ------ ----- ---- - - ¦ 
    ¦     1 NAME                                        2     10    C    1   ¦ 
    ¦     2 DEPT                               BREAK1   2     6     L    2   ¦ 
    ¦     3 JOB                                BREAK2   2     5     C    3   ¦ 
    ¦     4 SALARY                             SUM      2     10    L2   4   ¦ 
    ¦                                                                        ¦ 
    ¦    PAGE:   HEADING  ===>                                               ¦ 
    ¦            FOOTING  ===>                                               ¦ 
    ¦    FINAL:  TEXT     ===>                                               ¦ 
    ¦    BREAK1: NEW PAGE FOR BREAK? ===> NO                                 ¦ 
    ¦            FOOTING  ===> TOTAL FOR DEPT &2                             ¦ 
    ¦    BREAK2: NEW PAGE FOR BREAK? ===> NO+------+                         ¦ 
    ¦            FOOTING  ===> TOTAL FOR JOB¦&CALC1¦                         ¦ 
    ¦                                       +------+                         ¦ 
    ¦   OPTIONS: OUTLINE? ===> YES               DEFAULT BREAK TEXT? ===>    ¦ 
    ¦                                                                        ¦ 
    ¦     1=Help     2=Check    3=End    4=Show     5=Chart      6=Query     ¦ 
    ¦     7=Backward 8=Forward  9=      10=Insert  11=Delete    12=Report    ¦ 
    ¦                                                                        ¦ 
    ¦    COMMAND ===>                                        SCROLL ===> PAGE¦
    +------------------------------------------------------------------------+
                                                                                  
-----------------------------------------------------
Figure 3.  Using CALC identifier in report text lines.
 


Resulting Report: 

 +---------------------------------------------------------------------+
 ¦           REPORT                             LINE 1    POS 1   79   ¦
 ¦                                                                     ¦
 ¦                                                                     ¦
 ¦  NAME       DEPT    JOB       SALARY                                ¦
 ¦                                                                     ¦
 ¦  MOLINARE     10    MGR       22959.20                              ¦
 ¦  LU                           20010.00                              ¦
 ¦  DANIELS                      19260.25                              ¦
 ¦  JONES                        21234.00                              ¦
 ¦                      +-------+                                      ¦
 ¦         TOTAL FOR JOB¦MANAGER¦83463.45                              ¦
 ¦                      +-------+--------                              ¦
 ¦                                                                     ¦
 ¦         TOTAL FOR DEPT 10     83463.45                              ¦
 ¦                                                                     ¦
 ¦                                                                     ¦
 ¦  GAFNEY       84    CLERK     13030.50                              ¦
 ¦                                                                     ¦
 ¦                               --------                              ¦
 ¦         TOTAL FOR JOB CLERK   13030.50                              ¦
 ¦                                                                     ¦
 ¦                                                                     ¦
 ¦  QUILL              MGR       19818.00                              ¦
 ¦                                                                     ¦
 ¦                      +-------+--------                              ¦
 ¦         TOTAL FOR JOB¦MANAGER¦19818.00                              ¦
 ¦                      +-------+                                      ¦
 ¦  DAVIS              SALES     15454.50                              ¦
 ¦  EDWARDS                      17844.00                              ¦
 ¦                                                                     ¦
 ¦          TOTAL FOR JOB SALES  33298.50                              ¦
 ¦                               --------                              ¦
 ¦                                                                     ¦
 ¦         TOTAL FOR DEPT 84     66147.00                              ¦
 ¦                                                                     ¦
 ¦                                                                     ¦
 ¦                              =========                              ¦
 ¦                              149610.45                              ¦
 ¦                                                                     ¦
 ¦  1=Help      2=            3=End      4=Print   5=Chart     6=Query ¦
 ¦  7=Backward  8=Forward     9=Form     10=Left   11=Right   12=      ¦
 ¦                                                                     ¦
 ¦  COMMAND ===>                                       SCROLL ===> PAGE¦ 
 +---------------------------------------------------------------------+ 
  
Figure 4 Two-level control break report with job modified by REXX subroutine.
 
Using Subroutine Results in Detail Lines

Now that you have told QMF what the CALC ID is, you can use it to display the results of the 
REXX subroutine along with the detail line data.  See Figures 5 and 6 
for an example of how this might be done, and the results.


     +-----------------------------------------+                          
     ¦SQL Query                                ¦                          
     ¦                                         ¦                          
     ¦  SELECT NAME, DEPT, JOB, YEARS          ¦
     ¦  FROM Q.STAFF                           ¦                          
     ¦                                         ¦                          
     ¦                                         ¦                          
     ¦1=Help 2=Run 3=Exit 4=    5=   6=Draw    ¦                          
     ¦7=Backward 8=Forward                     ¦                          
     ¦Okay, QUERY is displayed.                ¦                          
     ¦===>                                     ¦                          
     +-----------------------------------------+                          

   +----------------------------------------------------------------------+
   ¦    FORM.MAIN                                                         ¦
   ¦                                                                      ¦
   ¦COLUMNS:         Total width of report columns: 66                    ¦
   ¦ NUM COLUMN HEADING                     USAGE   INDENT WIDTH EDIT SEQ ¦
   ¦ --- ---------------------------------- ------- ------ ----- ---- --- ¦
   ¦   1 NAME                              +----+    2    20     C    2   ¦
   ¦   2 DEPT                              ¦OMIT¦    2     6     L    3   ¦
   ¦   3 JOB                               ¦OMIT¦    2     5     C    4   ¦
   ¦   4 YEARS                             +----+    2    15     L    5   ¦

  +----------------------------------------------------------------------+  
  ¦FORM.DETAIL                                                           ¦     
  ¦                                                                      ¦     
  ¦ Include Column Headings with Detail Heading? ===> yes                ¦     
  ¦ LINE   ALIGN   DETAIL HEADING TEXT                                   ¦     
  ¦ ----   ------  ---------------------------------------------         ¦     
  ¦ 1      LEFT                                                          ¦     
  ¦ 2      LEFT                                                          ¦     
  ¦ 3      LEFT                                                          ¦     
  ¦                                                                      ¦     
  ¦ New Page for Detail Block? ===> NO  Repeat Detail Heading?   ===> NO ¦  
  ¦                                                                  +-+ ¦  
  ¦ Keep Block on Page         ===> NO  Blank Lines after Block  ===>¦1¦ ¦  
  ¦                                                                  +-+ ¦  
  ¦ Put Tabular Data at Line (Enter 1-999 or NONE)  ===> 1               ¦  
  ¦ LINE   ALIGN   DETAIL BLOCK TEXT                                     ¦  
  ¦ ----   ------  ---------------------------------------------         ¦  
  ¦                    +------+                                          ¦   
  ¦ 2      5       Job:¦&CALC1¦                                          ¦   
  ¦                    +------+                                          ¦   
  ¦ 2      30      Dept: &2_b                                            ¦   
  ¦ 3      LEFT                                                          ¦   
  ¦===>                                                                  ¦   
  +----------------------------------------------------------------------+   

Figure 5 Displaying the results of a REXX subroutine in detail line text. 
The job MGR has been changed to MANAGER by the REXX subroutine.  Part 1 of 2.
 


  +-----------------------------------------------------------------------+
  ¦                                                                       ¦
  ¦   FORM.CALC                                                           ¦
  ¦                                     FOR &CALCID ONLY                  ¦
  ¦                                                                       ¦
  ¦                                                                       ¦
  ¦   ID  CALCULATION EXPRESSION        WIDTH      EDIT                   ¦
  ¦   --- -------------------------     -----      ----                   ¦
  ¦                                                                       ¦
  ¦     1 FIXJOB(&3)                                                      ¦
  ¦                                                                       ¦
  ¦                                                                       ¦ 
  ¦   1=Help     2=Check    3=End    4=Show     5=Chart      6=Query      ¦ 
  ¦   7=Backward 8=Forward  9=      10=Insert  11=Delete    12=Report     ¦ 
  ¦                                                                       ¦ 
  ¦  COMMAND ===>                                        SCROLL ===> PAGE ¦ 
  +-----------------------------------------------------------------------+   
                                        
  +---------------------------------------------------------------+        
  ¦Report                                                         ¦        
  ¦                                                               ¦        
  ¦         NAME                            YEARS                 ¦        
  ¦         --------                        -----                 ¦        
  ¦         MOLINARE+-------+                   7                 ¦        
  ¦             JOB:¦MANAGER¦      DEPT: 10                       ¦        
  ¦                 +-------+                                     ¦        
  ¦         LU      +-------+                  10                 ¦        
  ¦             JOB:¦MANAGER¦      DEPT: 10                       ¦        
  ¦                 +-------+                                     ¦        
  ¦         DANIELS +-------+                   5                 ¦        
  ¦             JOB:¦MANAGER¦      DEPT: 10                       ¦        
  ¦                 +-------+                                     ¦        
  ¦         JONES   +-------+                  12                 ¦        
  ¦             JOB:¦MANAGER¦      DEPT: 10                       ¦        
  ¦                 +-------+                                     ¦        
  ¦         HANES   +-------+                  10                 ¦        
  ¦             JOB:¦MANAGER¦      DEPT: 15                       ¦        
  ¦                 +-------+                                     ¦        
  ¦         SANDERS +-------+                   7                 ¦        
  ¦             JOB:¦MANAGER¦      DEPT: 20                       ¦        
  ¦                 +-------+                                     ¦        
  ¦         PERNAL                              8                 ¦        
  ¦             JOB: SALES         DEPT: 20                       ¦        
  ¦ ==>                                                           ¦        
  +---------------------------------------------------------------+        
Figure 6 Displaying the results of a REXX subroutine in detail line text. 
The job MGR has been changed to MANAGER by the REXX subroutine.  Part 2 of 2.
 
Summary


    You have learned the basics of REXX in QMF.  
Please do not restrict your use of REXX to what you have seen here.  REXX can do far more.  

    What you have done with REXX is to extend the power of QMF far beyond its original capabilities.  I
n creating and using a REXX subroutine, you have in effect created a new 
QMF "system variable," such as "&DATE," "&PAGE," "&1," "&2," etc., only much more flexible.  

You can use these new "system variables" wherever appropriate on QMF forms.  
If you use them on FORM.DETAIL, your REXX subroutine is executed once for every detail line on your report.  
If you use them on FORM.FINAL, your REXX subroutine is executed once as the final line of your report is being printed.

If you use them under usage codes on FORM.MAIN, your REXX subroutine will be executed 
whenever appropriate for usage codes, on final summary lines or break summary lines.  
We urge you to create a few REXX subroutines and to try out their powerful features.  
 

Top of Page



















List of books on DB2 and other mainframe topics

List of books on REXX and other mainframe topics

[Books Computer]

Home Books for Computer Professionals Privacy Terms |
Site Map and Site Search Programming Manuals and Tutorials The REXX Files Top of Page |