Topics for installing, migrating to, and configuring for IOQWT 3.1.1
Installing IBM InfoSphere Optim Query Workload Tuner, Version
3.1.1
Installing IBM InfoSphere Optim Query Workload Tuner,
Version 3.1.1 installs two components on your workstation:
- IBM Data Studio full client, Version 3.1.1
- IBM Data Studio provides an integrated development environment
for robust database application development and database management
for database servers. It includes a limited set of features for tuning
that are free of charge.
- A License Activation Kit for InfoSphere Optim Query Workload Tuner,
Version 3.1.1
- This kit allows you to activate the full set of features for tuning
query workloads and single SQL statements. When you use the IBM Data
Studio full client to start tuning, the client will install and activate
a license on the connected DB2 for Linux, UNIX, and Windows database
or DB2 for z/OS subsystem. When you and other people subsequently
connect to that database through the IBM Data Studio full client or
administration client, you and they can use the full set of tuning
features.
There are two different License Activation Kits:
- InfoSphere Optim Query Workload Tuner for DB2 for Linux, UNIX,
and Windows License Activation Kit
- InfoSphere Optim Query Workload Tuner for DB2 for z/OS License
Activation Kit
Your product DVD or the compressed file that you downloaded from
IBM Passport Advantage contains the kit that corresponds to the product
that you purchased.
You can install both components
or only one of them, depending on your requirements.
- You can install both if neither of them are yet installed on your
workstation and you plan to use the License Activation Kit to activate
licenses on DB2 databases or subsystems.
- You can install the Data Studio full client alone, if you have
no need to install the license activation kit.
- You can install the license activation kit alone if the Data Studio
full client 3.1.1 is already installed on your workstation.
You can also choose whether to install these components
through a wizard or silently with a response file.
Installing Data Studio and the License Activation Kit for IBM
InfoSphere Optim Query Workload Tuner through a wizard
Before you begin
Ensure that your workstation meets the system requirements.
About this task
Procedure
- On the product DVD or in the extracted content of the compressed
file downloaded from IBM Passport Advantage, double-click either of
these files, depending on your operating system:
- On Windows operating systems: setup.exe
- On Linux operating systems: setup
- In the launchpad, select Install Product and select the
type of installation that you want to perform.
- On the Select Packages page of the wizard, select both
packages.
- On the Install Packages page, follow either of these steps:
- If you have the InfoSphere Optim Query Tuner client, Version
3.1 or IBM Data Studio full client, Version 3.1 installed and you
want to upgrade to IBM Data Studio full client, Version 3.1.1, select
the package group in which the product that you want to replace appears.
The Installation Manager wizard will uninstall that product before
installing IBM Data Studio full client, Version 3.1.1.
Note:
InfoSphere
Optim Query Tuner client, Version 3.1 and IBM Data Studio full client,
Version 3.1 could be in separate package groups or the same package
group. If they are in separate package groups, choose one of the package
groups. Installation Manager will not uninstall the client that is
in the other package group.
- If you do not have the InfoSphere Optim Query Tuner client,
Version 3.1 or IBM Data Studio full client, Version 3.1 installed,
create a new package group.
- Finish the remaining steps in the wizard.
What to do next
If you need to configure DB2 for z/OS subsystems for tuning,
see Configuring a DB2 for z/OS subsystem for query and query-workload
tuning with InfoSphere Optim Query Workload Tuner.
If your subsystems
were already configured for tuning with IBM InfoSphere Optim Query
Workload Tuner for DB2 for z/OS, Version 3.1 and a license for that
version is active on the subsystem, you can start IBM Data Studio
full client and start tuning.
Installing Data Studio and the License Activation Kit for IBM
InfoSphere Optim Query Workload Tuner silently
Before you begin
Ensure that any version of IBM Installation Manager from 1.3.3
through 1.5 is installed on your workstation.
About this task
This task involves generating a response file on one workstations,
and then performing the installation with the response file on other
workstations.
Restrictions:
- If you generate a response file on a workstation that is running
a Windows operating system, you must perform the silent installation
with that response file on another workstation that is running a Windows
operating system.
- If you generate a response file on a workstation that is running
a Linux operating system, you must perform the silent installation
with that response file on another workstation that is running a Linux
operating system.
Procedure
- Generate a response file:
- At a command line, change to the eclipse subdirectory
in the directory where IBM Installation Manager is installed.
- On Windows, for example: cd C:\Program Files\IBM\Installation
Manager\eclipse
- On Linux, for example: cd /opt/IBM/Installation Manager/eclipse
- Run one of these commands to start IBM Installation
Manager.
Note:
- The commands as shown will not install InfoSphere Optim Query
Workload Tuner on your workstation, and will generate a response file
only, in addition to a log file. If you want to install InfoSphere
Optim Query Workload Tuner on your workstation, remove the -skipInstall
option from the command that you run.
- Ensure the file paths you enter exist; Installation Manager will
not create directories for the response file and the log file.
If the users on the workstations where you plan to
install IBM InfoSphere Optim Query Workload Tuner have administrative
rights:
- For Windows: IBMIM.exe -record <response file and path
name> -log <log file and path name> -skipInstall <full
path name of a writable directory>
- For Linux: IBMIM -record < full path name of the response
file> -log <log file and path name> -skipInstall < full
path name of a writable directory>
If the users on the workstations where you plan to install
IBM InfoSphere Optim Query Workload Tuner do not have administrative
rights:
- For Windows: userinst.exe -record <response file and
path name> -log <log file and path name> -skipInstall <full
path name of a writable directory>
- For Linux: userinst -record < full path name of the
response file> -log <log file and path name> -skipInstall <full
path name of a writable directory>
- In IBM Installation Manager, select File > Preferences.
- On the Repositories page, add the location of the diskTag.inf files
for both IBM Data Studio full client, Version 3.1.1 and the IBM InfoSphere
Optim Query Workload Tuner for DB2 for z/OS License Activation Kit,
Version 3.1.1. Then, click OK.
The
path to the diskTag.inf file for IBM Data Studio
full client:
- On Windows operating systems: <Install media root>\disk1\diskTag.inf
- On Linux operating systems: <Install media root>/disk1/diskTag.inf
The path to the diskTag.inf file
for the license activation kit:
- On Windows operating systems: <Install media root>\oqwt\diskTag.inf
- On Linux operating systems: <Install media root>/oqwt/diskTag.inf
- In the main screen of IBM Installation Manager, click
Install.
- On the Install Packages page, follow either of these
steps. The results of these steps apply to the workstation
on which you are generating the response file, if you did not use
the -skipInstall option in step b; the results of
these steps also apply on the workstations where you perform the silent
installations.
- If the InfoSphere Optim Query Tuner client, Version 3.1 or IBM
Data Studio full client, Version 3.1 is installed and you want to
upgrade to IBM Data Studio full client, Version 3.1.1, select the
package group in which the product that you want to replace appears.
The Installation Manager wizard will uninstall that product before
installing IBM Data Studio full client, Version 3.1.1.
Note:
InfoSphere
Optim Query Tuner client, Version 3.1 and IBM Data Studio full client,
Version 3.1 could be in separate package groups or the same package
group. If they are in separate package groups, choose one of the package
groups. Installation Manager will not uninstall the client that is
in the other package group.
- If the InfoSphere Optim Query Tuner client, Version 3.1 or IBM
Data Studio full client, Version 3.1 is not installed, create a new
package group.
- Finish the remaining steps in the wizard.
- Transfer the response file to a workstation where you want
to install IBM InfoSphere Optim Query Workload Tuner for DB2 for z/OS,
Version 3.1.1. Ensure that the version of IBM Installtion
Manager that you used to generate the response file is installed on
the workstation where you want to run the silent installation. Ensure
that the repositories that the response file specifies are accessible
by IBM Installation Manager. For example, if the response file points
to the diskTag.inf files that are on the product DVD, place the product
DVD in the DVD drive of the workstation before starting the silent
installation.
- 3. Perform the silent installation.
- At a command line, change to the eclipse subdirectory
in the directory where IBM Installation Manager is installed.
- On Windows, for example: cd C:\Program Files\IBM\Installation
Manager\eclipse
- On Linux, for example: cd /opt/IBM/Installation Manager/eclipse
- Run one of these commands.
Note:
The -log option
creates a new log file, so the directory that you specify must be
writable.
If you generated the response file with any version
of IBM Installation Manager from 1.3.3 to 1.4.2:
- For Windows: IBMIMc.exe --launcher.ini silent-install.ini
-input <response file path and name> -log <log file path
and name>
- For Linux: ./IBMIM --launcher.ini silent-install.ini -input <response
file path and name> -log <log file path and name>
If you generated the response file with IBM Installation
Manager 1.4.3 or 1.5:
- For Windows: imcl.exe -acceptLicense input <response
file path and name> -log <log file path and name>
- For Linux: ./imcl -acceptLicense input <response file
path and name> -log <log file path and name>
What to do next
If you need to configure DB2 for z/OS subsystems for tuning,
see Configuring a DB2 for z/OS subsystem for query and query-workload
tuning with InfoSphere Optim Query Workload Tuner.
If your
subsystems were already configured for tuning with IBM InfoSphere
Optim Query Workload Tuner for DB2 for z/OS, Version 3.1 and a license
for that version is active on the subsystem, you can start IBM Data
Studio full client and start tuning.
Installing only the IBM Data Studio full client, Version 3.1.1
for IBM InfoSphere Optim Query Workload Tuner through a wizard
Before you begin
Ensure that your workstation meets the system requirements.
About this task
You might want to install only the IBM Data Studio full client,
Version 3.1.1 in either of these situations:
- The IBM InfoSphere Optim Query Tuner client, Version 3.1.1 is
installed on your workstation and you want to upgrade to IBM Data
Studio full client, Version 3.1.1. You can connect to subsystems on
which the license for IBM InfoSphere Optim Query Workload Tuner for
DB2 for z/OS, Version 3.1 is active and use the full set of features
in Version 3.1.1.
- You do not need to install the license activation kit because
you will not be activating product licenses on DB2 for z/OS subsystems.
Procedure
- On the product DVD or in the extracted content of the compressed
file downloaded from IBM Passport Advantage, double-click either of
these files, depending on your operating system:
- On Windows operating systems: setup.exe
- On Linux operating systems: setup
- In the launchpad, select Install Product and select the
type of installation that you want to perform.
- On the Select Packages page of the wizard, select the package
for the IBM Data Studio full client, Version 3.1.1.
- On the Install Packages page, follow either of these steps:
- If you have the InfoSphere Optim Query Tuner client, Version
3.1 or IBM Data Studio full client, Version 3.1 installed and you
want to upgrade to IBM Data Studio full client, Version 3.1.1, select
the package group in which the product that you want to replace appears.
The Installation Manager wizard will uninstall that product before
installing IBM Data Studio full client, Version 3.1.1.
Note:
InfoSphere
Optim Query Tuner client, Version 3.1 and IBM Data Studio full client,
Version 3.1 could be in separate package groups or the same package
group. If they are in separate package groups, choose one of the package
groups. Installation Manager will not uninstall the client that is
in the other package group.
- If you do not have the InfoSphere Optim Query Tuner client,
Version 3.1 or IBM Data Studio full client, Version 3.1 installed,
create a new package group.
- Finish the remaining steps in the wizard.
What to do next
If you need to configure DB2 for z/OS subsystems for tuning,
see Configuring a DB2 for z/OS subsystem for query and query-workload
tuning with InfoSphere Optim Query Workload Tuner.
If your subsystems
were already configured for tuning with IBM InfoSphere Optim Query
Workload Tuner for DB2 for z/OS, Version 3.1 and a license for that
version is active on the subsystem, you can start IBM Data Studio
full client and start tuning.
Installing only the IBM Data Studio full client, Version 3.1.1
for IBM InfoSphere Optim Query Workload Tuner silently
Before you begin
Ensure that any version of IBM Installation Manager from 1.3.3
through 1.5 is installed on your workstation.
About this task
You might want to install only the IBM Data Studio full client,
Version 3.1.1 in either of these situations:
- The IBM InfoSphere Optim Query Tuner client, Version 3.1.1 is
installed on your workstation and you want to upgrade to IBM Data
Studio full client, Version 3.1.1. You can connect to subsystems on
which the license for IBM InfoSphere Optim Query Workload Tuner for
DB2 for z/OS, Version 3.1 is active and use the full set of features
in Version 3.1.1.
- You do not need to install the license activation kit because
you will not be activating product licenses on DB2 for z/OS subsystems.
This task involves generating a response file on one workstations,
and then performing the installation with the response file on other
workstations.
Restrictions:
- If you generate a response file on a workstation that is running
a Windows operating system, you must perform the silent installation
with that response file on another workstation that is running a Windows
operating system.
- If you generate a response file on a workstation that is running
a Linux operating system, you must perform the silent installation
with that response file on another workstation that is running a Linux
operating system.
Procedure
- Generate a response file:
- At a command line, change to the eclipse subdirectory
in the directory where IBM Installation Manager is installed.
- On Windows, for example: cd C:\Program Files\IBM\Installation
Manager\eclipse
- On Linux, for example: cd /opt/IBM/Installation Manager/eclipse
- Run one of these commands to start IBM Installation
Manager.
Note:
- The commands as shown will not install the IBM Data Studio full
client, Version 3.1.1 on your workstation, and will generate a response
file only, in addition to a log file. If you want to install the IBM
Data Studio full client, Version 3.1.1 on your workstation, remove
the -skipInstall option from the command that you run.
- Ensure the file paths you enter exist; Installation Manager will
not create directories for the response file and the log file.
If the users on the workstations where you plan to
install the IBM Data Studio full client, Version 3.1.1 have administrative
rights:
- For Windows: IBMIM.exe -record <response file and path
name> -log <log file and path name> -skipInstall <full path
name of a writable directory>
- For Linux: IBMIM -record < full path name of the response
file> -log <log file and path name> -skipInstall < full path
name of a writable directory>
If the users on the workstations where you plan to install
the IBM Data Studio full client, Version 3.1.1 do not have administrative
rights:
- For Windows: userinst.exe -record <response file and
path name> -log <log file and path name> -skipInstall <full
path name of a writable directory>
- For Linux: userinst -record < full path name of the
response file> -log <log file and path name> -skipInstall <full
path name of a writable directory>
- In IBM Installation Manager, select File > Preferences.
- On the Repositories page, add the location of the diskTag.inf file
for the IBM Data Studio full client, Version 3.1.1. Then, click OK.
The path to the diskTag.inf file for
IBM Data Studio full client:
- On Windows operating systems: <Install media root>\disk1\diskTag.inf
- On Linux operating systems: <Install media root>/disk1/diskTag.inf
- In the main screen of IBM Installation Manager, click
Install.
- On the Select Packages page of the wizard, select the
package for the IBM Data Studio full client.
- On the Install Packages page, follow either of these
steps. The results of these steps apply to the workstation
on which you are generating the response file, if you did not use
the -skipInstall option in step b; the results of
these steps also apply on the workstations where you perform the silent
installations.
- If the InfoSphere Optim Query Tuner client, Version 3.1 or IBM
Data Studio full client, Version 3.1 is installed and you want to
upgrade to IBM Data Studio full client, Version 3.1.1, select the
package group in which the product that you want to replace appears.
The Installation Manager wizard will uninstall that product before
installing IBM Data Studio full client, Version 3.1.1.
Note:
InfoSphere
Optim Query Tuner client, Version 3.1 and IBM Data Studio full client,
Version 3.1 could be in separate package groups or the same package
group. If they are in separate package groups, choose one of the package
groups. Installation Manager will not uninstall the client that is
in the other package group.
- If the InfoSphere Optim Query Tuner client, Version 3.1 or IBM
Data Studio full client, Version 3.1 is not installed, create a new
package group.
- Finish the remaining steps in the wizard.
- Transfer the response file to a workstation where you want
to install the IBM Data Studio full client, Version 3.1.1. Ensure
that the version of IBM Installtion Manager that you used to generate
the response file is installed on the workstation where you want to
run the silent installation. Ensure that the repositories that the
response file specifies are accessible by IBM Installation Manager.
For example, if the response file points to the diskTag.inf file that
is on the product DVD, place the product DVD in the DVD drive of the
workstation before starting the silent installation.
- 3. Perform the silent installation.
- At a command line, change to the eclipse subdirectory
in the directory where IBM Installation Manager is installed.
- On Windows, for example: cd C:\Program Files\IBM\Installation
Manager\eclipse
- On Linux, for example: cd /opt/IBM/Installation Manager/eclipse
- Run one of these commands.
Note:
The -log option
creates a new log file, so the directory that you specify must be
writable.
If you generated the response file with any version
of IBM Installation Manager from 1.3.3 to 1.4.2:
- For Windows: IBMIMc.exe -launcher.ini silent-install.ini
-input <response file path and name> -log <log file path and
name>
- For Linux: ./IBMIM -launcher.ini silent-install.ini -input <response
file path and name> -log <log file path and name>
If you generated the response file with IBM Installation
Manager 1.4.3 or 1.5:
- For Windows: imcl.exe -acceptLicense input <response
file path and name> -log <log file path and name>
- For Linux: ./imcl -acceptLicense input <response file
path and name> -log <log file path and name>
What to do next
If you need to configure DB2 for z/OS subsystems for tuning,
see Configuring a DB2 for z/OS subsystem for query and query-workload
tuning with InfoSphere Optim Query Workload Tuner.
If your
subsystems were already configured for tuning with IBM InfoSphere
Optim Query Workload Tuner for DB2 for z/OS, Version 3.1 and a license
for that version is active on the subsystem, you can start IBM Data
Studio full client and start tuning.
Installing only the license activation kit for IBM InfoSphere
Optim Query Workload Tuner, Version 3.1.1 through a wizard
About this task
You might want to install only the license activation kit
if the IBM Data Studio full client, Version 3.1.1 is installed on
your workstation and you plan to activate licenses for IBM InfoSphere
Optim Query Workload Tuner for DB2 for z/OS, Version 3.1.1, so that
everyone who connects to your subsystems through an IBM Data Studio
client can access the full set of tuning features for DB2 for z/OS.
Procedure
- On the product DVD or in the extracted content of the compressed
file downloaded from IBM Passport Advantage, double-click either of
these files, depending on your operating system:
- On Windows operating systems: setup.exe
- On Linux operating systems: setup
- In the launchpad, select Install Product and select the
type of installation that you want to perform.
- On the Select Packages page of the wizard, select the package
for the license activation kit.
- 4. On the Install Packages page, select the package group
in which IBM Data Studio full client, Version 3.1.1 is installed.
- Finish the remaining steps in the wizard.
What to do next
If you need to configure DB2 for z/OS subsystems for tuning,
see Configuring a DB2 for z/OS subsystem for query and query-workload
tuning with InfoSphere Optim Query Workload Tuner.
Installing only the license activation kit for IBM InfoSphere
Optim Query Workload Tuner, Version 3.1.1 silently
Before you begin
Ensure that any version of IBM Installation Manager from 1.3.3
through 1.5 is installed on your workstation.
About this task
You might want to install only the license activation kit
if the IBM Data Studio full client, Version 3.1.1 is installed on
your workstation and you plan to activate licenses for IBM InfoSphere
Optim Query Workload Tuner for DB2 for z/OS, Version 3.1.1, so that
everyone who connects to your subsystems through an IBM Data Studio
client can access the full set of tuning features for DB2 for z/OS.
This
task involves generating a response file on one workstations, and
then performing the installation with the response file on other workstations.
Restrictions:
- If you generate a response file on a workstation that is running
a Windows operating system, you must perform the silent installation
with that response file on another workstation that is running a Windows
operating system.
- If you generate a response file on a workstation that is running
a Linux operating system, you must perform the silent installation
with that response file on another workstation that is running a Linux
operating system.
Procedure
- Generate a response file:
- At a command line, change to the eclipse subdirectory
in the directory where IBM Installation Manager is installed.
- On Windows, for example: cd C:\Program Files\IBM\Installation
Manager\eclipse
- On Linux, for example: cd /opt/IBM/Installation Manager/eclipse
- Run one of these commands to start IBM Installation
Manager.
Note:
- The commands as shown will not install the license activation
kit on your workstation, and will generate a response file only, in
addition to a log file. If you want to install the license activation
kit on your workstation, remove the -skipInstall option from the command
that you run.
- Ensure the file paths you enter exist; Installation Manager will
not create directories for the response file and the log file.
If the users on the workstations where you plan to
install the license activation kit have administrative rights:
- For Windows: IBMIM.exe -record <response file and path
name> -log <log file and path name> -skipInstall <full path
name of a writable directory>
- For Linux: IBMIM -record < full path name of the response
file> -log <log file and path name> -skipInstall < full path
name of a writable directory>
If the users on the workstations where you plan to install
the license activation kit do not have administrative rights:
- For Windows: userinst.exe -record <response file and
path name> -log <log file and path name> -skipInstall <full
path name of a writable directory>
- For Linux: userinst -record < full path name of the
response file> -log <log file and path name> -skipInstall <full
path name of a writable directory>
- In IBM Installation Manager, select File > Preferences.
- On the Repositories page, add the location of the diskTag.inf file
for the license activation kit. Then, click OK.
The path to the diskTag.inf file for
the license activation kit:
- On Windows operating systems: <Install media root>\oqwt\diskTag.inf
- On Linux operating systems: <Install media root>/oqwt/diskTag.inf
- In the main screen of IBM Installation Manager, click
Install.
- On the Select Packages page of the wizard, select the
package for the license activation kit.
- 4. On the Install Packages page, select the package
group in which IBM Data Studio full client, Version 3.1.1 is installed.
- Finish the remaining steps in the wizard.
- Transfer the response file to a workstation where you want
to install the license activation kit. Ensure that the
version of IBM Installtion Manager that you used to generate the response
file is installed on the workstation where you want to run the silent
installation. Ensure that the repositories that the response file
specifies are accessible by IBM Installation Manager. For example,
if the response file points to the diskTag.inf file that is on the
product DVD, place the product DVD in the DVD drive of the workstation
before starting the silent installation.
- 3. Perform the silent installation.
- At a command line, change to the eclipse subdirectory
in the directory where IBM Installation Manager is installed.
- On Windows, for example: cd C:\Program Files\IBM\Installation
Manager\eclipse
- On Linux, for example: cd /opt/IBM/Installation Manager/eclipse
- Run one of these commands.
Note:
The -log option
creates a new log file, so the directory that you specify must be
writable.
If you generated the response file with any version
of IBM Installation Manager from 1.3.3 to 1.4.2:
- For Windows: IBMIMc.exe -launcher.ini silent-install.ini
-input <response file path and name> -log <log file path and
name>
- For Linux: ./IBMIM -launcher.ini silent-install.ini -input <response
file path and name> -log <log file path and name>
If you generated the response file with IBM Installation
Manager 1.4.3 or 1.5:
- For Windows: imcl.exe -acceptLicense input <response
file path and name> -log <log file path and name>
- For Linux: ./imcl -acceptLicense input <response file
path and name> -log <log file path and name>
What to do next
If you need to configure DB2 for z/OS subsystems for tuning,
see Configuring a DB2 for z/OS subsystem for query and query-workload
tuning with InfoSphere Optim Query Workload Tuner.
Migrating from the InfoSphere Optim Query Tuner client to the
IBM Data Studio full client, Version 3.1.1
The InfoSphere Optim Query Tuner client is not a part of
IBM InfoSphere Optim Query Workload Tuner, Version 3.1.1. You must
export your Query Tuner projects and install the IBM Data Studio full
client into the package group where the InfoSphere Optim Query Tuner
client is located.
About this task
The IBM Data Studio full client, Version 3.1.1 allows
you to connect to DB2 databases and subsystems where licenses for
IBM InfoSphere Optim Query Workload Tuner, Version 3.1 or 3.1.1 are
active, and to use the full set of features for tuning queries and
query workloads.
If you install the license activation kit for
either IBM InfoSphere Optim Query Workload Tuner for DB2 for Linux,
UNIX, and Windows, Version 3.1.1 or IBM InfoSphere Optim Query Workload
Tuner for DB2 for z/OS, Version 3.1.1 together with the IBM Data Studio
full client, Version 3.1.1, you can activate licenses for those products
on DB2 databases and subsystems. Activating a license on a DB2 database
or subsystem allows anybody with access and who is using IBM Data
Studio full client or administration client, Version 3.1.1 to use
the full set of features for tuning queries and query workloads.
Procedure
To migrate from the InfoSphere Optim Query Tuner client
to the IBM Data Studio full client, Version 3.1.1:
- Optional: Export your Query Tuner projects
from the InfoSphere Optim Query Tuner client.
- In the InfoSphere Optim Query Tuner client, select File > Export.
- In the Export window, expand Query
Tuner and select Projects.
- Select the projects that you want to export, select
the directory to which you want to export them, and click Finish.
- Follow either of these steps:
- If you plan to activate the license for IBM InfoSphere Optim
Query Workload Tuner on any DB2 databases or subsystems, you can install
IBM Data Studio full client, Version 3.1.1 and the license activation
kit through the IBM Installation Manager wizard or silently from a
command-line.
- If you do not plan to activate the license for IBM InfoSphere
Optim Query Workload Tuner on any DB2 databases or subsystems, you
can install only IBM Data Studio full client, Version 3.1.1 through
the IBM Installation Manager wizard or silently from a command-line.
- Start the IBM Data Studio full client. When Data Studio
asks you to select a workspace, select the workspace that you used
with the InfoSphere Optim Query Tuner client.
- If you find any problems with your Query Tuner projects,
delete the ones in error and import them from the directory that you
exported them to in step 1.
Migrating from IBM(r) Optimization
Service Center to InfoSphere Optim(tm) Query
Workload Tuner for DB2 for z/OS(r)
Use this roadmap as an overview for the tasks to migrate
from IBM® Optimization
Service Center to InfoSphere Optim™ Query
Workload Tuner for DB2 for z/OS®.
Before you begin
About this task
The migration from Optimization Service Center to InfoSphere Optim Query
Workload Tuner for DB2 for z/OS includes
installing InfoSphere Optim Query
Workload Tuner for DB2 for z/OS and
configuring your DB2® subsystems
for query tuning and query-workload tuning.
Procedure
To migrate from Optimization Service Center to InfoSphere Optim Query
Workload Tuner for DB2 for z/OS:
- If you want to reuse existing projects, export those projects
by following these steps in Optimization Service Center:
- Click Project > Export.
- In the Export Project window, select
the project to export.
- Select the directory to contain the exported project.
- Click Finish.
The exported project is stored in a compressed file that
you can import later.
- Follow these steps to export individual workloads.
- Open the Workload List view.
- Right-click the workload to export and select Export.
- Specify the amount of information for the exported workload
to contain.
- In the File field, type the path
and name for the file to contain the workload. The extension must
be .zip. If you do not specify a path, Optimization Service Center
saves the file in the directory of the current project.
- Uninstall Optimization Service Center.
- On your Windows desktop, click Start > Programs > IBM Optimization Service
Center for DB2 for z/OS > Uninstall Optimization
Service Center. The Optimization Service
Center Uninstaller wizard opens.
- Click Next, and click Uninstall.
The uninstallation process runs. When it completes, Optimization Service
Center is no longer installed on the workstation.
- Click OK.
The exported workload is stored in a compressed file that
you can import later.
- Follow either of these steps:
- If you plan to activate the license for IBM InfoSphere Optim
Query Workload Tuner on any DB2 databases or subsystems, you can install
IBM Data Studio full client, Version 3.1.1 and the license activation
kit through the IBM Installation Manager wizard or silently from a
command-line.
- If you do not plan to activate the license for IBM InfoSphere
Optim Query Workload Tuner on any DB2 databases or subsystems, you
can install only IBM Data Studio full client, Version 3.1.1 through
the IBM Installation Manager wizard or silently from a command-line.
- Configure the DB2 for z/OS subsystem
for query tuning either from the client or by running a JCL job:
What to do next
To start tuning in the Query Tuner Workflow Assistant:
- Open the IBM Data
Studio client.
- In the Data Source Explorer, open the connection to the subsystem.
- Expand the connection, so that the subsystem is visible.
- Right-click the subsystem and click Start
Tuning.
Configuring DB2 databases and subsystems for use with IBM InfoSphere
Optim Query Workload Tuner
You must enable certain facilities and objects on a DB2
database or subsystem before you can tune the SQL statements and query
workloads that run on that database or subsystem.
You must configure your DB2 for Linux, UNIX, and Windows database
before you start tuning SQL statements.
The following objects are created on the database during
the configuration process:
- The license for InfoSphere Optim Query Workload Tuner
- If you are using the IBM Data Studio full client, Version 3.1.1,
and the IBM InfoSphere Optim Query Workload Tuner for DB2 for Linux,
UNIX, and Windows License Activation Kit, Version 3.1.1 is installed
on your workstation, you can activate the product license on the database.
You can activate the license either by running a script or by configuring
the database by using the IBM Data Studio full client.
- EXPLAIN tables
- You can create a set of EXPLAIN tables manually by running a script,
or you can let the IBM Data Studio client create a set in the SYSTOOLS
schema.
- QT_WCC* tables in the SYSTOOLS schema
- These tables store information about query workloads.
- The DB2OE.CALLDB2ADVIS stored procedure
- This stored procedure allows the Index Advisor and the Workload
Index Advisor to recommend new indexes.
- The OQT.WCC_EXPLAIN_SP stored procedure
- This stored procedure gathers EXPLAIN information for the SQL
statements that are in query workloads.
You can configure DB2 for Linux, UNIX, and Windows databases
for use the IBM InfoSphere Optim Query Workload Tuner by using the
IBM Data Studio client.
Before you begin
Recommendation:
In the DB2 for Linux, UNIX, and Windows database
that you are configuring, create a system temporary table space with
a page size of 8 KB or larger to avoid potential query tuning errors.
About this task
During configuration, when InfoSphere Optim Query Workload
Tuner creates the EXPLAIN tables and the EXPLAIN_GET_MSGS user-defined
function, it grants the EXECUTE privilege on the EXPLAIN_GET_MSGS
user-defined function to PUBLIC.
Procedure
To configure a DB2 for Linux, UNIX, and Windows database
for query tuning with InfoSphere Optim Query
Workload Tuner:
- In the Data Source Explorer, right-click the connection
to the database and select Connect.
- Expand the connection by clicking the plus symbol next
to it. An icon that represents the database appears. Right-click the
database and select Analyze and Tune > Configure for Tuning > Guided Configuration.
The client detects whether a license
for IBM InfoSphere Optim Query Workload Tuner, Version 3.1.1 is active
on the database.
If there is an active license on the database,
the client attempts to configure the database for query tuning and
query-workload tuning.
If there is no active license on the
database, your choice of action depends on which of the three following
situations match your own situation:
- If you are using the IBM Data Studio administration client, Version
3.1.1, the client attempts to configure the database for query tuning
for the no-charge features only. For a list of the no-charge features,
see Features by product. If you do
not want to configure the database for the no-charge features only,
contact the person in your organization who installed both the IBM
Data Studio full client, Version 3.1.1 and the IBM InfoSphere Optim
Query Workload Tuner for DB2 for Linux, UNIX, and Windows, Version
3.1.1 License Activation Kit on a workstation and have that person
activate the license.
- If you are using the IBM Data Studio full client, Version 3.1.1
and the IBM InfoSphere Optim Query Workload Tuner for DB2 for Linux,
UNIX, and Windows, Version 3.1.1 License Activation Kit is not installed
on your workstation, the client attempts to configure the database
for query tuning with the no-charge features only. For a list of the
no-charge features, see Features by product. If you do
not want to configure the database for the no-charge features only,
contact the person in your organization who installed both the IBM
Data Studio full client, Version 3.1.1 and the IBM InfoSphere Optim
Query Workload Tuner for DB2 for Linux, UNIX, and Windows, Version
3.1.1 License Activation Kit on a workstation and have that person
activate the license.
- If you are using the IBM Data Studio full client, Version 3.1.1
and the IBM InfoSphere Optim Query Workload Tuner for DB2 for Linux,
UNIX, and Windows, Version 3.1.1 License Activation Kit is installed
on your workstation, the client attempts to configure the database
for both query tuning and query-workload tuning, if your user ID has
the authority to run the CREATE FUNCTION statement.
When the client attempts to configure the database, it
first checks whether at least one EXPLAIN table exists under the current
schema, which is the user ID. The client then follows this algorithm:
- If at least one EXPLAIN table exists under the current schema,
the client checks whether all EXPLAIN tables are present under the
current schema.
- If only a subset of EXPLAIN tables exists in the current schema,
the client reports that some of the EXPLAIN tables are missing. You
must drop the subset of EXPLAIN tables. Then, you can create a full
set of EXPLAIN tables in the SYSTOOLS schema by selecting the Guided
Configuration option again, or you can create a full set in a different
schema by running the EXPLAIN.DDL file that is in the MISC folder
of the installation directory for DB2 for Linux, UNIX, and Windows
on your database. After you create the tables, in the Data Source
Explorer connect to the database and select the Analyze and Tune >
Guided Configuration option again.
- If a full set of EXPLAIN tables exists in the current schema,
the client checks whether the EXPLAIN tables are at the version that
your database requires.
- If the EXPLAIN tables are at the version that your database requires,
the client carries out the remaining steps in the configuration process.
- If the EXPLAIN tables are not at the version that your database
requires, the client determines whether the tables can be migrated
to the correct version.
- If the EXPLAIN tables can be migrated, the client reports that
you must migrate the tables and provides the DDL statements for the
migration. After you run the DDL statements, in the Data Source Explorer
connect to the database and select the Analyze and Tune > Guided Configuration
option again.
- If the EXPLAIN tables cannot be migrated, the client reports that
you must drop the tables. Then, you can create a full set of EXPLAIN
tables in the SYSTOOLS schema by selecting the Guided Configuration
option again, or you can create them in a different schema by running
the EXPLAIN.DDL file that is in the MISC folder of the installation
directory for DB2 for Linux, UNIX, and Windows on your database. After
you create the tables, in the Data Source Explorer connect to the
database and select the Analyze and Tune > Guided Configuration option
again.
- If no EXPLAIN table exists under the current schema, the client
checks whether there is at least one EXPLAIN table in the SYSTOOLS
schema.
- If there are no EXPLAIN tables in the SYSTOOLS schema, the client
creates a full set of EXPLAIN tables in that schema and carries out
the rest of the configuration process.
- If there is at least on EXPLAIN table in the SYSTOOLS schema,
the client checks whether a full set of EXPLAIN tables exists in the
SYSTOOLS schema.
- If only a subset of EXPLAIN tables exists in the SYSTOOLS schema,
the client reports that some of the EXPLAIN tables are missing. You
must drop the subset of EXPLAIN tables. Then, you can create a full
set of EXPLAIN tables in the SYSTOOLS schema by selecting the Guided
Configuration option again, or you can create a full set in a different
schema by running the EXPLAIN.DDL file that is in the MISC folder
of the installation directory for DB2 for Linux, UNIX, and Windows
on your database. After you create the tables, in the Data Source
Explorer connect to the database and select the Analyze and Tune >
Guided Configuration option again.
- If a full set of EXPLAIN tables exists in the SYSTOOLS schema,
the client checks whether the EXPLAIN tables are at the version that
your database requires.
- If the EXPLAIN tables are at the version that your database requires,
the client carries out the remaining steps in the configuration process.
- If the EXPLAIN tables are not at the version that your database
requires, the client determines whether the tables can be migrated
to the correct version.
- If the EXPLAIN tables can be migrated, the client reports that
you must migrate the tables and provides the DDL statements for the
migration. After you run the DDL statements, in the Data Source Explorer
connect to the database and select the Analyze and Tune > Guided Configuration
option again.
- If the EXPLAIN tables cannot be migrated, the client reports that
you must drop the tables. Then, you can create a full set of EXPLAIN
tables in the SYSTOOLS schema by selecting the Guided Configuration
option again, or you can create them in a different schema by running
the EXPLAIN.DDL file that is in the MISC folder of the installation
directory for DB2 for Linux, UNIX, and Windows on your database. After
you create the tables, in the Data Source Explorer connect to the
database and select the Analyze and Tune > Guided Configuration option
again.
- Verify that the database is configured for query and query-workload
tuning and that the required data objects are created or enabled.
- In the Data Source Explorer, right-click the database,
and then click Analyze and Tune > Configure for Tuning > Advanced Configuration
and Privilege Management. The Advanced
Configuration and Privilege Management window opens.
- In the Configuration Status section,
you can check the status of the tables, stored procedures, objects,
and automatic statistics collection that are required to use the query
and query-workload tuning features. If a data object is disabled,
follow the instructions in the message that is displayed beside that
object to create or enable it.
Results
Your DB2 for Linux, UNIX, and Windows database
is configured for query and query-workload tuning with InfoSphere Optim Query
Workload Tuner.
What to do next
To start tuning in the Query Tuner Workflow Assistant,
right-click the connection in the Data Source Explorer, then click Analyze and Tune > Start Tuning.
Authorities and privileges for configuring DB2 for Linux, UNIX, and Windows databases
for use with IBM InfoSphere Optim Query Workload Tuner
Ensure that you have the authorities and privileges that
are required to configure a DB2 for Linux, UNIX, and Windows database.
You must have the following authorities and privileges to
configure a DB2 for Linux, UNIX, and Windows database
for query tuning:
- To activate the license for InfoSphere Optim Query Workload Tuner
on a database, you must have the authority or privilege to run the
CREATE FUNCTION statement.
- You must have the authority to install a JAR file
with a stored procedure on the database server. For more information,
see JAR file administration on the database server.
- You must have the authorities and privileges for running the CREATE
PROCEDURE (external) statement. For more information, see CREATE PROCEDURE (external) statement.
- You must have the authorities and privileges to create EXPLAIN
tables on your database, if these tables are not yet created.
Migrating EXPLAIN tables
If you upgraded the version of DB2 for Linux, UNIX, and
Windows that is managing a database, and that database contains a
set of EXPLAIN tables that you are using with IBM InfoSphere Optim
Query Workload Tuner, you can run a script to migrate those tables
so that they are compatible with the new version of DB2 for Linux,
UNIX, and Windows.
To migrate the EXPLAIN tables, follow the instructions
in the migrate_explaintables_db2luw.ddl migration
script.
You can find this migration script in these locations
in the installation directory for the IBM Data Studio client.
- On Windows: \QueryTunerServerConfig\all_features\LUW subdirectory
- On Linux or UNIX: /QueryTunerServerConfig/all_features/LUW subdirectory
Running scripts to configure DB2 databases for use with IBM
InfoSphere Optim Query Workload Tuner
If you do not want to use the IBM Data Studio client to
configure a DB2 for Linux, UNIX, and Windows database, you can carry
out the configuration by editing and running a number of scripts.
Before you begin
Ensure that you have the required authorities and
privileges to configure a DB2 for Linux, UNIX, and Windows database
and activate the license for InfoSphere Optim Query Tuner.
Procedure
- Run the file EXPLAIN.DDL on the database
that you want to use for tuning SQL. This file is located
in the MISC folder in your DB2 installation.
- If you are using the IBM Data Studio full client, Version
3.1.1, the IBM InfoSphere Optim Query Workload Tuner for DB2 for Linux,
UNIX, and Windows, Version 3.1.1 License Activation Kit is installed
on your workstation: Run the file License.bat (for
Windows systems) or License.sh (for Linux systems),
which is in the directory <IBM Data Studio installation
directory>\QueryTunerServerConfig\all_features\LUW\License\,
to activate the product license on the database.
- In a DB2 command window, connect to the database on
which you want to activate the license.
- Change to the directory in which the License.bat (for
Windows systems) or License.sh (for Linux systems)
file is located.
- Run the file with the db2 -vtf command.
- In a text editor, edit the file luwsp.sql,
which is in the directory <IBM Data Studio installation
directory>\QueryTunerServerConfig\all_features\LUW\IA\,
by replacing the $install variable with the absolute
path to this file. You do not need to run the file after
you edit it. In a later step, you will run a batch file that will
run this file.
- In a text editor, edit the file wccexplainsp.sql,
which is in the directory <IBM Data Studio installation
directory>\QueryTunerServerConfig\all_features\LUW\WCC\,
by replacing the $install variable with the absolute
path to this file. You do not need to run the file after
you edit it. In a later step, you will run a batch file that will
run this file.
- Open an DB2 command window, connect to the database that
you activated the license on, and change to the directory <IBM
Data Studio installation directory>\QueryTunerServerConfig\all_features\LUW\.
- Run the file enablement_win.bat (on
Windows systems) or enablement_LinuxUnix.sh (on
Linux systems).
- Check these output files in the current directory for errors: luwsp.out, oqt_profile.out, wccexplainsp.out,
and wccluwddl.out
What to do next
To start tuning in the Query Tuner Workflow Assistant,
right-click the connection in the Data Source Explorer, then click Analyze and Tune > Start Tuning.
Recommendation:
In the DB2 for Linux, UNIX, and Windows database
that you are configuring, create a system temporary table space with
a page size of 8 KB or larger to avoid potential query tuning errors.
After you install the IBM Data Studio full client on your
workstation, you can configure your DB2 for z/OS subsystems for tuning
SQL statements and workloads.
Configuring for tuning means to create EXPLAIN tables and other
tables, table spaces, indexes, and aliases. It also means binding
required packages. You can carry out this task either by running JCL
on your subsystem, or by connecting to the subsystem with the IBM
Data Studio full client or administration client and using a configuration
wizard.
If you configured a subsystem for tuning with InfoSphere Optim
Query Workload Tuner for DB2 for z/OS, Version 2.2.1, you must migrate
the tables and other objects, so that you can use them with InfoSphere
Optim Query Workload Tuner for DB2 for z/OS, Version 3.1.1.
If you want to upgrade your DB2 for z/OS subsystem after you configure
for tuning, you must run a JCL job to migrate the EXPLAIN tables and
other objects that InfoSphere Optim Query Workload Tuner uses.
When you carry out the task of configuring for tuning, you can
also carry out one or more optional configuration tasks, such as setting
up the administrative scheduler to schedule tasks related to tuning
query workloads.
Running JCL to configure DB2 for z/OS subsystems for use with
InfoSphere Optim Query Workload Tuner
You can configure your DB2 for z/OS subsystem
for query and query-workload tuning by submitting a JCL job.
Before you begin
You must have the authorities and privileges that
are required for configuring DB2 for z/OS subsystems for query tuning.
About this task
If your subsystem was configured
for InfoSphere Optim Query Workload Tuner, Version 3.1, then you do
not need to follow the steps in this procedure.
If your subsystem
was configured for earlier versions of InfoSphere Optim Query Tuner
or InfoSphere Optim Query Workload Tuner, and before configuring for
version 3.1.1 you want to drop both the data objects from those earlier
versions and the data in those objects, the following procedure lists
a step for performing the drop.
Procedure
To configure a DB2 for z/OS subsystem
for query tuning and query-workload tuning:
- Upload the required JCL job files and DBRMs to the subsystem
from the client system.
The JCL files and DBRMs are
located inside the installation directory for the IBM Data Studio
full client.
- If you installed the IBM Data Studio full client on a Windows
system, the JCL is located in the installation_directory\QueryTunerServerConfig\all_features\ZOS\version_number_and_mode directory.
- If you installed the IBM Data Studio full client on a Linux system,
the JCL is located in the .installation_directory/QueryTunerServerConfig/all_features/ZOS/version_number_and_mode directory.
Upload the JCL files in ASCII format and the DBRMs in
binary format.
Table 1. JCL and DBRMs for Version 9.1, Version 10 conversion mode from Version 9.1, and Version 10 of DB2 for z/OS
Name of subfolder |
V9 |
V10CM9 |
V10 |
List of files |
JCL:
AOCDDL9
DBRMS:
AOC5OADM
AOC5OAPC
AOC5OEPK
AOC5OEXK
AOC5OFMJ
AOC5OIA1
AOC5OIA2
AOC5OIA3
AOC5OIA4
AOC5OIA5
AOC5OIA6
AOC5OIA7
AOC5OIA8
AOC5OIA9
AOC5OIAA
AOC5OIAK
AOC5OIAL
AOC5ONPT
AOC5OPKG
AOC5OQA
AOC5OQIA
AOC5OSA
AOC5OWCK
AOC5OWQA
AOC5OWSA
AOC5OWSK |
JCL:
AOCDDLX9
DBRMS:
AOC5OADM
AOC5OAPC
AOC5OEPK
AOC5OEXK
AOC5OFMM
AOC5OIA1
AOC5OIA2
AOC5OIA3
AOC5OIA4
AOC5OIA5
AOC5OIA6
AOC5OIA7
AOC5OIA8
AOC5OIA9
AOC5OIAA
AOC5OIAK
AOC5OIAL
AOC5ONPT
AOC5OPKG
AOC5OQA
AOC5OQIA
AOC5OSA
AOC5OWCK
AOC5OWQA
AOC5OWSA
AOC5OWSK |
JCL:
AOCDDL10
DBRMS:
AOC5OADM
AOC5OAPC
AOC5OEPK
AOC5OEXM
AOC5OFMM
AOC5OIA1
AOC5OIA2
AOC5OIA3
AOC5OIA4
AOC5OIA5
AOC5OIA6
AOC5OIA7
AOC5OIA8
AOC5OIA9
AOC5OIAA
AOC5OIAK
AOC5OIAL
AOC5ONPT
AOC5OPKG
AOC5OQA
AOC5OQIA
AOC5OSA
AOC5OWCM
AOC5OWQA
AOC5OWSA
AOC5OWSM |
- If your subsystem was configured for earlier versions of
InfoSphere Optim Query Tuner or InfoSphere Optim Query Workload Tuner,
and before configuring for version 3.1.1 you want to drop both the
data objects from those earlier versions and the data in those objects,
drop the following databases.
CAUTION:
Option |
Description |
If your subsystem is configured for versions 2.2.1 or 2.2.1.1: |
Drop databases DB2OSC, DSNOSCDB, and AOCOEDB. |
If your subsystem is configured for versions that are earlier
than 2.2.1: |
Drop databases DB2OSC, DSNOSCDB, and DB2OE. |
- If your subsystem was configured for IBM Optimization Center,
and before configuring for version 3.1.1 you want to drop both the
data objects from those earlier versions and the data in those objects,
drop databases DB2OSC and DSNOSCDB.
CAUTION:
This
step is not required. Follow it only if you are sure that you want
to delete the data that is in these databases.
- Create the tables for Version 3.1.1.
On the subsystem, modify the corresponding AOCDDL JCL job file to
conform to your environment, then submit the job. The following
table lists the AOCDDL JCL job files for each environment.
DB2 subsystem |
JCL job to create the query tuning
tables for Version 3.1.1 |
DB2 Version
10 for z/OS new-function
mode |
AOCDDL10 |
DB2 Version
10 for z/OS conversion
mode from Version 9 |
AOCDDLX9 |
DB2 Version
10 for z/OS conversion
mode from Version 8 |
AOCDDLX8 |
DB2 Version
9 for z/OS |
AOCDDL9 |
Using the IBM Data Studio client to configure DB2 for z/OS
subsystems for use with InfoSphere Optim Query Workload Tuner
You can connect to a DB2 for z/OS subsystem from the IBM
Data Studio client and configure the subsystem for query and query-workload
tuning by using a wizard.
Before you begin
About this task
If your subsystem was configured
for InfoSphere Optim Query Workload Tuner, Version 3.1, then you do
not need to follow the steps in this procedure.
The
procedure below explains how to use the IBM Data Studio client to
create the data objects that are required for query tuning and query-workload
tuning with IBM InfoSphere Optim Query Workload Tuner, Version 3.1.1..
For
subsystems configured for earlier versions of InfoSphere Optim Query
Tuner or InfoSphere Optim Query Workload Tuner, this procedure also
describes how you can perform either of these two actions:
- For versions 2.2.1.1 and earlier: Drop both the data objects
from those earlier versions and the data in those objects
- For version 2.2.1.1 and 2.2.1: Migrate the data objects
from those earlier versions and keep the data in those objects
For
the migration, you must have the required authorities
and privileges.
Procedure
To configure a DB2 for z/OS subsystem
for query tuning and query-workload tuning:
- If you want to drop data objects that were created for
earlier versions of InfoSphere Optim Query Tuner or InfoSphere Optim
Query Workload Tuner and you want to delete the data that is in those
objects, drop the following databases.
CAUTION:
Option |
Description |
If your subsystem is configured for versions 2.2.1 or 2.2.1.1: |
DB2OSC
DSNOSCDB
AOCOEDB |
If your subsystem is configured for versions that are earlier
than 2.2.1: |
DB2OSC
DSNOSCDB
DB2OE |
- If your subsystem was configured for IBM Optimization Center,
and before configuring for version 3.1.1 you want to drop both the
data objects from those earlier versions and the data in those objects,
drop databases DB2OSC and DSNOSCDB.
CAUTION:
This
step is not required. Follow it only if you are sure that you want
to delete the data that is in these databases.
- If you want to migrate the data objects that were created
for version 2.2.1 or 2.2.1.1, follow these steps:
- Upload the required JCL job files to the subsystem from
the client system.
The JCL files to upload are listed
in step b below and in step 5. The JCL files are installed with the IBM Data
Studio client in
the \QueryTunerServerConfig\ZOS\migration subdirectory
of the installation directory. The default installation directory
is C:\Program Files\IBM\DS3.1.1.
- Modify and submit the corresponding AOCM2B JCL job to
unload data from the existing Version 2.2.1 or Version 2.2.1 fix pack
1 query tuning tables. The following table lists the AOCM2B
JCL job files for each environment.
DB2 subsystem |
JCL job to unload data from query
tuning tables |
DB2 for z/OS Version 8 new-function
mode subsystem |
AOCM2B8N |
DB2 Version
9 for z/OS subsystem |
AOCM2B9 |
DB2 Version
10 for z/OS conversion
mode from DB2 for z/OS Version 8 subsystem |
AOCM2BX8 |
DB2 Version
10 for z/OS conversion
mode from DB2 Version
9 for z/OS subsystem |
AOCM2BX9 |
DB2 Version
10 for z/OS subsystem |
AOCM2BXN |
- Drop the DB2OSC, DSNOSCDB, and AOCOEDB databases.
- In the Data Source Explorer in the IBM Data Studio client,
expand the connection to the subsystem. Right-click the icon for the
subsystem for the subsystem and select Analyze
and Tune > Configure for Tuning > Guided Configuration. The
configuration wizard opens.
If the license for InfoSphere Optim
Query Workload Tuner is not yet active on the subsystem by this point,
the Data Studio full client installs the license on the subsystem
and activates the license, if the license activation kit is installed
in the directory where the Data Studio full client is installed on
your workstation. The configuration wizard allows you to configure
the subsystem for query tuning and query-workload tuning.
If
the licence for InfoSphere Optim Query Workload Tuner is not yet active
on the subsystem by this point and the license activation kit is not
installed in the directory where the Data Studio full client is installed
on your workstation, the configuration wizard allows you to configure
the subsystem only for the no-charge query-tuning features. Close
the configuration wizard and the Data Studio full client, install
the license activation kit on your workstation, then open the Data
Studio full client and the configuration wizard again. The license
should now be active on the subsystem and you should be able to configure
the subsystem for query tuning and query-workload tuning.
- Follow the instructions in the wizard.
- If you are migrating the data objects that were created
for version 2.2.1 or 2.2.1.1, modify and submit the corresponding
AOCM2F JCL job to load data to the query tuning tables for Version 3.1.1. The following table lists the AOCM2F JCL job files for each
environment.
DB2 subsystem |
JCL job to load data to the query
tuning tables |
DB2 for z/OS Version 8 new-function
mode subsystem |
AOCM2F8N |
DB2 Version
9 for z/OS subsystem |
AOCM2F9 |
DB2 Version
10 for z/OS conversion
mode from DB2 for z/OS Version 8 subsystem |
AOCM2FX8 |
DB2 Version
10 for z/OS conversion
mode from DB2 Version
9 for z/OS subsystem |
AOCM2FX9 |
DB2 Version
10 for z/OS subsystem |
AOCM2FXN |
- Verify that the subsystem is configured for query or query-workload
tuning. In the Data Source Explorer,
right-click the subsystem that you configured, and then click Analyze and Tune > Configure for Tuning > Advanced Configuration and Privilege Management.
You can check the status of the Query Tuner packages,
Query Tuner tables, EXPLAIN tables, and the query or query-workload
tuning features in this window.
Authorities and privileges for configuring DB2 for z/OS subsystems
for query and query-workload tuning with InfoSphere Optim Query Workload
Tuner
You must have the authorities and privileges that are required
to configure a DB2 for z/OS subsystem
for query and query-workload tuning.
If you have the SYSADM authority, you can configure a
subsystem for query and query-workload tuning. Otherwise, see the
following table for detailed authorities and privileges that are required
for each individual tasks.
Task |
Authorities or privileges required |
Connect to a DB2 for z/OS subsystem |
- To connect to the subsystem, you must have access authority on
underlying DB2 subsystem.
- To execute tuning functions, you must have EXECUTE privilege on
the following packages:
- AOC5OADM
- AOC5OEPJ (DB2 for z/OS Version 8)
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
|
Bind packages |
One of the following authorities and privileges:
- SYSADM or DBADM authority
- BINDADD privilege if required packages do not exist, and CREATEIN
privilege on the schema
- ALTERIN privilege on the schema if the required packages exist
- BIND privilege on the required packages if they exist
|
Free packages |
One of the following authorities and privileges:
- Ownership of the packages
- BINDAGENT privilege that is granted by the owner of the packages
- SYSCTRL authority
- SYSADM authority
- PACKADM authority for the collection or for all collections
|
Browse subsystem parameters |
Both of the following authorities and privileges:
- EXECUTE privilege on the SYSPROC.DSNWZP stored procedure.
- At least MONITOR1 system privilege
|
Manage users |
One of the following privileges or authorities:
- The privilege WITH GRANT OPTION on required packages
- Ownership of the packages
- SYSADM authority
|
Create EXPLAIN tables |
If you want to create the EXPLAIN tables in
a new database, one or more of the following privileges or authorities
for creating the database:
- CREATEDBA privilege
- CREATEDBC privilege
- SYSADM or SYSCTRL authority
For creating the table space, one or more of the following privileges
or authorities:
- CREATETS privilege for the database
- DBADM, DBCTRL, or DBMAINT authority for the database
- SYSADM or SYSCTRL authority
For creating the tables, one or more of the following privileges
or authorities:
- CREATETAB privilege for the database
- DBADM, DBCTRL, or DBMAINT authority for the database
- SYSADM or SYSCTRL authority
|
Create aliases for existing EXPLAIN tables |
One of the following privileges or authorities:
- The CREATEALIAS privilege
- SYSADM or SYSCTRL authority
- DBADM or DBCTRL authority on the database that contains the tables,
if the aliases are for tables and the value of field DBADM CREATE
AUTH on installation panel DSNTIPP is YES
|
Creating connections to DB2 for z/OS subsystems that you are
using for query and query-workload tuning
You can use the pages in the New Connection wizard to create
a connection profile, so that you can connect to a DB2 for z/OS subsystem.
This topic explains how to open the New Connection wizard from the
Data Source Explorer view and the Administration Explorer view.
Procedure
To create a connection to a DB2 for z/OS subsystem:
- Right-click in the Data Source Explorer, and select New from
the pop-up menu.
In the
Administration Explorer, you can click New > New Connection to a Database from
the Administration Explorer toolbar.
- On the first page of the wizard, select DB2 for z/OS as
the database manager, if you are connecting directly to the subsystem.
If you are connecting through a gateway, select DB2 for Linux, UNIX,
and Windows as the database manager. Then, specify other connection
details, as described.
- Connection identification
- Specify preferences for naming the new connection.
- Use default naming convention
- Specifies that a connection name is generated based on the name
of the subsystem that you are connecting to. This connection name
is displayed after you create the connection.
- Connection name
- Type a name for the connection. Available only if Use default
naming convention is not checked.
- JDBC driver
-
Specify a JDBC driver to use to connect to the subsystem. JDBC
drivers that appear in the list are fully supported. If a driver that
you want to use is not listed but it is supported by the database
manager, select Other Driver Default and provide
the details.
Click ... next to the JDBC
driver field to open a window so that you can modify the
path to the JAR files that are being used for a particular JDBC driver.
You can also use this window to view the names and typical locations
for JDBC JAR files for each listed driver.
Global driver properties
are set in the Preferences window, on the Driver Definitions page.
To get to that page, select Window > Preferences. Then, in the Preferences
window, expand Data Management > Connectivity.
The IBM Data Server Driver for JDBC
and SQLJ is included with the workbench product, and by default the
wizard uses the included version of the driver. It is recommended
that you use this version, because it has been tested thoroughly.
If you want to use a different version of this driver, you can modify
the path to the required JAR files by clicking ....
Other JDBC drivers for IBM data
servers might also be included, depending on the workbench product
that you are using.
JDBC driver limitation: Some drivers
require a pass code or license file. Although license information
can be set or available in the current environment, the information
might not be available in other environments, such as on a Web server.
The driver does not work if the pass code or license is not available.
- Properties - General
-
- Database
- This field appears when DB2 for Linux, UNIX, and Windows is the
selected database manager because you are connecting to the subsystem
through a gateway. Specify the name of the subsystem.
- Location
- This field appears when DB2 for z/OS is the selected database
manager. Type the DB2 location
name that is defined during installation. You must enter the name
in upper case. To determine the location, host, and port that should
be used for DB2 for z/OS connections, a DB2 for z/OS system
programmer or DBA can issue a <cmd prefix="">DIS
DDF where <cmd prefix=""> is a
preassigned character in your system for a particular DB2 subsystem.
- Host
- If you are connecting directly to the subsystem, specify the TCP/IP
host name or TCP/IP address of the subsystem.
If you are connecting through a gateway, specify the TCP/IP host
name or TCP/IP address of the gateway.
- Port number
- If you are connecting directly to the subsystem, specify the TCP/IP
connection port for the selected subsystem.
If you are connecting through a gateway, specify the TCP/IP connection
port for the gateway.
- Retrieve objects created by this user only
- This field appears when DB2 for z/OS is the selected database
manager. Select to load objects that were created by the user who
is specified in the User ID field.
- Default schema
- Type the name of the schema to use for unqualified database object
references in SQL statements. If you want SQL statements to refer
to database objects that are in other schemas, you must qualify the
names of the objects with the names of their corresponding schemas.
- Connection URL
- Shows the generated JDBC URL for the JDBC driver that you are
using. The URL identifies the database so that the driver can establish
a connection. The URL format depends on the driver.
- Properties - Tracing
- The controls on this page enable JDBC tracing for this connection.
JDBC tracing is useful for troubleshooting, but enabling JDBC tracing
can cause performance problems. Therefore, you should only enable
tracing if you are experiencing problems.
- Disable tracing
- Clear this check box to enable JDBC tracing for this connection.
- Directory
- Specify a directory to which the trace files are saved.
- File name
- Specify a base file name for the trace files. More than one trace
file might be created for each connection. If more than one trace
file is generated, this name is used and is appended with an underscore
and numeric values. For example, if you specify trace,
the generated files might be trace_1, trace_2,
and so on.
- Append
- Specifies that trace files are not overwritten if the files already
exist. If this option is selected, new trace information is appended
to any existing trace files.
- Trace levels
- Defines what kind of information is traced. Select a check box
next to each option to include information in the trace file.
- Properties - Optional
- Specify additional connection properties, for example: readOnly
= true. The properties that you can specify are different
for every JDBC driver. Refer to the JDBC driver documentation for
more examples.
- Optional: On the Filter page,
specify filtering options.
For best performance, you
should use filters when you are connecting to a large database.
If
you do not specify filtering options in the wizard, you can modify
them later by modifying connection properties or by specifying data
object filter options. (For information about these options, see "Data
object filters" at http://publib.boulder.ibm.com/infocenter/dstudio/v3r1/topic/com.ibm.datatools.server.ui.doc/topics/cfilters.html.)
To modify connection properties, right-click a connection and select Properties.
Filtering is not enabled by default on the wizard page.
To filter your connection, clear the Disable filter check
box, then specify filtering options either by using an expression
or by selecting specific objects to include or not include in the
connection view.
- Complete all other wizard steps and click Finish.
Results
The connection is displayed in the Data Source Explorer or
the Administration Explorer, depending on which view you started in.
Migrating data objects that were created for InfoSphere Optim
Query Workload Tuner for DB2 for z/OS, Version 2.2.1 or 2.2.1.1
If your subsystem was previously configured for query tuning
with Optim Query Tuner for DB2 for z/OS or Optim Query Workload Tuner
for DB2 for z/OS Version 2.2.1 or Version 2.2.1 fix pack 1, you must
migrate the data objects to enable query tuning with Version 3.1.1.
Before you begin
You must have the authorities and privileges for
migrating the EXPLAIN and Query Tuner tables.
About this task
To support table space and index name format changes that
were introduced by Version 3.1 and are continued into Version 3.1.1,
you must perform the migration in the following order on the subsystem:
- Unload data from the existing Version 2.2.1 or Version 2.2.1 fix
pack 1 query tuning tables.
- Drop the databases that contain the existing Version 2.2.1 or
Version 2.2.1 fix pack 1 query tuning tables.
- Create the databases and query tuning tables for Version 3.1.1.
- Load data to the query tuning tables for Version 3.1.1.
Procedure
To migrate the data objects for query tuning on a subsystem:
- Upload the required JCL job files to the subsystem from
the client system.
The files are installed with the IBM Data
Studio client.
You can find the AOCM JCL files in the \QueryTunerServerConfig\ZOS\migration subdirectory
of the installation directory. You can find the AOCDDL JCL files in
the \QueryTunerServerConfig\ZOS\z/OS_version_number_and_mode subdirectory
of the installation directory. The default installation directory
is C:\Program Files\IBM\DS3.1.1.
- Modify and submit the corresponding AOCM2B JCL job to unload
data from the existing Version 2.2.1 or Version 2.2.1 fix pack 1 query
tuning tables. The following table lists the AOCM2B JCL
job files for each environment.
DB2 subsystem |
JCL job to unload data from query
tuning tables |
DB2 for z/OS Version 8 new-function
mode subsystem |
AOCM2B8N |
DB2 Version
9 for z/OS subsystem |
AOCM2B9 |
DB2 Version
10 for z/OS conversion
mode from DB2 for z/OS Version 8 subsystem |
AOCM2BX8 |
DB2 Version
10 for z/OS conversion
mode from DB2 Version
9 for z/OS subsystem |
AOCM2BX9 |
DB2 Version
10 for z/OS subsystem |
AOCM2BXN |
- Drop the DB2OSC, DSNOSCDB, and AOCOEDB databases.
- Modify the corresponding AOCDDL JCL job file to conform
to your environment, then submit the job. The following
table lists the AOCDDL JCL job files for each environment.
DB2 subsystem |
JCL job to drop existing databases
and create new Version 3.1.1 databases
and query tuning tables |
DB2 Version
10 for z/OS new-function
mode |
AOCDDL10 |
DB2 Version
10 for z/OS conversion
mode from Version 9 |
AOCDDLX9 |
DB2 Version
10 for z/OS conversion
mode from Version 8 |
AOCDDLX8 |
DB2 Version
9 for z/OS |
AOCDDL9 |
DB2 for z/OS Version 8 new-function
mode |
AOCDDL8N |
- Modify and submit the corresponding AOCM2F JCL job to load
data to the query tuning tables for Version 3.1.1. The following table lists the AOCM2F JCL job files for each
environment.
DB2 subsystem |
JCL job to load data to the query
tuning tables |
DB2 for z/OS Version 8 new-function
mode subsystem |
AOCM2F8N |
DB2 Version
9 for z/OS subsystem |
AOCM2F9 |
DB2 Version
10 for z/OS conversion
mode from DB2 for z/OS Version 8 subsystem |
AOCM2FX8 |
DB2 Version
10 for z/OS conversion
mode from DB2 Version
9 for z/OS subsystem |
AOCM2FX9 |
DB2 Version
10 for z/OS subsystem |
AOCM2FXN |
Authorities and privileges for migrating the EXPLAIN tables
and Query Tuner tables on DB2 for z/OS subsystems
You must have the authorities and privileges that are required
to migrate the EXPLAIN tables and Query Tuner tables on DB2 for z/OS subsystems.
If you have the SYSADM authority, you can migrate the
EXPLAIN and Query Tuner tables. Otherwise, see the following table
for detailed authorities and privileges that are required for each
individual tasks.
Task |
Authorities or privileges required |
Alter EXPLAIN and Query Tuner tables |
One or more of the following privileges or authorities:
- ALTER privilege on the tables
- Ownership of the table
- DBADM authority for the database
- SYSADM or SYSCTRL authority
|
Create EXPLAIN and Query Tuner tables |
If you want to create the tables in a new database,
one or more of the following privileges or authorities for creating
the database:
- CREATEDBA privilege
- CREATEDBC privilege
- SYSADM or SYSCTRL authority
For creating the table space, one or more of the following privileges
or authorities:
- CREATETS privilege for the database
- DBADM, DBCTRL, or DBMAINT authority for the database
- SYSADM or SYSCTRL authority
For creating the tables, one or more of the following privileges
or authorities:
- CREATETAB privilege for the database
- DBADM, DBCTRL, or DBMAINT authority for the database
- SYSADM or SYSCTRL authority
|
Bind packages |
One of the following authorities and privileges:
- SYSADM or DBADM authority
- BINDADD privilege if required packages do not exist, and CREATEIN
privilege on the schema
- ALTERIN privilege on the schema if the required packages exist
- BIND privilege on the required packages if they exist
|
Packages that are required to be bound on DB2 for z/OS subsystems
that are used with InfoSphere Optim Query Workload Tuner
On each DB2 for z/OS that
runs the SQL statements that you tune, you must bind certain packages
to enable various functions of the IBM Data
Studio client.
You can bind the packages by using the IBM Data
Studio client or
by running an AOCDDL sample JCL job. After the packages are bound
from a particular workstation, you do not need to bind again to connect
from the IBM Data
Studio client on
another workstation.
Basic packages
- AOC5OADM
- This package accesses catalog tables to check the existence of
the objects, such as tables and databases that are used for query
tuning. This package also checks the format of objects, such as tables
and columns.
- AOC5OEPJ
- On a DB2 for z/OS Version
8 subsystem, this package accesses catalog information for any tables
on which the EXPLAIN statement is issued. This package accesses information
about columns, indexes, table spaces, and their statistics.
- AOC5OEPK
- On a DB2 for z/OS Version
9 or Version 10 subsystem, this package accesses catalog information
for any tables on which the EXPLAIN statement is issued. This package
accesses information about columns, indexes, table spaces, and their
statistics.
- AOC5ONPT
- This package accesses QMF™ control
tables in case the current user does not have SYSADM authority.
- AOC5OPKG
- This package accesses catalog tables to check the existence of
the package, and retrieves the privileges that users have on the packages
and tables.
Packages required for tuning SQL statements
- AOC5OSA
- For the Statistics Advisor, this package accesses statistics-profile
tables that are used by the statistics advisor to manage the statistics
profile.
- AOC5OQA
- For the Query Advisor, this package accesses catalog information
for referential constraints between tables.
- AOC5OQIA
- For the Index Advisor, this package accesses the catalog tables
to get the referential constraints, table definition, and statistics.
- AOC5OFMJ
- For the query annotation feature, this package accesses catalog
tables on a DB2 for z/OS Version
8 or Version 9 subsystem. This package obtains the DDL statements
for views and the materialized query tables.
- AOC5OFMM
- For the query annotation feature, this package accesses catalog
tables on a DB2 for z/OS Version
10 subsystem. This package obtains the DDL statements for views and
the materialized query tables.
Packages required for tuning query workloads
- AOC5OCWP
- For the SYSPROC.OPT_EXECUTE_TASK stored procedure, this package
accesses the workload tables, catalog tables, and EXPLAIN tables under
the DB2OSC schema on a DB2 for z/OS Version
8 compatibility mode subsystem.
- AOC5OEXJ
- For the SYSPROC.OPT_RUNSQL stored procedure, this package accesses
workload tables and EXPLAIN tables under the DB2OSC schema on a DB2 for z/OS Version
8 new-function mode or Version 10 conversion mode from Version 8 subsystem.
- AOC5OEXK
- For the SYSPROC.OPT_RUNSQL stored procedure, this package accesses
workload tables and EXPLAIN tables under the DB2OSC schema on a DB2 for z/OS Version
9 or Version 10 conversion mode from Version 9 subsystem.
- AOC5OEXM
- For the SYSPROC.OPT_RUNSQL stored procedure, this package accesses
workload tables and EXPLAIN tables under the DB2OSC schema on a DB2 for z/OS Version
10 new-function mode subsystem.
- AOC5OIAn where n={1-9, A-M}
- For the Index Advisor, this package accesses the tables that are
used by the Index Advisor to perform the workload-based index advisor
process. This package also accesses the catalog tables to get referential
constraints, table definitions, and statistics.
- AOC5OSA
- For the Statistics Advisor, this package accesses the statistics-profile
tables that are used by the Statistics Advisor to manage the statistics
profiles.
- AOC5OWCJ
- For the workload control center feature, this package accesses
workload tables, catalog tables, and EXPLAIN tables under the DB2OSC
schema on a DB2 for z/OS Version
8 new-function mode or Version 10 conversion mode from Version 8 subsystem.
- AOC5OWCK
- For the workload control center and workload monitoring features,
this package accesses workload tables, catalog tables, profile tables,
and EXPLAIN tables under the SYSIBM and DB2OSC schemas on a DB2 for z/OS Version
9 or Version 10 conversion mode from Version 9 subsystem.
- AOC5OWCM
- For the workload control center feature, this package accesses
workload tables, catalog tables, and EXPLAIN tables under the DB2OSC
schema on a DB2 for z/OS Version
10 new-function mode subsystem.
- AOC5OWQA
- For the Query Advisor, this package accesses tables that are used
by the Query Advisor to manage the analysis session and corresponding
operations.
- AOC5OWSJ
- For the SYSPROC.OPT_EXECUTE_TASK stored procedure, this package
accesses workload tables, catalog tables, and EXPLAIN tables under
the DB2OSC schema on a DB2 for z/OS Version
8 new-function mode or Version 10 conversion mode from Version 8 subsystem.
- AOC5OWSK
- For the SYSPROC.OPT_EXECUTE_TASK stored procedure, this package
accesses workload tables, catalog tables, and EXPLAIN tables under
the DB2OSC schema on a DB2 for z/OS Version
9 or Version 10 conversion mode from Version 9 subsystem.
- AOC5OWSM
- For the SYSPROC.OPT_EXECUTE_TASK stored procedure, this package
accesses workload tables, catalog tables, and EXPLAIN tables under
the DB2OSC schema on a DB2 for z/OS Version
10 new-function mode subsystem.
- AOC5OCWA
- For the Statistics Advisor, this package accesses tables that
are used by the Statistics Advisor to manage the analysis session
and corresponding operations on a DB2 for z/OS Version
8 compatibility mode subsystem.
- AOC5OCWC
- For the workload control center feature, this package accesses
workload tables, catalog tables, and EXPLAIN tables under the DB2OSC
schema on DB2 for z/OS Version
8 compatibility mode subsystem.
- AOC5OWSA
- For the Statistics Advisor, this package accesses tables that
are used by the Statistics Advisor to manage the analysis session
and corresponding operations on a DB2 for z/OS Version
8 new-function mode, Version 9, or Version 10 subsystem.
Package required for comparing access plans
- AOC5OAPC
- For the access plan comparison feature, this package accesses
the plan comparison table to perform the advisor process. It also
accesses the catalog tables to get information about user-specified DB2 packages
to be compared.
Migrating data objects when upgrading DB2 for z/OS
If you upgrade your DB2 for z/OS subsystem to a higher
version, and the subsystem is already configured for tuning with InfoSphere
Optim Query Workload Tuner, Version 3.1.1, you must migrate the data
objects in that configuration.
About this task
To migrate, modify and submit the corresponding AOCTIJ
JCL job. The following table lists the AOCTIJ JCL job files for each
environment.
Upgrading from |
Upgrading to |
JCL job for migrating data objects
for query tuning |
DB2 for z/OS Version 8 new-function
mode |
DB2 Version
10 for z/OS conversion
mode from DB2 for z/OS Version 8 |
AOCTIJC8 |
DB2 Version
10 for z/OS conversion
mode from DB2 for z/OS Version 8 |
DB2 Version
10 for z/OS new-function
mode |
AOCTIJ8X |
DB2 Version
9 for z/OS |
DB2 Version
10 for z/OS conversion
mode from DB2 Version
9 for z/OS |
AOCTIJC9 |
DB2 Version
10 for z/OS conversion
mode from DB2 Version
9 for z/OS |
DB2 Version
10 for z/OS new-function
mode |
AOCTIJ9X |
Procedure
- Upload to your subsystem the JCL file
that corresponds to the upgrade of your DB2 for z/OS subsystem. The JCL files are located in the directory <installation
directory for the IBM Data Studio client>\QueryTunerServerConfig\all_features\ZOS\migration.
Upload the file as an ASCII file.
- Follow the instructions in the Notes section of the comments
to customize the JCL for your environment, and then submit the job. For example, here are the instructions from the file AOCTIJ9X.
//* Notes =
//* PRIOR TO RUNNING THIS JOB, customize it for your system:
//* (1) Add a valid job card
//* (2) Locate and change all occurrences of the following strings
//* as indicated:
//* (A) '!DSN!' to the subsystem name of your DB2
//* (B) 'DSNTIA!!' to the plan name for DSNTIAD on your DB2
//* (C) 'DSN!!0' to the prefix of the target library for DB2
//* (D) '!AOCDBRM!' to the prefix of target library for QT
//* DBRMs
//* (E) 'USER!!' user id for individual explain tables
//* (F) '!GRANTEE! to one or more authorization IDs that
//* need to use stored procedures
//* SYSPROC.OPT_RUNSQL and
//* SYSPROC.OPT_EXECUTE_TASK
//* (G) '!USERID!' to the USER ID which will have execute
//* authority on packages
//* (H) 'PKGOWNER!' to the owner of the QT packages
//* (I) '!SQLID!' CURRENT SQLID which is set before execute
//* DDLs
//* (J) '!WLMENV4!' WLM environment name for SYSPROC.OPT_RUNSQL
//* (K) '!WLMENVJU!' WLM environment name for
//* SYSPROC.OPT_EXECUTE_TASK
//* and want to capture SQL from cache.
Attention: Step AOCSP is required only if you created the OPT_RUNSQL
and OPT_EXECUTE_TASK stored procedures in the configuration that you
are migrating. Creating these stored procedures when configuring a
subsystem for tuning is optional.
Running JCL to activate the license for InfoSphere Optim Query
Workload Tuner, Version 3.1.1
You can activate the license for InfoSphere Optim Query
Workload Tuner by running a JCL job, if you do not want the license
to be activated automatically by the IBM Data Studio full client.
About this task
A product license must be active on each DB2 for z/OS
subsystem that you connect to through the client when you are tuning
queries and query workloads.
When you connect to a DB2 for z/OS
subsystem from the IBM Data Studio full client and the license activation
kit is installed with the client, the client detects whether a license
is not yet present on the subsystem. If a license is not present,
the client installs and activates the license.
If you would
rather activate the license manually on a subsystem, you can run a
JCL job that is included in the installation directory for the client
after you install the license activation kit.
Important:
If
a license for version 3.1 of IBM InfoSphere Optim Query Workload Tuner
for DB2 for z/OS is already active on a subsystem that you want to
use for tuning, you do not need to follow these steps. The license
for version 3.1 gives you access to the new features and improvements
in version 3.1.1.
Procedure
- Upload the following file to the subsystem where you want
to activate the license: qwt_license.jcl This file is located in the QueryTunerServerConfig\all_features\ZOS\License\ folder
in the installation directory for the IBM Data Studio full client.
- Replace the variables $$DSN and $$USER with
values for your system environment and submit the job.
What to do next
In the Data Source Explorer view in the IBM Data Studio full
client, Version 3.1.1 or in the IBM Data Studio administration client,
Version 3.1.1, you can connect to the subsystem and begin tuning queries
and query workloads.
Optional tasks for configuring DB2 for z/OS subsystems for
tuning
Several of the tasks that are involved in configuring subsystems
for tuning are optional.
Enabling the SYSPROC.OPT_EXECUTE_TASK stored procedure on DB2 for z/OS subsystems
To gather EXPLAIN information for a query workload from
the database server, you must enable the SYSPROC.OPT_EXECUTE_TASK
stored procedure.
About this task
By enabling the SYSPROC.OPT_EXECUTE_TASK stored procedure,
you can gather and consolidate EXPLAIN information for a query workload
from the database server. The stored procedure must also be enabled
to consolidate literal values in EXPLAIN information.
Procedure
To enable the Java stored
procedure SYSPROC.OPT_EXECUTE_TASK:
- Ensure that the IBM SDK
for Java 2 Technology Edition,
Version 1.4 is installed in the UNIX System
Service folder that is specified by the JAVA_HOME environment
variable.
- Ensure that the IBM Data
Server Driver for JDBC and SQLJ is installed, the environment variables
are correctly set, and the properties are correctly configured. See the section "Installing the IBM Data Server Driver for
JDBC and SQLJ as part of a DB2 installation" in DB2 for z/OS
Application Programming Guide and Reference for Java for your version
of DB2 for z/OS for your version of DB2 for z/OS.
- Ensure that a WLM environment is set up for Java routines, and a started-task job for Java stored procedures exists in
the system procedure library. See the section "Setting
up the WLM application environment for Java routines" in DB2
for z/OS Application Programming Guide and Reference for Java for
your version of DB2 for z/OS for your version of DB2 for z/OS.
- Ensure that users have permission to write to the temporary z/OS UNIX directory.
The SYSPROC.OPT_EXECUTE_TASK stored procedure writes log files to
this directory.
Example
In the following sample, aocwccsp.jar is
the Java stored procedure JAR
file. This file is installed in two locations in the product. You
can use either copy. They are identical to each other.
- One copy of this file is installed with the IBM Data
Studio client.
You can find this file in the \serverconfig\WCC subdirectory
of the installation directory. If you use this copy, you must upload
it to the location that is specified by CLASSPATH.
Tip:
Use binary format when you upload this file to the subsystem.
- The other copy of this file is installed on the subsystem during
the SMP/E installation. The HFS path is /usr/lpp/oqt/v31/lib/IBM.
If you use this copy, place a copy in the location that is specified
by CLASSPATH.
JCC_HOME is the home directory
of the JDBC driver, and JAVA_HOME is the home
directory of the Java. You must
change the value of JCC_HOME and JAVA_HOME,
depending on the location of the JDBC driver and runtime environment.
You must also change the value of TZ, which represents
the time zone, to your local time zone, for example:
- PST08 for Pacific Standard Time.
- MST07 for Mountain Standard Time.
- CST06 for Central Standard Time.
- CET-01 for Central European Time.
ENVAR("CLASSPATH=/usr/lpp/db2910_base/classes/aocwccsp.jar",
"TZ=PST08",
"JCC_HOME=/usr/lpp/db2910_jdbc/",
"JAVA_HOME=/usr/lpp/java140/J1.4"),
MSGFILE(JSPDEBUG,,,,ENQ),
XPLINK(ON)
Enabling the SYSPROC.OPT_RUNSQL stored procedure on DB2 for z/OS subsystems
By enabling the SYSPROC.OPT_RUNSQL stored
procedure, you can capture the SQL statements from dynamic statement
caches and store these statements in the DSN_STATEMENT_CACHE_TABLE
table, if you do not have the privilege to run the EXPLAIN STMTCACHE
ALL statement dynamically.
Procedure
To enable the C stored procedure SYSPROC.OPT_RUNSQL:
- Upload the tersed load module to the subsystem. You
can find the load module in the \serverconfig\ZOS\z/OS_version_number_and_mode\Load
Module subdirectory of the installation directory.
Ensure
that the following transfer rules are specified.
ftp> quote site blk=6144 lrecl=1024 recfm=fb tracks unit=sysallda primary=90
200 SITE command was accepted
ftp> binary
200 Representation type is Image
- Use the following sample job to unterse the load module
to a partition data set.
//***************************************************************
//* Notes =
//* PRIOR TO RUNNING THIS JOB,
//* locate and change the string "!!" to the following suffix
//* for the different versions of DB2 for z/OS:
//* For V8 and V10CM8, change to "J".
//* For V9 and V10CM9, change to "K".
//* For V10NFM, change to "M".
//***************************************************************
//UNTERSE JOB CLASS=A,MSGLEVEL=(1,1),
// NOTIFY=&SYSUID
//UNTERSE EXEC PGM=TRSMAIN,PARM='UNPACK'
//STEPLIB DD DISP=SHR,DSN=IBMUSER.TERSE.LOADLIB
//SYSPRINT DD SYSOUT=*,DCB=(LRECL=1024,BLKSIZE=6144,RECFM=FB)
//INFILE DD DISP=SHR,DSN=USER.TERSED.AOCRNSQ!!
//OUTFILE DD DSN=USER.UNTERSED,
// DISP=(SHR,CATLG,DELETE),
// UNIT=3390,VOL=SER=DK8320,SPACE=(CYL,(5,5),RLSE)
/*
- Copy the untersed load module to your user EXIT library.
- Ensure that the EXIT library is concatenated
to your DB2 start task STEPLIB.
Setting up the administrative scheduler
You have the option of using the administrative scheduler
to schedule tasks on query workloads, such as workload capture and
monitoring.
Without the administrative scheduler, you can schedule tasks,
but you must keep the client application running on your workstation
and connected to the DB2 for z/OS subsystem for the operations to
run when scheduled. If the connection to the subsystem is dropped
while scheduled tasks are running, those tasks cannot complete.
Setting up the administrative scheduler on DB2 UDB for z/OS,
Version 8
You can use the administrative task scheduler to execute
administrative tasks according to a time- or event-based schedule.
About this task
Procedure
- Set up the administrative task scheduler.
- Customize the administrative task scheduler address
space startup procedure (xxxxADMT). The name of the startup
procedure needs to match the value of the ADMTPROC subsystem parameter.
Before using the startup procedure, locate and review the settings
for the following parameters:
- DB2SSID
- The name of this DB2® subsystem.
- DFLTUID
- The default ID that is used by the administrative task scheduler
to execute its tasks. DFLTUID must be different than the ID that is
used to start this address space.
- TRACE
- Indicates whether to activate tracing for the admin scheduler.
The value can be ON or the default value of OFF.
You can specify other parameters, such as ERRFREQ or
MAXTHD, or STOPONDB2STOP in the startup procedure similarly to the
three parameters DB2SSID, DFLTUID and TRACE. These additional administrative
task scheduler parameters are described in the information about scheduling
administrative tasks.
- Run job DSNTIJRA to define the administrative task scheduler
started task module to RACF® program control and to define the administrative
task scheduler as a trusted context in RACF. In DB2 data
sharing environments, DSNTIJRA needs to be customized and run for
each member of the group.
- Enable the administrative task scheduler routines.
- Create a job to make image copies of the administrative
task scheduler table spaces. Establish a schedule for
making image copies. In general, you should back up important databases
on a regular basis. The database for the administrative task scheduler
is no exception and should be copied on the same frequency as the
DB2 catalog and directory. The following example shows how to copy
the table spaces in this database to stacked data sets on tape with
a retention period of 99 days:
//*
//DSNTIC EXEC PGM=DSNUTILB,PARM='DSN,IMAGCOPY',COND=(4,LT)
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//DSNTRACE DD SYSOUT=*
//SYSLISTD DD *
LISTDEF DSNLDEF
INCLUDE TABLESPACES DATABASE DSNADMDB
//SYSIN DD *
TEMPLATE DSNTPLT
DSN(prefix.IMAGCOPY.&DB..;&TS.;)
DISP (NEW,KEEP,DELETE)
UNIT TAPE
STACK YES
RETPD 99
COPY LIST DSNLDEF
COPYDDN(DSNTPLT)
/*
- Enable the administrative task scheduler and administrative
enablement routines The administrative task scheduler routines
are DB2®-supplied routines that enable you to schedule work. The administrative
enablement routines are DB2-supplied routines that enable you to execute
administrative work immediately.
The following routines are administrative task scheduler routines
- ADMIN_TASK_ADD
- ADMIN_TASK_LIST
- ADMIN_TASK_REMOVE
- ADMIN_TASK_STATUS
The following routines are administrative enablement routines
- ADMIN_COMMAND_DB2
- ADMIN_COMMAND_DSN
- ADMIN_COMMAND_UNIX
- ADMIN_DS_BROWSE
- ADMIN_DS_DELETE
- ADMIN_DS_LIST
- ADMIN_DS_RENAME
- ADMIN_DS_SEARCH
- ADMIN_DS_WRITE
- ADMIN_INFO_HOST
- ADMIN_INFO_SSID
- ADMIN_INFO_SYSPARM
- ADMIN_JOB_CANCEL
- ADMIN_JOB_FETCH
- ADMIN_JOB_QUERY
- ADMIN_JOB_SUBMIT
- ADMIN_UTL_SCHEDULE
- ADMIN_UTL_SORT
- If you did not create these procedures during installation
or migration, customize and run job steps DSNTIAS, DSNTIAE, and DSNTIJG
of your customized job DSNTIJSG to define, bind and grant access to
the administrative task scheduler and administrative enablement stored
procedures.
Job step DSNTIJG grants EXECUTE authority
on these stored procedures and their packages to PUBLIC, and grants
ALL on related result set tables to PUBLIC. If you do not want this
authorization granted to PUBLIC, edit the job to grant the authority
only to specific users or groups.
Restriction:
This
job should be executed by a user with all the specific privileges
needed.
These stored procedures run in a WLM-managed stored
procedure address space.
- In the JCL for starting the WLM-established address
space for running the stored procedures whose load module must reside
in an APF-authorized library, ensure that all libraries in the STEPLIB
DD concatenation are APF-authorized.
In the JCL for
starting the WLM-established address space for running the following
stored procedures: ADMIN_COMMAND_DB2, ADMIN_COMMAND_UNIX, ADMIN_INFO_HOST,
ADMIN_INFO_SSID, ADMIN_JOB_FETCH, ADMIN_JOB_CANCEL, ADMIN_JOB_QUERY,
ADMIN_JOB_SUBMIT, ADMIN_TASK_ADD, ADMIN_TASK_LIST, ADMIN_TASK_REMOVE,
ADMIN_TASK_STATUS, ADMIN_UTL_SCHEDULE and ADMIN_UTL_SORT, ensure that
the library prefix.SDSNLOD2 is added to the STEPLIB DD concatenation
following the library prefix.SDSNLOAD.
The name of the WLM environment
must match the WLM ENVIRONMENT parameter value in the CREATE PROCEDURE
statement for each stored procedure.
- If the BPX.DAEMON facility class is active and the BPX.DAEMON.HFSCTL
facility class is not defined, perform the following actions for the
stored procedures that must be registered to RACF® program control.
The stored procedures that require RACF program control are:
- ADMIN_COMMAND_UNIX
- ADMIN_JOB_CANCEL
- ADMIN_JOB_FETCH
- ADMIN_JOB_QUERY
- ADMIN_JOB_SUBMIT
- Customize and run job step DSNADER in job DSNTIJRA to define the
security environment for these stored procedures. You need to uncomment
job step DSNADER before you run job DSNTIJRA. You do not need to run
any other job steps in job DSNTIJRA.
- Create a WLM environment for these stored procedures that meets
the following requirements:
- The WLM-established stored procedure address space loads only
controlled programs.
- In the JCL for starting the WLM-established address space for
running these stored procedures, ensure that all libraries in the
STEPLIB DD concatenation are APF-authorized and that the library prefix.SDSNLOD2
is added to the STEPLIB DD concatenation following the library prefix.SDSNLOAD.
- The name of this environment must match the WLM ENVIRONMENT parameter
value in the CREATE PROCEDURE statement for these stored procedures
in the job DSNTIJSG.
Setting up the administrative scheduler on DB2 Version 9.1
for z/OS
You can use the administrative task scheduler to execute
administrative tasks according to a time-based or event-based schedule.
Before you begin
You should have run job DSNTIJRA and job DSNTIJIN before you
set up the administrative task scheduler.
About this task
Restriction:
After migration to conversion mode,
the administrative task scheduler is disabled until you run job DSNTIJSG.
Procedure
- Set up the administrative task scheduler.
- Customize the administrative task scheduler address
space startup procedure (xxxxADMT) in job DSNTIJMV. The
name of the startup procedure needs to match the value of the ADMTPROC
subsystem parameter. Before using the startup procedure, locate and
review the settings for the following parameters:
- DB2SSID
- The name of this DB2® subsystem.
- DFLTUID
- The default ID that is used by the administrative task scheduler
to execute its tasks. DFLTUID must be different than the ID that is
used to start this address space.
- TRACE
- Indicates whether to activate tracing for the administrative task
scheduler. The value can be ON or the default value of OFF.
You can specify other parameters, such as ERRFREQ,
MAXHIST, MAXTHD, or STOPONDB2STOP in the startup procedure similarly
to the three parameters DB2SSID, DFLTUID and TRACE.
- ERRFREQ
- Specifies how frequently message DSNA679I displays on the console,
indicating that one of the redundant, active copies of the task list
is not accessible. By default, this message displays on the console
once per minute, for example, when DB2 is offline.
- MAXHIST
- Specifies the number of status entries per task that are stored.
This parameter is a positive integer with a default value of 10. When
the limit is reached, the oldest status entries are deleted.
- MAXTHD
- Specifies the maximum number of execution threads for an administrative
task scheduler. The default value is 99.
- STOPONDB2STOP
- Specifies that the administrative task scheduler will terminate
when DB2 is stopped.
Also, locate and review the ADMTDD1
DD statement. The VSAM data set that is indicated must match the VSAM
task list data set that is defined in job DSNTIJIN.
The following
example shows how these parameters can be updated.
//DSNADMT PROC LIB='DSN!!0.SDSNLOAD',
// DB2SSID=DSN,
// DFLTUID=DFLTUID,
// TRACE=OFF,
// MAXTHD=10
//*
//STARTADM EXEC PGM=DSNADMT0,DYNAMNBR=100,REGION=0K,
// PARM=('DB2SSID=&DB2SSID',
// ' DFLTUID=&DFLTUID',
// ' TRACE=&TRACE'
// ' MAXTHD=&MAXTHD'
// ' ERRFREQ=1440'
// ' STOPONDB2STOP')
//STEPLIB DD DISP=SHR,DSN=&LIB
//ADMTDD1 DD DISP=SHR,DSN=DSNC!!0.TASKLIST
- Ensure that the administrative task scheduler routines
were enabled. They were enabled in steps DSNTIAS and DSNTIJG of job
DSNTIJSG.
- Create a job to make image copies of the administrative
task scheduler table spaces. Establish a schedule for
making image copies. In general, you should back up important databases
on a regular basis. The database for the administrative task scheduler
is no exception and should be copied on the same frequency as the
DB2 catalog and directory. The following example shows how to copy
the table spaces in this database to stacked data sets on tape with
a retention period of 99 days:
//*
//DSNTIC EXEC PGM=DSNUTILB,PARM='DSN,IMAGCOPY',COND=(4,LT)
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//DSNTRACE DD SYSOUT=*
//SYSLISTD DD *
LISTDEF DSNLDEF
INCLUDE TABLESPACES DATABASE DSNADMDB
//SYSIN DD *
TEMPLATE DSNTPLT
DSN(prefix.IMAGCOPY.&DB..;&TS.;)
DISP (NEW,KEEP,DELETE)
UNIT TAPE
STACK YES
RETPD 99
COPY LIST DSNLDEF
COPYDDN(DSNTPLT)
/*
- Enable the administrative task scheduler and administrative
enablement routines.
The administrative task scheduler
routines are DB2®-supplied routines that enable you to schedule work.
The administrative enablement routines are DB2-supplied routines that
enable you to execute administrative work immediately.
Before
starting this step, set up the DB2-supplied routines that it enables.
For instructions, see the section "Enabling DB2-supplied routines"
in DB2 for z/OS Installation Guide at http://publib.boulder.ibm.com/epubs/pdf/dsnigk1f.pdf.
The
following routines are administrative task scheduler routines:
- ADMIN_TASK_ADD
- ADMIN_TASK_CANCEL
- ADMIN_TASK_LIST
- ADMIN_TASK_OUTPUT
- ADMIN_TASK_REMOVE
- ADMIN_TASK_STATUS
- ADMIN_TASK_UPDATE
The following routines are administrative enablement routines:
- ADMIN_COMMAND_DB2
- ADMIN_COMMAND_DSN
- ADMIN_COMMAND_UNIX
- ADMIN_DS_BROWSE
- ADMIN_DS_DELETE
- ADMIN_DS_LIST
- ADMIN_DS_RENAME
- ADMIN_DS_SEARCH
- ADMIN_DS_WRITE
- ADMIN_INFO_HOST
- ADMIN_INFO_SMS
- ADMIN_INFO_SQL
- ADMIN_INFO_SSID
- ADMIN_INFO_SYSLOG
- ADMIN_INFO_SYSPARM
- ADMIN_JOB_CANCEL
- ADMIN_JOB_FETCH
- ADMIN_JOB_QUERY
- ADMIN_JOB_SUBMIT
- ADMIN_UTL_SCHEDULE
- ADMIN_UTL_SORT
- If you did not create these procedures during installation
or migration, customize and run job steps DSNTIAS, DSNTIAE, and DSNTIJG
of your customized job DSNTIJSG to define, bind, and grant access
to the administrative task scheduler and administrative enablement
stored procedures.
Job step DSNTIJG grants EXECUTE
authority on these stored procedures and their packages to PUBLIC,
and grants ALL on related result set tables to PUBLIC. If you do not
want this authorization granted to PUBLIC, edit the job to grant the
authority only to specific users or groups.
Tip:
Do
not grant EXECUTE authority to PUBLIC. If you do, anyone that can
access DB2 can use these stored procedures.
Restriction:
This job should be executed by a user with all
the specific privileges needed.
These stored procedures run
in a WLM-managed stored procedure address space.
- In the JCL for starting the WLM-established address
space for running the stored procedures whose load module must reside
in an APF-authorized library, ensure that all libraries in the STEPLIB
DD concatenation are APF-authorized. The name of the WLM environment
must match the WLM ENVIRONMENT parameter value in the CREATE PROCEDURE
statement for each stored procedure.
- If the BPX.DAEMON facility class is active and the BPX.DAEMON.HFSCTL
facility class is not defined, perform the following actions for the
stored procedures that must be registered to RACF® program control.
The stored procedures that require RACF program control are:
- ADMIN_COMMAND_UNIX
- ADMIN_JOB_CANCEL
- ADMIN_JOB_FETCH
- ADMIN_JOB_QUERY
- ADMIN_JOB_SUBMIT
- Customize and run job step DSNADER in job DSNTIJRA to define the
security environment for these stored procedures. You need to uncomment
job step DSNADER before you run job DSNTIJRA. You do not need to run
any other job steps in job DSNTIJRA.
- Ensure that you have a WLM environment for these stored procedures
that meets the following requirements:
- The WLM-established stored procedure address space loads only
controlled programs.
- In the JCL for starting the WLM-established address space for
running these stored procedures, ensure that all libraries in the
STEPLIB DD concatenation are APF-authorized.
- The name of this environment must match the WLM ENVIRONMENT parameter
value in the CREATE PROCEDURE statement for these stored procedures
in the job DSNTIJSG.
- Authorize the appropriate users to use one or more of
these stored procedures by issuing the GRANT EXECUTE statement.
Important:
The privileges to run DSN8EXP and DSNAEXP
should be granted with consideration that DSN8EXP and DSNAEXP can
EXPLAIN on any explainable SQL statement that is valid on the system,
and that EXPLAIN output can reveal potentially sensitive information.
For example, you should not grant access to PUBLIC to use DSN8EXP
and DSNAEXP.
Setting up the administrative scheduler on DB2 Version 10 for
z/OS
You can use the administrative task scheduler to execute
administrative tasks according to a time-based or event-based schedule.
Before you begin
You should have run job DSNTIJRA and job DSNTIJIN before you
set up the administrative task scheduler.
About this task
Restriction:
After migration to conversion mode
(from Version 8 or Version 9.1), the administrative task scheduler
is disabled until you run job DSNTIJRT.
Procedure
- Set up the administrative task scheduler.
- Customize the administrative task scheduler address
space startup procedure (xxxxADMT) in job DSNTIJMV. The
name of the startup procedure needs to match the value of the ADMTPROC
subsystem parameter. Before using the startup procedure, locate and
review the settings for the following parameters:
- DB2SSID
- The name of this DB2® subsystem.
- DFLTUID
- The default ID that is used by the administrative task scheduler
to execute its tasks. DFLTUID must be different than the ID that is
used to start this address space.
- TRACE
- Indicates whether to activate tracing for the administrative task
scheduler. The value can be ON or the default value of OFF.
You can specify other parameters, such as ERRFREQ,
MAXHIST, MAXTHD, or STOPONDB2STOP in the startup procedure similarly
to the three parameters DB2SSID, DFLTUID and TRACE.
- ERRFREQ
- Specifies how frequently message DSNA679I displays on the console,
indicating that one of the redundant, active copies of the task list
is not accessible. By default, this message displays on the console
once per minute, for example, when DB2 is offline.
- MAXHIST
- Specifies the number of status entries per task that are stored.
This parameter is a positive integer with a default value of 10. When
the limit is reached, the oldest status entries are deleted.
- MAXTHD
- Specifies the maximum number of execution threads for an administrative
task scheduler. The default value is 99.
- STOPONDB2STOP
- Specifies that the administrative task scheduler will terminate
when DB2 is stopped.
Also, locate and review the ADMTDD1 DD statement.
The VSAM data set that is indicated must match the VSAM task list
data set that is defined in job DSNTIJIN.
The following example
shows how these parameters can be updated.
//DSNADMT PROC LIB='DSN!!0.SDSNLOAD',
// DB2SSID=DSN,
// DFLTUID=DFLTUID,
// TRACE=OFF,
// MAXTHD=10
//*
//STARTADM EXEC PGM=DSNADMT0,DYNAMNBR=100,REGION=0K,
// PARM=('DB2SSID=&DB2SSID',
// ' DFLTUID=&DFLTUID',
// ' TRACE=&TRACE'
// ' MAXTHD=&MAXTHD'
// ' ERRFREQ=1440'
// ' STOPONDB2STOP')
//STEPLIB DD DISP=SHR,DSN=&LIB
//ADMTDD1 DD DISP=SHR,DSN=DSNC!!0.TASKLIST
- Ensure that the administrative task scheduler routines
were enabled. They were enabled by job DSNTIJRT.
- Create a job to make image copies of the administrative
task scheduler table spaces. Establish a schedule for
making image copies. In general, you should back up important databases
on a regular basis. The database for the administrative task scheduler
is no exception and should be copied on the same frequency as the
DB2 catalog and directory. The following example shows how to copy
the table spaces in this database to stacked data sets on tape with
a retention period of 99 days:
//*
//DSNTIC EXEC PGM=DSNUTILB,PARM='DSN,IMAGCOPY',COND=(4,LT)
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//DSNTRACE DD SYSOUT=*
//SYSLISTD DD *
LISTDEF DSNLDEF
INCLUDE TABLESPACES DATABASE DSNADMDB
//SYSIN DD *
TEMPLATE DSNTPLT
DSN(prefix.IMAGCOPY.&DB..;&TS.;)
DISP (NEW,KEEP,DELETE)
UNIT TAPE
STACK YES
RETPD 99
COPY LIST DSNLDEF
COPYDDN(DSNTPLT)
/*
- Enable the administrative task scheduler and administrative
enablement routines.
The administrative task scheduler
routines are DB2®-supplied routines that enable you to schedule work.
The administrative enablement routines are DB2-supplied routines that
enable you to execute administrative work immediately.
Before
you complete these steps, set up WLM application environments for
the administrative task scheduler and administrative enablement routines,
and either install the DB2-supplied routines during migration or install
the DB2-supplied routines during installation. The steps for setting
up WLM application environments and installing the DB2-supplied routines
are in the IBM Information Management Software for z/OS Solutions
Information Center and the DB2 for z/OS Installation
and Migration Guide at http://publib.boulder.ibm.com/epubs/pdf/dsnigm05.pdf in
the following locations:
- For instructions on setting up WLM application environments for
the administrative task scheduler and administrative enablement routines:
- For instructions on installing the DB2-supplied routines during
migration:
- For instructions on installing the DB2-supplied routines during
installation:
The following routines are administrative task scheduler
routines:
- ADMIN_TASK_ADD
- ADMIN_TASK_CANCEL
- ADMIN_TASK_LIST
- ADMIN_TASK_OUTPUT
- ADMIN_TASK_REMOVE
- ADMIN_TASK_STATUS
- ADMIN_TASK_UPDATE
The following routines are administrative enablement routines:
- ADMIN_COMMAND_DB2
- ADMIN_COMMAND_DSN
- ADMIN_COMMAND_UNIX
- ADMIN_DS_BROWSE
- ADMIN_DS_DELETE
- ADMIN_DS_LIST
- ADMIN_DS_RENAME
- ADMIN_DS_SEARCH
- ADMIN_DS_WRITE
- ADMIN_INFO_HOST
- ADMIN_INFO_SMS
- ADMIN_INFO_SQL
- ADMIN_INFO_SSID
- ADMIN_INFO_SYSLOG
- ADMIN_INFO_SYSPARM
- ADMIN_JOB_CANCEL
- ADMIN_JOB_FETCH
- ADMIN_JOB_QUERY
- ADMIN_JOB_SUBMIT
- ADMIN_UTL_EXECUTE
- ADMIN_UTL_MODIFY
- ADMIN_UTL_MONITOR
- ADMIN_UTL_SCHEDULE
- ADMIN_UTL_SORT
As part of the installation and migration process, job DSNTIJRT
defines, binds, and grants access to these DB2-supplied routines.
- In the JCL for starting the WLM-established address
space for running the stored procedures whose load module must reside
in an APF-authorized library, ensure that all libraries in the STEPLIB
DD concatenation are APF-authorized.
- If the BPX.DAEMON facility class is active and the BPX.DAEMON.HFSCTL
facility class is not defined, perform the following actions for the
stored procedures that must be registered to RACF® program control.
The stored procedures that require RACF program control are:
- ADMIN_COMMAND_UNIX
- ADMIN_JOB_CANCEL
- ADMIN_JOB_FETCH
- ADMIN_JOB_QUERY
- ADMIN_JOB_SUBMIT
- Customize and run job step DSNADER in job DSNTIJRA to define the
security environment for these stored procedures. You need to uncomment
job step DSNADER before you run job DSNTIJRA. You do not need to run
any other job steps in job DSNTIJRA.
- Ensure that you have a WLM environment for these stored procedures
that meets the following requirements:
- The WLM-established stored procedure address space loads only
controlled programs.
- In the JCL for starting the WLM-established address space for
running these stored procedures, ensure that all libraries in the
STEPLIB DD concatenation are APF-authorized.
- The name of this environment must match the WLMENV parameter value
in the configuration control statement for each stored procedure in
job DSNTIJRT.
Recommendation:
If you do not have
a WLM environment defined to run these routines, use DB2 core WLM
environment DSNWLM_GENERAL, except as follows:
- ADMIN_COMMAND_DSN - Use DSNWLM_REXX
- ADMIN_COMMAND_UNIX - Use DSNWLM_PGM_CONTROL
- ADMIN_INFO_SYSLOG - Use DSNWLM_REXX
- ADMIN_INFO_SYSPARM - Use DSNWLM_NUMTCB1
- ADMIN_JOB_CANCEL - Use DSNWLM_PGM_CONTROL
- ADMIN_JOB_FETCH - Use DSNWLM_PGM_CONTROL
- ADMIN_JOB_QUERY - Use DSNWLM_PGM_CONTROL
- ADMIN_JOB_SUBMIT - Use DSNWLM_PGM_CONTROL
- ADMIN_UTL_MONITOR - Use DSNWLM_PGM_CONTROL
To be able to capture a workload from the statement cache
on all subsystems of a data sharing group, you must configure a database
group in the client. This database group must contain the same members
of the data sharing group in a Parallel Sysplex® in your DB2 for z/OS environment.
Before you begin
- Ensure that the data sharing group that you want to access exists
in a Parallel Sysplex in
your DB2 for z/OS environment.
- Ensure that you know the connection details of the subsystems
in the data sharing group.
About this task
A data sharing group is a collection of one or more DB2 for z/OS subsystems
that share DB2 data. A DB2 subsystem that belongs to a
data sharing group is a member of that group. Each member can belong
to one, and only one, data sharing group. All members of a data sharing
group share the same DB2 catalog
and directory, and all members must be in the same Parallel Sysplex. Data sharing improves
the availability of DB2 data,
extends the processing capacity of the system, provides more flexible
ways to configure your environment, and increases transaction rates.
The
SQL statements that are used by an application can run on multiple
subsystems. If these subsystems are members of a data sharing group,
you can configure a database group in the client to contain the same
members. You can then define a workload to contain all SQL statements
of the application from the statement cache, and capture the workload
from the data sharing group.
For information about how data
sharing work, see these references:
- For DB2 UDB for z/OS, Version 8:
- For DB2 Version 9.1 for z/OS:
- For DB2 10 for z/OS:
Procedure
To configure a database group in the client:
- To open the Database Groups view, click the Database Groups
tab. If the tab is not visible, click Window > Show View > Database
Groups.
- Configure a database group by creating a group and adding
the same members of the data sharing group. Ensure that
the connection profiles of a subsystem are correct when you add the
subsystem to a group.