XML files that use the InfoSphere Optim Query Workload Tuner schema

You can create an XML file to create a custom workload that you can import or capture SQL statements from.

The specification for the XML file that defines a workload includes the following elements:

workload
The workload element is the root element, meaning that a valid file has only one workload element.
source
The source element is the child of workload. A workload can have more than one source element. The name attribute contains a unique name for each source element. If no source element is included, the workload is considered to have a text source.
source_detail
The source_detail element is a child of the source element.
statement
The statement element is a child of the source element. Each statement element represents one SQL statement in the workload. Each source element can contain more than one statement element.
statement_text
The statement_text element, which contains the text of the SQL statement, is a child of the statement element. A statement element cannot contain more than one statement_text element. The qualifier attribute specifies the qualifier of the statement. If the qualifier attribute is not specified, the qualifier attribute specified in the source_detail element is used. If that attribute is unspecified too, the current user ID will be used as the statement qualifier.

Certain symbols that are commonly part of the text of a SQL statement are invalid characters in XML. You must replace them with the corresponding entities shown in the table below.

Table 1. XML entities for common symbols in SQL statements
Symbol to replace Entity reference to use
< &lt;
> &gt;
' &apos;
" &quot
& &amp;
statement_runtime
The statement_runtime element is a child of the statement element. A statement element cannot contain more than one statement_runtime element. A statement_runtime element can include any of the following attributes:
stat_exec
The number of times that the statement has been executed.
stat_cpu
The accumulated CPU time, in seconds.
stat_elap
The accumulated elapsed time, in seconds.

The following example XML file creates a workload that contains three SQL statements.

<workload desc="test workload">
<source name="DB01 2007/11/06 - Source0">
<source_detail condition="QUALIFIER" operator="=" value="SYSADM"/> 
<statement>
<statement_text qualifier="SYSIBM">SELECT * FROM SYSTABLES WHERE NAME 
 = &apos;PART&apos;
</statement_text>
<statement_runtime stat_exec="2" stat_cpu="0.1" stat_elap="0.2"/>
</statement>
<statement>
<statement_text>SELECT
SUM(PS_SUPPLYCOST) * AVG(P_SIZE * PS_AVAILQTY / 2) AS SupplyRatio_Expr_Operators,
O_ORDERSTATUS || O_ORDERPRIORITY AS Key_Expr_CONCAT
FROM PART, ORDER, PARTSUPP
WHERE O_TOTALPRICE BETWEEN (2 * (SELECT MIN(P_RETAILPRICE) FROM PART))
                     AND (0.5 * (SELECT MAX(P_RETAILPRICE) FROM PART))
AND O_SHIPPRIORITY IN (1, 2, 3)
AND O_CLERK LIKE &apos;clerk&amp;888%&apos;
AND O_SHIPPRIORITY - 1 &gt;= 3
AND O_SHIPPRIORITY - 1 &gt;= 1
AND P_PARTKEY = PS_PARTKEY
GROUP BY PS_SUPPLYCOST, P_SIZE, PS_AVAILQTY, O_ORDERSTATUS, O_ORDERPRIORITY
ORDER BY PS_SUPPLYCOST, P_SIZE, PS_AVAILQTY, O_ORDERSTATUS, O_ORDERPRIORITY
</statement_text>
<statement_runtime stat_exec="5" stat_cpu="0.2" stat_elap="0.4"/>
</statement>
<statement>
<statement_text>SELECT
XML2CLOB(XMLELEMENT(NAME &quot;LITMLINEITEM&quot;,
         XMLAGG(XMLELEMENT(NAME &quot;bo:lineItem&quot;,
         XMLNAMESPACES(&apos;urn:bo&apos; AS &quot;bo&quot;), LINEITEM.L_ORDERKEY)
         ORDER BY LINEITEM.L_ORDERKEY)) ) AS &quot;xmlLITMLINEITEM&quot;
FROM LINEITEM
</statement_text>
</statement>
</source>
</workload>

Feedback