× 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

Hi Bill
I am probably completely wrong on this
Unless I completely missed it, but where is the key on the create table
The RPG is expecting one
I know the DDS has defined one, but the table wasn't created by the DDS, but the SQL (no key is mentioned)
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: 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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
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.