Adaptive Server lets you analyze dynamic parameters (which are indicated by question marks) before running a query, helping you avoid inefficient query plans.
Analyze dynamic parameters using:
@@lwpid global variable – returns the object ID of the most recently prepared lightweight procedure that corresponds to a dynamic SQL prepare statement.
@@plwpid global variable – returns the object ID of the next most recently prepared lightweight procedure that corresponds to a dynamic SQL prepare statement.
show_dynamic_params_in_xml – displays information about parameters in dynamic SQL statements. See the Reference Manual: Blocks.
Using the value provided by @@plwpid as the value for the show_dynamic_params_in_xml object_id parameter, Adaptive Server displays information about the dynamic parameters in the query. Continue refining the parameters until you find the ones that provide you with the best query plan.
Disable the statement cache before you analyze dynamic parameters for a query. The statement cache reuses query plans to avoid compilation.
The output of show_dynamic_params_in_xml is similar to:
<!ELEMENT query (parameter*)> <!ELEMENT parameter (number, type, column?)> <!ELEMENT number (#PCDATA)> <!ELEMENT type (#PCDATA)> <!ELEMENT column (#PCDATA)>
The root element of the document is <query>,
which can have zero or more <parameter> elements.
Each <parameter> element
includes:
number – the
dynamic parameter's position in the statement (starting at 1).
type – the
datatype.
column – the
name of a table and column (in the format table.column) associated
with the dynamic parameter, if such an association exists. If no association
exists, there is no column information in the output.
