Re: [EXTERNAL] Why file journaling requirement for SQL operations?

There's another spot that might bite - the default for the COMMIT parameter on the CRTSQLRPGI command is *chg, which means if you do updates or deletes, the file has to be journaled -

     **** that might be the cause of your batch job issue, since it worked interactively. ****

You can use "with nc" on each embedded statement, or set commit on the CRT* command, or in the program with "set option...", IIRC.

RUNSQL also assumes *CHG for its COMMIT parameter, so I usually use "with nc" instead of setting the parameter to *NONE - just cleaner in code.

If your statements are not update or delete, commit doesn't matter as to journaling. So when our programs only do reads with SQL, we don't need to change the parameter on the CRT*. I still do it now, just in case, however.

Cheers
Vern

On 5/13/2021 2:45 PM, dr2@xxxxxxxx wrote:

Rob, Alan,

thanks...I have some RTFM'ing to do it would appear :)

On 2021-05-13 15:23, Rob Berendt wrote:

But getting back to Alan's question: What did you have in your commit definition? Was it the same program? Or was one in STRSQL and the other in RUNSQLSTM?

Setting commitment control on individual statements:
RTM: In the manual you will see a commitment clause. You are probably looking for WITH NC as in with no commitment. Sample: Delete from mytable where recid='Z' with nc

Setting commitment control on STRSQL:
F13=Services, 1. Change session attributes, Commitment control . . . . . . *NONE

Setting commitment control on RUNSQLSTM:
RUNSQLSTM COMMIT(*NONE)

Setting commitment control in Run SQL Scripts:
Edit, JDBC Configurations, Isolation level

Setting commitment control in embedded sql:
See Alan's examples.

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 dr2@xxxxxxxx
Sent: Thursday, May 13, 2021 3:12 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: [EXTERNAL] 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.

Alan,

It was actually a simple: DELETE FROM WHERE to a work file....nothing
special or complex...

It worked fine interactively, but evidently evidently there's different
rules for batch programs that I wasn't aware of....

On 2021-05-13 15:08, Alan Shore via MIDRANGE-L wrote:

How are you setting your SQL commit option?

These are what we normally set in embedded SQL
Exec Sql Set Option Commit=*None, CLOSQLCSR=*ENDMOD, DECMPT=*PERIOD;
Exec Sql Set Option DATFMT = *YMD;

Alan Shore
Solutions Architect
IT Supply Chain Execution

[cid:image001.png@01D74809.C433BEB0]

60 Orville Drive
Bohemia, NY 11716
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
E-mail : ASHORE@xxxxxxxxxxxxxxxxxxxx

'If you're going through hell, keep going.'
Winston Churchill

From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of dr2@xxxxxxxx
Sent: Thursday, May 13, 2021 3:02 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] Why file journaling requirement for SQL operations?

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<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l<https://lists.midrange.com/mailman/listinfo/midrange-l>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l<https://archive.midrange.com/midrange-l>.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com<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