No embedded SQL at all in this job stream...
Not seeing how trigger would cause the CPF4326 - "Commitment definition
not valid for open of
."
One thing I notice about the message it does not include variable
info... IE file name?
Side Note response...First steps in bring our company to the real world.
Taking all physicals files-DDS and creating them as SQL Created tables.
Indexes next on the list. But this company has mixed code.. IE Old RPG
from late 80's - left side indicators :) with newer /Free blended in.
Can only work with what I got :)
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Schutte, Michael D
Sent: Thursday, June 09, 2011 9:52 AM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Table and RPGLE Error message
Ok, I see now... the table has been built with DDL outside of the RPGLE
program. That does mean there is NO sql in the RPGLE program even
though you are using a logical built of the SQL created table. I've
never done any triggers but could this be the culprit?
As a side note, you started the table with DDL, why not use DDL to
create the indexes too? You can read from indexes when you define them
in the FSPECS just like a logical file. With that being said, I'll stay
out of this cuz I have no clue. Sorry.
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Bill Hopkins
Sent: Thursday, June 09, 2011 9:35 AM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Table and RPGLE Error message
SQLTBLSRC
CREATE TABLE ILD009P (
IPPLN NUMERIC(3, 0) NOT NULL DEFAULT 0 ,
IPDSC CHAR(25) CCSID 37 NOT NULL DEFAULT '' ,
IPSORD CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
IPBASE CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
IPDLV DECIMAL(7, 3) NOT NULL DEFAULT 0 ,
IPDLVC CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
IPCRDA CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
IPGLA NUMERIC(8, 0) NOT NULL DEFAULT 0 ,
IPGLD NUMERIC(2, 0) NOT NULL DEFAULT 0 ,
IP1ST CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
IPPOS CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
IPRTE CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
IPITEM CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
IPPRTI CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
IPPRTP CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
IPPRTS CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
IPSLS CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
IPORTY CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
IPPPRC CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
IPEXIT CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
IPCRTU CHAR(10) CCSID 37 NOT NULL DEFAULT '' ,
IPCRTD DATE NOT NULL DEFAULT '0001-01-01' ,
IPCRTT TIME NOT NULL DEFAULT CURRENT_TIME ,
IPCHGU CHAR(10) CCSID 37 NOT NULL DEFAULT '' ,
IPCHGD DATE NOT NULL DEFAULT '0001-01-01' ,
IPCHGT TIME NOT NULL DEFAULT CURRENT_TIME ,
IPCORD CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
IPARC CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
IPRGLA NUMERIC(8, 0) NOT NULL DEFAULT 0 ,
IPRGLD NUMERIC(2, 0) NOT NULL DEFAULT 0 ,
IPTEMPLT NUMERIC(3, 0) NOT NULL DEFAULT 0 ,
IPCRDAP CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
IPACCREB CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
IPSLSCOM CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
IPFREE CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
IPBBCD CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
IPTPC NUMERIC(5, 0) NOT NULL DEFAULT 0 ,
IPOPCO NUMERIC(3, 0) NOT NULL DEFAULT 0,
IPREV$ CHAR(1) CCSID 37 NOT NULL DEFAULT '',
IPRYAL CHAR(1) CCSID 37 NOT NULL DEFAULT '',
IPSKPI CHAR(1) CCSID 37 NOT NULL DEFAULT '')
RCDFMT ILPREC ;
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
WHERE FMCMP02A . C02KWD = 'OPCO#' ) ; END ;
LABEL ON TABLE ILD009P
IS 'Invoice level plan master file' ;
LABEL ON COLUMN ILD009P
( IPPLN IS 'Plan Nbr' ,
IPDSC IS 'Description' ,
IPSORD IS 'S=Surcharge D=Discount' ,
IPBASE IS 'Volume C=cases D=$' ,
IPDLV IS 'Delvry Day Discount' ,
IPDLVC IS 'D=$ P=%' ,
IPCRDA IS 'P=Part F=Full N=None' ,
IPGLA IS 'G/L Account Number' ,
IPGLD IS 'G/L Dept Number' ,
IP1ST IS '1st Invoice Only' ,
IPPOS IS 'Omit POS Y/_' ,
IPRTE IS 'Omit Spc Rte' ,
IPITEM IS 'Item Lvl' ,
IPPRTI IS 'Prnt Item Amt' ,
IPPRTP IS 'Add to Price' ,
IPPRTS IS 'Prnt Plan Smry' ,
IPSLS IS 'Incl in Sls' ,
IPORTY IS 'Incl Inventory Type' ,
IPPPRC IS 'Print Price on Ivoice' ,
IPEXIT IS 'Exclude Select Items' ,
IPCRTU IS 'Create User' ,
IPCRTD IS 'Create Date' ,
IPCRTT IS 'Create Time' ,
IPCHGU IS 'Change User' ,
IPCHGD IS 'Change Date' ,
IPCHGT IS 'Change Time' ,
IPCORD IS 'Total Cust Orders Delv/Rte' ,
IPARC IS 'Active Flag' ,
IPRGLA IS 'Rebate G/L Acct' ,
IPRGLD IS 'Rebate G/L Dept' ,
IPTEMPLT IS 'Template#' ,
IPCRDAP IS 'Omit Price Adj Credit' ,
IPACCREB IS 'Accrued Rebate Flag' ,
IPSLSCOM IS 'Exclude Commission' ,
IPFREE IS 'Free Item Plan' ,
IPBBCD IS 'Billback Code' ,
IPTPC IS 'Invoice Plan Tax Product Class' ,
IPOPCO IS 'Opco Number' ,
IPREV$ IS 'Auto reversal of $ in A/R' ,
IPRYAL IS 'Royality flag' ,
IPSKPI IS 'Skip 1st inv of the week');
LABEL ON COLUMN ILD009P
( IPPLN TEXT IS 'Plan Number' ,
IPDSC TEXT IS 'Plan Description' ,
IPSORD TEXT IS 'Surcharge or Discount' ,
IPBASE TEXT IS 'Plan Volume C=Cases D=$' ,
IPDLV TEXT IS 'Delivery Day Discount' ,
IPDLVC TEXT IS 'Delivery Day D=$,P=%' ,
IPCRDA TEXT IS 'Credit Action Part/Full/None' ,
IPGLA TEXT IS 'G/L Account Number' ,
IPGLD TEXT IS 'G/L Department' ,
IP1ST TEXT IS 'Apply to 1st invoice only' ,
IPPOS TEXT IS 'Omit POS Orders (Y/_' ,
IPRTE TEXT IS 'Omit Special Routes (Y/_' ,
IPITEM TEXT IS 'Item Level Options (Y/_' ,
IPPRTI TEXT IS 'Print Item amount (Y/_' ,
IPPRTP TEXT IS 'Add amount to printed price (Y/_' ,
IPPRTS TEXT IS 'Print Plan Summary (Y/_' ,
IPSLS TEXT IS 'Include in Sales (Y/_' ,
IPORTY TEXT IS 'Include Inventory Type' ,
IPPPRC TEXT IS 'Print price on Invoice N/_' ,
IPEXIT TEXT IS 'Exclude Selected Items Y/_' ,
IPCRTU TEXT IS 'Created by User' ,
IPCRTD TEXT IS 'Creation Date' ,
IPCRTT TEXT IS 'Creation Time' ,
IPCHGU TEXT IS 'Changed by User' ,
IPCHGD TEXT IS 'Change Date' ,
IPCHGT TEXT IS 'Change Time' ,
IPCORD TEXT IS 'Total Cust Orders Delv/Rte' ,
IPARC TEXT IS 'Active Flag' ,
IPRGLA TEXT IS 'Rebate G/L Account Number' ,
IPRGLD TEXT IS 'Rebate G/L Dept Number' ,
IPTEMPLT TEXT IS 'Template #' ,
IPCRDAP TEXT IS 'Omit Price Adj from Credit' ,
IPACCREB TEXT IS 'Accrued Rebate Flag' ,
IPSLSCOM TEXT IS 'Exclude from Commission' ,
IPFREE TEXT IS 'Does this IP give free items' ,
IPBBCD TEXT IS 'Billback code' ,
IPTPC TEXT IS 'Invoice Plan Tax Product Class' ,
IPOPCO TEXT IS 'Opco Number' ,
IPREV$ TEXT IS 'Auto reversal of $ in A/R' ,
IPRYAL TEXT IS 'Royality flag' ,
IPSKPI TEXT IS 'Skip 1st inv of week' );
************************************************************
QDDSSRC
A R ILPREC PFILE(ILD009P)
A K IPPLN
QRPGLESRC
FIld009L1 IF E K DISK
Gives message CPF4326 on call of RPGLE from RPGLE and no other SQL
executed.
Thanks All for help
Bill
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Schutte, Michael D
Sent: Thursday, June 09, 2011 9:03 AM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Table and RPGLE Error message
I've been loosely following this thread and now I'm confused, there's no
SQL except for a Create table? If there's a create table statement
being executed, then you have SQL being performed. In order to execute
the create table statement, the RPGLE program must be a SQLRPGLE
program. Maybe I missed it, where is this table being created?
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Bill Hopkins
Sent: Thursday, June 09, 2011 8:55 AM
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.
________________________________
Notice from Bob Evans Farms, Inc: This e-mail message, including any
attachments, may contain confidential information that is intended only
for the person or entity to which it is addressed. Any unauthorized
review, use, disclosure or distribution is strictly prohibited. If you
are not the intended recipient, please contact the sender by reply
e-mail and destroy all copies of the original message and any
attachments.
--
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.
________________________________
Notice from Bob Evans Farms, Inc: This e-mail message, including any
attachments, may contain confidential information that is intended only
for the person or entity to which it is addressed. Any unauthorized
review, use, disclosure or distribution is strictly prohibited. If you
are not the intended recipient, please contact the sender by reply
e-mail and destroy all copies of the original message and any
attachments.
midrange.com
