DB2 10.5 for Linux, UNIX, and Windows

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


>>-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.