Re: Can I use DDS to create an SQL table name -- MIDRANGE-L

Jon, I think SQL does the V on W - the performance benefit presented by Dan Cruikshank is that there are 25 times reads than writes, say - so eliminating the validation on read makes for better performance. I'm not convinced it matters THAT much in real world, but I can discuss it over real data.

The V on W gives better data integrity as I understand it - "no V on R" is a performance thing, "V on W" is a data integrity thing - I think!!

Onwards
Vern

On 10/24/2014 4:16 PM, Jon Paris wrote:

But isn’t that “Validation on Write” Birgitta?

All I know is that when IBM started pushing the “on read” bit many of my clients were very confused and were certainly given the impression that it would assist their RPG programs.

Jon Paris

www.partner400.com
www.SystemiDeveloper.com

On Oct 24, 2014, at 5:12 PM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx> wrote:

Jon,

The "validation on read" does not mean in RPG or any other language, but
directly within the table.

Just try to copy invalid (packed) numeric data in a DDS described file with
CPYF *NOCHK. All records are inserted.
Create the same file/table with the same fileds/columns as the DDS described
file (you may use the GENERATE SQL-Statement in IBM i Navigator) and execute
the same CPYF *NOCHK against this table.
The copy will be stopped and the invalid data/record is not inserted.
*

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"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!"

-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jon
Paris
Gesendet: Friday, 24.10 2014 20:44
An: Midrange-L Midrange-l
Betreff: Re: Can I use DDS to create an SQL table name

I’ve never understood the “validation on read” claim. There is no
validation - if there was you would never get a decimal data error on the
field move that follows the basic read/chain. It would be stopped back at
the DB level.

The new query engine now works on just about all tables right? So there’s no
specific advantage there.

I’m with you Vern - there’s a lot of reason to use SQL. Far less to switch
to DDL.

Jon Paris

www.partner400.com
www.SystemiDeveloper.com

On Oct 24, 2014, at 2:35 PM, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
wrote:

I too have been thinking that I've not seen enough performance testing to

justify the claims - they all feel pretty theoretical right now. Lots of "it
depends".

The new query engine - yes, that clearly has advantages for many queries.

But faster reads because there is no validation there? Probably no big

deal for interactive work - long batch stuff, maybe.

I'm all for the new capabilities in SQL - and the DML can work against

both DDS-based files and DDL-based tables. At this time I'm inclined to say
that the benefits are more in using SQL as a language, not so much in
creating database objects.

OK, bring on the firearms, y'all - I'm fully protected!! I've survived

conversations in LinkedIn groups! And left!

Vern

On 10/24/2014 12:44 PM, rob@xxxxxxxxx wrote:

I think that the vastly improved data integrity and security of DDL
should be a much bigger factor than the improved key indexing. Chuck
has a point that he really wants to see the cold hard performance

comparisons.

Rob Berendt
-- IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600

Mail to: 2505 Dekko Drive Garrett, IN 46738 Ship to: Dock 108 6928N 400E
Kendallville, IN 46755 http://www.dekko.com From: Mike Cunningham
<mike.cunningham@xxxxxxx> To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxx> Date: 10/24/2014 01:18 PM Subject: RE: Can I use
DDS to create an SQL table name Sent by: "MIDRANGE-L"
<midrange-l-bounces@xxxxxxxxxxxx> Chuck, what about access to the improved
key indexing that an SQL defined table gives you? And then there is always
the future to consider. My crystal ball says at some point IBM will pull the
plug completely on DDS for database definitions just like it has pulled the
plug on SEU. To their credit, they have not gone the path of some other
companies who have said change your ways or don't upgrade, they have given
us many years to make the switch. So I don't see the demise of DDS anytime
soon but 5 years from now I'm not so sure. -----Original Message----- From:
MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Friday, October 24, 2014 1:03 PM To: midrange-l@xxxxxxxxxxxx Subject:
Re: Can I use DDS to create an SQL table name On 24-Oct-2014 10:38 -0500,
Darryl Freinkel wrote:

If you use iNav, you can convert all DDS to DDL. That would help
you modernise quickly and take advantage of the bigger buffer sizes
which gives better performance.

Of course, "bigger" is not always better, with regard to the
performance of every [type of] application and\or system [environment].
To be clear, "bigger buffer sizes" could just as well give worse
performance; the likelihood increases that the performance effects
could be worse, if the applications using the changed file(s) had not
previously or simultaneously been changed from RLA to SQL DML.

I will take this opportunity_to remind_ that conversion from DDS
to DDL is*not* in any way "modernizing" the database. A change that
is not required, is merely change [for the sake of change]; such
changes effected without requirements are likely to expose unforeseen

issues.

If there is a feature without support via the DDS, and that feature
is available only with the SQL DDL, then a change specifically to
implement that feature via DDL would qualify as a valid modernization;

e.g.

implementing_surrogate keys_ using IDENTITY columns, for which no
support exists in\via DDS.

There are no special "bigger buffer sizes" that result from a mere
conversion; the Page Size setting defaults are different between DDS
and DDL, but they can be customized. There is little that comes from
that conversion that could not have been achieved by a change to [the
creation of] the existing DDS-defined files. The one conspicuous
difference from that change, is the potential for read-performance
improvements for the lack of data validation performed on read [per
the assumption that all SQL data was validated on input\write] is
one_non-modernization_ benefit from the change; for that same
effect, there is an implicit potential for an existing dependence on
the non-validation effect to be exposed in applications that use a file

that was changed.

Changing from DDS to SQL DDL is best performed only*after* having
converted existing applications from using RLA to using SQL DML, to
prevent some difficulties that might be encountered with existing
and\or persisting non-SQL applications from having changed the
underlying database physical *FILE objects into database SQL TABLE
*FILE objects; most notably, Record Format Level Identifiers and Data
Mapping Errors per increased validation requirements for SQL database
objects. Failing to consider the[se and possibly other] side effects
of changing from DDS to SQL DDL can result in issues that were not

foreseen or noticed in testing.

I know of some installations that had implemented such a switch,
only to find out afterward that negative effects on some existing
applications required that they back-out the changes... surely at
great cost; I recall several specifically, one because an APAR was
opened, regardless there was nothing that would be/fixed/ because
they were close d Permanent Restriction (PRS) [increased memory
footprint for journaling had caused performance problems due to
implicit Page Size
(PAGESIZE) change; since, the SQL was enabled to set a smaller page
size to reduce the memory footprint for the non-SQL accesses] and all
but one of the others were incidents of data mapping errors being
effected on input\write that had never been diagnosed before the change

to DDL.

--
Regards, Chuck

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.