Topic
13 replies Latest Post - ‏2012-10-25T07:57:54Z by UteBaumbach
jmueller
jmueller
26 Posts
ACCEPTED ANSWER

Pinned topic OPM flooded monitored database db2diag.log

‏2012-04-17T14:00:15Z |
Hello OPM Team,

sometimes a lot of messsages (every minute) flooding the db2didag.log from the monitored database:
2012-04-17-15.53.50.656022+120 E5356333A846 LEVEL: Warning
PID : 5505126 TID : 34127 PROC : db2sysc 0
INSTANCE: db2tkp NODE : 000 DB : TKP
APPHDL : 0-55487 APPID: 172.16.111.94.61962.120417134420
AUTHID : DB2TKP
EDUID : 34127 EDUNAME: db2agent (TKP) 0
FUNCTION: DB2 UDB, runtime interpreter, sqlriTrustedTruncateTokensUserSqlState, probe:100
DATA #1 : signed integer, 4 bytes
-443
DATA #2 : String with size, 5 bytes
38553
DATA #3 : String with size, 30 bytes
SYSPROC.EVMON_FORMAT_UE_TO_XML
DATA #4 : String with size, 22 bytes
EVMON_FORMAT_UE_TO_XML
DATA #5 : String with size, 21 bytes
Partial events exist.
DATA #6 : signed integer, 4 bytes
68
DATA #7 : signed integer, 4 bytes
22
DATA #8 : signed integer, 4 bytes
22
DATA #9 : signed integer, 4 bytes
21

2012-04-17-15.53.50.656691+120 I5357180A1226 LEVEL: Error
PID : 5505126 TID : 34127 PROC : db2sysc 0
INSTANCE: db2tkp NODE : 000 DB : TKP
APPHDL : 0-55487 APPID: 172.16.111.94.61962.120417134420
AUTHID : DB2TKP
EDUID : 34127 EDUNAME: db2agent (TKP) 0
FUNCTION: DB2 UDB, trace services, sqlt_logerr_data (secondary logging func, probe:0
MESSAGE : Superceding prev. error, probe 165, SQLCA:
DATA #1 : Hexdump, 136 bytes
0x0A00000017BF7940 : 5351 4C43 4120 2020 0000 0088 0000 0064 SQLCA .......d
0x0A00000017BF7950 : 0000 2020 2020 2020 2020 2020 2020 2020 ..
0x0A00000017BF7960 : 2020 2020 2020 2020 2020 2020 2020 2020
0x0A00000017BF7970 : 2020 2020 2020 2020 2020 2020 2020 2020
0x0A00000017BF7980 : 2020 2020 2020 2020 2020 2020 2020 2020
0x0A00000017BF7990 : 2020 2020 2020 2020 5351 4C52 4930 3146 SQLRI01F
0x0A00000017BF79A0 : 8004 0001 0000 0001 0000 0000 0000 0000 ................
0x0A00000017BF79B0 : 0000 0000 0000 0000 2020 2020 2020 2020 ........
0x0A00000017BF79C0 : 2020 2030 3230 3030 02000

2012-04-17-15.53.50.660183+120 I5358407A498 LEVEL: Error
PID : 5505126 TID : 34127 PROC : db2sysc 0
INSTANCE: db2tkp NODE : 000 DB : TKP
APPHDL : 0-55487 APPID: 172.16.111.94.61962.120417134420
AUTHID : DB2TKP
EDUID : 34127 EDUNAME: db2agent (TKP) 0
FUNCTION: DB2 UDB, relation data serv, sqlrr_fetch_error, probe:300
RETCODE : ZRC=0x8012006D=-2146303891=SQLR_CA_BUILT
"SQLCA has already been built"

Environment:

db2pmp@r2l04
[/opt/IBM/OPM/IOPM/RepositoryServer/bin]> pelevel
==============================================================================
IBM InfoSphere Optim Performance Manager
==============================================================================
InfoSphere Optim Performance Manager Server for DB2 for Linux, UNIX and Windows
Version 5.1.0.0.3638, code levels: opmc-5.1.0.0.3638,common-5.1.0.0.3638,pdq-3.10.203
==============================================================================

db2pmp@r2l04
[/opt/IBM/OPM/IOPM/RepositoryServer/bin]> oslevel -s
7100-01-02-1150

db2pmp@r2l04
[/opt/IBM/OPM/IOPM/RepositoryServer/bin]> db2level
DB21085I Instance "db2pmp" uses "64" bits and DB2 code release "SQL09075" with
level identifier "08060107".
Informational tokens are "DB2 v9.7.0.5", "s111017", "IP23285", and Fix Pack
"5".
Product is installed at "/opt/IBM/OPM/V9.7".

Best regards
Joachim
Updated on 2012-10-25T07:57:54Z at 2012-10-25T07:57:54Z by UteBaumbach
  • UteBaumbach
    UteBaumbach
    55 Posts
    ACCEPTED ANSWER

    Re: OPM flooded monitored database db2diag.log

    ‏2012-04-19T14:22:08Z  in response to jmueller
    Hi Joachim,
    there seems to be a DB2 problem on the monitored instance. This part refers to SQL0443 message:
    DATA #1 : signed integer, 4 bytes
    -443
    DATA #2 : String with size, 5 bytes
    38553

    On which DB2 level is the monitored instance ? Has db2updv97 been run on the monitored database after you applied a DB2 V9.7 fixpack ?

    Here are a few links which refer to either the 443 message or to some APARs related to EVMON_FORMAT_UE_TO_XML
    problems.
    http://www-01.ibm.com/support/docview.wss?uid=swg1IC69912
    http://bytes.com/topic/db2/answers/180766-sql-error-code-443-sql-state-38553-what-does-error-mean
    http://www-01.ibm.com/support/docview.wss?uid=swg1IC75525

    Best if you would open a DB2 PMR.

    Regards
    Ute
  • cidaew
    cidaew
    3 Posts
    ACCEPTED ANSWER

    Re: OPM flooded monitored database db2diag.log

    ‏2012-09-19T17:53:56Z  in response to jmueller
    I am getting similar issue with the same evironment just like your.
    Was there any help to resolve this issue.
    • sholars
      sholars
      3 Posts
      ACCEPTED ANSWER

      Re: OPM flooded monitored database db2diag.log

      ‏2012-09-19T18:54:31Z  in response to cidaew
      Does this appear to describe the problem you're seeing:
      http://www-01.ibm.com/support/docview.wss?uid=swg21599972
      • cidaew
        cidaew
        3 Posts
        ACCEPTED ANSWER

        Re: OPM flooded monitored database db2diag.log

        ‏2012-09-19T20:14:06Z  in response to sholars
        This is exactly what the error message is:

        2012-09-19-20.04.26.633268+000 I100163205A1225 LEVEL: Error
        PID : 15335490 TID : 71255 PROC : db2sysc 0
        INSTANCE: db2tsp NODE : 000 DB : TSP
        APPHDL : 0-36513 APPID: 10.36.12.181.57691.120919175923
        AUTHID : DB2TSP
        EDUID : 71255 EDUNAME: db2agent (TSP) 0
        FUNCTION: DB2 UDB, trace services, sqlt_logerr_data (secondary logging func, probe:0 MESSAGE : Superceding prev. error, probe 165, SQLCA:
        DATA #1 : Hexdump, 136 bytes
        0x0700000029FF7710 : 5351 4C43 4120 2020 0000 0088 0000 0064 SQLCA .......d
        0x0700000029FF7720 : 0000 2020 2020 2020 2020 2020 2020 2020 ..
        0x0700000029FF7730 : 2020 2020 2020 2020 2020 2020 2020 2020
        0x0700000029FF7740 : 2020 2020 2020 2020 2020 2020 2020 2020
        0x0700000029FF7750 : 2020 2020 2020 2020 2020 2020 2020 2020
        0x0700000029FF7760 : 2020 2020 2020 2020 5351 4C52 4930 3146 SQLRI01F
        0x0700000029FF7770 : 8004 0001 0000 0001 0000 0000 0000 0000 ................
        0x0700000029FF7780 : 0000 0000 0000 0000 2020 2020 2020 2020 ........
        0x0700000029FF7790 : 2020 2030 3230 3030 02000

        2012-09-19-20.04.26.633697+000 I100164431A497 LEVEL: Error
        PID : 15335490 TID : 71255 PROC : db2sysc 0
        INSTANCE: db2tsp NODE : 000 DB : TSP
        APPHDL : 0-36513 APPID: 10.36.12.181.57691.120919175923
        AUTHID : DB2TSP
        EDUID : 71255 EDUNAME: db2agent (TSP) 0
        FUNCTION: DB2 UDB, relation data serv, sqlrr_fetch_error, probe:300 RETCODE : ZRC=0x8012006D=-2146303891=SQLR_CA_BUILT
        "SQLCA has already been built"
        Please any help to resolve this will be very appreciated. This id flooding my db2diag.log
        • sholars
          sholars
          3 Posts
          ACCEPTED ANSWER

          Re: OPM flooded monitored database db2diag.log

          ‏2012-09-20T19:04:51Z  in response to cidaew
          Hi, I would advise to open a PMR so our support team can collect the necessary information to delve into this more carefully.
          • cidaew
            cidaew
            3 Posts
            ACCEPTED ANSWER

            Re: OPM flooded monitored database db2diag.log

            ‏2012-09-20T23:42:58Z  in response to sholars
            I have opened a PMR 62409.227.000 if you can please help follow up on this to resolving this problem, as it is currently flooding the db2diag.log
  • Jerrica
    Jerrica
    3 Posts
    ACCEPTED ANSWER

    Re: OPM flooded monitored database db2diag.log

    ‏2012-09-26T22:53:09Z  in response to jmueller
    I too have the same error messages flooding monitored database db2diag.log on DB2 v9.7 fp6 and have opened a PMR 62950 442 000 with OPM today 9/26.
    My OPM in v5.1.1 with repository on DB2 v9.7 fp5.
    Does anybody has update on this issue ?
  • UteBaumbach
    UteBaumbach
    55 Posts
    ACCEPTED ANSWER

    Re: OPM flooded monitored database db2diag.log

    ‏2012-09-27T09:47:31Z  in response to jmueller
    Hi all,
    user cidaew is hitting DB2 APAR IC85203. This APAR will be fixed in V9.7fp7.
    Here is the APAR description:

    ABSTRACT:
    WARNING sqlriTrustedTruncateTokensUserSqlState DB2DIAG.LOG PROBE WHEN
    SYSPROC.EVMON_FORMAT_UE_TO_XML DETECTS PARTIAL EVENTS

    DESCRIPTION:
    If partial or incomplete events exist in an event monitor Unformatted
    Event (UE) table, a message (SQL443N) is returned when
    EVMON_FORMAT_UE_TO_XML is run, whether or not the
    LOG_PARTIAL_EVENTS option is specified. Incomplete events can occur when
    an agent finishes processing before the entire event record can be
    inserted in to the UE table.
    This situation can sometimes arise with LOCKING or UNIT OF WORK event
    monitors, particularly in partitioned database environments.

    Whenever EVMON_FORMAT_UE_TO_XML is run directly or if the
    EVMON_FORMAT_UE_TO_TABLES procedure is called (which runs
    EVMON_FORMAT_UE_TO_XML) and partial events are detected, the following
    db2diag.log warning probe is written out and may cause unwanted clutter
    in the db2diag.log:

    2012-04-20-08.39.56.284237-300 E1808342A856 LEVEL: Warning
    PID : 43253948 TID : 17550 PROC : db2sysc
    0
    INSTANCE: <inst-name> NODE : 000 DB :
    <db-name>
    APPHDL : 0-20978 APPID: <app-id>
    AUTHID : <auth-id>
    EDUID : 17550 EDUNAME: db2agent (<db-name>) 0
    FUNCTION: DB2 UDB, runtime interpreter,
    sqlriTrustedTruncateTokensUserSqlState, probe:100
    DATA #1 : signed integer, 4 bytes
    -443
    DATA #2 : String with size, 5 bytes
    38553
    DATA #3 : String with size, 30 bytes
    SYSPROC.EVMON_FORMAT_UE_TO_XML
    DATA #4 : String with size, 22 bytes
    EVMON_FORMAT_UE_TO_XML
    DATA #5 : String with size, 21 bytes
    Partial events exist.
    DATA #6 : signed integer, 4 bytes
    68
    DATA #7 : signed integer, 4 bytes
    22
    DATA #8 : signed integer, 4 bytes
    22
    DATA #9 : signed integer, 4 bytes
    21

    Jerrica, the messages in your db2diag.log look different. Your PMR is currently analyzed.
  • Jerrica
    Jerrica
    3 Posts
    ACCEPTED ANSWER

    Re: OPM flooded monitored database db2diag.log

    ‏2012-10-03T20:50:40Z  in response to jmueller
    I encountered this error too in Db2 v9.7 FP4, opened PMR 64056 442 000 yesterday & being pointed to APAR IC85203 as well
  • NormanWong@CN
    NormanWong@CN
    4 Posts
    ACCEPTED ANSWER

    Re: OPM flooded monitored database db2diag.log

    ‏2012-10-23T18:02:05Z  in response to jmueller
    I get this message and a side effect is that the LOCKING event monitor created by OPM is deactivated. This leaves me with no details of future deadlocks. There is no alert indicating that Locking event monitoring is no longer working.

    I'm left with running the old DB2DETAILDEADLOCK and the new LOCKING event monitors at the same time.

    Not sure if it's the Watchdog or OPM trying to drop the Locking event monitor, but db2diag.log is flooded with:
    2012-10-23-10.51.55.280248-240 I313268A660 LEVEL: Error
    PID : 33096152 TID : 32691 PROC : db2sysc 0
    INSTANCE: db2dudb NODE : 000 DB : UDB2DDSS
    APPHDL : 0-47350 APPID: 10.14.3.32.40280.121101224946
    AUTHID : DB2P900
    EDUID : 32691 EDUNAME: db2agent (UDB2DDSS) 0
    FUNCTION: DB2 UDB, database monitor, sqm_evmon_mgr::dropEvmon, probe:20
    CALLED : DB2 UDB, database monitor, sqm_evmon_mgr::dropEvmonLocally
    RETCODE : ZRC=0x800D002D=-2146631635=SQLM_RC_EVACT "drop active mon"
    DIA8053C Event monitor cannot be deactivated.
    DATA #1 : String with size, 11 bytes
    OPMN8KZNOJ1

    Need to Unconfigure/Reconfigure lock monitoring in OPM to get rid of errors.

    Would be nice if OPM was more graceful.
    Norm
  • UteBaumbach
    UteBaumbach
    55 Posts
    ACCEPTED ANSWER

    Re: OPM flooded monitored database db2diag.log

    ‏2012-10-24T09:09:05Z  in response to jmueller
    Hi Norman,
    likely you are hitting an OPM APAR.
    As soon as I have the APAR number I will post it here. What is the OPM level you are running ? Use the pelevel from the RepositoryServer/bin directory to get the OPM level.

    If you are hitting the OPM APAR then you would see the following messages in the db2pesrv.trc file for this monitored database:
    NLOCK 2012-10-16 23:46:20.784 EXCPMon-283 http://LockMonInst. com.ibm.db2pm.server.lockmon.LockMonitorInternalException: Persistance exception
    at com.ibm.db2pm.server.lockmon.dao.ParticipantDAO.insertTO(Unknown Source)
    at com.ibm.db2pm.server.lockmon.StoringHandler.store(Unknown Source)
    at com.ibm.db2pm.server.lockmon.StoringHandler.handleEvent(Unknown Source)
    at com.ibm.db2pm.server.lockmon.MultipleEventHandler.handleEvent(Unknown Source)
    at com.ibm.db2pm.server.lockmon.SingleLockMonitor.retrieve(Unknown Source)
    at com.ibm.db2pm.server.lockmon.PseudoTandemLockMonitor.retrieve(Unknown Source)
    at com.ibm.db2pm.server.lockmon.LockMonInstanceManager.iterate(Unknown Source)
    at com.ibm.db2pm.server.excp.EXCPMonitor$2.iterate(Unknown Source)
    at com.ibm.db2pm.server.excp.EXCPMonitor.iterate(Unknown Source)
    at com.ibm.db2pm.server.master.PEThread.run2(Unknown Source)
    at com.ibm.db2pm.server.master.PEThread.run(Unknown Source)
    Caused by: com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=3.63.81
    at com.ibm.db2.jcc.am.fd.a(fd.java:671)
    at com.ibm.db2.jcc.am.fd.a(fd.java:60)
    at com.ibm.db2.jcc.am.fd.a(fd.java:127)
    at com.ibm.db2.jcc.am.yn.c(yn.java:2644)
    at com.ibm.db2.jcc.am.yn.a(yn.java:2124)
    at com.ibm.db2.jcc.t4.cb.o(cb.java:844)
    at com.ibm.db2.jcc.t4.cb.j(cb.java:266)
    at com.ibm.db2.jcc.t4.cb.d(cb.java:55)
    at com.ibm.db2.jcc.t4.q.c(q.java:44)
    at com.ibm.db2.jcc.t4.sb.j(sb.java:147)
    at com.ibm.db2.jcc.am.yn.ib(yn.java:2119)
    at com.ibm.db2.jcc.am.zn.b(zn.java:4299)
    at com.ibm.db2.jcc.am.zn.b(zn.java:4429)
    at com.ibm.db2.jcc.am.zn.cc(zn.java:722)
    at com.ibm.db2.jcc.am.zn.executeQuery(zn.java:696)

    Can you confirm that ? you find the db2pesrv.trc file in the <instance id> subdirectory in the OPM working directory. If you don't know the instance id then use the command 'peconfig -list'

    Regards Ute Baumbach
    • NormanWong@CN
      NormanWong@CN
      4 Posts
      ACCEPTED ANSWER

      Re: OPM flooded monitored database db2diag.log

      ‏2012-10-24T12:19:18Z  in response to UteBaumbach
      I'm running OPM 5.1.1.1. Will there be a fix to OPM before 9.7 fp07 is out?
  • UteBaumbach
    UteBaumbach
    55 Posts
    ACCEPTED ANSWER

    Re: OPM flooded monitored database db2diag.log

    ‏2012-10-25T07:57:54Z  in response to jmueller
    Norman,
    the OPM APAR is the following:
    IC87465 IOPM MONITORED DATABASE GETS A MESSAGE IN THE DB2DIAG.LOG OF DIA8053C EVENT MONITOR CANNOT BE DEACTIVATED.

    We don't have a date yet for a new maintenance level for OPM 5111 containing this APAR.
    Via PMR you can request a fix for this APAR for OPM 5111.

    Regards
    Ute Baumbach