DB2 11.5.9 : SQL to find applications holding a share lock (internal plan lock) on a specific package

Question

I'm running DB2 v11.5.9 running on zLinux SLES 12SP5.

I'm trying to build a SQL statement (so not using db2pd utility) in order to find all applications/connections/application handles that currently hold an internal plan lock (share lock) on a specific package. The only information I have is the package schema & name. I'm not getting there using table functions MON_GET_LOCKS & MPN_FORMAT_LOCK_NAME...

Thanks

tried all table functions / administrative views I know off

Answer 1

Try this:

SELECT L.LOCK_NAME, L.APPLICATION_HANDLE
FROM TABLE (MON_GET_LOCKS ('<lock_object_type>plan</lock_object_type>', -2)) L
CROSS JOIN TABLE
(
  SELECT VALUE AS UNIQUE_ID
  FROM TABLE (MON_FORMAT_LOCK_NAME (L.LOCK_NAME))
  WHERE NAME = 'PACKAGE_TOKEN'
) F
JOIN SYSCAT.PACKAGES P ON P.UNIQUE_ID = F.UNIQUE_ID
WHERE (P.PKGSCHEMA, P.PKGNAME) = ('MY_PKG_SCHEMA', 'MY_PKG_NAME')