LOWER() and UPPER() functions not found.

[herman]

Hi ... We are running v7.1 on Linux Redhat 7.2
All things were fine, until the power supply of the box fried
after 4 years of un-interrupted service (2 reboots only)
Replaced the power supply, and all (well almost) is working 100%

Problem is that all of a sudden the LOWER() and UPPER() functions
in my queries stopped working, with the following error.

ERROR INFO
------------------------------------------------------------------------------------------------------
DB2 Error Code = -440
[IBM][CLI Driver][DB2/LINUX] SQL0440N No function by the name "LOWER"
having compatible arguments was found in the function path.
SQLSTATE=42884
------------------------------------------------------------------------------------------------------
QUERY
-----------------------------------------------------------------------------------
SELECT Username FROM db2inst1.USERS
WHERE SiteID = 0 AND LOWER(UserName) = 'herman'
-----------------------------------------------------------------------------------
TABLE INFO
db2 => describe table users

Column Type Type
name schema name Length
Scale Nulls
------------------------------ --------- ------------------ --------
----- -----
SITEID SYSIBM INTEGER 4
0 No
USERNAME SYSIBM VARCHAR 30
0 No
PASSWORD SYSIBM VARCHAR 30
0 No
ACCESSRIGHTS SYSIBM VARCHAR 50
0 No
REFERNAME SYSIBM VARCHAR 50
0 Yes
REFERSITE SYSIBM INTEGER 4
0 No
SITERIGHTS SYSIBM VARCHAR 50
0 Yes
JUMPURL SYSIBM VARCHAR 100
0 Yes


db2 => SELECT DISTINCT(CURRENT PATH) FROM USERS
shows : "SYSIBM","SYSFUN","DB2INST1"

Most functions are working, but so far LOWER() and UPPER() just stopped
working for no reason.
I have been fighting this all day, and this is a live production
server,
so we have absolute pandamonium here....

If ANYONE can help, im very desperate...

Thanks

[Gert van der Kooij]

In article <11**********************@v46g2000cwv.googlegroups .com>,
he****@appscom.co.za says...

Hi ... We are running v7.1 on Linux Redhat 7.2
All things were fine, until the power supply of the box fried
after 4 years of un-interrupted service (2 reboots only)
Replaced the power supply, and all (well almost) is working 100%

Problem is that all of a sudden the LOWER() and UPPER() functions
in my queries stopped working, with the following error.

Just a first guess but maybe running DB2UPDV7 against the database might
fix some troubles.
Did you check the db2diag.log for error messages?

[herman]

I have checked db2diag....no errors
I havent got the file db2updv7 ... cant find it anywhere on the
system...

[Gert van der Kooij]

In article <11**********************@i40g2000cwc.googlegroups .com>,
he****@appscom.co.za says...

I have checked db2diag....no errors
I havent got the file db2updv7 ... cant find it anywhere on the
system...

If I do remember right it should be on the server in the misc directory.
If you don't have it on your system you are running with the oldest V7
level. You need to install later fixpacks to be a bit less outdated (FP3
to get you to 7.2, FP14 after that to get you to the latest one).

Sorry, I can't help you with the upper/lower problem.

[herman]

SUCCESS !!!!!

When I replaced the power supply, the BOIS reset the date
to the year 2000, and I never picked it up.

I rebooted, set the BIOS time to now() and Bob is now my uncle...

Herman

[Gert van der Kooij]

In article <11**********************@j33g2000cwa.googlegroups .com>,
he****@appscom.co.za says...

SUCCESS !!!!!

When I replaced the power supply, the BOIS reset the date
to the year 2000, and I never picked it up.

I rebooted, set the BIOS time to now() and Bob is now my uncle...

Herman

Good news. Still I would recommend an update if you are really on V7.1
without any fixpacks.

[herman]

:) hehe....
this box is very old, and is going to be retired in about 3 months.
Its been running as is for 4 years without a single problem,
so Id rather not tamper with it now ..

[Gert van der Kooij]

In article <11**********************@j33g2000cwa.googlegroups .com>,
he****@appscom.co.za says...

:) hehe....
this box is very old, and is going to be retired in about 3 months.
Its been running as is for 4 years without a single problem,
so Id rather not tamper with it now ..

Yep, then it's the best choice :)

[Serge Rielau]

Gert van der Kooij wrote:

In article <11**********************@j33g2000cwa.googlegroups .com>,
he****@appscom.co.za says...

:) hehe....
this box is very old, and is going to be retired in about 3 months.
Its been running as is for 4 years without a single problem,
so Id rather not tamper with it now ..

Yep, then it's the best choice :)

Just to provide some color:
Function resolution ignores functions created in the future.
For functions defined in SYSFUN (such as old versions of UPPER and
LOWER) this means the system time needs to be higher than the time the
database was created.
Apparently LOWER and UPPER were the only SYSFUN functions you used,
otherwise the problem would have been more generalized.

I recall a PMR years ago where a customer had a DPF (then EEE) system
with a major clock skew between the catalog node and other nodes.
As a result any UDF they defined could not be accessed for some minutes
after the CREATE. Magically after a few retries they appeared. :-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab