Journaling Tutorial by Rob Berendt

Do these steps

CRTLIB ROBJERRY

CRTJRNRCV JRNRCV(ROBJERRY/JERRY0001)

CRTJRN JRN(ROBJERRY/JERRY) JRNRCV(ROBJERRY/JERRY0001) MNGRCV(*SYSTEM)
DLTRCV(*NO)

Create a file in ROBJERRY.  Use DDS or one of the following:
CRTPF FILE(ROBJERRY/TEST) RCDLEN(10)
STRSQL:  CREATE TABLE ROBJERRY/TESTTWO (MYCHAR CHAR (10 ), MYNBR DEC (15
,5))

Journal the file:
STRJRNPF FILE(ROBJERRY/TEST) JRN(ROBJERRY/JERRY) IMAGES(*BOTH)

Use UPDDTA, STRSQL or something to add several records to TEST.

DSPJRN JRN(ROBJERRY/JERRY) FILE((ROBJERRY/TEST))  FROMTIME(...)
TOTIME(...)
Pick a "PT" entry and use option 5 to see what was written to the record.
Now hit F10 and you can tell what:
  - job
  - date/time
  - user
  - and even the program used
that updated that row.

Let's say you ran the SQL from hell and forget the where clause and now
all your rows are deleted.
STRSQL:  delete from robjerry/test

DSPPFM ROBJERRY/TEST
Yep, they're gone.

DSPJRN JRN(ROBJERRY/JERRY) FILE((ROBJERRY/TEST))  FROMTIME(...)
TOTIME(...)
I see some "DL" types in there from that sql statement.  In my case,
sequence 13-15.

RMVJRNCHG JRN(ROBJERRY/JERRY) FILE((ROBJERRY/TEST)) FROMENTLRG(15)
TOENTLRG(13)
Notice from 15 to 13?  Seems weird until you understand it.

DSPPFM ROBJERRY/TEST
Hey, the records are back!

Now I save the file.
CRTSAVF FILE(ROBJERRY/JERRYSAVF)
SAVOBJ OBJ(TEST) LIB(ROBJERRY) DEV(*SAVF) SAVF(ROBJERRY/JERRYSAVF)

Then I slung several more records in the file.

Then somebody deleted the file
DLTF ROBJERRY/TEST

DSPJRN JRN(ROBJERRY/JERRY)
Had to leave the file off since it no longer exists.
Ho!  What's this?
Code  Type
 D     DT
Sequence . . . . . . :   32
Code . . . . . . . . :   D  - Database file operation
Type . . . . . . . . :   DT - Delete file
F10=Display only entry details
Date . . . . . . . . :   08/20/09
Time . . . . . . . . :   16:09:51
Job  . . . . . . . . :   554181/ROB/ROBS1
User profile . . . . :   ROB     (Helps in case of profile handles and C/S

type jobs.)
Program  . . . . . . :   QCMD
I'm gonna kick his tail...

But first let's get the data back.

RSTOBJ OBJ(TEST) SAVLIB(ROBJERRY) DEV(*SAVF) OBJTYPE(*FILE)
SAVF(ROBJERRY/JERRYSAVF)

DSPJRN JRN(ROBJERRY/JERRY) FILE((ROBJERRY/TEST))

Sequence . . . . . . :   23
Code . . . . . . . . :   F  - Database file member operation
Type . . . . . . . . :   MS - Member saved
Date . . . . . . . . :   08/20/09
Time . . . . . . . . :   16:08:52
But it gets better, I can use the *LASTSAVE instead of having to find the
above entry.

APYJRNCHG JRN(ROBJERRY/JERRY) FILE((ROBJERRY/TEST)) RCVRNG(*LASTSAVE)
FROMENTLRG(*LASTSAVE) TOENTLRG(31)
3 entries applied to 1 objects.
Entry 31 was the entry just prior to the DLTF.  Make sure that you check
the journal receiver for related operations like member delete (MD) , etc
which are all part of delete file (DT).

DSPPFM ROBJERRY/TEST
Yep, I now have all my data.

That's the quick and dirty.

Adding commitment control and stuff on this is gravy.

Then you go into the philosophy of storing your receivers in a different
library in a different ASP.  Or do you just count on RAID and mirroring to

CYA?

We store all of our journals and receivers in a separate library that
starts with a # to encourage that library to be restored prior to any
other user data.  Restoring data prior to restoring journals is a big
no-no.  Now, if you journal stuff in QUSRSYS or QGPL you might want to put

them in there.  IBM now restores those "user" libraries before all other
user libraries.  I believe that Al had them made that change.
Our library name is #MXJRN for Mimix.  Everything is in one ASP.

Somewhere there's a mathematical formula for ideal journal size based on
the number of disk arms you have - no kidding.

Check this out:
STRSQL
F13=Services
Commitment control . . . . . .   *ALL
INSERT INTO ROBJERRY/TEST VALUES('Q')
F3=Exit   with no COMMIT
Changes waiting for COMMIT or ROLLBACK.  (Last chance to go back in and
commit them.)
DSPPFM ROBJERRY/TEST
"Q" is in there.
SIGNOFF
Sign back on.
DSPPFM ROBJERRY/TEST
Hey, where the heck is "Q"?
I never committed the transaction.  Therefore it get's backed out. Assumed

a system crash.
DSPJRN JRN(ROBJERRY/JERRY) FILE((ROBJERRY/TEST))
Code . . . . . . . . :   R  - Operation on specific record
Type . . . . . . . . :   DR - Record deleted for rollback

Another way of automatically backing out from the query from heck, huh?
Just signoff before a commit.
Although I think there's a limit to the number of uncommitted transactions

you can have.  Which, if you have a tendency to use a DELETE (a million
rows) FROM MYTABLE sql statement at year end you may have an issue.  You
can always open that up with no commitment control.  It's not like you
have to turn journalling off/on to do it.  That's an appropriate time for
CRTSQLRPGI ... COMMIT(*NONE).  Not because you're too stubborn to start
journalling your files.  Then again, a WITH NC on the DELETE is more
granular.

See the CL commands COMMIT and ROLLBACK.  Gee, put that in your default
error trapping?
You can also COMMIT with RPG and SQL
 > INSERT INTO ROBJERRY/TEST VALUES('Q')
  1 rows inserted in TEST in ROBJERRY.
 > COMMIT
  Commit completed.
F3

SIGNOFF
sign back on
DSPPFM ROBJERRY/TEST
"Q" is in there.

[report a broken link by clicking here]