SQLCODE -922 and DT161E errors during EXPLAIN or Show SQL Text in Detector for Db2

Products

• Detector for DB2 for z/OS

Issue/Introduction

When performing an EXPLAIN or "Show SQL Text" operation in Detector for Db2 for z/OS (PDT), the "DETECTOR Plan SQL Display" screen displays the error message:

DT161E: SQL call text locate failed; validation/read error. 

The Xmanager address space logs concurrently report the following SQLCODE -922 error:
PDT0220 STC DB2=#### PKGE=#### REQ=CAF-OPEN SQLCODE= -922

This issue prevents the retrieval of SQL text from the Db2 catalog, impacting the ability to analyze SQL statements through Detector.

Environment

Product: Detector for Db2 for z/OS
Version: All supported releases
Platform: z/OS
Component: Detector, Xmanager

Cause

The message PDT0220 with SQLCODE= -922 and REQ=CAF-OPEN indicates that the Detector for Db2 Xmanager address space lacks the necessary authorization to connect to the Db2 subsystem. Consequently, Xmanager fails to retrieve the SQL text from the Db2 catalog.

Resolution

To resolve this connection failure, update the authorization for the Xmanager Db2 Authorization ID:

1. Identify the Xmanager DB2 Authorization ID from the Xmanager startup output.
2. Review member PDTSQTGR in the product library hlq.CDBASQL.
3. Execute the required GRANT statements defined in the member for the Xmanager Db2 Authorization ID.
4. Recycle the Xmanager address space to apply the permission updates.

Refer to the Detector User Guide for additional details:
Xmanager Started Task Authorization