Advises users on what indexes to create for one or more SQL statements. 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 recommended indexes for each table, the statistics derived for them, as well as the DDL by which each can be created, are written to a user-created table, ADVISE_INDEX.
Authorization
Read access to the database. Read and write access to the explain tables.
Required Connection
None. This command establishes a database connection.
Command Syntax
>>-db2advis----d--database-name----+--------------------+------->
+--w--workload-name--+
+--s--"statement"----+
'--f--filename-------'
>-----+---------------------------+---+-----------------+------->
'--a--userid--+----------+--' '--l--disk-limit--'
'-/passwd--'
>-----+----------------------+---+-----+-----------------------><
'--t--max-advise-time--' '--h--'
Command Parameters
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.
Examples
In the following example, the utility connects to the PROTOTYPE database, and recommends indexes for the ADDRESSES table 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 the PROTOTYPE database, and recommends indexes that will not exceed 53MB for queries in the ADVISE_WORKLOAD table whose 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 utility connects to the TEST database, and recommends at most five indexes of up to three columns each for each table referenced by any of the queries in the file myqueries.sql. No limits are placed on run time, or on the amount of disk space consumed.
db2advis -d test -f myqueries.sql -c 3 -n 5
In the final example, an input file called 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) GROUP BY WORKDEPT ORDER BY WORKDEPT;
The utility connects to the SAMPLE database, 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 -f db2advis.in -t 5
Usage Notes
For dynamic SQL statements, the frequency with which statements are executed can be obtained from the monitor as follows:
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.