Re: Why file journaling requirement for SQL operations?

Charles, I think what you describe is what I was trying to remember - there is a level of journal caching that takes place with commitment control - it's not as extensive, IIRC, as option 42 of the OS, but still helps performance. Didn't it include that you just turn on CC, maybe without a specific file named? No one quote me, please, if I got this wrong, it's been years. I believe an IBMer named Turner or some such wrote something up on this.

Cheers
Vern

On 5/14/2021 8:49 AM, Charles Wilt wrote:

Yep, pay no attention to the name...

It's very useful anytime you've turned on journaling but are NOT using
commitment control.

If you are using commitment control, it is not necessary.

Charles

On Fri, May 14, 2021 at 5:11 AM Rob Berendt <rob@xxxxxxxxx> wrote:

I was just thinking of saying the something along the same line.
I wanted to use journaling for years. But my manager kept putting it off.
Then we started using a replication H/A package which required journaling.
Now they all love it. You definitely want to install this though if you do
a lot of jounaling:
Resource
ID Option Feature Description
5770SS1 42 5117 HA Journal Performance


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of x y
Sent: Thursday, May 13, 2021 8:17 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Why file journaling requirement for SQL operations?

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.


The answer to the original question is this: it's the way SQL works.

SQL, and all real databases, have "ACID" properties: atomicity (all
individual database operations complete or the transaction fails),
consistency (the data is processed only in the allowed manner), isolation
(the transaction won't be affected by any other processes or transactions),
and durability (at commitment, the transaction is made permanent). ACID
recognizes the concept of a "transaction", a term describing a series of
multiple database operations executed in the course of completing a task.
First utilized in IBM's IMS database, these properties provide protection
against database/program/OS failures, or power outages, occuring in the
middle of a transaction.

Your application is waiting for a record lock to release and the user is
impatient. So, OF COURSE they cancel the job and then half the updates are
done, half the history records are written, and so on. Without journaling
and commitment control, you're going to have a busy night cleaning up the
mess.

Suppose you have a system crash right in the middle of your heaviest order
entry time--how much rebuilding--or restoring from backup--are you facing?
When the system comes back up, commitment control will clean up the mess if
all your tables are properly journaled.

I journal master files (customers master, city/state/ZIP master) and
transactions file (orders, order history). I don't journal work files
(anything that can be easily recreated). I have a daemon reading the
journal receiver and creating a before-and-after inquiry program showing
who changed what; that program has solved thousands of problems *without
having to add code to one user program*. This benefit makes up for the
very small performance impact and disk space required. The auditors had to
change their shorts, they were so happy.

Long ago, a customer programmer deleted 100,000 AR records. I read the
journal receiver and had the data recovered in less than an hour. Thank
you, journaling! No magic, just using the tools we have available.


On Thu, May 13, 2021 at 12:02 PM <dr2@xxxxxxxx> wrote:

OK, out of curiosity, why is it a requirement that files be journaled
before you can do programmatic SQL operations against them?

Ran into CPF4328 this morning and never heard of this requirement
before!

/DR2
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com