Rob,
Thanks a million. Went through all of the steps. By the way, I used the SQL step to create the table. Slight typo there; the table is named TESTTWO but everywhere else it is simply called TEST. Minor thing, though. The time you spent on this was not trivial, I know.
I think I'm getting a better hold on journaling, but wanted one clarification. Some tables, such as a Shipping Order, are by nature volatile by design. I.e., an order is entered, printed, and picked. Then the order is invoiced, at which time it is deleted from the Shipping Order table. So records are being added and deleted willy-nilly through out the day. It looks like the APYJRNCHG command would handle this if, say, an order was both entered and deleted within the range. I guess that the journal would add the order back and, later, delete it. Right?
Again, thanks.
Jerry C. Adams
IBM System i Programmer/Analyst
--
B&W Wholesale
office: 615-995-7024
email: jerry@xxxxxxxxxxxxxxx
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Thursday, August 20, 2009 3:49 PM
To: Midrange Systems Technical Discussion
Subject: Journal Tutorial (was: Modernizing applications (was: Explaining single level store to non ipeople))
Jerry,
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.
Rob Berendt
midrange.com
