SQL0440N returned using table function ENV_GET_SYSTEM_RESOURCES

https://www.ibm.com/support/pages/sql0440n-returned-using-table-function-envgetsystemresources

Troubleshooting

Problem

It is possible to recieve error SQL0440n on a routine like "ENV_GET_SYSTEM_RESOURCES" when running a query similar to the following: Example: SELECT CPU_ONLINE, CPU_HMT_DEGREE, CPU_USER, CPU_SYSTEM, CPU_IOWAIT, CPU_IDLE, MEMORY_TOTAL, MEMORY_FREE, MEMORY_SWAP_TOTAL, MEMORY_SWAP_FREE, VIRTUAL_MEM_TOTAL, VIRTUAL_MEM_FREE FROM table(SYSPROC.ENV_GET_SYSTEM_RESOURCES()); DB2 Database Error: ERROR [42884] [IBM][DB2/AIX64] SQL0440N No authorized routine named "ENV_GET_SYSTEM_RESOURCES" of type "FUNCTION" having compatible arguments was found.

Cause

The reason for this maybe because of a function missing from the database, due to it having been migrated or upgrade to V9.7 Fixpack 6 or later. The SQL0440n error is a good indication that the table function is missing.

Diagnosing The Problem

To verify in fact the table function is missing run:

db2 "select substr(funcschema,1,12)as funcs, substr(funcname,1,30) as name from syscat.functions where funcname='function name in question' order by 1,2";

Replace 'function name in question' with the function name that received the SQL0440N, in this case it would be ENV_GET_SYSTEM_RESOURCES so it would like this:

db2 "select substr(funcschema,1,12)as funcs, substr(funcname,1,30) as name from syscat.functions where funcname='ENV_GET_SYSTEM_RESOURCES' order by 1,2";

Resolving The Problem

If the query:

db2 "select substr(funcschema,1,12)as funcs, substr(funcname,1,30) as name from syscat.functions where funcname='ENV_GET_SYSTEM_RESOURCES' order by 1,2";

comes back with no records:

FUNCS NAME

------------ ------------------------------

0 record(s) selected.

Then you may need to run:

db2updv97 -d dbname

Where dbname is as at put the name of the database in question.

------------------------------------------------------------------------------------------
如果你觉得文章有用，欢迎打赏