set commands for XML format messages

You can regenerate diagnostics as an XML document. This makes it easier for front-end tools to interpret a document. You can use the native XPath query processor inside Adaptive Server to query this output if the XML option is enabled.

Either the query optimizer or the query execution layer can generate diagnostics output. To generate an XML document for the diagnostic output, use this set plan command:

set plan for
	{show_exec_xml, show_opt_xml, show_execio_xml, 
	show_lop_xml, show_managers_xml, show_log_props_xml, 
	show_parallel_xml, show_histograms_xml, show_final_plan_xml,
	show_abstract_plan_xml, show_search_engine_xml,
	show_counters_xml, show_best_plan_xml, show_pio_costing_xml, 
	show_lio_costing_xml, show_elimination_xml}
	to {client | message} on

To turn an option off, specify:

set plan for
	{show_exec_xml, show_opt_xml, show_execio_xml, show_lop_xml,
	show_managers_xml, show_log_props_xml, show_parallel_xml,
	show_histograms_xml,show_final_plan_xml
	show_abstract_plan_xml, show_search_engine_xml,
	show_counters_xml, show_best_plan_xml, show_pio_costing_xml,
	show_lio_costing_xml, show_elimination_xml} off

You need not specify the destination stream when turning the option off.

When message is specified, the client application must get the diagnostics from the buffer using a built-in function called showplan_in_xml(query_num).

query_num refers to the number of queries that are cached in the buffer. Currently, a maximum of 20 queries are cached in the buffer. The cache stops collecting query plans when it reaches 20 queries; it ignores the rest of the query plans. However, the message buffer continues to collect query plans. After 20 queries, you can display the message buffer only in its entirety by using a value of 0.

Valid values for query_num are 1 – 20, -1, and 0 (zero). A value of -1 refers to the last XML doc in the cache; a value of 0 refers to the entire message buffer.

The message buffer may overflow. If this occurs, there is no way to log all of the XML document, which may result in a partial and invalid XML document.

When the message buffer is accessed using showplan_in_xml, the buffer is emptied after execution.

You may want to use set textsize to set the maximum text size, as the XML document is printed as a text column and the document is truncated if the column is not large enough. For example, set the text size to 100000 bytes using:

set textsize 100000

When set plan is issued with off, all XML tracing is turned off if all of the trace options have been turned off. Otherwise, only specified options are turned off. Other options previously turned on are still valid and tracing continues on the specified destination stream. When you issue another set plan option, the previous options are joined with the current options, but the destination stream is switched unconditionally to a new one.