Installation prerequisite for Query Patroller, Version 8
Setting DYN_QUERY_MGMT for installing and using Query Patroller
Additional notes on migration from Query Patroller, Version 7
Restriction on the generation of historical data with positioned UPDATE or DELETE statements
Removal of the prerequisites for starting Query Patroller (AIX)
Using the DB2 governor with Query Patroller
Query Patroller command line help correction
Query Patroller query recovery
Query Patroller server installation
Using Query Patroller with the DB2 connection concentrator
DBCLOB objects not available in Show result dialog
Need to run qpsetup command on each database
Query Patroller profile IDs are case-sensitive
No operator profile required for users with DBADM authority
Apply filters when viewing a large number of queries
Corrections to LIST QUERIES command description
Corrections to the descriptions of qpstart and qpsetup commands
Recommendations for generating historical data
Stopping the historical data generator
Queries run in the background issue loads
Out of memory errors with Query Controller or Query Patroller Center
If you are installing Query Patroller, Version 8 on DB2 Universal Database Enterprise Server Edition, Version 8.1.2, you must have also installed FixPak 2+. This means that you need to have installed both DB2 Version 8.1.2 (also known as FixPak 2) and FixPak 2+ before installing Query Patroller.
If you have a later version of DB2 Enterprise Server Edition, then you do not need this additional FixPak.
Both DB2 Version 8.1.2 (Fixpak 2) and FixPak 2+ are available from the DB2 Technical Support download page: http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/download.d2w/report
If you attempt to install Query Patroller, Version 8 on DB2 Enterprise Server Edition, Version 8.1.2 without having installed FixPak 2+, you will receive an error message saying that you do not have the correct level of DB2 Universal Database to install the product.
In order for Query Patroller to intercept and manage queries, the DYN_QUERY_MGMT parameter must be set to ENABLE. However, this parameter must be set to DISABLE before you install Query Patroller to prevent the interception of any internal queries run by the installer.
By default this parameter is set to DISABLE.
When migrating an administrator profile from Query Patroller, Version 7 to Query Patroller, Version 8, an operator profile is created. This profile is automatically granted the maximum authority level for all Query Patroller privileges. However, the migrated profile is not automatically granted DBADM authority on the Query Patroller database. This means that the migrated administrator profile does not have privileges to generate historical data or create, update, or remove operator profiles. If the user or users with this migrated profile need to perform these tasks, you must manually grant DBADM authority to the user after migration.
Historical data cannot be generated for queries that include positioned UPDATE or positioned DELETE statements.
The Query Patroller documentation states the following prerequisites for starting Query Patroller on AIX operating systems:
EXTSHM=ON export EXTSHM
This dependency has been removed, and the items above are no longer prerequisites for starting Query Patroller on AIX operating systems.
The main function of Query Patroller is to help database administrators manage queries against a database. The main function of the DB2 governor is to help administrators manage applications running against a database.
The DB2 governor allows you to place limits on resources such as the number of locks, the amount of idle time, and the amount of CPU used by an application.The DB2 governor can be used together with Query Patroller to provide a tremendous amount of administrative control. However, in order to use them together effectively, you must understand how they interact.
Query Patroller is a system of collaborative applications that run against a database. Since the governor can act against these applications the way it does against other applications, there are some guidelines to follow when specifying rules in the governor configuration file.
Specifically, it is important to avoid including the processes used by Query Patroller in the rules of the governor configuration file. Query Patroller uses javaw.exe, java.exe, db2fmp.exe, and qp.exe on Windows and java, db2fmp, and qp on UNIX to carry out its operations. To prevent the governor from acting against Query Patroller, do not include these processes in the governor configuration file. You should also make sure you do not have a general rule that intercepts all applications by default. Instead, explicitly include the list of applications to be intercepted by the DB2 governor.
If the processes used by Query Patroller cannot be excluded from interception by the DB2 governor, then you should follow the following guidelines in writing your governor configuration file rules.
Restrictiveness of other limits, including CPU usage and idle time, may also cause the DB2 governor to act against Query Patroller processes depending on the amount of time and resources used by Query Patroller to operate on Query Patroller control tables. Once again, this amount cannot be predetermined as it depends on hardware capability and data size. If desired, increase the limits to higher values to prevent the DB2 governor from acting against Query Patroller.
If the priority or schedule actions apply to the Query Patroller processes, Query Patroller will continue to run with reduced system resources. However, if a force action applies to a Query Patroller process, the process can be terminated. The force action might terminate a Query Patroller process normally, returning a SQL1224N return code, or it might cause an application error or an abnormal termination of the DARI process (SQL1131N) if the db2fmp process was started before the force was issued. Query Patroller cannot stop the db2fmp process after it has started. The db2fmp process will try to complete execution even after Query Patroller has shut down the database connection that the db2fmp process requires for successful execution.
For more information about the db2fmp process, see the Application Development Guide: Programming Client Applications.
Both Query Patroller and the DB2 governor can be used against the same query submitter applications. For example, a submitting application such as DB2 CLP (db2bp.exe on Windows and db2bp on UNIX) can be listed as an application that is intercepted by Query Patroller as well as included in the governor configuration file.
Query Patroller intercepts queries at submission time, while the DB2 governor intercepts applications at query execution time. Since query submission occurs before query execution, Query Patroller will always intercept queries before the DB2 governor does. This means that if Query Patroller holds or queues a query, the DB2 governor must wait until the query is executed before intercepting the application that submitted the query.
A query that is intercepted by Query Patroller can be executed either by the submitting application or by another application called qprunquery.exe on Windows and qprunquery on UNIX. If the submitter's submission preferences specify that the submitting application has to wait until the query results are returned before releasing the application, then it is the submitting application that will execute the query. If the submitting application is listed in the DB2 governor configuration file, then the DB2 governor will intercept the submitting application when it executes the query.
If the submitter's submission preferences specify that the submitting application should be released and the query results sent to a result table, then the query is executed by qprunquery. In this situation, the DB2 governor will only intercept the application if qprunquery is included in the DB2 governor configuration file.
The Query Patroller documentation states that a list of Query Patroller commands can be obtained from the command line by entering "qp ?". This is incorrect. In order to view a list of Query Patroller commands, you must enter a database name as follows:
qp -d db_name ?
or
qp -d db_name help
qp -d db_name -u user_id -p password ?
or
qp -d db_name -u user_id -p password help
On rare occasions, when the status of a queued or running query is changed, Query Patroller may be unable to record the new status immediately. This usually happens in an abnormal termination situation such as one of the following:
Query Patroller server performs an automatic recovery at startup time and at periodic intervals. It checks for queries with a current status of queued or running and checks to see if the status is still accurate. If the current status is accurate, the query is handled normally and, if Query Patroller server was terminated and restarted, internal Query Patroller server data structure is restored. If, however, a query with a status of queued or running is found to no longer exist in DB2 because the DB2 server was terminated and restarted, or the Query Patroller was down and could not update the status of the query, then recovery will be performed on the query. The recovery action taken depends on whether the query was to return the results to a client application or to a DB2 result table:
When you are installing Query Patroller server please note the following:
When Query Patroller places a query in the queue, that query blocks the application the entire time it is in the queue, until the query is run.
When the DB2 connection concentrator is not activated, every application gets its own agent to manage the database connection until the application disconnects. When the concentrator is activated, all applications share a pool of agents which are switched between applications on transaction boundaries. This means that if the concentrator is enabled and Query Patroller queues queries, it ties up those agents until the queries are run. This would have the effect of reducing the pool of available agents and affect the performance of DB2 since applications would not be able to connect or execute a request due to their inability to acquire the services of an agent. For this reason, when the connection concentrator is activated, Query Patroller will not queue queries; instead, it will, by default, reject the queries which are supposed to be queued with an sqlcode 29009, reason code 6.
To prevent queries from being rejected when they are chosen to be queued, you can choose to allow Query Patroller to run queries instead of rejecting them when the concentrator is activated by setting the option BLOCK_OPTION at the system level, using the UPDATE QP_SYSTEM command, or at the user level, using the UPDATE SUBMITTER_PROFILE command. By default, BLOCK_OPTION is set to 'reject' ('R') specifying that queries are to be rejected rather than queued when the concentrator is activated. To specify that Query Patroller should run queries rather than reject them when the concentrator is activated, set BLOCK_OPTION to 'proceed' ('P').
For example, to allow Query Patroller to run queries against the database "sample" that would otherwise be rejected when the concentrator is activated, set BLOCK_OPTION option to 'P' as follows:
qp -d sample -u userid -p password "UPDATE QP_SYSTEM USING BLOCK_OPTION 'P'"
To allow Query Patroller to run queries submitted under the profile "STEVED" that would otherwise be rejected when the concentrator is activated, set BLOCK_OPTION for this profile to 'P' as follows:
qp -d sample -u userid -p password "UPDATE SUBMITTER_PROFILE FOR USER'STEVED' USING BLOCK_OPTION 'P'"
The values for BLOCK_OPTION are stored in the QP_SYSTEM and SUBMITTER_PROFILE tables for the database.
The BLOCK_OPTION setting for QP_SYSTEM is not nullable; the BLOCK_OPTION setting for SUBMITTER_PROFILE is nullable. If the BLOCK_OPTION is set both for QP_SYSTEM and for a user's submitter profile, the value for the submitter profile takes precedence for that user. For all other users, the BLOCK_OPTION setting for QP_SYSTEM applies. To ensure that the BLOCK_OPTION setting for QP_SYSTEM applies to a particular user, set the BLOCK_OPTION for that user's SUBMITTER_PROFILE to NULL.
Due to a JDBC limitation, DBCLOB objects can not be displayed in the Query Patroller Show result dialog window. Instead, an empty string will appear in place of DBCLOB objects in the dialog window. This limitation applies only to the Query Patroller Center, and not to the Query Patroller command line.
For each database that you want to use Query Patroller with, you need to run the qpsetup command. This will create, for each database, a set of Query Patroller control database objects, such as control tables, views and triggers associated with the tables, as well as user defined functions and procedures required for QP execution, for each database. The control tables contain information such as configuration settings, user profiles, and historical query data. For more information, see the Query Patroller Guide: Installation, Administration, and Usage or the Query Patroller information in the DB2 Information Center.
It is important to keep in mind that Query Patroller submitter and operator profile ids are case-sensitive. These profile IDs must also exist as DB2 authorization IDs. This means that if you create a submitter profile for a user identified as "TESTUSER" there must also be an existing DB2 authorization ID called "TESTUSER". If you create a submitter profile for a user identified as "testuser," this profile will not be associated with the DB2 authorization ID "TESTUSER" and will not be used by Query Patroller. Instead, any queries submitted under the "TESTUSER" ID will be associated with the default PUBLIC submitter profile.
Operator profiles do not need to be created for users with DBADM authority on a database. Such users already possess the maximum level of operator privileges, therefore adding operator profiles for them is redundant. It may also be misleading to create an operator profile for a user with DBADM authority since the user can automatically perform all Query Patroller tasks despite any restrictions on the operator privileges associated with the profile.
The response time of Query Patroller Center might slow down considerably if you are viewing several hundred managed or historical queries. To alleviate this problem, it is recommended that you apply a filter to the views to reduce the number of queries displayed. For information on how to apply filters in the Query Patroller Center, see the Query Patroller Guide: Installation, Administration, and Usage or the Query Patroller information in the DB2 Information Center.
The Query Patroller, Version 8 documentation incorrectly describes the parameter values for the WITH STATUS parameter of the LIST QUERIES command. The documentation indicates that a value of "R" will return all rejected queries, when in fact, this value specifies that a list of all running queries is returned.
There is also an error in the description of the default behaviour of the LIST QUERIES command. The documentation states that issuing the LIST QUERIES command without any parameters specified will return the last 100 queries. This is incorrect. The LIST QUERIES command issued without any parameters will return a list of all managed queries belonging to the current user. To view all managed queries, the "FOR USER ALL" must be specified.
The Query Patroller, Version 8 documentation states that a user with DBADM authority can run the qpstart command. This is incorrect. To issue the qpstart command, you must be the owner of the instance containing the database that you want to run Query Patroller on.
The documentation also states that you can run the qpsetup command if you have DBADM authority. This is incorrect. You must have SYSADM authority to run the qpsetup command on a database.
It is strongly recommended that you run the historical data generator (using the GENERATE HISTORICAL_DATA command) during periods of minimal database usage. Running this command during these off-peak hours minimizes the risk of a performance impact on the database.
In addition, it is recommended that you run the GENERATE HISTORICAL_DATA command on a regular basis to reduce the number of queries that data is being collected for at one time.
To stop the generation of historical data while the historical data generator is running, issue the following command:
generate historical_data stop
This will set a flag which is checked every 20 queries while the historical data generator is running. If this flag is set, the historical data generator will stop running. The query data that has been generated up to this point will be retained and will not be regenerated the next time that the historical data generator is run. However, the value that indicates when historical data was last collected (TIME_HIST_GENERATOR_LAST_RUN in the QP_SYSTEM control table) will not be updated.
When a query is run in the background, the results of the query are stored in a result table. Any query that will generate a result table is run by a process called qprunquery. This process creates a result table and issues a load from cursor to fill the table with the results of the query. This means that queries that produce result tables are subject to all of the same restrictions as any other load from cursor. For a complete description of these restrictions, see the documentation of the LOAD command in the DB2 Command Reference.
During each load performed by qprunquery, entries are placed in the db2diag.log file. On UNIX operating systems, one or more messages will be created in a subdirectory under the INSTANCE/db2dump directory, where INSTANCE is the directory where you installed DB2. On Windows, one or more messages will be created in a subdirectory under the directory specified in the diagpath database configuration parameter. The name of the message file subdirectory is generated based on the details of the load operation. For example, the following is the name of a generated message file subdirectory:
qpTbLoad_SAMPLE_349_2003-05-21-16.51.32
where:
The message file name contained in this subdirectory would be as follows.
qpTbLoad_SAMPLE_349_2003-05-21-16.51.32.MSG.*
.
The message files are deleted once the load completes successfully. To aid in problem determination, the message files are not deleted if the load fails.
There is a limit to the number of simultaneous loads that can be run in parallel. Exceeding this limit results in an aborted query and an error SQL6555 recorded in the qpdiag.log file. If this error is encountered, you can remedy the situation by changing the range specified by the DB2ATLD_PORTS registry variable, which determines the number of parallel loads permitted at once. To calculate the approximate number of ports required in your system, decide on the maximum number of loads that need to be run at one time, including those issued by qprunquery and other load operations. Multiply this number by the number of logical partitions per physical partition in your environment. Add 25% to this amount.
To set the DB2ATLD_PORTS registry variable, issue the following command:
db2set DB2ATLD_PORTS=num1:num2
where num1< num2
Query Patroller uses a default of 6000 ports in the range 50000-56000; setting DB2ATLD_PORTS will override this value.
When Query Patroller is managing a large number of queries, and the Query Controller or the Query Patroller Center is running, you may receive an out of memory error even when there is sufficient memory available on the machine. In order to be able to use more of the available memory, you can increase the java heap environment variable settings from their default levels.
The environment variables to update are QP_INIT_JAVA_HEAP_SIZE and QP_MAX_JAVA_HEAP_SIZE. If these variables are not set, the default is 32 mb and 512 mb, respectively.
IBM may not offer the products, services, or features discussed in this document in all countries. Consult your local IBM representative for information on the products and services currently available in your area. Any reference to an IBM product, program, or service is not intended to state or imply that only that IBM product, program, or service may be used. Any functionally equivalent product, program, or service that does not infringe any IBM intellectual property right may be used instead. However, it is the user's responsibility to evaluate and verify the operation of any non-IBM product, program, or service.
IBM may have patents or pending patent applications covering subject matter described in this document. The furnishing of this document does not give you any license to these patents. You can send license inquiries, in writing, to:
IBM Director of LicensingFor license inquiries regarding double-byte (DBCS) information, contact the IBM Intellectual Property Department in your country/region or send inquiries, in writing, to:
IBM World Trade Asia CorporationThe following paragraph does not apply to the United Kingdom or any other country/region where such provisions are inconsistent with local law: INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS PUBLICATION "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY, OR FITNESS FOR A PARTICULAR PURPOSE. Some states do not allow disclaimer of express or implied warranties in certain transactions; therefore, this statement may not apply to you.
This information could include technical inaccuracies or typographical errors. Changes are periodically made to the information herein; these changes will be incorporated in new editions of the publication. IBM may make improvements and/or changes in the product(s) and/or the program(s) described in this publication at any time without notice.
Any references in this information to non-IBM Web sites are provided for convenience only and do not in any manner serve as an endorsement of those Web sites. The materials at those Web sites are not part of the materials for this IBM product, and use of those Web sites is at your own risk.
IBM may use or distribute any of the information you supply in any way it believes appropriate without incurring any obligation to you.
Licensees of this program who wish to have information about it for the purpose of enabling: (i) the exchange of information between independently created programs and other programs (including this one) and (ii) the mutual use of the information that has been exchanged, should contact:
IBM Canada LimitedSuch information may be available, subject to appropriate terms and conditions, including in some cases payment of a fee.
The licensed program described in this document and all licensed material available for it are provided by IBM under terms of the IBM Customer Agreement, IBM International Program License Agreement, or any equivalent agreement between us.
Any performance data contained herein was determined in a controlled environment. Therefore, the results obtained in other operating environments may vary significantly. Some measurements may have been made on development-level systems, and there is no guarantee that these measurements will be the same on generally available systems. Furthermore, some measurements may have been estimated through extrapolation. Actual results may vary. Users of this document should verify the applicable data for their specific environment.
Information concerning non-IBM products was obtained from the suppliers of those products, their published announcements, or other publicly available sources. IBM has not tested those products and cannot confirm the accuracy of performance, compatibility, or any other claims related to non-IBM products. Questions on the capabilities of non-IBM products should be addressed to the suppliers of those products.
All statements regarding IBM's future direction or intent are subject to change or withdrawal without notice, and represent goals and objectives only.
This information may contain examples of data and reports used in daily business operations. To illustrate them as completely as possible, the examples include the names of individuals, companies, brands, and products. All of these names are fictitious, and any similarity to the names and addresses used by an actual business enterprise is entirely coincidental.
COPYRIGHT LICENSE:
This information may contain sample application programs, in source language, which illustrate programming techniques on various operating platforms. You may copy, modify, and distribute these sample programs in any form without payment to IBM for the purposes of developing, using, marketing, or distributing application programs conforming to the application programming interface for the operating platform for which the sample programs are written. These examples have not been thoroughly tested under all conditions. IBM, therefore, cannot guarantee or imply reliability, serviceability, or function of these programs.
Each copy or any portion of these sample programs or any derivative work must include a copyright notice as follows:
(C) (your company name) (year). Portions of this code are derived from IBM Corp. Sample Programs. (C) Copyright IBM Corp. _enter the year or years_. All rights reserved.
The following terms are trademarks of International Business Machines
Corporation in the United States, other countries, or both, and have been used
in at least one of the documents in the DB2 UDB documentation library.
|
ACF/VTAM AISPO AIX AIXwindows AnyNet APPN AS/400 BookManager C Set++ C/370 CICS Database 2 DataHub DataJoiner DataPropagator DataRefresher DB2 DB2 Connect DB2 Extenders DB2 OLAP Server DB2 Query Patroller DB2 Universal Database Distributed Relational Database Architecture DRDA eServer Extended Services FFST First Failure Support Technology IBM IMS IMS/ESA iSeries |
LAN Distance MVS MVS/ESA MVS/XA Net.Data NetView OS/390 OS/400 PowerPC pSeries QBIC QMF RACF RISC System/6000 RS/6000 S/370 SP SQL/400 SQL/DS System/370 System/390 SystemView Tivoli VisualAge VM/ESA VSE/ESA VTAM WebExplorer WebSphere WIN-OS/2 z/OS zSeries |
The following terms are trademarks or registered trademarks of other companies and have been used in at least one of the documents in the DB2 UDB documentation library:
Microsoft, Windows, Windows NT, and the Windows logo are trademarks of Microsoft Corporation in the United States, other countries, or both.
Intel and Pentium are trademarks of Intel Corporation in the United States, other countries, or both.
Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.
UNIX is a registered trademark of The Open Group in the United States and other countries.
Other company, product, or service names may be trademarks or service marks of others.