Hi Bill,
I think it has to be made clear once more that there is NOT something like a "table with commit *none" (which you keep on repeating so I presume you don't understand this concept).
Commitment control relates to the mode in which you execute statements, not how tables are defined.
So when in interactive SQL, you can set commitment control to *NONE (F13, option 1)... the table you create at that time is in no point different from running the same CREATE TABLE statement with commitment control *CHG (or any other setting). The result is just a physical file, nothing more, nothing less.
As far as RPG programs are concernd, you've several options in controlling which commitment control mode will be used; either the COMMIT parameter during the CRTSQLRPGI statement, either via "Exec SQL Set Option Commit = *None;" or via specifying the "With NC" on each individual SQL statement.
Kind regards,
Paul
________________________________________
From: rpg400-l-bounces@xxxxxxxxxxxx [rpg400-l-bounces@xxxxxxxxxxxx] on behalf of Bill Hopkins [bhopkins@xxxxxxxx]
Sent: Thursday, June 09, 2011 14:55
To: RPG programming on the IBM i / System i
Subject: RE: SQL Table and RPGLE Error message
No SQL at all except for the SQL Created table with commit *none.
No Journaling.
No file opens when program called not user controlled.
default activation group
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Morgan, Paul
Sent: Wednesday, June 08, 2011 5:09 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Table and RPGLE Error message
Bill,
Any SQL being run in the CLLE programs by RUNSQLSTM or something
similar?
Is the file currently journaled? If it's not then asking about any
prior commitment control is moot.
When you get the error message is the file already opened in the job?
What activation group are these programs running under? Are they all in
the default activation group or is it a mix of default and QILE?
Thanks,
Paul Morgan
Principal Programmer Analyst
IT Supply Chain/Replenishment
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Bill Hopkins
Sent: Wednesday, June 08, 2011 4:26 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Table and RPGLE Error message
How this program runs ......
CLLE running all the time with delay waits.....
Calls RPGLE program
Calls CLLE
Calls RPGLE
Calls RPGLE program
Calls RPGLE - program that fails
Back to CLLE
No SQLRPGLEs and no other SQL created tables
That's why the message is strange to me.
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Morgan, Paul
Sent: Wednesday, June 08, 2011 4:08 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Table and RPGLE Error message
Bill,
No in answer to your questions.
Could this be an RPGLE program called from or after another program
using commitment control on this file? That CPF4326 makes mention of
the file already being under commitment control when this program
attempts to open the file. Maybe the prior transaction hasn't been
committed causing a problem when this program opens the file.
Have you possibly done a OVRDBF with OVRSCOPE(*JOB) before calling this
program? There is also a mention of *JOB open scope on the file after
the file has been opened with *ACTGRPDFN scope by a previous program
with commitment control.
Paul Morgan
Principal Programmer Analyst
IT Supply Chain/Replenishment
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Bill Hopkins
Sent: Wednesday, June 08, 2011 3:19 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Table and RPGLE Error message
So does that mean every RPGLE program that I want to use SQL created
tables with commit *none have to be changed to SQLRPGLE types? Even
though the programs will not be executing any SQL statements?
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Mark Walter
Sent: Wednesday, June 08, 2011 2:59 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Table and RPGLE Error message
Your other option is to create the SQLRPG program object with the
COMMIT(*NONE) option. The default is COMMIT(*CHG). If you don't use
commitment control on your system, change the command default to
COMMIT(*NONE).
Mark Walter
Business to Business Data Integration Specialist
Certified IBM System i Specialist
Paragon Consulting Services, Inc.
mwalter@xxxxxxxxxxxxxxx
717-764-7909 ext. 126
________________________________________
From: rpg400-l-bounces@xxxxxxxxxxxx [rpg400-l-bounces@xxxxxxxxxxxx] On
Behalf Of Bill Hopkins [bhopkins@xxxxxxxx]
Sent: Wednesday, June 08, 2011 2:49 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Table and RPGLE Error message
Alan
Can you explain why this needs to be in the program "set option commit
*none" when the SQL Table I'm opening was already define with "commit
*none"
Thanks
Bill
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Wednesday, June 08, 2011 2:22 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: RE: SQL Table and RPGLE Error message
Hi Bill
Do you have the following in your RPGLE program
/free
exec sql Set Option Commit = *None;
/end-free
If not, make sure to insert this in the early (low numbered ) c-specs
Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Bill Hopkins
Sent: Wednesday, June 08, 2011 11:41 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: SQL Table and RPGLE Error message
Hi All,
I have an SQL Table created with "commit *none".
When the RPGLE that uses the table is called it fails when it tries to
open the SQL Table.
Message given CPF4326- "Commitment definition not valid for open of
"
I understand getting this message with poor activation group planning,
bad scope, etc. but never had this issue.
V5R4
Thanks
Bill
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/rpg400-l.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/rpg400-l.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/rpg400-l.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/rpg400-l.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/rpg400-l.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/rpg400-l.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/rpg400-l.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/rpg400-l.
midrange.com
