[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 Cheat Sheet

QMF Cheat Sheet, reference

A handy reference with QMF variables, codes and commands.




Text variables
For Heading, footing, control break heading, footing, final &DATE current date &TIME current time &PAGE page number &ROW number of first or last row on page &n value of column 'n' on form.main For control break footing, final &AVGn average of column 'n' since last break &CALCn id number of calculation defined on FORM.CALC &COUNT number of rows since last control break &COUNTn number of non-null values in column 'n' since last break &CPCTn cumulative percent of column 'n' &CSUMn cumulative sum of column 'n' &FIRSTn first value of column 'n' since last break &LASTn last value of column 'n' since last break &MAXn highest value of column 'n' since last break &MINn lowest value of column 'n' since last break &SUMn total of column 'n' since last break
Usage codes - on form.main
AVERAGE average of the numbers COUNT count the rows FIRST first one to occur LAST last one to occur MAX largest value to occur MIN smallest value to occur STDEV standard deviation SUM total of the values OMIT do not print PCT percentage GROUP grouping on the report (sort in sql) ACROSS another grouping on the report BREAK1 first level control break (B1) BREAK2 second level BREAK3-6 other levels BREAK1X break but omit in printing
SQL column datatypes
CHAR(10) 10 character, fixed length non numeric VARCHAR(10) max 10 character, variable length non numeric SMALLINT whole number between -32768 and + 32767 INTEGER whole number between -2 billion and + 2 billion DECIMAL(5,2) normal number with 2 decimal positions total length 5: 123.45 NUMERIC same as decimal FLOAT floating point - extremely large or small DATE TIME TIMESTAMP date+time+microseconds
What form does what?
FORM.MAIN specify columnar organization of report specify usage codes short specification of: heading/footing text break text (break 1 and 2) outline for breaks yes/no column width, indent, edit codes, report sequence change names of columns create a subtotal/control break report FORM.FINAL full specification of final text, its position FORM.BREAK1-6 set format and content of break text control break heading, footing for that level FORM.OPTIONS width of report set fixed columns in the report (don’t move during scroll) reordering of columns when there is a break separator lines outlining for break values single, double spacing FORM.CONDITIONS conditional formatting constraints complex conditions for formatting data FORM.DETAIL for detail lines, (data rows) specify detail heading, block text obtain complete control over printing of detail data (custom report) rearrange text and data values FORM.CALC calulation expressions tailored calculations, REXX execs FORM.PAGE report page heading, footing, alignment number of lines on page suppress page breaks column alignment FORM.COLUMNS specify columns and usage codes alignment of column headings, values definition of new columns
QMF Usage Codes
Codes that summarize: AVERAGE * - averages the values in a column COUNT - counts the values in a column that are not null FIRST - the first value selected in a column LAST - the last value selected in a column MAXIMUM - the highest number; the string highest in the computer's sorting sequence MIMIMUM - the lowest number; the string lowest in the computer's sorting sequence STDEV * - standard deviation of the column values SUM * - total of the values in a column GROUP - Roll several into one. ACROSS - A second level of grouping. printed across the page. Codes that replace detail lines:(QMF Statistical Usage Codes.) CSUM * - cumulative sum of each value in a column PCT * - percent the value is of the total CPCT * - cumulative percent the value is of the total TPCT * - total percent each value is of the total TCPCT * - total cumulative percent each value is of the total OMIT - Don't print column on report. * Math function, can be used only on numeric columns
QMF Commands
QMF commands listed alphabetically. CMS Execute a CMS or CP command, EXEC, or REXX program while you are still in QMF. CONVERT Change a Prompted Query or QBE query to an SQL query. DISPLAY Pull a copy of a saved item from the QMF database into the QMF workspace, and then display the corresponding panel. DPRE See what the printed report will look like. DRAW Create a working SELECT query, or a sample, fill-in-the-blanks INSERT or UPDATE query. EDIT Jump into the full-service system editor: TSO/ISPF EDIT depending on the operating system you are in. ERASE Delete a saved object from the QMF database EXPORT Put a copy of an item that is in the QMF database or in the QMF workspace into a permanent TSO file. HELP Obtain assistance from QMF about QMF commands, panels, and SQL language statements. INSERT Add a line on a panel, to allow you to type in additional information. IMPORT Pull a copy of a TSO or CMS file containing a QUERY, FORM, or PROC into the QMF workspace. ISPF Jump into the full screen, menu-driven program preparation, and file management system that your operating system offers. LAYOUT View the general appearance that your report will have, based solely on the FORM now in the QMF workspace. There will not be any data shown in the sample report. LIST View the names of objects that you have SAVEd in the QMF database. You may type certain QMF commands next to the names to perform various QMF functions on the objects. PRINT Send a copy of your report to the hardcopy printer that your system support group has designated. You may be able to designate a different printer using the SHOW PROFILE or the SET PROFILE commands. RESET Clear a panel, such as query or proc. RUN Execute a query or proc. SAVE Make something in the workspace permanent on disk. SHOW Takes you to the panel that contains and displays the appropriate QMF object that is now in the QMF workspace. SHOW QUERY takes you to the Query panel and displays the query now in the QMF workspace. SHOW GLOBALS displays global variables and lets you change them SET Change one of your Profile settings for the duration of your QMF session. SET PROFILE (LANG = PROMPTED) changes your language to Prompted but not permanently. To make the Profile setting permanent, you need to type SAVE PROFILE. SET GLOBAL change a global variable RESET GLOBAL TSO Execute a TSO command, Clist, or REXX program. ? retrieve the last QMF command -----------------------------------------------------
QMF Commands
COMMANDS LISTED BY FUNCTION GROUPS 1) SESSION CONTROL COMMANDS: END (or PF3) HELP (PF1) SET PROFILE 2) QMF DATABASE COMMANDS: DISPLAY ERASE LIST SAVE 3) COMMANDS THAT DO WORK: CHECK (PF2, depending on panel) CONVERT DRAW LAYOUT PRINT (PF4, depending on panel) DPRE RUN (PF2, depending on panel) 4) COMMANDS THAT DEAL WITH QMF WORKSPACE AND PANELS: BACKWARD (PF7: scroll up) DELETE (PF11) FORWARD (PF8: scroll down) INSERT (PF10) LEFT (PF10: scroll left, on Report panel) RESET RIGHT (PF11: scroll right, on Report panel) SHOW
QMF Commands
QMF COMMANDS LISTED BY FUNCTION KEYWORD FUNCTION KEYWORD CORRESPONDING QMF COMMAND Case, set upper, lower SET PROFILE Clear panel RESET Construct a query DRAW Convert prompted to SQL CONVERT Copy from TSO, CMS IMPORT Copy out to TSO, CMS EXPORT Delete a database item ERASE Display item in workspace SHOW Draw a query DRAW Edit with system editor EDIT Erase a database item ERASE Execute query or proc RUN EXPORT to TSO, CMS EXPORT IMPORT from TSO, CMS IMPORT ISPF editor EDIT Language, set SET PROFILE LAYOUT LAYOUT LIST saved items LIST Load saved item into workspace DISPLAY PDF (Form of ISPF) ISPF Print preview DPRE Prompted, convert to SQL CONVERT Report layout LAYOUT RESET panel RESET Retrieve saved item into workspace DISPLAY Retrieve last QMF command ? RUN query or proc RUN SAVE in database SAVE Scratch a database item ERASE Skeleton query DRAW SPF (synonym of ISPF) ISPF Store in database SAVE View saved item DISPLAY View item in workspace SHOW View report layout LAYOUT View what printed report will look like DPRE View list of saved items LIST
QMF EDIT CODES
Edit codes let you display data in an enhanced report format with dollar signs, commas, pluses and minuses, percent signs, etc. These are easy to specify; you simply type the edit code you want into the Edit Code area for the column you wish to modify. The allowable edit codes are categorized by column data type (different edit codes are available for numeric, character, date/time data, etc.). +-------------------------------------------------------------------+ ¦ Available edit codes for character, numeric and date columns ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦ Character columns: ¦ ¦ ¦ ¦ C Display data unchanged ¦ ¦ CW Column wrap - wrap at specific column ¦ ¦ CT Wrap at most suitable blank ¦ ¦ CDx Wrap at delimiter x ¦ ¦ ¦ X Formats data in hexadecimal form. XW Formats data in hexadecimal form; wraps the data breaking lines at the column boundary. B Formats data in binary form. BW Formats data in binary form; wraps the data breaking lines at the column boundary. G Makes no change in the display of a graphic value. GW Makes no change in the display of a graphic value; wraps the value breaking lines at the column boundary. ¦ ¦ ¦ ¦ ¦ Numeric columns: ¦ ¦ ¦ ¦ L Numeric, no decimal places ¦ ¦ L2 Numeric, 2 decimal places, negative sign ¦ ¦ D2 Dollar editing, 2 decimal places, commas ¦ ¦ DZ2 zero suppress ¦ ¦ K2 Numeric, 2 decimal places, no dollar sign ¦ ¦ commas, negative sign ¦ ¦ P2 2 decimal places, percent sign ¦ ¦ J2 prints leading zero's ¦ E numbers in scientific notation. Dxx numbers in decimal notation with currency symbol and thousands separators. Ixx numbers in decimal notation with leading zeros. Jxx numbers in decimal notation with leading zeros and no negative sign. Kxx numbers in decimal notation with thousands separators. ¦ Date columns: ¦ ¦ ¦ TDMA/ format 12/25/05 TDMA. format 12.25.05 TDM/ format 12/25/2005 TDY/ format 2005/12/25 TDY- format 2005-12-25 ¦ TDM/ 02/16/2005 ¦ ¦ TDD. 16.02.2005 (USA) ¦ ¦ TDYA 02-02-16 (Europe) ¦ ¦ TTU: 01:25 PM (USA) ¦ ¦ TTA 13:25 ¦ ¦ TTS 13:25:10 ¦ ¦ TSI 2005-02-16-13.25.10.012345 Timestmp ¦ ¦ ¦ +-------------------------------------------------------------------+
How do I…?
add a column to the report SHOW FORM.COLUMNS add break text SHOW FORM.BREAKx add dollar signs D2 edit code on FORM.MAIN add final text SHOW FORM.FINAL add footer text SHOW FORM.FINAL add page headings and footings to the report show FORM.MAIN add two columns with QMF SHOW FORM.CALC, create calc expression, use it in FORM.DETAIL break on a column sort on in SQL; break1 usage code on FORM.MAIN calculate a value SHOW FORM.CALC change page heading, alignment SHOW FORM.PAGE change “environment” variables SET GLOBAL change column heading? SHOW FORM.MAIN change column name? SHOW FORM.MAIN change column sequence? change SEQ on FORM.MAIN change date format see Edit codes change detail line spacing SHOW FORM.OPTIONS change global variables SET GLOBAL change width of report SHOW FORM.OPTIONS change without confirmation? choose CONFIRM NO on profile clear a panel RESET QUERY / PROC / FORM confirm before changes? choose CONFIRM YES on profile convert typing to upper case? choose UPPER on profile copy a table into QMF database SAVE DATA AS name copy forms into QMF database SAVE FORM AS name copy SQL into QMF database SAVE QUERY AS name count rows in report usage code COUNT create break text SHOW FORM.BREAKx create free form report SHOW FORM.DETAIL create skeleton query DRAW table-name display a form SHOW FORM.xx display a table DISPLAY name display saved FORM DISPLAY name display saved QUERY DISPLAY name don’t print detail data SHOW FORM.DETAIL end QMF? type EXIT on the command line enter a value when query runs use &variable in SQL or proc execute SQL query? press PF2 on QUERY panel, or type RUN QUERY exit from QMF? type EXIT on the command line fix columns in report SHOW FORM.OPTIONS FORM panel SHOW FORM.xx freeze columns in report (don’t scroll) SHOW FORM.OPTIONS get help press PF1, or type HELP on the command line group values across the page usage code ACROSS group values usage code GROUP Home panel from inside of QMF, press PF3 one or more times Home panel from outside of QMF, choose your company’s menu option indent a column? change indent on FORM.MAIN mixed case with literals? choose STRING on profile move around in a Report? PF7 up, PF8, down, PF10 left, PF11 right omit a column on the report usage code OMIT omit column headings SHOW FORM.DETAIL omit detail lines SHOW FORM.DETAIL outline for break SHOW FORM.MAIN, OUTLINE YES page break before final text SHOW FORM.FINAL print leading zeros edit code J2 print preview DPRE print value once only on break SHOW FORM.MAIN, OUTLINE YES produce subtotals on a column sort on in SQL; BREAKx usage code on FORM.MAIN produce totals SUM in usage code on FORM.MAIN reorder columns on a break SHOW FORM.OPTIONS reorder columns on break SHOW FORM.OPTIONS reorder columns? change SEQ on FORM.MAIN retrieve FORM from QMF database DISPLAY name retrieve SQL from QMF database DISPLAY name retrieve SQL from QMF database DISPLAY name reuse a FORM with a changed query? RUN QUERY (FORM = FORM) run a QUERY with the existing FORM? RUN QUERY (FORM = FORM) run a saved QUERY with a saved FORM RUN MY_1ST_QUERY (FORM = MY_1ST_FORM) save PROFILE? type SAVE PROFILE save the FORMs SAVE FORM as name save the PROFILE SAVE PROFILE save the QUERY SAVE QUERY as name scroll through a Report? PF7 up, PF8, down, PF10 left, PF11 right see the PROFILE? type SHOW PROFILE or press PF11 from the home panel set “environment” variables SET GLOBAL set global variables SET GLOBAL share a saved FORM SAVE FORM AS name (SHARE = YES) share a saved QUERY SAVE QUERY AS name (SHARE = YES) show a FORM SHOW FORM.xx show a row’s number use &ROW variable show average in added text use &n variable show average in report usage code AVERAGE show column data value in QMF text &n variable show count of rows in added text use &COUNTn variable show cumulative percent in added text use &CPCTn variable show cumulative sum in report usage code CSUM show data as Dollar 2 decimal places, negative sign Edit code D2 show data as numeric 2 decimal places, negative sign Edit code L2 show date in report use &DATE variable show first value in report usage code FIRST show first value of column data in added text use &FIRSTn variable show highest value in report usage code MAX show highest value of column data in added text use &MAXn variable show last value in report usage code LAST show last value of column data in added text use &LASTn variable show lowest value in report usage code MIN show lowest value of column data in added text use &MINn variable show numeric data as percent Edit code P show page in report use &PAGE variable show percent in added text use &n variable show percent in report usage code PCT show standard deviation in report usage code STDEV show sum in added text use &SUMn variable show time in report use &TIME variable show total in added text use &SUMn variable skip a column on the report usage code OMIT subtotal on a column sort on in SQL; break1 usage code on FORM.MAIN subtotal values usage code GROUP sum rows in report usage code SUM terminate QMF? type EXIT on the command line total rows in report usage code SUM type in SQL? SHOW QUERY, tab, tab, start typing widen a column? change width on FORM.MAIN wrap data at a specific column Edit code CW wrap data at blank Edit code CT wrap data at specific delimiter Edit code CDx

[Books Computer]

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