jdbc error from Execute statement(s) as explain plan in DB2 v9.7

Environment dbvis 8.0.10 (x64 with the jvm as downloaded) on Win7 x64 Database: db2 v9.7.5 is remote on Linux (sles10 on x64) configured dbvis to use the db2 jdbc driver version 3.62.56 Can't get the "Execute statements as explain plan" working in dbvis, for statements that otherwise run without errors. When I use the SQL-Commander button 'Execute the statement(s) as explain plan' even for simple queries it always returns a jdbc error. For example: 'select count(*) from schema.table' will return something like: [Error Code: -104, SQL State: 42601] An unexpected token "-" was found following "LUMN_COUNT FROM (((v". Expected tokens may include: "JOIN".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.62.56. 2) [Error Code: -727, SQL State: 56098] An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-104", SQLSTATE "42601" and message tokens "-|LUMN_COUNT FROM (((v|JOIN".. SQLCODE=-727, SQLSTATE=56098, DRIVER=3.62.56 The odd thing is that the text in the message does not seem to relate to the SQL statment being explained. In particular, the tokens reported in the error message are not part of the SQL statement being explained. For example, the query used to generate the message above was 'select count(*) from schema.table' (and the query runs fine, and gives a result-set of 1 row, when not explained and just executed as is, and the target table really is a table and not a view , although it is a partitioned (distribute by hash) table. Another strange thing is that on the same workstation, i can use IBM data Studio 2.2.1 to get the explain plan of the same statement, connecting to the same database with the same credentials. However I prefer the explain GUI in dbvis, and it has worked fine for me with other db2 databases in the past. Just can't see what's wrong in this specific case. I have the DBVis debug (dbvis and jdbc driver trace) in case it helps (attached )

hi Roger, I am able to re-create the symptom with the dbvis 8.0.10 driver. I attach the trace, of the dbvis only (without jdbc). I believe the problem may be connected with the specific setup at this site. My user-account does not have implicit-schema rights on the database, and from the dbvis trace I can see that dbvis is attempting to create the explain tables in my schema (whereas in reality when I perform EXPLAIN (or any other user performs explain) then the database is configured to write the explain data to one specific shared-schema called SYSTOOLS.) When I use IBM-data-studio, and use the explain feature, it writes the explain data to the systools schema and retrieves the plan from there. When any user uses explain, the results go into the systools schema, even when each user connects to the database with his or her own credentials. Is it possible to configure db-vis to use a specific schema for the explain tables (i.e. a schema that is different from the current authid ?). Note: for the example query in the attached file , it is a simple "select count(*) from schema.table with ur", and it returned 09:34:54 [SELECT - 0 row(s), 0.000 secs] 1) [Error Code: -104, SQL State: 42601] An unexpected token "-" was found following "LUMN_COUNT FROM (((v". Expected tokens may include: "JOIN".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.13.127. 2) [Error Code: -727, SQL State: 56098] An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-104", SQLSTATE "42601" and message tokens "-|LUMN_COUNT FROM (((v|JOIN".. SQLCODE=-727, SQLSTATE=56098, DRIVER=4.13.127

In Connection Properties --> Properties --> Explain Plan I can see "Explain plan table" and under that it says "Use these properties to control the explain table for Oracle" however, my connection is to DB2 not Oracle. The docs also explain that the explain plan options are different per rdbms type. The screen that I see for 'Explain Plan' (for a connection to DB2 LUW) in the preferences show no item for "use default plan table" or "use user defined plan table" . Maybe that is the problem? The only options that appear on the screen for a db2 connection are "Create Plan Table if not Exists" (which is ticked) and "Drop explain plan table". If I untick (do not select) the "Create Plan table if not exists" and then attempt to show the explain plan in the GUI, the result is an error message " Explain tables does not exist. Check the explain plan properties for this database connection."

Thank you Roger - do you mean 8.0.11 ? Can you give an approximate month when that might happen, or even the quarter (e.g. 1Q2013) The only reason for asking is that it will determine wider use of the tool at this site , so I might choose a difference advocacy approach if I know that information :-)

The feature of user-specified explain-table schema for DB2 now works correctly, thanks to v8.0.11