Topic
  • No replies
de052494
de052494
9 Posts

Pinned topic Explain from statement cache fails (4.1.3 APAR3)

‏2018-12-03T13:43:24Z |

Hello,

I upgraded my DS installation to 4.1.3 APAR 3 and get internal errors when trying to explain (tune) a SQL statement from Db2 z/OS dynamic statement cache.

Data Studio trace shows the following:

[12324] [Thread-589] [12/3/18 2:31:39 PM CET] [Explainer] [Entry] [com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread] [buildExplainModel()] Starts to build the explain model.
[12324] [Thread-589] [12/3/18 2:31:39 PM CET] [Explainer] [Entry] [com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread] [extractExplainInfo()] Starts to extract the explain information from explain tables.
[12324] [Thread-589] [12/3/18 2:31:39 PM CET] [Explainer] [Trace] [com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread] [extractExplainInfo()] Table status=1, all table exist.
[12324] [Thread-589] [12/3/18 2:31:39 PM CET] [Explainer] [Entry] [com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread] [extractPlanTable(ExplainInfoImpl epInfo)] Starts to extract information from PLAN_TALE.
[12324] [Thread-589] [12/3/18 2:31:39 PM CET] [Explainer] [Trace] [com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread] [extractPlanTable(ExplainInfoImpl epInfo)] The SOURCE of the SQL object is: CACHE
[12324] [Thread-589] [12/3/18 2:31:39 PM CET] [Explainer] [Trace] [com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread] [extractPlanTable(ExplainInfoImpl epInfo)] The attribute STMT_ID of the SQL object is: 378
[12324] [Thread-589] [12/3/18 2:31:39 PM CET] [Explainer] [Trace] [com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread] [extractPlanTable(ExplainInfoImpl epInfo)] The attribute CACHED_TS of the SQL object is: 2018-11-29 15:07:48.233967
[12324] [Thread-589] [12/3/18 2:31:39 PM CET] [Explainer] [Trace] [com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread] [extractPlanTable(ExplainInfoImpl epInfo)] The SQL attribute CACHED_TS could not be found. Add the attribute, and run EXPLAIN again for the SQL statement.
[12324] [Thread-589] [12/3/18 2:31:39 PM CET] [Explainer] [Exception] [com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread] [extractPlanTable(ExplainInfoImpl epInfo)] java.lang.NullPointerException
    at com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread.extractPlanTable(Unknown Source)
    at com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread.extractExplainInfo(Unknown Source)
    at com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread.buildExplainModel(Unknown Source)
    at com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread.commonProcess(Unknown Source)
    at com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread.run(Unknown Source)

[12324] [Thread-589] [12/3/18 2:31:39 PM CET] [Explainer] [Exception] [com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread] [process(Connection connection, SQL sql, Properties parameters)] com.ibm.datatools.dsoe.explain.zos.exception.ExtractExplainDataException: java.lang.NullPointerException
    at com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread.extractPlanTable(Unknown Source)
    at com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread.extractExplainInfo(Unknown Source)
    at com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread.buildExplainModel(Unknown Source)
    at com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread.commonProcess(Unknown Source)
    at com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread.run(Unknown Source)
Caused by: java.lang.NullPointerException
    ... 5 more

[12324] [Thread-589] [12/3/18 2:31:39 PM CET] [Explainer] [Trace] [com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread] [process(Connection connection, SQL sql, Properties parameters)] Failed to process the SQL object.
[12324] [Thread-589] [12/3/18 2:31:39 PM CET] [Explainer] [Entry] [com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread] [releaseSQLExecutors()] Start to release the SQL executors.
[12324] [Thread-589] [12/3/18 2:31:39 PM CET] [Data Access] [Entry] [com.ibm.datatools.dsoe.common.da.DynamicSQLExecutorImpl] [close()] Starts to close the SQL executor.
[12324] [Thread-589] [12/3/18 2:31:39 PM CET] [Data Access] [Exit] [com.ibm.datatools.dsoe.common.da.DynamicSQLExecutorImpl] [close()] Succeeds to close the SQL executor.
[12324] [Thread-589] [12/3/18 2:31:39 PM CET] [Explainer] [Trace] [com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread] [releaseSQLExecutors()] conn.isClosed(): false
[12324] [Thread-589] [12/3/18 2:31:39 PM CET] [Explainer] [Exit] [com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread] [releaseSQLExecutors()] Succeeds to release the SQL executors.
[12324] [Thread-589] [12/3/18 2:31:39 PM CET] [Explainer] [Exit] [com.ibm.datatools.dsoe.explain.zos.impl.ExplainThread] [run()] Fails to process the SQL object by asynchronous method.


This happens with Db2 z/OS versions 11 and 12. Re-explaining the statement (instead of explaining from cache) works fine.

Anyone else facing this error ?

Kind regards

Christoph