SQL/MX Guide to Stored Procedures in Java (G06.24+, H06.03+)

Performance and Troubleshooting
HP NonStop SQL/MX Guide to Stored Procedures in Java—523727-004
7-4
Displaying the Shape of a CALL Statement
Displaying the Shape of a CALL Statement
The SHOWSHAPE command displays the shape for a given DML statement, such as
a CALL statement, in an MXCI session. The result can be used at a later time to force
the SQL/MX compiler to choose a particular execution plan (or to force the same
execution plan for the statement).
CALL Statements Without a Shape
A CALL statement that does not contain a subquery as one of its SQL parameter
arguments does not have a shape. For example, this SHOWSHAPE command is for a
CALL statement without subquery parameters:
SHOWSHAPE CALL samdbcat.persnl.adjustsalary(202,5.5,?);
Because this CALL statement does not have a shape, the SHOWSHAPE command
returns this output:
CALL Statements With a Shape
If a CALL statement contains one or more subqueries as its SQL parameter
arguments, the SHOWSHAPE command generates a shape that describes the entire
SQL statement. For example, this SHOWSHAPE command is for a CALL statement
with a subquery parameter:
SHOWSHAPE CALL samdbcat.sales.totalprice((SELECT qty_ordered
FROM samdbcat.sales.odetail
WHERE ordernum = 100210
AND partnum = 5100),
'standard', ?param);
The generated shape describes all subquery trees and contains an ANYTHING token
to represent the CALL statement, as shown:
If the result of the SHOWSHAPE command is undesirable, you can use the CONTROL
QUERY SHAPE statement to force the execution plan. First generate the result of the
EXPLAIN function for a prepared CALL statement, and then modify the operator tree
for the execution plan by using CONTROL QUERY SHAPE.
For more information about the SHOWSHAPE command and the CONTROL QUERY
SHAPE statement, see the SQL/MX Reference Manual. For information about forcing
execution plans, see the SQL/MX Query Guide.
control query shape anything;
--- SQL operation complete.
control query shape nested_join(nested_join(sort_groupby(partition_access(
scan(path 'SAMDBCAT.SALES.ODETAIL', forward, blocks_per_access 1
, mdam off))),tuple),anything);
--- SQL operation complete.