db2advis - Db2 Design Advisor command
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
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:This option cannot be specified with the -g, -s, or -w options.
The frequency can be updated any number of times in the file.--#SET FREQUENCY x
- -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.
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 M -b space1 -c space2 -k HIGH -q schema1 -n schema2 -r
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.
- Issue the command:
Wait for an appropriate interval of time.db2 reset monitor for database database-alias
- 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
.
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.