Writing from another box now, so I can't 'reply'.

1 - Best option is always a restore.

2 - (onlog) I suspect that onlog will only report that a row was deleted,
not show the data that was deleted. I was thinking of a logged update when
I replied earlier. Hmmm, when I try it out I can actually see the data from
the row which was deleted (HDELETE)

3 - (raw data)
a) Figure out what the pages are that your table is stored on:
oncheck -pt database:table
look at the Extents listing - this is where your table is (physical),
the first number is the chunk (compare that onstat -d)
second number is the offset.

b) Dump those pages:
dd if=device bs=pagesize skip=offset+1 count=npages
(rinse and repeat for every set of extents)

device is what you found on onstat -d
offset is what oncheck -pt reported. Mind you the first page is the table
header, the second page is a bitmap, but then the counting starts at 0, not
1, so just add 1 - this time.
npages is how many pages there are in this extent (min that in the first
extent, you lost two pages to header and bitmap, you will also have a bitmap
page every <pagesize> pages.
So if your first extent is 8 pages, then add one to offset, for the second
extent, SUBTRACT 1 from the offset as there is no longer a table header nor
a bitmap. Follow?

You could also use oncheck -pP chunk offset which will yield you cleaner
output, if it can be interpreted. Except that this will not display deleted
data.

Depending on your engine the first 24 bytes is page header. You will also
have to understand the slot table at the end of each page - which points to
the data in question (or rather doesn't if the row has been deleted). This
works backwards from the end of the page. The 'last' (first if you are
reading properly from right to left) 4 bytes are a time stamp. Each
additional 4 bytes is a pointer to the offset into the table for the row and
the row length. If the offset is 0, the row has been deleted.

I don't know (not sure I ever did) if it's possible for a slot entry to be
re-used and directed somewhere else, but if you eliminate all of the slot
table offsets that point to real data, everything else is your deleted data.

c) ... through a parser which you will probably have to come up with on your
own.

Character data is easily recognizable, numeric data is fairly
straightforward as well. It's the sort of thing you'd do with one hand on
the SQL Reference manual chapter 2.

Of course this all assumes that nobody did an update which required a
re-write or an insert since the deletion occurred, and that no compresses
have occurred on the cleaned pages. Whatever the case, this is by no means
a guaranteed retrieval of lost data, some of it will indeed be gone - hence
the reluctance of anybody official saying that they could do something -
your database integrity is basically shot.


Ah well. Have I scared you off yet? As I said, the best option is a decent
backup.

cheers
j.


sending to informix-list