missing functions

[JasonFriedman80238]

Quite suddenly, it seems, our DB does not recognize these functions:
UPPER
LOWER
LTRIM
LEAST
LOG
The error message is:
SQL0440N No authorized routine named "LOG" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884

There might be others, we haven't tested them all. These functions
continue to work:
MIN
MAX
SUBSTR

Running 8.2.0 on Linux.

[JasonFriedman80238]

I should add that we had a WebSphere application connected to this
database, and it continued to perform normally, until we bounced it,
and now it fails (SQL0440N) whenver trying to invoke, for example,
LOWER.

[Larry Menard]

Check that the LOG function does exist in the catalogs:

C:\>db2 select substr(funcname, 1, 5) as funcname, substr(funcschema, 1, 8) as funcschema from syscat.functions where funcname = 'LOG'

FUNCNAME FUNCSCHEMA
-------- ----------
LOG SYSFUN

1 record(s) selected.

And if it does, make sure that schema is in your CURRENT FUNCTION PATH special register:

C:\>db2 values current function path

1
--------------------------------------------------------------------------------
"SYSIBM","SYSFUN","SYSPROC","LMENARD"

1 record(s) selected.

Note that the WebSphere app could conceivably be modifying its own CURRENT FUNCTION PATH, so that what you get from a CLP connection won't necessarily reflect what the app would see.
--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"JasonFriedman80238" <ja***@powerpull.net> wrote in message news:11**********************@g49g2000cwa.googlegr oups.com...

I should add that we had a WebSphere application connected to this
database, and it continued to perform normally, until we bounced it,
and now it fails (SQL0440N) whenver trying to invoke, for example,
LOWER.

[JasonFriedman80238]

Also, I created a new database on this instance, and the functions are
working there normally.

[Serge Rielau]

Larry Menard wrote:

Check that the LOG function does exist in the catalogs:
C:\>db2 select substr(funcname, 1, 5) as funcname,
substr(funcschema, 1, 8) as funcschema from syscat.functions where
funcname = 'LOG'

FUNCNAME FUNCSCHEMA
-------- ----------
LOG SYSFUN

1 record(s) selected.
And if it does, make sure that schema is in your CURRENT FUNCTION
PATH special register:
C:\>db2 values current function path

1
--------------------------------------------------------------------------------
"SYSIBM","SYSFUN","SYSPROC","LMENARD"

1 record(s) selected.
Note that the WebSphere app could conceivably be modifying its own
CURRENT FUNCTION PATH, so that what you get from a CLP connection won't
necessarily reflect what the app would see.

Also note that really bad things can happen if teh systemclock gets
reset/turned backwards.
Check CURRENT TIMESTAMP and make sure its prior to when the database was
created (you can check the create time of a system object to compare)

Function resolution goes by TIME, PATH, privileges and signature.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Larry]

JasonFriedman80238 wrote:

Also, I created a new database on this instance, and the functions are
working there normally.

Any recent changes in fixpak levels to DB2?

Larry Edelstein

[JasonFriedman80238]

Timestamp looks normal, I'm not aware of any resets.
We recently upgraded from FixPak5 to FixPak7, but that was before we
created the database and installed the application.