The DESIGN_ADVISOR procedure retrieves design advisor recommendations from a IBM® DB2® 10.1 server.
>>-DESIGN_ADVISOR--(--major_version--,--minor_version--,--requested_locale--,--> >--xml_input--,--xml_filter--,--xml_output--,--xml_message--)--><
The schema is SYSPROC.
Currently, the only supported value for requested_locale is en_US.
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the procedure is automatically created.
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:
|
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. |
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. |
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. |
<?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>
<?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>
<?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.
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,'')"
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, ?, ?)"
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 : -
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.