Come fare una Explain senza avere le autorizzazioni sulle tabelle

Massimiliano Castellini

Come fare una Explain senza avere le autorizzazioni sulle tabelle
Alcuni clienti mi avevano posto il problema di poter esguire una Explain in
ambiente di produzione senza dover avere le autorizzazioni relative alle
tabelle coinvolte.
Questa problematica è stata risolta con la seguente PTF: UQ92322 DB2 V7 o
UQ92323 DB2 V8.
Vi allego una breve spiegazione.

****************************************************************
* USERS AFFECTED: All users of EXPLAIN in DB2 UDB for OS/390 *
* and z/OS Version 7 and DB2 UDB for z/OS *
* Version 8 are affected by this change. *
****************************************************************
* PROBLEM DESCRIPTION: DB2 application programmers need a way *
* to run an EXPLAIN of an SQL statement *
* without needing the authority to run *
* the SQL. *
****************************************************************
* RECOMMENDATION: *
****************************************************************
Application programmers need to be able to make sure that SQL
will perform correctly, but in many cases they should not have
the authority to read the actual data the SQL will be selecting
or updating eg. sensitive payroll data. OWNER(SYSADM)
DYNAMICRULES(BIND) allows a user to EXPLAIN SQL statements
without having the privilege to execute those SQL statements.
However, the drawback of this setting is that the owner becomes
the default qualifier for unqualified tables/views inside the
SQL.
.
This PTF adds a new sample stored procedure called DSN8EXP that
can be used to work around these constraints. DSN8EXP accepts
an SQL statement of up to 32700 single-byte characters, passed
as an input parameter from its caller. It first sets the
current SQLID to locate EXPLAIN tables, then it issues the
EXPLAIN statement for the input SQL. You can optionally let the
stored procedure parse the SQL and add the qualifier ahead of
those unqualified tables/views if there is any unqualified
tables/views in the SQL. For insert-within-select and common
table expressions, you need to disable the parsing function-
ality, and add the qualifier manually. DSN8EXP returns the
SQLCODE, SQLSTATE, and diagnostic text from the EXPLAIN by
output parameter.
.
A client side tool, Visual Explain for DB2 for z/OS Version 8,
provides an easy-to-use interface to call the DSN8EXP stored
procedure. Visual Explain Version 8 can be downloaded via the
IBM website at:
http://www.ibm.com/software/data/db2/zos/osc/ve/index.html.
.
****************************************************************
*** Deploying the DSN8EXP stored procedure on DB2 for z/OS
****************************************************************
This PTF also provides a new sample job called DSNTEJXP that
you can use to prepare and bind the DSN8EXP stored procedure.
DSNTEJXP is not customized by the DB2 installation CLIST. See
the prolog of DSNTEJXP for guidance on customizing it to run
at your site.
.
****************************************************************
*** Running the DSN8EXP stored procedure from Visual Explain V8
****************************************************************
In the Subsystem Properties Panel, which is invoked from the
menu Properties => Subsystem Settings, there are fields to
specify the stored procedure's schema name and procedure name.
When all necessary fields are filled in the TuneSQL panel there
is a field called"Table qualifier for explain stored procedure".
When this field has a non-blank value, it is used as the input
parameter QUALIFIER for the stored procedure and the parsing
functionality will be enabled; if this field is blank,
then the parsing is disabled. There is also a button called
"Explain with stored procedure" shown in the TuneSQL panel,
which actually invokes the explain stored procedure.
The original Explain button still issues normal explain
statements.

---------------------------------------------------------------------------------
Benvenuti alla lista DB2 User Group Italia.Per annullare l ' iscrizione collegarsi a : http://www.idugdb2-l.org/archives/DB2-UG-Italy.html. Selezionare "Join or Leave the list". Per consultare le IDUG Listserv FAQ collegarsi a :http://www.idugdb2-l.org. L'amministratore della lista DB2 User Group - Italy può essere raggiunto al seguente indirizzo: [login to unmask email] Per sapere le ultime novità sulle conferenze IDUG potete consultare : http://conferences.idug.org/index.cfm