Authorization ID null when trying to EXPLAIN QUERY on DB2 LUW 9.5

ccstonge commented 2 years ago

System information:

Operating system (distribution) and version - Windows 10
DBeaver version - 21.3.2
Additional extensions - No

Connection specification:

Database name and version - DB2 LUW 9.5
Driver name -db2jcc4.jar version 4.19.66
Do you use tunnels or proxies (SSH, SOCKS, etc)? -No

Describe the problem you're observing: When clicking Explain Execution Plan for an SQL query, a dialog appears with the following message:

"Tables for explain not found within current authorization ID (null) nor in SYSTOOLS..."

EXPLAIN tables do exist in the current user schema. Not sure why authorization ID is null when credentials are required to connect to the database. If the authorization ID had a value of the username used for authentication then the explain query should work.

Include any warning/errors/backtraces from the logs

org.jkiss.dbeaver.model.exec.DBCException: EXPLAIN tables not found. Query cannot be explained. at org.jkiss.dbeaver.ext.db2.model.DB2DataSource.planQueryExecution(DB2DataSource.java:378) at org.jkiss.dbeaver.ui.editors.sql.plan.ExplainPlanViewer$ExplainPlanService.lambda$0(ExplainPlanViewer.java:408) at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:171) at org.jkiss.dbeaver.ui.editors.sql.plan.ExplainPlanViewer$ExplainPlanService.evaluate(ExplainPlanViewer.java:402) at org.jkiss.dbeaver.ui.editors.sql.plan.ExplainPlanViewer$ExplainPlanService.evaluate(ExplainPlanViewer.java:1) at org.jkiss.dbeaver.ui.LoadingJob.run(LoadingJob.java:88) at org.jkiss.dbeaver.ui.LoadingJob.run(LoadingJob.java:72) at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)

LonwoLonwo commented 2 years ago

Hello @ccstonge

Let's try to figure out what went wrong.

First, the execution plan mechanism tries to find schema with EXPLAIN tables with the query: "VALUES(SESSION_USER)".

Does this query work for you? If it works - what result does it return? If the query returns schema name - does this schema persist in your schema list?

ccstonge commented 2 years ago

Hi,

That query does return the proper schema name and that schema does exist and the EXPLAIN tables are in it.

LonwoLonwo commented 2 years ago

Ok. And what about verifying statement? Can you execute it?

CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN','V','','SCHEMANAME')

Use the result from VALUES(SESSION_USER) instead 'SCHEMANAME'.

This procedure returns error -438 if no EXPLAIN tables in the schema.

ccstonge commented 2 years ago

Running that from DBeaver results in:

[-438] Application raised error or warning with diagnostic text: "EXPLAIN INVALID".. SQLCODE=-438, SQLSTATE=3900

Running from control center gives:

SQL0438N Application raised error with diagnostic text: "EXPLAIN INVALID".
SQLSTATE=3900

SQL0438N Application raised error with diagnostic text: "EXPLAIN INVALID ".

Explanation:

This error or warning occurred as a result of execution of the
RAISE_ERROR function or the SIGNAL SQLSTATE statement in a trigger. An
SQLSTATE value that starts with '01' or '02' indicates a warning.

User response:

See application documentation.

sqlcode: -438, +438

sqlstate: application-defined

The tables exist. These are the tables listed in the schema that corresponds with the user:

EXPLAIN_ARGUMENT EXPLAIN_DIAGNOSTIC EXPLAIN_DIAGNOSTIC_DATA EXPLAIN_INSTANCE EXPLAIN_OBJECT EXPLAIN_OPERATOR EXPLAIN_PREDICATE EXPLAIN_STATEMENT EXPLAIN_STREAM

We can generate an Access Plan from Control Center but we do get the following error:

[IBM][CLI Driver][DB2/LINUXX8664] SQL0440N No authorized routine named "EXPLAIN_GET_MSGS" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884

dbeaver / dbeaver