Home » DbVisualizer Forums » DbVisualizer » DbVisualizer - Support

Thread: How do I access the Explain Plan functionality for DB2 8.2?


Permlink Replies: 16 - Pages: 2 [ 1 2 | Next ] - Last Post: Mar 28, 2012 11:33 PM Last Post By: Roger Bjarevall
mark lawford

Posts: 77
Registered: 08/23/05
How do I access the Explain Plan functionality for DB2 8.2?
Posted: Apr 11, 2006 2:51 PM
   thread.click_reply Reply
I'm using DBVis 5.0 with a license. I'm connecting to a DB2 8.2 database but I can't work out how to access the touted explain plan functionality.

While I can see the regular grid, text and chart tabs, I can't see the explain tab.

I don't get any explain options under the connection properties dialog either.

I'm connecting through a JDBC/ODBC beidge with the profile manually set to DB2.

What am I doing wrong?
Roger Bjarevall


Posts: 5,047
Registered: 12/17/04
Re: How do I access the Explain Plan functionality for DB2 8.2?
Posted: Apr 12, 2006 2:59 PM    global.in_response_to.tooltip in response to: mark lawford
   thread.click_reply Reply
Mark,

You need to use the IBM driver to enable the explain plan feature. The reason is not that this is unsupported via the Jdbc/Odbc driver but rather they way DbVis determine that DB2 is being used.

Regards

Roger
mark lawford

Posts: 77
Registered: 08/23/05
Re: How do I access the Explain Plan functionality for DB2 8.2?
Posted: Apr 13, 2006 11:18 AM    global.in_response_to.tooltip in response to: Roger Bjarevall
   thread.click_reply Reply
Thanks Roger. I'll set that up and report back.
Jack Marks

Posts: 1
Registered: 06/20/06
Re: How do I access the Explain Plan functionality for DB2 8.2?
Posted: Jun 20, 2006 9:22 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
   thread.click_reply Reply
Roger,

I am trying to setup the db2/udb explain plain functionality according to the documentation: http://www.dbvis.com/products/dbvis/doc/5.0/doc/ug/sqlCommander/sqlCommander.html#mozTocId979712.

I "go to Connection Properties->\[database]" in the driver setup, but do not see "Explain Plan".

Is this supported in the IBM type 4 driver? What am I missing? Thanks.

jack
Roger Bjarevall


Posts: 5,047
Registered: 12/17/04
Re: How do I access the Explain Plan functionality for DB2 8.2?
Posted: Jun 20, 2006 9:24 PM    global.in_response_to.tooltip in response to: Jack Marks
   thread.click_reply Reply
Jack,

You must be connected when switching to Connection Properties and the DB2 category.

What driver are you using?

Regards

Roger
mark lawford

Posts: 77
Registered: 08/23/05
Re: How do I access the Explain Plan functionality for DB2 8.2?
Posted: Jul 4, 2006 1:24 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
   thread.click_reply Reply
Sp, I've finally gotten around to needing this again and as I'm now set up with the DB2 driver I thought I'd give it another go.

This time I can see the Explain Plan options in the connection settings and I can see and press the Explain Plan button on the interface. Doing so just returns a Null Pointer Exception however.

I've attached an image showing various settings I'm using and the reported error.

The error occurs irrespective of the statement I want to explain over.

Is there any instability in the DBVis version I'm using? Do I need other packages installed? I'm logged on as a high-permissions user but is there anything else I need to be aware of?

Cheers,

Mark
Roger Bjarevall


Posts: 5,047
Registered: 12/17/04
Re: How do I access the Explain Plan functionality for DB2 8.2?
Posted: Jul 4, 2006 8:15 PM    global.in_response_to.tooltip in response to: mark lawford
   thread.click_reply Reply
Mark,

It would be great if you can enable debug and email the output.
More info in:

http://www.minq.se/products/dbvis/faq.html#6.11

Regards

Roger
support@minq.se
mark lawford

Posts: 77
Registered: 08/23/05
Re: How do I access the Explain Plan functionality for DB2 8.2?
Posted: Jul 5, 2006 10:34 AM    global.in_response_to.tooltip in response to: Roger Bjarevall
   thread.click_reply Reply
Hi Roger,

With the debug options set (and checking the Debug JDBC Drivers box) I get this output in the debug window.

DB2Statement closed in finalizer, last SQL: "null"

However, clearing the output and clicking the explain button again doesn't consistently write the message to the debug window, so I'm not sure whether that's directly related or not.
Roger Bjarevall


Posts: 5,047
Registered: 12/17/04
Re: How do I access the Explain Plan functionality for DB2 8.2?
Posted: Jul 6, 2006 12:13 AM    global.in_response_to.tooltip in response to: mark lawford
   thread.click_reply Reply
Mark,

I would like to see more of the debug output. It is possible to post more of it?

Regards

Roger
mark lawford

Posts: 77
Registered: 08/23/05
Re: How do I access the Explain Plan functionality for DB2 8.2?
Posted: Jul 6, 2006 10:03 AM    global.in_response_to.tooltip in response to: Roger Bjarevall
   thread.click_reply Reply
I've attached the debug trace gained from connecting to my target database and then running the explain plan over a simple query (SELECT * FROM schema.some_view).

Actually, whe running against either an actual view OR a mistyping the view name the same message is reported:

09:04:47 \[SELECT - 0 row(s), 0.000 secs] null (java.lang.NullPointerException )
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec \[0 successful, 0 warnings, 1 errors]

I'm sorry I can't give you more. I've re-run this a few times looking for different output but nothing...

It doesn't appear to be a problem with my SQL statement (right or wrong).

What prerequisites are there for me to be able to run the explain plans against db2?
Roger Bjarevall


Posts: 5,047
Registered: 12/17/04
Re: How do I access the Explain Plan functionality for DB2 8.2?
Posted: Jul 10, 2006 10:36 AM    global.in_response_to.tooltip in response to: mark lawford
   thread.click_reply Reply
Mark,

I recommend that you try with the universal JDBC driver for DB2. It is the latest driver from IBM and it doesn't require and DLLs or dynamic libraries in the path.

Here is the download instruction:

http://www.minq.se/products/dbvis/drivers.html#db2

Regards

Roger
mark lawford

Posts: 77
Registered: 08/23/05
Re: How do I access the Explain Plan functionality for DB2 8.2?
Posted: Jul 10, 2006 5:41 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
   thread.click_reply Reply
Great. That did it.

It is non-standard (as far as our restrictive environment goes) but as it is an official DB2 driver I think I can work with this.

This is going to make my life a lot easier.

Thanks again.

Mark
Roger Bjarevall


Posts: 5,047
Registered: 12/17/04
Re: How do I access the Explain Plan functionality for DB2 8.2?
Posted: Jul 10, 2006 5:44 PM    global.in_response_to.tooltip in response to: mark lawford
   thread.click_reply Reply
Perfect!

Thanks for letting me know.

Regards

Roger
Jay Damon

Posts: 2
Registered: 04/26/06
Re: How do I access the Explain Plan functionality for DB2 8.2?
Posted: Aug 22, 2006 11:15 PM    global.in_response_to.tooltip in response to: mark lawford
   thread.click_reply Reply
I have never been able to get the Explain functionality to work either. Attached is a document with error and debug information. I have the latest driver per this post. What must I do to make this work?
Roger Bjarevall


Posts: 5,047
Registered: 12/17/04
Re: How do I access the Explain Plan functionality for DB2 8.2?
Posted: Aug 30, 2006 10:12 AM    global.in_response_to.tooltip in response to: Jay Damon
   thread.click_reply Reply
Jay,

Can you please provide more information about your setup so that we have more to work with:

- What userid you use at login
  • If you have set the currentSchema (and its value) URL parameter or via Driver Properties in Connection Properties
  • Do you have the privilege to create tables (the explain tables) in your login schema?

Have you tried login as another user and then issue the Explain function?

Regards

Roger

Point your RSS reader here for a feed of the latest messages in all forums