Explain tables issue - 9.1.10 Pro

Hello, I'm a relatively new DbVisualizer user on 9.1.10 Pro, and am having difficulty generating an explain plan using the table auto-creation method with DB2 LUW. Under Connection Properties -> Explain Plan, I selected "Use User Defined Plan Table", entered the schema name for my instance owner, and checked "Create Plan Table if not Exists". The tables are being created, however I receive this error message when attempting to run an explain: 08:26:17 [SELECT - 0 row(s), 0.000 secs] [Error Code: -220, SQL State: 55002] The Explain table "DB2PIRC3.EXPLAIN_OBJECT", column "47" does not have the proper definition or is missing.. SQLCODE=-220, SQLSTATE=55002, DRIVER=4.15.82 I've attached the DDL for all explain tables, as generated by DbVisualizer. Noting the above error, EXPLAIN_OBJECT contains only 46 columns. Should I be doing something differently? Let me know if more information is needed. Debug output follows: 08:26:17 [DEBUG pool-3-thread-13 C.?] RootConnection: b.createStatement() 08:26:17 [DEBUG ExecutorRunner-pool-2-thread-9 AbstractFacade.isValid] Executing connection validation statement: select 1 from sysibm.sysdummy1 08:26:17 [DEBUG pool-3-thread-13 B.?] RootConnection: po.executeQuery("select 1 from sysibm.sysdummy1") 08:26:17 [DEBUG pool-3-thread-13 C.?] DefaultEditor49: b.createStatement() 08:26:17 [DEBUG ExecutorRunner-pool-2-thread-9 AbstractFacade.isValid] Executing connection validation statement: select 1 from sysibm.sysdummy1 08:26:17 [DEBUG pool-3-thread-13 B.?] DefaultEditor49: po.executeQuery("select 1 from sysibm.sysdummy1") 08:26:17 [DEBUG pool-3-thread-13 E.?] DefaultEditor49: e.getTables(null, "DB2PIRC3", "EXPLAIN_INSTANCE", null) 08:26:17 [DEBUG ExecutorRunner-pool-2-thread-9 J.execute] Executing... 08:26:17 [DEBUG ExecutorRunner-pool-2-thread-9 J.setCurrentCatalog] DbConnection='PIRC3' Catalog='null' Schema='null' NewCatalog='null' 08:26:17 [DEBUG ExecutorRunner-pool-2-thread-9 AbstractFacade.getColumn] executing "select current schema from sysibm.sysdummy1" 08:26:17 [DEBUG pool-3-thread-13 C.?] DefaultEditor49: b.createStatement() 08:26:17 [DEBUG pool-3-thread-13 B.?] DefaultEditor49: po.executeQuery("select current schema from sysibm.sysdummy1") 08:26:17 [DEBUG ExecutorRunner-pool-2-thread-9 J.setCurrentSchema] DbConnection='PIRC3' Catalog='null' Schema='DB2PIRC3' NewSchema='DB2PIRC3' 08:26:17 [DEBUG ExecutorRunner-pool-2-thread-9 AbstractFacade.getColumn] executing "select current schema from sysibm.sysdummy1" 08:26:17 [DEBUG pool-3-thread-13 C.?] DefaultEditor49: b.createStatement() 08:26:17 [DEBUG pool-3-thread-13 B.?] DefaultEditor49: po.executeQuery("select current schema from sysibm.sysdummy1") 08:26:17 [DEBUG pool-3-thread-13 C.?] DefaultEditor49: b.createStatement() 08:26:17 [DEBUG pool-3-thread-14 B.?] DefaultEditor49: po.execute("explain plan set querytag = 'dbvis-1412169977467' for SELECT * FROM PIR_OPS.PROC WHERE PROC_NM = 'SRF_VER_MSTR_000_Master'") 08:26:17 [DEBUG pool-3-thread-14 B.?] DefaultEditor49: EXCEPTION -> com.ibm.db2.jcc.am.SqlException: The Explain table "DB2PIRC3.EXPLAIN_OBJECT", column "47" does not have the proper definition or is missing.. SQLCODE=-220, SQLSTATE=55002, DRIVER=4.15.82 com.ibm.db2.jcc.am.SqlException: The Explain table "DB2PIRC3.EXPLAIN_OBJECT", column "47" does not have the proper definition or is missing.. SQLCODE=-220, SQLSTATE=55002, DRIVER=4.15.82 at com.ibm.db2.jcc.am.fd.a(fd.java:701) at com.ibm.db2.jcc.am.fd.a(fd.java:60) at com.ibm.db2.jcc.am.fd.a(fd.java:127) at com.ibm.db2.jcc.am.po.c(po.java:2761) at com.ibm.db2.jcc.am.po.d(po.java:2749) at com.ibm.db2.jcc.am.po.b(po.java:2116) at com.ibm.db2.jcc.t4.ab.i(ab.java:225) at com.ibm.db2.jcc.t4.ab.c(ab.java:48) at com.ibm.db2.jcc.t4.p.b(p.java:38) at com.ibm.db2.jcc.t4.qb.h(qb.java:114) at com.ibm.db2.jcc.am.po.hb(po.java:2111) at com.ibm.db2.jcc.am.po.a(po.java:3284) at com.ibm.db2.jcc.am.po.e(po.java:1091) at com.ibm.db2.jcc.am.po.execute(po.java:1070) at sun.reflect.GeneratedMethodAccessor74.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at com.onseven.dbvis.g.B.B.?(Z:104) at com.onseven.dbvis.g.B.F$A.call(Z:1474) at java.util.concurrent.FutureTask.run(Unknown Source) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) 08:26:17 [DEBUG ExecutorRunner-pool-2-thread-9 AbstractFacade.getColumn] executing "select current schema from sysibm.sysdummy1" 08:26:17 [DEBUG pool-3-thread-14 C.?] DefaultEditor49: b.createStatement() 08:26:17 [DEBUG pool-3-thread-13 B.?] DefaultEditor49: po.executeQuery("select current schema from sysibm.sysdummy1")

Roger, My apologies for not including my DB2 version. I am running: DB2/LINUXX8664 SQL09074 IBM Data Server Driver for JDBC and SQLJ 4.15.82 Edited by: haylo75 on Oct 3, 2014 4:52 PM

Roger, Sorry for the delay in replying. I believe I am indeed on version 9.7. Here is output from db2level: DB21085I Instance "{instance}" uses "64" bits and DB2 code release "SQL09074" with level identifier "08050107". Informational tokens are "DB2 v9.7.0.4", "s110330", "IP23243", and Fix Pack "4". Product is installed at "/opt/ibm/db2/V9.7".

Roger, I checked EXPLAIN.DDL in my /opt/ibm/db2/V9.7/misc path, and it indeed shows this column as the last for EXPLAIN_OBJECT: NUM_DATA_PARTS INTEGER NOT NULL, That got me curious though... I also found a DB2 10.5 installation on the same server at /opt/ibm/db2/V10.5. That being said, all the symlinks under the instance owner's sqllib directory point to the 9.7 installation. Perhaps I'm missing an extra configuration item considering there are two versions of DB2 on the server. Thanks, haylo Edited by: haylo75 on Oct 9, 2014 7:56 PM Edited by: haylo75 on Oct 9, 2014 7:56 PM

Roger, I found the source of my issue. We are running a mixture of DB2 9.7 and 10.5 instances on this server. Explain tables for 10.5 were somehow created in a 9.7 instance. I dropped and recreated them straight from the 9.7 EXPLAIN.DDL, and now everything is working as expected. Sorry for the bother!