RE: Can I use DDS to create an SQL table name

Data integrity is DDL's biggest advantage in my mind. So much bad data could be avoided if we just had DDL files with the proper keys. If you have ever seen duplicate records in a file you know what I'm talking about!

Here are the other ones that are awesome:

1. Identity columns (think surrogate keys). Surrogate keys are really foreign to DDS programmers but make so much sense because it protects you from changes in the data model. I don't know about other organizations but our data model is always evolving.
2. As for the performance just look at the IBM's own performance aggregates: http://www-03.ibm.com/systems/resources/systems_i_software_db2_pdf_Performance_DDS_SQL.pdf


-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx]
Sent: Friday, October 24, 2014 2:02 PM
To: Midrange Systems Technical Discussion
Subject: Re: Can I use DDS to create an SQL table name

There is the integrity of DDL on the validation on write though. With DDL you just would absolutely not get that decimal data error.
Again, I'm all for the integrity of DDL over DDS. I'm skeptical on the performance claims though.


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: Jon Paris <jon.paris@xxxxxxxxxxxxxx>
To: Midrange-L Midrange-l <midrange-l@xxxxxxxxxxxx>
Date: 10/24/2014 02:43 PM
Subject: Re: Can I use DDS to create an SQL table name
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



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.