Just Enough   QMF/ REXX CALC   to be Dangerous   Tutorial   Using REXX Subroutines with the QMF Calc panel The purpose of this REXX/QMF 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. Manuals on DB2, SQL, QMF Books on SQL 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. Table of Contents What assumptions did I make about the reader? Requirements for the REXX Subroutine Contrasting REXX on the Calc panel and REXX on the Proc panel Telling QMF the Name of the REXX Subroutine Using Subroutine Results in Text lines Using Subroutine Results in Detail Lines Summary 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 of you as 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. ¦ ¦===> ¦ +-----------------------------------------+ +------------------------------------------------------------------------+ ¦ ¦ ¦ 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. In 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. About this series. Sometimes you need just enough information so that you can do something - it gets you started when you don’t have time to learn everything. Get all the others in the series at: Justenuf Other Books for professionals: REXX Reference All REXX verbs, keywords, and built-in functions as found in MVS, VM/CMS and OS/2. Order from MVS Training (800) 356 9093.MVS Training The REXX Language on TSO How to use REXX on TSO. Information, ordering at: REXX ISPF Services: Using the Dialogue Manager with REXX How to create ISPF panels on TSO. Examples in REXX. Information, ordering at:ISPF Services You’ll find manuals on TSO, JCL, REXX, COBOL, DB2 at:Manuals. Top of Page

List of books on DB2 and other mainframe topics List of books on REXX and other mainframe topics