How to unload rows from a table avoiding inaccessible data using the ROWID

Problem(Abstract)

How to unload rows from a table avoiding inaccessible data using the ROWID on data corruption scenarios.

Resolving the problem

INTRODUCTION

This document describes a technique that can help you rebuild a table based on the data that might be still accessible. It uses the concept of the ROWIDs and the UNLOAD command.

Attention: This technique only saves rows that are still accessible. Damaged rows are lost.

Note: The technique will not work for fragmented tables, because they do not have rowids.

STEPS

1. Use the UNLOAD command to extract the accessible rows from the table. This command will fail when the first corrupted row is encountered:

UNLOAD TO file1 SELECT rowid, * FROM table ;

file1  
File name where the data is going to the stored

table
Name of the source table

2. Open file1 and take note of the last ROWID that was unloaded successfully:

last_rowid = Last ROWID in file1

3. Add two units to the last_rowid. You will use this value in the next step:

next_rowid = last_rowid + 2

4. Run the UNLOAD command again, this time to retrieve the remaining rows of the table using the next_rowid as the filtering argument:

UNLOAD TO file2
SELECT rowid, * FROM table where rowid >= next_rowid

Note: If UNLOAD fails without producing any data, skip another rowid by increasing next_rowid. You might have to repeat steps 2, 3 and 4 many times depending on the quantity of corrupted rows. There are some scenarios where this technique can be very time-consuming.

5. After unloading all possible rows, repeat steps 1 and 2, this time without selecting the rowid in the column list of the SELECT statement.

UNLOAD TO file1 SELECT * FROM table ;
UNLOAD TO file2
SELECT * FROM table where rowid >= next_rowid

6. Recreate the table using the data that was just unloaded.

LOAD FROM file1 INSERT INTO table;
LOAD FROM file2 INSERT INTO table;

Example:

1. oncheck reported an error in the table customer. Run the first UNLOAD command until it fails.

UNLOAD TO file1 SELECT rowid, * FROM customer;

2. Suppose the last rowid found in the last record on file1 is decimal 1038:

last_rowid = 1038

3. Add two units to last_rowid :

next_rowid = (last_rowid + 2) = 1040

4. Using this calculation, restart the unload on rowid 1040:

UNLOAD TO file2
SELECT rowid, * FROM customer where rowid >= 1040;

5. Repeat this process until you scan the whole table. Assuming that the second UNLOAD command finished without errors, the next step is to run the same commands again without the rowid.

UNLOAD TO file1 SELECT * FROM customer;
UNLOAD TO file2
SELECT * FROM customer where rowid >=1040;

6. Recreate the table and load the data using file1 and file2 as input files:

LOAD from file1 insert into customer;
LOAD from file2 insert into customer;

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