Parameter substitution to allow Explain

Database
Teradata Employee

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

3 REPLIES
Teradata Employee

Re: Parameter substitution to allow Explain

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...

Teradata Employee

Re: Parameter substitution to allow Explain

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.

Teradata Employee

Re: Parameter substitution to allow Explain

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.