RE: General ideas on researching deleted records

UPDDTA produces a print report of deletions - run this for date range you
need - this is all since 5/10/2015
WRKSPLF SELECT(*ALL *ALL *ALL *ALL *ALL QPDZDTALOG) PERIOD((*AVAIL
'05/10/2015'))


File        User        Queue 
QPDZDTALOG  CARMEND     QPRINT
QPDZDTALOG  CARMEND     QPRINT
QPDZDTALOG  CARMEND     QPRINT
QPDZDTALOG  JIMF        QPRINT

Also, if physical file is Reuse deleted records . . . . . . . . . . . :
REUSEDLT   *NO
And file has not been reorganized, then accessing file by RRN should either
show 
Missing RRN (the records were deleted) --or-- the records are there, but the
keys or date were altered  (like a bad fix where attempt to change key field
has left the records unreachable by the application). 

 backups? 

Jim 
 

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
CRPence
Sent: Wednesday, May 13, 2015 8:48 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: General ideas on researching deleted records

On 13-May-2015 14:43 -0500, Hoteltravelfundotcom wrote:
> This is a general and long shot question.
>
> It seems we have lost 2 days of invoices from a particular file.

   They are not visible\found according to what selection?  They may have
been changed in a way that they are no longer visible with that criteria.

>
> How to research what caused this?

   As apparently already responded [in a followup, but in the wrong 
message thread], reviewing of the _journal_ of the file that logs the 
activity will be pursued if indeed the file is journaled.  The journal 
of the file is the definitive answer; that is the /logging/ of the I/O 
activity against the file.

   Hints can be found in auditing if the object is audited to include 
change activity, less informative but even hint if _read_ activity is 
logged because the /open/ is a read-access; though a change or read 
access may be logged, there is no telling what actually was done, and 
even the journal of the file can not reveal what reading was done beyond 
the open.

   If the file has any delete triggers, a delete trigger may produce 
some logging; update triggers if the entries are /lost/ due to updates 
rather than delete.

>
> Developers are the primary suspects of course, or rather process of
> elim. while most devs would never do some of these I am trying to
> see what may have happened and how to locate any logs of these: by
> all means let me know if my thinking is completely wrong and how it
> should be looked for.
>
> MY thinking is records get deleted by a dev if there is
>
> 1. any program (rpg/cl ?)  that outputs to that file

   Output is non-destructive to active records; exception is output 
_with clear_, but then all records, not just two-days of rows would be 
expected to be unavailable.  Thus only deleted records might get lost, 
reused, if the database file attribute is REUSEDLT(*YES).

   FWiW, CL has no real interface to database for I/O except read by 
RCVF.  Otherwise CL is pretty much restricted to what the CL calls that 
might change file data; e.g. any Output File (OUTFILE) capability is 
much like "Output" described just above with RPG, but the CL offers 
entry points into the SQL via things like RUNSQL, RUNSQLSTM, STRQMQRY, 
QSH CMD('DB2 ...'), or any number of other SQL and even any non-SQL 
invocations via command or by CALL or by CALLPRC that might end up 
modifying data in DB file(s).

> 2. A Cpyf to that file, overlaying blanks i guess.

   That would be an output operation, just like the RPG program scenario 
noted above.  The operation is destructive to active records only with 
MBROPT(*REPLACE), for which all data in the member would have been lost.

> 3. command but a dltf would be halted because logicals exist.

   If someone were to have deleted the file, then they could have gone 
to the work to delete the logical files; simple enough to do without 
DLTF, just use the SQL statement DROP TABLE with the CASCADE option 
against the physical file.

   But by mention of that... Wow... OK, so massive destruction is in 
consideration; I though allusions to effective Clear Physical File 
Member (CLRPFM) were a probable stretch given the apparently limited 
data loss.  But if that destructive possibility is considered, then one 
could presume that whoever had deleted the members and\or files also had 
done a RSTOBJ from a recent backup; perhaps from two days ago.  In that 
case the Restore Date\Time visible on the DSPFD output would be 
revealing; the *FILE object has a restore date\time, but be sure to look 
at the member, because RMVM is much the same as DLTF with regard to 
total destruction.  And of course the LFs or LFMs would have to have 
been restored as well.

> 4. Upddta. this shop uses upddta a lot, not just developers. but then
> it has to be F23 and a lot of those.

   Easy enough, albeit repetitive.  There is also STRQM feature 
[option-3="Work with Query Manager Tables" that allows /editing/ rows; I 
do not recall how much easier that interactive feature would be for 
delete activity.  Nonetheless, as others note, the use of an SQL DELETE 
statement seems more likely than CHGDTA or UPDDTA; or if the 
accessibility is just impacted versus truly prevented, then perhaps an 
SQL UPDATE statement.

>
> Thank you for any concrete info I acknowledge this is a fuzzy topic
> if  this is fully off topic please remove.
>

   If the file is not journaled, best to immediately effect a SAVOBJ 
and\or CRTDUPOBJ [that is then saved] to ensure a copy of the DBF with 
the delete records is available for possible recovery if the file is not 
journaled or the journal receivers were not kept or are otherwise 
unavailable.  Both require an exclusive lock long enough to /copy/ the 
data to the target.  Just because the file is REUSEDLT(*NO) does not 
prevent the loss of those; reorganize or any feature that does similar can