DSNAEXP stored procedure
The DB2® EXPLAIN stored
procedure, DSNAEXP, is a sample stored procedure that enables you to execute an EXPLAIN statement
for certain simple SQL statements without having the authorization to execute that SQL
statement.This stored procedure is
deprecated. Starting in DB2 10, use of the
EXPLAIN privilege or SQLADM authority is recommended instead.
- Very large SQL statements
- SQL statements that contain complex structures, such as embedded joins, sub-selects, numerous predicates, common table expressions, and unqualified object references.
- SQL statements other than the following basic types:
- SELECT
- INSERT
- DELETE
- UPDATE
- MERGE
In certain cases, you might need to provide explicit qualifiers for object references in SQL statements to enable the DSNAEXP stored procedure to explain the statements.
Environment
DSNAEXP must run in a WLM-established stored procedure address space.
Before you can invoke DSNAEXP, table sqlid.PLAN_TABLE must exist. sqlid is the value that you specify for the sqlid input parameter when you call DSNAEXP.
Job DSNTESC in DSN8A10.SDSNSAMP contains a sample CREATE TABLE statement for the PLAN_TABLE.
Authorization required
To execute the CALL SYSPROC.DSNAEXP statement, the owner of the package or plan that
contains the CALL statement must have one or more of the following privileges on each package that
the stored procedure uses:
- The EXECUTE privilege on the package for DSNAEXP
- Ownership of the package
- PACKADM authority for the package collection
- SYSADM authority
In addition:
- The SQL authorization ID of the process in which DSNAEXP is called must have the authority to execute SET CURRENT SQLID=sqlid.
- The SQL authorization ID of the process must also have one of the following characteristics:
- Be the owner of a plan table named PLAN_TABLE
- Have an alias on a plan table named owner.PLAN_TABLE and have SELECT and INSERT privileges on the table
DSNAEXP syntax diagram
The following syntax diagram shows the CALL statement for invoking DSNAEXP. Because the linkage convention for DSNAEXP is GENERAL, you cannot pass null values to the stored procedure.
>>-CALL--DSNAEXP--(--sqlid--,--queryno--,--sql-statement--,-----> >--parse--,--qualifier--,--sqlcode--,--sqlstate--,--------------> >--error-message--)--------------------------------------------><
DSNAEXP option descriptions
- sqlid
- Specifies:
- The authorization ID under which the EXPLAIN statement is to be executed
- The qualifier for the table into which EXPLAIN output is written: sqlid.PLAN_TABLE
- The implicit qualifier for unqualified objects in the SQL statement on which EXPLAIN is executed, if the value of parse is 'N'
sqlid is an input parameter of type CHAR(8).
- queryno
- Specifies a number that is to be used to identify sql-statement in the EXPLAIN output. This number appears in the QUERYNO column in the PLAN_TABLE. queryno is an input parameter of type INTEGER.
- sql-statement
- Specifies the SQL statement on which EXPLAIN is to be executed. sql-statement is an input parameter of type CLOB(2M).
- parse
- Specifies whether DSNAEXP adds a qualifier for unqualified table or view names in the input SQL
statement. Valid values are 'Y' and 'N'. If the value of parse is 'Y',
qualifier must contain a valid SQL qualifier name.
If sql-statement is insert-within-select and common table expressions, you need to disable the parsing functionality, and add the qualifier manually.
parse is an input parameter of type CHAR(1).
- qualifier
- Specifies the qualifier that DSNAEXP adds to unqualified table or view names in the input SQL
statement. If the value of parse is 'N',
qualifier is ignored.
If the statement on which EXPLAIN is run contains an INSERT within a SELECT or a common table expression, parse must be 'N', and table and view qualifiers must be explicitly specified.
qualifier is an input parameter of type CHAR(8).
- sqlcode
- Contains the SQLCODE from execution of the EXPLAIN statement. sqlcode is an output parameter of type INTEGER.
- sqlstate
- Contains the SQLSTATE from execution of the EXPLAIN statement. sqlstate is an output parameter of type CHAR(5).
- error-message
- Contains information about DSNAEXP execution. If the SQLCODE from execution of the EXPLAIN statement is not 0, error-message contains the error message for the SQLCODE. error-message is an output parameter of type VARCHAR(960).
Example of DSNAEXP invocation
The following C example shows how to call DSNAEXP to execute an EXPLAIN statement.
EXEC SQL BEGIN DECLARE SECTION;
char hvsqlid[9]; /* Qualifier for PLAN_TABLE */
long int hvqueryno; /* QUERYNO to give the SQL */
struct {
short int hvsql_stmt_len; /* Input SQL statement length */
hvsql_stmt_txt SQL type is CLOB 2M;
/* SQL statement text */
} hvsql_stmt; /* SQL statement to EXPLAIN */
char hvparse[1]; /* Qualify object names */
/* indicator */
char hvqualifier[9]; /* Qualifier for unqualified */
/* objects */
long int hvsqlcode; /* SQLCODE from CALL DSNAEXP */
char hvsqlstate[6]; /* SQLSTATE from CALL DSNAEXP */
struct {
short int hvmsg_len; /* Error message length */
char hvmsg_text[961]; /* Error message text */
} hvmsg; /* Error message from failed */
/* CALL DSNAEXP */
EXEC SQL END DECLARE SECTION;
short int i;
⋮
/* Set the input parameters */
strcpy(hvsqlid,"ADMF001 ");
hvqueryno=320;
strcpy(hvsql_stmt.hvsql_stmt_text,"SELECT CURRENT DATE FROM SYSDUMMY1");
hvsql_stmt.hvsql_stmt_len=strlen(hvsql_stmt.hvsql_stmt_text);
hvparse[0]='Y';
strcpy(hvqualifier,"SYSIBM");
/* Initialize the output parameters */
hvsqlcode=0;
for (i = 0; i < 5; i++) hvsqlstate[i] = '0';
hvsqlstate[5]='\0';
hvmsg.hvmsg_len=0;
for (i = 0; i < 960; i++) hvmsg.hvmsg_text[i] = ' ';
hvmsg.hvmsg_text[960] = '\0';
/* Call DSNAEXP to do EXPLAIN and put output in ADMF001.PLAN_TABLE */
EXEC SQL
CALL SYSPROC.DSNAEXP(:hvsqlid,
:hvqueryno,
:hvsql_stmt,
:hvparse,
:hvqualifier,
:hvsqlcode,
:hvsqlstate,
:hvmsg);
DSNAEXP output
If DSNAEXP executes successfully, sqlid.PLAN_TABLE contains the EXPLAIN output. A user with SELECT authority on sqlid.PLAN_TABLE can obtain the results of the EXPLAIN that was executed by DSNAEXP by executing this query:
SELECT * FROM sqlid.PLAN_TABLE WHERE QUERYNO='queryno';If DSNAEXP does not execute successfully, sqlcode, sqlstate, and error-message contain error information.
