× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. June 2011

RE: SQL Table and RPGLE Error message

Sorry Paul for not being clear.... but last week it was a madhouse here.

Statement should have stated


"No SQL at all except for the SQL Created Table with CREATE TRIGGER with
commit *none."

I had posted this earlier.....

CREATE TRIGGER ILD009P_BEFORE_TRIGGER_OPCO BEFORE INSERT ON ILD009P
REFERENCING NEW AS NEW_ROW FOR EACH ROW MODE DB2ROW SET OPTION
ALWBLK = *ALLREAD , ALWCPYDTA = *YES , COMMIT = *NONE , DECRESULT =
(31, 31, 00) , DFTRDBCOL = *NONE , DLYPRP = *NO , DYNDFTCOL = *NO ,
DYNUSRPRF = *USER , SRTSEQ = *HEX BEGIN SET NEW_ROW . IPOPCO = (
SELECT FMCMP02A . C02VOD FROM FMCMP02


Along with the Create Table Statement, etc.


And I also stated that I found out that ......

Journaling IS USED for MIMIX but commit *none on Create trigger.

The program fails(CPF4326- "Commitment definition not valid for open
of ") when calling the RPGLE program that opens this file not a SQLRPGLE
program which I have stated before. No SQL is used except for the Create
Table.

Thanks for all your help and I PRESUMED this site was for sharing and
learning. :)

Also note I personally handled over a 150 helpdesk tickets last week
along with writing two sub-file programs and rewrote a POS program(1000
lines plus when you consider that modules/service programs changes) so
my brain might not have been reaching my fingers when typing questions
to list then. But those three things I also did last week did past QA on
Friday with no issues.

Thanks Again for all your help...
Bill


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Paul Nicolay
Sent: Friday, June 10, 2011 3:13 AM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Table and RPGLE Error message

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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.