SQL0440N while executing new monitoring table memory functions functions added in v9.7 fp5

Technote (troubleshooting)


Problem(Abstract)

Receive SQL0440N error when using "MON_GET_MEMORY_SET" & MON_GET_MEMORY_POOL functions on DB2 V9.7 FP5

Symptom

When we use MON_GET_MEMORY_SET & MON_GET_MEMORY_POOL functions. we received SQL0440N error :

Example:
db2 "SELECT varchar(memory_set_type,20) as set_type,varchar(db_name,20)
as dbname,memory_set_used,memory_set_used_hwm FROM TABLE
(MON_GET_MEMORY_SET(NULL,CURRENT_SERVER,-2))"

SQL0440N No authorized routine named "MON_GET_MEMORY_SET" of type
"FUNCTION" having compatible arguments was found. SQLSTATE=42884


Cause

If db2updv97 not executed on the upgraded database, MON_GET_MEMORY_SET & MON_GET_MEMORY_POOL functions will be missing in DB3 V9.7 FP5

Environment

DB2 V9.7

Diagnosing the problem

Prior to DB2 V9.7 FP5, by default the following "MON_GET_" functions will be there:
From the below output we can see "MON_GET_MEMORY_SET" function is missing.



Example: Output from DB2 V9.7 FP4


$ db2 "select funcname from syscat.functions where funcname like 'MON_GET_%'"

FUNCNAME
-------------------------------------------------------------------------
MON_GET_ACTIVITY_DETAILS
MON_GET_APPLICATION_HANDLE
MON_GET_APPLICATION_ID
MON_GET_APPL_LOCKWAIT
MON_GET_BUFFERPOOL
MON_GET_CONNECTION
MON_GET_CONNECTION_DETAILS
MON_GET_CONTAINER
MON_GET_EXTENT_MOVEMENT_STATUS
MON_GET_FCM
MON_GET_FCM_CONNECTION_LIST
MON_GET_INDEX
MON_GET_LOCKS
MON_GET_PKG_CACHE_STMT
MON_GET_PKG_CACHE_STMT_DETAILS
MON_GET_SERVICE_SUBCLASS
MON_GET_SERVICE_SUBCLASS_DETAILS
MON_GET_TABLE
MON_GET_TABLESPACE
MON_GET_UNIT_OF_WORK
MON_GET_UNIT_OF_WORK_DETAILS
MON_GET_WORKLOAD
MON_GET_WORKLOAD_DETAILS

23 record(s) selected.

Example : After applying FP5 before running db2updv97:

$ db2 "select funcname from syscat.functions where funcname like 'MON_GET_%'"

FUNCNAME
---------------------------------------------------------------------
MON_GET_ACTIVITY_DETAILS
MON_GET_APPLICATION_HANDLE
MON_GET_APPLICATION_ID
MON_GET_APPL_LOCKWAIT
MON_GET_BUFFERPOOL
MON_GET_CONNECTION
MON_GET_CONNECTION_DETAILS
MON_GET_CONTAINER
MON_GET_EXTENT_MOVEMENT_STATUS
MON_GET_FCM
MON_GET_FCM_CONNECTION_LIST
MON_GET_INDEX
MON_GET_LOCKS
MON_GET_PKG_CACHE_STMT
MON_GET_PKG_CACHE_STMT_DETAILS
MON_GET_SERVICE_SUBCLASS
MON_GET_SERVICE_SUBCLASS_DETAILS
MON_GET_TABLE
MON_GET_TABLESPACE
MON_GET_UNIT_OF_WORK
MON_GET_UNIT_OF_WORK_DETAILS
MON_GET_WORKLOAD
MON_GET_WORKLOAD_DETAILS

23 record(s) selected.


Example : After running db2updv97

$ db2 "select funcname from syscat.functions where funcname like 'MON_GET_%'"

FUNCNAME
-------------------------------------------------------------------------
MON_GET_ACTIVITY_DETAILS
MON_GET_APPLICATION_HANDLE
MON_GET_APPLICATION_ID
MON_GET_APPL_LOCKWAIT
MON_GET_BUFFERPOOL
MON_GET_CONNECTION
MON_GET_CONNECTION_DETAILS
MON_GET_CONTAINER
MON_GET_EXTENT_MOVEMENT_STATUS
MON_GET_FCM
MON_GET_FCM_CONNECTION_LIST
MON_GET_INDEX
MON_GET_LOCKS
MON_GET_MEMORY_POOL
MON_GET_MEMORY_SET
MON_GET_PKG_CACHE_STMT
MON_GET_PKG_CACHE_STMT_DETAILS
MON_GET_SERVICE_SUBCLASS
MON_GET_SERVICE_SUBCLASS_DETAILS
MON_GET_TABLE
MON_GET_TABLESPACE
MON_GET_UNIT_OF_WORK
MON_GET_UNIT_OF_WORK_DETAILS
MON_GET_WORKLOAD
MON_GET_WORKLOAD_DETAILS

25 record(s) selected.

From the above output we can see MON_GET_MEMORY_SET & MON_GET_MEMORY_POOL functions added after executing db2updv97.


Resolving the problem

Run db2updv97 to update the database system catalog.


Historical Number

05097
000
744

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

InfoSphere Balanced Warehouse
Balanced Warehouse

Software version:

9.7

Operating system(s):

AIX, Linux, Windows

Reference #:

1640267

Modified date:

2013-07-19

Translate my page

Machine Translation

Content navigation