Explain for DB2 in IBM Data Server Manager fails

Question

I am using the SQL Editor in IBM Data Server Manager to execute queries against DB2. The query in question works fine. However, when I click "Explain" to generate the access plan I get

"Access Plan Graph Cannot Be Generated"

and the diagnostic text is

The SQL statement failed. Explanation: The SQL statement resulted in an error with SQLCODE: -204 and SQLSTATE: 42704. User response: The DB2 documentation contains more information about the SQLCODE and how to resolve the error. "DB2ADMIN.TABLENAME" is an undefined name.. SQLCODE=-204, SQLSTATE=42704, DRIVER=3.66.46

The query is something like

select col1, col2 from tablename where pred=value

Why is it failing? What needs to be changed?

Answer 1

The error code -204 hints at the tablename not being know ("undefined name"). It seems that even though a query runs successfully without using a fully qualified name (schemaname.tablename), the explain functionality in IBM Data Server Manager needs the full name to work.

Changing the query to "select col1, col2 from schemaname.tablename where pred=value" caused the explain to succeed and it shows a nice graph of the access plan.