XML files that use the InfoSphere Optim™ Query Workload Tuner schema for query workloads that run on DB2 for z/OS

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.

&ltworkload desc="test workload"&gt; 	&ltsource name="DB01 2007/11/06 - Source0"&gt; 	 &ltsource_detail condition="QUALIFIER" operator="=" value="SYSADM" /&gt;  &lt;satement&gt; 	  &ltstatement_text qualifier="SYSIBM"&gt;SELECT * FROM 	   SYSTABLES WHERE NAME 	   = &aps;PART&aps; &lt;/tatement_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 'clerk&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 	  &lt;/statement_text&gt 	  &lt;statement_runtime stat_exec="5" stat_cpu="0.2" 	   stat_elap="0.4" /&gt 	 &lt;/statement&gt;  &lt;satement&gt;   &lt;satement_text&gt;SLECT 	   XML2CLOB(XMLELEMENT(NAME 	   &quotLITMLINEITEM&quot, 	   XMLAGG(XMLELEMENT(NAME 	   &quotbo:lineItem&quot, 	   XMLNAMESPACES(&aposurn:bo&apos; AS 	   &qut;bo&quot;), LINEITEM.L_ORDERKEY) 	   ORDER BY LINEITEM.L_ORDERKEY)) 	   ) AS &qut;xmlLITMLINEITEM" 	   FROM LINEITEM 	  </statement_text> 	 </statement> 	</source>
</workload>