DB2 Query Patroller Installation Guide

Appendix A. Manual Installation

To install DB2 Query Patroller using installp or smit, perform the steps listed below. Refer to Manual Installation Commands for detailed syntax and parameter information.

Server

1. Set up or create a DB2 UDB EEE instance to use with DB2 Query Patroller.

2. Add an entry in the etc/services file to be used with the DB2 Query Patroller server. For example, dqp1 55000/TCP.

3. Create a user named iwm if one does not exist already.

4. Install the appropriate filesets.

   1. Mount the CD.

   2. Go to the /cdrom/db2 directory

   3. For AIX, use smit to install the following filesets for the DB2 Query Patroller server:

      • db2_06_01.srv

      • db2_06_01.agt

      • db2_06_01.qlic

   4. For Query Patroller Agent on AIX install: db2_06_01.agt.

   5. For Solaris, use pkgadd to install the following packages for the DB2 Query Patroller server:

      • db2dqps61

      • db2dqpa61

      • db2qlic61

   6. For a DB2 Query Patroller agent on Solaris install: db2dqpa61.

5. If you have installed the server set up the license as follows:

6. Add the user iwm to the primary group for the DB2 EEE instance owner. This will give the iwm user SYSADM authority over the instance.

7. Add the following lines to the .profile file of the iwm user. The INSTHOME variable is the home directory of the DB2 Query Patroller server instance.

   . INSTHOME/sqllib/db2profile

   . INSTHOME/sqllib/dqpprofile

   Note:

   If a C shell is being used, add source /sqllib/db2cshrc and source /sqllib/dqpcshr to the .login file.

8. Log on as root, and run the following command:

   dqpcrt -s -p port_name instance_name

   The port_name variable is the port name you used in step 2. instance_name is the name of the DB2 UDB EEE instance. Refer toManual Installation Commands for detailed syntax and parameter information.

   Note:

   To remove an instance you can run the dqpdrop instance_name command. Refer to the Commands section for detailed syntax and parameter information. You can only run this command on the node where the server is set up.

9. Log on as the instance name. Run the following command:

   dqpsetup -D database_name -g nodegroup_name -n node_number -t tablespace_name -r result_tablespace_name -l tablespace_path

   Refer to Manual Installation Commands for detailed syntax and parameter information.

10. Use the db2licm command to register DB2 Query Patroller. See the DB2 Command Reference for further information.

Agent

1. Create a user named iwm if one does not already exist.

2. Add the user iwm to the primary group for the DB2 EEE instance owner. This will give the iwm user SYSADM authority over the instance.

3. Add the following lines to the .profile file of the iwm user. The INSTHOME variable is the home directory of the DB2 Query Patroller server instance.

   . INSTHOME/sqllib/db2profile

   . INSTHOME/sqllib/dqpprofile

   Note:

   If a C shell is being used add source /sqllib/db2cshrc source /sqllib/dqpcshr to the .login file.

4. The same DB2 EEE instance used for the DB2 Query Patroller server must be set up for this node. To find out the name of the DB2 UDB EEE instance being used by the DB2 Query Patroller server, you can run the dqplist command on the DB2 Query Patroller server node. Refer to Manual Installation Commands for detailed syntax and parameter information.

5. Add an entry to the /etc/services file. This entry must be the same as the entry made in the etc/services file for the server installation.

6. Log on as root and enter the following command:

   dqpcrt -a -p port_name instance_name

   Refer to Manual Installation Commands for detailed syntax and parameter information.

Creating the Query Patroller Schema and Binding the Application Bind Files

To manually create the DB2 Query Patroller schema and bind all the application bind files perform the following steps:

1. Create the DB2 tablespace that will be used for the DB2 Query Patroller schema.

2. Use the program db2_qp_schema in the DB2 bin directory to create the schema. This program will use the script file iwm_schema.sql as input. db2_qp_schema supports either syntax:

   db2_qp_schema <schema_input_filename> <database_alias> <user> <password> <querypatroller_tablespace>

   db2_qp_schema <schema_input_filename> <database_alias> <querypatroller_tablespace>

3. Bind the DB2 Query Patroller server bind files using the bind file list file db2qp.lst in the DB2 bnd directory. After connecting to the database, issue the DB2 CLP command:

   db2 bind @db2qp.lst blocking all grant public

4. Run the following command:

   db2 bind iwmsx001.bnd isolation ur blocking all grant public insert buf datetime iso

5. Bind the DB2 Query Patroller stored procedure bind files using the bind file list file db2qp_sp.lst in the DB2 bnd directory. After connecting to the database, issue the DB2 CLP command:

   db2 bind @db2qp_sp.lst blocking all

6. Create a tablespace for the DB2 Query Patroller result tables.

Manual Installation Commands

dqpcrt

This command is used to allocate a node on the DB2 UDB EEE system as a DB2 Query Patroller server or agent. The port name to be used with the DB2 Query Patroller instance, and the name of the DB2 UDB EEE instance designated as the DB2 Query Patroller server, are required parameters. Syntax:

>>-dpqcrt----+--s---p--port_name instance_name--+--------------><
             +--a---p--port_name instance_name--+
             '--h-------------------------------'
 

Table 6. dqpcrt Command Parameters

Parameter	Description
-s	Used to create a DB2 Query Patroller server on the named DB2 UDB EEE instance.
-a	Used to create a DB2 Query Patroller agent on the named DB2 UDB EEE instance.
port_name	Identifies the port name to be used with the DB2 Query Patroller server or agent.
instance_name	Identifies the name of the DB2 UDB EEE instance that is to designated as a DB2 Query Patroller server instance.
-h	Displays command usage information.

dqpsetup

This command is used to set the parameters for the DB2 Query Patroller server. The size_DMS parameter and the -o flag are optional. The -o flag can be used to remove schema objects from a previously installed version of this product. Syntax:

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

Table 7. dqpsetup Command Parameters

Parameter	Description
-d database_name	Name of the database to be used with the DB2 Query Patroller server.
-g nodegroup_name	Name of the nodegroup that contains the tablespace for the DB2 Query Patroller server.
-n node_number	Node number of a single node where the nodegroup is defined.
-t tablespace_name	Name of the DB2 Query Patroller tablespace. The default type is an SMS tablespace.
-r result_tablespace_name	Name of the Result Table Space to be used
-l tablespace_path	Full path name of the tablespace.
-s size_DMS	Size of the DMS tablespace. Use the -s flag to specify the size for the DMS tablespace. This parameter is optional and only specified if a DMS tablespace is to be used. The default is an SMS tablespace.
-o	Overwrites any existing DB2 Query Patroller schema objects. This parameter is optional.
instance_name	Name of the DB2 UDB EEE instance that is to be designated as a DB2 Query Patroller server.
-h	Displays command usage information.

dqplist

This command is used to find the name of the DB2 UDB EEE instance being used as the DB2 Query Patroller server. It can only be run from the node where the DB2 Query Patroller server was created. Syntax:

>>-dpqlist----+-----+------------------------------------------><
              '--h--'
 

The -h flag displays command usage information.

dqpdrop

This command is used to drop an existing DB2 Query Patroller server instance. This command can only be run from the node where the DB2 Query Patroller server was created. Syntax:

>>-dpqdrop----+-instance_name--+-------------------------------><
              '--h-------------'
 

The -h flag provides usage information. The instance_name parameter is the name of the DB2 Query Patroller instance that you want to drop.

[ DB2 List of Books | Search the DB2 Books ]