DESIGN_ADVISOR - retrieve design advisor recommendations
The DESIGN_ADVISOR procedure retrieves design advisor recommendations from a database server.
Syntax
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. 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. 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.
Default PUBLIC privilege
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the procedure is automatically created.
Information returned
This information is always returned unless an error is generated.
| 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:
MCindicates 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
section 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&
,<
,>
,'
, andquot;
. - 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.
- 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 "
/home/dricard/prog/adv spaces! sp/cli/db2advis.in" -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 ...
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.