[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   > QMF Procedures in REXX

QMF Procedures Written in REXX



The purpose of this tutorial is to get you started with QMF/REXX Procedures 
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 write QMF procs using the REXX language. 
The primary emphasis is on QMF running under TSO/ISPF 
on an IBM Mainframe computer with the MVS operating system.

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

Table of Contents What is a REXX QMF Procedure? What assumptions did I make about the reader? How do you Create a Proc in QMF? Go to the PROC screen Type in the proc How do you run a Proc in QMF? How do you save a QMF Proc? Now that I saved it, how do I get it back? Difference between QMF-standard procs and REXX procs. Some things you should know about REXX procs. QMF variables in REXX procs. Resolving variables in queries inside of REXX procs. Setting Global Variables inside of REXX procs Setting a query variable to a literal value within a REXX proc What is a REXX QMF Procedure? A QMF procedure (proc) is a file that you execute under QMF, on the QMF PROC screen. The file contains QMF commands, TSO commands, and REXX programming statements. Each command in the proc is executed, one following the next. QMF procs are very powerful, very flexible, and very practical. They allow you to string together a series of commands that can be saved and executed at any time. You can let other users run your proc by SAVEing it with an attribute of SHARE. You can execute QMF Procs in batch or background processing mode, that is through an MVS job running JCL. This job can be scheduled on a periodic basis, freeing you from the responsibility of having to remember to log on to QMF and generate a business report that is date or time dependent. 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 and REXX programming in this environment Running SQL under QMF to produce reports from DB2 tables (databases) So I won’t explain those things here. If you have used QMF in IBM’s VM/CMS environment, you may still benefit from this document, because many of the things mentioned here will be similar, although not the same, as in VM/CMS. In other words, you are a typical MVS Mainframe programmer who wants to add spice to his/her QMF reporting by using REXX procedures. How do you Create a Proc in QMF? Go to the proc screen First go to the QMF PROC screen, where procs are created and executed. Two ways to get there: PF10 on the Home screen or SHOW PROC on any screen. Pressing PF10 on the QMF Home panel to go to the proc screen. __________________QMF Home Panel________________________________________________ IBM* Licensed Materials - Property of IBM 5706-254 5706-255 5248-067 (c) Copyright IBM Corp. 1982, 1995 All Rights Reserved. * Trademark of International Business Machines ______________________________________________________________________________ QMF HOME PANEL Version 3 Release 2.0 B ****** ** ** ********* ** ** *** *** ** Query ** ** **** **** ******* Management ** ** ** ** ** ** ** Facility ** * ** ** **** ** ** ****** ** ** ** ** ________________________________________ Type command on command line or use PF keys. For help, press PF1 or type HELP. ______________________________________________________________________________ 1=Help 2=List 3=End 4=Show 5=Chart 6=Query 7=Retrieve 8=Edit Table 9=Form 10=Proc 11=Profile 12=Report To go to the Proc screen from any QMF panel, type SHOW Proc on the panel. This example is the REPORT panel. REPORT LINE 1 POS 1 79 ID NAME DEPT JOB YEARS SALARY COMM ------ --------- ------ ----- ------ ---------- ---------- 10 SANDERS 20 MGR 7 18357.50 - 20 PERNAL 20 SALES 8 18171.25 612.45 30 MARENGHI 38 MGR 5 17506.75 - 40 O'BRIEN 38 SALES 6 18006.00 846.55 50 HANES 15 MGR 10 20659.80 - 60 QUIGLEY 38 SALES - 16808.30 650.25 70 ROTHMAN 15 SALES 7 16502.83 1152.00 80 JAMES 20 CLERK - 13504.60 128.20 90 KOONITZ 42 SALES 6 18001.75 1386.70 100 PLOTZ 42 MGR 7 18352.80 - 110 NGAN 15 CLERK 5 12508.20 206.60 120 NAUGHTON 38 CLERK - 12954.75 180.00 130 YAMAGUCHI 42 CLERK 6 10505.90 75.60 140 FRAYE 51 MGR 6 21150.00 - 1=Help 2= 3=End 4=Print 5=Chart 6=Query 7=Backward 8=Forward 9=Form 10=Left 11=Right 12= OK, this is the REPORT from your RUN command. COMMAND SHOW PROC SCROLL ===> PAGE Type in the proc This is a blank proc screen. It should not have any coding in it. If it does, type RESET PROC on the command line to clear the screen. PROC LINE 1 *** END *** 1=Help 2=Run 3=End 4=Print 5=Chart 6=Draw 7=Backward 8=Forward 9=Form 10=Insert 11=Delete 12=Report OK, PROC is displayed. COMMAND SCROLL ===> PAGE Type in your proc on the Proc screen. There are no column requirements on this panel, so you may start in column 1 or any other column. Blank lines are all right. PROC LINE 1 /* REXX QMF PROC STAFF_REPORT */ “RUN STAFF QUERY (FORM=STAFF FORM DET)” “PRINT REPORT (PRINTER=ABC000)” “SAVE DATA AS LATEST_STAFF_TABLE (CONFIRM=NO)” *** END *** 1=Help 2=Run 3=End 4=Print 5= 6= 7=Backward 8=Forward 9=Form 10=Insert 11=Delete 12=Report OK, PROC is displayed. COMMAND SCROLL ===> PAGE You have now created a QMF Proc. How do you run a Proc in QMF? Press PF2 or type RUN PROC on the command line, then press ENTER. Your proc will be executed. If your proc runs another QMF proc, you will lose this one (unless you have saved it) since it will be replaced on the Proc screen by the new one. PROC LINE 1 /* REXX QMF PROC STAFF_REPORT */ “RUN STAFF QUERY (FORM=STAFF FORM DET)” “PRINT REPORT (PRINTER=ABC000)” “SAVE DATA AS LATEST_STAFF_TABLE (CONFIRM=NO)” *** END *** 1=Help 2=Run 3=End 4=Print 5= 6= 7=Backward 8=Forward 9=Form 10=Insert 11=Delete 12=Report OK, PROC is displayed. COMMAND RUN PROC SCROLL ===> PAGE How do you save a QMF Proc? Save your Proc either inside of QMF, in what is known as the QMF database, or in TSO, as a catalogued MVS/TSO dataset. To save inside of QMF and prevent other users from using your proc: Type SAVE PROC AS proc_name on the command line for example SAVE PROC AS STAFF_PROC To save inside of QMF and allow other users to use your proc: Type SAVE PROC AS proc_name (SHARE = YES) on the command line for example SAVE PROC AS STAFF_PROC (SHARE = YES) If someone else wants to use your proc, they will have to know your TSO userid which QMF has prefixed on to the name STAFF_PROC. Its actual name is userid.STAFF_PROC. PROC LINE 1 /* REXX QMF PROC STAFF_REPORT */ “RUN STAFF QUERY (FORM=STAFF FORM DET)” “PRINT REPORT (PRINTER=ABC000)” “SAVE DATA AS LATEST_STAFF_TABLE (CONFIRM=NO)” *** END *** 1=Help 2=Run 3=End 4=Print 5= 6= 7=Backward 8=Forward 9=Form 10=Insert 11=Delete 12=Report OK, PROC is displayed. COMMAND SAVE PROC AS STAFF_PROC (SHARE = YES) SCROLL ===> PAGE To save your proc in TSO, type EXPORT PROC TO ‘userid.name-of-file’ on the command line. Example: EXPORT PROC TO ‘userid.STAFF.PROC’ This will create a catalogued MVS/TSO file containing your QMF Proc. This will be a text file so you can use TSO/ISPF facilities to edit, print, rename or copy it. PROC LINE 1 /* REXX QMF PROC STAFF_REPORT */ “RUN STAFF QUERY (FORM=STAFF FORM DET)” “PRINT REPORT (PRINTER=ABC000)” “SAVE DATA AS LATEST_STAFF_TABLE (CONFIRM=NO)” *** END *** 1=Help 2=Run 3=End 4=Print 5= 6= 7=Backward 8=Forward 9=Form 10=Insert 11=Delete 12=Report OK, PROC is displayed. COMMAND EXPORT PROC TO ‘userid.STAFF.PROC’ SCROLL ===> PAGE Now that I saved it, how do I get it back? Getting it back from QMF. If you saved your proc in QMF with the command SAVE PROC AS STAFF_PROC you can get it back by going to the proc screen, removing anything that may be on it with the command RESET PROC, and then typing DISPLAY STAFF_PROC on the command line. Your saved proc will appear on the proc screen. If another user wants to use your proc saved in QMF with SHARE = YES, they will have to change the command to DISPLAY userid.STAFF_PROC. PROC LINE 1 *** END *** 1=Help 2=Run 3=End 4=Print 5=Chart 6=Draw 7=Backward 8=Forward 9=Form 10=Insert 11=Delete 12=Report OK, PROC is displayed. COMMAND DISPLAY STAFF_PROC SCROLL ===> PAGE Getting it back from MVS/TSO. If you saved your proc on MVS/TSO with the command EXPORT PROC TO ‘userid.STAFF.PROC’, you can get it back by going to the proc screen, removing anything that may be on it with the command RESET PROC, and then typing IMPORT PROC FROM ‘userid.STAFF.PROC’ If another user wants to use your proc saved on MVS/TSO, they do it in exactly the same way as shown here. Created by http://theamericanprogrammer.com. You may copy this document provided this notice is attached. PROC LINE 1 *** END *** 1=Help 2=Run 3=End 4=Print 5=Chart 6=Draw 7=Backward 8=Forward 9=Form 10=Insert 11=Delete 12=Report OK, PROC is displayed. COMMAND IMPORT PROC FROM ‘userid.STAFF.PROC’ SCROLL ===> PAGE Difference between QMF-standard procs and REXX procs. Standard procs can contain comments and the commands that you might type in from the QMF command line. REXX procs may contain comments, QMF commands, and logical statements that control the execution of the proc, such as IF and DO. They may also do repetition, setting and using program variables, and dialoguing with the user. REXX procs must start out with a REXX comment on the first line. A REXX comment is /* anything */. The word REXX does not have to appear in the comment, as it does with TSO REXX programs. Procs that do not start with the REXX comment are processed as standard QMF procs. I’ll start with two examples: a QMF standard proc and a REXX proc. Example of a QMF standard proc. -- This is a procedure to run an accounting period -- query and a consolidation report (another stored proc) -- Various reports are printed to different destinations -- Confirmation messages are sent --1. Import query edited in TSO/ISPF with REXX exec to contain -- the correct WHERE condition for the latest accounting period IMPORT QUERY FROM ‘userid.MYSTAFF.QUERY’ --2. Run imported query using specific QMF Form -- that was saved in the QMF Database RUN QUERY (FORM=ACCTS_REC_F) --3. Print report produced on system printer PRINT REPORT (PRINTER=SYSTEM1) --4. Save data for use in consolidation proc (next step) -- Overwrite existing Latest period accounting info SAVE DATA AS LATEST_STAFF_TABLE (CONFIRM=NO) --5. Run an accounts consolidation proc -- that was saved in the QMF Database RUN ACCT_CONSOLIDATN_P --6. Clean up saved data used in this proc ERASE LATEST_STAFF_TABLE (CONFIRM=NO) --7. Send users message that report is produced and ready -- to print on their printers. -- This is a TSO command, and needs to be prefixed with -- the word TSO. -- Since it is too long to fit on one line, it must -- continued with a plus sign at the end of the line. TSO SEND 'WEEKLY REPORTING/CONSOLIDATION PROCESS RUN' + U(TSO001 TSO967) LOGON --8. End of proc. The EXIT command is optional for -- procs run interactively, but required for procs run in -- Batch processing mode EXIT A few comments about the QMF standard proc. Comments are shown by two hyphens. Anything following the two hyphens on the line is ignored. The QMF command doesn’t need quotes or apostrophes. If a command won’t fit on one line, the way you continue is with a + at the beginning of the second line. This is an example of a REXX QMF Proc. /* This is a procedure to run an accounting period query and a consolidation report (another stored proc) Various reports are printed to different destinations Confirmation messages are sent */ /* 1. Import query edited in TSO/ISPF with REXX exec to contain the correct WHERE condition for the latest accounting period */ "IMPORT QUERY FROM ‘userid.MYSTAFF.QUERY’ " /* 2. Run imported query using specific QMF Form that was saved in the QMF Database */ "RUN QUERY (FORM=ACCTS_REC_F)" /* 3. Print report produced on system printer */ "PRINT REPORT (PRINTER=SYSTEM1)" /* 4. Save data for use in consolidation proc (next step) Overwrite existing Latest period accounting info */ "SAVE DATA AS LATEST_STAFF_TABLE (CONFIRM=NO)" /* 5. Run an accounts consolidation proc that was saved in the QMF Database */ "RUN ACCT_CONSOLIDATN_P" /* 6. Clean up saved data used in this proc */ "ERASE LATEST_STAFF_TABLE (CONFIRM = NO)" /* 7. Send users message that report is produced and ready to print on their printers. This is a TSO command, and needs to be prefixed with the word TSO. Since it is too long to fit on one line, it must be continued with a comma at the end of the line. */ "TSO SEND 'WEEKLY REPORTING/CONSOLIDATION PROCESS RUN' ", "U(TSO001 TSO967) LOGON" /* 8. End of proc. The EXIT command is optional for procs run interactively, but required for procs run in Batch processing mode. The quotes are required because you are passing the command to QMF, and asking it to exit. If you need to use a REXX exit as well, you do not put the REXX exit in quotes. */ "EXIT" A few notes about the REXX proc. Comments are placed between a /* and a */. The comment may continue onto one or more lines. TSO and QMF commands are placed in quotes or apostrophes. The way you continue a REXX line is by placing a comma at the end of the first line, then continuing on the next line. Some things you should know about REXX procs REXX procs may contain: Any valid QMF command bounded by quotes or apostrophes Comments. REXX comments are bounded by /* and */. REXX procs must in fact begin with a REXX comment on the first line. Statements that execute TSO commands, CLISTS, and other REXX programs. REXX programming statements, such as IF, SAY and DO. These are not bounded by quotes or apostrophes. REXX variables which contain data created by or found within the REXX proc. These are not bounded by quotes or apostrophes. Some differences between REXX under QMF and REXX in TSO. The ARG and PARSE ARG statements do not function The initial address environment is QRW, which stands for QMF To send a command to TSO, prefix it with ADDRESS TSO (out of quotes) or put the command in quotes and prefix it with TSO space (in the quotes) The word REXX is not required in the initial comment You may not send commands to the ISPF editor ISREDIT Most ISPF functions do not work, so do not send commands to ISPEXEC QMF variables in REXX procs. Since these are part of a QMF command in a REXX proc, they are enclosed in quotation marks or apostrophes. When a variable is found in a proc, QMF presents the RUN Command Prompt Panel, with the original variable(s), and an area for you to enter literal values to resolve the variable. The Proc: /* REXX proc containing QMF variables */ "RUN &WHICH_REPORT (FORM=&FORM_NAME) "SAVE DATA AS REPORT_DATA "PRINT REPORT (PRINTER=PRT123) The panel on which you can type values: RUN Command Prompt - Values of Variables Your RUN command runs a query or procedure with variables that need values. Fill in the value for each variable named below, after the arrow. &WHICH_REPORT ===> &FORM_NAME ===> Setting Global Variables inside of REXX procs. This is a good way to set a variable that is used in your query. A global variable remains set for the life of the QMF session. Setting a global variable to a literal in a REXX program “SET GLOBAL (STARTDATE = ‘2003-05-09’)” Setting a global variable to a variable in a REXX program Startdate = “‘2003-05-09’” “SET GLOBAL (STARTDATE = “ Startdate Setting a query variable to a literal value within a REXX proc This is done the same way as for standard procs, with one difference: the entire command is enclosed in quotation marks. Do not use apostrophes as they will interfere with the apostrophes needed around character data values). To set a query variable to a literal from within a proc, simply code a substitute "&&variable=value" parameter in your proc's run statement. The general form is &&VARIABLE=. A RUN command that demonstrates this technique is shown just below. Note from the figure that you reference the query variable in the RUN command after the open parenthesis and FORM=formname specifier. The general format for this is as follows: "RUN (FORM=
&&QUERY VARIABLE NAME=)" Example: “RUN MY_QUERY (FORM = MY_FORM &&SALARY = 80000” Remember, the literal you use as a substitute for the variable must match the column data type of the column the query variable is being compared with in the original stored query: Character columns require apostrophes around your values. This is the SQL Query with a query variable (&PERIOD). It has been saved with the name ACCTS_REC_Q. SELECT * FROM ACCTS_REC_TAB WHERE PERIOD = &PERIOD This is the proc that runs the query just shown: /* Sample REXX proc with QMF variable */ "RUN ACCTS_REC_Q (FORM=ACCTS_REC_F &&PERIOD=4)"

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 |