Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Dec 2005
    Posts
    39

    Unanswered: DB2 UDB for LUW - sysproc.env_get_sys_info() not running.

    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!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What OS and DB2 (including FP) version are you using?

    Andy

  3. #3
    Join Date
    Dec 2005
    Posts
    39
    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?

  4. #4
    Join Date
    Dec 2005
    Posts
    39
    And the OS is Windows XP.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  6. #6
    Join Date
    Dec 2005
    Posts
    39
    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?

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  8. #8
    Join Date
    Dec 2005
    Posts
    39
    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!!!!!!!!!!!!!!!!!!!!!

  9. #9
    Join Date
    Dec 2005
    Posts
    39
    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!

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Did you run the db2updv8 command?

    Andy

  11. #11
    Join Date
    Dec 2005
    Posts
    39
    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?

  12. #12
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  13. #13
    Join Date
    Dec 2005
    Posts
    39
    My teamlead ran that command. It completed successfully. Now I tried running my env_get_sys_info() again. Again the same error!

  14. #14
    Join Date
    Dec 2005
    Posts
    39
    I ran your query. I dont see the routine env_get_sys_info() in the results. What does that mean?

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •