All Forums Database
TGooch44 8 posts Joined 09/10
13 Mar 2013
Parameter substitution to allow Explain

Is there anyway to get what parameter a given query is using, so that it's possible to explain the query?  Contrived example of what I'm talking about(syntax may not be 100% correct):
CREATE PROCEDURE whatever(
IN someDate DATE,
IN numDays INT,
)
BEGIN
DECLARE currDate DATE;
DECLARE loopCnt INT;
SET currDate=someDate;
SET loopCnt=0;
WHILE loopCnt < numDays
DO
 INSERT INTO this_table
SELECT col1, col2, sum(col3)
FROM that_table
WHERE part_col = someDate + loopCnt
GROUP By 1,2;
SET loopCnt = loopCnt + 1;
END WHILE;
END;
Now, the above will have something like this in Viewpoint/QryLog/PMPC API calls as the QueryText:
USING
 
INSERT INTO this_table
SELECT col1, col2, sum(col3)
FROM that_table
WHERE part_col = someDate + loopCnt;
SET loopCnt = loopCnt + 1;
_spVV8 (DATE),
_spVV9 (INT) INSERT INTO this_table
SELECT col1, col2, sum(col3)
FROM that_table
WHERE partCol = :_spVV8 + :_spVV9;
Is the value for :spVV8 and :spVV9 stored anywhere that I can query while the query is inflight? Alternatively, did I miss or is there an undocumented API for getting the full-explain text or XML explain for inflight queries?  The closest seems to be MonitorSQLSteps, but it only has partial step text.  But, it must be available somewhere since Viewpoint(and previously TD Manager) have it in their Query Explain Display.  
Thanks in advance

TGooch44 8 posts Joined 09/10
13 Mar 2013

Hmm the above got kind of mangled with the formatting the second part should read like this:
USING
 

_spVV8 (DATE),

_spVV9 (INT) INSERT INTO this_table

SELECT col1, col2, sum(col3)

FROM that_table

WHERE partCol = :_spVV8 + :_spVV9;

 
For some reason the INSERT part got put in there twice...
 

tomnolan 594 posts Joined 01/08
13 Mar 2013

A caution -- in case you are asking about in-flight JDBC/ODBC/etc. parameterized queries that you want to explain using a separate session and/or a separate tool like BTEQ.
If you simply substitute query parameter values back into the SQL request text as hardcoded literals you may get an explain that is quite different from the plan used with the parameter values.
Also, the database may execute the SQL request differently depending on driver-specific connection parameters, such as whether LOB support is enabled, which session character set is used, etc.

TGooch44 8 posts Joined 09/10
13 Mar 2013

Thanks for the heads up.  Yes, being able to access the actual explain for a given session would be best.  e.g MonitorExplain(HostId, SessionNo, runVprocNo) to match with the montiorsession, monitorSQLtext, etc.
In many cases though, close is good enough.  I can cross-reference other data(e.g. DBQL Usage/XML explain from previous runs of the same job) to corroborate any conclusions.

You must sign in to leave a comment.