Dev Terms

Articles

WebGUI Hosting

Site Map

Site Menu

Repair DB2 database from SQL1034C and SQL2216N

Have you got one of following messages from your lovely DB2 server?

SQL1034C The database is damaged

or

SQL2216N SQL error "-1224" occurred while reorganizing a database

First of all check your hardware

No matter what excellent brandname is on the face of your server check its memory and HDD.
For memory test run both memtest86+ and mprime

Then check your filesystem with fsck -f /dev/sd[x]

Now run db2dart [db name] and see its output x.RPT which is generally placed under
/home/db2inst/sqllib/db2dump/DART0000/

It will indicate which tables have problems.

Even if you have had automatic backup it is possible that for a few last weeks each of them contains corrupted data. So if you try to restore some tables will be corrupted.

In such case you may try to rescue data by:

1. exporting problematic tables by db2dart with option /DDEL to a comma delimitered file
   # db2dart [db name] /DDEL

and enter following: [table name] 2 0 [number of pages]

[number of pages] can be just a large enough number like 900000000
this step will create a file named like: [TSxTy].DEL

2. save DDL for problematic tables by issueing following command:
   db2look -d [db name] -t [table name] -e > [table name].sql

   Note: you may need to split such files into parts like create_table_[table name].sql and create_other_[table name].sql (indexes, etc.)

3. drop problematic tables, this step may require you to drop some business logic to free the table from dependencies.
   # db2 drop table [table name]

4. recreate tables from previously saved DDL files
   # db2batch -d [db name] -f create_table_[table name].sql

5. try to load data back to the database
   #db2 load from [TSxTy].DEL of del messages [table name].log insert into [table name]
   You can control the progress of loading by
   #db2 list utilities show detail

6. now recreate keys, indexes, business logic.
   # db2batch -d [db name] -f create_other_[table name].sql

You are done.

Sometimes data rescued by db2dart may be lacking many rows due to many damages. In such case you may think about taking some tables from earlier backups when they were in good condition. You can use IXF format for hazzle free export/import:

db2 "export to [table name].ixf of ixf messages export_[table name].msg select * from [table name]"

db2 "load from [table name].ixf of ixf messages load_[table name].msg insert into [table name]"

© Aulix.com 2007

The author does not give any warranties on that method, cannot be responsible for your results and you can use it only on your own risk. So be careful.