The DB2® Design Advisor
advises users on the creation of materialized query tables (MQTs)
and indexes, the repartitioning of tables, the conversion to multidimensional
clustering (MDC) tables, and the deletion of unused objects.
The recommendations are based on one or more SQL statements
provided by the user. A group of related SQL statements is known as
a workload. Users can rank the importance of each statement
in a workload and specify the frequency at which each statement in
the workload is to be executed. The Design Advisor outputs a DDL
CLP script that includes CREATE INDEX, CREATE SUMMARY TABLE (MQT),
and CREATE TABLE statements to create the recommended objects.
Structured
type columns are not considered when this command is executed.
Authorization
Read access to the database.
Read and write access to the explain tables. If materialized query
tables (MQTs) are used, you must have CREATE TABLE authorization,
and read and write access to the MQTs.
Required connection
None. This command establishes
a database connection.
Command syntax
>>-db2advis--+- -d--+--database-name---------------------------->
'- -db-'
>--+---------------------------------------------------------------------------------------------------------+-->
+- -w--workload-name--------------------------------------------------------------------------------------+
+- -s--"statement"----------------------------------------------------------------------------------------+
+- -i--filename-------------------------------------------------------------------------------------------+
+- -g-----------------------------------------------------------------------------------------------------+
'- -wlm--evmonname--+-----------------------------------------------------+--+--------------------------+-'
+-+-workloadname-+--workloadname----------------------+ '-start-time--+----------+-'
| '-wl-----------' | '-end-time-'
'-+-serviceclass-+--superclassname--+---------------+-'
'-sc-----------' '-,subclassname-'
>--+--------------------------+--+------------------+----------->
'- -a--userid--+---------+-' '- -m--advise-type-'
'-/passwd-'
>--+-----+--+-----+--+-----------------+------------------------>
'- -x-' '- -u-' '- -l--disk-limit-'
>--+----------------------+--+---------------+--+-----+--------->
'- -t--max-advise-time-' '- -k--+-HIGH-+-' '- -f-'
+-MED--+
+-LOW--+
'-OFF--'
>--+-----+--+------------------+--+------------------+---------->
'- -r-' '- -n--schema-name-' '- -q--schema-name-'
>--+----------------------+--+----------------------+----------->
'- -b--tablespace-name-' '- -c--tablespace-name-'
>--+-----+--+-----+--+--------------+--+---------+-------------->
'- -h-' '- -p-' '- -o--outfile-' '- -nogen-'
>--+---------------+--+----------------------------+------------>
'- -delim--char-' '- -mdcpctinflation--percent-'
>--+----------------------------------+--+---------+-----------><
'- -tables--table-predicate-clause-' '- -noxml-'
Command parameters
- -d database-name
- Specifies the name of the database to which a connection is to
be established.
- -w workload-name
- Specifies
the name of the workload to be assessed and have indexes suggested
by the Design Advisor. This name is used in the ADVISE_WORKLOAD table.
This option cannot be specified with the -g, -i,
or -s options.
- -s "statement"
- Specifies
the text of a single SQL statement to be assessed and have indexes
suggested by the Design Advisor. The statement must be enclosed by
double quotation marks. This option cannot be specified with the -g, -i,
or -w options.
- -i filename
- Specifies the name of an input file containing
one or more SQL statements. The default is standard input. Identify
comment text with two hyphens at the start of each line; that is, -- comment.
Statements must be delimited by semicolons.
The frequency at which
each statement in the workload is to be executed can by changed by
inserting the following line into the input file:
--#SET FREQUENCY x
The
frequency can be updated any number of times in the file.
This option cannot be specified with the -g, -s,
or -w options.
- -g
- Specifies
the retrieval of the SQL statements from a dynamic SQL snapshot. If
combined with the -p command parameter, the SQL
statements are kept in the ADVISE_WORKLOAD table. This option cannot
be specified with the -i, -s,
or -w options.
- -wlm evmonname
- Specifies to get the table names corresponding to the ACTIVITY
and ACTIVITYSTMT logical data groups from SYSCAT.EVENTTABLES for event
name evmonname, and joins them together on ACTIVATE_TIMESTAMP,
ACTIVITY_ID and ACTIVITY_SECONDARY_ID for records that have PARTIAL_RECORD
= 0 (completed transactions). An optional start-time and end-time timestamp
can be added to get statements on or after the start-time and,
optionally, on or before the end-time. start-time and end-time are
with respect to the TIME_COMPLETED column from the ACTIVITY tables.
- workloadname | wl workloadname
- Specifies the workloadname that is searched
for in SYSCAT.WORKLOADS. The ACTIVITY event monitor table is joined
with SYSCAT.WORKLOADS on the workload id to obtain these statements.
- serviceclass | sc superclassname
- Specifies the service class information which comes from SYSCAT.SERVICECLASSES.
When no subclass is given, all statements for a service superclass
is retrieved, which is basically the PARENTSERVICECLASS in SYSCAT.SERVICECLASSES.
The ACTIVITY event monitor table is joined with SYSCAT.SERVICECLASSES
on the service class id to obtain these statements.
- ,subclassname
- Specifies the subclassname if a superclassname is
specified; separated by a comma. This parameter is optional.
- start-time
- Specifies the start timestamp.
- end-time
- Specifies the end timestamp. This parameter is optional.
- -a userid/passwd
- Name and password used to connect to the database. The slash (⁄)
must be included if a password is specified. A password should not
be specified if the -x option is specified.
- -m advise-type
- Specifies the type of recommendation the advisor will return.
Any combination of I, M, C,
and P (in upper- or lowercase) can be specified.
For example, db2advis -m PC will recommend partitioning
and MDC tables. If -m P or -m M are
used in a partitioned database environment, the advise_partition table
is populated with the final partition recommendation. The choice of
possible values are:
- I
- Recommends new indexes. This is the default.
- M
- Recommends new materialized query tables (MQTs) and indexes on
the MQTs. In partitioned database environments, partitioning on MQTs
is also recommended.
- C
- Recommendation to convert standard tables to multidimensional
clustering (MDC) tables; or, to create a clustering index on the tables.
- P
- Recommends the repartitioning of existing tables.
- -x
- Specifies that the password will be read from the terminal or
through user input.
- -u
- Specifies that the advisor will consider the recommendation of
deferred MQTs. Incremental MQTs will not be recommended. When this
option is specified, comments in the DDL CLP script indicate which
of the MQTs could be converted to immediate MQTs. If immediate MQTs
are recommended in a partitioned database environment, the default
distribution key is the implied unique key for the MQT.
- -l disk-limit
- Specifies the number of megabytes available for all recommended
indexes and materialized views in the existing schema. Specify -1 to
use the maximum possible size. The default value is 20% of the total
database size.
- -t max-advise-time
- Specifies the maximum allowable time, in minutes, to complete
the operation. If no value is specified for this option, the operation
will continue until it is completed. To specify an unlimited time
enter a value of zero. The default is zero.
- -k
- Specifies to what degree the workload will be compressed. Compression
is done to allow the advisor to reduce the complexity of the advisor's
execution while achieving similar results to those the advisor could
provide when the full workload is considered. HIGH indicates
the advisor will concentrate on a small subset of the workload. MED indicates
the advisor will concentrate on a medium-sized subset of the workload. LOW indicates
the advisor will concentrate on a larger subset of the workload. OFF indicates
that no compression will occur and every query is considered. The
default is MED.
- -f
- Drops previously existing simulated catalog tables.
- -r
- Specifies that detailed statistics should be used for the virtual
MQTs and for the partitioning selection. If this option is not specified,
the default is to use optimizer statistics for MQTs. Although the
detailed statistics might be more accurate, the time to derive them
will be significant and will cause the db2advis
execution time to be greater. The -r command
parameter uses sampling to obtain relevant statistics for MQTs and
partitioning. For MQTs, when the sample query either fails or returns
no rows, the optimizer estimates are used.
- -n schema-name
- Specifies the qualifying name of simulation catalog tables, and
the qualifier for the new indexes and MQTs. The default schema name
is the caller's user ID, except for catalog simulation tables where
the default schema name is SYSTOOLS. The default is for new indexes
to inherit the schema name of the index's base.
- -q schema-name
- Specifies the qualifying name of unqualified names in the workload.
It serves as the schema name to use for CURRENT SCHEMA when db2advis executes.
The default schema name is the user ID of the person executing the
command.
- -b tablespace-name
- Specifies the name of a table space in which new MQTs will be
created. If not specified, the advisor will select the table spaces
from the set of table spaces that exist.
- -c tablespace-name
- Specifies the name of a table space (where the table space can
be of any type, for example, use a file name or directory) in which
to create the simulation catalog tables. This table space must only
be created on the catalog database partition group. The default is USERSPACE1.
It is recommended that the user create the table space employed
for the simulation instead of using the default USERSPACE1.
In addition, the ALTER TABLESPACE DROPPED TABLE RECOVERY OFF statement
should be run on this table space to improve the performance of the db2advis utility.
When the utility completes, turn the history back on for the table
space. In a partitioned database environment, this option is required
as USERSPACE1 is usually created across all partition groups.
- -h
- Display help information. When this option is specified, all other
options are ignored, and only the help information is displayed.
- -p
- Keeps
the plans that were generated while running the tool in the explain
tables. The -p command parameter causes the workload
for -g to be saved in the ADVISE_WORKLOAD table
and saves the workload query plans that use the final recommendation
in the explain tables.
- -o outfile
- Saves the script to create the recommended objects in outfile.
- -nogen
- Indicates that generated columns are not to be included in multidimensional
clustering recommendations.
- -delim char
- Indicates the statement delimiter character char in
a workload file input. Default is ';'.
- -mdcpctinflation percent
- Specifies the maximum percentage that the table disk size can
increase in an MDC recommendation. For example, it indicates that
a table is allowed to increase to 1+percent/100
times its original size when it is converted to a MDC table. percent is
a floating point number with a default value of 10.
- -tables table-predicate-clause
- Indicates that only a subset of all existing tables should be
considered. The table-predicate-clause must be
a predicate that can be used in the WHERE clause of a query on SYSCAT.TABLES.
The tables considered by db2advis will be the intersection
of the tables from this query and the tables in the workload.
This
command parameter does not apply to recommendations about new MQTs.
- -noxml
- Indicates that the detailed XML output following the recommendation
text is not to be written to the console.
Examples
- In the following example, the utility connects to database PROTOTYPE,
and recommends indexes for table ADDRESSES without any constraints
on the solution:
db2advis -d prototype -s "select * from addresses a
where a.zip in ('93213', '98567', '93412')
and (company like 'IBM%' or company like '%otus')"
- In the following example, the utility connects to database PROTOTYPE,
and recommends indexes that will not exceed 53 MB for queries in table
ADVISE_WORKLOAD. The workload name is equal to "production". The
maximum allowable time for finding a solution is 20 minutes.
db2advis -d prototype -w production -l 53 -t 20
- In the following example, the input file db2advis.in contains
SQL statements and a specification of the frequency at which each
statement is to be executed:
--#SET FREQUENCY 100
SELECT COUNT(*) FROM EMPLOYEE;
SELECT * FROM EMPLOYEE WHERE LASTNAME='HAAS';
--#SET FREQUENCY 1
SELECT AVG(BONUS), AVG(SALARY) FROM EMPLOYEE
GROUP BY WORKDEPT ORDER BY WORKDEPT;
The utility
connects to database SAMPLE, and recommends indexes for each table
referenced by the queries in the input file. The maximum allowable
time for finding a solution is 5 minutes:
db2advis -d sample -i db2advis.in -t 5
- In the following example, MQTs are created in table space SPACE1
and the simulation table space is SPACE2. The qualifying name for
unqualified names in the workload is SCHEMA1, and the schema name
in which the new MQTs will be recommended is SCHEMA2. The workload
compression being used is HIGH and the disk space
is unlimited. Sample statistics are used for the MQTs. Issuing the
following command will recommend MQTs and, in a partitioned database
environment, indexes and partitioning will also be recommended.
db2advis -d prototype -w production -l -1 -m M -b space1 -c space2 -k
HIGH -q schema1 -n schema2 -r
To get the recommended
MQTs, as well as indexes, partitioning and MDCs on both MQT and base
tables, issue the command specifying a value of IMCP for
the -m option as follows: db2advis -d prototype -w production -l -1 -m IMCP -b space1 -c space2 -k
HIGH -q schema1 -n schema2 -r
- In the following example, the utility connects to database SAMPLE,
and recommends MDC for tables for EMPLOYEE and DEPT where MDC candidates
are allowed to grow by 30.5% of their original size.
db2advis -d sample -type C -disklimit 100 -i db2advis.in
-tables "TABNAME IN ('EMPLOYEE','DEPT')" -mdcpctinflation 30.5
Usage notes
When
it provides recommendations about indexes, MQTs, or MDC tables, the
Design Advisor ignores column-organized tables.
Because these features must be set up before you can run
the DDL CLP script, database partitioning, multidimensional clustering,
and clustered index recommendations are commented out of the DDL CLP
script that is returned. It is up to you to transform your tables
into the recommended DDL. One example of doing this is to use the
ALTER TABLE stored procedure but there are restrictions associated
with it in the same way the RENAME statement is restricted.
Starting
with Version 9.7, the Design Advisor will not recommend partitioned
indexes. All indexes will be recommended with the NOT PARTITIONED
clause. With this recommendation, it is your choice whether to use
PARTITIONED (the default) or NOT PARTITIONED to create indexes based
on their application scenarios and on the benefit that partitioned
index can provide.
For dynamic SQL statements, the frequency
with which statements are executed can be obtained from the monitor
as follows:
- Issue the command:
db2 reset monitor for database database-alias
Wait
for an appropriate interval of time.
- Issue the command:
db2advis -g other-options
If the -p parameter is used with
the -g parameter, the dynamic SQL statements
obtained will be placed in the ADVISE_WORKLOAD table with a generated
workload name that contains a timestamp.
The default frequency
for each SQL statement in a workload is 1, and the default importance
is also 1. The generate_unique() function assigns
a unique identifier to the statement, which can be updated by the
user to be a more meaningful description of that SQL statement.
Any db2advis error information
can also be found in the db2diag log file.
When
the advisor begins running, the ADVISE_INSTANCE table will contain
a row that identifies the advisor. The main advisor row is identified
by the START_TIME showing when the advisor began its run. This row's
STATUS is "STARTED".
If issuing the
db2advis command
results in an error saying
“Cannot insert into DB2ADVIS_INSTANCE”,
you will need to bind
db2advis.bnd and run the
db2advis command
with the
-l option. The bind operation can be
performed by issuing the command:
db2 bind db2advis.bnd blocking all grant public
When
the advisor is completed, you can check the associated row with the
appropriate START_TIME in the ADVISE_INSTANCE table. If STATUS is "COMPLETED",
the advisor executed successfully. If STATUS is still "STARTED" and
there is no db2advis process running, the advisor
has terminated prematurely. If STATUS has an "EX", you are also
shown an "SQLCODE" to determine how the advisor failed.
If
the -l disk-limit option is
not specified, you must have at least one of SYSADM, SYSCTRL, SYSMAINT,
or SYSMON authority to determine the maximum database size using the
GET_DBSIZE_INFO stored procedure.
Thetable-predicate-clause in
the -tables parameter is used to query SYSCAT.TABLES
and determine the tables that the advisor will consider. Only base
tables or existing MQTs can be considered, but aliases and logical
views can be used in the table-predicate-clause to
return the list of base table names or MQTs. For example, to specify
the subset of tables that have views that start with 'TV', specify -tables
"(tabname, tabschema) in (SELECT bname, bschema FROM SYSCAT.TABDEP
WHERE TABNAME LIKE 'TV%')".
As of
Version 9.7, the query optimizer measures the cost of the I/O savings
and the cost of decompressing key values and RIDs in the cost model.
As such, the Index advisor is capable of estimating the compressed
index size.