RE: [External] Re: RPGLE native I/O and SQL Tables/Views/Indexes -- RPG400-L

<begin irrelevant note>
JD Edwards EnterpriseOne recognizes DB2 tables and indexes (Logical files) if created in the EnterpriseOne product.
E1 business views created in the E1 Solution Explorer tool set over DB2 tables are proprietary to E1, are NOT IBMi objects, and therefore invisible, hidden from native products like SQL, RPG, COBOL, etc.

/end irrelevant note>

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Alan Campin
Sent: Monday, September 27, 2021 12:26 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: [External] Re: RPGLE native I/O and SQL Tables/Views/Indexes

[WARNING]: External Email

And, now days, that should be an Identity Key (with rare exception) with a unique key as candidate keys. All table referencing that table should be using the identity key to join the tables. (My opinion only). This ought to set off a firestorm.

Example.

Master table.

CREATE OR REPLACE TABLE SYSTEM_DEVELOPER_DEBUG_LIST_MASTER FOR SYSTEM NAME XVDBLM (
DEBUG_IDENTITY_MASTER FOR COLUMN DMKEYM INTEGER GENERATED ALWAYS AS
IDENTITY (
START WITH 1 INCREMENT BY 1
NO MINVALUE NO MAXVALUE
NO CYCLE NO ORDER
CACHE 20 ),

DEVELOPER_USER_PROFILE FOR COLUMN DMDEVUSER VARCHAR(10) CCSID 37 NOT NULL , .... Other fields

CONSTRAINT Q_XVDBLM_DMKEY_PRIME PRIMARY KEY( DEBUG_IDENTITY_MASTER ) )

RCDFMT RDBLM;

ALTER TABLE SYSTEM_DEVELOPER_DEBUG_LIST_MASTER

ADD CONSTRAINT Q_XVDBLM_DMDEVUSER_00001

UNIQUE( DEVELOPER_USER_PROFILE ) ;

Detail table.

CREATE OR REPLACE TABLE SYSTEM_DEVELOPER_DEBUG_LIST_LIBRARIES FOR SYSTEM NAME XVDBLL (
DEBUG_IDENTITY_LIBRARIES FOR COLUMN DLKEYL INTEGER GENERATED ALWAYS
AS IDENTITY (
START WITH 1 INCREMENT BY 1
NO MINVALUE NO MAXVALUE
NO CYCLE NO ORDER
CACHE 20 ),

DEVELOPER_IDENTITY FOR COLUMN DLDEVID INTEGER ,

LOAD_SEQUENCE FOR COLUMN DLLOADSEQ SMALLINT,
.... other fields.
CONSTRAINT Q_XVDBLL_DLKEY_PRIME PRIMARY KEY( DEBUG_IDENTITY_LIBRARIES ) )

RCDFMT RDBLL;

ALTER TABLE SYSTEM_DEVELOPER_DEBUG_LIST_LIBRARIES
ADD CONSTRAINT Q_XVDBLL_DLDEVID_DLLIBRARY_00001
UNIQUE( DEVELOPER_IDENTITY, LIBRARY_NAME ) ;

ALTER TABLE SYSTEM_DEVELOPER_DEBUG_LIST_LIBRARIES
ADD CONSTRAINT Q_XVDBLL_DLDEVID_DLLIBRARY_00002
UNIQUE( DEVELOPER_IDENTITY, LOAD_SEQUENCE) ;

ALTER TABLE SYSTEM_DEVELOPER_DEBUG_LIST_LIBRARIES
ADD CONSTRAINT Q_XVDBLL_DLDEVID_00001
FOREIGN KEY( DEVELOPER_IDENTITY )
REFERENCES SYSTEM_DEVELOPER_DEBUG_LIST_MASTER ( DEBUG_IDENTITY_MASTER ) ON DELETE NO ACTION ON UPDATE NO ACTION ;

On Mon, Sep 27, 2021 at 9:44 AM Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

I really hope your standards for SQL Tables have a primary key...

Just like your standards for PF should include a unique key.

Charles

On Mon, Sep 27, 2021 at 10:41 AM Brian Parkins
<goodprophet.bp@xxxxxxxxx>
wrote:

A Table = non-keyed, Physical File
A View = non-keyed, single-format, Logical File An Index = a keyed,
non-join, single format, Logical File

RPG IV operations can be used in exactly the same manner as with the
"native" (DDS + CRTxx) defined objects.

To complicate matters, don't forget you CAN use CHAIN, SETLL etc. on
a View - if processing by relative record number, (i.e. non-keyed
processing).

HTH,
Brian.

On 27/09/2021 17:23, Alan Campin wrote:

it's not that simple. A physical file created with DDS and a table

created

with SQL are the same thing to RPG. If the table has an index, you
can

do

operations like Chain and Setll just like a DDS defined table.

Views are more complex. Views don't have indexes so the only thing
that

you

could do with a view in an RPG program is read it sequentially or
write

to

it.

An SQL index looks like a logical file to an RPG program so you
could

use

it like a logical file. That's not normally how you would do it.
You

would

define an SQL index and then define a logical and it should share
the access path (Index) and it is just another logical to RPG.

On Mon, Sep 27, 2021 at 9:11 AM K Crawford <kscx3ksc@xxxxxxxxx> wrote:

I am trying to help someone and clarify for me the following. I
don't think I am typing in my searches correctly. What I am looking for is:
What native RPGLE I/O can you do over a SQL Table?
What native RPGLE I/O can you do over a SQL View?
What native RPGLE I/O can you do over a SQL Index?
A chart something like:
I/O Table View Index
Read Yes Yes Yes
Reade No No Yes
Readp x x x
Readpe x x x
Chain x x x
SetLL x x x
SetGT x x x
Delete x x x
Write x x x

--
KCrawford

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post
a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fli
sts.midrange.com%2Fmailman%2Flistinfo%2Frpg400-l&data=04%7C01%7C
elehti%40quiktrip.com%7Cba3520d4c6df4252b8b408d981dbf820%7Cbe902cb3a
2b74bfd86442264fe46c43d%7C0%7C0%7C637683604056028083%7CUnknown%7CTWF
pbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI
6Mn0%3D%7C1000&sdata=IWodDycE4fSrMTpfP1k6mq9SqkC0gPXmXO%2B7oBJSt
%2FE%3D&reserved=0 or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Frpg400-l&data=04%7C01%7Celehti%40quiktrip.com%7Cba3520d4c6df4252b8b408d981dbf820%7Cbe902cb3a2b74bfd86442264fe46c43d%7C0%7C0%7C637683604056038043%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=ei46DH3CJFfchnOII%2B7J4aask3NsKlSisFj6ZvIEGrE%3D&reserved=0.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link:
https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fam
azon.midrange.com%2F&data=04%7C01%7Celehti%40quiktrip.com%7Cba35
20d4c6df4252b8b408d981dbf820%7Cbe902cb3a2b74bfd86442264fe46c43d%7C0%
7C0%7C637683604056038043%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDA
iLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=h1E
%2FIb7%2FdUxe3SAQzycg%2BpNvHNYp4FqZuv81Uxo5egY%3D&reserved=0

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Flist
s.midrange.com%2Fmailman%2Flistinfo%2Frpg400-l&data=04%7C01%7Celeh
ti%40quiktrip.com%7Cba3520d4c6df4252b8b408d981dbf820%7Cbe902cb3a2b74bf
d86442264fe46c43d%7C0%7C0%7C637683604056038043%7CUnknown%7CTWFpbGZsb3d
8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C
1000&sdata=WbmCsTuyZL68Vuw2lPBbLvJHnWUOrXRTvU61qv%2BsyUE%3D&re
served=0 or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Frpg400-l&data=04%7C01%7Celehti%40quiktrip.com%7Cba3520d4c6df4252b8b408d981dbf820%7Cbe902cb3a2b74bfd86442264fe46c43d%7C0%7C0%7C637683604056038043%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=ei46DH3CJFfchnOII%2B7J4aask3NsKlSisFj6ZvIEGrE%3D&reserved=0.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Famaz
on.midrange.com%2F&data=04%7C01%7Celehti%40quiktrip.com%7Cba3520d4
c6df4252b8b408d981dbf820%7Cbe902cb3a2b74bfd86442264fe46c43d%7C0%7C0%7C
637683604056038043%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjo
iV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=h1E%2FIb7%2Fd
Uxe3SAQzycg%2BpNvHNYp4FqZuv81Uxo5egY%3D&reserved=0

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Frpg400-l&data=04%7C01%7Celehti%40quiktrip.com%7Cba3520d4c6df4252b8b408d981dbf820%7Cbe902cb3a2b74bfd86442264fe46c43d%7C0%7C0%7C637683604056038043%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=WbmCsTuyZL68Vuw2lPBbLvJHnWUOrXRTvU61qv%2BsyUE%3D&reserved=0
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Frpg400-l&data=04%7C01%7Celehti%40quiktrip.com%7Cba3520d4c6df4252b8b408d981dbf820%7Cbe902cb3a2b74bfd86442264fe46c43d%7C0%7C0%7C637683604056038043%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=ei46DH3CJFfchnOII%2B7J4aask3NsKlSisFj6ZvIEGrE%3D&reserved=0.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com%2F&data=04%7C01%7Celehti%40quiktrip.com%7Cba3520d4c6df4252b8b408d981dbf820%7Cbe902cb3a2b74bfd86442264fe46c43d%7C0%7C0%7C637683604056038043%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=h1E%2FIb7%2FdUxe3SAQzycg%2BpNvHNYp4FqZuv81Uxo5egY%3D&reserved=0