Re: Reality check on PFs created as SQL TABLEs

James (JHHL) .... for even more SQL, you could also reference this link:

https://ibmireference.blogspot.com/p/ibm-i-sql-other-sql.html



Respectfully,
Michael Mayer
IBM i on Power System Admin
INFOR LN Jr ERP Admin
ERMCO-ECI
2225 Industrial Road
Dyersburg, Tennessee 38024
Cell, Office and Text Messages: 518.641.8906
www.ermco-eci.com<https://www.ermco-eci.com>
www.ermcopower.com<https://ermcopower.com>

ibmireference.blogspot.com<http://ibmireference.blogspot.com>
IBM 2024/2025 Advocate, Contributor, Influencer and IBM i Ready Badges.
www.credly.com/users/michael-mayer.586da683/badges#credly<http://www.credly.com/users/michael-mayer.586da683/badges#credly>


[cid:d6a5c987-0baa-4a23-a0f1-83a0a317b5fb]


________________________________


message: 5
date: Wed, 10 Jun 2026 11:11:51 -0700
from: "James H. H. Lampert via MIDRANGE-L"
<midrange-l@xxxxxxxxxxxxxxxxxx>
subject: Re: Reality check on PFs created as SQL TABLEs

On 6/10/26 10:43 AM, Daniel Gross wrote:

Yes - primary key constraint is the way - the primary key "index" is
embedded in the table/PF object.

Thanks. That worked, and the syntax is correct.

I could have sworn that you couldn't have keyed SQL tables . . . and I
just found the exact same constraint clause in *SQL for Dummies,* after
having created a test table using the clause.

I don't suppose it's possible to create an SQL TABLE with more than one
inherent keyfield, the way you can with a DDS-defined PF, is there?

On 6/10/26 10:51 AM, Dan Bale wrote:

If I'm not sure how SQL "replicates" a DDS feature, I'll use the
Generate SQL function in Run SQL Scripts to generate the converted
SQL source for DDS PF & LF objects.

I tried an experiment. With PF "FOO" generated from the following DDS:

A R FOOBAR
A FIELD1 10A
A FIELD2 10A
A K FIELD1
A K FIELD2

I tried "Generate SQL," and got:

CREATE TABLE MERCURY.FOO (
-- SQL150B 10 REUSEDLT(*NO) in table FOO in MERCURY ignored.
-- SQL1506 30 Key or attribute for FOO in MERCURY ignored.
FIELD1 CHAR(10) CCSID 37 NOT NULL DEFAULT '' ,
FIELD2 CHAR(10) CCSID 37 NOT NULL DEFAULT '' )

RCDFMT FOOBAR ;

So it looks like "anything DDS PFs & LFs support, SQL does as well" is
indeed an oversimplification.

Then again, Theory and practice are the same in theory, but not
necessarily in practice. (Paraphrased from a maxim falsely attributed to
Yogi Berra, among others, that may have originated with Jan van de
Snepscheut.)

--
JHHL