Help with explain plan.......

Answered

I see I can execute explain plan from the context menu but when I do nothing happens and nothing shows in the event log window...

I am running against DB2 on an iSeries using the JTOpen driver...

Any help is greatly appreciated....

Thanks

12 comments
Comment actions Permalink

Hello Stephen,

The results should be visible in the Database Console window:

Is it empty?

Please provide/post output from idea.log after the connect and trying to execute plan and content of the Output tab in Database Console tool window (if it is available).

0
Comment actions Permalink

There is no tab called plan......

 

 

2016-04-20 08:56:23,056 [ 6833] INFO - #com.intellij.idea.Main - ------------------------------------------------------ IDE STARTED ------------------------------------------------------
2016-04-20 08:56:23,071 [ 6848] INFO - #com.intellij.idea.Main - IDE: DataGrip (build #DB-143.2263.2, 18 Feb 2016 00:00)
2016-04-20 08:56:23,071 [ 6848] INFO - #com.intellij.idea.Main - OS: Windows 7 (6.1, x86)
2016-04-20 08:56:23,071 [ 6848] INFO - #com.intellij.idea.Main - JRE: 1.8.0_60-b27 (Oracle Corporation)
2016-04-20 08:56:23,071 [ 6848] INFO - #com.intellij.idea.Main - JVM: 25.60-b23 (Java HotSpot(TM) Server VM)
2016-04-20 08:56:23,071 [ 6848] INFO - #com.intellij.idea.Main - JVM Args: -Xms128m -Xmx512m -XX:MaxPermSize=250m -XX:ReservedCodeCacheSize=240m -XX:+UseConcMarkSweepGC -XX:SoftRefLRUPolicyMSPerMB=50 -ea -Dsun.io.useCanonCaches=false -Djava.net.preferIPv4Stack=true -XX:+HeapDumpOnOutOfMemoryError -XX:-OmitStackTraceInFastThrow -Djb.vmOptionsFile=C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\bin\datagrip.exe.vmoptions -Xbootclasspath/a:C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\boot.jar -Didea.platform.prefix=DataGrip -Didea.no.jre.check=true -Didea.paths.selector=DataGrip10
2016-04-20 08:56:23,071 [ 6848] INFO - #com.intellij.idea.Main - ext: C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\jre\jre\lib\ext: [access-bridge-32.jar, cldrdata.jar, dnsns.jar, jaccess.jar, jfxrt.jar, localedata.jar, meta-index, nashorn.jar, sunec.jar, sunjce_provider.jar, sunmscapi.jar, sunpkcs11.jar, zipfs.jar]
2016-04-20 08:56:23,461 [ 7238] INFO - #com.intellij.idea.Main - JNA library loaded (32-bit) in 390 ms
2016-04-20 08:56:23,461 [ 7238] INFO - penapi.util.io.win32.IdeaWin32 - Native filesystem for Windows is operational
2016-04-20 08:56:23,477 [ 7254] INFO - #com.intellij.idea.Main - Using "FocusKiller" library to prevent focus stealing.
2016-04-20 08:56:30,044 [ 13821] INFO - llij.ide.plugins.PluginManager - 1 plugins initialized in 514 ms
2016-04-20 08:56:30,044 [ 13821] INFO - llij.ide.plugins.PluginManager - Loaded bundled plugins: IDEA CORE (143.SNAPSHOT)
2016-04-20 08:56:30,216 [ 13993] INFO - ellij.vfs.persistent.FSRecords - Marking VFS as corrupted: 'C:\Users\shauck\.DataGrip10\system\caches\names.dat' does not exist
2016-04-20 08:56:30,216 [ 13993] INFO - ellij.util.io.PagedFileStorage - lower=100; upper=200; buffer=10; max=498008064
2016-04-20 08:56:30,247 [ 14024] INFO - pl.local.NativeFileWatcherImpl - Starting file watcher: C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\bin\fsnotifier.exe
2016-04-20 08:56:30,278 [ 14055] INFO - pl.local.NativeFileWatcherImpl - Native file watcher is operational.
2016-04-20 08:56:30,700 [ 14477] INFO - .history.utils.LocalHistoryLog - local history version mismatch (was: 0, expected: 6), rebuilding...
2016-04-20 08:56:30,700 [ 14477] INFO - .history.utils.LocalHistoryLog - FS has been rebuild, rebuilding local history...
2016-04-20 08:56:30,934 [ 14711] INFO - rains.ide.BuiltInServerManager - built-in server started, port 63342
2016-04-20 08:56:32,182 [ 15959] INFO - tellij.xml.Html5SchemaProvider - HTML5_SCHEMA_LOCATION = C:/Program Files (x86)/JetBrains/DataGrip 1.0.3/lib/datagrip.jar!/resources/html5-schema/html5.rnc
2016-04-20 08:56:32,182 [ 15959] INFO - tellij.xml.Html5SchemaProvider - XHTML5_SCHEMA_LOCATION = C:/Program Files (x86)/JetBrains/DataGrip 1.0.3/lib/datagrip.jar!/resources/html5-schema/xhtml5.rnc
2016-04-20 08:56:32,182 [ 15959] INFO - tellij.xml.Html5SchemaProvider - CHARS_DTD_LOCATION = C:/Program Files (x86)/JetBrains/DataGrip 1.0.3/lib/datagrip.jar!/resources/html5-schema/html5chars.ent
2016-04-20 08:56:32,400 [ 16177] INFO - ellij.project.impl.ProjectImpl - 15 project components initialized in 218 ms
2016-04-20 08:56:32,400 [ 16177] INFO - le.impl.ModuleManagerComponent - 0 module(s) loaded in 0 ms
2016-04-20 08:56:32,416 [ 16193] INFO - plication.impl.ApplicationImpl - 73 application components initialized in 2886 ms
2016-04-20 08:56:37,471 [ 21248] INFO - .intellij.idea.IdeaApplication - App initialization took 22169 ms
2016-04-20 08:56:37,877 [ 21654] INFO - ellij.project.impl.ProjectImpl - 90 project components initialized in 234 ms
2016-04-20 08:56:37,877 [ 21654] INFO - le.impl.ModuleManagerComponent - 0 module(s) loaded in 0 ms
2016-04-20 08:56:37,892 [ 21669] INFO - i.vcs.changes.ChangeListWorker - default list not found when copy
2016-04-20 08:56:37,892 [ 21669] INFO - i.vcs.changes.ChangeListWorker - default list not found when copy in original object too
2016-04-20 08:56:38,001 [ 21778] INFO - ellij.project.impl.ProjectImpl - 90 project components initialized in 15 ms
2016-04-20 08:56:38,001 [ 21778] INFO - le.impl.ModuleManagerComponent - 0 module(s) loaded in 0 ms
2016-04-20 08:56:38,391 [ 22168] INFO - .diagnostic.PerformanceWatcher - Pushing properties took 0ms; general responsiveness: ok; EDT responsiveness: ok
2016-04-20 08:56:38,407 [ 22184] INFO - tor.impl.FileEditorManagerImpl - Project opening took 447 ms
2016-04-20 08:56:38,485 [ 22262] INFO - .diagnostic.PerformanceWatcher - Indexable file iteration took 94ms; general responsiveness: ok; EDT responsiveness: ok
2016-04-20 08:56:38,485 [ 22262] INFO - indexing.UnindexedFilesUpdater - Unindexed files update started: 215 files to update
2016-04-20 08:56:39,140 [ 22917] INFO - .diagnostic.PerformanceWatcher - Unindexed files update took 655ms; general responsiveness: ok; EDT responsiveness: ok
2016-04-20 09:01:25,753 [ 309530] INFO - ellij.project.impl.ProjectImpl - 90 project components initialized in 31 ms
2016-04-20 09:01:25,753 [ 309530] INFO - le.impl.ModuleManagerComponent - 0 module(s) loaded in 0 ms
2016-04-20 09:01:25,753 [ 309530] INFO - i.vcs.changes.ChangeListWorker - default list not found when copy
2016-04-20 09:01:25,753 [ 309530] INFO - i.vcs.changes.ChangeListWorker - default list not found when copy in original object too
2016-04-20 09:01:27,368 [ 311145] INFO - ellij.project.impl.ProjectImpl - 90 project components initialized in 32 ms
2016-04-20 09:01:27,368 [ 311145] INFO - le.impl.ModuleManagerComponent - 0 module(s) loaded in 0 ms
2016-04-20 09:01:27,446 [ 311223] INFO - .diagnostic.PerformanceWatcher - Pushing properties took 0ms; general responsiveness: ok; EDT responsiveness: ok
2016-04-20 09:01:27,461 [ 311238] INFO - tor.impl.FileEditorManagerImpl - Project opening took 122 ms
2016-04-20 09:01:27,461 [ 311238] INFO - .diagnostic.PerformanceWatcher - Indexable file iteration took 15ms; general responsiveness: ok; EDT responsiveness: ok
2016-04-20 09:04:13,651 [ 477428] INFO - ution.rmi.RemoteProcessSupport - "C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\jre\jre\bin\java" -Djava.net.preferIPv4Stack=true -Dfile.encoding=UTF-8 -classpath "C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\util.jar;C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\trove4j.jar;C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\groovy-all-2.3.9.jar;C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\jdbc-console.jar;C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\dekaf-single-2.0.0.229.jar;C:\Users\shauck\.DataGrip10\config\jdbc-drivers\jtopen-driver-license.txt;C:\Users\shauck\.DataGrip10\config\jdbc-drivers\jt400.jar" com.intellij.database.remote.RemoteJdbcServer com.ibm.as400.access.AS400JDBCDriver
2016-04-20 09:04:14,130 [ 477907] INFO - ution.rmi.RemoteProcessSupport - Port/ID: 32133/RemoteDriverImpl1b20e174
2016-04-20 09:04:14,200 [ 477977] INFO - ase.dataSource.LocalDataSource - Connecting to: jdbc:as400://192.168.25.4:446
2016-04-20 09:04:14,440 [ 478217] INFO - ution.rmi.RemoteProcessSupport - Terminating: 32133/RemoteDriverImpl1b20e174
2016-04-20 09:04:14,516 [ 478293] INFO - ution.rmi.RemoteProcessSupport - Process finished with exit code -1
2016-04-20 09:04:16,918 [ 480695] INFO - ution.rmi.RemoteProcessSupport - "C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\jre\jre\bin\java" -Djava.net.preferIPv4Stack=true -Dfile.encoding=UTF-8 -classpath "C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\util.jar;C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\trove4j.jar;C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\groovy-all-2.3.9.jar;C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\jdbc-console.jar;C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\dekaf-single-2.0.0.229.jar;C:\Users\shauck\.DataGrip10\config\jdbc-drivers\jtopen-driver-license.txt;C:\Users\shauck\.DataGrip10\config\jdbc-drivers\jt400.jar" com.intellij.database.remote.RemoteJdbcServer com.ibm.as400.access.AS400JDBCDriver
2016-04-20 09:04:17,411 [ 481188] INFO - ution.rmi.RemoteProcessSupport - Port/ID: 49698/RemoteDriverImpl6bb800b2
2016-04-20 09:04:17,462 [ 481239] INFO - ase.dataSource.LocalDataSource - Connecting to: jdbc:as400://192.168.25.4:446
2016-04-20 09:04:22,997 [ 486774] INFO - ution.rmi.RemoteProcessSupport - Terminating: 49698/RemoteDriverImpl6bb800b2
2016-04-20 09:04:23,017 [ 486794] INFO - ution.rmi.RemoteProcessSupport - Process finished with exit code -1
2016-04-20 09:06:35,897 [ 619674] INFO - ution.rmi.RemoteProcessSupport - "C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\jre\jre\bin\java" -Djava.net.preferIPv4Stack=true -Dfile.encoding=UTF-8 -classpath "C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\util.jar;C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\trove4j.jar;C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\groovy-all-2.3.9.jar;C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\jdbc-console.jar;C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\dekaf-single-2.0.0.229.jar;C:\Users\shauck\.DataGrip10\config\jdbc-drivers\jtopen-driver-license.txt;C:\Users\shauck\.DataGrip10\config\jdbc-drivers\jt400.jar" com.intellij.database.remote.RemoteJdbcServer com.ibm.as400.access.AS400JDBCDriver
2016-04-20 09:06:36,365 [ 620142] INFO - ution.rmi.RemoteProcessSupport - Port/ID: 62496/RemoteDriverImpl24d1332
2016-04-20 09:06:36,413 [ 620190] INFO - ase.dataSource.LocalDataSource - Connecting to: jdbc:as400://192.168.25.4:446
2016-04-20 09:07:06,417 [ 650194] WARN - om.intellij.util.ProfilingUtil - Profiling agent is not enabled. Add -agentlib:yjpagent to idea.vmoptions if necessary to profile IDEA.
2016-04-20 09:20:32,209 [1455986] INFO - ase.dataSource.LocalDataSource - Connecting to: jdbc:as400://192.168.25.4:446
2016-04-20 09:47:15,506 [3059283] WARN - ConfigurableExtensionPointUtil - ignore deprecated groupId: editor for id: editor.preferences.import
2016-04-20 10:02:01,564 [3945341] WARN - ConfigurableExtensionPointUtil - ignore deprecated groupId: editor for id: editor.preferences.import
2016-04-20 10:02:17,550 [3961327] INFO - ution.rmi.RemoteProcessSupport - "C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\jre\jre\bin\java" -Djava.net.preferIPv4Stack=true -Dfile.encoding=UTF-8 -classpath "C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\util.jar;C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\trove4j.jar;C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\groovy-all-2.3.9.jar;C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\jdbc-console.jar;C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\dekaf-single-2.0.0.229.jar;C:\Users\shauck\.DataGrip10\config\jdbc-drivers\jtopen-driver-license.txt;C:\Users\shauck\.DataGrip10\config\jdbc-drivers\jt400.jar" com.intellij.database.remote.RemoteJdbcServer com.ibm.as400.access.AS400JDBCDriver
2016-04-20 10:02:18,071 [3961848] INFO - ution.rmi.RemoteProcessSupport - Port/ID: 27883/RemoteDriverImplc4e8720b
2016-04-20 10:02:18,126 [3961903] INFO - ase.dataSource.LocalDataSource - Connecting to: jdbc:as400://192.168.25.4:446
2016-04-20 10:02:18,767 [3962544] INFO - ution.rmi.RemoteProcessSupport - Terminating: 27883/RemoteDriverImplc4e8720b
2016-04-20 10:02:18,786 [3962563] INFO - ution.rmi.RemoteProcessSupport - Process finished with exit code -1
2016-04-20 10:02:19,648 [3963425] INFO - ution.rmi.RemoteProcessSupport - "C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\jre\jre\bin\java" -Djava.net.preferIPv4Stack=true -Dfile.encoding=UTF-8 -classpath "C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\util.jar;C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\trove4j.jar;C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\groovy-all-2.3.9.jar;C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\jdbc-console.jar;C:\Program Files (x86)\JetBrains\DataGrip 1.0.3\lib\dekaf-single-2.0.0.229.jar;C:\Users\shauck\.DataGrip10\config\jdbc-drivers\jtopen-driver-license.txt;C:\Users\shauck\.DataGrip10\config\jdbc-drivers\jt400.jar" com.intellij.database.remote.RemoteJdbcServer com.ibm.as400.access.AS400JDBCDriver
2016-04-20 10:02:20,128 [3963905] INFO - ution.rmi.RemoteProcessSupport - Port/ID: 54894/RemoteDriverImpl1c682fbb
2016-04-20 10:02:20,187 [3963964] INFO - ution.rmi.RemoteProcessSupport - Terminating: 54894/RemoteDriverImpl1c682fbb
2016-04-20 10:02:20,200 [3963977] INFO - ution.rmi.RemoteProcessSupport - Process finished with exit code -1
2016-04-20 10:07:33,669 [4277446] INFO - openapi.editor.impl.EditorImpl - Cache hits:4096, total requests:64289,file://C:/Users/shauck/.DataGrip10/config/consoles/db/73f23fac-4c13-4b5f-a87f-b2f1694bf408/StationInfo.sql

0
Comment actions Permalink

Do you see Database tool window? What content does it have? Can you select from the table?

0
Comment actions Permalink

Please also try to select Explain Plan (Raw) action. Do you see same behavior?

1
Comment actions Permalink

Explan plan (Raw) does the smae thing .. nothing....

The SQL runs fine ........

In the database tool window I see tables and schemas I have included in my filter.....

0
Comment actions Permalink

Please attach output from Test Connection for the data source. Thanks.

0
Comment actions Permalink

You mean when I go into data sources and hit the test connection button like below ?

 

Database: DB2 UDB for AS/400/07.01.0000 V7R1m0

Driver name: AS/400 Toolbox for Java JDBC Driver
Driver version: 10.3
JDBC version: 4.0
Case sensitivity: UPPER (quoted: EXACT)

Connection successful

 

 

 

0
Comment actions Permalink

Thank you for the information. Looks like it is not supported for this database. Created the https://youtrack.jetbrains.com/issue/DBE-2597 request.

0
Comment actions Permalink

I found this post while evaluating DataGrip for my complicated SQL query needs. This is so completely awful that there is no way I will consider DataGrip for at least 1 major version or 1 year, whichever comes later.

I literally created a forum account just to express how completely and utterly awful this is. Imagine putting your car in reverse and nothing happening. Why? Because your car is in Oregon, that's why. That's what your coders have created.

0
Comment actions Permalink

Ted, I did buy a copy of DataGrip because the only tools we have at work are DataStudio and System i navigator and they're just not that great either.

While I agree with you that there are issues with DataGrip and how it works and such I have to say that I use it more like an add on to supplement DataStudio and System i navigator since neither of them provide what I need.

The issue with explain for me is this... if you use IBM Navigator you can perform explain but not using DataGrip....whatever...

So in summary.. I have to agree with some of your assessment but in order to get things done and keep my job and deal with the whole DB2 for I variant I have to use ...

  • System i navigator
  • DataStudio
  • DataGrip

It works with SQLite for my Xamarin development as well ...

If you want the best solution I suggest you just get Toad for DB2 IF they support the iSeries variant and you can afford it!

0
Comment actions Permalink

Andrey Dernov, thank you so match sir, it's verey useful, regards.

0
Comment actions Permalink

Was able to right click on sql and use Explain Plan. Not obvious, but successful.

0

Please sign in to leave a comment.