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?
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.