DB2 Version 9.7 for Linux, UNIX, and Windows

DESIGN_ADVISOR - retrieve design advisor recommendations

The DESIGN_ADVISOR procedure retrieves design advisor recommendations from a DB2® server.

Syntax

Note: If your instance has databases created in Version 9.7 before Fix Pack 5, you must run the db2updv97 command to add this new procedure to the system catalog.


>>-DESIGN_ADVISOR--(--major_version--,--minor_version--,--requested_locale--,-->

>--xml_input--,--xml_filter--,--xml_output--,--xml_message--)--><

The schema is SYSPROC.

Procedure parameters

major_version: An input and output argument of type INTEGER that indicates the major document version. On input, this argument indicates the major document version that the caller supports for the XML documents passed as parameters in the procedure (see the parameter descriptions for xml_input, xml_output, and xml_message). The procedure processes all XML documents in the specified version, or returns an error (+20458) if the version is not valid. On output, this parameter specifies the highest major document version that is supported by the procedure. To determine the highest supported document version, specify NULL for this input parameter and all other required parameters.; If the xml_input parameter specifies a Document Type Major Version key and the major_version parameter is not NULL, then the major_version parameter takes precedence.; Supported versions: 1
minor_version: An input and output argument of type INTEGER that indicates the minor document version. On input, this argument specifies the minor document version that the caller supports for the XML documents passed as parameters for this procedure (see the parameter descriptions for xml_input, xml_output, and xml_message). The procedure processes all XML documents in the specified version, or returns an error if the version is not valid. On output, this parameter indicates the highest minor document version that is supported for the highest supported major version. To determine the highest supported document version, specify NULL for this input parameter and all other required parameters.; If the xml_input parameter specifies a Document Type Minor Version key and the minor_version parameter is not NULL, then the minor_version parameter takes precedence.; Supported versions: 0
requested_locale: An input argument of type VARCHAR(33) that specifies a locale. If the specified language is supported on the server, translated content is returned in the xml_output and xml_message parameters. Otherwise, content is returned in the default language. Only the language, and possibly the territory information, is used from the locale. The locale is not used to format numbers or influence the document encoding. For example, key names and values are not translated. The only translated portion of the XML output and XML message documents is the error message text. The caller should always compare the requested language to the language that is used in the XML output document (see the document locale entry in the XML output document).
Currently, the only supported value for requested_locale is en_US.
xml_input: An input argument of type BLOB(32M) that specifies a PLIST XML input string.
xml_filter: An input argument of type BLOB(4K). This parameter is reserved for future use.
xml_output: An output parameter of type BLOB(12K) that returns a PLIST XML output string.
xml_message: An output parameter of type BLOB(64K) that returns a complete XML output document of type Data Server Message, in UTF-8 encoding. This document provides detailed information about an SQL warning condition.

Authorization

Read access to the database.
Read and write access to the explain tables of the currently connected schema or the SYSTOOLS schema.
If materialized query tables (MQTs) are used, you must have CREATE TABLE authorization, and read and write access to the MQTs
EXECUTE privilege on the DESIGN_ADVISOR function.

Information returned

This information is always returned unless an error is generated.

Table 1. Information returned by the DESIGN_ADVISOR - retrieve design advisor recommendations table function
Column name	Data type	Description
SCHEMA	VARCHAR(128)	Schema name of the object or the proposed object to which this recommendation applies.
NAME	VARCHAR(128)	Name of the object or the proposed object to which this recommendation applies.
EXISTS	CHAR(1)	Indicates that the object exists.
RECOMMENDATION	VARCHAR(8)	Indicates the recommendation type. Valid values are: M for MQT P for database partitioning C for MDC I for index D if the object is not used by the given workload and can be considered for deletion. The result can be any combination of these values. For example "MC" indicates that the table is recommended as an MQT and an MDC table.
BENEFIT	DOUBLE	Estimated benefit, in timerons, of the proposed object or modification to the proposed object of the query. For base tables or MQTs that have MDC or partitioning recommendations, this value is NULL.
OVERHEAD	DOUBLE	Estimated cost, in timerons, to maintain either the proposed object or the modification to the proposed object. Indexes are ranked with the formula `BENEFIT - OVERHEAD`. MQTs are ranked with `BENEFIT - (0.5 * OVERHEAD)`. For base tables or MQTs that have MDC or partitioning recommendations, this value is NULL.
STATEMENT_NO	INTEGER	Statement number referred to by this recommendation. Reflects the statement number in the ADVISE_WORKLOAD table. When a recommendation applies to multiple statements, only one row is returned for each statement.
DISKUSE	DOUBLE	Estimated size, in MB, of either the recommended object or the result of modifications to the current object on disk.

Usage Notes

XML_INPUT options

Table 2. XML_INPUT options
Key name	Optional	Data type	Description
MAJOR_VERSION	Y	INTEGER	XML_OUTPUT schema major version supported by the client as input. If the procedure parameter of the same name is provided, it is used. Otherwise, this value is retrieved and required in XML_INPUT.
MINOR_VERSION	Y	INTEGER	XML_OUTPUT schema minor version supported by the client as input. If the procedure parameter of the same name is provided, it is used. Otherwise, this value is retrieved and required in XML_INPUT.
REQUESTED_LOCALE	Y	STRING	If the locale is supported at the server, the error messages are returned in the requested locale. If the locale is unsupported or invalid, the data is returned in the default locale of the server. If the procedure parameter of the same name is provided, it is used. Otherwise, this value is retrieved and required in XML_INPUT.
CMD_OPTIONS	N	STRING	List of arguments as accepted by the db2advis command. See the Usage Notes for a list of differences between the command-line parameters accepted by the db2advis command and this procedure.
USER_TEMP_TSPACE	Y	STRING	The name of a USER TEMPORARY TABLESPACE where a declared global temporary table (DGTT) can be created to hold the result set. If no name is provided, fenced process memory is used instead. The supplied table space must exist, be writeable by the caller, and have enough space for the entire result set. The DGTT exists and uses system resources until the application disconnects. Contents are deleted each time to save space and because the output is non-deterministic.

Note: Special characters like "&", "<", ">", "'" (single quotation mark), and """ (double quotation mark) can be specified by their corresponding HTML entities of "&", "<", ">", "'", and "quot;".

XML_OUTPUT description

The XML_OUTPUT document is always returned in a UTF-8 code page. Code page conversion is done for database identifiers, objects, and other possible non-UTF-8 characters. Special characters (see XML_INPUT options for a list) are translated as well.

Table 3. XML_OUTPUT description
Key name	Data type	Description
Document Type Name	STRING	Always returns the string "Data Server Message Output"
MAJOR_VERSION	INTEGER	Document version. Currently, the only return value is 1.
MINOR_VERSION	INTEGER	Document sub-version. Currently, the only return value is 0.
NUM_OUTPUT_ROWS	INTEGER	Number of rows returned in the result set.
ADVISE_START_TIME	STRING	Timestamp when the advisor began working. Equivalent to the ADVISE_INSTANCE.START_TIME column.
WORKLOAD_NAME	STRING	Name of the workload used by the advisor. Equivalent to the ADVISE_WORKLOAD.WORKLOAD_NAME column.
ADVISE_SCHEMA	STRING	Name of the explain/advisor table schema. This schema is used to read from and write to the ADVISE_WORKLOAD.ADVISE_INSTANCE and other explain/advisor tables.
TOTAL_DISK	STRING	Total initial disk space, in MB, needed if all recommended objects are to be created.
TOTAL_DISK_UPPER_BOUND	INTEGER	Upper bound limit for total disk space used when evaluating solution options, in MB.
ORIG_TOTAL_COST	STRING	Total cost, in timerons, without recommendations.
NEW_TOTAL_COST	STRING	Total cost, in timerons, with recommendations.
NUM_SOLUTIONS_EVAL	INTEGER	Number of solutions considered and evaluated by the advisor.

Difference between db2advis command-line parameters and DESIGN_ADVISOR

These options are not allowed because only the current database connection is being used by the procedure:

-[db|d]: The database name. The current database connection is used.
-[user|a]: The username to connect with (and optionally, the user password). In DESIGN_ADVISOR, this option is replaced by the SESSION_USER special register.
-[password|x]: This parameter indicates that the password is read from the standard input. It is not used in DESIGN_ADVISOR.

For file and directory locations, supply absolute path names whenever possible, to ensure a consistent behavior on different database server installations. Files and directories need to be readable (-file) or writeable (-script) by the fenced user ID on Linux/UNIX, or the DB2USERS group on Windows.

When the command-line options -file or -script are used, the statements are inserted into the ADVISE_WORKLOAD table for later reference with a unique workload name.

Clarification of the different schemas used by db2advis

Explain/advisor table schema name: The explain/advisor table schema name used by the DESIGN_ADVISOR procedure is defined by the CURRENT_USER special register. This special register defaults to the currently connected user. If the explain/advisor tables are not found through the user ID defined in the previous two options, then the SYSTOOLS schema is used.
Recommended objects schema name: The schema name for recommended objects is optionally defined using the -[schema|n] command-line option. If no name is provided, the value of the SESSION_USER special register is used by default.
Default workload schema name: The schema name for the default workload is optionally defined using the -q command-line option. If no name is provided, the value of the SESSION_USER special register is used by default.

Examples

Example 1: An example of an XML_INPUT:

<?xml version="1.0" encoding="UTF-8"?>
<plist version="1.0">
<dict>
<key>MAJOR_VERSION</key><integer>1</integer>
<key>MINOR_VERSION</key><integer>0</integer>
<key>REQUESTED_LOCALE</key><string>en_US</string>
<key>CMD_OPTIONS</key><string>-w "workload 1" -t 5</string>
<key>USER_TEMP_TSPACE</key><string>MY_TEMP_TS</string>
</dict>
</plist>

Example 2: An example of an XML_OUTPUT:

<?xml version="1.0" encoding="UTF-8"?>
<plist version="1.0">
<dict>
<key>Document Type Name</key><string>Data Server Message Output</string>
<key>MAJOR_VERSION</key><integer>1</integer>
<key>MINOR_VERSION</key><integer>0</integer>
<key>NUM_OUTPUT_ROWS</key><integer>1</integer>
<key>NUM_RESULT_SETS</key><integer>1</integer>
<key>ADVISE_START_TIME</key><string>2011-03-10-14.22.51.707742</string>
<key>WORKLOAD_NAME</key><string>MYWORKLOAD</string>
<key>ADVISE_SCHEMA</key><string>MYSCHEMA</string>
<key>TOTAL_DISK</key><string>0.0762</string>
<key>TOTAL_DISK_UPPER_BOUND</key><string>33.3203</string>
<key>ORIG_TOTAL_COST</key><string>28434.0000</string>
<key>NEW_TOTAL_COST</key><string>11108.0000</string>
<key>NUM_SOLUTIONS_EVAL</key><integer>31</integer>
</dict>
</plist>

Example 3: An example of an XML_MESSAGE:

<?xml version="1.0" encoding="UTF-8"?>
<plist version="1.0">
<dict>
<key>db2luw20458E</key><string>SQLCODE: 20458, SQLSTATE: 01H54 ,
<![CDATA[SQL20458W The procedure "SYSPROC.DESIGN_ADVISOR" has 
encountered an internal parameter processing error in parameter "4". 
The value for parameter "7" contains further information about 
the error.]]>(Extra message: <![CDATA[Error: Unsupported major 
version.]]>)</string>
</dict>
</plist>

Example 4: A full initialization and procedure call, with the resulting XML outputs.

Initializing the workload:

db2 "INSERT INTO ADVISE_WORKLOAD values('workload 1', 0, 
'SELECT COUNT(*) FROM EMPLOYEE','',100,0,0,0,0,'')"

db2 "INSERT INTO ADVISE_WORKLOAD values('workload 1', 1, 
'SELECT * FROM EMPLOYEE WHERE LASTNAME=''HAAS'' ','',100,0,0,0,0,'')"

db2 "INSERT INTO ADVISE_WORKLOAD values('workload 1', 2, 
'SELECT AVG(BONUS), AVG(SALARY) FROM EMPLOYEE GROUP BY WORKDEPT 
ORDER BY WORKDEPT','',1,0,0,0,0,'')"

Call the stored procedure:

db2 "CALL SYSPROC.DESIGN_ADVISOR(1,0, 'en_US', blob('
<?xml version=\"1.0\" encoding=\"UTF-8\"?>
<plist version=\"1.0\">
<dict>
<key>MAJOR_VERSION</key><integer>1</integer>
<key>MINOR_VERSION</key><integer>0</integer>
<key>REQUESTED_LOCALE</key><string>en_US</string>
<key>CMD_OPTIONS</key><string>-i &quot;
/home/dricard/prog/adv spaces! sp/cli/db2advis.in&quot; -t 5</string>
</dict>
</plist>') , NULL, ?, ?)"

The value of the output parameters:

Parameter Name  : MAJOR_VERSION
Parameter Value : 1

Parameter Name  : MINOR_VERSION
Parameter Value : 0

Parameter Name  : XML_OUTPUT
Parameter Value : x'
<plist version="1.0">
<dict>
<key>Document Type Name</key><string>Data Server Message Output</string>
<key>MAJOR_VERSION</key><integer>1</integer>
<key>MINOR_VERSION</key><integer>0</integer>
<key>NUM_OUTPUT_ROWS</key><integer>3</integer>
<key>NUM_RESULT_SETS</key><integer>1</integer>
<key>ADVISE_START_TIME</key><string>2011-03-10-14.22.51.707742</string>
<key>WORKLOAD_NAME</key><string>MYWORKLOAD</string>
<key>ADVISE_SCHEMA</key><string>MYSCHEMA</string>
<key>TOTAL_DISK</key><string>0.076</string>
<key>TOTAL_DISK_UPPER_BOUND</key><string>33.377</string>
<key>ORIG_TOTAL_COST</key><string>28434.0000</string>
<key>NEW_TOTAL_COST</key><string>11108.0000</string>
<key>NUM_SOLUTIONS_EVAL</key><string>31</string>
</dict>
</plist>'
Parameter Name  : XML_MESSAGE
Parameter Value : -

The values of the result set:

CREATOR                   NAME                      EXISTS RECOMMENDATION ...
------------------------- ------------------------- ------ -------------- ...
DRICARD                   XEMP2                     Y      I              ...
DRICARD                   IDX1103211528140          N      I              ...
DRICARD                   IDX1103211529540          N      I              ...

Output from the result set continued:

BENEFIT                  OVERHEAD                 STMTNO      DISKUSE               
------------------------ ------------------------ ----------- ----------------------
  +0.00000000000000E+000   +0.00000000000000E+000 0           +0.00000000000000E+000
  +1.72000000000000E+004   +0.00000000000000E+000 1           +0.06350000000000E+000
  +1.26000000000000E+002   +0.00000000000000E+000 2           +1.27190000000000E-002

  3 record(s) selected.