Topic
  • 4 replies
  • Latest Post - ‏2014-08-08T10:02:18Z by mor
JamesBonde
JamesBonde
2 Posts

Pinned topic Marking fmp as unstable, fmp is forced or aborted

‏2014-08-08T03:34:29Z |

     Hi,Everyone, we are facing a SQL0501N(The cursor specified in a FETCH or CLOSE statement is not open.)  problem in our application which used DB2 as the database, I only know that when make the connection, the application will execute a stored procedure in DB2. I check the server and error log using the db2diag, I see the "Marking fmp as unstable, fmp is forced or aborted" error, is it related the SQL0501N error? how could I get rid the error, I have attached the log below.  thanks a lot .


2014-08-04-18.43.27.402000+480 I2306954F950       LEVEL: Error

PID     : 1880                 TID  : 3200        PROC : db2syscs.exe

INSTANCE: DB2                  NODE : 000         DB   : SDETUDI

APPHDL  : 0-20562              APPID: *LOCAL.DB2.140804104426

AUTHID  : DB2ADMIN

EDUID   : 3200                 EDUNAME: db2agent (SDETUDI) 0

FUNCTION: DB2 UDB, relation data serv, sqlrr_rds_common_post, probe:1700

MESSAGE : Severe TERMINATE err at nest lvl 1, in SQL from rtn

          SYSPROC.SYSINSTALLOBJECTS:

DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes

 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -1224   sqlerrml: 0

 sqlerrmc:

 sqlerrp : SQLRRRCP

 sqlerrd : (1) 0x80040003      (2) 0x00000003      (3) 0x00000000

           (4) 0x00000000      (5) 0x00000000      (6) 0x00000000

 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)    

           (7)      (8)      (9)      (10)        (11)     

 sqlstate: 55032



2014-08-04-18.43.27.402000+480 I2307906F513       LEVEL: Error

PID     : 1880                 TID  : 3200        PROC : db2syscs.exe

INSTANCE: DB2                  NODE : 000         DB   : SDETUDI

APPHDL  : 0-20562              APPID: *LOCAL.DB2.140804104426

AUTHID  : DB2ADMIN

EDUID   : 3200                 EDUNAME: db2agent (SDETUDI) 0

FUNCTION: DB2 UDB, routine_infrastructure, sqlerInvokeFencedRoutine, probe:40

DATA #1 : Hex integer, 4 bytes

0x804B006D

DATA #2 : Hex integer, 4 bytes

0xFFFFFB38



2014-08-04-18.43.27.402000+480 I2308421F513       LEVEL: Error

PID     : 1880                 TID  : 3200        PROC : db2syscs.exe

INSTANCE: DB2                  NODE : 000         DB   : SDETUDI

APPHDL  : 0-20562              APPID: *LOCAL.DB2.140804104426

AUTHID  : DB2ADMIN

EDUID   : 3200                 EDUNAME: db2agent (SDETUDI) 0

FUNCTION: DB2 UDB, routine_infrastructure, sqlerInvokeFencedRoutine, probe:45

DATA #1 : Hex integer, 4 bytes

0x804B006D

DATA #2 : Hex integer, 4 bytes

0xFFFFFB95



2014-08-04-18.43.27.402000+480 I2308936F7657      LEVEL: Error

PID     : 1880                 TID  : 3200        PROC : db2syscs.exe

INSTANCE: DB2                  NODE : 000         DB   : SDETUDI

APPHDL  : 0-20562              APPID: *LOCAL.DB2.140804104426

AUTHID  : DB2ADMIN

EDUID   : 3200                 EDUNAME: db2agent (SDETUDI) 0

FUNCTION: DB2 UDB, routine_infrastructure, sqlerInvokeFencedRoutine, probe:88

DATA #1 : String, 37 bytes

Routine failed with termination error

DATA #2 : Hexdump, 1392 bytes

0x000007FF0A841A38 : 5546 4F42 0000 0000 D80D 0000 0000 0000    UFOB............

0x000007FF0A841A48 : 0000 0000 0000 0000 505B 09FE FF07 0000    ........P[......

0x000007FF0A841A58 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841A68 : 0000 0000 0000 0000 20FE 00FE FF07 0000    ........ .......

0x000007FF0A841A78 : 0000 0000 0000 0000 0821 840A FF07 0000    .........!......

0x000007FF0A841A88 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841A98 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841AA8 : 0000 0000 0000 0000 8021 840A FF07 0000    .........!......

0x000007FF0A841AB8 : 0022 840A FF07 0000 C07E 8809 FF07 0000    .".......~......

0x000007FF0A841AC8 : 7021 840A FF07 0000 A81F 840A FF07 0000    p!..............

0x000007FF0A841AD8 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841AE8 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841AF8 : 0100 0000 0100 0000 0000 0000 4000 0000    ............@...

0x000007FF0A841B08 : 0100 0000 0000 0000 4808 0000 0000 0000    ........H.......

0x000007FF0A841B18 : 2821 840A FF07 0000 0000 0000 0000 0000    (!..............

0x000007FF0A841B28 : 0100 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841B38 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841B48 : 0000 0000 0000 0000 C803 0000 0000 0000    ................

0x000007FF0A841B58 : 0000 0001 0200 FFFF 4C70 0801 0000 0000    ........Lp......

0x000007FF0A841B68 : 0000 0000 0000 0000 2201 0100 0000 0000    ........".......

0x000007FF0A841B78 : 0000 0000 0000 0000 0200 0000 0800 0000    ................

0x000007FF0A841B88 : 0400 0000 0100 0000 0400 0000 0700 0C00    ................

0x000007FF0A841B98 : 0000 0900 1900 0000 0700 1100 0400 0400    ................

0x000007FF0A841BA8 : 0400 0000 0000 0400 0000 0000 0000 0000    ................

0x000007FF0A841BB8 : 0000 0800 0000 0000 0000 0000 0000 1E00    ................

0x000007FF0A841BC8 : 0000 0000 0000 0000 0000 6A05 0000 2300    ..........j...#.

0x000007FF0A841BD8 : 0D00 1200 5359 5350 524F 432E 5359 5349    ....SYSPROC.SYSI

0x000007FF0A841BE8 : 4E53 5441 4C4C 4F42 4A45 4354 5300 0000    NSTALLOBJECTS...

0x000007FF0A841BF8 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841C08 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841C18 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841C28 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841C38 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841C48 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841C58 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841C68 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841C78 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841C88 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841C98 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841CA8 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841CB8 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841CC8 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841CD8 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841CE8 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841CF8 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841D08 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841D18 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841D28 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841D38 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841D48 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841D58 : 0000 0000 0000 0053 5953 494E 5354 414C    .......SYSINSTAL

0x000007FF0A841D68 : 4C4F 424A 4543 5453 0000 0000 0000 0000    LOBJECTS........

0x000007FF0A841D78 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841D88 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841D98 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841DA8 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841DB8 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841DC8 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841DD8 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841DE8 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841DF8 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841E08 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841E18 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841E28 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841E38 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841E48 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841E58 : 0000 0000 0000 0000 5359 5349 424D 2020    ........SYSIBM  

0x000007FF0A841E68 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841E78 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841E88 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841E98 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841EA8 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841EB8 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841EC8 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841ED8 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841EE8 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841EF8 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841F08 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841F18 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841F28 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841F38 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841F48 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841F58 : 0000 0000 0000 0000 0000 0701 0000 0000    ................

0x000007FF0A841F68 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FF0A841F78 : 0000 0000 0000 0000 0044 5809 FF07 0000    .........DX.....

0x000007FF0A841F88 : 2090 5809 FF07 0000 800C 0000 0000 0000     .X.............

0x000007FF0A841F98 : 0000 0000 0000 0000 0000 0000 0000 0000    ................



2014-08-04-18.43.27.402000+480 E2316595F3526      LEVEL: Severe

PID     : 1880                 TID  : 3200        PROC : db2syscs.exe

INSTANCE: DB2                  NODE : 000         DB   : SDETUDI

APPHDL  : 0-20562              APPID: *LOCAL.DB2.140804104426

AUTHID  : DB2ADMIN

EDUID   : 3200                 EDUNAME: db2agent (SDETUDI) 0

FUNCTION: DB2 UDB, routine_infrastructure, sqlerReturnFmpToPool, probe:900

DATA #1 : String, 50 bytes

Marking fmp as unstable, fmp is forced or aborted:

DATA #2 : Boolean, 1 bytes

true

DATA #3 : String, 20 bytes

Fmp entry use count:

DATA #4 : unsigned integer, 4 bytes

1

DATA #5 : String, 8 bytes

Fmp TID:

DATA #6 : Hexdump, 4 bytes

0x000007FFFE00FF70 : 140F 0000                                  ....

DATA #7 : String, 8 bytes

Fmp row:

DATA #8 : sqlerFmpRow, PD_SQLER_TYPE_FMP_ROW, 496 bytes

 fmpPid: 3544

 fmpPoolList Ptr: 0x000007fffdd9d440    fmpForcedList Ptr: 0x0000000000000000

 nextFmpCB Ptr: 0x000007fffdbcf460    prevFmpCB Ptr: 0x0000000000000000

 fmpIPCList Ptr: 0x000007fffe08d560

 stateFlags: 0x00000012    numFmp32Attaches: 0

 numActiveThreads: 1    numPoolThreads: 8

 fmpCodePage: 0    fmpRowUseCount: 1

 active: 0x01     rowLoaderValidate: 0x00

 ipcLatch:

0x000007FFFE00C724 : 0000 AD01                                  ....

 rowLatch:

0x000007FFFE00C728 : 0000 AE01                                  ....

 fmpAgentList:

0x000007FFFE00C730 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FFFE00C740 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FFFE00C750 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FFFE00C760 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FFFE00C770 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FFFE00C780 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FFFE00C790 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FFFE00C7A0 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FFFE00C7B0 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FFFE00C7C0 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FFFE00C7D0 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FFFE00C7E0 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FFFE00C7F0 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FFFE00C800 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FFFE00C810 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FFFE00C820 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FFFE00C830 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FFFE00C840 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FFFE00C850 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FFFE00C860 : 0000 0000 0000 0000 00F0 0000 9A00 0000    ................

0x000007FFFE00C870 : 0000 0000 0094 0000 9A00 0000 0000 0000    ................

0x000007FFFE00C880 : 0000 0000 0000 0000 20FE 00FE FF07 0000    ........ .......

0x000007FFFE00C890 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x000007FFFE00C8A0 : 20C7 00FE FF07 0000 E0FE BCFD FF07 0000     ...............

0x000007FFFE00C8B0 : 40C9 00FE FF07 0000 0000 0000 0000 0000    @...............

0x000007FFFE00C8C0 : 0000 0000 0000 0000 0000 0000 0000 0000    ................



2014-08-04-18.43.27.402000+480 I2326402F513       LEVEL: Error

PID     : 1880                 TID  : 3200        PROC : db2syscs.exe

INSTANCE: DB2                  NODE : 000         DB   : SDETUDI

APPHDL  : 0-20562              APPID: *LOCAL.DB2.140804104426

AUTHID  : DB2ADMIN

EDUID   : 3200                 EDUNAME: db2agent (SDETUDI) 0

FUNCTION: DB2 UDB, routine_infrastructure, sqlerInvokeFencedRoutine, probe:99

DATA #1 : Hex integer, 4 bytes

0xFFFFFB95

DATA #2 : Hex integer, 4 bytes

0xFFFFFB95

Updated on 2014-08-08T03:35:00Z at 2014-08-08T03:35:00Z by JamesBonde
  • Jun Su Lee
    Jun Su Lee
    24 Posts

    Re: Marking fmp as unstable, fmp is forced or aborted

    ‏2014-08-08T05:16:29Z  

    Not sure which version and fix pack you are using.

    It can be triggered by many reasons.

    An example I can bring is mostly related with cursor with for loop statement in stored procedure and one procedure calls other procedure.

    But, it's hard to say just from the db2diag.log. I believe the store procedure needs to be investigated in detail.

     

  • mor
    mor
    513 Posts

    Re: Marking fmp as unstable, fmp is forced or aborted

    ‏2014-08-08T09:02:43Z  

    Most likely the SQL0501N is caused by a programming mistake in the application (or stored procedure).

    Most likely the information you posted from db2diag.log is not connected to the SQL0501N.

    Involve the application support people: find out *which* stored procedures get called,  find out which code is broken by using application-supplied tracing.

    If the symptom (SQL0501N) is simple to recreate then the DBA can run an event-monitor for statements and transactions to see what is happening:  a common programming mistake is poorly written exception handlers in stored procedures.

  • JamesBonde
    JamesBonde
    2 Posts

    Re: Marking fmp as unstable, fmp is forced or aborted

    ‏2014-08-08T09:38:59Z  

    Thank you very much for the posting. The problem here is that the "stored procedures" come with the commercial software we bought, it is a black box to us. It is quite mature since we have been used for years. The SQL0501N comes first this month, but after I restart the DB2 instance, the problem is gone. I will check the event-monitor to see what is happening when the error comes. Thanks  again.

  • mor
    mor
    513 Posts

    Re: Marking fmp as unstable, fmp is forced or aborted

    ‏2014-08-08T10:02:18Z  

    Thank you very much for the posting. The problem here is that the "stored procedures" come with the commercial software we bought, it is a black box to us. It is quite mature since we have been used for years. The SQL0501N comes first this month, but after I restart the DB2 instance, the problem is gone. I will check the event-monitor to see what is happening when the error comes. Thanks  again.

    It does not really matter where the stored-procedures originate - as long as they are supported by someone.

    The most important thing is to identify the code (which sproc, if any) is suffering this symptom (the source of the problem might differ from your expectation).

    It's possible the vendor (of the commercial software) is aware of this symptom, so worthwhile to check with the vendor also.

    But if the issue happens only one time per month, and you can assess the impact on the business (if any) as low, then you can make a decision about exactly how much effort to assign to this specific symptom...