IBM DB2 Query Patroller Administration Guide
This section provides information on creating user or
group profiles (adding users) for the DB2 Query Patroller system, editing user
profiles, and steps for removing user profiles. In order for a
user's queries to be managed by DB2 Query Patroller, the user must be
defined in the DB2 User Profile table, or belong to a group that has a profile
in the User Profile table. The task of adding user or group profiles is
performed using the QueryAdministrator tool.
In order to be added to the DB2 Query Patroller system, a
user must have a system account on the target database server system with the
CONNECT database privilege for DB2. Because DB2 Query Patroller for DB2
UDB Version 7 offers group support, a user may also belong to a group that has
been granted the CONNECT privilege. For information on creating DB2
users and groups and granting the CONNECT privilege, refer to the
DB2 Administration Guide.
Once the user has a system account on the target database server with the
CONNECT privilege, or belongs to a group with the CONNECT privilege, you can
add the user or group to the DB2 Query Patroller system using the User
Administration page of the QueryAdministrator tool. When a user or
group is added using the DB2 QueryAdminstrator tool, the information provided
for the user or group is added to the User Profile table.
If a user belongs to more than one group defined in the
DB2 Query Patroller User Profile table, the authorities for the two profiles
will be merged, and the highest authorities will be granted to the
user. For example, user jsmith belongs to Group A and Group B.
If the Group A profile specifies that jsmith can run a maximum of 20 queries
simultaneously, and the Group B profile specifies that the maximum is 30,
jsmith will have the authority to run 30 queries simultaneously. The
same logic applies for a user who is defined as both an individual user and as
part of a group.
For the Management Threshold parameter, the highest value is considered to
have the greatest authority. The Management Threshold parameter value
determines whether or not a query will be intercepted by the QueryEnabler tool
for user intervention. If a query does not meet this threshold value,
the query will not be intercepted by the QueryEnabler tool, and will execute
against the data source immediately, without user intervention.
If a user that belongs to more than one group, and a value for either the
e-mail address parameter or User ID parameter is not provided in one group,
the user's groups will be searched in alphabetical order by group name
for the first non-null parameter value. See Creating a User Profile for more information about user profile parameters.
To query the effective user profile for a user that belongs to more than
one group, or for a user that is defined individually, and as part of a group,
you can use the Query button on the User Administration page of the
QueryAdministrator tool. For more information, see Querying for the Effective User Profile.
Use the following procedure to create a user profile:
- In the QueryAdministrator main window, select the User
Administration tab.
- Click on Create User.
A user profile window opens:
- Enter a value in each of the fields and click on OK when
finished.
The following list provides information for each user profile
parameter:
- User ID
- Provides the ID for the user or group.
- Type
- Indicates whether the profile is for a user or a group.
- Cost Analysis
- Provides a setting for cost analysis:
- Do Cost Analysis indicates that cost analysis is performed on
all jobs for this user or group.
- Don't Do Cost Analysis indicates that the user or group
can determine whether cost analysis is to be performed on specific
jobs. If this option is selected, the user can use the Cost
analyze query check box in the QueryEnabler window to enable or disable
cost analysis for a specific job.
- Authority Level
- Provides the authority level for the user or group:
- Not Allowed indicates that this user or group is not allowed to
use DB2 Query Patroller.
- User indicates that this user or group has normal authority to
submit queries and manage the status of those jobs in the DB2 Query Patroller
system.
- Operator indicates that the user or group has operator
authority. With operator authority a user can use the QueryMonitor tool
to manage jobs for all users and groups.
- Administrator indicates that this user or group has
administrative authority to alter the DB2 Query Patroller configuration and
manage jobs for all users and groups.
- Maximum Queries
- Indicates the maximum number of jobs that a user is able to run
simultaneously. Maximum Queries must be an integer from 0 to 99.
When creating a group profile, note that the value set for this parameter
applies to each user. If this value were set to 10 for Group A, each
user belonging to Group A has the authority to run 10 jobs
simultaneously.
- Low Priority
- Provides the priority assigned to a job when the job is submitted on low
priority. Low Priority must be an integer from 0 to 999.
- Normal Priority
- Provides the priority assigned to a job when the job is submitted on
normal priority. Normal Priority must be an integer from 0 to
999.
- High Priority
- Provides the priority assigned to a job when the job is submitted on high
priority. High Priority must be an integer from 0 to 999.
- User Threshold
- Provides the threshold, which if exceeded by a job, causes the job to be
placed on hold. User threshold is measured in timeron units. A
timeron is a unit of measurement used to give a rough relative estimate of the
resources, or cost, required by the database server to execute two plans for
the same query. The resources calculated in the estimate include
weighted CPU and I/O costs. Refer to the DB2 Administration Guide for more information.
- Management Threshold
-
This value determines whether or not a dynamic SQL query will be trapped by
the QueryEnabler component. If the cost of the query does not exceed
the set value, the query will not be trapped by the QueryEnabler and will
execute without user intervention. Query cost is measured in timeron
units. A timeron is a unit of measurement used to give a rough relative
estimate of the resources, or cost, required by the database server to execute
two plans for the same query. The resources calculated in the estimate
include weighted CPU and I/O costs. Refer to the DB2 Administration Guide for more information.
- Maximum Elapsed Time
- This option specifies the maximum number of seconds the query will be
permitted to run against the DB2 Query Patroller server. If this value
is set to 0 or -1 the query will always run to completion.
- Maximum Result Rows
- This option specifies the maximum number of rows that will be returned in
the answer set. If this value is set to 0, the complete answer set will
be returned.
- Account ID
- Provides a user-defined accounting ID that is written in the Job
Accounting table.
- Email Address
- Provides an address for notification when a job completes. If Email
Address is null, the user ID is used as the e-mail address. For more
information on e-mail notification, refer to DB2 Query Patroller E-Mail Notification.
Use the following procedure to edit a user profile:
- In the QueryAdminstrator main window, select the User
Administration tab.
- Click on List All Users to list all user profiles.
- Select a profile.
- Click on View / Edit. The Detailed User Profile window
opens.
- Edit each field that you want to change.
| Note: | Once a profile has been created, you cannot change the User ID or
Type.
|
- Click on OK after all new values have been entered.
Use the following procedure to remove a user profile:
- On the User Administration page, select a profile.
- Click on Remove.
- Click on Yes to verify that you want to remove the selected
user profile.
| Note: | User iwm cannot be deleted.
|
To query the effective user profile for a user, you can
use the Query button on the User Administration page of the
QueryAdministrator tool. Perform the following steps:
- Click the Query button on the User Administration page.
- Enter a User ID and click Apply. The different profiles
for the user will be merged providing an effective user profile, which
combines the highest authorities from each of the user's profiles.
For information on how an effective user profile is resolved when more than
one profile exists for a user, see How User Profiles are Resolved.
[ Top of Page | Previous Page | Next Page ]