DB2v7 z/OS: No Explains for Distributed SQL

Philip Sevetson

DB2v7 z/OS: No Explains for Distributed SQL
We have a plan bound in SSID 1 which uses ALIASes to run SQL against tables
in SSID 2. We BIND the plan with EXPLAIN(YES) but we don't get any
PLAN_TABLE lines in either the local or the remote subsystem. Anyone got
any idea what we need to do to get the EXPLAIN to work for the SQL that runs
against the remote tables?

--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
Wakefern Food Corporation
Edison, NJ, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.

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

Basivi Inaganti

Re: DB2v7 z/OS: No Explains for Distributed SQL
(in response to Philip Sevetson)
Hi Phil, From manual, EXPLAIN for remote binds: A remote requester that
accesses DB2 can specify EXPLAIN(YES) when binding a package at the DB2
server. The information appears in a plan table at the server, not at the
requester. If the requester does not support the propagation of the option
EXPLAIN(YES), rebind the package at the requester with that option to
obtain access path information. You cannot get information about access
paths for SQL statements that use private protocol.
-------------------
In case the DBPROTOCOL is not DRDA, please change and try to rebind. Let
us know how it goes.


Thanks,
Basivi.






Philip Sevetson <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/13/2005 10:36 AM
Please respond to DB2 Database Discussion list at IDUG

To: [login to unmask email]
cc:
Subject: [DB2-L] DB2v7 z/OS: No Explains for Distributed
SQL


We have a plan bound in SSID 1 which uses ALIASes to run SQL against
tables in SSID 2. We BIND the plan with EXPLAIN(YES) but we don't get any
PLAN_TABLE lines in either the local or the remote subsystem. Anyone got
any idea what we need to do to get the EXPLAIN to work for the SQL that
runs against the remote tables?

--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for
zOS
Wakefern Food Corporation
Edison, NJ, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.
---------------------------------------------------------------------------------
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

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

Philip Sevetson

Re: DB2v7 z/OS: No Explains for Distributed SQL
(in response to Basivi Inaganti)
Hrmp. I think our DBPROTOCOL is PRIVATE.

On 12/13/05, Basivi Inaganti <[login to unmask email]> wrote:
>
>
> Hi Phil, From manual, EXPLAIN for remote binds: A remote requester that
> accesses DB2 can specify EXPLAIN(YES) when binding a package at the DB2
> server. The information appears in a plan table at the server, not at the
> requester. If the requester does not support the propagation of the option
> EXPLAIN(YES), rebind the package at the requester with that option to obtain
> access path information. You cannot get information about access paths for
> SQL statements that use private protocol.
> -------------------
> In case the DBPROTOCOL is not DRDA, please change and try to rebind. Let
> us know how it goes.
>
>
> Thanks,
> Basivi.
>



--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
Wakefern Food Corporation
Edison, NJ, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.

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