Hello,

I have a question about the Visual Explain of the IBM Data Studio.

I'm taking my first steps with Db2 and the IBM Data Studio and wanted to generate an access plan diagram. Instead of that, Data Studio returned the following error message:

It states that the access plan diagram for SQL couldn't be generated. The error message further points to a profile which was not found. The stack trace is at the end of this post.

The Db2 system currently runs on V12. My mentor thought that an old Db2JCC driver may be the culprit, but with the new driver (this one is for V11.5 though), my login gets rejected.

Due to the mentioning of "profile" in the error message, another idea were the Db2 Tuning Services. Can this be the case? If so, is it necessary to install something on the host and add some kind of profile in order to use the Db2 Tuning Services?

I appreciate any input.

Best

Sabine

com.ibm.datatools.dsoe.explain.zos.exception.ExtractExplainDataException: com.ibm.datatools.dsoe.common.da.exception.OSCSQLException: java.sql.SQLException: Profil com.ibm.datatools.dsoe.common.da.sqljs4NoBind.ExplainerStaticSQLExecutorImplV10_SJProfile0 wurde nicht gefunden: java.io.StreamCorruptedException: invalid type code: 65
    at com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread.buildExplainModel(ExplainThread.java:2137)
    at com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread.commonProcess(ExplainThread.java:569)
    at com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread.process(ExplainThread.java:327)
    at com.ibm.datatools.dsoe.explain.zos.Explainer.process(Explainer.java:271)
    at com.ibm.datatools.dsoe.ape.core.ZOSAPEModelGenerator.generate(ZOSAPEModelGenerator.java:133)
    at com.ibm.datatools.dsoe.ape.core.APEModelGenerator.generate(APEModelGenerator.java:125)
    at com.ibm.datatools.visualexplain.common.viewer.popup.LaunchVisualExplainFromEditorAction$1.run(LaunchVisualExplainFromEditorAction.java:760)
    at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:119)
Caused by: com.ibm.datatools.dsoe.common.da.exception.OSCSQLException: java.sql.SQLException: Profil com.ibm.datatools.dsoe.common.da.sqljs4NoBind.ExplainerStaticSQLExecutorImplV10_SJProfile0 wurde nicht gefunden: java.io.StreamCorruptedException: invalid type code: 65
    at com.ibm.datatools.dsoe.common.da.sqljs4NoBind.ExplainerStaticSQLExecutorImplV10.executeQuery(ExplainerStaticSQLExecutorImplV10.java:2179)
    at com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread.accessCatalogTable(ExplainThread.java:8972)
    at com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread.executeSQLCatalogTable(ExplainThread.java:8873)
    at com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread.extractCatalogInfoFromDB(ExplainThread.java:6051)
    at com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread.extractCatalogInfo(ExplainThread.java:5588)
    at com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread.buildExplainModel(ExplainThread.java:2107)
    ... 7 more
Caused by: java.sql.SQLException: Profil com.ibm.datatools.dsoe.common.da.sqljs4NoBind.ExplainerStaticSQLExecutorImplV10_SJProfile0 wurde nicht gefunden: java.io.StreamCorruptedException: invalid type code: 65
    at sqlj.runtime.error.Errors.raiseError(Errors.java:125)
    at sqlj.runtime.error.Errors.raiseError(Errors.java:98)
    at sqlj.runtime.error.RuntimeRefErrors.raise_PROFILE_NOT_FOUND(RuntimeRefErrors.java:54)
    at sqlj.runtime.ref.ProfileGroup.getProfileKey(ProfileGroup.java:225)
    at com.ibm.datatools.dsoe.common.da.sqljs4NoBind.EPSQLJContext10.getProfileKey(ExplainerStaticSQLExecutorImplV10.java:99)
    at com.ibm.datatools.dsoe.common.da.sqljs4NoBind.ExplainerStaticSQLExecutorImplV10_SJProfileKeys.<init>(ExplainerStaticSQLExecutorImplV10.java:3612)
    at com.ibm.datatools.dsoe.common.da.sqljs4NoBind.ExplainerStaticSQLExecutorImplV10_SJProfileKeys.getKey(ExplainerStaticSQLExecutorImplV10.java:3603)
    at com.ibm.datatools.dsoe.common.da.sqljs4NoBind.ExplainerStaticSQLExecutorImplV10.executeQueryBody(ExplainerStaticSQLExecutorImplV10.java:2260)
    at com.ibm.datatools.dsoe.common.da.sqljs4NoBind.ExplainerStaticSQLExecutorImplV10.executeQuery(ExplainerStaticSQLExecutorImplV10.java:2153)
    ... 12 more

------------------------------
Sabine Diemt
Student / IT Consultant
FernUniversität in Hagen / European Mainframe Academy
------------------------------

Hello Sabine, 

IBM Data Studio is not recommended anymore for working with Db2 for z/OS database servers and support ends on March 31, 2025. However, technically, it should still work at least with Version 12 of Db2 for z/OS servers. Make sure you have the latest version and the latest APAR installed (launch IBM Installation Manager and generate a report of the installed packages - it should report "IBM Data Studio Client 4.1.4.3"). For getting more details on your error, activate a trace (check both options under "Options for trace files" in the "Collect Explain Data") and look for more details in the trace output. Potential reasons might be missing package binds, missing explain tables etc. 

The "profile" you mentioned has nothing to do with tuning services. This is a different story. When installed, SQL Tuning services allow tools such as Db2 Developer Extensions within VS Code, IBM Admin Foundation for Db2 or your own REST clients to invoke EXPLAIN services. Data Studio does its own invocation of EXPLAIN.

Even if Data Studio should work somehow with Db2 12, it is recommended to migrate to VS Code with Db2 Developer Extensions and/or IBM Admin Foundation. In both cases, SQL Tuning Services need to be available as well. All these offerings come with no license cost. 

Best regards 

Christoph

------------------------------
Christoph Theisen
Principal Solutions Advisor
Rocket Software
Cologne Germany
------------------------------

Make you are using the latest version of datastudio.  4.1.2 or higher.    

Also you may need to config DS for VE.  Start by clicking on a connection and then under access plans.  Here I would recommend rebuilding the plan tables as they need to be in the same format as DS.   This will link the to together.  

------------------------------
Sir Douglas Partch
------------------------------