Visual Explain against a Z/OS database

Richard Gugel

Visual Explain against a Z/OS database
Is there a way from within Visual explain to qualify a plan table owned
by somone other than the primary id?


Background:
The plan table is owned by DBA's, programmers want to use it, but
they can't set the secondary because they are not in the DBA group Sql
code -553. The are plaing on using the explain with stored procedure
feature. I think we can work around this by using an alias to point to
the DBA's plan table from the programmers authid. Just seems like
there should be an easier way.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Patrick Bossman

Re: Visual Explain against a Z/OS database
(in response to Richard Gugel)
Hi Rich,
This isn't purely a VE issue, this is a "use of explain table" issue. The
only way to allow developers to dynamically "explain into" the DBA explain
tables without connecting them to the secondary authid is the ALIAS route.

Another solution is to create a set of explain tables for the application
developer group. Create their own set of tables under a schema for their
own (possibly already existing) secondary authid.

If the developers want to control the qualification of the tables, in V8
there's the option to SET CURRENT SCHEMA.

So...
Developer userid: DEV01
Connected to group: DB2DEV
Accessing tables with schema: DB2DBA

Use enable visual explain to create tables with creator DB2DEV.

Developer launches VE with SQLID DB2DBA.
On Tune SQL panel, the developer would set SCHEMA to DB2DBA.

This allows the developer to explain into DB2DEV explain tables while
qualifying the tables in the SQL with DB2DBA.

The primary purpose of explain with stored procedure is to allow developers
to explain SQL which they do not have the authority to execute. Meaning,
SELECT SALARY
FROM PAYROLL
WHERE NAME = 'your name here';

Explain with stored procedure lets the application developer EXPLAIN this
SQL while the developer does not have the authority to EXECUTE the SQL. The
explain with stored procedure will not allow the developer explain into a
set of explain tables they cannot set their SQLID to.

Regards,
Pat

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Richard Gugel

Re: Visual Explain against a Z/OS database
(in response to Patrick Bossman)
Thanks Pat

It's just to bad there isn't a place in the product that lets you
specify the plan table (and it's buddies) qualifier that you want to
work with.

I could not find where to set the schema on the tune SQL panel. I'm on
the August 2006 version of VE

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Patrick Bossman

Re: Visual Explain against a Z/OS database
(in response to Richard Gugel)
Hi Rich,
The SCHEMA should be there. It's on the TUNE SQL panel above the
categorized SQL on the right of QUERYNO and SQLID.
It was added in Feb. 2006. I want to add that you can only exploit the SET
CURRENT SCHEMA after you go to NFM.

v1.0.9 (2006/02/27):

* Tune SQL: Provide a field to enable SET CURRENT SCHEMA for V8 NF mode.

Regards,
Pat

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Suresh Sane

Re: Visual Explain against a Z/OS database
(in response to Patrick Bossman)
Patrick,

We have done exactly what you describe (a set of plan tables for a developer
group) and VE handles this well when you use the "explain with stored
procedure option" for an individual statement.

I do have concerns with the usability aspects, however.

When using this scheme, you cannot just explain the statement after it is
retrived from a packge. You need to use the sql option, and then add the
right qualifier (VE wipes it out and sets the sqlid to equal the package
qualifier). This has to happen for each statement. Attempting the normal
explain fails but the epxlain with stored procedure works.

So yes... a workaround exists but it is far from obvious. Maybe you could
do an IDUG session to cover these finer points? I love the product and wish
we could do more to promote it.

Thx
Suresh


>From: Patrick Bossman <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: Re: [DB2-L] Visual Explain against a Z/OS database
>Date: Wed, 29 Nov 2006 12:14:50 -0600
>
>Hi Rich,
>This isn't purely a VE issue, this is a "use of explain table" issue. The
>only way to allow developers to dynamically "explain into" the DBA explain
>tables without connecting them to the secondary authid is the ALIAS route.
>
>Another solution is to create a set of explain tables for the application
>developer group. Create their own set of tables under a schema for their
>own (possibly already existing) secondary authid.
>
>If the developers want to control the qualification of the tables, in V8
>there's the option to SET CURRENT SCHEMA.
>
>So...
>Developer userid: DEV01
>Connected to group: DB2DEV
>Accessing tables with schema: DB2DBA
>
>Use enable visual explain to create tables with creator DB2DEV.
>
>Developer launches VE with SQLID DB2DBA.
>On Tune SQL panel, the developer would set SCHEMA to DB2DBA.
>
>This allows the developer to explain into DB2DEV explain tables while
>qualifying the tables in the SQL with DB2DBA.
>
>The primary purpose of explain with stored procedure is to allow developers
>to explain SQL which they do not have the authority to execute. Meaning,
>SELECT SALARY
>FROM PAYROLL
>WHERE NAME = 'your name here';
>
>Explain with stored procedure lets the application developer EXPLAIN this
>SQL while the developer does not have the authority to EXECUTE the SQL.
>The
>explain with stored procedure will not allow the developer explain into a
>set of explain tables they cannot set their SQLID to.
>
>Regards,
>Pat
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences at
>http://conferences.idug.org/index.cfm

_________________________________________________________________
Get free, personalized commercial-free online radio with MSN Radio powered
by Pandora http://radio.msn.com/?icid=T002MSN03A07001

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm