75 ways to demystify DB2 #78: Techtip : Bad Page Reported During Backup, However, db2dart is Clean

Body

During offline or online backup, customer encounters bad page error and backup fails, however when using db2dart to check the database, the result is clean.

In db2diag log similar entries can be found:

2015-07-01-11.51.04.622000+600 E41122675F504        LEVEL: Info

…

FUNCTION: DB2 UDB, database utilities, sqlubSetupJobControl, probe:1802

MESSAGE : Starting an offline db backup.

2015-07-01-11.51.15.606000+600 I41123181F803        LEVEL: Warning

…

FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::SubsequentConnect, probe:10167

RETCODE : ZRC=0xFFFFFBF5=-1035

          SQL1035N  The operation failed because the specified database cannot

          be connected to in the mode requested.

…

2015-07-01-11.51.58.606000+600 I41124791F3978       LEVEL: Severe

…

FUNCTION: DB2 UDB, buffer pool services, sqlb_verify_page, probe:2

MESSAGE : ZRC=0x86020001=-2046689279=SQLB_BADP "page is bad"

          DIA8400C A bad page was encountered.

DATA #1 : String, 64 bytes

Error encountered trying to read a page - information follows :

DATA #2 : String, 23 bytes

Page verification error

DATA #3 : Page ID, PD_TYPE_SQLB_PAGE_ID, 4 bytes

500

DATA #4 : Object descriptor, PD_TYPE_SQLB_OBJECT_DESC, 96 bytes

    Obj: {pool:3;obj:45;type:0} Parent={3;45}

…

CALLSTCK:

  [0] 0x00007FFCBEAFF279 pdLog + 0x369

  [1] 0x00007FFCB592EFFF sqlbLogReadAttemptFailure + 0x1DF

  [2] 0x00007FFCB59327AD sqlb_verify_page + 0x5AD

  [3] 0x00007FFCB592FB3E sqlbReadPage + 0x14E

…

2015-07-01-11.51.58.622000+600 E41128771F1040       LEVEL: Critical

…

FUNCTION: DB2 UDB, buffer pool services, sqlbLogReadAttemptFailure, probe:10

MESSAGE : ADM14001C  An unexpected and critical error has occurred: "BadPage".

          The instance may have been shutdown as a result. "Automatic" FODC

          (First Occurrence Data Capture) has been invoked and diagnostic

          information has been recorded in directory

…

2015-07-01-11.51.58.622000+600 I41129813F636        LEVEL: Severe

…

FUNCTION: DB2 UDB, buffer pool services, sqlb_verify_page, probe:2

MESSAGE : ZRC=0x86020001=-2046689279=SQLB_BADP "page is bad"

          DIA8400C A bad page was encountered.

DATA #1 : String, 33 bytes

Page verification error occurred.

…

2015-07-01-11.52.10.138000+600 E41143806F669        LEVEL: Warning

…

FUNCTION: DB2 UDB, buffer pool services, sqlbReadPage, probe:1199

MESSAGE : ADM6006E  DB2 encountered an error while reading page "500" from

          table space "3" for object "45" (located at offset "500" of container …).

We can see that the corrupted page information is as below: tablespace ID: 3, object id: 45, pool page number: 500,

db2dart <dbname> /t /tsi <tablespace ID> /oi <object ID> /rptn <output file name> (e.g. db2dart <dbname> /t /tsi 3 /oi 45 /rptn tsi3oi45.t)

From db2dart result:

Tablespace inspection phase start. Pool: 3

         Tablespace-info inspection phase start.

         Tablespace-info inspection phase end.

…

    Table inspection start: RCENTRAL.TEMP_USER_ESAC

         Data inspection phase start. Data obj: 45  In pool: 3

         Data inspection phase end.

    Table inspection end.

Tablespace inspection phase end.

So DB2 dart is clean, but db2diag log did report bad page was encountered and the backup failed because of it. So what is wrong?

This is actually caused by the table state. Testing load action or select from the table will result error:
 

SQL0668N  Operation not allowed for reason code "3" on table

"<table name>".  SQLSTATE=57016

SQL0668N  Operation not allowed for reason code "<reason-code>" on table

      "<table-name>".

Explanation:

Access to table "<table-name>" is restricted. The cause is based on the

following reason codes "<reason-code>":

3       

         The table is in the Load Pending state. A previous LOAD attempt

         on this table resulted in failure. No access to the table is

         allowed until the LOAD operation is restarted or terminated.

Use command "db2 load query table <table name>" to check the table will receive:

SQL3523W There are no messages to be retrieved from the message file. Reason

code: "1".

Tablestate:

Load Pending

The solution is to bring the table out of the load pending state to normal state. To do this, we can load empty data into the table:

db2 load from /dev/null of del terminate into <table name>

Now if we check the table state again:

db2 load query table <table name>

Tablestate:

  Normal

Now backup will succeed.