Start a new topic

Explain tables issue - 9.1.10 Pro

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

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

[This reply is migrated from our old forums.]

Re: Explain tables issue - 9.1.10 Pro
Hi, Thanks for the update and that it now works. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Explain tables issue - 9.1.10 Pro
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!
[This reply is migrated from our old forums. The original author name has been removed]

Re: Explain tables issue - 9.1.10 Pro
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
[This reply is migrated from our old forums.]

Re: Explain tables issue - 9.1.10 Pro
Hi, That is strange. If you look in the explain.ddl script provided with 9.7 which is the last column in the EXPLAIN_OBJECT table? It should be NUM_DATA_PARTS INTEGER NOT NULL. However, I suspect it is NULLKEYS CHAR(1 OCTETS). What you can try if the latter is to remove all EXPLAIN tables and run the explain.ddl script. Then make sure DbVisualizer don't drop the tables in the Connection Properties after running explain. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Explain tables issue - 9.1.10 Pro
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".
[This reply is migrated from our old forums.]

Re: Explain tables issue - 9.1.10 Pro
Hi, Thanks for the info. There is an incompatibility with explain in DbVisualizer with DB2 starting at version 10.5 which will be fixed in an upcoming maintenance version. Based on that you are using 9.7.4 the error message doesn't make sense based on the official documentation from IBM as column 47 was introduced in 10.5. Is it really 9.7.4 you are using and not 10.5? Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Explain tables issue - 9.1.10 Pro
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
[This reply is migrated from our old forums.]

Re: Explain tables issue - 9.1.10 Pro
Hi, Sorry for the delay. What DB2 LUW are you using? You find this in the Connection message box while connected in the Connection tab. Right-click and then paste in a reply. Regards Roger