 |
Installing and Managing DB2 QMF for TSO/CICS Version 9.1 updates
|
| | | Abstract | | This document provides updated information that supplements or replaces that found in the "Installing and Managing DB2 QMF for TSO/CICS" manual, Version 9.1, GC18-9684-01. You can find this document by searching for GC18-9684-01 on the IBM Publications Center Web site at: www.ibm.com/shop/publications/order | | | | | | | | Content | This document includes the following updates:
- Addition of a URL address where customers can find the QMF program directories in all cases where the program directory is referenced
- Revisions that help remind customers to run job DSQ1EPNL when installing or migrating QMF
- Revisions to remind users of the jobs they need to run when they migrate the mode or version of the database, but do not migrate QMF
- Clarification of spill-file specifications
- Precautionary measures to be taken if you are using certain z/OS services that intercept DCB abends; such services can mask information about spill-related I/O abends and prevent QMF from properly recovering
- The addition of a procedure that allows users to recover their systems either during or after migration to QMF New Function Mode
This update also includes revisions to the description of the DSQ1BLNM job to prevent users from receiving errors when they run the job.
- Clarification of restrictions on changing or adding BIND PACKAGE or BIND PLAN options to the QMF bind jobs for plans and installation packages
- Addition of a bind job for migrating from QMF Version 8.1 New Function Mode to QMF Version 9.1 New Function Mode
- Correction of an error in the table that summarizes QMF program parameters and their default values.
- Changes resulting from a change in DB2 for z/OS Version 9 that no longer allows users to create simple table spaces.
- Clarification on which installation job customers should run to install list views when migrating from one QMF version to another, depending on whether standard views or enhanced list views will be used.
Update for item 1 In all cases where the program directory is mentioned, the following text should be added in parentheses: "(available at http://www.ibm.com/software/data/qmf/library.html)".
Updates for item 2 - Item 1 in the first bulleted list in "First server (new installation or migration)" on page 12 should read as follows:
"Read the Program Directory (available at http://www.ibm.com/software/data/qmf/library.html). Ensure that you complete the SMP/E installation and edit and run job SDSQSAPE(DSQ1EPNL) as described." - The first sentence in "Reading the QMF program directory" on page 15 should read as follows:
"Before beginning the installation process, read the QMF Program Directory (available at http://www.ibm.com/software/data/qmf/library.html)."
(Note that the phrase "...for supplementary data" following "read the QMF Program Directory" has been eliminated.) - In Chapter 3, "Configuring QMF as a DB2 application requester" on page 25, the following sentence should be added just before "Performing a new installation":
"Before you begin these installation or migration tasks, ensure that you have followed all the instructions in "First server (new installation or migration)" on page 12. - Step 4 in "Running the IVP (CICS)" on page 55 should have the following text immediately following Figure 18:
"If the version and release information is incorrect, verify that the DSQ1EPNL installation job completed. This job is described in the QMF Program Directory (available at http://www.ibm.com/software/data/qmf/library.html)." Update for item 3
The text that directly follows Table 15 on page 44 should be changed as follows and then duplicated and placed in a new subsection in "Road maps for the QMF installation" on page 12, titled "Migrating the database version or mode":
"Even if you do not plan to migrate QMF at this time, the job sequence in Table 15 on page 43 must be run if you are migrating the database to New Function Mode or if you are migrating the database from one version to another." Update for item 4
Parts of the topic explaining the DSQSPILL startup parameter (found in Chapter 13, "Passing QMF program parameters at startup time," on page 106) have been rewritten. Below is the revised topic in its entirety. DSQSPILL (extra storage)
Parm name DSQSPILL
Short form L
Valid values YES or NO
Default YES
Because large amounts of report data in storage might affect the operation of other programs, QMF lets you allocate a spill file.
A spill file can improve performance in an interactive QMF session. Buffers in memory can store data so that QMF does not need to return to the database for multiple copies of the same data. Data that the user needs to view multiple times does not need to be retrieved from the database several times; the spill file can be used to store it.
The spill file is activated automatically unless you specify NO when you start QMF: DSQQMFn L=NO
Data is written to the spill file until: - The QMF report is reset (via the RESET DATA command) or a new report is created (by issuing a QMF RUN QUERY, DISPLAY TABLE, or IMPORT DATA command). QMF resets the space allocation to the primary allocation in these cases by opening and closing the spill file with a TTR of zero.
- Your query has finished (all rows requested have been retrieved) and the DATA object is complete.
- Your defined storage for the spill file is full.
Allocating a spill file for TSO users
You can allocate a spill file through a DD statement in the user’s logon procedure, JCL, or CLIST.
When you allocate the spill file, follow these guidelines: - Allocate the file as a temporary data set, which persists for the duration of the user's session.
- You can allocate the file to a virtual I/O device (as shown on the UNIT=SYSVIO statement in the example below) or you can allocate the spill file to other DASD storage, as long as the DASD is a single volume not exceeding 65535 tracks. You cannot allocate the spill file to a terminal.
The QMF spill file is allocated for direct access using the BSAM access method. The z/OS NOTE, POINT, READ, WRITE and CHECK services are used to manage the QMF spill file. Any z/OS restrictions that apply to the use of these system services also apply to the use of the QMF spill file. - Specify fixed-length records, one record for each block. The records must always be unblocked. (A block is the size of a z/OS page: 4096 bytes.)
- Specify release storage by using the RLSE keyword as shown in the example below.
Important:
QMF performance might slow down if QMF needs a data row (as a result of a SCROLL BACKWARD command) and that data is not in the spill file or in virtual storage. In this case, an I/O abend occurs. QMF provides error-handling routines for the DCB SYNAD exit and recovers from these I/O abends using information provided via the DCB ABEND exit. QMF then quits using the spill file and fetches the data again from the database. If you use z/OS tools that intercept DCB ABENDs (such as the B37 abend), ensure that you exclude the QMF spill file from such operations. Otherwise, QMF cannot properly manage the spill file, causing not only unpredictable results, but also difficulty in tracing and diagnosing any errors.
An example allocation statement for spill storage appears in the sample logon procedure provided with QMF, where the spill file is allocated through the DD statement DSQSPILL. The statement looks like this:
//DSQSPILL DD DSN=&&SPILL,DISP=(NEW,DELETE),
// UNIT=SYSVIO,SPACE=(TRK,(1,19),RLSE),
// DCB=(RECFM=F,LRECL=4096,BLKSIZE=4096)
The statement’s SPACE operand can minimize spill file storage requirements during a session: - The small primary extent keeps the space held by the spill file to a single track during sessions when a spill file is not needed.
- The much larger secondary extents are used only when a spill file is required.
- The RLSE keyword lets QMF release all secondary extents when the user’s DATA object is reset. This happens, for example, when the user runs a new query and a new report is returned.
The example below shows how to allocate the spill file in a CLIST. The ATTR statement is not required, but can be specified.
ATTR SPILL RECFM(F) LRECL(4096) BLKSIZE(4096)
ALLOC FILE(DSQSPILL) UNIT(SYSVIO) SPACE(1,19) RELEASE +
NEW DELETE USING(SPILL)
If you need to allocate or re-allocate the spill file during a user's QMF session: - Issue the QMF RESET DATA command to close the current spill file.
- Issue the FREE command to free the current spill file allocation. If you issue this command from within QMF, it must be preceded by the QMF TSO command, as in the following example:
___________________________________________________________________________
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
OK, you may enter a command.
COMMAND ===> TSO FREE FILE(DSQSPILL) - Allocate the new spill file using ATTR and ALLOC statements like those shown above. When the user creates a new report, QMF uses the new spill file specifications.
Update for item 5
Certain precautions must be taken if you are using z/OS services that intercept DCB abends. Such programs can mask information that QMF would normally use to recover from spill-related I/O abends. The topic "Increasing the user's report storage" (in Chapter 21, "Troubleshooting and problem diagnosis" on page 300) should contain this text. This topic is shown in its entirety below. The update for item 5 in the topic on the DSQSPILL parameter (above) also contains information about these precautions. Increasing the user’s report storage This topic provides information about ways to increase users' report storage if you are seeing storage-related errors or performance problems. You can make adjustments to users' virtual storage or spill files to correct any problems you encounter. Adjusting virtual storage for reports
Users might experience slow performance if they do not have enough virtual storage to accommodate a large report. For example, if you set the DSQSBSTG parameter at a very low value and the user runs a query that retrieves hundreds of thousands of rows, QMF can only maintain a small amount of data in memory. The user might find performance slow for formatting complex reports or scrolling the report.
To maximize report performance, make sure you specify an adequate amount of virtual storage for the user, using the DSQSBSTG or DSQSRSTG parameter. To provide the best performance, use a value that accommodates the largest report the user is likely to have. However, be aware that setting the DSQSRSTG parameter at a very high value can cause slow performance. Allocating a spill file for reports
You can also define a spill file for the user. However, using primarily virtual storage for QMF operations provides better performance. Users who rely on a spill file and have little virtual storage might notice slow performance for large reports. For CICS, because a spill file can hold a maximum of 32,767 rows of size 4K each, setting DSQSBSTG higher ensures that QMF will complete the report.
Even with a spill file, a user can encounter the incomplete data condition if the spill file is not large enough to hold all the data. QMF performance may also slow down if QMF needs a data row (as a result of a SCROLL BACKWARD command) and that data is not in the spill file or in virtual storage. In this case, an I/O abend occurs. QMF provides error-handling routines for the DCB SYNAD exit and recovers from these I/O abends using information provided via the DCB ABEND exit. QMF then quits using the spill file and fetches the data again from the database.
Important: If you use z/OS tools that intercept DCB ABENDs (such as the B37 abend), ensure that you exclude the QMF spill file from such operations. Otherwise, QMF cannot properly manage the spill file, causing not only unpredictable results, but also difficulty in tracing and diagnosing any errors. Update for item 6
In Chapter 7, the section "Migrating from a Compatibility Mode release" on page 46 should be replaced with the following section: Migrating from a Compatibility Mode release
This series of steps will migrate a QMF Compatibility Mode installation to a QMF New Function Mode installation. Migration procedure
Use the following procedure to migrate to QMF New Function Mode. The migration procedure includes steps for backing up both the QMF Compatibility Mode system before the migration and the QMF New Function Mode system after the migration. You can use these backups to recover the system to one of these states if you encounter errors in running any of the migration jobs. If you need to return to QMF Compatibility Mode for any reason after the migration, see "Returning to QMF Compatibility Mode after migrating to New Function Mode" below. - Ensure that the data in this server does not need to be accessed by a prior-release Compatibility Mode installation running on a requester database.
Important: After you migrate to QMF Version 9.1 New Function Mode, prior-release Compatibility Mode installations (Version 8.1 Compatibility Mode or Version 7.2 and below) will no longer be able to access this server. (New Function Mode installations cannot coexist with Compatibility Mode installations in the same database.) QMF Version 9.1 Compatibility Mode installations can connect to a QMF Version 9.1 New Function Mode installation, but requester installations that are below QMF Version 9.1 Compatibility Mode will no longer be able to access this server after you migrate to QMF New Function Mode. - If you have not done so already, migrate from the old to the new QMF release while keeping the same mode.
If you are migrating to QMF Version 9.1 New Function Mode from QMF Version 7 or from QMF Version 8.1 Compatibility Mode, you need to first migrate to QMF Version 9.1 using the steps in "Migrating from a previous release" on page 26. That procedure produces a QMF Version 9.1 Compatibility Mode installation. You can then follow the remaining steps in this procedure to migrate from QMF Version 9.1 Compatibility Mode to QMF Version 9.1 New Function Mode. - Back up the QMF Compatibility Mode system before you begin the migration. Follow these steps to create a backup:
a. Obtain the DBID, PSID, and OBIDs of all objects to be migrated in case you need these for recovery purposes. Because the structure of the control tables is different between QMF Compatibility Mode and New Function Mode installations, but the control table names remain the same, DB2 creates different IDs for the Compatibility Mode and New Function Mode control tables during the migration process. You will need the IDs of the objects as they exist in QMF Compatibility Mode if you need to recover the system to this state.
The SQL needed to obtain the DBID, PSID, and OBIDs can be found in the following topic, under the SYSXLAT DD statement: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.ugref/db2z_datasetsdsn1copy.htm
b. Take a full image copy of the following QMF table spaces:
- DSQDBCTL.DSQTSCT1 (contains the Q.OBJECT_DIRECTORY table and its index)
- DSQDBCTL.DSQTSCT2 (contains the Q.OBJECT_REMARKS table and its index)
- DSQDBCTL.DSQTSCT3 (contains the Q.OBJECT_DATA table and its index)
- DSQDBCTL.DSQTSLOG (contains the Q.ERROR_LOG table)
- DSQDBCTL.DSQTSPRO (contains the Q.PROFILES table and its index)
- DSQDBCTL.DSQTSGOV (contains the Q.RESOURCE_TABLE table and its index)
- DSQDBCTL.DSQTSRDO (contains the Q.DSQ_RESERVED table; this table contains information used by QMF during installation and it should never be modified)
- DSQDBCTL.DSQTSSYN (contains the Q.COMMAND_SYNONYMS table)
Copy the indexes at the same time as you copy the data; otherwise, inconsistencies might exist. - Run job DSQ1BSQL to bind QMF installation packages and plans.
(Note: In general, you should never change or add any BIND PACKAGE or BIND PLAN options in any of the QMF bind jobs for plans and installation packages. However, if you are installing QMF via a secondary authorization ID, edit job DSQ1BSQL and add either the OWNER(secauth) parameter or a SET CURRENT SQLID='secauth' specification to the BIND PACKAGE and BIND PLAN statements so that QMF is able to resolve the ID.) - Make a note of what privileges have been granted on the Q.OBJECT_DATA table.
The DSQ1BLNM job that you will run in a later step modifies the Q.OBJECT_DATA table in such a way that any specific user authorizations granted on this table are lost during the migration. Thus, prior to running this job, you must determine what privileges were granted on this table and then regrant them after the migration process is complete.
Run the following query to obtain this information:
SELECT * FROM SYSIBM.SYSTABAUTH
WHERE TCREATOR='Q'
AND TTNAME = 'OBJECT_DATA'
AND GRANTEETYPE <> 'P' - Drop any views, triggers, or materialized query tables defined on the QMF control tables. Job DSQ1BLNM, which you will run in the next step, modifies the QMF control tables in such a way that any views, triggers, or MQTs defined on these tables are lost and must be recreated after the migration.
- Run job DSQ1BLNM to migrate the QMF control tables to QMF New Function Mode.
- Back up the New Function Mode system
Because the definitions of the QMF control tables differ between QMF New Function Mode and QMF Compatibility Mode, take another backup -- this time of the QMF New Function Mode system. This backup will be key if you need to recover the system back to the newly migrated New Function Mode state for any reason.
To create a backup:
a. Obtain the DBID, PSID, and OBIDs, of the migrated objects in case you need them for recovery purposes.
The SQL needed to obtain the DBID, PSID, and OBIDs can be found in the following topic, under the SYSXLAT DD statement: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.ugref/db2z_datasetsdsn1copy.htm
b. Take another image copy of the following QMF table spaces. This image copy represents the state of the QMF control tables in New Function Mode.
- DSQDBCTL.DSQTSCT1 (contains the Q.OBJECT_DIRECTORY table and its index)
- DSQDBCTL.DSQTSCT2 (contains the Q.OBJECT_REMARKS table and its index)
- DSQDBCTL.DSQTSCT3 (contains the Q.OBJECT_DATA table and its index)
- DSQDBCTL.DSQTSLOG (contains the Q.ERROR_LOG table)
- DSQDBCTL.DSQTSPRO (contains the Q.PROFILES table and its index)
- DSQDBCTL.DSQTSGOV (contains the Q.RESOURCE_TABLE table and its index)
- DSQDBCTL.DSQTSRDO (contains the Q.DSQ_RESERVED table; this table contains information used by QMF during installation and it should never be modified)
- DSQDBCTL.DSQTSSYN (contains the Q.COMMAND_SYNONYMS table)
Copy the indexes at the same time as you copy the data; otherwise, inconsistencies might exist. - Run the jobs listed below. If you encounter serious errors with any of these jobs, you can use the DB2 RECOVER utility to return to the full image copies you made in Step 5 and restart the migration process from there.
See the following topic for more information about the RECOVER utility: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.ugref/db2z_utl_recover.htm
- DSQ1BROG
For DB2 for z/OS servers only, this is a sample job that reorganizes the QMF table spaces. You can use your own utilities to perform this function.
- DSQ1BINX
This job drops and creates QMF indexes.
- DSQ1BVW
This job creates DB2 QMF Version 9.1 views.
- DSQ1BPKG
This job binds QMF packages. In this job, the DSQ1DEFS ddcard is optional.
Do not change or add any BIND PACKAGE or BIND PLAN options in this job. - Install QMF sample tables if necessary.
Previous releases of QMF sample tables are still valid in DB2 QMF Version 9.1. If the older sample tables were not previously installed and you want to install them with DB2 QMF Version 9.1, run the appropriate job in the table below. | Job name | Purpose | | DSQ1EIVS | Installs sample tables for DB2 for z/OS servers | | DSQ1EDJ4 | Installs sample tables for DB2 Linux, Unix, and Windows servers | | DSQ1EAS4 | Installs sample tables for DB2 for iSeries servers | - Regrant any privileges that had been previously granted on the Q.OBJECT_DATA table, as well as any views, triggers, or MQTs you had defined on the QMF control tables.
Returning to QMF Compatibility Mode after migrating to New Function Mode
If you encounter serious errors in migrating to New Function Mode, you might need to return to QMF Compatibility Mode. To do so, follow the steps below to return the QMF control tables and their table spaces to their original pre-migration state. - Recreate the QMF control tables with their original structure.
You can do this in one of two ways:
- Drop the migrated objects and recreate them with their original Compatibility Mode structure and data (from your pre-migration image copy that you created in Step 2a of the migration process).
- Use ALTER statements to change the definition of the objects to match the original Compatibility Mode specifications as they existed before the migration. - Recover the original image copies.
Because the migration process for New Function Mode renames the original control tables and drops the renamed tables to recover using the image copies, use the DSN1COPY utility rather than the RECOVER utility to recover the original image copies.
Because the OBIDs of the original image copies do not match the OBIDs of the newly created tables, the OBIDXLAT parameter is required.
The following topics provide more information about the DSN1COPY utility: General information: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.ugref/db2z_utl_dsn1copy.htm Steps to perform before running DSN1COPY: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.ugref/db2z_beforerunningdsn1copy.htm Syntax and options: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.ugref/db2z_dsn1copysyntax.htm Examples: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.ugref/db2z_dsn1copysamples.htm - Recover any indexes on the objects.
- Take a full image copy of the recreated Compatibility Mode objects.
- Rerun jobs DSQ1BVW and DSQ1BPKG.
Updates for item 7 Throughout the manual, two changes should be made regarding cautionary statements about QMF bind jobs: - Change 1:
Existing text:
"Note: Do not change or add any BIND PACKAGE or BIND PLAN options in any of the QMF bind jobs for plans and installation packages."
This text occurs on pages 25, 26, 27, 41, 42, 43, 44, 46, 47, 144, and 323: New text:
"Note: Do not change or add any BIND PACKAGE or BIND PLAN options in any of the QMF bind jobs for plans and installation packages unless instructed to do so in this information or by IBM Support." - Change 2:
Existing text: "If you are installing QMF via a secondary authorization ID, edit this job and add either the OWNER(secauth) parameter or a SET CURRENT SQLID='secauth' specification to the BIND PACKAGE and BIND PLAN statements so that QMF is able to resolve the ID."
This text occurs on pages 25, 26, 27, 41, 42, 43, 45, 46, 47, and 144. New text: "In general, you should never change or add any BIND PACKAGE or BIND PLAN options in any of the QMF bind jobs for plans and installation packages. However, if you are installing QMF via a secondary authorization ID, edit this job and add either the OWNER(secauth) parameter or a SET CURRENT SQLID='secauth' specification to the BIND PACKAGE and BIND PLAN statements so that QMF is able to resolve the ID." Update for item 8 Table 20 on page 47 is missing a bind job. Job DSQ1BINR should be added to the left column with the following corresponding description in the right column:
"DB2 for z/OS requesters only: binds the QMF application plan
If you are installing QMF via a secondary authorization ID, edit this job and add either the OWNER(secauth) parameter or a SET CURRENT SQLID='secauth' specification to the BIND PACKAGE and BIND PLAN statements so that QMF is able to resolve the ID." Update for item 9
The Environment column of Table 34 on page 124 should read "TSO" rather than "TSO, CICS" for the DSQSSUBS parameter. The description of the parameter on page 116 of the manual is correct. Updates for item 10 - Text under "Maintenance" and "Resource contention" on page 151 should read as follows:
Maintenance
When you use the QMF explicit table space option, you simplify maintenance if you take advantage of segmented table spaces. When a table is dropped in a segmented table space, its segments become immediately available for reuse when the drop is committed. It is not necessary to wait for reorganization of the table space.
Implicitly created table spaces can also simplify maintenance. An implicitly created table space is erased automatically when the table it contains is erased. Resource contention To avoid resource contention, use either the explicit table space option with a segmented table space or the implicit table space option. With a segmented table space, when a table is locked, the lock does not interfere with access to segments of other tables. - The list under "Choosing the type of table space" on page 152 should read as follows:
Updates for item 11
When a user issues a LIST or DESCRIBE command for a table, QMF uses a view defined on a set of DB2 catalog tables to obtain information about the table. QMF provides a set of default views, loaded during installation, that return only the tables and column information that the user is authorized to see. Alternatively, the enhanced object list feature allows users to list DB2 tables that belong to group IDs, tables that are owned by the user, and tables available for public viewing.
When you install QMF into a database that contains an existing QMF release, you have a choice of installing the default definitions for the views underlying the LIST command or the definitions that support the enhanced object list feature. This should be made clearer in the publication in the following ways: - In Table 8 on page 26, the second row should read "DSQ1BVW or DSQ1BUDV." The corresponding description in the right column of the table should read "Creates views that are used when users issue the LIST command. Job DSQ1BVW installs default views; job DSQ1BUDV installs views that support the enhanced object list feature. For more information about the enhanced object list feature, see "Enhancing users' object lists" on page 142.
- In the following tables, the line for DSQ1BVW should read as described above:
- Table 15 on page 43
- Table 18 on page 46
- Table 20 on page 47 | | | | | | | Publication number | | GC18-9684 | | | | | Original publication date | | 2007/8/6 | | | | | |
 |
| IBM, the IBM logo and ibm.com are trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the Web at "Copyright and trademark information" at www.ibm.com/legal/copytrade.shtml. |
 |
 |
 |
| Please take a moment to complete this form to help us better serve you. |
 |
 |
 |
|
|
|
 |
 |
| Product categories: |
 |
| | Software |  |
| | Data Warehouse and Industry Data Models |  |
| | Data Warehouse Servers and Appliances |  |
| | DB2 Query Management Facility |  |
| | QMF for TSO/CICS |  |
 |
| Operating system(s): |
| |
z/OS
|
 |
| Software version: |
| |
9.1
|
 |
| Software edition: |
| |
All Editions
|
 |
| Reference #: |
| |
7010290
|
 |
| IBM Group: |
| | Software Group |
 |
| Modified date: |
| | 2009-10-13 |
 |
|