I am trying to retrieve the hostname using:

SELECT OS_NAME, HOST_NAME, TOTAL_CPUS,
CONFIGURED_CPUS, TOTAL_MEMORY FROM TABLE(SYSPROC.ENV_GET_SYS_INFO()) AS SYSTEMINFO ;

but its giving me, "SQL0440N No authorized routine named "ENV_GET_SYS_INFO" of type "FUNCTION " having compatible arguments was found."

I ran db2updv8 -d database command. It was completed successfully.

Still its giving me the same error. Can anyone help me please? I am totally new to DB2 and I have been breaking my head over this since days. And just to let you know about my other problem, I have to retrieve the names of all databases in that particular server, and I have no idea as to how to accomplish that.
I know the command db2 list database directory would work. But, I have to put in SQL queries (only SELECT stmts). Help please! Thanks a lot!

What OS and DB2 (including FP) version are you using?

Andy

I have no idea what FP they are using. We had DB2 for AS/400 before. Since, it's performance was slow, they said I have to work now on DB2 UDB for LUW. So, I am trying to figure out what FP they must ve been using. The 'About' menu item in the Control Center's Help has the following info:

Level: DB2 V8.1.7.664

Does this help?

And the OS is Windows XP.

Run db2level on the server to get the DB2 info.
What you posted shows FP 7. The function you are
trying to use was not introduced until FP 9.

And as far as I know, there is no SQL available to get the DB names.

Andy

yeah, I did that. Its FixPak 7. So, how should I upgrade it to FP 9?? Can you tell me the procedures and steps required to perform the installation and after it's installed, what should be done for the function to run?

Goto here:

http://www-306.ibm.com/software/data/db2/udb/support/

Download the appropriate FP. The current FP is 10.

Follow the directions in the readme and do not skip any step.

Andy

I did install FP 10. But, still when I try to run the query:
SELECT OS_NAME, HOST_NAME, TOTAL_CPUS,CONFIGURED_CPUS, TOTAL_MEMORY FROM TABLE(SYSPROC.ENV_GET_SYS_INFO()) AS SYSTEMINFO;

from admin client's control center, it's still giving the same error!!!!!!!!!!!!!!!!!!!!!

But, other functions in other schemas, for example, I tried GET_DBM_CONFIG in SYSFUN schema, it seem to have run well. Only those in the SYSPROC are not running, as far as I can diagonise the problem. Help please!

Did you run the db2updv8 command?

Andy

Tried running that from Command Window. The database created for me for testing purposes is called FOO. So I ran:

db2updv8 -d FOO

But it gave the following error:

Connect to database 'FOO' failed with sqlcode = -30082.
DB2UPDV8 processing failed for database 'FOO'.

When my Team Lead (who created the db for me), tried running the same command, it worked. I have no idea as to why.

What do u think is the problem?

db2updv8 only needs to be run once, so when your team lead ran it you should be good to go.

Run:

select routinetype,routinename from syscat.routines where routineschema = 'SYSPROC' order by routinename.

Have your Team Lead run it also.

If you see the routine then you probably do not have rights to execute it.

Andy

My teamlead ran that command. It completed successfully. Now I tried running my env_get_sys_info() again. Again the same error!

I ran your query. I dont see the routine env_get_sys_info() in the results. What does that mean?

In the Control Center, go to Applicaiton Objects, User Defined Functions.

See if there is a UDF called "ENV_GET_SYS_INFO". Check the privledges on the function. The group "public" should have execute authority.