On 04-Apr-2012 18:57 , John McKee wrote:
The problem: Somebody, using an application function, deleted detail
rows from a production table named PREFERENCE_DETAIL. The header
table, PREFERENCE_HEADER has not been damaged. No idea when this
<<SNIP>> There is a mirrored copy, years out of date, that could
be used to at least fix one specific problem. At least, it would be
a starting point.
I was directed to a website that provided what is presented below.
Problems with this are: 1) It does an update where an insert is
needed. 2) There are 20 - 30 columns that need to be inserted, and 3)
I only want to insert specific rows where a match exists between
PROVIDER_ID and PRCEDURE_ID match between the two databases.
In describing such scenarios, the DDL is often helpful to understand
the relationships; or at least definition of the referential integrity
rules, regardless of just implied or actually enforced.
Recovery from data that is 'years out of date' surely must be
undesirable.? Be sure to backup any logging before doing much work in
that database, in case logging wraps, versus effecting a full-stop upon
reaching a logging limit; see my later comments, alluding possibility of
recovering row data from the logs.
Although other ways to effect the same might perform better or might
be more easily understood, I think the following INSERT example should
be easily enough understood to be and functional to effect: copy the
child rows that exist in the mirrored copy for which no matching child
key exists in the active copy of the DETAIL table, and for which there
exists a /parent/ row in the active copy of the HEADER table.
Performing that work as recovery however, assumes the accidentally
deleted rows from the active DETAIL table included every row for each
key (PROVIDER_ID, PRCEDURE_ID) that was deleted; irrelevant if that key
is unique, because /every row/ is /the one row/.
insert into PREFERENCE_DETAIL as target
select * from PREFERENCE_DETAIL as mirror
/* ensure there is a matching parent row */
exists ( select '1' from PREFERENCE_HEADER header
where mirror.PROVIDER_ID = header.PROVIDER_ID
and mirror.PRCEDURE_ID = header.PRCEDURE_ID )
/* copy only rows for which the /key/ does not already exist */
and not exists
( select '1' from PREFERENCE_DETAIL detail
where mirror.PROVIDER_ID = detail.PROVIDER_ID
and mirror.PRCEDURE_ID = detail.PRCEDURE_ID )
Worst case, the people who use this system could print the formatted
data from the old mirror and manually re-enter things - and try to
figure out what, if anything, has changed. But, due to the sharp
knives, staff has been cut to the bone. This system is used to
increase efficiency in the surgery department. But, not in this
Again, I apologize for this non i issue. I just know that I have
seen many great responses for i related help. I understand if this is
just too far removed to be considered.
Investigate "logging"; for other databases, that is something like
the "journaling" on the DB2 for IBM i. Try to find a way to recover the
deleted rows from the log data; e.g. perhaps in a similar manner, how
the UPDATE example was located.