Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2014
    Location
    Long Island, New York
    Posts
    14

    Unanswered: MON_GET_PKG_CACHE_STMT error

    Hi,

    I'm trying to get a list of SQL statements in the package cache using the follow statement but I'm getting the error below. This works on all of my internal databases and all of my clients except for one. I'm running db2 9.7 FP 3 on AIX. Does anyone have any ideas why this database is giving this error?

    From config file
    db2 get db cfg for mydb |grep MON|grep METRICS

    Request metrics (MON_REQ_METRICS) = BASE
    Activity metrics (MON_ACT_METRICS) = BASE
    Object metrics (MON_OBJ_METRICS) = BASE

    Statement
    SELECT MEMBER,
    SECTION_TYPE , TOTAL_CPU_TIME , NUM_EXEC_WITH_METRICS,
    TOTAL_CPU_TIME/NUM_EXEC_WITH_METRICS as
    AVG_CPU_TIME,EXECUTABLE_ID
    FROM TABLE(MON_GET_PKG_CACHE_STMT ( NULL, NULL, NULL, -2)) as T
    WHERE T.NUM_EXEC_WITH_METRICS <> 0
    ORDER BY AVG_CPU_TIME desc
    FETCH FIRST 20 ROWS ONLY;

    Error
    Routine "*CHE_STMT" (specific name "MON_GET_PKG_CACHE_STMT") is implemented with code in library or path "...trusted", function "monGetPkgCacheStmtv97fp6" which cannot be accessed. Reason code: "6".. SQLCODE=-444, SQLSTATE=42724, DRIVER=4.17.30

    Thank you

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    seems that you downgraded a fixpack level or restored your database from higher fixpack level system.
    Try on the server:
    - bind db2schema.bnd bind file
    - run
    db2updv97 -d dbname
    Regards,
    Mark.

  3. #3
    Join Date
    Sep 2014
    Location
    Long Island, New York
    Posts
    14
    H Mark,

    I tried this but no change, I get the same error...

    db2 terminate
    db2 connect to optpac
    db2 BIND db2schema.bnd BLOCKING ALL GRANT PUBLIC SQLERROR CONTINUE
    db2 connect reset
    db2 terminate
    db2updv97 -d optpac

  4. #4
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Your first post in this thread stated you are running DB2 V9.7 FP3.

    But the message refers to an object in FP6.

    Your jdbc driver version is 4.17.30 which is a higher build than any shipped for V9.7.

    Please do the problem determination at the shell /console of the DB2 server (not from a remote client or GUI).

    Post (copy/paste) the output of these commands on the DB2 server in instance+database concerned:

    db2level

    db2 connect to $dbname

    db2 "select versionnumber, version_timestamp, versionbuildlevel from sysibm.sysversions"

    db2 "select char(release_num ,12) as release_num, char(service_level,18) as service_level, char(bld_level,12) as bld_level,fixpack_num from sysibmadm.env_inst_info "

    Please also post the messages that arrive in db2diag.log (with diaglevel 3 or higher) when the query fails.

  5. #5
    Join Date
    Sep 2014
    Location
    Long Island, New York
    Posts
    14
    Here is the info requested:

    $ db2level
    ---------------
    DB21085I Instance "db2inst1" uses "64" bits and DB2 code release "SQL09073"
    with level identifier "08040107".
    Informational tokens are "DB2 v9.7.0.3", "special_25384", "IP23092_25384", and
    Fix Pack "3".
    Product is installed at "/opt/IBM/db2/V9.7".

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

    db2 "select versionnumber, version_timestamp, versionbuildlevel from sysibm.sysversions"

    VERSIONNUMBER VERSION_TIMESTAMP VERSIONBUILDLEVEL
    ------------- -------------------------- ------------------------------
    9050800 2011-12-13-14.56.41.786396 -
    -9070700 2013-05-09-00.30.40.684245 s121002
    -9070500 2013-05-09-00.30.40.684245 s111017
    9070300 2013-05-09-00.30.40.684245 special_25384

    4 record(s) selected.


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

    db2 "select char(release_num ,12) as release_num, char(service_level,18) as service_level, char(bld_level,12) as bld_level,fixpack_num from sysibmadm.env_inst_info "

    RELEASE_NUM SERVICE_LEVEL BLD_LEVEL FIXPACK_NUM
    ------------ ------------------ -------------- -----------
    08040107 DB2 v9.7.0.3 special_25384 3

    1 record(s) selected.


    db2diag.log
    -------------------


    2015-05-18-10.33.47.660557-240 I90614622A620 LEVEL: Severe
    PID : 4259932 TID : 96264 PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000 DB : OPTTEST
    APPHDL : 0-63605 APPID: 63.117.221.180.60944.1505241444
    AUTHID : DB2INST1
    EDUID : 96264 EDUNAME: db2agent (OPTTEST) 0
    FUNCTION: DB2 UDB, routine_infrastructure, sqlerRoutineResolveAddress, probe:10
    RETCODE : ZRC=0x870F0038=-2029060040=SQLO_PROC_NOT_FOUND "Stored Proc Not Found"
    DIA1002C Error accessing function "monGetPkgCacheStmtv97fp6" in
    library "db2dbrouttrusted".

  6. #6
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    You appear to be working with a special build of DB2.
    I suggest you open a PMR with IBM.

  7. #7
    Join Date
    Sep 2014
    Location
    Long Island, New York
    Posts
    14
    Problem resolved. We upgraded to Fix Pack 9. The original database was taken from DB2 9.7 FP 7 and restored into a site with FP 3. Upgrading to FP 9 seems to have fixed the problem. Thanks everyone for your replies.

Tags for this Thread

Posting Permissions

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