1. SQL Tables do not support keys (only Primary and unique Key constraints,
that means non-unique keys on tables are not supported. Non-unique keys must
be created as Indexes
2. there are some architectonical differences. When writing in a DDS
described table the data is not checked (that's why you can get for examples
*Blanks in numeric columns). Data is not checked before reading from the DDS
described table. When writing into SQL tables the data is checked, i.e. you
cannot write invalid data to an SQL table, the write will fail. Since there
are only valid data in SQL tables, there is no check when reading from SQL
tables. That results in slower writes and updates into SQL Tables but faster
reads ... and now think about how many reads and how many writes you have!
80% read / 20% writes?!
3. Keywords on columns (such as DATFMT or EDTCODE) are not supported for SQL
Tables. The date format is the job format and other (numeric) values are not
formatted.
4. DDS described tables are per Default created with RESUSEDLT *NO and a
maximum number of (10000) rows, while SQL Tables are created with REUSEDLT
*YES and *NOMAX for the number of rows.
--> these information can be changed by a CHGPF (even for SQL Tables)
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
Database and Software Architect
IBM Champion since 2020
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience ? everything else is only information!" (Albert
Einstein)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of James
H. H. Lampert via MIDRANGE-L
Sent: Wednesday, 10 June 2026 19:23
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: James H. H. Lampert <jamesl@xxxxxxxxxxxxxxxxx>
Subject: Reality check on PFs created as SQL TABLEs
Just to be sure:
There isn't some oddball extension to SQL that I'm not aware of, such that
you can create a keyed PF via an SQL CREATE TABLE statement, right?
--
JHHL
(I just experienced what I'd thought was a weird asymmetry when attempting
to a LF as an SQL INDEX with a descending key; it turned out that I'd
misspelled "DESC," and SQL was treating "DSC" as a field rename. You learn
something new every day. This has been a fun week so far.)
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
midrange.com
