How can we help you today?

Start a new topic
Discussions DbVisualizer Forums Support

Explain Plan Tables Not Created in DB2 v9.5

[This topic is migrated from our old forums. The original author name has been removed]
[Attachment has been removed.]

Hi, We're on DbVis 8.0.11 running against DB2 v9.5. When trying to run an explain plan an error occurs indicating that the EXPLAIN_STATEMENT table does not exist. I have the properties for the connection set to: Use User Defined Plan Table Schema Name: DB2ADMIN (so that I know I have permissions to create the table) Create Plan Table if not Exists: checked Drop Explain Plan Table: checked The first time that I set the properties and executed a plan, it ran fine. From there on out I get the error message. [Error Code: -219, SQL State: 42704] The required Explain table "DB2ADMIN.EXPLAIN_STATEMENT" does not exist.. SQLCODE=-219, SQLSTATE=42704, DRIVER=4.13.127 Debug output attached. Thanks for your help.
6 CommentsSorted by Oldest First
[This reply is migrated from our old forums.]

Re: Explain Plan Tables Not Created in DB2 v9.5
Hi, What is your login schema? If not DB2ADMIN, what happen if you connect as DB2ADMIN and run the explain? Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Explain Plan Tables Not Created in DB2 v9.5
Hi Roger, I 'm connecting as DB2ADMIN, so I should have all the permissions needed. I"m perplexed as to why it worked the first time and not afterward. Thanks.
[This reply is migrated from our old forums.]

Re: Explain Plan Tables Not Created in DB2 v9.5
Thanks. It seems as if not all required tables exist in the schema. Verify this by opening the DB2ADMIN->Tables node in the databases tab. These are the tables required by explain: EXPLAIN_ARGUMENT EXPLAIN_INSTANCE EXPLAIN_OBJECT EXPLAIN_OPERATOR EXPLAIN_PREDICATE EXPLAIN_STATEMENT EXPLAIN_STREAM If not all of these are present. Drop all and then try again. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]
[Attachment has been removed.]

Re: Explain Plan Tables Not Created in DB2 v9.5
Roger, Only one table was present in the DB2ADMIN schema, EXPLAIN_INSTANCE. I dropped it and ran the explain plan again. It recreated the EXPLAIN_INSTANCE table, but none of the others. I've attached the debug file. The problem seems to have something to do with the following, but I don't know what it means. EXCEPTION -> com.ibm.db2.jcc.am.SqlException: The table "lob options prohibited with blocknonlogged enabled" is in an invalid state for the operation. Reason code="".. SQLCODE=-20054, SQLSTATE=55019, DRIVER=4.13.127 Thanks for your help.
[This reply is migrated from our old forums.]
[Attachment has been removed.]

Re: Explain Plan Tables Not Created in DB2 v9.5
Jeff, It seems that the NOT LOGGED option used for some columns in the explain plan tables are not supported by your DB2 configuration. I assume there is an option turned off resulting in this issue. I have attached a script that creates the explain tables with no NOT LOGGED options. First make sure the "Use User Defined Schema" is set to "DB2ADMIN" and that both the Create Explain Tables and Drop Explain Tables are unchecked. Then remove all EXPLAIN_xxx tables located in the DB2ADMIN schema. Now load the create_exp_table.sql in the SQL Commander and run it. It will create the needed explain tables. And last, give explain another try. Let me know the result. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Explain Plan Tables Not Created in DB2 v9.5
Roger, Thanks for the help and the table scripts. It generates the explain plan fine now. This is a production instance and may explain why the logging is set the way it is. We don't often need to run explain plans in production, but occasionally this is the only environment where we have data that is needed to test a query. Thanks again, Jeff
Comments to this discussion are now closed!
More topics in Support
See all 1297 topics