|The QMF Tools Utility is an application which provides several options for |managing QMF objects from the Control Center environment without entering the |QMF product interface. The QMF Tools Utility also provides an easy way |for a user to view the CREATED, MODIFIED and LAST USED dates of an object as |well as its restricted attribute. An easy way to modify the restricted |attribute is also provided.
|The QMF Tools Utility consists of a full screen interactive interface which |is invoked from the Control Center main menu.
|The QMF Tools Utility can do the following: |
|If the COUNT option is chosen, count values are returned on the screen next |to the names entered.
|If the LIST option is chosen, a list of objects, according to the options |chosen, is presented, sorted by owner, type of object, and name. The |list contains the CREATED, MODIFIED, and LAST USED fields from the QMF |Directory table. A second screen, shown when PF6 is pressed, displays |the subtype (if a QUERY), the RESTRICTED field, and the first 25 characters of |the REMARKS field. An option is given to change the value of the |RESTRICTED field. On the first list screen, these options are |available: |
|Use of QMF Tools requires the following: |
|A sample control file named QMFINIT $CONTROL is supplied with Control |Center. This file must be copied to the Database Administrator's |A-disk, modified, renamed to QMFINIT CONTROL, and then copied back to the |Control Center code disk to make it available to other Control Center |users.
|QMFINIT $CONTROL contains sample entries of control data, which when |modified, are used by the Control Center QMF Tools Utility to establish the |QMF environment for a database prior to invoking QMF. There is one line |of control data for each database for which the user wants to invoke QMF using |the Control Center QMF Tools Utility. Each of these lines contain the |following: |
| Note: | Lines beginning with an asterisk ("*") are treated as comments. |
|If all databases use the same values, an entry of ALL for the database name |can be used. If ALL is used and there is another entry for a specific |database, the specific database entry overrides the ALL entry.
|Figure 64. Example of QMFINIT CONTROL file after being modified by the Database Administrator
SQLDBA QMF320E QMFLNK32 QMFEX32 V3R2 SQLDBA * Database SQLDBA; dcssid QMF320E; invoke QMFLNK32 exec to * * link and access QMF environment; invoke QMFEX32 to link,access* * and invoke QMF; Version 3.2 of QMF; Database machine userid * * SQLDBA. * * This entry overrides the ALL entry for SQLDBA. * ALL QMF610E QMFLNK61 QMFEX61 V6R1 * Database: all databases; dcssid QMF610E; invoke QMFLNK61 exec * * to link and access QMF environment; invoke QMFEX61 to link, * * access and invoke QMF; Version 6.1 of QMF. * * * SQLDBA1 QMF320E QMFLNK32 QMFEX32 V3R2 SQLMACH1 * Database SQLDBA1; dcssid QMF320E; invoke QMFLNK32 exec to * * link and access QMF environment; invoke QMFEX32 to link,access* * and invoke QMF; Version 3.2 of QMF; Database machine userid * * SQLMACH1. This entry overrides the ALL entry for SQLDBA1. * ********************************************************************** |
|The installation-dependent user execs named in the QMFINIT CONTROL file |MUST be available to Control Center users and to the support machine that is |running a scheduled PROC. This means that the Database Administrator |must place them on an installation's common disk or make some other |arrangement for their availability. Note that IBM provides samples with |QMF.
|Caution:
|These EXECs must not link and access QMF using the same virtual address or
|file mode that is used to link and access the Control Center or DB2 code
|disks. These EXECs must not detach the Control Center or DB2 code
|disks.
|
|The QMF Tools Utility is invoked from the Control Center Main Menu by |selecting option Q and pressing Enter. When the QMF Tools Utility is |invoked, the following menu is displayed:
|Figure 65. QMF Tools Utility Menu
+--------------------------------------------------------------------------------+ | mm/dd/yyyy Control Center hh:mm:ss | | *---------------------------------- QMF Tools ------------------------------* | | | Option ===> CTRLID: MSTRSRV1 | | | | Database => SQLDBA NODE: VMSYSTM1 | | | | | | | | *****FUNCTION**** **PARMS** *****DESCRIPTION***** | | | | | | | | 1 QMF Invoke the QMF Facility | | | | 2 LIST List QMF objects | | | | 3 CREATE FILE owner Create File of Queries & Procs | | | | 4 TRANSFER OWNER oldowner newowner Change Object Ownership | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | *---------------------------------------------------------------QMFUTIL----* | | PF: 1 Help 3 End 4 Exit | | | +--------------------------------------------------------------------------------+ |
|There are four options to select from: |
|In the OPTION field, the user specifies "3 nnnnn" ("3" |is the Create File option number and "nnnnn" is the QMF object |owner ID whose objects will be printed). No quotes are allowed, and a |space must exist between the option number (3) and the nnnnn owner |ID. If no objects exist for the owner ID specified, a message is |displayed.
|In the OPTION field, the user specifies "4 oldowner |newowner" ("4" is the Transfer Owner option number, |"oldowner" is the current owner name, and |"newowner" is the new owner name). No quotes are |allowed, and a space must exist between the option number (4), the |oldowner, and the newowner. |
|Selecting the LIST option from the menu shown in Figure 65 causes the following menu to be dislpayed:
|Figure 66. LIST QMF OBJECTS Menu
+--------------------------------------------------------------------------------+ | dd/mm/yyyy Control Center hh:mm:ss | |*----------------------------- LIST QMF OBJECTS ----------------------------* | || Option ===> CTRLID: MSTRSRV1 | | || Database => SQLDBA NODE: VMSYSTM1 | | || | | || ENTER C (Count) or L (List) Option above and one of the choices below: | | || | | || CHOICE 1 | | || OWNER ==> ________ OBJECT ==> __________________ | | || | | || Use a specific OWNER name, the SQL wildcard (%) or ALL for OWNER. | | || For OBJECT use ALL, QUERY, PROC, FORM, a specific name, or SQL wildcard. | | || | | || CHOICE 2 | | || OWNER ==> ________ OBJECT ==> __________________ | | || OWNER ==> ________ | | || OWNER ==> ________ | | || | | || Enter up to 3 specific OWNER names. | | || For OBJECT use ALL, QUERY, PROC, FORM, a specific name, or SQL wildcard. | | || | | |*---------------------------------------------------------------SQMOLIST----* | | PF: 1 Help 3 End 4 Exit 5 Main Menu | +--------------------------------------------------------------------------------+ |
|The LIST option enables the user to either get a count of objects or to get |a list of objects. This is indicated in the option field by a "C" |or "L." The Database name can be changed to request data from |another server.
|The user can choose from one of two OWNER/OBJECT formats: |
|If the COUNT option is chosen, the number of objects is displayed on the |same screen with the value adjacent to each OWNER field.
|If the LIST option is chosen and no objects matching the search criteria |exist, then a message is displayed. Figure 67 is an example of the menu displayed if objects exist that |match the search criteria:
|Figure 67. LIST QMF QUERIES, PROCS, FORMS Menu
+--------------------------------------------------------------------------------+ | mm/dd/yyyy Control Center hh:mm:ss | | *------------------------ LIST QMF QUERIES, PROCS, FORMS -------------------* | | | Option ===> CTRLID: MSTRSRV1 | | | | Database => SQLDBA NODE: VMSYSTM1 | | | | | | | | SEL OWNER TYPE NAME CREATED MODIFIED LAST USED | | | | --- -------- ----- ------------------ ---------- ---------- ---------- | | | | ___ M356959 QUERY ACTNO 05-11-1999 05-11-1999 05-11-1999 | | | | ___ EXPLAIN_QUERY 04-05-1999 04-05-1999 04-09-1999 | | | | ___ TESTTIME 10-14-1998 10-14-1998 05-06-1999 | | | | ___ PROC MYACTNO 05-11-1999 05-11-1999 05-11-1999 | | | | ___ MYPROC 04-29-1999 05-03-1999 05-11-1999 | | | | ___ FORM ACTNO_FORM 05-11-1999 05-11-1999 05-11-1999 | | | | ___ M760595 QUERY TOP5 03-04-1999 03-23-1999 05-03-1999 | | | | ___ USERAUTH 05-03-1999 05-03-1999 05-03-1999 | | | | | | | | SELECT: D (Display) E (Erase) N (New Owner) S (Schedule) X (eXplain) | | | | PROC PARM=> | | | | Page 1 of 1 | | | | | | | *---------------------------------------------------------------SQMOLST2----* | | PF: 1 Help 3 Quit 4 Exit 5 Main Menu 6 Remarks 9 Refresh | +--------------------------------------------------------------------------------+ |
|The display lists the OWNER (specified only once for all objects owned), |the type of object (again, listed only once per object type), the names of the |objects (in alphabetic order), and the CREATED, MODIFIED, and LAST_USED time |stamps from the OBJECT_DIRECTORY table. The objects are listed in the |order: QUERY, PROC, and then FORM.
|Enter the letter of the SELECT option you want to execute in the SELect |field. You can choose to: |
|When the "S" option is chosen for a PROC, a job scheduling panel is |presented (SQMSCHDM). If substitution characters are needed to run the |PROC, the field PROC PARM at the bottom of the selection menu is available for |entering up to 60 characters. For example, if the PROC to be scheduled |(for example, M356959.MYACTNO) consists of:
| SET PROFILE (CONFIRM = NO | RUN M356959.ACTNO (&&NUM=&NUM) | PRINT REPORT
|The query M356959.ACTNO is:
| SELECT * FROM SQLDBA.ACTIVITY | WHERE ACTNO > &NUM
|The parameter needed in the PROC PARM field is:
| &&NUM=150
|The command generated by the scheduling facility is:
| SQMFBATC SQLDBA "M356959"."MYACTNO" (&&NUM=150)
|A new EXEC (SQMFBATC) is supplied which supports execution of a QMF PROC |when the Support Machine receives the command to run it. SQMFBATC |invokes QMF in batch mode to run the PROC. The symbolic parameters are |passed by the EXEC to QMF when it invokes the QMF Batch job.
|SQMFBATC is scheduled to run on a Control Center support machine, which |must have link access to the database and to the necessary QMF |libraries. SQMFBATC reads the QMFINIT $CONTROL file that is maintained |by the user. It supplies the database name, correct DCSS ID and user |EXEC name that links to the QMF code disk. (Refer to QMFINIT $CONTROL Sample Control File for more information about this process.)
|When option "X" is entered next to a QUERY object name, the following |menu is displayed:
| Note: | For more information about running EXPLAIN on package statements, refer to Running EXPLAIN on a Package Query. |
|Figure 68. EXPLAIN QMF QUERY Menu
+--------------------------------------------------------------------------------+ | mm/dd/yyyy Control Center hh:mm:ss | | *------------------------------ EXPLAIN QMF QUERY --------------------------* | | | Option ===> CTRLID: MSTRSRV1 | | | | Database => SQLDBA NODE: VMSYSTM1 | | | | | | | | | | | | EXPLAIN ====> ___ ALL | | | | ___ COST | | | | ___ PLAN | | | | ___ REFERENCE | | | | ___ STRUCTURE | | | | | | | | SET QUERYNO = _______ VIEW RESULTS => 1 (1=YES, 0=NO) | | | | | | | | FOR M356959.EXPLAIN_QUERY | | | | | | | | NOTE: Place an "X" in the appropriate selection fields. Enter a query | | | | number so the results can be extracted for viewing. | | | | | | | | Press "Enter" to submit the EXPLAIN statement. | | | | | | | *---------------------------------------------------------------SQMEXPLN----* | | PF: 1 Help 3 Quit 4 Exit 5 Main Menu | +--------------------------------------------------------------------------------+ |
|Place an "X" in front of each EXPLAIN option you want performed; |ALL includes each of the four other options. Then, provide a numeric ID |for the SET QUERYNO field. After the EXPLAIN is run, if you enter 1 for |VIEW REPORTS, you can view the results immediately in a report presented to |you as a CMS file. If you enter 0, the report is not displayed; it |is saved in a CMS file named QUERYNO Qnn, where |"nn" is the SET QUERYNO value you entered.
|A check is also made to ensure that the EXPLAIN tables exist for the user |requesting the EXPLAIN. An error message is produced if the EXPLAIN |tables do not exist.
|Note that the query name is displayed on the screen. When you press |Enter, the tool runs the EXPLAIN dynamically; the results are saved in |the user's EXPLAIN tables in the database.
|EXPLAIN reports include a description of each EXPLAIN table column. Sample EXPLAIN Report below shows an example of an EXPLAIN report with data from |the REFERENCE table:
|1 CONTROL CENTER QMF EXPLAIN REPORT | EXPLAIN RESULTS FOR QUERYNO: 12345 | ---------------------------- | | QUERY NAME: MYQUERY.ACTNO | DATABASE : SQLDBA | DATE : mm/dd/yyyy hh:mm:ss | | SELECT * FROM SQLDBA.ACTIVITY | WHERE ACTNO > &NUM | |1 | REFERENCE TABLE | --------------- | | TIMESTAMP : dd/mm/yyyy hh:mm:ss | QUERYNO : 12345 | QUERY NAME: MYQUERY.ACTNO | | | RINO QBLOCKNO REFTYPE CREATOR TNAME TABNO | ---- -------- ------- -------- ------------------ ----- | 0 0 SELECT 0 | 0 1 TABLE SQLDBA ACTIVITY 1 | 0 1 COLUMN SQLDBA ACTIVITY 1 | 0 1 COLUMN SQLDBA ACTIVITY 1 | 0 1 COLUMN SQLDBA ACTIVITY 1 | | RINO QBLOCKNO REFTYPE TABNO CNAME COLNO FILTER | ---- -------- ------- ----- ------------------ ----- --------- | 0 0 SELECT 0 0 00.00E+00 | 0 1 TABLE 1 0 00.00E+00 | 0 1 COLUMN 1 ACTDESC 3 10.00E-01 | 0 1 COLUMN 1 ACTKWD 2 10.00E-01 | 0 1 COLUMN 1 ACTNO 1 33.33E-02 | | DBSS JOIN ORDER GROUP UPDATE | RINO QBLOCKNO REFTYPE TABNO COLNO PRED PRED COL COL COL | ---- -------- ------- ----- ----- ---- ---- ----- ----- ------- | 0 0 SELECT 0 0 0 0 | 0 1 TABLE 1 0 0 0 | 0 1 COLUMN 1 3 N N 0 0 | 0 1 COLUMN 1 2 N N 0 0 | 0 1 COLUMN 1 1 Y N 0 0 | | RINO: Value set to 0 for the user's original statement and | incremented by 1 for each internally-generated statement | that is processed for referential integrity or cascade | delete. RINO is intended to distinguish among queries | and internally-generated queries. | | QBLOCKNO: Query block number, where 1 is the outer-level query | block. Different query blocks (as occur in subqueries | receive different numbers. | | REFTYPE: An indication of the purpose of the current row | in the table. Rows are inserted for 3 reasons: | | 1. For each query a keyword is associated indicating | the type of SQL statement: | SELECT - A select statement | INSERT - An insert statement | UPDATE - An update statement | DELETE - A delete statement | SELUPD - A select statement with a FOR UPDATE clause | DELCUR - A delete where current of cursor statement | UPDCUR - An update where current of cursor statement | | 2. For each table referenced - the keyword is TABLE | | 3. For each column referenced - the keyword is COLUMN | | CREATOR: Creator of the table. | | TNAME: Name of the table. | | TABNO: A number that identifies different references to the | same table. | | CNAME: Name of the column. | | COLNO: A number that identifies the column's ordinal position | in the table. | | FILTER: The most selective filter factor associated with this | column. | | DBSSPRED: Is there a sargable predicate associated with this | column? It may not necessarily be the most selective | one. Y - Yes, N - No. | | JOINPRED: Is there a sargable equi-join predicate associated with | this column? If yes, then DBSSPRED must be Y (Yes) | as well. Y - Yes, N - No. | | ORDERCOL: If this column is referenced in an ORDER BY clause | gives its position and sort direction; otherwise zero. | (Positive for ascending order and negative for | descending order). | | GROUPCOL: If this column is referenced in a GROUP BY clause | gives its position; otherwise zero. | | UPDATECOL:If this column is in a SET clause of an UPDATE | statement, indicates how it is updated; | otherwise blank. | L - updated by a literal | X - updated by a column or expression