IBM DB2 Query Patroller Installation Guide

Performing the Installation

This section provides all the information needed to install a DB2 Query Patroller Server on a UNIX-based workstation. Before performing the steps in this section, be sure that you have read and are familiar with the information in Before You Begin.

Sometimes display problems can occur when running the db2setup utility. If your display encounters these problems, you can press CTRL+L or F5 at any time to refresh your current screen.
To avoid most potential display problems, we recommend that you install your DB2 product in a virtual console session. A virtual console session is a terminal window outside of the standard graphical interface that is installed with many UNIX-based workstations.

To install a DB2 Query Patroller Server, perform the following steps:

Step  1.
Log on to your workstation as a user with root authority.
Step  2.
Insert and mount your product's CD-ROM. If you are not sure how to mount the CD-ROM drive on your operating system, refer to the "Basic Task Knowledge" appendix in your DB2 server's Quick Beginnings manual.
Step  3.
Change to the directory where the CD-ROM is mounted by entering the following command:
cd cdrom

where cdrom is the mount point of the CD-ROM drive.
Step  4.
Enter the ./db2setup command. The DB2 Setup Utility opens.

You can generate a trace log, called /tmp/db2setup.trc, to record any errors experienced during an installation. To generate this log file, enter the db2setup command as follows:
db2setup -d

Step  5.
Select Install and press Enter. The Install DB2 V7 window opens.
Step  6.
From the product list, select DB2 Query Patroller Server.
Press Tab to move between highlighted options and fields, and Enter to select or deselect an option. For more information or assistance with a screen or its options, select Help.
If you want to customize your installation, select the Customize option to the right of the item that you want to customize.
Step  7.
Complete the fields in the remaining windows that you are presented with by the db2setup utility.
When the installation completes, the DB2 Query Patroller Server software is installed in the same location as the DB2 software on your server. The installation path for all DB2 software depends on the operating system:
/usr/lpp/db2_07_01 on AIX /opt/IBMdb2/V7.1 on HP-UX, NUMA-Q/PTX, or Solaris

Post-Installation Tasks for Migration

If you are not migrating from a previous installation of DB2 Query Patroller, you do not need to perform any post installation tasks. Go to Verifying the Installation.

If you are migrating from a previous installation of DB2 Query Patroller, there are certain tasks that you need to complete to finish the installation.

First, you need to ensure that all of your instances and databases have been migrated to the Version 7 format. If you installed both your DB2 server and DB2 Query Patroller before performing any migration steps, refer to your Quick Beginnings manual for the necessary steps to migrate your DB2 products and then run the dqpsetup command described below.

If you had already installed and migrated your DB2 server before installing DB2 Query Patroller, you need to run the db2iupdt command for each instance that you want to use with DB2 Query Patroller. To run this command, you require root authority on your server. For details on this command, refer to the Command Reference.

After you have completed the migration of any DB2 products, you need to run the dqpsetup command to set the parameters for the DB2 Query Patroller Server. To run this command, you must be logged on to the system as the owner of the DB2 Query Patroller instance. If you did not specify a different user during the installation, this is the iwm user.

If you are migrating from a previous release of DB2 Query Patroller, and you want to maintain the same settings that you used before migration, you can enter the dqpsetup command without any parameters.

The syntax of the dqpsetup command is:

>>-dqpsetup----+-----------------------+-----------------------><
               +-| setup parameters |--+
               '--h--------------------'
 
setup parameters
 
|----d--database_name-----g--nodegroup_group-------------------->
 
>------n--node_number-----t--tablespace_name-------------------->
 
>------l--tablespace_path----+---------------+------------------>
                             '--s--size_DMS--'
 
>------r--result_tablespace_name----+-----+---instance_name-----|
                                    '--o--'

where:

-d database_name: The name of the database where the queries are run that you want to trap and monitor on the DB2 Query Patroller Server. This parameter is required.
-g nodegroup_name: The name of the nodegroup that contains the meta-data table space for the DB2 Query Patroller Server. This parameter is required if you are using DB2 Enterprise - Extended Edition. If you are using DB2 Enterprise Edition, this parameter is ignored.
-n node_number: The node number of the partition database server where the nodegroup, specified by the -g flag, is defined. This parameter is required if you are using DB2 Enterprise - Extended Edition. If you are using DB2 Enterprise Edition, this parameter is ignored.
-t tablespace_name: The name of the DB2 Query Patroller meta-data table space. The default table space created by the db2setup utility is an SMS table space. If the table space does not exist, the dqpsetup will create the table space on your behalf. In a DB2 Enterprise - Extended Edition environment, this table space must reside in a single partitioned nodegroup. For information on the differences between SMS and DMS table spaces, refer to the Administration Guide. This parameter is required.
-l tablespace_path: The absolute or relative of the table space defined by the -t flag. To specify an absolute directory, the directory you specify should begin with a /. For example, /SMSA. To specify a directory that is relative to the instance owner's home directory, just enter the directory. For example, SMSA. This parameter is required.
-s size_DMS: The size of the DMS table space. This parameter is only required if the table space defined by the -t flag is not an SMS table space. For information on the differences between SMS and DMS table spaces, refer to the Administration Guide.
-r result_tablespace: The name of the DB2 Query Patroller Result table space. The default result table space created by the db2setup utility is an SMS table space. If the table space does not exist, the dqpsetup will create the table space on your behalf. In a DB2 Enterprise - Extended Edition environment, this table space can reside in a single partitioned or multipartitioned nodegroup. For information on the differences between SMS and DMS table spaces, refer to the Administration Guide. This parameter is required.
-o: Overwrites any existing DB2 Query Patroller schema objects. This parameter is optional.
instance_name: The name of the instance where the database that the queries are run against resides.
-h: Displays detailed help for this command.

For example, to set up a DB2 Query Patroller Server that is running a DB2 Enterprise Edition server to capture queries issued against the SAMPLE database in the db2inst1 instance, using the SMSA table space which is located in the /SMSA directory, with the RESULTS resultant table space, enter the following command:

   dpqsetup -d SAMPLE -t SMSA -r RESULTS -l /SMSA db2inst1

Verifying the Installation

After you have installed the DB2 Query Patroller Server, you can verify your installation by submitting a query to the database that you set up to be monitored by DB2 Query Patroller and checking it's status.

To verify your DB2 Query Patroller installation, perform the following steps:

Step  1.
Log on to the DB2 Query Patroller Server with the iwm user that was created before or during the installation.
Step  2.
Start the DB2 Query Patroller service by entering the dqpstart command. You should receive output that is similar to the following:
DB29997I "DB2 Query Patroller" started successfully.

Step  3.
Submit a query that will be captured by the DB2 Query Patroller Server by entering the following command:
iwm_submit -S "select count(*) from sysibm.systables"

You should receive output that is similar to the following:
DB2 Query Patroller Version "7.1" (c) Copyright International Business Machines Corp. 1998, 2000. All rights reserved. DB2-09973-I Job "1" queued. (Cost = "1").

This verifies that the query was passed to the DB2 Query Patroller Server. Allow a few moments for the job to process.
Step  4.
Check the status of the query you just submitted by entering the following command:
iwm_cmd

If the job has completed successfully, you should receive output that is similar to the following:
DB2 Query Patroller Version "7.1" (c) Copyright International Business Machines Corp. 1998, 2000. All rights reserved. Job Status (user "iwm") --- ---------------------------------------------------------------------- 1 Done at "2000-02-07 15:16:03.015001" : "1" rows in "iwm.JOB0000001_RES

If the job is still in progress, you would receive output such as:
DB2 Query Patroller Version "7.1" (c) Copyright International Business Machines Corp. 1998, 2000. All rights reserved. Job Status (user "iwm") --- ------------------------ 1 Queued: Priority is "500"

Step  5.
Stop the DB2 Query Patroller service by entering the dqpstop command. You should receive the following output:
DB29998I "DB2 Query Patroller" stopped successfully.

If DB2 Query Patroller does not start or the query does not run, examine the syserr.log file, located in the INSTHOME/sqllib/instance_name/log directory, where:

INSTHOME is the home directory of the DB2 Query Patroller instance owner.
instance_name is the name of the instance where the database that you submitted the query to resides.

The syserr.log file may contain error messages related to the failed query or failed operations with the DB2 Query Patroller Server. For more information, refer to the DB2 Query Patroller Administration Guide or the Troubleshooting Guide.

This completes the installation and verification steps required for a DB2 Query Patroller Server. If you want to manage queries for a database that was created after the installation of DB2 Query Patroller, refer to the DB2 Query Patroller Administration Guide.

Installing the DB2 Query Patroller Agent

If you installed DB2 Query Patroller Server in a DB2 Enterprise - Extended Edition environment, you can optionally install a DB2 Query Patroller Agent on all of the database partition servers in the instance. Installing multiple agents will help balance the workload across your partitioned database system, much like the balancing that occurs from configuring multiple coordinator database partitions for your client connections.

If you installed a DB2 Query Patroller Server in a DB2 Enterprise Edition environment, you do not need to install a DB2 Query Patroller Agent.

To install a DB2 Query Patroller Agent, simply log on to a database partition server and run the db2setup utility as you did in Performing the Installation. This time, select to install the DB2 Query Patroller Agent software instead of the DB2 Query Patroller Server software that you selected beforehand. Each instance can only have one DB2 Query Patroller Server installation in a DB2 Enterprise - Extended Edition environment.

Whenever you install a DB2 Query Patroller Agent on a database partition server, you must add an entry to the dqpnodes.cfg file on the DB2 Query Patroller Server if you want to start the agent when the server is started. This entry describes the server where you installed the DB2 Query Patroller Agent. You do not need to add an entry to this file for a particular database partition server if you do not want to start the agent on it. The dpqstart and dpqstop commands use the dqpnodes.cfg file to synchronize start and stop processes on the listed database partition servers. For more information, refer to the DB2 Query Patroller Administration Guide.

Installing the DB2 Query Patroller Client

In order for your users to have their queries trapped and routed to a DB2 Query Patroller Server in a UNIX environment, or to manage a DB2 Query Patroller Server you must install the DB2 Query Patroller Client on a DB2 client workstation.

To install a DB2 Query Patroller Client, simply log on to a DB2 client workstation and run the db2setup utility as you did in Performing the Installation. This time, select to install the DB2 Query Patroller Client software.

[ Top of Page | Previous Page | Next Page ]

Step 1.	Log on to the DB2 Query Patroller Server with the iwm user that was created before or during the installation.
Step 2.	Start the DB2 Query Patroller service by entering the dqpstart command. You should receive output that is similar to the following: DB29997I "DB2 Query Patroller" started successfully.
Step 3.	Submit a query that will be captured by the DB2 Query Patroller Server by entering the following command: iwm_submit -S "select count(*) from sysibm.systables" You should receive output that is similar to the following: DB2 Query Patroller Version "7.1" (c) Copyright International Business Machines Corp. 1998, 2000. All rights reserved. DB2-09973-I Job "1" queued. (Cost = "1"). This verifies that the query was passed to the DB2 Query Patroller Server. Allow a few moments for the job to process.
Step 4.	Check the status of the query you just submitted by entering the following command: iwm_cmd If the job has completed successfully, you should receive output that is similar to the following: DB2 Query Patroller Version "7.1" (c) Copyright International Business Machines Corp. 1998, 2000. All rights reserved. Job Status (user "iwm") --- ---------------------------------------------------------------------- 1 Done at "2000-02-07 15:16:03.015001" : "1" rows in "iwm.JOB0000001_RES If the job is still in progress, you would receive output such as: DB2 Query Patroller Version "7.1" (c) Copyright International Business Machines Corp. 1998, 2000. All rights reserved. Job Status (user "iwm") --- ------------------------ 1 Queued: Priority is "500"
Step 5.	Stop the DB2 Query Patroller service by entering the dqpstop command. You should receive the following output: DB29998I "DB2 Query Patroller" stopped successfully.