Unloading around table corruption

Problem(Abstract)

This article describes how to unload rows from a table while avoiding inaccessible data.

Resolving the problem

INTRODUCTION

This document describes how to unload as many rows as possible from a corrupted table.

Note: This technique will not retrieve damaged rows. Damaged rows cannot be retrieved.

Note: This technique will not work for fragmented tables. Please contact advanced support for advice on how to work with fragmented tables. 


STEPS

Before unloading the available data from your table, you will need to create the WHERE clause for the unload. This will be an iterative process that you may have to run several times.

1. Run the SQL command:

SELECT *, HEX(rowid) from table ; 

table
The table with corruption

This will fail when it hits a corrupt row. 

Example: 

SELECT *, HEX(rowid) FROM customer_table; 


Result: the last rowid is 0x00027773

2. Note the last rowid obtained. 


3. Strip the last two digits off of the value of the last rowid. Add one (in hexadecimal) to the remaining value and concatenate "01" to that value. This will be the next rowid value to try to retrieve. 

Example: 

Strip last two digits ( 73) from 0x00027773. The resulting value is 0x000277. Add one (in hexadecimal) for a new value of 0x000278. Concatenate "01"to that value for a resulting value of 0x00027801.

4. Add the last rowid and the next rowid to your WHERE clause. You will want all rows less than or equal to the last rowid and all rows greater than or equal tothe next rowid. 

Example: 

SELECT * FROM customer_table 
WHERE HEX(rowid) <= "0x00027773" OR HEX(rowid) >= "0x00027801"

Important: This is an iterative process you may have to go through several times to fully develop your WHERE clause. For each additional iteration of steps 1-4, concatenate an “AND” to the where clause. 

Example: 

SELECT *, HEX(rowid) FROM customer_table 
WHERE HEX(rowid) <= "0x00027773" OR HEX(rowid) >= "0x00027801" 
AND HEX(rowid) <= "0x00030001" OR HEX(rowid) >= "0x00030101"

5. once you have a list of all retrievable rows, use that query for your UNLOAD statement after removing the HEX(rowid) from the SELECT portion of the query. 

Example: 

UNLOAD TO filename 
SELECT * FROM customer_table 
WHERE HEX(rowid) <= "0x00027773" OR HEX(rowid) >= "0x00027801" 
AND HEX(rowid) <= "0x00030001" OR HEX(rowid) >= "0x00030101"

http://www-01.ibm.com/support/docview.wss?uid=swg21300182