Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
10 replies Latest Post - ‏2010-03-19T12:04:14Z by Voigt
SystemAdmin
SystemAdmin
6968 Posts
ACCEPTED ANSWER

Pinned topic DB2e Error 58004 after deleting/inserting rows

‏2009-02-06T11:32:15Z |
Hi,

I have a question related to DB2e Error 58004 (internal system error).
Our mobile application runs into this error rarely in an undeterministic way.
As a consequence, the mobile database is not usable any more and we have data loss.
As our application will become productive in 4 weeks,
we have to avoid this problem even at the cost of migrating to another database.
Perhaps, somebody can help.

Our application manages data on a mobile device.
The application is written in Java, runs on Windows Mobile 6.0 on an ARM processor
and uses DB2e V9.1.2 as mobile database system. DB2e binaries are taken from path wince/v5/ARMV4TRel.
The data files are placed on an SD card.

Mobile data is synchronized with a J2EE server.
Synchronisation does not make use of DB2e's sync server.
Instead, JMS is used as the basic protocol.
During synchronisation lots of data is deleted and inserted on the device
executing SQL DELETE/INSERT statements on the same table rows.
Rarely, execution of DELETE statement returns DB2e error 58004.
When this error shows up, each consecutive synchronisation ends up returning this error deterministically.
As a consequence, we have to reinstall DB2e's data files. Thus, we have data loss.

The database table of data corruption does not have indexes. Instead,
it has a compound primary key which contains 5 columns. This is the DDL:

create table LTEXT (
ANLN1 CHAR(12) not null,
ANLN2 CHAR(4) not null,
BUKRS CHAR(4) not null,
LINENUM integer not null,
TDFORMAT CHAR(2),
TDLINE VARCHAR(132),
status char(1) not null,
deleted_sap char(1) not null default '0',
deleted char(1) not null default '0',
lastchanged char(14) not null default '00000000000000',
primary key(anln1, anln2, bukrs, status, LINENUM)
) with encryption
;

DBCHECK command returns that this table is broken wrt. row count and rids (scan):

*****************************************************
Checking table LTEXT
*****************************************************
File: DSY_LTEXT
Opening file OK
Seeking OK
Reading OK
Checking file format version 7.1
Checking file-/pagesize OK
Checking filesize 16412672
Seeking OK
Reading OK
File: DSY_iLTEXT
Opening file OK
Seeking OK
Reading OK
Checking file format version 7.1
Checking file-/pagesize OK
Checking filesize 2359296
Seeking OK
Reading OK
Checking row counts FAILED
Checking rids (scan) FAILED
Checking rids (index)
FAILED Index: "ANLN1","ANLN2","BUKRS","STATUS","LINENUM"

Any help on how to avoid this DB2e bug is appreciated.
Is there a way to recover from this error?
When will this bug be fixed?

Thanks,
Michael
Updated on 2010-03-19T12:04:14Z at 2010-03-19T12:04:14Z by Voigt
  • SystemAdmin
    SystemAdmin
    6968 Posts
    ACCEPTED ANSWER

    Re: DB2e Error 58004 after deleting/inserting rows

    ‏2009-02-09T02:19:21Z  in response to SystemAdmin
    First thing you should do is report you database corruption to IBM support and see if there is a known fix or open a problem. If you are not on the latest fix pack, that is the first thing IBM support will tell you to do and IMHO that is the first thing you should do.

    Second thing you should be concerned about is that having your DB2e database on an SD card has some considerations. Those considerations are performance and performance. The first performance issue is a database on an SD card is measurably slower then a database in memory. If at all possible I would recommend the database be in memory. The next performance issue revolves around the option of how database updates are done on the SD card. The options are directly or via the operating system. By doing the update directly you insure the update has been written to the SD card when you get the return code for the update in the program. If you rely on the operating system to do the physical write to the SD card, in the intervening time something bad might happen.
    The direct write option is available for both application programs and synching - see the DB2 Everyplace application program manual for detailed descriptions of the writethrough option.

    If you need a backup of the DB2e database you can always copy it to an SD card.
  • SystemAdmin
    SystemAdmin
    6968 Posts
    ACCEPTED ANSWER

    Re: DB2e Error 58004 after deleting/inserting rows

    ‏2009-02-09T10:58:41Z  in response to SystemAdmin
    IC52486 describes a similar problem, but the bug is fixed in DB2e V8.2. The latest version is V9.1.3 but the release notes do not contain something about our problem. So, I opened a service request (CSOLUS0902090005).

    Clearly, a database on an SD card performs slower than one in main memory, but our database does not fit in main memory. Thus, this is not an option.

    Currently, we use the default (false) related to the write through option. Do you expect, that this option effects error 58004?

    Thanks,
    Michael
  • SystemAdmin
    SystemAdmin
    6968 Posts
    ACCEPTED ANSWER

    Re: DB2e Error 58004 after deleting/inserting rows

    ‏2009-02-10T01:02:17Z  in response to SystemAdmin
    Since this error is occurring very randomly, it is possible that a user randomly does something to the device that prevents the operating system from completely writing the updates to the DB2e database. You might want to put something in the application that verifies the database before letting the user turn off the device or end the application.
    • Elsa.Xiao
      Elsa.Xiao
      14 Posts
      ACCEPTED ANSWER

      Re: DB2e Error 58004 after deleting/inserting rows

      ‏2009-02-10T03:50:04Z  in response to SystemAdmin
      Hi,

      May I know your detailed ErrorCode for the SQLState: 58004?
      While the APAR IC52486 has already been integrated into 9.1.3. But it is only related to logical delete. Probably they are not the same root cause.
      By the way, for this database corruption issue, I think you'd better raise a ticket in case you are the customer of DB2 Everyplace. In this way, you could provide the corrupted database, the error scenario, the reproduce steps to the team help you make further investigation debugging into the internal.

      Elsa
    • SystemAdmin
      SystemAdmin
      6968 Posts
      ACCEPTED ANSWER

      Re: DB2e Error 58004 after deleting/inserting rows

      ‏2009-02-10T13:12:17Z  in response to SystemAdmin
      Probably it's a good idea to use the write through option. Is there another way to verify the database besides DBCHECK command?
  • SystemAdmin
    SystemAdmin
    6968 Posts
    ACCEPTED ANSWER

    Re: DB2e Error 58004 after deleting/inserting rows

    ‏2009-02-10T13:25:38Z  in response to SystemAdmin
    Hi Elsa,

    when I execute "select count(*) from ltext" on the corrupted database, it returns successfully and the result is 135831.

    When I execute "select * from ltext", it returns successfully and the result is 391 rows.

    When I executed "select count(*) from ltext where anln1 = '000010110062' and bukrs = '1200'", it returns sqlstate = 58004,errorcode = -1.

    When I executed "delete from ltext where anln1 = '000010110062' and bukrs = '1200'", it returns sqlstate = 58004,errorcode = -1.

    Unfortunately, I do not know the error code at the time the database was corrupted. Does this help?

    Thanks,
    Michael
    • Elsa.Xiao
      Elsa.Xiao
      14 Posts
      ACCEPTED ANSWER

      Re: DB2e Error 58004 after deleting/inserting rows

      ‏2009-02-11T02:40:16Z  in response to SystemAdmin
      Hi Michael,

      SQLState: 58004, ErrorCode: -1 is a fatal error for DB2e connection state. According to the different row count returned by the two queries, your DB2e database should be corrupted or in an inconsistent state.
      However, as I said before, only with the SQLState: 58004, ErrorCode: -1 without database itself, the history operations on the database, there would be no exactly root cause identified for your problem.
      Anyhow, you could try turn on write through in order to avoid the problem. But if you really want to make out the root cause, you could raise ticket for DB2e L3 support.Thanks.

      Best Regards,
      Elsa
      • SystemAdmin
        SystemAdmin
        6968 Posts
        ACCEPTED ANSWER

        Re: DB2e Error 58004 after deleting/inserting rows

        ‏2009-02-11T09:43:46Z  in response to Elsa.Xiao
        Hi Elsa,

        I raised a ticket yesterday. Does it make sense to turn on the deletePhysicalRemove option in addition to the writeThrough option on order to work around the bug?

        Thanks,
        Michael
        • Elsa.Xiao
          Elsa.Xiao
          14 Posts
          ACCEPTED ANSWER

          Re: DB2e Error 58004 after deleting/inserting rows

          ‏2009-02-11T09:51:35Z  in response to SystemAdmin
          Hi Michael,
          Yes, you could turn on DeletePhysicalRemove together with the WRITE_THROUGH on. One thing to note is that the performance will be impacted for great amount of delete operations while DeletePhysicalRemove is ON.
          At the same time, you could wait for the feedback from DB2e L3 once the ticket is at L3 side. Thanks.

          Best Regards,
          Elsa
  • Voigt
    Voigt
    3 Posts
    ACCEPTED ANSWER

    Re: DB2e Error 58004 after deleting/inserting rows

    ‏2010-03-19T12:04:14Z  in response to SystemAdmin
    Hello,

    is this problem solved in the meanwhile ?

    Looks like we have the same problem - but we are using 9.1.3.
    What about this "writeThrough" and "deletePhysicalRemove" ? how do i use this ? Does it help ?

    with us it's the same: the DB does a lot of deletes and inserts every day - and runs stable for maybe 2 weeks - then we get this 58004.
    According to DBcheck usually indexes are damaged - and we mainly use primary indexes, like in your example.

    Please answer...
    Thank you in advance.
    Andreas Merkle